👮🏻‍♀️HKCent Cyber Security👮🏻‍♂️

ADVERSARIAL ARTIFICIAL INTELLIGENCE

laura@hkcent.org (Laura Collins) — Sat, 13 May 2023 15:18:00 +0000

A student at MIT developed an algorithm that trick AI to misidentify objects. For example, the algorithm can wrongly identify a headphone as a gun at an airport terminal. By switching a few pixels or adding noise to the image, AI can misidentify a gray tabby cat as a bowl of guacamole.

Resource: https://www.theregister.com/2017/11/06/mit_fooling_ai/

Solution: https://wmx-api-production.s3.amazonaws.com/courses/5741/supplementary/us-18-Parikh-Protecting-the-Protector-Hardening-Machine-Learning-Defenses-Against-Adversarial-Attacks.pdf

Bodywire-Human Body Communication (HBC) using Electro Quasi Static Human Body Communication (EQS-HBC)

laura@hkcent.org (Laura Collins) — Mon, 15 Feb 2021 01:17:00 +0000

Dr. Sheryan Sen, an associate professor at Purdue University, shared his team's research paper titled, "Enabling New Interaction Modalities by Communicating Strictly Through Touch Using Electro-Quasistatic Human Body Communication."

Dr. Sen argues that wearable devices that use Bluetooth could use our body as a wire to send information securely because the signals are not getting radiated, but only transferred by touching. Using this technology you can open a computer, a home/car door, also create a second-factor authentication by just toughing with your finger. Also, it can transfer my contact information through a handshake.

As far as the applications of this technology are concerned, the possibility includes augmented reality, virtual reality, basically anything you think Bluetooth is used today around the body. This technology can replace Bluetooth technology to connect/transfer data with Human-computer Interaction utilizing low frequency Electro-Quasi Static Human Body Communication.

Currently, the team sees huge application space and multiple different large companies as well as startups have shown interest including the touch-based-ID technology and they are working with them to develop this technology and bring it to market.

Sources

https://thecyberwire.com/podcasts/research-saturday/170/notes

https://www.purdue.edu/newsroom/releases/2020/Q4/tech-makes-it-possible-to-digitally-communicate-through-human-touch.html

Follow conflicts or geopolitical tensions

laura@hkcent.org (Laura Collins) — Mon, 08 Feb 2021 01:09:00 +0000

Joe Slowik, a Senior Security Researcher at DomainTools, discussed their research team's discovery based on their theory that identifying infrastructure in adversary operations by tracking identifiers related to major events and conflict zones can yield insights into defense and response for upcoming incidents.

During the initial investigation, the research team discovered that a document reflecting very specific themes related to the conflict in the Caucasus region between Armenia and Azerbijan. The document was masquerading as a news article and attempted to communicate with a certain domain.

The team found an unusually long string of numbers as a template object trying to attempt to communicate a domain: msofficeupate.org. Also, the template item is serving as a signifier to identify additional samples similarly constructed.

Based on the characteristics found, DomainTools researchers identified 35 domains matching the patterns associated with the initially observed malicious domain.

Overall, the adversary operations were related to political, military, and related subjects in the Caucasus region. By tracking identifiers and pivoting the investigation, the researcher were able to link to a phishing email that is state sponsored.

The lessons the team discovered is that the analysis of both the malicious documents and related network infrastructure by tracking identifiers can be used to gain insight to deploy defensive countermeasure that is coming in the near future because it is unlikely that adversaries will completely change their life cycle.

Kimsuky - the North Korean APT group

laura@hkcent.org (Laura Collins) — Sun, 31 Jan 2021 01:46:00 +0000

Yonatan Striem-Amit, CTO and co-founder at Cybereason, discussed Kimsuky "KGH_Spy" Suite and "CSPY downloader."

Kimsuky is a North Korean Cyber espionage group which has been active since 2012. Their target has been expanded from Korean peninsula to US and European countries. The observed targets are COIVD-19 related research companies, Think Tanks, Human Right Groups, South Korean Military, etc. The infrastructure of Kimsuky consists with phishing campaign and RATs, Remote access took kits. The method of detection evasion is backdating or timestomping by changing dates to 2015, 2016 from 2020 to evade the forensic investigation.

