MySQL Practice Exercises with Solutions – Ordering System Database – Part 9

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

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

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

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

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 More MySQL 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

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

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 More How 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

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

SELECT `purchase_date`, SUM(`order_total_price`) 
FROM `orders` 
GROUP BY `purchase_date`;

ALTER TABLE `orders` ADD `category` TINYINT UNSIGNED NOT NULL AFTER `order_total_price`;

How to Add a Column in a Table in MySQLIn this tutorial, we are going to see how to add a column in a table in MySQL. The ALTER TABLE statement is used to…Read More

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 );

CASE WHEN in MySQL with Multiple ConditionsIn this tutorial, we are going to see how to use CASE expression in MySQL with syntax and examples. CASE expression allows you to add…Read More

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

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

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