转载

SQL入门练习（二）

表结构

SQL入门练习（二）

创建测试数据

CREATE TABLE Departments (   Code INTEGER PRIMARY KEY,   Name varchar(255) NOT NULL ,   Budget decimal NOT NULL  );  CREATE TABLE Employees (   SSN INTEGER PRIMARY KEY,   Name varchar(255) NOT NULL ,   LastName varchar(255) NOT NULL ,   Department INTEGER NOT NULL ,    foreign key (department) references Departments(Code)  ) ENGINE=INNODB;  INSERT INTO Departments(Code,Name,Budget) VALUES(14,'IT',65000); INSERT INTO Departments(Code,Name,Budget) VALUES(37,'Accounting',15000); INSERT INTO Departments(Code,Name,Budget) VALUES(59,'Human Resources',240000); INSERT INTO Departments(Code,Name,Budget) VALUES(77,'Research',55000);  INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('123234877','Michael','Rogers',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('152934485','Anand','Manikutty',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('222364883','Carol','Smith',37); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('326587417','Joe','Stevens',37); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332154719','Mary-Anne','Foster',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332569843','George','ODonnell',77); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('546523478','John','Doe',59); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('631231482','David','Smith',77); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('654873219','Zacary','Efron',59); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('745685214','Eric','Goldsmith',59); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657245','Elizabeth','Doe',14); INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657246','Kumar','Swamy',14);

CREATETABLEDepartments (   CodeINTEGER PRIMARYKEY,   Namevarchar(255) NOT NULL ,   BudgetdecimalNOT NULL  );   CREATETABLEEmployees (   SSNINTEGER PRIMARYKEY,   Namevarchar(255) NOT NULL ,   LastNamevarchar(255) NOT NULL ,   DepartmentINTEGER NOT NULL ,    foreignkey (department) referencesDepartments(Code)  ) ENGINE=INNODB;   INSERTINTODepartments(Code,Name,Budget) VALUES(14,'IT',65000); INSERTINTODepartments(Code,Name,Budget) VALUES(37,'Accounting',15000); INSERTINTODepartments(Code,Name,Budget) VALUES(59,'Human Resources',240000); INSERTINTODepartments(Code,Name,Budget) VALUES(77,'Research',55000);   INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('123234877','Michael','Rogers',14); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('152934485','Anand','Manikutty',14); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('222364883','Carol','Smith',37); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('326587417','Joe','Stevens',37); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('332154719','Mary-Anne','Foster',14); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('332569843','George','ODonnell',77); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('546523478','John','Doe',59); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('631231482','David','Smith',77); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('654873219','Zacary','Efron',59); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('745685214','Eric','Goldsmith',59); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('845657245','Elizabeth','Doe',14); INSERTINTOEmployees(SSN,Name,LastName,Department) VALUES('845657246','Kumar','Swamy',14);

使用场景

-- 2.1 Select the last name of all employees. select LastName from Employees;

-- 2.2 Select the last name of all employees, without duplicates. select distinct LastName from employees;

-- 2.3 Select all the data of employees whose last name is "Smith". select * from employees where lastname = 'Smith';

-- 2.4 Select all the data of employees whose last name is "Smith" or "Doe". select * from Employees where lastname in ('Smith', 'Doe'); select * from Employees where lastname = 'Smith' or lastname = 'Doe';

-- 2.5 Select all the data of employees that work in department 14. select * from Employees where department = 14;

-- 2.6 Select all the data of employees that work in department 37 or department 77. select * from employees where department = 37 or department = 77; select * from employees where department in (37, 77);

-- 2.7 Select all the data of employees whose last name begins with an "S". select * from employees where LastName like 'S%';

-- 2.8 Select the sum of all the departments' budgets. select sum(budget) from Departments;

select Name, sum(Budget) from Departments group by Name;

-- 2.9 Select the number of employees in each department (you only need to show the department code and the number of employees). select Department, count(*) from employees group by department;

SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

-- 2.10 Select all the data of employees, including each employee's department's data. select a. , b. from employees a join departments b on a.department = b.code;