Cybereason Nocturnus discovered KGH_SPY and CSPY Downloader. KGH_SPY is a kit that gives the operator full control of your computer by executing a filess malware. CSPY is a tool designed to evade analysis. It installs a small beacon and once it is executed, it starts the cascading effect of downloading more content from the internet. It has built-in evasion techniques by using stolen signed signature.

The take-home lessons from this research findings are first, IT hygiene and user training so they don't fall prey into the traps. Second, adopt endpoint protections which is effective detecting Kimsuky malware and spyware. Lastly, be vigilant.

Sources

https://www.cybereason.com/blog/back-to-the-future-inside-the-kimsuky-kgh-spyware-suite

https://thecyberwire.com/podcasts/research-saturday/168/notes

Trickbot is still alive.

laura@hkcent.org (Laura Collins) — Sun, 24 Jan 2021 20:43:00 +0000

Mark Arena, CEO of Intel471, shared his team's analysis on Trickbot during the period from Sep 22, 2020 to Nov. 6, 2020. Trickbot has been active since Oct. 2016 and started as a bank trojan and evolved to Spyware and backdoor malware. Also, its target has been evolved from customers to corporates. Trickbot is stealthy and invisible to users, so the victims are unaware of the infection. The methods of infection are spam emails or malware as a service by providing framework to access victims computer.

On Sept. 22, 2020 U.S. Cyber Command modified several Trickbot configuration files that were sent by their C&C infrastructure. The goal was to disrupt the communication between bots and their control servers. However the actors maintained access to systems they already were engaged in their intrusion activity.

On Oct. 12, 2020 Microsoft announced their action against Trickbot by appealing to the court to order U.S. hosting companies that host Trickbot controller infrastructure to shut down the infrastructure. From Oct.13, 2020, to Nov. 1, 2020 Intel471 started noticing a significant amount of Trickbot's infrastructure was inoperable. Between Oc.28, 2020 and Nov. 6, 2020, Intel471 has not seen any new Trickbot infection campaigns.

In the recent Ryuk ransomware attacks, incident responders have reported that they saw a malware known as BazarLoader instead of Trickbot. BazarLoader is linked to the Trickbot operators in that it shared the infrastructure and code similarities. This indicates Trickbot continues to launch targeted ransomware attacks successfully despite the disruption of the Trickbot infrastructure.

In conclusion, U.S. Cyber Command and Microsoft action on Trickbot by disrupting Trickbot infrastructure caused the actors to spend time and effort setting up new infrastructure instead of impacting and ransoming victims. However, we are unsure actors will continue their operation using Trickbot or move entirely to BazarLoader. Nonetheless, we see a greenlight in that the Trickbot infrastructure disruption delayed the intrusion operations.

https://thecyberwire.com/podcasts/research-saturday/167/noteshttps://thecyberwire.com/podcasts/research-saturday/167/notes

How Blockchain (BC) technology in medical device industry

laura@hkcent.org (Laura Collins) — Sun, 03 Jan 2021 04:01:00 +0000

What is Blockchain (BC) technology? Basically, it is a linked, hashed, time stamped, decentralized record which makes it very secure because no one can modify the records. The technology behind of Bitcoin is blockchain. How does it affect medical device industry? According to Yoav Raiter, Star Fish Medical, there will be significant changes in the medical device industry as listed below.

1. Have patient control their data not the hospital.

2. Have regulators take full control of real time anonymous patient data.

3. Arrange financial coverage by governments and insurance companies.

4. Support new treatment releases.

5. Control outbreak of disease by health organizations.

6. Disrupt the development and certification of medical devices.

7. Manage Device Master Record (DMR) and Device History File (DHF).

8. Manage the life cycle of each manufactured medical device unit.

9. Help provide faster FDA approvals.

10. Help regulators automate audit, even real time background audits.

