QuocCao-dev/01.select-order-by-1.md

Last active December 4, 2022 05:39

Star (0) You must be signed in to star a gist
Fork (0) You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/QuocCao-dev/2b33ca26699672b20318a1af88e4a45f.js"></script>
Save QuocCao-dev/2b33ca26699672b20318a1af88e4a45f to your computer and use it in GitHub Desktop.

Raw

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0)
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26);

Several records have been inserted into the table. Create a query that displays all data from the Stock table sorted in ascending order by the Ticker column.
Several records have been inserted into the table. Create a query that displays all records from the Stock table sorted in descending order by the Price column.
Several records have been inserted into the table. Create a query that displays the columns from the Stock table (in the given order): Ticker FullName Price Sort the result by the Price column in descending order.
Several records have been inserted into the table. Create a query that displays the columns from the Stock table (in the given order): Ticker Price Sort the result by the FullName column in ascending order.

Raw

02.select-order-by-2.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

EPS means Earnings Per Share. Create a query that displays all the data in the table sorted in ascending order by EPS. Note the missing data in the table.
Create a query that displays all the data from the Stock table sorted by EPS in descending order. Note the missing data in the table.
Create a query that displays all the data from the Stock table sorted by columns: Sector ascending, Ticker ascending
Create a query that displays the specified columns from the Stock table: Sector Ticker Price, sorted by columns accordingly: Sector ascending, Ticker ascending
Create a query that displays the specified columns from the Stock table: Sector EPS Ticker Price, sorted by columns accordingly: Sector descending, EPS descending

Raw

03.select-order-by-3.md

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS job;
 
CREATE TABLE country (
	country_id   TEXT    NOT NULL,
	country_name TEXT    NOT NULL,
	PRIMARY KEY (country_id)
);
 
CREATE TABLE location (
	location_id    INTEGER NOT NULL,
	street_address TEXT,
	postal_code    TEXT,
	city           TEXT    NOT NULL,
	state_province TEXT,
	country_id     INTEGER NOT NULL,
	PRIMARY KEY (location_id),
	FOREIGN KEY (country_id) REFERENCES country (country_id)
);
 
CREATE TABLE department (
	department_id   INTEGER NOT NULL,
	department_name TEXT    NOT NULL,
	location_id     INTEGER NOT NULL,
	PRIMARY KEY (department_id),
	FOREIGN KEY (location_id) REFERENCES location (location_id)
);
 
CREATE TABLE job (
	job_id     INTEGER NOT NULL,
	job_title  TEXT    NOT NULL,
	min_salary REAL    NOT NULL,
	max_salary REAL    NOT NULL,
	PRIMARY KEY (job_id)
);
 
CREATE TABLE employee (
	employee_id   INTEGER NOT NULL,
	first_name    TEXT,
	last_name     TEXT    NOT NULL,
	email         TEXT    NOT NULL,
	phone_number  TEXT,
	hire_date     TEXT    NOT NULL,
	job_id        INTEGER NOT NULL,
	salary        REAL    NOT NULL,
	department_id INTEGER NOT NULL,
	PRIMARY KEY (employee_id),
	FOREIGN KEY (job_id) REFERENCES job (job_id),
	FOREIGN KEY (department_id) REFERENCES department (department_id)
);
 
-- country
INSERT INTO country(country_id, country_name) VALUES ('AR', 'Argentina');
INSERT INTO country(country_id, country_name) VALUES ('AU', 'Australia');
INSERT INTO country(country_id, country_name) VALUES ('BE', 'Belgium');
INSERT INTO country(country_id, country_name) VALUES ('BR', 'Brazil');
INSERT INTO country(country_id, country_name) VALUES ('CA', 'Canada');
INSERT INTO country(country_id, country_name) VALUES ('CH', 'Switzerland');
INSERT INTO country(country_id, country_name) VALUES ('CN', 'China');
INSERT INTO country(country_id, country_name) VALUES ('DE', 'Germany');
INSERT INTO country(country_id, country_name) VALUES ('DK', 'Denmark');
INSERT INTO country(country_id, country_name) VALUES ('EG', 'Egypt');
INSERT INTO country(country_id, country_name) VALUES ('FR', 'France');
INSERT INTO country(country_id, country_name) VALUES ('HK', 'HongKong');
INSERT INTO country(country_id, country_name) VALUES ('IL', 'Israel');
INSERT INTO country(country_id, country_name) VALUES ('IN', 'India');
INSERT INTO country(country_id, country_name) VALUES ('IT', 'Italy');
INSERT INTO country(country_id, country_name) VALUES ('JP', 'Japan');
INSERT INTO country(country_id, country_name) VALUES ('KW', 'Kuwait');
INSERT INTO country(country_id, country_name) VALUES ('MX', 'Mexico');
INSERT INTO country(country_id, country_name) VALUES ('NG', 'Nigeria');
INSERT INTO country(country_id, country_name) VALUES ('NL', 'Netherlands');
INSERT INTO country(country_id, country_name) VALUES ('SG', 'Singapore');
INSERT INTO country(country_id, country_name) VALUES ('UK', 'United Kingdom');
INSERT INTO country(country_id, country_name) VALUES ('US', 'United States of America');
INSERT INTO country(country_id, country_name) VALUES ('ZM', 'Zambia');
INSERT INTO country(country_id, country_name) VALUES ('ZW', 'Zimbabwe');
 
-- location
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2500, 'Magdalen Centre,  The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
 