SELECT SSN, E.Name AS Name E, LastName, D.Name AS Name D, Department, Code, Budget FROM Employees E INNER JOIN Departments D ON E.Department = D.Code;

-- 2.11 Select the name and last name of each employee, along with the name and budget of the employee's department. select a.name, a.lastname, b.name Department_name, b.Budget from employees a join departments b on a.department = b.code;

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

-- 2.12 Select the name and last name of employees working for departments with a budget greater than $60,000. select name, lastname from employees where department in ( select code from departments where Budget>60000 );

/ Without subquery / SELECT Employees.Name, LastName FROM Employees INNER JOIN Departments ON Employees.Department = Departments.Code AND Departments.Budget > 60000;

/ With subquery / SELECT Name, LastName FROM Employees WHERE Department IN (SELECT Code FROM Departments WHERE Budget > 60000);

-- 2.13 Select the departments with a budget larger than the average budget of all the departments. select * from departments where budget > ( select avg(budget) from departments );

SELECT * FROM Departments WHERE Budget > ( SELECT AVG(Budget) FROM Departments );

-- 2.14 -- Select the names of departments with more than two employees. select b.name from departments b where code in ( select department from employees group by department having count(*)>2 );

/ With subquery / SELECT Name FROM Departments WHERE Code IN ( SELECT Department FROM Employees GROUP BY Department HAVING COUNT(*) > 2 );

/ With UNION. This assumes that no two departments have the same name / SELECT Departments.Name FROM Employees INNER JOIN Departments ON Department = Code GROUP BY Departments.Name HAVING COUNT(*) > 2;

-- 2.15 -- Very Important -- Select the name and last name of employees working for departments with second lowest budget.

select name, lastname from employees where department =( select temp.code from (select * from departments order by budget limit 2) temp order by temp.budget desc limit 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);

-- 2.16 -- Add a new department called "Quality Assurance", with a budget of $40,000 and departmental code 11. -- Add an employee called "Mary Moore" in that department, with SSN 847-21-9811. insert into departments values(11, 'Quality Assurnce', 40000); insert into employees values(847219811, 'Mary', 'Moore', 11);

-- 2.17 -- Reduce the budget of all departments by 10%. update departments set budget = 0.9 * budget;

-- 2.18 -- Reassign all employees from the Research department (code 77) to the IT department (code 14). update employees set department = 14 where department = 77;

-- 2.19 -- Delete from the table all employees in the IT department (code 14).

delete from employees where department = 14;

-- 2.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 );

-- 2.21 -- Delete from the table all employees. delete from employees;