Will the block chain technology change our current technology with data, which is highly centralized by Google? If then, how should we prepare our medical device industry for the new blockchain world? I clearly see the benefit of blockchain in that it will increase security, agility, and innovation in the medical device industry.

Resource:

https://starfishmedical.com/blog/blockchain-technology/

Questionable "Mintegral" Ad Software Development Kit (SDK)

laura@hkcent.org (Laura Collins) — Fri, 25 Dec 2020 18:40:00 +0000

Alyssa Miller from Synk shared their research findings on Mintegral SDK's malicious behaviors in their codes.

Mintegral SDK is a mediator between advertisers and app developers by providing an ad platform that developers can download and insert ads in their app to monetize when an app user clicks on an advertisement.

The research team looked at the fingerprints of Mintegral SDK on iOS and found that the codes are obfuscated as well as command and control functions which is unusual for ad SDK. Then, the team looked at the attrition space, and their volume was much higher than their competition attrition owners. When a user clicks an ad in the app, all the clicks of any URLs including the ads sent from other advertisement source sent to Mintegral server and modified as if it was from Mintegral. In addition, all the user traffics were sent to Mintegral server including secret google docs while the app is open.

The research team cannot confirm what Mintegral SDK do with the sensitive user information and Apple Store doesn't seem too concerned about it. On the surface, Mintegral is just stealing attrition from other advertising source and there is no user impact because of this.

How did the SDK pass the App Store's verification? The SDK used obfuscation and a method called swizzling which happens on Runtime. In order to detect those behaviors, it requires in depth analysis which is impossible for AppStore to go through daily.

Miller advises that app developers need to be aware of malicious ad SDK that their app can serve to expose user's sensitive data by clicking ads in their app. He believes it is crucial that we research into these open source to find malicious behaviours to prevent any cyber crimes.

https://thecyberwire.com/podcasts/research-saturday/164/notes

SSL/TSL, is it our protector or enabler?

laura@hkcent.org (Laura Collins) — Sun, 06 Dec 2020 00:29:00 +0000

Deepen Desai, CISO and VP of Security Research and Operations at Zscaler, talked about Zscaler cloud research report - 2020: The State of Encrypted Attacks, which the research team analyzed encrypted traffic across the Zscaler cloud for the first nine months of 2020.

Zscaler Cloud blocked a 6.6 billion security threats that were encrypted which is basically average of 733 million blocked a month in 2020 compared to 283 million blocked in 2019. 1.6 billion out of 6.6 billion encrypted threats were targeting healthcare. According to the report, 80% of all cyber traffic is encrypted and the SSL based attack has been increased 200%. Especially, the ransomware attacks has increased 500%.

The most popular brands that have been spoofed are Office 365, Tech Support, PayPal, Google, and Netflix. Nowadays, you cannot differentiate the spoofed site with the legitimate sites.

How do we prevent the SSL/TLS based attacks? First, inspect all SSL traffic by setting up a proxy based architecture. The on-premise security tool such as next generation firewalls are incapable of performing decrypt, inspect, and re-encrypt effectively. Second, set up a true zero-trust network access architecture which means there is no network presence of any of the user laptops. Zscaler performs SSL inspect at scale as part of its platform of services.

In conclusion, Desai emphasized the importance of defense in-depth to prevent the increasingly popular SSL/TLS encrypted attacks.

https://thecyberwire.com/podcasts/research-saturday/162/notes

The importance of least privilege in IAM role configuration

laura@hkcent.org (Laura Collins) — Sat, 28 Nov 2020 02:20:00 +0000

Matt Chiodi, Chief Security Officer(CSO) for Public Cloud at Palo Alto Networks, discussed the highlights from the biannual Unit 42 Cloud Threat Report.

The team conducted a red team exercise on a high profile company with a large cybersecurity team to find the vulnerability in their cloud configuration. Surprisingly, the team could take over the company's AWS environment due to a misconfigured IAM role ("AssumeRole")which lead to access S3 buckets and encryption keys. This red team exercise revealed that the vulnerability of the system wasn't AWS misconfiguration but IAM role misconfiguration. The misconfigured identity in the cloud impact is much greater than on-premise system.

