《数据库》实验二SQL查询代码

)
)
)
);
Q23. List the names of managers who have at least one dependent.
查询至少有一个家属的部门经理姓名。
SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SSN IN (
ORDER BY FNAME ASC;
Q2. 查询不重复的员工工资值。
SELECT DISTINCT SALARY FROM EMPLOYEE
Q3. 查询没有直接上司的员工姓名。
SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SUPERSSN IS NULL;
查询至少参与了所有John Smith参与项目的员工姓名。
SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SSN IN (
SELECT ESSN FROM WORKS_ON
WHERE PNO=ANY (
SELECT PNUMBER FROM PROJECT
Q16. Select all combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the Company database.
查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。
SELECT E.SSN,D.DNAME
WHERE SALARY>=30000 GROUP BY D.DNAME
HAVING COUNT(SALARY)>=2;
Q18. Retrieve the names of all employees who do not work on any project controlled by department number 5.
对于每个部门，列出部门名称以及此部门员工的平均工资。
SELECT D.DNAME,AVG(SALARY)AS 平均工资
FROM EMPLOYEE E JOIN DEPARTMENT D ON D.DNUMBER=E.DNO
GROUP BY D.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 MGRSSN FROM DEPARTMENT
WHERE MGRSSN IN (
SELECT ESSN FROM DEPENDENT
)
);
Q24. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。
SELECT DNAME,
WHERE PNUMBER IN (
SELECT PNO FROM WORKS_ON
WHERE ESSN IN (
SELECT SSN FROM OYEE
WHERE FNAME='JOHN' AND LNAME='SMITH'
Q8. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。SELECT FNAME AS 名,LNAME AS 姓,
SSN AS 社会保险号,SALARY*8 AS 人民币工资
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DNO=D.DNUMBER;
Q17. 查询有两个或以上“工资大于等于30000员工”的部门名称。
SELECT D.DNAME
FROM EMPLOYEE E JOIN DEPARTMENT D ON D.DNUMBER=E.DNO
对于每个员工，查询其姓名以及他/她的直接上司的姓名。
SELECT EE.FNAME+'`'+EE.LNAME AS 员工姓名,
ES.FNAME+'`'+ES.LNAME AS 直接上司
FROM EMPLOYEE EE JOIN EMPLOYEE ES ON EE.SUPERSSN=ES.SSN;
WHERE SSN IN (
SELECT ESSN FROM WORKS_ON
WHERE PNO=ANY (
SELECT PNUMBER FROM PROJECT
WHERE DNUM=4
)
);
Q22. Retrieve the names of employees who work on all the projects that ‘John Smith’ works on.
SELECT PNAME,SUM(HOURS) AS "此项目总时间"
FROM PROJECT P JOIN WORKS_ON W ON P.PNUMBER=W.PNO
GROUP BY PNAME
Q14.For each department, retrieve the department name and the average salary of all employees working in that department.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO=(
SELECT DNO FROM DEPARTMENT
WHERE DNAME= ’Research’
)
Q10. 对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。SELECT P.PNUMBER, P.DNUM, E.LNAME, E.ADDRESS, E.BDATE
FROM PROJECT P JOIN WORKS_ON W ON P.PNUMBER=W.PNO
JOIN EMPLOYEE E ON W.ESSN=E.SSN
WHERE P.PNAME='PRODUCTX'
AND W.HOURS>10 AND E.DNO='5';
Q13. 对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。
Q6. 从Employee表中查询出部门号为4或者5的员工资料。SELECT * FROM EMPLOYEE
WHERE DNO=4 OR DNO=5或
SELECT * FROM EMPLOYEE
WHERE DNO IN(4,5)
Q7. 从Employee表中查询出LNAME中含有字母o的员工资料。SELECT * FROM EMPLOYEE WHERE LNAME LIKE ‘%O%’
SELECT E.SSN FROM EMPLOYEE E
JOIN DEPENDENT D ON E.SSN=D.ESSN
GROUP BY E.SSN
HAVING COUNT(ESSN)>2;
Q12. 查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。 SELECT E.FNAME, SELECT E.FNAME,E.LNAME
(SELECT COUNT(*) FROM EMPLOYEE
WHERE SALARY>=30000 AND DNO=E.DNO)AS 总数
FROM EMPLOYEE E JOIN DEPARTMENT ON E.DNO=DNUMBER
GROUP BY DNAME,DNO HAVING COUNT(*)>=2;
SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SALARY>ALL (
SELECT SALARY FROM EMPLOYEE
WHERE DNO=5
);
Q20. Retrieve the names of all employees who work on every project.
查询参与了所有项目的员工姓名。
SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SSN IN (
SELECT ESSN FROM WORKS_ON
WHERE PNO=ALL (
SELECT PNAME FROM PROJECT
)
);
Q25. List the names of all employees with two or more dependents.
查询有两个或以上家属的员工姓名。
SELECT FNAME,LNAME FROM EMPLOYEE
Q21. Find the names of employees who work on all the projects controlled by department number 4.
查询至少参与了所有部门4控制项目的员工姓名。
SELECT FNAME,LNAME FROM EMPLOYEE
FROM PROJECT P JOIN DEPARTMENT D ON P.DNUM =D.NUMBER
JOIN EMPLOYEE E ON D.MGRSSN=E.SSN WHERE PLOCATION =’Stafford’
Q11.查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。