-- job
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (1, 'Public Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (2, 'Accounting Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (3, 'Administration Assistant', 3000.00, 6000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (4, 'President', 20000.00, 40000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (5, 'Administration Vice President', 15000.00, 30000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (6, 'Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (7, 'Finance Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (8, 'Human Resources Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (9, 'Programmer', 4000.00, 10000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (10, 'Marketing Manager', 9000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (11, 'Marketing Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (12, 'Public Relations Representative', 4500.00, 10500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (13, 'Purchasing Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (14, 'Purchasing Manager', 8000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (15, 'Sales Manager', 10000.00, 20000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (16, 'Sales Representative', 6000.00, 12000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (17, 'Shipping Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (18, 'Stock Clerk', 2000.00, 5000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (19, 'Stock Manager', 5500.00, 8500.00);
 
-- department
INSERT INTO department(department_id, department_name, location_id) VALUES (1, 'Administration', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (2, 'Marketing', 1800);
INSERT INTO department(department_id, department_name, location_id) VALUES (3, 'Purchasing', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (4, 'Human Resources', 2400);
INSERT INTO department(department_id, department_name, location_id) VALUES (5, 'Shipping', 1500);
INSERT INTO department(department_id, department_name, location_id) VALUES (6, 'IT', 1400);
INSERT INTO department(department_id, department_name, location_id) VALUES (7, 'Public Relations', 2700);
INSERT INTO department(department_id, department_name, location_id) VALUES (8, 'Sales', 2500);
INSERT INTO department(department_id, department_name, location_id) VALUES (9, 'Executive', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (10, 'Finance', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (11, 'Accounting', 1700);
 
-- employee
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (100, 'Steven', 'King', '[email protected]', '515.123.4567', '1987-06-17', 4, 24000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (101, 'Neena', 'Kochhar', '[email protected]', '515.123.4568', '1989-09-21', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (102, 'Lex', 'De Haan', 'lex.de [email protected]', '515.123.4569', '1993-01-13', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (103, 'Alexander', 'Hunold', '[email protected]', '590.423.4567', '1990-01-03', 9, 9000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (104, 'Bruce', 'Ernst', '[email protected]', '590.423.4568', '1991-05-21', 9, 6000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (105, 'David', 'Austin', '[email protected]', '590.423.4569', '1997-06-25', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (106, 'Valli', 'Pataballa', '[email protected]', '590.423.4560', '1998-02-05', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (107, 'Diana', 'Lorentz', '[email protected]', '590.423.5567', '1999-02-07', 9, 4200.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (108, 'Nancy', 'Greenberg', '[email protected]', '515.124.4569', '1994-08-17', 7, 12000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (109, 'Daniel', 'Faviet', '[email protected]', '515.124.4169', '1994-08-16', 6, 9000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (110, 'John', 'Chen', '[email protected]', '515.124.4269', '1997-09-28', 6, 8200.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (111, 'Ismael', 'Sciarra', '[email protected]', '515.124.4369', '1997-09-30', 6, 7700.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (112, 'Jose Manuel', 'Urman', 'jose [email protected]', '515.124.4469', '1998-03-07', 6, 7800.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (113, 'Luis', 'Popp', '[email protected]', '515.124.4567', '1999-12-07', 6, 6900.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (114, 'Den', 'Raphaely', '[email protected]', '515.127.4561', '1994-12-07', 14, 11000.00, 3);

Create a query that displays the columns from the employee table:

first_name
last_name
email
salary

Sort the result in descending order by the salary column.

Create a query that displays the columns from the employee table:

first_name
last_name
email
salary

Sort the result in ascending order by the following columns: first_name and last_name.

Create a query that displays the columns from the employee table:

department_id
email
salary

Sort the result by department_id in ascending order, then by salary in descending order.

Create a query that displays all of the data from the job table. Additionally, add a column that calculates the difference between the maximum and minimum salary for a given position and assign the alias diff_salary. Sort the result by decreasing diff_salary.

Raw

04.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

Create a query that displays the first 5 records from the Stock table.
Create a query that displays 5 records from the Stock table that have the highest price (the Price column). Sort the result in descending order by the Price column.
Create a query that displays the first three records in the Stock table that have the highest EPS (earnings per share) value. Sort the result in descending order by the EPS column.
Create a query that displays the following columns from the Stock table:

Sector
FullName
Price

Sort the result by the Sector column in descending order. Limit the result to the first five records.

Create a query that skips the first three records of the Stock table and displays the next five records.
Create a query that displays the following columns from the Stock table:

Ticker
Price
EPS

Sort the result by the EPS column in descending order. Then skip the first three records and display the next five records.

Create a query that displays the third company with the highest EPS from the Stock table. Use the LIMIT and OFFSET clauses for this exercise.
Create a query that displays the second company with the lowest Price from the Stock table. Use the LIMIT and OFFSET clauses for this exercise.

Raw

05.md

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS job;
 
CREATE TABLE country (
	country_id   TEXT    NOT NULL,
	country_name TEXT    NOT NULL,
	PRIMARY KEY (country_id)
);
 
CREATE TABLE location (
	location_id    INTEGER NOT NULL,
	street_address TEXT,
	postal_code    TEXT,
	city           TEXT    NOT NULL,
	state_province TEXT,
	country_id     INTEGER NOT NULL,
	PRIMARY KEY (location_id),
	FOREIGN KEY (country_id) REFERENCES country (country_id)
);
 
CREATE TABLE department (
	department_id   INTEGER NOT NULL,
	department_name TEXT    NOT NULL,
	location_id     INTEGER NOT NULL,
	PRIMARY KEY (department_id),
	FOREIGN KEY (location_id) REFERENCES location (location_id)
);
 
CREATE TABLE job (
	job_id     INTEGER NOT NULL,
	job_title  TEXT    NOT NULL,
	min_salary REAL    NOT NULL,
	max_salary REAL    NOT NULL,
	PRIMARY KEY (job_id)
);
 
CREATE TABLE employee (
	employee_id   INTEGER NOT NULL,
	first_name    TEXT,
	last_name     TEXT    NOT NULL,
	email         TEXT    NOT NULL,
	phone_number  TEXT,
	hire_date     TEXT    NOT NULL,
	job_id        INTEGER NOT NULL,
	salary        REAL    NOT NULL,
	department_id INTEGER NOT NULL,
	PRIMARY KEY (employee_id),
	FOREIGN KEY (job_id) REFERENCES job (job_id),
	FOREIGN KEY (department_id) REFERENCES department (department_id)
);
 
-- country
INSERT INTO country(country_id, country_name) VALUES ('AR', 'Argentina');
INSERT INTO country(country_id, country_name) VALUES ('AU', 'Australia');
INSERT INTO country(country_id, country_name) VALUES ('BE', 'Belgium');
INSERT INTO country(country_id, country_name) VALUES ('BR', 'Brazil');
INSERT INTO country(country_id, country_name) VALUES ('CA', 'Canada');
INSERT INTO country(country_id, country_name) VALUES ('CH', 'Switzerland');
INSERT INTO country(country_id, country_name) VALUES ('CN', 'China');
INSERT INTO country(country_id, country_name) VALUES ('DE', 'Germany');
INSERT INTO country(country_id, country_name) VALUES ('DK', 'Denmark');
INSERT INTO country(country_id, country_name) VALUES ('EG', 'Egypt');
INSERT INTO country(country_id, country_name) VALUES ('FR', 'France');
INSERT INTO country(country_id, country_name) VALUES ('HK', 'HongKong');
INSERT INTO country(country_id, country_name) VALUES ('IL', 'Israel');
INSERT INTO country(country_id, country_name) VALUES ('IN', 'India');
INSERT INTO country(country_id, country_name) VALUES ('IT', 'Italy');
INSERT INTO country(country_id, country_name) VALUES ('JP', 'Japan');
INSERT INTO country(country_id, country_name) VALUES ('KW', 'Kuwait');
INSERT INTO country(country_id, country_name) VALUES ('MX', 'Mexico');
INSERT INTO country(country_id, country_name) VALUES ('NG', 'Nigeria');
INSERT INTO country(country_id, country_name) VALUES ('NL', 'Netherlands');
INSERT INTO country(country_id, country_name) VALUES ('SG', 'Singapore');
INSERT INTO country(country_id, country_name) VALUES ('UK', 'United Kingdom');
INSERT INTO country(country_id, country_name) VALUES ('US', 'United States of America');
INSERT INTO country(country_id, country_name) VALUES ('ZM', 'Zambia');
INSERT INTO country(country_id, country_name) VALUES ('ZW', 'Zimbabwe');
 
-- location
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2500, 'Magdalen Centre,  The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
 
-- job
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (1, 'Public Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (2, 'Accounting Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (3, 'Administration Assistant', 3000.00, 6000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (4, 'President', 20000.00, 40000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (5, 'Administration Vice President', 15000.00, 30000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (6, 'Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (7, 'Finance Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (8, 'Human Resources Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (9, 'Programmer', 4000.00, 10000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (10, 'Marketing Manager', 9000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (11, 'Marketing Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (12, 'Public Relations Representative', 4500.00, 10500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (13, 'Purchasing Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (14, 'Purchasing Manager', 8000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (15, 'Sales Manager', 10000.00, 20000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (16, 'Sales Representative', 6000.00, 12000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (17, 'Shipping Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (18, 'Stock Clerk', 2000.00, 5000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (19, 'Stock Manager', 5500.00, 8500.00);
 
-- department
INSERT INTO department(department_id, department_name, location_id) VALUES (1, 'Administration', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (2, 'Marketing', 1800);
INSERT INTO department(department_id, department_name, location_id) VALUES (3, 'Purchasing', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (4, 'Human Resources', 2400);
INSERT INTO department(department_id, department_name, location_id) VALUES (5, 'Shipping', 1500);
INSERT INTO department(department_id, department_name, location_id) VALUES (6, 'IT', 1400);
INSERT INTO department(department_id, department_name, location_id) VALUES (7, 'Public Relations', 2700);
INSERT INTO department(department_id, department_name, location_id) VALUES (8, 'Sales', 2500);
INSERT INTO department(department_id, department_name, location_id) VALUES (9, 'Executive', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (10, 'Finance', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (11, 'Accounting', 1700);
 
-- employee
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (100, 'Steven', 'King', '[email protected]', '515.123.4567', '1987-06-17', 4, 24000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (101, 'Neena', 'Kochhar', '[email protected]', '515.123.4568', '1989-09-21', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (102, 'Lex', 'De Haan', 'lex.de [email protected]', '515.123.4569', '1993-01-13', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (103, 'Alexander', 'Hunold', '[email protected]', '590.423.4567', '1990-01-03', 9, 9000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (104, 'Bruce', 'Ernst', '[email protected]', '590.423.4568', '1991-05-21', 9, 6000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (105, 'David', 'Austin', '[email protected]', '590.423.4569', '1997-06-25', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (106, 'Valli', 'Pataballa', '[email protected]', '590.423.4560', '1998-02-05', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (107, 'Diana', 'Lorentz', '[email protected]', '590.423.5567', '1999-02-07', 9, 4200.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (108, 'Nancy', 'Greenberg', '[email protected]', '515.124.4569', '1994-08-17', 7, 12000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (109, 'Daniel', 'Faviet', '[email protected]', '515.124.4169', '1994-08-16', 6, 9000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (110, 'John', 'Chen', '[email protected]', '515.124.4269', '1997-09-28', 6, 8200.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (111, 'Ismael', 'Sciarra', '[email protected]', '515.124.4369', '1997-09-30', 6, 7700.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (112, 'Jose Manuel', 'Urman', 'jose [email protected]', '515.124.4469', '1998-03-07', 6, 7800.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (113, 'Luis', 'Popp', '[email protected]', '515.124.4567', '1999-12-07', 6, 6900.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (114, 'Den', 'Raphaely', '[email protected]', '515.127.4561', '1994-12-07', 14, 11000.00, 3);

Create a query that displays the first ten rows from the job table.
Create a query that displays the top five highest paying positions (take into account the max_salary column).
Create a query that displays the top five highest paying positions. Take the mean value of the columns into account:

min_salary
max_salary

To do this, create a new column called avg_salary that will contain the average value of the above-mentioned columns. In the result table, display all columns of the job table including the avg_salary column (as the last column).

Raw

06.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

Create a query that displays unique values for the Sector column (unique sector names) from the Stock table.

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS job;
 
CREATE TABLE country (
	country_id   TEXT    NOT NULL,
	country_name TEXT    NOT NULL,
	PRIMARY KEY (country_id)
);
 
CREATE TABLE location (
	location_id    INTEGER NOT NULL,
	street_address TEXT,
	postal_code    TEXT,
	city           TEXT    NOT NULL,
	state_province TEXT,
	country_id     INTEGER NOT NULL,
	PRIMARY KEY (location_id),
	FOREIGN KEY (country_id) REFERENCES country (country_id)
);
 
CREATE TABLE department (
	department_id   INTEGER NOT NULL,
	department_name TEXT    NOT NULL,
	location_id     INTEGER NOT NULL,
	PRIMARY KEY (department_id),
	FOREIGN KEY (location_id) REFERENCES location (location_id)
);
 
CREATE TABLE job (
	job_id     INTEGER NOT NULL,
	job_title  TEXT    NOT NULL,
	min_salary REAL    NOT NULL,
	max_salary REAL    NOT NULL,
	PRIMARY KEY (job_id)
);
 
CREATE TABLE employee (
	employee_id   INTEGER NOT NULL,
	first_name    TEXT,
	last_name     TEXT    NOT NULL,
	email         TEXT    NOT NULL,
	phone_number  TEXT,
	hire_date     TEXT    NOT NULL,
	job_id        INTEGER NOT NULL,
	salary        REAL    NOT NULL,
	department_id INTEGER NOT NULL,
	PRIMARY KEY (employee_id),
	FOREIGN KEY (job_id) REFERENCES job (job_id),
	FOREIGN KEY (department_id) REFERENCES department (department_id)
);
 
-- country
INSERT INTO country(country_id, country_name) VALUES ('AR', 'Argentina');
INSERT INTO country(country_id, country_name) VALUES ('AU', 'Australia');
INSERT INTO country(country_id, country_name) VALUES ('BE', 'Belgium');
INSERT INTO country(country_id, country_name) VALUES ('BR', 'Brazil');
INSERT INTO country(country_id, country_name) VALUES ('CA', 'Canada');
INSERT INTO country(country_id, country_name) VALUES ('CH', 'Switzerland');
INSERT INTO country(country_id, country_name) VALUES ('CN', 'China');
INSERT INTO country(country_id, country_name) VALUES ('DE', 'Germany');
INSERT INTO country(country_id, country_name) VALUES ('DK', 'Denmark');
INSERT INTO country(country_id, country_name) VALUES ('EG', 'Egypt');
INSERT INTO country(country_id, country_name) VALUES ('FR', 'France');
INSERT INTO country(country_id, country_name) VALUES ('HK', 'HongKong');
INSERT INTO country(country_id, country_name) VALUES ('IL', 'Israel');
INSERT INTO country(country_id, country_name) VALUES ('IN', 'India');
INSERT INTO country(country_id, country_name) VALUES ('IT', 'Italy');
INSERT INTO country(country_id, country_name) VALUES ('JP', 'Japan');
INSERT INTO country(country_id, country_name) VALUES ('KW', 'Kuwait');
INSERT INTO country(country_id, country_name) VALUES ('MX', 'Mexico');
INSERT INTO country(country_id, country_name) VALUES ('NG', 'Nigeria');
INSERT INTO country(country_id, country_name) VALUES ('NL', 'Netherlands');
INSERT INTO country(country_id, country_name) VALUES ('SG', 'Singapore');
INSERT INTO country(country_id, country_name) VALUES ('UK', 'United Kingdom');
INSERT INTO country(country_id, country_name) VALUES ('US', 'United States of America');
INSERT INTO country(country_id, country_name) VALUES ('ZM', 'Zambia');
INSERT INTO country(country_id, country_name) VALUES ('ZW', 'Zimbabwe');
 
-- location
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2500, 'Magdalen Centre,  The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
 
-- job
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (1, 'Public Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (2, 'Accounting Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (3, 'Administration Assistant', 3000.00, 6000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (4, 'President', 20000.00, 40000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (5, 'Administration Vice President', 15000.00, 30000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (6, 'Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (7, 'Finance Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (8, 'Human Resources Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (9, 'Programmer', 4000.00, 10000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (10, 'Marketing Manager', 9000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (11, 'Marketing Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (12, 'Public Relations Representative', 4500.00, 10500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (13, 'Purchasing Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (14, 'Purchasing Manager', 8000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (15, 'Sales Manager', 10000.00, 20000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (16, 'Sales Representative', 6000.00, 12000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (17, 'Shipping Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (18, 'Stock Clerk', 2000.00, 5000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (19, 'Stock Manager', 5500.00, 8500.00);
 
-- department
INSERT INTO department(department_id, department_name, location_id) VALUES (1, 'Administration', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (2, 'Marketing', 1800);
INSERT INTO department(department_id, department_name, location_id) VALUES (3, 'Purchasing', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (4, 'Human Resources', 2400);
INSERT INTO department(department_id, department_name, location_id) VALUES (5, 'Shipping', 1500);
INSERT INTO department(department_id, department_name, location_id) VALUES (6, 'IT', 1400);
INSERT INTO department(department_id, department_name, location_id) VALUES (7, 'Public Relations', 2700);
INSERT INTO department(department_id, department_name, location_id) VALUES (8, 'Sales', 2500);
INSERT INTO department(department_id, department_name, location_id) VALUES (9, 'Executive', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (10, 'Finance', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (11, 'Accounting', 1700);
 
-- employee
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (100, 'Steven', 'King', '[email protected]', '515.123.4567', '1987-06-17', 4, 24000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (101, 'Neena', 'Kochhar', '[email protected]', '515.123.4568', '1989-09-21', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (102, 'Lex', 'De Haan', 'lex.de [email protected]', '515.123.4569', '1993-01-13', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (103, 'Alexander', 'Hunold', '[email protected]', '590.423.4567', '1990-01-03', 9, 9000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (104, 'Bruce', 'Ernst', '[email protected]', '590.423.4568', '1991-05-21', 9, 6000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (105, 'David', 'Austin', '[email protected]', '590.423.4569', '1997-06-25', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (106, 'Valli', 'Pataballa', '[email protected]', '590.423.4560', '1998-02-05', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (107, 'Diana', 'Lorentz', '[email protected]', '590.423.5567', '1999-02-07', 9, 4200.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (108, 'Nancy', 'Greenberg', '[email protected]', '515.124.4569', '1994-08-17', 7, 12000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (109, 'Daniel', 'Faviet', '[email protected]', '515.124.4169', '1994-08-16', 6, 9000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (110, 'John', 'Chen', '[email protected]', '515.124.4269', '1997-09-28', 6, 8200.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (111, 'Ismael', 'Sciarra', '[email protected]', '515.124.4369', '1997-09-30', 6, 7700.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (112, 'Jose Manuel', 'Urman', 'jose [email protected]', '515.124.4469', '1998-03-07', 6, 7800.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (113, 'Luis', 'Popp', '[email protected]', '515.124.4567', '1999-12-07', 6, 6900.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (114, 'Den', 'Raphaely', '[email protected]', '515.127.4561', '1994-12-07', 14, 11000.00, 3);

Create a query that displays the unique first names from the employee table.
Create a query that displays the number of unique first names (the first_name column) from the employee table. Assign the alias unique_first_name to the result.

Raw

07.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

Create a query that displays a record for the company with the symbol 'TSLA' (Ticker column).
Create a query that displays records for the following companies (Ticker column):

'TSLA'
'AMZN'

Create a query that displays records for the following companies (Ticker column):

'TSLA'
'AMZN'
'MSFT'

Create a query that displays records for companies whose price is greater than $ 250 (Price column).
Create a query that displays records for companies whose price is less than $ 100 or greater than $ 1000 (Price column).
Create a query that displays records for companies whose price is less than $ 500 (Price column) and EPS is greater than $ 5.
Create a query that displays records for companies that have a value in the Id column other than 7.
Create a query that displays records for companies with a price between $ 100 and $ 300 inclusive (Price column).
Create a query that displays records for companies whose price is not in the range [100, 300] (Price column).
Create a query that displays records for companies whose Ticker is different from the given values: 'V', 'FB', 'PYPL'
Create a query that displays records for companies with a missing EPS value
Create a query that displays records for companies that have a non-NULL EPS value.
Create a query that displays records for companies whose name starts with the letter 'A' (column FullName).
Create a query that displays records for companies whose Ticker is exactly 4 characters long.
Create a query that displays records for companies whose name contains the text 'INC' (column FullName).
Create a query that displays records for companies whose names contain the text 'INC' (FullName column) and the Ticker is exactly 4 characters long.
Create a query that displays records for companies whose name (column FullName) does not contain the text 'CORP'.
Create a query that displays records for companies whose sector name contains the text 'TECH' (Sector column) and the EPS value is above $ 5.

Raw

08.md

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS job;
 
CREATE TABLE country (
	country_id   TEXT    NOT NULL,
	country_name TEXT    NOT NULL,
	PRIMARY KEY (country_id)
);
 
CREATE TABLE location (
	location_id    INTEGER NOT NULL,
	street_address TEXT,
	postal_code    TEXT,
	city           TEXT    NOT NULL,
	state_province TEXT,
	country_id     INTEGER NOT NULL,
	PRIMARY KEY (location_id),
	FOREIGN KEY (country_id) REFERENCES country (country_id)
);
 
CREATE TABLE department (
	department_id   INTEGER NOT NULL,
	department_name TEXT    NOT NULL,
	location_id     INTEGER NOT NULL,
	PRIMARY KEY (department_id),
	FOREIGN KEY (location_id) REFERENCES location (location_id)
);
 
CREATE TABLE job (
	job_id     INTEGER NOT NULL,
	job_title  TEXT    NOT NULL,
	min_salary REAL    NOT NULL,
	max_salary REAL    NOT NULL,
	PRIMARY KEY (job_id)
);
 
CREATE TABLE employee (
	employee_id   INTEGER NOT NULL,
	first_name    TEXT,
	last_name     TEXT    NOT NULL,
	email         TEXT    NOT NULL,
	phone_number  TEXT,
	hire_date     TEXT    NOT NULL,
	job_id        INTEGER NOT NULL,
	salary        REAL    NOT NULL,
	department_id INTEGER NOT NULL,
	PRIMARY KEY (employee_id),
	FOREIGN KEY (job_id) REFERENCES job (job_id),
	FOREIGN KEY (department_id) REFERENCES department (department_id)
);
 
-- country
INSERT INTO country(country_id, country_name) VALUES ('AR', 'Argentina');
INSERT INTO country(country_id, country_name) VALUES ('AU', 'Australia');
INSERT INTO country(country_id, country_name) VALUES ('BE', 'Belgium');
INSERT INTO country(country_id, country_name) VALUES ('BR', 'Brazil');
INSERT INTO country(country_id, country_name) VALUES ('CA', 'Canada');
INSERT INTO country(country_id, country_name) VALUES ('CH', 'Switzerland');
INSERT INTO country(country_id, country_name) VALUES ('CN', 'China');
INSERT INTO country(country_id, country_name) VALUES ('DE', 'Germany');
INSERT INTO country(country_id, country_name) VALUES ('DK', 'Denmark');
INSERT INTO country(country_id, country_name) VALUES ('EG', 'Egypt');
INSERT INTO country(country_id, country_name) VALUES ('FR', 'France');
INSERT INTO country(country_id, country_name) VALUES ('HK', 'HongKong');
INSERT INTO country(country_id, country_name) VALUES ('IL', 'Israel');
INSERT INTO country(country_id, country_name) VALUES ('IN', 'India');
INSERT INTO country(country_id, country_name) VALUES ('IT', 'Italy');
INSERT INTO country(country_id, country_name) VALUES ('JP', 'Japan');
INSERT INTO country(country_id, country_name) VALUES ('KW', 'Kuwait');
INSERT INTO country(country_id, country_name) VALUES ('MX', 'Mexico');
INSERT INTO country(country_id, country_name) VALUES ('NG', 'Nigeria');
INSERT INTO country(country_id, country_name) VALUES ('NL', 'Netherlands');
INSERT INTO country(country_id, country_name) VALUES ('SG', 'Singapore');
INSERT INTO country(country_id, country_name) VALUES ('UK', 'United Kingdom');
INSERT INTO country(country_id, country_name) VALUES ('US', 'United States of America');
INSERT INTO country(country_id, country_name) VALUES ('ZM', 'Zambia');
INSERT INTO country(country_id, country_name) VALUES ('ZW', 'Zimbabwe');
 
-- location
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2500, 'Magdalen Centre,  The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
 
-- job
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (1, 'Public Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (2, 'Accounting Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (3, 'Administration Assistant', 3000.00, 6000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (4, 'President', 20000.00, 40000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (5, 'Administration Vice President', 15000.00, 30000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (6, 'Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (7, 'Finance Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (8, 'Human Resources Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (9, 'Programmer', 4000.00, 10000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (10, 'Marketing Manager', 9000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (11, 'Marketing Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (12, 'Public Relations Representative', 4500.00, 10500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (13, 'Purchasing Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (14, 'Purchasing Manager', 8000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (15, 'Sales Manager', 10000.00, 20000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (16, 'Sales Representative', 6000.00, 12000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (17, 'Shipping Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (18, 'Stock Clerk', 2000.00, 5000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (19, 'Stock Manager', 5500.00, 8500.00);
 
-- department
INSERT INTO department(department_id, department_name, location_id) VALUES (1, 'Administration', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (2, 'Marketing', 1800);
INSERT INTO department(department_id, department_name, location_id) VALUES (3, 'Purchasing', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (4, 'Human Resources', 2400);
INSERT INTO department(department_id, department_name, location_id) VALUES (5, 'Shipping', 1500);
INSERT INTO department(department_id, department_name, location_id) VALUES (6, 'IT', 1400);
INSERT INTO department(department_id, department_name, location_id) VALUES (7, 'Public Relations', 2700);
INSERT INTO department(department_id, department_name, location_id) VALUES (8, 'Sales', 2500);
INSERT INTO department(department_id, department_name, location_id) VALUES (9, 'Executive', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (10, 'Finance', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (11, 'Accounting', 1700);
 
-- employee
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (100, 'Steven', 'King', '[email protected]', '515.123.4567', '1987-06-17', 4, 24000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (101, 'Neena', 'Kochhar', '[email protected]', '515.123.4568', '1989-09-21', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (102, 'Lex', 'De Haan', 'lex.de [email protected]', '515.123.4569', '1993-01-13', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (103, 'Alexander', 'Hunold', '[email protected]', '590.423.4567', '1990-01-03', 9, 9000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (104, 'Bruce', 'Ernst', '[email protected]', '590.423.4568', '1991-05-21', 9, 6000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (105, 'David', 'Austin', '[email protected]', '590.423.4569', '1997-06-25', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (106, 'Valli', 'Pataballa', '[email protected]', '590.423.4560', '1998-02-05', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (107, 'Diana', 'Lorentz', '[email protected]', '590.423.5567', '1999-02-07', 9, 4200.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (108, 'Nancy', 'Greenberg', '[email protected]', '515.124.4569', '1994-08-17', 7, 12000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (109, 'Daniel', 'Faviet', '[email protected]', '515.124.4169', '1994-08-16', 6, 9000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (110, 'John', 'Chen', '[email protected]', '515.124.4269', '1997-09-28', 6, 8200.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (111, 'Ismael', 'Sciarra', '[email protected]', '515.124.4369', '1997-09-30', 6, 7700.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (112, 'Jose Manuel', 'Urman', 'jose [email protected]', '515.124.4469', '1998-03-07', 6, 7800.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (113, 'Luis', 'Popp', '[email protected]', '515.124.4567', '1999-12-07', 6, 6900.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (114, 'Den', 'Raphaely', '[email protected]', '515.127.4561', '1994-12-07', 14, 11000.00, 3);

Create a query that displays job titles with a minimum wage of 9,000 or more.

sql SELECT job_title FROM job WHERE min_salary >= 9000;

Create a query that displays the job names for which the minimum wage is in the range [5000, 8000].

SELECT job_title FROM job WHERE min_salary BETWEEN 5000 AND 8000;

Create a query that displays all data for employees hired on '1999-12-07'

SELECT * FROM employee WHERE hire_date = '1999-12-07';

Create a query that displays all data from the employee table for employees with the value of employee_id: 101, 106, 109.

SELECT * FROM employee WHERE employee_id IN (101, 106, 109);

Raw

09.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

Create a query that displays the number of records in the Stock table.

SELECT COUNT(*) FROM Stock;

Create a query that displays the number of non-empty values in the EPS column. Assign the alias NonMissingEPS to the result.

SELECT COUNT(EPS) AS NonMissingEPS FROM Stock;

Create a query that displays the number of NULL values in the EPS column. Assign the alias MissingEPS to the result.

SELECT COUNT(*) - COUNT(EPS) AS MissingEPS FROM Stock;

Create a query that displays the number of unique values for the Sector column and assign an alias NumberOfSectors to the result.

SELECT COUNT(DISTINCT Sector) AS NumberOfSectors FROM Stock;

Create a query that displays the average value for the Price column. Assign the alias AvgPrice to the result.

SELECT AVG(Price) AS AvgPrice FROM Stock;

Create a query that displays the lowest and highest values for EPS in the table. Assign the MinEPS and MaxEPS aliases respectively.

SELECT MIN(EPS) AS MinEPS, MAX(EPS) AS MaxEPS FROM Stock;

Create a query that displays the following columns from the Stock table:

Id
Ticker
FullName

Also add a fourth column to the result that is the number of characters in the Ticker. Assign the alias TickerLength to the fourth column.

SELECT
  Id,
  Ticker,
  FullName,
  LENGTH(Ticker) AS TickerLength
FROM
  Stock;

Raw

10.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

Group data at the Sector level. Then, calculate the number of companies for each sector and assign an alias NumOfStocks.

SELECT Sector, COUNT(*) AS NumOfStocks FROM Stock GROUP BY Sector;

Group data at the Sector level. Then, calculate the number of companies for each sector and assign an alias NumOfStocks. Sort the result by the NumOfStocks column in descending order.

SELECT
  Sector,
  COUNT(*) AS NumOfStocks
FROM
  Stock
GROUP BY
  Sector
ORDER BY
  NumOfStocks DESC;

Group data at the Sector level. Then find the average EPS for each sector and assign the alias AvgEPSPerSector. Sort the result by the AvgEPSPerSector column in descending order.

SELECT
  Sector,
  AVG(EPS) AS AvgEPSPerSector
FROM
  Stock
GROUP BY
  Sector
ORDER BY
  AvgEPSPerSector DESC;

Raw

11.md

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS job;
 
CREATE TABLE country (
	country_id   TEXT    NOT NULL,
	country_name TEXT    NOT NULL,
	PRIMARY KEY (country_id)
);
 
CREATE TABLE location (
	location_id    INTEGER NOT NULL,
	street_address TEXT,
	postal_code    TEXT,
	city           TEXT    NOT NULL,
	state_province TEXT,
	country_id     INTEGER NOT NULL,
	PRIMARY KEY (location_id),
	FOREIGN KEY (country_id) REFERENCES country (country_id)
);
 
CREATE TABLE department (
	department_id   INTEGER NOT NULL,
	department_name TEXT    NOT NULL,
	location_id     INTEGER NOT NULL,
	PRIMARY KEY (department_id),
	FOREIGN KEY (location_id) REFERENCES location (location_id)
);
 
CREATE TABLE job (
	job_id     INTEGER NOT NULL,
	job_title  TEXT    NOT NULL,
	min_salary REAL    NOT NULL,
	max_salary REAL    NOT NULL,
	PRIMARY KEY (job_id)
);
 
CREATE TABLE employee (
	employee_id   INTEGER NOT NULL,
	first_name    TEXT,
	last_name     TEXT    NOT NULL,
	email         TEXT    NOT NULL,
	phone_number  TEXT,
	hire_date     TEXT    NOT NULL,
	job_id        INTEGER NOT NULL,
	salary        REAL    NOT NULL,
	department_id INTEGER NOT NULL,
	PRIMARY KEY (employee_id),
	FOREIGN KEY (job_id) REFERENCES job (job_id),
	FOREIGN KEY (department_id) REFERENCES department (department_id)
);
 
-- country
INSERT INTO country(country_id, country_name) VALUES ('AR', 'Argentina');
INSERT INTO country(country_id, country_name) VALUES ('AU', 'Australia');
INSERT INTO country(country_id, country_name) VALUES ('BE', 'Belgium');
INSERT INTO country(country_id, country_name) VALUES ('BR', 'Brazil');
INSERT INTO country(country_id, country_name) VALUES ('CA', 'Canada');
INSERT INTO country(country_id, country_name) VALUES ('CH', 'Switzerland');
INSERT INTO country(country_id, country_name) VALUES ('CN', 'China');
INSERT INTO country(country_id, country_name) VALUES ('DE', 'Germany');
INSERT INTO country(country_id, country_name) VALUES ('DK', 'Denmark');
INSERT INTO country(country_id, country_name) VALUES ('EG', 'Egypt');
INSERT INTO country(country_id, country_name) VALUES ('FR', 'France');
INSERT INTO country(country_id, country_name) VALUES ('HK', 'HongKong');
INSERT INTO country(country_id, country_name) VALUES ('IL', 'Israel');
INSERT INTO country(country_id, country_name) VALUES ('IN', 'India');
INSERT INTO country(country_id, country_name) VALUES ('IT', 'Italy');
INSERT INTO country(country_id, country_name) VALUES ('JP', 'Japan');
INSERT INTO country(country_id, country_name) VALUES ('KW', 'Kuwait');
INSERT INTO country(country_id, country_name) VALUES ('MX', 'Mexico');
INSERT INTO country(country_id, country_name) VALUES ('NG', 'Nigeria');
INSERT INTO country(country_id, country_name) VALUES ('NL', 'Netherlands');
INSERT INTO country(country_id, country_name) VALUES ('SG', 'Singapore');
INSERT INTO country(country_id, country_name) VALUES ('UK', 'United Kingdom');
INSERT INTO country(country_id, country_name) VALUES ('US', 'United States of America');
INSERT INTO country(country_id, country_name) VALUES ('ZM', 'Zambia');
INSERT INTO country(country_id, country_name) VALUES ('ZW', 'Zimbabwe');
 
-- location
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2500, 'Magdalen Centre,  The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
 
-- job
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (1, 'Public Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (2, 'Accounting Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (3, 'Administration Assistant', 3000.00, 6000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (4, 'President', 20000.00, 40000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (5, 'Administration Vice President', 15000.00, 30000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (6, 'Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (7, 'Finance Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (8, 'Human Resources Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (9, 'Programmer', 4000.00, 10000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (10, 'Marketing Manager', 9000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (11, 'Marketing Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (12, 'Public Relations Representative', 4500.00, 10500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (13, 'Purchasing Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (14, 'Purchasing Manager', 8000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (15, 'Sales Manager', 10000.00, 20000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (16, 'Sales Representative', 6000.00, 12000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (17, 'Shipping Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (18, 'Stock Clerk', 2000.00, 5000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (19, 'Stock Manager', 5500.00, 8500.00);
 
-- department
INSERT INTO department(department_id, department_name, location_id) VALUES (1, 'Administration', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (2, 'Marketing', 1800);
INSERT INTO department(department_id, department_name, location_id) VALUES (3, 'Purchasing', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (4, 'Human Resources', 2400);
INSERT INTO department(department_id, department_name, location_id) VALUES (5, 'Shipping', 1500);
INSERT INTO department(department_id, department_name, location_id) VALUES (6, 'IT', 1400);
INSERT INTO department(department_id, department_name, location_id) VALUES (7, 'Public Relations', 2700);
INSERT INTO department(department_id, department_name, location_id) VALUES (8, 'Sales', 2500);
INSERT INTO department(department_id, department_name, location_id) VALUES (9, 'Executive', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (10, 'Finance', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (11, 'Accounting', 1700);
 
-- employee
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (100, 'Steven', 'King', '[email protected]', '515.123.4567', '1987-06-17', 4, 24000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (101, 'Neena', 'Kochhar', '[email protected]', '515.123.4568', '1989-09-21', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (102, 'Lex', 'De Haan', 'lex.de [email protected]', '515.123.4569', '1993-01-13', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (103, 'Alexander', 'Hunold', '[email protected]', '590.423.4567', '1990-01-03', 9, 9000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (104, 'Bruce', 'Ernst', '[email protected]', '590.423.4568', '1991-05-21', 9, 6000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (105, 'David', 'Austin', '[email protected]', '590.423.4569', '1997-06-25', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (106, 'Valli', 'Pataballa', '[email protected]', '590.423.4560', '1998-02-05', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (107, 'Diana', 'Lorentz', '[email protected]', '590.423.5567', '1999-02-07', 9, 4200.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (108, 'Nancy', 'Greenberg', '[email protected]', '515.124.4569', '1994-08-17', 7, 12000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (109, 'Daniel', 'Faviet', '[email protected]', '515.124.4169', '1994-08-16', 6, 9000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (110, 'John', 'Chen', '[email protected]', '515.124.4269', '1997-09-28', 6, 8200.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (111, 'Ismael', 'Sciarra', '[email protected]', '515.124.4369', '1997-09-30', 6, 7700.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (112, 'Jose Manuel', 'Urman', 'jose [email protected]', '515.124.4469', '1998-03-07', 6, 7800.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (113, 'Luis', 'Popp', '[email protected]', '515.124.4567', '1999-12-07', 6, 6900.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (114, 'Den', 'Raphaely', '[email protected]', '515.127.4561', '1994-12-07', 14, 11000.00, 3);

Group data from the employee table at the department_id level and calculate the number of employees for each department. Assign the alias number_of_employees to this column. Sort the result in descending order by number_of_employees.

SELECT
  department_id,
  COUNT(*) AS number_of_employees
FROM
  employee
GROUP BY
  department_id
ORDER BY
  number_of_employees DESC;

Group data from the employee table at the job_id level. Calculate the average salary for each position and assign the alias avg_salary. Sort the result by descending avg_salary.

SELECT
  job_id,
  AVG(salary) AS avg_salary
FROM
  employee
GROUP BY
  job_id
ORDER BY
  avg_salary DESC;

Raw

12.md

DROP TABLE IF EXISTS sale;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS brand;
 
CREATE TABLE category (
	category_id   INTEGER NOT NULL,
	category_name TEXT    NOT NULL,
	PRIMARY KEY (category_id)
);
 
CREATE TABLE brand (
	brand_id   INTEGER NOT NULL,
	brand_name TEXT    NOT NULL,
	PRIMARY KEY (brand_id)
);
 
CREATE TABLE product (
	product_id   INTEGER NOT NULL,
	product_name TEXT    NOT NULL,
	brand_id     INTEGER NOT NULL,
	category_id  INTEGER,
	model_year   INTEGER NOT NULL,
	list_price   REAL    NOT NULL,
	PRIMARY KEY (product_id),
	FOREIGN KEY (category_id) REFERENCES category (category_id),
	FOREIGN KEY (brand_id) REFERENCES brand (brand_id)
);
 
CREATE TABLE sale (
	order_id   INTEGER NOT NULL,
	product_id INTEGER NOT NULL,
	quantity   INTEGER NOT NULL,
	PRIMARY KEY (order_id),
	FOREIGN KEY (product_id) REFERENCES product (product_id)
);
 
-- category
INSERT INTO category (category_id, category_name) VALUES (1, 'Children Bicycles');
INSERT INTO category (category_id, category_name) VALUES (2, 'Comfort Bicycles');
INSERT INTO category (category_id, category_name) VALUES (3, 'Cruisers Bicycles');
INSERT INTO category (category_id, category_name) VALUES (4, 'Cyclocross Bicycles');
INSERT INTO category (category_id, category_name) VALUES (5, 'Electric Bikes');
INSERT INTO category (category_id, category_name) VALUES (6, 'Mountain Bikes');
INSERT INTO category (category_id, category_name) VALUES (7, 'Road Bikes');
 
-- brand 
INSERT INTO brand (brand_id, brand_name) VALUES (1, 'Electra');
INSERT INTO brand (brand_id, brand_name) VALUES (2, 'Haro');
INSERT INTO brand (brand_id, brand_name) VALUES (3, 'Heller');
INSERT INTO brand (brand_id, brand_name) VALUES (4, 'Pure Cycles');
INSERT INTO brand (brand_id, brand_name) VALUES (5, 'Ritchey');
INSERT INTO brand (brand_id, brand_name) VALUES (6, 'Strider');
INSERT INTO brand (brand_id, brand_name) VALUES (7, 'Sun Bicycles');
INSERT INTO brand (brand_id, brand_name) VALUES (8, 'Surly');
INSERT INTO brand (brand_id, brand_name) VALUES (9, 'Trek');
 
-- product
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (1, 'Trek 820 - 2021', 9, 6, 2021, 379.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (2, 'Ritchey Timberwolf Frameset - 2021', 5, 6, 2021, 749.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (3, 'Surly Wednesday Frameset - 2021', 8, 6, 2021, 999.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (4, 'Trek Fuel EX 8 29 - 2021', 9, 6, 2021, 2899.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (5, 'Heller Shagamaw Frame - 2021', 3, 6, 2021, 1320.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (6, 'Surly Ice Cream Truck Frameset - 2021', 8, 6, 2021, 469.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (7, 'Trek Slash 8 27.5 - 2021', 9, 6, 2021, 3999.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (8, 'Trek Remedy 29 Carbon Frameset - 2021', 9, 6, 2021, 1799.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (9, 'Trek Conduit+ - 2021', 9, 5, 2021, 2999.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (10, 'Surly Straggler - 2021', 8, 4, 2021, 1549);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (11, 'Surly Straggler 650b - 2021', 8, 4, 2021, 1680.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (12, 'Electra Townie Original 21D - 2021', 1, 3, 2021, 549.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (13, 'Electra Cruiser 1 (24-Inch) - 2021', 1, 3, 2021, 269.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (14, 'Electra Girl''s Hawaii 1 (16-inch) - 2020/2021', 1, 3, 2021, 269.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (15, 'Electra Moto 1 - 2021', 1, 3, 2021, 529.99);
 
-- sale
INSERT INTO sale (order_id, product_id, quantity) VALUES (1, 3, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (2, 12, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (3, 1, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (4, 4, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (5, 8, 3);
INSERT INTO sale (order_id, product_id, quantity) VALUES (6, 9, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (7, 5, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (8, 6, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (9, 10, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (10, 1, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (11, 2, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (12, 7, 3);
INSERT INTO sale (order_id, product_id, quantity) VALUES (13, 8, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (14, 5, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (15, 11, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (16, 4, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (17, 2, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (18, 8, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (19, 10, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (20, 11, 1);

Group data from the sale table at product_id level. Calculate the number of orders for each product and assign the alias number_of_orders.

SELECT
  product_id,
  COUNT(*) AS number_of_orders
FROM
  sale
GROUP BY
  product_id;

The following query is given:

SELECT
  product_id,
  COUNT(*) AS number_of_orders
FROM
  sale
GROUP BY
  product_id;

Transform this query to extract the first three records with the highest value for the number_of_orders column.

SELECT
  product_id,
  COUNT(*) AS number_of_orders
FROM
  sale
GROUP BY
  product_id
ORDER BY
  number_of_orders DESC
LIMIT
  3;

Raw

13.md

DROP TABLE IF EXISTS Stock;
 
CREATE TABLE Stock (
	Id         INTEGER PRIMARY KEY,
	Ticker     TEXT    NOT NULL,
	FullName   TEXT    NOT NULL,
	TradingDay TEXT    NOT NULL,
	Price      REAL    NOT NULL CHECK (Price > 0),
	Sector     TEXT    NOT NULL,
	EPS        REAL
);
 
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (1, 'AAPL', 'APPLE INC.', '2021-02-03', 134.99, 'Electronic Technology', 3.74);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (2, 'MSFT', 'MICROSOFT CORP.', '2021-02-03', 240.7, 'Technology Services', 6.78);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (3, 'AMZN', 'AMAZON COM INC', '2021-02-03', 3380.29, 'Retail Trade', 42.60);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (4, 'GOOGL', 'ALPHABET INC (GOOGLE) CLASS A', '2021-02-03', 2040.09, 'Technology Services', 59.26);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (5, 'FB', 'FACEBOOK INC', '2021-02-03', 264.88, 'Technology Services', 10.23);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (6, 'TSLA', 'TESLA INC', '2021-02-03', 864.75, 'Consumer Durables', 0.57);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (7, 'BABA', 'ALIBABA GROUP HOLDINGS LTD.', '2021-02-03', 264.70, 'Retail Trade', 8.63);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (8, 'V', 'VISA INC', '2021-02-03', 202.83, 'Finance', 4.85);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (9, 'PYPL', 'PAYPAL HOLDINGS INC', '2021-02-03', 251.93, 'Technology Services', 2.68);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (10, 'ORCL', 'ORACLE CORP', '2021-02-03', 62.26, 'Technology Services', 3.37);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (11, 'JNJ', 'JOHNSON & JOHNSON', '2021-02-03', 160.5, 'Health Technology', NULL);
INSERT INTO Stock (Id, Ticker, FullName, TradingDay, Price, Sector, EPS) 
VALUES (12, 'CMCSA', 'COMCAST CORP', '2021-02-03', 50.47, 'Consumer Services', NULL);

Group data at the Sector level. Then find the average EPS for each sector and assign the alias AvgEPSPerSector. Display sectors with AvgEPSPerSector above 10 and sort the result by the AvgEPSPerSector column in descending order. Solution:

SELECT
  Sector,
  AVG(EPS) AS AvgEPSPerSector
FROM
  Stock
GROUP BY
  Sector
HAVING
  AVG(EPS) > 10
ORDER BY
  AvgEPSPerSector DESC;

Raw

14.md

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS location;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS job;
 
CREATE TABLE country (
	country_id   TEXT    NOT NULL,
	country_name TEXT    NOT NULL,
	PRIMARY KEY (country_id)
);
 
CREATE TABLE location (
	location_id    INTEGER NOT NULL,
	street_address TEXT,
	postal_code    TEXT,
	city           TEXT    NOT NULL,
	state_province TEXT,
	country_id     INTEGER NOT NULL,
	PRIMARY KEY (location_id),
	FOREIGN KEY (country_id) REFERENCES country (country_id)
);
 
CREATE TABLE department (
	department_id   INTEGER NOT NULL,
	department_name TEXT    NOT NULL,
	location_id     INTEGER NOT NULL,
	PRIMARY KEY (department_id),
	FOREIGN KEY (location_id) REFERENCES location (location_id)
);
 
CREATE TABLE job (
	job_id     INTEGER NOT NULL,
	job_title  TEXT    NOT NULL,
	min_salary REAL    NOT NULL,
	max_salary REAL    NOT NULL,
	PRIMARY KEY (job_id)
);
 
CREATE TABLE employee (
	employee_id   INTEGER NOT NULL,
	first_name    TEXT,
	last_name     TEXT    NOT NULL,
	email         TEXT    NOT NULL,
	phone_number  TEXT,
	hire_date     TEXT    NOT NULL,
	job_id        INTEGER NOT NULL,
	salary        REAL    NOT NULL,
	department_id INTEGER NOT NULL,
	PRIMARY KEY (employee_id),
	FOREIGN KEY (job_id) REFERENCES job (job_id),
	FOREIGN KEY (department_id) REFERENCES department (department_id)
);
 
-- country
INSERT INTO country(country_id, country_name) VALUES ('AR', 'Argentina');
INSERT INTO country(country_id, country_name) VALUES ('AU', 'Australia');
INSERT INTO country(country_id, country_name) VALUES ('BE', 'Belgium');
INSERT INTO country(country_id, country_name) VALUES ('BR', 'Brazil');
INSERT INTO country(country_id, country_name) VALUES ('CA', 'Canada');
INSERT INTO country(country_id, country_name) VALUES ('CH', 'Switzerland');
INSERT INTO country(country_id, country_name) VALUES ('CN', 'China');
INSERT INTO country(country_id, country_name) VALUES ('DE', 'Germany');
INSERT INTO country(country_id, country_name) VALUES ('DK', 'Denmark');
INSERT INTO country(country_id, country_name) VALUES ('EG', 'Egypt');
INSERT INTO country(country_id, country_name) VALUES ('FR', 'France');
INSERT INTO country(country_id, country_name) VALUES ('HK', 'HongKong');
INSERT INTO country(country_id, country_name) VALUES ('IL', 'Israel');
INSERT INTO country(country_id, country_name) VALUES ('IN', 'India');
INSERT INTO country(country_id, country_name) VALUES ('IT', 'Italy');
INSERT INTO country(country_id, country_name) VALUES ('JP', 'Japan');
INSERT INTO country(country_id, country_name) VALUES ('KW', 'Kuwait');
INSERT INTO country(country_id, country_name) VALUES ('MX', 'Mexico');
INSERT INTO country(country_id, country_name) VALUES ('NG', 'Nigeria');
INSERT INTO country(country_id, country_name) VALUES ('NL', 'Netherlands');
INSERT INTO country(country_id, country_name) VALUES ('SG', 'Singapore');
INSERT INTO country(country_id, country_name) VALUES ('UK', 'United Kingdom');
INSERT INTO country(country_id, country_name) VALUES ('US', 'United States of America');
INSERT INTO country(country_id, country_name) VALUES ('ZM', 'Zambia');
INSERT INTO country(country_id, country_name) VALUES ('ZW', 'Zimbabwe');
 
-- location
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2500, 'Magdalen Centre,  The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO location(location_id, street_address, postal_code, city, state_province, country_id) VALUES (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
 
-- job
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (1, 'Public Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (2, 'Accounting Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (3, 'Administration Assistant', 3000.00, 6000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (4, 'President', 20000.00, 40000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (5, 'Administration Vice President', 15000.00, 30000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (6, 'Accountant', 4200.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (7, 'Finance Manager', 8200.00, 16000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (8, 'Human Resources Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (9, 'Programmer', 4000.00, 10000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (10, 'Marketing Manager', 9000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (11, 'Marketing Representative', 4000.00, 9000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (12, 'Public Relations Representative', 4500.00, 10500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (13, 'Purchasing Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (14, 'Purchasing Manager', 8000.00, 15000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (15, 'Sales Manager', 10000.00, 20000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (16, 'Sales Representative', 6000.00, 12000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (17, 'Shipping Clerk', 2500.00, 5500.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (18, 'Stock Clerk', 2000.00, 5000.00);
INSERT INTO job(job_id, job_title, min_salary, max_salary) VALUES (19, 'Stock Manager', 5500.00, 8500.00);
 
-- department
INSERT INTO department(department_id, department_name, location_id) VALUES (1, 'Administration', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (2, 'Marketing', 1800);
INSERT INTO department(department_id, department_name, location_id) VALUES (3, 'Purchasing', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (4, 'Human Resources', 2400);
INSERT INTO department(department_id, department_name, location_id) VALUES (5, 'Shipping', 1500);
INSERT INTO department(department_id, department_name, location_id) VALUES (6, 'IT', 1400);
INSERT INTO department(department_id, department_name, location_id) VALUES (7, 'Public Relations', 2700);
INSERT INTO department(department_id, department_name, location_id) VALUES (8, 'Sales', 2500);
INSERT INTO department(department_id, department_name, location_id) VALUES (9, 'Executive', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (10, 'Finance', 1700);
INSERT INTO department(department_id, department_name, location_id) VALUES (11, 'Accounting', 1700);
 
-- employee
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (100, 'Steven', 'King', '[email protected]', '515.123.4567', '1987-06-17', 4, 24000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (101, 'Neena', 'Kochhar', '[email protected]', '515.123.4568', '1989-09-21', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (102, 'Lex', 'De Haan', 'lex.de [email protected]', '515.123.4569', '1993-01-13', 5, 17000.00, 9);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (103, 'Alexander', 'Hunold', '[email protected]', '590.423.4567', '1990-01-03', 9, 9000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (104, 'Bruce', 'Ernst', '[email protected]', '590.423.4568', '1991-05-21', 9, 6000.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (105, 'David', 'Austin', '[email protected]', '590.423.4569', '1997-06-25', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (106, 'Valli', 'Pataballa', '[email protected]', '590.423.4560', '1998-02-05', 9, 4800.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (107, 'Diana', 'Lorentz', '[email protected]', '590.423.5567', '1999-02-07', 9, 4200.00, 6);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (108, 'Nancy', 'Greenberg', '[email protected]', '515.124.4569', '1994-08-17', 7, 12000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (109, 'Daniel', 'Faviet', '[email protected]', '515.124.4169', '1994-08-16', 6, 9000.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (110, 'John', 'Chen', '[email protected]', '515.124.4269', '1997-09-28', 6, 8200.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (111, 'Ismael', 'Sciarra', '[email protected]', '515.124.4369', '1997-09-30', 6, 7700.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (112, 'Jose Manuel', 'Urman', 'jose [email protected]', '515.124.4469', '1998-03-07', 6, 7800.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (113, 'Luis', 'Popp', '[email protected]', '515.124.4567', '1999-12-07', 6, 6900.00, 10);
INSERT INTO employee(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (114, 'Den', 'Raphaely', '[email protected]', '515.127.4561', '1994-12-07', 14, 11000.00, 3);

Modify this query to display those job_id values for which the average salary is greater than 10,000. Solution

SELECT
  job_id,
  AVG(salary) AS avg_salary
FROM
  employee
GROUP BY
  job_id
HAVING
  AVG(salary) > 10000
ORDER BY
  avg_salary DESC;

Modify this query to display those job_id values for which the average salary is in the range [10,000, 20,000].

SELECT
  job_id,
  AVG(salary) AS avg_salary
FROM
  employee
GROUP BY
  job_id
ORDER BY
  avg_salary DESC;

Solution

SELECT
  job_id,
  AVG(salary) AS avg_salary
FROM
  employee
GROUP BY
  job_id
HAVING
  AVG(salary) BETWEEN 10000
  AND 20000
ORDER BY
  avg_salary DESC;

Raw

15.md

DROP TABLE IF EXISTS sale;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS brand;
 
CREATE TABLE category (
	category_id   INTEGER NOT NULL,
	category_name TEXT    NOT NULL,
	PRIMARY KEY (category_id)
);
 
CREATE TABLE brand (
	brand_id   INTEGER NOT NULL,
	brand_name TEXT    NOT NULL,
	PRIMARY KEY (brand_id)
);
 
CREATE TABLE product (
	product_id   INTEGER NOT NULL,
	product_name TEXT    NOT NULL,
	brand_id     INTEGER NOT NULL,
	category_id  INTEGER,
	model_year   INTEGER NOT NULL,
	list_price   REAL    NOT NULL,
	PRIMARY KEY (product_id),
	FOREIGN KEY (category_id) REFERENCES category (category_id),
	FOREIGN KEY (brand_id) REFERENCES brand (brand_id)
);
 
CREATE TABLE sale (
	order_id   INTEGER NOT NULL,
	product_id INTEGER NOT NULL,
	quantity   INTEGER NOT NULL,
	PRIMARY KEY (order_id),
	FOREIGN KEY (product_id) REFERENCES product (product_id)
);
 
-- category
INSERT INTO category (category_id, category_name) VALUES (1, 'Children Bicycles');
INSERT INTO category (category_id, category_name) VALUES (2, 'Comfort Bicycles');
INSERT INTO category (category_id, category_name) VALUES (3, 'Cruisers Bicycles');
INSERT INTO category (category_id, category_name) VALUES (4, 'Cyclocross Bicycles');
INSERT INTO category (category_id, category_name) VALUES (5, 'Electric Bikes');
INSERT INTO category (category_id, category_name) VALUES (6, 'Mountain Bikes');
INSERT INTO category (category_id, category_name) VALUES (7, 'Road Bikes');
 
-- brand 
INSERT INTO brand (brand_id, brand_name) VALUES (1, 'Electra');
INSERT INTO brand (brand_id, brand_name) VALUES (2, 'Haro');
INSERT INTO brand (brand_id, brand_name) VALUES (3, 'Heller');
INSERT INTO brand (brand_id, brand_name) VALUES (4, 'Pure Cycles');
INSERT INTO brand (brand_id, brand_name) VALUES (5, 'Ritchey');
INSERT INTO brand (brand_id, brand_name) VALUES (6, 'Strider');
INSERT INTO brand (brand_id, brand_name) VALUES (7, 'Sun Bicycles');
INSERT INTO brand (brand_id, brand_name) VALUES (8, 'Surly');
INSERT INTO brand (brand_id, brand_name) VALUES (9, 'Trek');
 
-- product
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (1, 'Trek 820 - 2021', 9, 6, 2021, 379.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (2, 'Ritchey Timberwolf Frameset - 2021', 5, 6, 2021, 749.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (3, 'Surly Wednesday Frameset - 2021', 8, 6, 2021, 999.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (4, 'Trek Fuel EX 8 29 - 2021', 9, 6, 2021, 2899.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (5, 'Heller Shagamaw Frame - 2021', 3, 6, 2021, 1320.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (6, 'Surly Ice Cream Truck Frameset - 2021', 8, 6, 2021, 469.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (7, 'Trek Slash 8 27.5 - 2021', 9, 6, 2021, 3999.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (8, 'Trek Remedy 29 Carbon Frameset - 2021', 9, 6, 2021, 1799.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (9, 'Trek Conduit+ - 2021', 9, 5, 2021, 2999.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (10, 'Surly Straggler - 2021', 8, 4, 2021, 1549);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (11, 'Surly Straggler 650b - 2021', 8, 4, 2021, 1680.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (12, 'Electra Townie Original 21D - 2021', 1, 3, 2021, 549.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (13, 'Electra Cruiser 1 (24-Inch) - 2021', 1, 3, 2021, 269.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (14, 'Electra Girl''s Hawaii 1 (16-inch) - 2020/2021', 1, 3, 2021, 269.99);
INSERT INTO product (product_id, product_name, brand_id, category_id, model_year, list_price) VALUES (15, 'Electra Moto 1 - 2021', 1, 3, 2021, 529.99);
 
-- sale
INSERT INTO sale (order_id, product_id, quantity) VALUES (1, 3, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (2, 12, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (3, 1, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (4, 4, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (5, 8, 3);
INSERT INTO sale (order_id, product_id, quantity) VALUES (6, 9, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (7, 5, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (8, 6, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (9, 10, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (10, 1, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (11, 2, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (12, 7, 3);
INSERT INTO sale (order_id, product_id, quantity) VALUES (13, 8, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (14, 5, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (15, 11, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (16, 4, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (17, 2, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (18, 8, 1);
INSERT INTO sale (order_id, product_id, quantity) VALUES (19, 10, 2);
INSERT INTO sale (order_id, product_id, quantity) VALUES (20, 11, 1);

Group the data in the sale table at product_id level. Calculate the number of orders for each product and assign it to a column named number_of_orders. Then, using the HAVING clause, extract products with a number of orders greater than one. Sort the result in descending order by number_of_orders.

Solution

SELECT
  product_id,
  COUNT(*) AS number_of_orders
FROM
  sale
GROUP BY
  product_id
HAVING
  COUNT(*) > 1
ORDER BY
  number_of_orders DESC;