针对于2021.5.17日晚的sqlserver数据库实验二的记录，仅供参考，如有抄袭行为与我无关
把USE Company_huanghe改成自己的就可以了。也可以不要这一句，对本次实验不影响。
另外提示一下，语句一定要自己去跑一下，理解一下。千万不要拿原图！图片是唯一的，数据库名是唯一的，直接拿原图的话，会出问题的。
1．单表查询
(1)基本查询。
Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。
代码/截图：
USE Company_huanghe select LNAME+FNAME as NAME from Employee
Q1. 使用查询分析器从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。
代码/截图：
USE Company_huanghe select FNAME AS 名,lname as 姓,SSN as 社会保险号,BDATE as 生日,SALARY as 工资 from Employee order by SALARY DESC,FNAME ASC
Q2. Retrieve all distinct salary values.
查询不重复的员工工资值。
代码/截图：
USE Company_huanghe select DISTINCT SALARY from Employee
Q3. Retrieve the names of all employees who do not have supervisors.
查询没有直接上司的员工姓名。
代码/截图：
USE Company_huanghe select LNAME+FNAME as NAME from Employee Where superssn is null
(2) 基于WHERE子句进行数据查询。
1）基于比较条件。
Q4. 从Employee表中查询出工资大于等于40000的员工资料。
代码/截图：
USE Company_huanghe select * FROM Employee WHERE SALARY>=40000
2）基于BETWEEN子句的查询。
Q5. 从Employee表中查询出1960年——1970年之间出生的员工资料。
代码/截图：
USE Company_huanghe SELECT*FROM EMPLOYEE WHERE Bdate BETWEEN'1960-01-01'AND'1970-01-01';
3）基于IN子句的查询。
Q6. 从Employee表中查询出部门号为4或者5的员工资料。
代码/截图：
USE Company_huanghe
select * FROM Employee
WHERE DNO IN('4' ,'5')
4）基于LIKE子句的查询。
Q7. 从Employee表中查询出LNAME中含有字母o的员工资料。
代码/截图：
USE Company_huanghe select * FROM Employee WHERE LNAME LIKE '%O%'
(3) 使用计算列查询。
Q8. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。
代码/截图：
USE Company_huanghe select FNAME AS 名,lname as 姓,SSN as 社会保险号,SALARY *8 as 人民币工资 from Employee
2． 多表连接查询(使用JOIN)
Q9. Retrieve the name and address of all employees who work for the ‘Research’ department.
查询所有为Research部门工作的员工姓名及地址。
代码/截图：
USE Company_huanghe SELECT ADDRESS,FNAME+LNAME AS NAME FROM Department INNER JOIN Employee on Department.DNUMBER=Employee.DNO where DNUMBER=5
Q10. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthdate.
对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。
代码/截图：
USE Company_huanghe SELECT PNUMBER,DNUM,LNAME,ADDRESS,BDATE FROM EMPLOYEE JOIN PROJECT ON (EMPLOYEE.DNO=PROJECT.DNUM) JOIN DEPT_LOCATIONS ON (PROJECT.DNUM=DEPT_LOCATIONS.DNUMBER) WHERE DLOCATION='Stafford';
Q11. List the names of all employees with two or more dependents.
查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。
代码/截图：
USE Company_huanghe SELECT SSN FROM EMPLOYEE JOIN DEPENDENT ON(EMPLOYEE.SSN=DEPENDENT.ESSN) GROUP BY SSN HAVING COUNT(SSN)>2;
[reply]
Q12. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project.
查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。
代码/截图：
USE Company_huanghe SELECT FNAME,LNAME FROM EMPLOYEE JOIN WORKS_ON ON(EMPLOYEE.SSN=WORKS_ON.ESSN) JOIN PROJECT ON (WORKS_ON.PNO=PROJECT.PNUMBER) WHERE PROJECT.PNAME='PRODUCTX'
Q13. For each project, list the project name and the total hours per week (by all employees) spent on that project.
对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。
代码/截图：
USE Company_huanghe SELECT PNAME,SUM(HOURS)AS 总时间 FROM WORKS_ON JOIN PROJECT ON (WORKS_ON.PNO=PROJECT.PNUMBER) GROUP BY PNAME;
Q14. For each department, retrieve the department name and the average salary of all employees working in that department.
对于每个部门，列出部门名称以及此部门员工的平均工资。
代码/截图：
USE Company_huanghe SELECT DNAME,AVG(SALARY)as 平均工资 FROM EMPLOYEE JOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER) GROUP BY DNAME;
Q15. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.
对于每个员工，查询其姓名以及他/她的直接上司的姓名。
代码/截图：
SELECT YG.FNAME+YG.LNAME as 员工姓名,SS.FNAME+SS.LNAME as 上司姓名 FROM EMPLOYEE YG LEFT JOIN EMPLOYEE SS ON YG.SUPERSSN=SS.SSN
Q16. Select all combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the Company database.
查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。
代码/截图：
USE Company_huanghe SELECT SSN,DNAME FROM EMPLOYEE JOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER)
Q17. 查询有两个或以上“工资大于等于30000员工”的部门名称。
代码/截图：
SELECT DNAME FROM EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER WHERE SALARY>=30000 GROUP BY DNO,DNAME HAVING COUNT(*)>=2
3．嵌套查询(子查询)
Q18. Retrieve the names of all employees who do not work on any project controlled by department number 5.
查询没有参与任何部门5控制项目的员工姓名。
代码/截图：
USE Company_huanghe SELECT LNAME+FNAME AS NAME FROM EMPLOYEE where DNO<>ALL (SELECT DNUMBER FROM DEPARTMENT WHERE DNUMBER=5)
Q19. Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5.
查询工资超过部门5所有员工工资的员工姓名。
代码/截图：
USE Company_huanghe SELECT LNAME+FNAME AS NAME FROM EMPLOYEE where SALARY>ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO=5)
Q20. Retrieve the names of all employees who work on every project.
查询参与了所有项目的员工姓名。（这里不是做错了，是没有参与所有项目的员工，所以才是空）
代码/截图：
USE Company_huanghe select FNAME+LNAME AS NAME FROM Employee WHERE SSN IN (SELECT ESSN FROM WORKS_ON GROUP BY ESSN HAVING COUNT(ESSN)=(SELECT COUNT(*) FROM Project))
Q21. Find the names of employees who work on all the projects controlled by department number 4.
查询至少参与了所有部门4控制项目的员工姓名。
代码/截图：
USE Company_huanghe select FNAME+LNAME AS NAME from Employee where SSN in( select distinct essn from Works_On where PNO in(select PNUMBER from Project where DNUM=4))
Q22. Retrieve the names of employees who work on all the projects that ‘John Smith’ works on.
查询至少参与了所有John Smith参与项目的员工姓名。
代码/截图：
USE Company_huanghe select FNAME+LNAME AS name from Employee where SSN in( select distinct ESSN from Works_On where PNO in( select pno from Works_On where ESSN=( select SSN from Employee where FNAME='John' and LNAME='Smith')) and ESSN<>(select SSN from Employee where FNAME='John' and LNAME='Smith'))
Q23. List the names of managers who have at least one dependent.
查询至少有一个家属的部门经理姓名。
代码/截图：
USE Company_huanghe SELECT FNAME,LNAME FROM EMPLOYEE WHERE SSN IN (SELECT MGRSSN FROM DEPARTMENT WHERE MGRSSN IN (SELECT ESSN FROM DEPENDENT))
Q24. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。
代码/截图：
USE Company_huanghe select Department.DNAME ,count(*) from Employee join Department on Employee.DNO=Department.DNUMBER where SALARY>=30000 group by Department.DNAME
Q25. List the names of all employees with two or more dependents.
查询有两个或以上家属的员工姓名。
代码/截图：
USE Company_huanghe SELECT LNAME+FNAME as 姓名 FROM EMPLOYEE WHERE SSN IN (SELECT ESSN FROM DEPENDENT GROUP BY ESSN HAVING COUNT(*)>=2)
4．集合查询
Q26. Make a list of project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
查询符合以下任意条件的项目编号：(1)参与此项目员工的LNAME是Smith；(2)控制此项目的部门经理的LNAME是Smith。
代码/截图：
USE Company_huanghe SELECT PNUMBER FROM PROJECT JOIN EMPLOYEE ON (PROJECT.DNUM=EMPLOYEE.DNO) WHERE LNAME='SMITH' UNION SELECT SSN FROM EMPLOYEE WHERE LNAME='SMITH'AND SSN IN (SELECT MGRSSN FROM PROJECT JOIN DEPARTMENT ON (PROJECT.DNUM=DEPARTMENT.DNUMBER))
[/reply]
演示到此结束。
