Today we will look at an example database and various commands for aggregation, grouping, sorting, joining tables and more using MySQL as an example. The data itself is a set of tables with arbitrary names and values. The structure of the tables and their relationships are presented below.
excel:
, , . .
: , , .
, ,
:
SELECT SUM(amount_of_orders) AS orders_per_year FROM year_statistics;
:
SELECT month_name, amount_of_orders
FROM year_statistics
ORDER BY amount_of_orders DESC;
, :
SELECT month_name, amount_of_orders FROM year_statistics
WHERE amount_of_orders = (SELECT MAX(amount_of_orders)
FROM year_statistics);
:
SELECT district
FROM customers
GROUP BY district
ORDER BY COUNT(district) DESC;
:
SELECT courier_id, COUNT(order_id)
From delivery_list
WHERE date_arrived IS NOT NULL
GROUP BY courier_id;
IN, EXISTS, UNION .
, "South":
SELECT * FROM Customers
WHERE district IN ('South');
, :
SELECT * FROM delivery_list
WHERE taken NOT IN ('Yes');
, :
SELECT menu_name FROM products
WHERE EXISTS
(SELECT * FROM orders_products
WHERE orders_products.product_id = products.product_id);
, :
SELECT menu_name FROM products
WHERE NOT EXISTS
(SELECT * FROM orders_products
WHERE orders_products.product_id = products.product_id);
:
SELECT 'Customer' AS category, first_name, last_name, phone_number
FROM customers
UNION
SELECT 'Employee' AS category, first_name, last_name, phone_number
FROM courier_info;
INNER, NATURAL, CROSS, LEFT JOIN
, (, , ). ROUND, :
SELECT orders_products.order_id, products.menu_name, quantity,
ROUND(price*quantity, 2) AS total_price
FROM orders_products
INNER JOIN products ON orders_products.product_id = products.product_id
ORDER BY order_id, quantity;
, , :
SELECT *, SEC_TO_TIME(TIMESTAMPDIFF(second, date_get, date_arrived))
AS time_of_delivery
FROM orders
NATURAL JOIN delivery_list;
, :
SELECT DISTINCT courier_info.courier_id, customers.district
FROM courier_info
CROSS JOIN customers WHERE courier_info.delivery_type = 'car'
ORDER BY courier_id;
, :
SELECT customers.first_name, customers.last_name,
customers.phone_number, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
As a result, we have analyzed a lot of useful SQL fetch queries. Basic and rarer operations were shown. In fact, it does not matter how much data in your table is ten or a thousand, this will not change the queries, but will always remain the same. The main thing is that the meaning is clear, the amount of data plays a much smaller role. It is absolutely stupid to be afraid and upset that you are not yet able to get the desired request. It is absolutely normal if you google, read a book on a topic of interest, and there is still no result. This can take from a dozen minutes to days. We are all human beings and one person cannot know everything. Be patient, ask your comrades, on the forums and just keep looking for yourself, you will succeed! Good luck.