So, how do companies prevent from this happening? First, practice the concept of least privilege. 74% of JAPAC & EMEA organizations use admin role for workloads. It is recommended to grant non admin role for workloads operations. Second, automate scanning their IAM roles looking for the misconfigurations and fix it. Third, don't gloss over the cyber security due to the cost. The attackers are gotten smarter and it is harder to catch them because they design their scripts to run at a low utilization. The attackers use misconfigured environments for cryptojacking.

https://thecyberwire.com/podcasts/research-saturday/161/notes

Don't be afraid to fail!

laura@hkcent.org (Laura Collins) — Tue, 17 Nov 2020 21:09:00 +0000

Larry Cashdollar from Akamai talked about his first CVE experience back in 1999 and how the experience played out his career path in Cyber Security.

According to Larry, his first CVE was conducted without permission. He was working as a Linux system admin for Bath Iron Works under contract by Computer Sciences Corporation. They had a SGI Onyx/2 machine room that was allowed to only a few senior members. One day, one of the senior members taunted the junior team members that they would get the access to the room if they get the root access to the machine. There he started his first pen testing to get the root access to the Onyx machine following steps below without any permission.

1. Went to /usr/sbin to look for setuid binaries.

2. Found "Midikeys" file which has setuid bit set on it and ran it.

3. Opened up the password file and put a zero in his user ID and saved it and then logged in again.

4. Logged into the Onyx's LP, forward an x window back to his machine and forward back a -execute the Midikeys, bring it up, open up /etc/password, created a "Larry" account, made the user id"0"

5. Got a root prompt.

6. Changed the root password to CTRL-D by accident thinking he was changing the password for "Larry."

Larry was panicked after this incident and tried to inform the admin on the password change but he was in the middle of giving a demo to Navy admiral and upper managements. Luckily, the admin was able to change the password.

Despite the ominous incident, he got the legitimacy to conduct pen testing instead of being fired from the position.

He sent the CVE to Bugtraq, which is a network security notification system people used in the late 90s, and there were over 230 CVEs he had posted since then.

While working for Akamai CERT he started look at WordPress plugins and found lots of vulnerabilities. These days, he finds vulnerabilities in Web applications and a /temp race condition vulnerability for Solaris 11x86, where one of their utilities would create a file in /temp and then you get root access by using chmod /etc/shadow and then change the password.

The advice Larry would like to give for the students and the junior Cyber Security professionals is first, don't be afraid to fail. You learn from your failures. Second, don't lose your traction for learning new stuff. Lastly, don't do anything illegal. Make sure you get permission to do stuff.

https://thecyberwire.com/podcasts/research-saturday/160/notes

Click below for more information on the race condition venerability.

Race condition venerability

APT41 and human greed

laura@hkcent.org (Laura Collins) — Thu, 05 Nov 2020 16:11:00 +0000

Jon Dimaggio from Symantec's Threat Hunter Team talked about the cyber attacks that involve China based hacker group called 'APT41' for espionage and cyber crimes. APT41 is an early adaptor of 'supply chain' attack which they leverage victims by establishing relationships with companies to use them for later bigger attacks and has been active since 2012. Jon stated that the confusing part of this investigation was that it is unusual in that state-sponsored espionage group involved with financial gain motivated cyber crimes. The motivation behind the espionage is not for financial gains but to collect intellectual information for political and military purposes. APT41 is a custom developed sophisticated espionage malware that has invested with time and money. It is unlikely that the government would permit to use it for financial gains as it can expose its signature to researchers which result in close of the long standing operation. One crucial tactic his team used to identify the crime was to monitor the human logging event timestamps. It appeared that the cyber crime actors were active between 10 pm to 1 am which is unusual time for espionage activity to happen. It lead to a hypothesis that it could be a moonlighting activity caused by a human greed. It is well known fact that highly skilled tech individual is well paid and lives a comfortable life in China but it appears human greed lead to perform the cyber attack for a personal capital gain to earn cyber money using APT41 malware thinking that they wouldn't be caught. The US government indicted seven individuals including APT41 with variety of crimes. Two of them worked both the espionage and cyber crimes operations. He believes that his team is doing a good job with investigating this cyber attacks based on the information revealed on the indictment. As the result of this indictment, APT41 will change their tactics, retool the attacks , and slow down but comeback with new creative way of attacks.

