Here is a challenge to your new SQL skills and knowledge you gained after watching the SQL course.

Compare your solutions with the answers provided in the next lecture, but only after completing the tasks!

####################### ASSESSMENTS ########################

-----------------------------------------------------------------------------------

Assessment 1 - Querying Data

Retrieve ALL data and fields from orders.
Retrieve only order date date and quantity of all orders.
List all salaries of all employees without duplicates.
Retrieve all orders where the result is sorted by quantity (smallest first).
Retrieve all employees, sorting the result by salary (highest first) and then by country (alphabetically).

-----------------------------------------------------------------------------------

Assessment 2 - Filtering Data

Find all employees working in UK
Find all orders where quantity is higher than 450
Find all employees who do not work in UK
Find all orders where quantity is less or equal to 1000 and ordered before 2021-07-01
Find all orders placed from customers whose ID equal to 1 or orders with quantity equal to 500
Find all orders placed between 2021-01-01 and 2021-07-01
Find all orders placed from customers whose ID equal to either 1,2 or 3
Find all employees whose last name begins with 'S'
Find all employees whose last name ends with 'l'
Find all employees whose country contains the letter 'a'
Find all employees whose country does not starts with the letter 'U' and their last name contains 'r' at the 4d position.

-----------------------------------------------------------------------------------

Assessment 3 - Joining Data

Select all orders with customer information.
Select all information about customers and show any orders they might have (display only the order id).
Select all customers who have placed orders with quantity less than 700.
Select all customers who have not placed an order.

-----------------------------------------------------------------------------------

Assessment 4 - SQL Functions

Find the lowest salary of all employees.
Find the total number of orders.
Find the average of the quantity of all orders.
Find out how many employees from the country UK.
Find the highest quantity ordered.
List the last name of employees in lowercase.
Find the length of the country column for all employees.
Subtract the first 3 characters from the country column of all employees.

-----------------------------------------------------------------------------------

Assessment 5 - SQL Advanced Topics

List all countries along with the total salaries paid for each of them.
List the number of employees for each country in the descending order.
Find the countries where less than two workers are employed and order the result by country (alphabetically).
Find all orders from customers whose country is Germany using SQL Subquery.

-----------------------------------------------------------------------------------

Assessment 6 - Modifying Data

Insert new order from customer with ID 5, ordered on 2023-02-11.
Change the quantity of the order with ID 1004 from 750 to 800.
Delete all orders that were ordered in 2023-01-01.

-----------------------------------------------------------------------------------

Assessment 7 - Defining Data

Create new SQL table called Products with 3 columns: Product ID, Product Name, and Category. The Product ID should be the primary Key. And at the end insert 3 products in the table.
Add new column called Product Price.
Drop the new table Products from our database.

######################## SOLUTIONS #########################

MAKE SURE YOU WORK OUT THE PROBLEMS ON YOUR OWN BEFORE MOVING ON TO MY SOLUTION!!

-----------------------------------------------------------------------------------

Solutions to Assessment 1 - Querying Data

Solution of Task1:

SELECT * FROM orders

Solution of Task2:

SELECT order_date, quantity FROM orders

Solution of Task3:

SELECT DISTINCT salary FROM employees

Solution of Task4:

SELECT * FROM orders

ORDER BY quantity

Solution of Task5:

SELECT * FROM employees

ORDER BY salary DESC, emp_country

-----------------------------------------------------------------------------------

Solutions to Assessment 2 - Filtering Data

Solution of Task1:

SELECT * FROM employees

WHERE emp_country = 'UK'

Solution of Task2:

SELECT * FROM orders

WHERE quantity > 450

Solution of Task3:

SELECT * FROM employees

WHERE NOT emp_country = 'UK'

-- It could be queried like next, both are correct:

SELECT * FROM employees

WHERE emp_country != 'UK'

Solution of Task4:

SELECT * FROM orders

WHERE quantity

Solution of Task5:

SELECT * FROM orders

WHERE customer_id = 1 OR quantity = 500

Solution of Task6:

SELECT * FROM orders

WHERE order_date BETWEEN '2021-01-01' AND '2021-07-01'

-- It could be queried like next, both are correct:

SELECT * FROM orders

WHERE order_date >= '2021-01-01' AND order_date

