Sqlite3,维基百科中的练习：

https://en.wikibooks.org/wiki/SQL_Exercises/The_computer_store

两个相连的表格

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

 正确✅写法：

    select name,budget from Departments

where budget >

(

select avg(budget) from Departments

);

 

15. Select the name and last name of employees working for departments with second lowest budget.

 我的写法：

select name,lastname from Employees

where Department in

(

select  code from Departments order by budget limit 1 offset 1 

);

 答案：

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