https://thecyberwire.com/podcasts/research-saturday/158/notes

Search Engine Optimization (SEO) from Thenewboston

laura@hkcent.org (Laura Collins) — Mon, 26 Oct 2020 18:48:00 +0000

How Google works?

Spider and bots crawl all the info on webpages and save in big database. When someone types a keyword, it looks at its database and gives you the websites that are the most relevant to this keyword based on the factors listed below.

1. Domain name and structure. Get url that includes the keyword and change profile.php to keyword.php.

Ex. Key word: Pokémon URL: www.bestpoketmongo.com/poketmon.php

2. Title and Meta Description. Make the title relevant to the key word and keep the meta description minimum to show up in full.

3. Don't' change <h1> to <h3> to make heading bigger. Instead change the font_size:13px.

4. One long article is better than many pages.

5. Search popular key words or use Google Trend for key words.

6. Add links to the popular community such as GitHub, Wikipedia, Reddit.

7. Make a lot of external sites pointing to the site.

8. Create social media account such as Facebook, Twitter, GitHub.

9. Contact the relevant review blogger and look at your site to promote.

https://youtu.be/F2jfZ9AwwAQ

A Dual-Layer Architecture for the Protection of Medical Devices (CT, MRI, Ultra sounds)

laura@hkcent.org (Laura Collins) — Mon, 26 Oct 2020 18:11:00 +0000

Tom Mahler from Ben-Gurion University has shared a solution that will protect medial medical devices such as CT, MRI, Ultra sound systems that use a host control PC to send instructions to the gantry by developing two layered architecture: (1)an unsupervised context-free layer that detects anomalies based solely on the instructions' content and intercorrelations; and (2) a supervised context-sensitive layer that detects anomalies with respect to the classifier's output, relative to the clinical objectives. This solution improved anomaly detection performance from 71.6% to 82.3%-98.8%.

Why do we need this solution in addition to Firewalls and other network monitoring solutions? Number one, there are big gaps in the context of these devices are being used. The same device is used for kids and adults and the device does not know which patient is being evaluated which can introduce potentially harmful threats to patients such as radiation overexposure. Number two, the host is closed system, so security updates cannot be installed to the endpoint devices. This solution can monitor the traffic between the host system and the gantry by plugging in one side and the output goes to the gantry without changing the hosting PC's networks or interface. If the system fails, simply you need to unplug the solution and everything operates without interruption. Lastly, the layer 2 utilizes AI technology to learn its context to detect the anomaly of instruction which helps manufacture to detect the cyber attacks, human errors, or host PC software bugs.

This additional context sensitive layer enables to detect anomalies which cannot be detected using only context free layer that anomalies based solely on the instructions' content and intercorrelations.

https://thecyberwire.com/podcasts/research-saturday/157/notes

ZDNet poll: Is Google guilty of antitrust violations?

laura@hkcent.org (Laura Collins) — Mon, 26 Oct 2020 17:07:00 +0000

The United States Department of Justice filed a lawsuit against Google accusing of "Exclusionary practices that are harmful to competition." Do you think they are right?

https://www.zdnet.com/article/zdnet-poll-is-google-guilty-of-antitrust-violations/

In my opinion, they are wrong. Google is not trying to monopoly and harm its competitors but benefiting many people making information globally accessible and useful. I believe this lawsuit is simply showing how government can kill excellence. I am not an expert on tech or laws but only an average Google user. However, I know how much Google helped me fill my thirst in learning without costing any money. I have learned my programming, networking, building websites without costing any money other than my Comcast bill. I am quite ignorant of Apple but I believe Apple is doing exact the same thing that Google is being guilty of and they are not being sued for it. I think the core role of government is serving its people not other business. Is Google harming other business or people? This is different from Rockefeller's oil industry monopoly. We need to look at Google's intention or mission of its business.

