两个相连的表格
Manufactures: code, name
products: code, name, price, manufacturer
//黄色是关联。
15. Select the name and price of the cheapest product.
⚠️ :使用嵌套结构,这样能得到所有最便宜的价格的产品,产品价格如果有相同的话。
如果只是写子结构内的代码,只能返回一行。
SELECT Name, Price
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);
16. Select the name of each manufacturer along with the name and price of its most expensive product.不是非常理解;
SELECT A.Name, A.Price, F.Name FROM Products A INNER JOIN Manufacturers F ON A.Manufacturer = F.Code AND A.Price = ( SELECT MAX(A.Price) FROM Products A WHERE A.Manufacturer = F.Code );
https://en.wikibooks.org/wiki/SQL_Exercises/Employee_management
11.Select the name and last name of each employee, along with the name and budget of the employee's department
这道题可以分为带label和不带label的两个写法:
/* Without labels */ SELECT Employees.Name, LastName, Departments.Name AS DepartmentsName, Budget FROM Employees INNER JOIN Departments ON Employees.Department = Departments.Code; /* With labels */ SELECT E.Name, LastName, D.Name AS DepartmentsName, Budget FROM Employees E INNER JOIN Departments D ON E.Department = D.Code; //最关键的是在 join两侧定义label
12 Select the name and last name of employees working for departments with a budget greater than $60,000,用到in()
/* With subquery */ SELECT Name, LastName FROM Employees WHERE Department IN (SELECT Code FROM Departments WHERE Budget > 60000);
13. Select the departments with a budget larger than the average budget of all the departments.
错误❌写法:
select name,budget from Departments where budget > avg(budget);
报告错误:misuse aggregate function();
正确✅写法:
我的写法:
答案:
/* With subquery */
SELECT e.Name, e.LastName
FROM Employees e
WHERE e.Department = (
SELECT sub.Code
FROM (SELECT * FROM Departments d ORDER BY d.budget LIMIT 2) sub
ORDER BY budget DESC LIMIT 1);
17. Reduce the budget of all departments by 10%.
UPDATE Departments SET Budget = Budget * 0.9;
20. Delete from the table all employees who work in departments with a budget greater than or equal to $60,000.
DELETE FROM Employees
WHERE Department IN
(
SELECT Code FROM Departments
WHERE Budget >= 60000
);