• Sqlite3,维基百科中的练习:


    两个相连的表格

    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

     Employees.png

     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
      );
  • 相关阅读:
    android 布局中的单位及分辨率自解
    7种例子讲解Android Dialog!
    jqDnR 层拖动插件 潇湘博客
    jQuery选择器热榜
    左边补0 php 潇湘博客
    Javascript代码压缩、加密算法的破解分析及工具实现
    discuz 整合总结
    js 格式化 潇湘博客
    Linux内核网络协议栈深入分析(五)套接字的绑定、监听、连接和断开
    Linux内核基于Netfilter的内核级包过滤防火墙实现
  • 原文地址:https://www.cnblogs.com/chentianwei/p/8158005.html
Copyright © 2020-2023  润新知