"Google’s corporate mission is “to organize the world’s information and make it universally accessible and useful.” Ever since its beginnings, the company has focused on developing its proprietary algorithms to maximize effectiveness in organizing online information. Google continues to focus on ensuring people’s access to the information they need. The company’s mission statement adheres to a utilitarian benefit that the business provides to its users. "

http://panmore.com/google-vision-statement-mission-statement#:~:text=In%20this%20regard%2C%20the%20following%20are%20the%20primary,information%202%20Organization%203%20Universal%20accessibility%204%20Usefulness

I believe killing Google to help its competitor is to kill its own people. I have experienced many cases Apple charging for apps that are available for free in Android app store such as Duo app while working as a security analyst at a healthcare industry. I think this lawsuit is helping other business that's aim is making money. Google has been great source of my own growth. I believe Google is an excellent company that has a strong mission to improve people's life better using their cutting edge technology. Thank you, Google!

VIEW from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Sat, 24 Oct 2020 01:28:00 +0000

Instruction: Create a temporary table called VIEW that gives you list of movie from the movie table that has greater than 8,000,000 views.

(Note: Views do not store data themselves and updates automatically as the source tables data updates.)

CREATE VIEW Jamesbond AS

SELECT name, view from movies

WHERE view>8,000,000

ALTER/DROP/RENAME TABLE from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Sat, 24 Oct 2020 00:44:00 +0000

Instruction: 1. Add 'name' column to 'movies' table. 2. Delete the added column. 3. Delete the table. 4. Rename the 'movies' table to 'netflix'.

ALTER TABLE movies ADD name varchar(30)

ALTER TABLE movies DROP COLUMN name

DROP TABLE movies

RENAME TABLE movies TO netflix

🡇

CREATE TABLE & NOT NULL & AUTO INCREMENT from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Sat, 24 Oct 2020 00:11:00 +0000

Instruction: Create a table with id that auto-increments and columns that is not empty such as username and password.

CREATE TABLE movie (

id int NOT NULL AUTO_INCREMENT,

username varchar(30) NOT NULL,

password varchar(20) NOT NULL,

PRIMARY KEY(id)

)

UPDATE & DELETE from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Fri, 23 Oct 2020 18:26:00 +0000

Instruction: Edit Astrazeneca PLC to Astrazeneca and delete Sinovac.

UPDATE company SET name="Astrazeneca" WHERE name="Astrazeneca PLC";

DELETE FROM company WHERE name="Sinovac"

name	symbol	country
Johnson & Johnson	JNJ	US
Pfizer	PFE	US/Germany
Moderna	MRNA	US
Astrazeneca	AZN	UK
GlaxoSmithkilne	GSK	UK/France
Novavax	NVAX	US
Casino Biologics	6185[Hong Kong]	China

INSERT INTO from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Fri, 23 Oct 2020 17:50:00 +0000

Instruction: Insert the companies in China to the company table.

INSERT INTO company("name, symbol, country") VALUES

("Casino Biologics, 6185[Hong Kong], China"),

("Sinovac, Private, China")

name	symbol	country
Johnson & Johnson	JNJ	US
Pfizer	PFE	US/Germany
Moderna	MRNA	US
Astrazeneca PLC	AZN	UK
GlaxoSmithkilne	GSK	UK/France
Novavax	NVAX	US
Casino Biologics	6185[Hong Kong]	China
Sinovac	Private	China

Full-Text Searching from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Fri, 23 Oct 2020 17:32:00 +0000

Instruction: Give me all the Covid-19 Vaccine Candidates in the US and exclude companies in China.

ALTER TABLE company ADD FULLTEXT (country)

SELECT name, symbol, country FROM company

