We have split this collection of exercises on MYSQL into seven parts, this is the first one, then you will find the second part, the third part, the fourth part, the fifth part, the sixth part, seventh part, eighth part, and finally the ninth part these exercises are based on simple, advanced and finally complex MYSQL queries. These questions are based on the following three tables, Customer, Orders and OrderLine table.
Customer table
Orders table
OrderLine table
Download the file containing the SQL statements allowing the creation of these tables. (Click here)
Questions
1. Get the user with the first name “Ivan” and the password “password2”, considering that the encoding of the password is done with the Sha1 algorithm.
SELECT *
FROM `customer`
WHERE `firstname` = 'Ivan'
AND `password` = SHA1("password2");
MySQL MD5()In SQL language, the MD5() function encrypts a string into a 32 character hexadecimal integer. The output can be particularly useful to be used as…Read More
2. Get the list of all products that are present on several orders.
SELECT name, COUNT(*) AS nbr_items
FROM `orderLine`
GROUP BY name
HAVING nbr_items > 1
ORDER BY nbr_items DESC;
MySQL HAVINGHAVING clause is used in SELECT statement to specify filter conditions for a group of rows or aggregates. HAVING clause is often used with GROUP…Read More
3. Get the list of all the products that are present on several orders and add a column that lists the id of the associated orders.
SELECT name, COUNT(*) AS nbr_items , GROUP_CONCAT(`order_id`) AS order_list
FROM `orderLine`
GROUP BY name
HAVING nbr_items > 1
ORDER BY nbr_items DESC;
MySQL GROUP BYThe GROUP BY clause is used to group rows with the same values, and is used in SELECT statement with aggregation functions such as COUNT,…Read More
4. Store the total price within each order line, based on the unit price and quantity.
UPDATE `orderLine`
SET `total_price` = (`quantity` * `unit_price`);
MySQL UPDATEThe Update command is used to modify rows of a table. The Update command can be used to modify or update one or more fields…Read More
5. Get the total price for each order and the date associated with that order as well as the first and last name of the associated customer.
SELECT customer.firstname, customer.lastname, orders.purchase_date, order_id, SUM(total_price) AS order_price
FROM `orderLine`
LEFT JOIN orders ON orders.id = orderLine.order_id
LEFT JOIN customer ON customer.id = orders.customer_id
GROUP BY `order_id`;
MySQL Select SUM() with ExampleIn SQL language, the SUM() aggregation function allows calculating the total sum of a column containing numerical values. This function only works on columns of…Read MoreMySQL LEFT JOINIn the SQL language, the LEFT JOIN command (also called LEFT OUTER JOIN) is a type of join between 2 tables. It allows listing all…Read More
6. Store the total price of each order in the field named “order_total_price”.
UPDATE orders AS o
INNER JOIN
( SELECT order_id, SUM(total_price) AS total_p
FROM orderLine
GROUP BY order_id ) l
ON o.id = l.order_id
SET o.order_total_price = l.total_p;
MySQL INNER JOIN with ExamplesInner JOIN is used to return rows from two tables that meet a given condition. In MySQL, INNER JOIN selects all rows from two participating…Read More
7. Get the total price of all orders, for each month.
SELECT YEAR(`purchase_date`), MONTH(`purchase_date`), SUM(`order_total_price`)
FROM `orders`
GROUP BY YEAR(`purchase_date`), MONTH(`purchase_date`)
ORDER BY YEAR(`purchase_date`), MONTH(`purchase_date`);
How To Get Year From Date In MySQLIn SQL language the YEAR() function allows extracting a year from a date in YYYY-MM-DD format. Syntax: To use this function in a SQL…Read MoreHow To Get Month From Date In MySQLIn this tutorial, we are going to see how to get month from date in MySQL. In SQL language MONTH() function allows extracting month number…Read More
8. Get a list of the 10 customers who made the largest amount of orders, and get this total price for each customer.
SELECT customer.firstname, customer.lastname, SUM(order_total_price) AS order_amount
FROM `orders`
LEFT JOIN customer ON customer.id = orders.customer_id
GROUP BY orders.customer_id
ORDER BY order_amount DESC
LIMIT 3;
MySQL LIMITLIMIT keyword is used to limit the number of rows returned in the result of a query. It can be used in conjunction with SELECT,…Read More
9. Get the total price of orders for each date.
SELECT `purchase_date`, SUM(`order_total_price`)
FROM `orders`
GROUP BY `purchase_date`;
10. Add a column named “category” to the table containing the orders. This column will contain a numerical value.
ALTER TABLE `orders` ADD `category` TINYINT UNSIGNED NOT NULL AFTER `order_total_price`;
11. Enter the value of the category, according to the following rules:
“1” for orders under 200€.
“2” for orders between 200€ and 500€.
“3” for orders between 500€ and 1.000€.
“4” for orders over 1.000€.
UPDATE `orders`
SET `category` = (
CASE
WHEN order_total_price < 200 THEN 1
WHEN order_total_price < 500 THEN 2
WHEN order_total_price < 1000 THEN 3
ELSE 4
END );
12. Create a table called “category_order” which will contain the description of these categories.
CREATE TABLE `category_order` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
MySQL CREATE TABLEIn this tutorial, we are going to see how to create a MySQL table. CREATE TABLE statement is used to create a new table in…Read More
13. Insert the 4 descriptions of each category in the table previously created.
“1” for orders under 200€.
“2” for orders between 200€ and 500€.
“3” for orders between 500€ and 1.000€.
“4” for orders over 1.000€.
INSERT INTO `category_order` (`id`, `description`)
VALUES (1, 'Orders under 200€');
INSERT INTO `category_order` (`id`, `description`)
VALUES (2, 'Orders between 200€ and 500€');
INSERT INTO `category_order` (`id`, `description`)
VALUES (3, 'Orders between 500€ and 1.000€');
INSERT INTO `category_order` (`id`, `description`)
VALUES (4, 'Orders over 1.000€');
MySQL INSERT INTOIn this tutorial, we are going to see how to insert data with MySQL. To insert data into a MySQL table you need to use…Read More
14. Delete all orders (and orderLines) below January 7, 2022.
DELETE FROM `orderLine`
WHERE `order_id` IN (SELECT id FROM orders WHERE purchase_date < '2022-01-07');
DELETE FROM `orders` WHERE purchase_date < '2022-01-07';
MySQL DELETEDELETE command is used to delete rows from a database table. DELETE command can delete multiple rows from a table in a single query. Once…Read More
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More