練習(xí)1_sql語句答案

1、.習(xí)題1請根據(jù)給出的數(shù)據(jù)庫表結(jié)構(gòu)來回答相應(yīng)問題：DEPT (DEPTNO INT, DNAME VARCHAR(14),LOC VARCHAR(13);EMP (EMPNO INT,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE, SAL FLOAT, COMM FLOAT, DEPTNO INT);BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL INT, COMM INT);SALGRADE ( GRADE INT, LOSAL INT, HISAL INT);其中表中包含如下數(shù)據(jù)

2、：DEPT表：EMP表：SALGRADE表：BONUS表： 無數(shù)據(jù)根據(jù)上面描述完成下面問題：（注意：注意保存腳本，尤其是DDL和DML，以便進(jìn)行數(shù)據(jù)還原）DDL1. 寫出上述表的建表語句。此外，在DEPT上創(chuàng)建名為”PK_DEPT”的主鍵約束，在EMP表上創(chuàng)建名為” PK_EMP”的主鍵約束以及指向表DEPT的外鍵約束” FK_DEPTNO”。命令：create table dept(DEPTNO INT CONSTRAINT PK_DEPT primary key,DNAME varchar(14),LOC varchar(13)create table EMP(EMPNO INT CONS

3、TRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT,HIREDATE DATE,SAL FLOAT,COMM FLOAT,DEPTNO INT CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES dbo.dept(DEPTNO)create table BONUS(ENAME VARCHAR(10),JOB VARCHAR(9),SAL INT,COMM INT)create table SALGRADE(GRADE INT,LOSAL INT,HISAL INT)DML2.

4、給出相應(yīng)的INSERT語句來完成題中給出數(shù)據(jù)的插入。命令：insert into SALGRADE (GRADE,LOSAL,HISAL)VALUES(1,1,1)3. 將所有員工的工資上浮10%.然后查詢員工姓名、薪水、補助。（emp.sal為工資，m為補助）命令：update emp set sal=sal*1.1Select ename,sal,comm from emp結(jié)果：單表查詢4. 查看EMP表中部門號為10的員工的姓名，職位，參加工作時間，工資。命令：select ename,job,hiredate,sal from emp where deptno=10結(jié)果：

5、5. 查所有已有的職位，要求去除重復(fù)項。命令： SELECT DISTINCT JOB FROM EMP結(jié)果：6. 計算每個員工的年薪，并取列名為Salary of Year（emp.sal為員工的月薪），要求輸出員工姓名，年薪。命令：select ename,sal*12 as salary of year from emp結(jié)果：7. 查詢每個員工每個月拿到的總金額（emp.sal為工資，m為補助）。（提示：isnull（ex1,ex2）表示如果ex1為空則返回ex2）命令：select ename,isnull(sal+comm,sal) as total from emp結(jié)

6、果：8. 顯示職位是主管（manager）的員工的姓名，工資。命令：select ename,sal from emp where job=manager結(jié)果：9. 顯示第3個字符為大寫O的所有員工的姓名及工資。命令：select ename,sal from emp where ename like _O%結(jié)果：10. 顯示職位為銷售員（SALESMAN）或主管（MANAGER）的員工的姓名，工資，職位。命令：select ename,sal,job from emp where job=salesman or job=manager結(jié)果：11. 顯示所有沒有補助的員工的姓名。命令：sele

7、ct ename from emp where comm is null結(jié)果：12. 顯示有補助的員工的姓名，工資，補助。命令：select ename from emp where comm is not null結(jié)果：13. 排序顯示所有員工的姓名，工資（按工資降序方式）。命令：select ename,sal from emp order by sal desc結(jié)果：14. 顯示員工的最高工資和最低工資。命令:select MAX(sal) as 最高工資,MIN(sal) as 最低工資 from emp結(jié)果：15. 顯示所有員工的平均工資和總計工資。命令：select AVG(sal

8、) as 平均工資, SUM(sal) as 總計工資 from emp結(jié)果：16. 顯示補助在員工中的發(fā)放比例、即有多少比例的員工有補助。（此題需注意兩個問題：1.select語句中進(jìn)行除法如何保留小數(shù)點后數(shù)據(jù)。2.count函數(shù)如何處理null型數(shù)據(jù)。）命令：select cast(count(comm)*1.0/count(*) as decimal(18,2) from emp結(jié)果：聚合查詢17. 顯示每種職業(yè)的平均工資。命令：select job,AVG(sal) averager from emp group by job結(jié)果：18. 顯示每個部門每種崗位的平均工資和最高工資。命令

9、：select deptno,job,AVG(sal) average,MAX(sal) max from emp group by job,deptno結(jié)果：19. 顯示平均工資低于2500的部門號，平均工資及最高工資。命令：select deptno,AVG(sal) as average,MAX(sal) max from emp group by deptno having avg(sal)2500結(jié)果：20. 上一條語句以平均工資升序排序。命令：select deptno,AVG(sal) as average,MAX(sal) max from emp group by deptn

10、o having avg(sal)2500 or job=manager結(jié)果： 22. 排序顯示所有員工的姓名，部門號，工資（以部門號升序，工資降序，雇用日期升序顯示）。命令：select ename,deptno,sal from emp order by deptno,sal desc,hiredate結(jié)果：23. 采用自然連接原理顯示部門名以及相應(yīng)的員工姓名。（Sql server不支持NATURAL JOIN語法。）命令：select dname,ename from emp inner join dept on emp.deptno=dept.deptno結(jié)果：24. 查詢SCOTT

11、的上級領(lǐng)導(dǎo)的姓名。命令：select emp2.ename from emp emp1 inner join emp emp2 on emp1.mgr=emp2.empno where emp1.ename=scott結(jié)果：25. 顯示部門的部門名稱，員工名即使部門沒有員工也顯示部門名稱。命令：select dname,ename from emp full join dept on emp.deptno=dept.deptno結(jié)果：子查詢26. 顯示所有員工的名稱、工資以及工資級別。命令：（連接方法）select ename,sal,grade from emp left join salg

12、rade on emp.sal=salgrade.losal and emp.sal(select max(SAL) from EMP where DEPTNO=30)結(jié)果：集合查詢30. 顯示工資高于2500或職位為MANAGER的員工的姓名，工資和職位（采用UNION語法實現(xiàn)）。命令：select ename,SAL,job from EMP where SAL2500 union select ename,SAL,job from EMP where JOB=manager結(jié)果：31. 顯示工資高于2500且職位為MANAGER的員工的姓名，工資和職位（采用INTERSECT語法實現(xiàn)）。命令：select ename,SAL,job from EMP where SAL2500 intersect select ename,SAL