WHERE Match(country) Against("+US - CHINA" IN BOOLEAN MODE)

name	symbol	country
Johnson & Johnson	JNJ	US
Pfizer	PFE	US/Germany
Moderna	MRNA	US
Astrazeneca PLC	AZN	UK
GlaxoSmithkilne	GSK	UK/France
Novavax	NVAX	US

UNION from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Fri, 23 Oct 2020 16:43:00 +0000

Instruction: Give me all the top rated and the most watched James Bond movies.

SELECT name, rate, views FROM movies WHERE rate >24

UNION

SELECT name, rate, views FROM movies WHERE views>1,000,000

(Note: Columns should be the same to work and this command will remove duplicate entries.)

name	rate	views
Dr. No	24	1,000,000
FROM RUSSIA WITH LOVE	23	2,000,000
GOLDFINGER	22	3,000,000
THUNDERBALT	21	4,000,000
YOU ONLY LIVE TWICE	20	5,000,000
On HER MAJESTY'S SECRET SERVICE	19	6,000,000
Diamonds Are Forever	18	7,000,000
LIVE AND LET DIE	17	8,000,000
MOONRAKER	16	8,000,000
FOR YOUR EYES ONLY	15	8,000,000
OCTOPUSSY	14	8,000,000
NEVER SAY NEVER AGAIN	13	8,000,000
A VIEW TO A KILL	12	8,000,000
THE LIVING DAY LINGTS	5	8,000,000
LICENSE TO KILL	6	8,000,000
GOLDEN EYE	1	10,000,000
Tomorrow Never Dies	2	10,000,000
The World Is Not Enough	3	10,000,000
DIE ANOTHER DAY	4	10,000,000
CASINO ROYALE	7	9,000,000
QYUANTUM OF SOLACE	8	9,000,000
Skyfall	9	9,000,000
Spectre	10	9,000,000
No Time to Die	11	9,000,000

LEFT OUTER JOIN ... ON...from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Fri, 23 Oct 2020 15:37:00 +0000

Instruction: Give me all customers with their selling items even if they are not selling any items.

SELECT customers.name, items.name

FROM customers

LEFT OUTER JOIN items ON customers.id=seller_id

name	name
Henry Jackson	Google Pixel 3
Harry Potter	NULL
Sherry Gibbons	Samsung Ear Pods
Jedi Simpson	Beer
Luke Skywalker	Alexa
Darth Vader	Google Home

SQL basics - Join tables from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Mon, 19 Oct 2020 21:25:00 +0000

Instruction: Add seller information to the items table. (You need to join two tables by finding the relation between two tables.)

SELECT customers.id, customers.name, items.name, items.cost

FROM customers, items

WHERE customers.id=seller_id

ORDER BY customers.id

id	name	name	cost
1	Laura Kwon	Chocolate	10
2	Bucky Parker	Books	20
3	Noah Burger	Pixel 10	30
4	David Smith	Laptop	40
5	Teresa Jackson	Yoga pants	50
6	Gary Foster	Monitor	60

SQL Basics -Subquery from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Mon, 19 Oct 2020 20:47:00 +0000

Instruction: Give me the name of an item that costs the least.

SELECT name, MIN(cost)

FROM items

WHERE name LIKE "%boxes of chocolates"

AND seller_id

IN(

SELECT seller_id FROM items WHERE name LIKE "%boxes of chocolates"

)

name	MIN(cost)
3 boxes of chocolates	30.4899997711182

Source:

https://youtu.be/rDqtBKEY9Ok

https://youtu.be/6BfofgkrI3Y

SQL basics -GROUP BY from THENEWBOSTON

laura@hkcent.org (Laura Collins) — Mon, 19 Oct 2020 16:25:00 +0000

Instruction: Give me the seller ID and number of items from each seller who listed 3 or more items arranged from highest to lowest.

SELECT seller_id, count(*) AS item_count

FROM items

GROUP BY seller_id

HAVING COUNT(*)>=3

ORDER BY item_count DESC

source: https://youtu.be/GjjU6gdfXzc