-- 2.1 Selectthelastnameofallemployees. selectLastNamefromEmployees;     -- 2.2 Selectthelastnameofallemployees, withoutduplicates. selectdistinctLastNamefromemployees;     -- 2.3 Selectallthedataofemployeeswhoselastnameis "Smith". select * fromemployeeswherelastname = 'Smith';     -- 2.4 Selectallthedataofemployeeswhoselastnameis "Smith" or "Doe". select * fromEmployeeswherelastnamein ('Smith', 'Doe'); select * fromEmployeeswherelastname = 'Smith' or lastname = 'Doe';     -- 2.5 Selectallthedataofemployeesthatworkin department 14. select * fromEmployeeswheredepartment = 14;   -- 2.6 Selectallthedataofemployeesthatworkin department 37 or department 77. select * fromemployeeswheredepartment = 37 or department = 77; select * fromemployeeswheredepartmentin (37, 77);     -- 2.7 Selectallthedataofemployeeswhoselastnamebeginswithan "S". select * fromemployeeswhereLastNamelike 'S%';     -- 2.8 Selectthesumofallthedepartments' budgets. select sum(budget) from Departments;   select Name, sum(Budget) from Departments group by Name;     -- 2.9 Select the number of employees in each department (you only need to show the department code and the number of employees). select Department, count(*) from employees group by department;   SELECT Department, COUNT(*)   FROM Employees   GROUP BY Department;     -- 2.10 Select all the data of employees, including each employee's department's data. select a.*, b.* from employees a join departments b on a.department = b.code;   SELECT SSN, E.Name AS Name_E, LastName, D.Name AS Name_D, Department, Code, Budget FROM Employees E INNER JOIN Departments D ON E.Department = D.Code;     -- 2.11 Select the name and last name of each employee, along with the name and budget of the employee's department. select a.name, a.lastname, b.nameDepartment_name, b.Budget fromemployees a joindepartments b on a.department = b.code;   /* Without labels */ SELECTEmployees.Name, LastName, Departments.NameAS DepartmentsName, Budget   FROMEmployeesINNERJOINDepartments   ONEmployees.Department = Departments.Code;   /* With labels */ SELECT E.Name, LastName, D.NameAS DepartmentsName, Budget   FROMEmployees E INNERJOINDepartments D   ON E.Department = D.Code;     -- 2.12 Selectthenameand lastnameofemployeesworkingfor departmentswith a budgetgreaterthan $60,000. selectname, lastname fromemployees wheredepartmentin ( selectcodefromdepartmentswhereBudget>60000 );   /* Without subquery */ SELECTEmployees.Name, LastName   FROMEmployeesINNERJOINDepartments   ONEmployees.Department = Departments.Code     AND Departments.Budget > 60000;   /* With subquery */ SELECTName, LastNameFROMEmployees   WHEREDepartmentIN   (SELECTCodeFROMDepartmentsWHEREBudget > 60000);     -- 2.13 Selectthedepartmentswith a budgetlargerthantheaveragebudgetofallthedepartments. select * fromdepartments wherebudget > ( selectavg(budget) fromdepartments );   SELECT *   FROMDepartments   WHEREBudget >   (     SELECTAVG(Budget)     FROMDepartments   );     -- 2.14  -- Selectthenamesofdepartmentswithmorethantwoemployees. select b.name fromdepartments b wherecodein ( selectdepartment fromemployees groupbydepartment havingcount(*)>2 );   /* With subquery */ SELECTNameFROMDepartments   WHERECodeIN   (     SELECTDepartment       FROMEmployees       GROUPBYDepartment       HAVINGCOUNT(*) > 2   );   /* With UNION. This assumes that no two departments have    the same name */ SELECTDepartments.Name   FROMEmployeesINNERJOINDepartments   ONDepartment = Code   GROUPBYDepartments.Name   HAVINGCOUNT(*) > 2;     -- 2.15 -- VeryImportant -- Selectthenameand lastnameofemployeesworkingfor departmentswithsecondlowestbudget.   selectname, lastname fromemployees wheredepartment =( selecttemp.code from (select * fromdepartmentsorderbybudgetlimit 2) temp orderbytemp.budgetdesclimit 1 );     /* With subquery */ SELECT e.Name, e.LastName FROMEmployees e  WHERE e.Department = (       SELECTsub.Code       FROM (SELECT * FROMDepartments d ORDERBY d.budgetLIMIT 2) sub       ORDERBYbudgetDESCLIMIT 1);          -- 2.16 -- Add a new departmentcalled "Quality Assurance", with a budgetof $40,000 and departmentalcode 11.  -- Addanemployeecalled "Mary Moore" in thatdepartment, withSSN 847-21-9811. insertintodepartmentsvalues(11, 'Quality Assurnce', 40000); insertintoemployeesvalues(847219811, 'Mary', 'Moore', 11);     -- 2.17 -- Reducethebudgetofalldepartmentsby 10%. updatedepartments setbudget = 0.9 * budget;     -- 2.18 -- ReassignallemployeesfromtheResearchdepartment (code 77) to theITdepartment (code 14). updateemployees setdepartment = 14 wheredepartment = 77;   -- 2.19 -- Deletefromthetableallemployeesin theITdepartment (code 14).   deletefromemployees wheredepartment = 14;   -- 2.20 -- Deletefromthetableallemployeeswhoworkin departmentswith a budgetgreaterthanor equalto $60,000. deletefromemployees wheredepartmentin ( selectcode fromdepartments wherebudget>=60000 );     -- 2.21 -- Deletefromthetableallemployees. deletefromemployees;

SQL入门练习（二）

原文 https://xiequan.info/sql入门练习（二）/

正文到此结束