Solution of Task7:

SELECT * FROM orders

WHERE customer_id IN (1,2,3)

Solution of Task8:

SELECT * FROM employees

WHERE last_name LIKE 'S%'

Solution of Task9:

SELECT * FROM employees

WHERE last_name LIKE '%l'

Solution of Task10:

SELECT * FROM employees

WHERE emp_country LIKE '%a%'

Solution of Task11:

SELECT * FROM employees

WHERE emp_country NOT LIKE 'U%' AND last_name LIKE '___r%'

-----------------------------------------------------------------------------------

Solutions to Assessment 3 - Joining Data

Solution of Task1:

SELECT

*

FROM customers c

INNER JOIN orders o

ON c.customer_id = o.customer_id

Solution of Task2:

SELECT

c.*,

o.order_id

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id

Solution of Task3:

SELECT

*

FROM customers c

INNER JOIN orders o

ON c.customer_id = o.customer_id

WHERE o.quantity

Solution of Task4:

SELECT

c.*

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL

-- It could be queried like next, both are correct:

SELECT

c.*

FROM orders o

RIGHT JOIN customers c

ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL

-----------------------------------------------------------------------------------

Solutions to Assessment 4 - SQL Functions

Solution of Task1:

SELECT MIN(salary) FROM employees

Solution of Task2:

SELECT COUNT(*) FROM orders

Solution of Task3:

SELECT AVG(quantity) FROM orders

Solution of Task4:

SELECT COUNT(*) FROM employees

WHERE emp_country = 'UK'

Solution of Task5:

SELECT MAX(quantity) FROM orders

Solution of Task6:

SELECT LOWER(last_name) FROM employees

Solution of Task7:

SELECT emp_country, LENGTH(emp_country) FROM employees

Solution of Task8:

SELECT emp_country, SUBSTRING(emp_country,1,3) AS sub_country

FROM employees

-----------------------------------------------------------------------------------

Solutions to Assessment 5 - SQL Advanced Topics

Solution of Task1:

SELECT emp_country, SUM(salary) as Total_Salary

FROM employees

GROUP BY emp_country

Solution of Task2:

SELECT emp_country, count(*) as Total_Employees

FROM employees

GROUP BY emp_country

ORDER BY Total_Employees DESC

Solution of Task3:

SELECT emp_country, COUNT(*) as Total_Employees

FROM employees

GROUP BY emp_country

HAVING count(*)

ORDER BY emp_country

Solution of Task4:

SELECT *

FROM orders AS o

WHERE EXISTS (

SELECT 1

FROM customers AS c

WHERE c.customer_id = o.customer_id

AND country ='Germany')

-- It could be queried like next, both are correct:

SELECT *

FROM orders

WHERE customer_id IN (

SELECT customer_id

FROM customers

WHERE country ='Germany')

-----------------------------------------------------------------------------------

Solutions to Assessment 6 - Modifying Data

Solution of Task1:

INSERT INTO orders

(customer_id, order_date)

VALUES (5,'2023-02-11')

Solution of Task2:

UPDATE orders

SET quantity = 800

WHERE order_id = 1004

Solution of Task3:

DELETE FROM orders

WHERE order_date >= '2023-01-01'

You might get the following error in MySQL Workbench:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec

Thats because we are deleting a record without using the primary key, to change that:

Menu -> Edit -> Preferences -> SQL Editor -> unmark Safe Updates.

-----------------------------------------------------------------------------------

Solutions to Assessment 7 - Defining Data

Solution of Task1:

CREATE TABLE products (

Product_ID INT PRIMARY KEY AUTO_INCREMENT,

Product_name VARCHAR(50) NOT NULL,

Category VARCHAR(50) NOT NULL

);

INSERT INTO products VALUES (1, 'LG Monitor', 'Monitor');

INSERT INTO products VALUES (2, 'Razer Headset','Accessories');

INSERT INTO products VALUES (3, 'Samsung Monitor',' Monitor');

Solution of Task2:

ALTER TABLE products

ADD Product_Price INT(15) NOT NULL

Solution of Task3:

DROP TABLE products

Baraa Khatib Salkini

Lead Big Data, Cloud Architecture, Data

449

2

Company

Community

Teaching

Mobile

Company

Community

Teaching