کمی تمرین SQL

در این نوشته قصد دارم به مروری سریع بر نکات SQL بپردازم. ابتدا دیتابیسی که در سایت w3schools برای بخش آموزش sql است را با کمک این ریپو تکمیل می‌کنیم. در نهایت اندکی مهارت گزارش گیری را تمرین و مرور می کنیم.

همچنین در یکسری سوالات مفهومی از sql نیز مطرح شده است.

لازم به ذکر است برای این نوشته از دیتابیس mysql استفاده می‌کنیم.

1. تفاوت دیتابیس های رابطه ای با غیر رابطه ای چیست و چه زمانی از آن ها استفاده می کنیم؟

2. تفاوت DDL، DML چیست؟

3. معنی ترنزکشن چیست؟

4. درباره ایندکس ها توضیح دهید و انواع آن ها را با هم مقایسه کنید.

5. چه زمانی می توان ادعا کنیم به سمت بیگ دیتا حرکت کرده ایم؟!

6. زمانی که رکوردی به دیتابیس اضافه می کنیم در پس زمینه به صورت کلی چه اتفاقی می افتد؟

7. تفاوت UNION با UNION ALL چیست؟

8. تفاوت All با Any و In چیست؟

9. چه زمانی از sub query ها استفاده می کنیم؟ چه زمانی استفاده نمی کنیم؟

10. تفاوت تابع با sp ها چیست؟

11. تفاوت jon ها با union چیست؟

12. چه زمانی از تریگر ها استفاده می کنیم یا نمی کنیم؟

13. از چه طریقی می توانیم بفهمیم داده ای در پایگاه داده آپدیت یا اینزرت شده است؟

14. بهترین راه ها برای پشتیبانی از پایگاه داده چه هستند؟

15. ایجاد DB:

CREATE DATABASE W3Schools;

16. ایجاد جدول categories :

CREATE TABLE categories (
  CategoryID int NOT NULL,
  CategoryName varchar(255) DEFAULT NULL,
  Description varchar(255) DEFAULT NULL
);

17. رکوردی به جدول categories اضافه می کنیم.

INSERT INTO categories (CategoryID, CategoryName, Description) VALUES (1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales');

18. رکورد ها را به صورت دسته ای اضافه کنید. (مزیت های این روش نسبت به روش قبل چیست؟)

INSERT INTO categories (CategoryID, CategoryName, Description) VALUES
(2, 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings'),
(3, 'Confections', 'Desserts, candies, and sweet breads'),
(4, 'Dairy Products', 'Cheeses'),
(5, 'Grains/Cereals', 'Breads, crackers, pasta, and cereal'),
(6, 'Meat/Poultry', 'Prepared meats'),
(7, 'Produce', 'Dried fruit and bean curd'),
(8, 'Seafood', 'Seaweed and fish');

الباقی جداول و رکورد ها را از اینجا ایجاد کنید.

تمرین کوئری نویسی:

19. بازیابی شهر مشتریان بدون نمایش شهر های تکراری:

SELECT DISTINCT Country FROM Customers;

20. کوئری بنویسید که تعداد شهر های unique را برگرداند.

SELECT count(DISTINCT Country) FROM Customers;

21. تعداد کاراکتر نام همه مشتریانی که حرف اول آن ها با کاراکتر a شروع شود و حرف آخر e باشد.

SELECT LENGTH(CustomerName) FROM Customers WHERE CustomerName LIKE 'a%e';

22. طولانی ترین نام مشتری را چه شخصی دارد؟

SELECT * FROM Customers where LENGTH(CustomerName) in (select max(LENGTH(CustomerName)) from Customers);

23. مشخصات نام، شهر و کشور مشتریان. مرتب سازی براساس شهر باشد. در صورتی که شهری برای مشتری ثبت نشده باشد، براساس نام کشور لحاظ شود.

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

24. تعداد فاکتور هایی که اقلام آن ها بیشتر از 30 مورد، مساوی 30 و کمتر از 30 را محاسبه کنید.

SELECT 
	sum(CASE WHEN Quantity > 30 THEN 1 else 0 end) as 'greater_than_30',
	sum(CASE WHEN Quantity = 30 THEN 1 else 0 end) as 'is_30',
    sum(CASE WHEN Quantity < 30 THEN 1 else 0 end) as 'under_30'
FROM order_details;


-- یا می توان به صورت زیر انجام داد

SELECT 'greater_than_30',
	sum(CASE WHEN Quantity > 30 THEN 1 else 0 end) as 'greater_than_30'
FROM order_details
UNION ALL
SELECT 'is_30',
	sum(CASE WHEN Quantity = 30 THEN 1 else 0 end) as 'is_30'
FROM order_details
UNION ALL
SELECT 'under_30',
    sum(CASE WHEN Quantity < 30 THEN 1 else 0 end) as 'under_30'
FROM order_details

25. لیست همه محصولاتی که تعداد فروش آن ها حداقل یکبار بیشتر از 90 بوده است.

SELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
  FROM order_details
  WHERE Quantity > 99);

26. لیست همه محصولاتی که هر بار به فروش رسیده دقیقا به مقدار 10 تا بوده است.

SELECT ProductName
FROM Products
WHERE ProductID = ALL
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);

27. جدول denorm شده دو جدول Orders و Customers ها را تولید کنید.

create table denorm as
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

28. لیستی از فروشندگان و ارسال کنندگان را تولید کنید.

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

29. گزارش مشتریانی که هر نوع سفارشی که تا به حال داشته اند.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

30. گزارش کارمندانی که هر نوع سفارشی که تا به حال داشته اند.

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

31. گزارشی که رفتار مشتریان را توصیف می‌کند.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

32. نام محصولاتی را بیابید که یا قیمتشان از 20 بیشتر باشد یا تاریخ سفارش آن ها از سال 1999 بالاتر باشد.

select p.ProductName
from order_details od
join orders o on o.OrderID = od.OrderID
join products p on p.ProductID = od.ProductID
where p.Price > 20 or year(o.OrderDate) > 1999
limit 100
;

33. گزارشی که تاریخ سفارش محصولات را به صورت سال، ماه و روز و همچنین ترکیب سال و ماه نمایش دهد.

select 
	OrderDate,
	SUBSTRING_INDEX(OrderDate, '-', 1) as year,
        SUBSTRING(OrderDate, 6, 2) as month,
        SUBSTRING_INDEX(OrderDate, '-', -1) as day,
        SUBSTRING_INDEX(OrderDate, '-', 2) as year_and_month
from orders limit 100;

34. گزارشی بنویسید که تعداد مشتریان را در هر کشور فهرست می‌کند، که از بالا به پایین مرتب شده‌اند (فقط شامل کشورهایی با بیش از 5 مشتری می‌شود):

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

امیدوارم این نوشته براتون مفید بوده باشه.✌