CREATE TABLE EMP ( EMPNO numeric(5,0) NOT NULL primary key,--雇员的编号 ENAME nvarchar(10) not null,--雇员的名字 JOB nvarchar(9)not null,--雇员的的职位 MGR numeric(5,0),--上级主管编号 HIREDATE datetime,--入职(受雇)日期 SAL numeric(7, 2),--薪金； COMM numeric(7, 2),--佣金； DEPTNO numeric(2,0)--部门编号 ) CREATE TABLE DEPT ( DEPTNO numeric(2) primary key,--部门编号 DNAME nvarchar(14) not null,--部门名称 LOC部门所在地 nvarchar(13) --部门所在地 ); INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,'2000-12-17', 800, NULL, 20); INSERT INTO EMP VALUES (7499, 'allen', 'SALESMAN', 7698,'2001-2-20', 1600, 300, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,'2001-2-22', 1250, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,'2001-4-2', 2975, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,'2001-9-28',1250, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,'2001-5-1', 2850, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,'2001-6-9', 2450, NULL, 10); INSERT INTO EMP VALUES (7788, 'scott', 'ANALYST', 7566,'2002-12-9',3000, NULL, 20); INSERT INTO EMP VALUES (7839, 'king', 'PRESIDENT', NULL,'2001-11-17',5000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,'2001-9-8', 1500, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,'2003-1-12',1100, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,'2001-3-12',950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,'2001-3-12',3000, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,'2002-01-23',1300, NULL, 10); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); SELECT *FROM EMP --1、查询所有的雇员名字 SELECT ENAME FROM EMP --2、查询所有的职位 SELECT DISTINCT JOB FROM EMP --DISTINCT 隐藏重复的行 --3、查询没有佣金（COMM）的所有雇员信息 select * from EMP where COMM is null --4、查询薪金(SAL)和佣金(COMM)总数大于2000的所有雇员信息 select * from EMP where (SAL+COMM)>2000 --cuo SELECT * FROM EMP WHERE SAL+ISNULL(COMM,0)>2000 --提示：isnull(列名，0） ：如果该列中有空值，就把空值当做0做计算 --5、选择部门编号=30中的雇员 select * from EMP where EMPNO=30 --6、列出所有Job办事员（"CLERK"）的姓名、编号和部门名称 select EMP.ENAME,EMP.EMPNO,DEPT.DNAME from EMP left join Dept on EMP.DEPTNO=DEPT.DEPTNO where EMP.Job='CLERK' --6、列出所有Job办事员（"CLERK"）的姓名、编号和部门名称 SELECT ENAME,EMPNO,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='CLERK' --7、找出佣金高于薪金的雇员 select * from EMP where ISNULL(COMM,0)>SAL select * from EMP where COMM>SAL --8、找出佣金高于薪金的60%的雇员 select * from EMP where ISNULL(COMM,0)/(ISNULL(COMM,0)-SAL)>0.6 SELECT ENAME FROM EMP WHERE COMM>0.6 * SAL --9、找出部门10中所有经理和部门20中的所有办事员的详细资料 select * from EMP where (EMP.DEPTNO=10 and EMP.JOB='MANAGER') or (EMP.DEPTNO=20 and JOB='CLERK') SELECT *FROM EMP WHERE JOB='MANAGER' AND DEPTNO=10 OR JOB='CLERK' AND DEPTNO =20 --10、既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from EMP where job !='MANAGER' and job!='CLERK' and SAL>2000 --11、找出收取佣金的雇员的不同工作 --select a.JOB from EMP as a left join EMP as b on a.ENAME=b.ename where select DISTINCT job from emp where COMM is not null select * from emp where COMM is not null --如何查询某一张表某一字段重复次数，以及重复的字段 select count(*) as count,job from EMP Group by JOB HAVING count(*)>1 --12、找出不收取佣金或收取的佣金低于100的雇员 select * from emp where COMM is null or comm<100 --13、找出早于8年之前受雇的雇员 select * from emp where HIREDATE < DateAdd(yyyy,-8,getdate()) SELECT * FROM EMP WHERE GETDATE()-HIREDATE>8----例子错误 --14、显示首字母大写的所有雇员的姓名 --区分大小写:collate:指定排序规则的 --修改表,设置大小写是否敏感, chinese_prc_ci_as 不区分大小写 --区分大小写 chinese_prc_cs_as SELECT * FROM emp WHERE (ASCII(SUBSTRING(ENAME, 1, 1)) > 64) AND (ASCII(SUBSTRING(ENAME, 1, 1)) < 91) select * from emp where ENAME collate chinese_prc_cs_as_ws like '[A]%' --SELECT UPPER(SUBSTRING('aYAME',1,1))+LOWER(SUBSTRING('aYAME',2,( SELECT LEN('aYAME')))) --15、显示正好为5个字符的雇员姓名 select * from emp where LEN(ENAME)=5 --16、显示带有'R'的雇员姓名 不区分大小写 select * from emp where ENAME like '%R%' --17、显示不带有'R'的雇员姓名 select * from emp where ENAME not like '%R%' --18、显示包含"A"的所有雇员的姓名及"A"在姓名字段中的位置 --select emp.ENAME from emp where ENAME select ename,CHARINDEX('A',ENAME) from emp where ename like '%A%'; --19、显示所有雇员的姓名，用a替换所有'A' select REPLACE(ename,'A','a') as ename from emp --20、显示所有雇员的姓名的前三个字符 --1.left(name,4)截取左边的4个字符 --2.right(name,2)截取右边的2个字符 --3.SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取... --4.SUBSTRING(name,3) 截取name这个字段 从第三个字符开始,之后的所有个... --5.SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置(倒数).. select left(ename,3) as Ename from emp --21、显示雇员的详细资料，按姓名排序 select * from emp order by ENAME --22、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 select ename from emp order by HIREDATE Asc --23、显示所有雇员的姓名、工作和薪金，按工作降序顺序排序， --而工作相同的按薪金升序排序. select emp.ENAME,EMP.JOB,emp.SAL from emp order by job desc ,SAL ASC --如果第一个排序条件重复,则继续用第二个排序 --24、显示在一个月为30天的情况下所有雇员的日薪金,忽略小数 SELECT ENAME,SAL/30, CAST(SAL/30 AS INT) FROM EMP --25、找出在（任何年份的）2月受聘的所有雇员 SELECT ENAME,HIREDATE FROM EMP WHERE MONTH(HIREDATE)=2 --26、对于每个雇员，显示其加入公司的天数 --提示:datediff(day,hiredate,getdate()) --获取两个时间的差值.(单位可选) select ename, DATEDIFF(DAY,emp.HIREDATE,GETDATE()) as tianshu from EMP --27、列出至少有一个雇员的所有部门 select count(EMP.EMPNO),DEPT.DNAME from emp left join DEPT on EMP.DEPTNO=DEPT.DEPTNO GROUP BY DEPT.DNAME Having COUNT(EMP.EMPNO)IS NOT NULL SELECT DEPTNO,COUNT(EMPNO) 人数 FROM EMP a GROUP BY DEPTNO HAVING COUNT(EMPNO) IS NOT NULL --28、列出各种类别工作的最低工资 select JOB, MIN(sal) AS 最低工资 from emp GROUP BY JOB ORDER BY 最低工资 DESC SELECT JOB,MIN(SAL+ISNULL(COMM,0)) 最低工资 FROM EMP GROUP BY JOB --29、列出各个部门的MANAGER（经理）的最低薪金 select min(Sal) from emp where job='MANAGER' --所有的取最低值 SELECT DEPTNO 部门名称,MIN(SAL) 最低薪金 FROM EMP WHERE JOB='MANAGER' GROUP BY DEPTNO --分组之后 ,取组内最低值 --30、列出薪金高于公司平均水平的所有雇员 select * from emp Having SAL> AVG(SAL) --cuo SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL)FROM EMP) --31、列出各种工作类别的最低薪金，并显示最低薪金大于1500 --select job, min(SAL)>1500 from emp group by job 错误 SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB HAVING MIN(SAL)>1500 --32、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇 日所在月排序，将最早年份的项目排在最前面 select ename,emp.HIREDATE from emp SELECT ENAME,MONTH(HIREDATE) as 月,YEAR(HIREDATE) as 年 FROM EMP ORDER BY MONTH(HIREDATE) ,YEAR(HIREDATE)ASC --33、显示所有雇员的姓名以及满8年服务年限的日期 select emp.ENAME ,emp.HIREDATE from emp where DATEDIFF(YEAR,emp.HIREDATE,GETDATE())>8 --34、显示所有雇员的服务年限：总的年数或总的月数或总的天数 select *, DATEDIFF(YEAR,emp.HIREDATE,GETDATE()) As 年,DATEDIFF(MONTH,emp.HIREDATE,GETDATE()) As 月, DATEDIFF(DAY,emp.HIREDATE,GETDATE()) As 天 from emp --35、列出按计算的字段排序的所有雇员的年薪.即：按照年薪对雇 员进行排序,年薪指雇员每月的总收入总共12个月的累加 --select emp.ename, sum(sal) as 年薪 from emp where YEAR(EMP.HIREDATE)=2020 group by EMP.ENAME ling SELECT ENAME ,SAL*12 FROM EMP ORDER BY SAL*12 ASC --36、列出年薪前5名的雇员 SELECT TOP 5 ENAME, SAL*12 AS 年薪 FROM EMP ORDER BY SAL*12 DESC --37、列出年薪低于10000的雇员 select * ,(sal*12) as nianxin from emp where sal*12<10000 --38、列出雇员的平均月薪和平均年薪 select avg(sal)as 平均月薪, AVG(sal*12)as 平均年薪 from emp SELECT CAST( AVG(SAL)AS INT)AS 平均月薪,CAST( AVG(SAL*12)AS INT)AS 平均年薪 FROM EMP --39、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门 select d.DNAME,e.ENAME from dept d left join emp e on d.DEPTNO=e.DEPTNO SELECT DNAME,ENAME FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO --40、列出每个部门的信息以及该部门中雇员的数量 --select count(DEPTNO) from (select d.* from DEPT d left join emp e on e.DEPTNO=d.DEPTNO) group by d.DEPTNO cuowu select d.DEPTNO,DNAME,LOC部门所在地,count(d.DEPTNO)As 人数 from DEPT d left join emp e on e.DEPTNO=d.DEPTNO group by DNAME,LOC部门所在地,d.DEPTNO SELECT DEPT.DEPTNO,DNAME,LOC部门所在地,COUNT(EMPNO) 雇员数量 FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO GROUP BY DNAME,LOC部门所在地,DEPT.DEPTNO --41、列出薪金比"SMITH"多的所有雇员 select * from emp where SAL>(select sal from emp where ename='JONES') SELECT A.ENAME,A.SAL FROM EMP AS A,EMP AS B WHERE A.SAL>B.SAL AND B.ENAME='JONES'--自连接 当同一列的值相互之间进行比较时 ORDER BY A.SAL ASC --42、列出所有雇员的姓名及其直接上级的姓名(自连接) --select a.ename from emp a ,emp b where a.DEPTNO=b.DEPTNO and --select ename,DEPTNO from EMP where job='MANAGER' select a.ename ,b.ENAME from emp a ,emp b where a.MGR=b.EMPNO select a.ename ,b.ename from emp a left join emp b on a.MGR=b.EMPNO --43、列出入职日期早于其直接上级的所有雇员 select a.ename ,b.ename from emp a inner join emp b on a.MGR=b.EMPNO and a.HIREDATE<b.HIREDATE SELECT A.ENAME FROM EMP AS A,EMP AS B WHERE A.MGR=B.EMPNO AND A.HIREDATE<B.HIREDATE --44、列出所有办事员（"CLERK"）的姓名及其部门名称 select ename,dept.DNAME from emp inner join DEPT on EMP.JOB='CLERK' AND emp.DEPTNO=DEPT.DEPTNO SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.JOB='CLERK' --45、列出从事"SALES"（销售）工作的雇员的姓名，假定不知道 销售部的部门编号 select ename from DEPT join emp on DEPT.DNAME='SALES' and DEPT.DEPTNO=EMP.DEPTNO SELECT ENAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.DNAME='SALES' --46、列出与"SCOTT"从事相同工作的所有雇员 select b.ENAME from emp a inner join emp b on a.ename='SCOTT' and a.JOB=b.job --47、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30 中任何一个雇员的薪金 select ename,sal,deptno from emp where sal=any(select sal from emp where DEPTNO=30) --48、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30 中所有雇员的薪金 select ename,sal,deptno from emp where sal>all(select sal from emp where DEPTNO=30) --49、列出从事同一种工作但属于不同部门的雇员的不同组合 select DISTINCT a.ENAME, b.ENAME from emp a join emp b on a.JOB=b.JOB and a.DEPTNO != b.DEPTNO SELECT DISTINCT A.ENAME, B.ENAME FROM EMP AS A,EMP AS B WHERE A.JOB=B.JOB AND A.DEPTNO!=B.DEPTNO
外增 多列
CREATE TABLE [dbo].[course]( [cno] [int] NOT NULL, [cname] [nchar](10) NOT NULL, [tno] [nchar](10) NOT NULL,
)
CREATE TABLE [dbo].[sc]( [sno] [int] NULL, [cno] [int] NULL, [socre] [float] NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[student]( [sno] [int] NOT NULL, [sname] [nchar](10) NOT NULL, [sage] [int] NOT NULL, [ssex] [nchar](10) NOT NULL,
CREATE TABLE [dbo].[teacher]( [tno] [nchar](10) NOT NULL, [tname] [nchar](10) NULL,
INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1001,'english','TS01') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1002,'math','TS02') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1003,'art','TS05') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1004,'dance','TS04') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1005,'physic','TS06') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1006,'cheministy','TS08') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1007,'paint','TS07') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1008,'panio','TS09') INSERT INTO [dbo].[course]([cno],[cname],[tno])VALUES(1009,'computer','TS03')
insert into dbo.sc(sno,cno,socre)values(1,1001,89) insert into dbo.sc(sno,cno,socre)values(1,1002,89) insert into dbo.sc(sno,cno,socre)values(1,1003,95) insert into dbo.sc(sno,cno,socre)values(1,1004,95) insert into dbo.sc(sno,cno,socre)values(1,1005,83) insert into dbo.sc(sno,cno,socre)values(1,1006,95) insert into dbo.sc(sno,cno,socre)values(1,1007,82) insert into dbo.sc(sno,cno,socre)values(1,1008,96) insert into dbo.sc(sno,cno,socre)values(1,1009,97) insert into dbo.sc(sno,cno,socre)values(2,1001,85) insert into dbo.sc(sno,cno,socre)values(2,1002,89) insert into dbo.sc(sno,cno,socre)values(2,1003,75) insert into dbo.sc(sno,cno,socre)values(2,1004,95) insert into dbo.sc(sno,cno,socre)values(2,1005,83) insert into dbo.sc(sno,cno,socre)values(2,1006,85) insert into dbo.sc(sno,cno,socre)values(2,1007,92) insert into dbo.sc(sno,cno,socre)values(2,1008,96) insert into dbo.sc(sno,cno,socre)values(2,1009,97) insert into dbo.sc(sno,cno,socre)values(3,1001,95) insert into dbo.sc(sno,cno,socre)values(3,1002,89) insert into dbo.sc(sno,cno,socre)values(3,1003,95) insert into dbo.sc(sno,cno,socre)values(3,1004,95) insert into dbo.sc(sno,cno,socre)values(3,1005,88) insert into dbo.sc(sno,cno,socre)values(3,1006,95) insert into dbo.sc(sno,cno,socre)values(3,1007,92) insert into dbo.sc(sno,cno,socre)values(3,1008,96) insert into dbo.sc(sno,cno,socre)values(3,1009,90) insert into dbo.sc(sno,cno,socre)values(4,1001,96) insert into dbo.sc(sno,cno,socre)values(4,1002,89) insert into dbo.sc(sno,cno,socre)values(4,1003,95) insert into dbo.sc(sno,cno,socre)values(4,1004,95) insert into dbo.sc(sno,cno,socre)values(4,1005,89) insert into dbo.sc(sno,cno,socre)values(4,1006,95) insert into dbo.sc(sno,cno,socre)values(4,1007,82) insert into dbo.sc(sno,cno,socre)values(4,1008,96) insert into dbo.sc(sno,cno,socre)values(4,1009,97) insert into dbo.sc(sno,cno,socre)values(5,1001,93) insert into dbo.sc(sno,cno,socre)values(5,1002,89) insert into dbo.sc(sno,cno,socre)values(5,1003,98) insert into dbo.sc(sno,cno,socre)values(5,1004,93) insert into dbo.sc(sno,cno,socre)values(5,1005,83) insert into dbo.sc(sno,cno,socre)values(5,1006,95) insert into dbo.sc(sno,cno,socre)values(5,1007,92) insert into dbo.sc(sno,cno,socre)values(5,1008,93) insert into dbo.sc(sno,cno,socre)values(5,1009,97) insert into dbo.sc(sno,cno,socre)values(6,1001,92) insert into dbo.sc(sno,cno,socre)values(6,1002,89) insert into dbo.sc(sno,cno,socre)values(6,1003,95) insert into dbo.sc(sno,cno,socre)values(6,1004,91) insert into dbo.sc(sno,cno,socre)values(6,1005,83) insert into dbo.sc(sno,cno,socre)values(6,1006,95) insert into dbo.sc(sno,cno,socre)values(6,1007,92) insert into dbo.sc(sno,cno,socre)values(6,1008,86) insert into dbo.sc(sno,cno,socre)values(6,1009,98) insert into dbo.sc(sno,cno,socre)values(7,1001,95) insert into dbo.sc(sno,cno,socre)values(7,1002,84) insert into dbo.sc(sno,cno,socre)values(7,1003,95) insert into dbo.sc(sno,cno,socre)values(7,1004,95) insert into dbo.sc(sno,cno,socre)values(7,1005,83) insert into dbo.sc(sno,cno,socre)values(7,1006,85) insert into dbo.sc(sno,cno,socre)values(7,1007,94) insert into dbo.sc(sno,cno,socre)values(7,1008,96) insert into dbo.sc(sno,cno,socre)values(7,1009,97) insert into dbo.sc(sno,cno,socre)values(8,1001,96) insert into dbo.sc(sno,cno,socre)values(8,1002,89) insert into dbo.sc(sno,cno,socre)values(8,1003,95) insert into dbo.sc(sno,cno,socre)values(8,1004,85) insert into dbo.sc(sno,cno,socre)values(8,1005,83) insert into dbo.sc(sno,cno,socre)values(8,1006,99) insert into dbo.sc(sno,cno,socre)values(8,1007,92) insert into dbo.sc(sno,cno,socre)values(8,1008,96) insert into dbo.sc(sno,cno,socre)values(8,1009,94) insert into dbo.sc(sno,cno,socre)values(9,1001,93) insert into dbo.sc(sno,cno,socre)values(9,1002,89) insert into dbo.sc(sno,cno,socre)values(9,1003,86) insert into dbo.sc(sno,cno,socre)values(9,1004,95) insert into dbo.sc(sno,cno,socre)values(9,1005,83) insert into dbo.sc(sno,cno,socre)values(9,1006,95) insert into dbo.sc(sno,cno,socre)values(9,1007,92) insert into dbo.sc(sno,cno,socre)values(9,1008,96) insert into dbo.sc(sno,cno,socre)values(9,1009,92) insert into dbo.sc(sno,cno,socre)values(10,1001,96) insert into dbo.sc(sno,cno,socre)values(10,1002,88) insert into dbo.sc(sno,cno,socre)values(10,1003,75) insert into dbo.sc(sno,cno,socre)values(10,1004,95) insert into dbo.sc(sno,cno,socre)values(10,1005,83) insert into dbo.sc(sno,cno,socre)values(10,1006,95) insert into dbo.sc(sno,cno,socre)values(10,1007,82) insert into dbo.sc(sno,cno,socre)values(10,1008,96) insert into dbo.sc(sno,cno,socre)values(10,1009,87)
插入数据找不到了
--1.查询课程编号为“1001”的课程比“1002”的课程成绩高的所有学生的学号 select a.sno from sc a, sc b where a.socre>b.socre and a.cno=1001 and b.cno=1002 and a.sno=b.sno select * from sc --2.查询平均成绩大于60分的学生的学号和平均成绩 select sno ,avg(socre) from sc group by sno having AVG(socre)>60 --3.查询所有学生的学号、姓名、选课数、总成绩 select s.sname,s.sno ,sum(socre) ,count(cno) from student s join sc on s.sno=sc.sno group by sc.sno,s.sname --4、查询姓“李”的老师的个数 select tname, count(1) from teacher where SUBSTRING(tname,1,1)='李' group by tname --5、查询没学过“王海”老师课的学生的学号、姓名 select sno,sname from student where sno not in(select sno from sc where cno=(select cno from course where tno=(select tno from teacher where tname='王海'))) select sno,sname from student where sno not in(select sno from SC where cno in(select cno from course where tno in(select tno from teacher where tname='王海'))) --6、查询学过“王海”老师所教的所有课的同学的学号、姓名 --(对原始表Course，SC稍作修改，让王海交2门课 insert into course values('1010','Exercise','TS03')
select sno,count(socre) from sc where socre<60 group by sno having count(socre)>2 --查询学生的总成绩并进行排名 select sno ,sum(socre) as s from sc group by sno order by s desc --查询平均成绩大于60分的学生的学号和平均成绩 select sno ,avg(socre) as pingjun from sc group by sno having avg(socre)>60; --查询所有课程成绩小于60分学生的学号、姓名 select s.sno,s.sname,sc.cno,sc.socre from student s left join sc on s.sno=sc.sno where sc.socre<60 --查询没有学全所有课的学生的学号、姓名| select s.sno,s.sname from student s where s.sno in (select sno from sc group by sc.sno having count(cno)<(select count(cno) from course)) --查询出只选修了两门课程的全部学生的学号和姓名| select sc.sno,s.sname from student s left join sc on s.sno=sc.sno group by sc.sno ,s.sname having count(cno)=2 --查找1990年出生的学生名单 --select * from student where year() --查询所有学生的学号、姓名、选课数、总成绩 select s.sno,s.sname,count(sc.cno) as xuankeshu, sum(sc.socre) as zongchengji from student s left join sc on s.sno=sc.sno group by sc.sno,s.sno,s.sname --查询平均成绩大于85的所有学生的学号、姓名和平均成绩 select s.sno,s.sname,count(sc.cno) as xuankeshu, sum(sc.socre) as zongchengji ,avg(sc.socre)as pingjun from student s left join sc on s.sno=sc.sno group by sc.sno,s.sno,s.sname having avg(sc.socre)>85 --查询学生的选课情况：学号，姓名，课程号，课程名称 select s.sno,s.sname,sc.cno,c.cname from student s inner join sc on sc.sno=s.sno inner join course as c on sc.cno=c.cno --查询出每门课程的及格人数和不及格人数 select cno, count(sno),'及格' from sc where socre>59 group by cno union select cno, count(sno),'不及格' from sc where socre<60 group by cno -- 考察case表达式 select cno,sum(case when socre>=60 then 1 else 0 end) as 及格人数,sum(case when socre < 60 then 1 else 0 end) as 不及格人数 from sc group by cno; --使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称 select sum (case when socre BETWEEN 85 and 100 then 1 else 0 end) as [100-85], sum (case when socre>=70 and socre<85 then 1 else 0 end) as [85-70] , sum (case when socre >= 60 and socre<70 then 1 else 0 end) as [70-60] , sum (case when socre <60 then 1 else 0 end) as [<60] from sc ; --检索"0001"课程分数小于60，按分数降序排列的学生信息 select s.sno,s.sname,s.sage, sc.socre from student s left join sc on s.sno=sc.sno where sc.cno='1001' and sc.socre<60 order by sc.socre desc --查询不同老师所教不同课程平均分从高到低显示 select t.tname,t.tno,avg(sc.socre) as pingjun from teacher t inner join course c on t.tno=c.tno inner join sc on c.cno=sc.cno group by t.tno ,tname order by pingjun desc --查询课程名称为"数学"，且分数低于60的学生姓名和分数 select s.sname,sc.socre from student s inner join sc on s.sno=sc.sno inner join course c on sc.cno=c.cno where c.cname='math' and sc.socre<60 order by socre --group by c.cname='' --查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似） select s.sname,c.cname,sc.socre from student s inner join sc on s.sno=sc.sno inner join course c on sc.cno=c.cno where sc.socre>70 order by socre desc --查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 select s.sname,s.sno,avg(sc.socre) as pingjun from student s left join sc on s.sno=sc.sno left join course c on sc.cno=c.cno group by sc.sno,s.sname,s.sno having sum(case when sc.socre<60 then 1 else 0 end ) >=2 --select b.sname, avg(sc.socre)as pingju,sc.sno from sc inner join student b on sc.sno =b.sno where sc.socre <60 group by sc.sno, b.sname having count(sc.sno) >=2; --cuowu 平均成绩求错 --查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select sc.sno as 学生编号A,sc.cno as 课程编号A,sc.socre as 学生成绩A, bsc.sno as 学生编号B,bsc.cno as 课程编号B,bsc.socre as 学生成绩B from sc left join sc bsc on sc.sno=bsc.sno where bsc.socre=sc.socre and bsc.cno!=sc.cno --查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号 select sc.sno,sc.socre,sc.cno,bsc.sno,bsc.socre,bsc.cno from sc left join sc as bsc on sc.sno=bsc.sno where sc.cno='1001' and bsc.cno='1002' and sc.socre>bsc.socre --查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名 select * from student s left join sc on sc.sno=s.sno where sc.cno='1001' or sc.cno='1002' --查询学过“王海”老师所教的所有课的同学的学号、姓名 select * from student s left join sc on sc.sno =s.sno left join course c on sc.cno=c.cno left join teacher t on t.tno=c.tno where tname='王海' --查询没学过"王海"老师讲授的任一门课程的学生姓名（与上题类似，"没学过"用not in来实现) select bs.sno,bs.sname from student bs where bs.sno not in (select s.sno from student s left join sc on sc.sno =s.sno left join course c on sc.cno=c.cno left join teacher t on t.tno=c.tno where tname='王海') --查询学习“王海”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个） --select s.sname,sc.cno,sc.socre from student s left join sc on sc.sno =s.sno left join course c on sc.cno=c.cno left join teacher t on t.tno=c.tno where tname='王海' order by sc.socre desc limit 1 --查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名 --select s.sno,s.sname ,* from student s left join sc on sc.sno=s.sno left join sc bsc on sc.sno=bsc.sno where sc.sno!=bsc.sno select sc.sno,sname,cno from student left join sc on sc.sno=student.sno where cno in (select sc.cno from sc where sc.sno=1) and sc.sno!=1 --group by sc.sno,sname having count(sc.sno)>0 多此一举 --按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 select sno, avg(socre)as pingjun from sc group by sno,sno order by pingjun desc select sno, max(case when c.cname='math' then sc.socre else null end)as 数学, max(case when c.cname='english' then sc.socre else null end)as yingyu, max(case when c.cname='art' then sc.socre else null end)as yishu, max(case when c.cname='dance' then sc.socre else null end)as wudao, max(case when c.cname='physic' then sc.socre else null end)as wuli, max(case when c.cname='cheministy' then sc.socre else null end)as huaxue, max(case when c.cname='paint' then sc.socre else null end)as paint, max(case when c.cname='panio' then sc.socre else null end)as panio, max(case when c.cname='computer' then sc.socre else null end)as computer, max(case when c.cname='Exercise' then sc.socre else null end)as Exercise, avg(socre)as pingjun from sc inner join course as c on c.cno=sc.cno group by sno order by pingjun desc --查询学生平均成绩及其名次 --select sno,avg(socre)as pingjun,row_number() over(order by avg(socre) desc) as mingci from sc group by sno -- order by pingjun desc --查询每门功课成绩最好的前两名学生姓名 --select sc.cno,row_number()over(order by avg(socre) desc) as ranking from student s left join sc on sc.sno=s.sno group by sc.cno where ranking<3 cuowu select a.cno,s.sname,a.socre,a.ranking from (select cno ,sno ,socre ,row_number() over(partition by cno order by socre desc) as ranking from sc)as a inner join student s on a.sno =s.sno where a.ranking<3 --查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（与上一题相似） select a.cno,s.sname,a.socre,a.ranking from (select cno ,sno ,socre ,row_number() over(partition by cno order by socre desc) as ranking from sc)as a inner join student s on a.sno =s.sno where a.ranking in( 2,3) ; ---查询各科成绩前三名的记录（不考虑成绩并列情况）（与上一题相似） select a.cno,s.sname,a.socre,a.ranking from (select cno ,sno ,socre ,row_number() over(partition by cno order by socre desc) as ranking from sc)as a inner join student s on a.sno =s.sno where a.ranking <4
继续补充
--1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 select * from sc a left join sc b on a.sno=b.sno where a.cno='1001' and b.cno='1002' and a.socre>b.socre --2.查询同时存在" 001 "课程和" 002 "课程的学生数据 select s.sno,s.sname,a.cno,b.cno from student s left join sc a on s.sno=a.sno join sc b on a.sno=b.sno where a.cno='1001' and b.cno='1002' --3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) select sno,cno,socre from sc where cno='1001' and sno not in (select sno from sc where cno='1002') select * from (select * from sc where sc.cno = '1001')as t1 left join (select * from sc where sc.cno = '1002')as t2 on t1.sno = t2.sno; select * from sc a left join sc b on a.sno=b.sno --4.查询不存在" 01 "课程但存在" 02 "课程的情况 select * from (select * from sc a where a.cno='1002') as sc1 left join (select * from sc b where b.cno='1001') as sc2 on sc1.sno=sc2.sno select * from sc where sc.sno not in (select sno from sc where sc.cno = '1001') and sc.cno = '1002' --5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 select s.sno,s.sname ,avg(sc.socre) pingjun from student s left join sc on s.sno=sc.sno group by s.sno,s.sname having avg(sc.socre)>60 --方法一： select st.sno,st.sname,r.avg_socre from student as st,(select sno,avg(socre) as avg_socre from sc group by sno having avg(socre) >60) as r where st.sno = r.sno; --方法二： select st.sno,st.sname from student as st right join(select sno,avg(socre) as avg_socre from sc group by sno having avg(socre) >60) as t on st.sno = t.sno; --方法三： select t.sno,r.sname,t.avg_socre from (select sno,avg(socre) as avg_socre from sc group by sno having avg(socre) >60) as t left join (select st.sno,st.sname from student as st) as r on t.sno = r.sno --———————————————— --版权声明：本文为CSDN博主「xGuardian」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 --原文链接：https://blog.csdn.net/xGuardian/article/details/93167928 --6.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和 select s.sno,s.sname ,count(sc.sno)as xunekeshu,sum(sc.socre) as zongchengji from student s left join sc on sc.sno=s.sno group by s.sno,s.sname --标准 --联合查询不会显示没选课的学生： select st.sno,st.sname,t.cnums,t.socresum from student as st,(select sno,count(cno) as cnums,sum(socre) as socresum from sc group by sno)as t where st.sno = t.sno; --显示没选课的学生（显示为null），需要使用join select st.sno,st.sname,t.cnums,t.socresum from Student as st left join (select sno,count(sc.cno) as cnums,sum(sc.socre) as socresum from sc group by sno) as t on st.sno = t.sno; select s.sno,s.sname,t.cnums,t.socresum from ((select st.sno,st.sname from student as st) as s left join (select sc.sno,count(sc.cno) as cnums,sum(sc.socre) as socresum from sc group by sc.sno)as t on s.sno = t.sno) --7.查询学过「王海」老师授课的同学的信息 select * from student where sno in(select distinct sno from sc where cno in(select cno from course where tno=(select tno from teacher where tname='王海'))) select * from student s left join sc on s.sno=sc.sno left join course c on sc.cno=c.cno left join teacher t on c.tno=t.tno where t.tname='王海' select st.* from student as st,course,teacher,sc where st.sno = sc.sno and sc.cno = course.cno and course.tno = teacher.tno and teacher.tname = '王海' --8. 查询没有学全所有课程的同学的信息 select * from student where sno in(select sc.sno from sc,(select count(cno)as c from course) as cc group by sc.sno,cc.c having count(sc.sno) <cc.c) select * from student where student.sno not in (select sc.sno from sc group by sc.sno having count(sc.cno) = (select count(course.cno) from course)) --9.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 --select * from sc a,(select cno from sc where sno=1)as b where a.sno!=1 group by sno having count(case when a.cno in b then 1 else 0 end) >2 select sno, count(cno) as 数量 from sc where sno!=1 and cno in(select cno from sc where sno=1) group by sno having count(cno)>0 --10.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 select sno, count(cno) as 数量 from sc where sno!=1 and cno in(select cno from sc where sno=1) group by sno having count(sc.cno)=(select count(cno) from sc where sno=1) --思路 ： 查询 和 01号同学 课程 数量一样的 其他 同学 --再把数量一样的 同学 inner join 01 号同学。 如果数量 还是一样则课程相同 --11. 查询没学过"王海"老师讲授的任一门课程的学生姓名 select sname from student where sno not in(select s.sno from student s left join sc a on s.sno=a.sno left join sc b on a.sno=b.sno left join course c on b.cno=c.cno left join teacher t on c.tno=t.tno where t.tname='王海' group by s.sno) --select * --from student as st --where st.sno not in ( -- select sno from sc where sc.cno in ( -- select course.cno from course where course.tid in ( -- select tid from teacher where tname = '张三'))); --select * from student --where student.sno not in (select sc.sno from course,sc,teacher -- where sc.cno = course.cno -- and course.tid = teacher.tid -- and tname = '张三'); --12.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 select sc.sno,s.sname,avg(socre) from student s left join sc on s.sno=sc.sno group by sc.sno,s.sname having count(case when socre<60 then 1 else 0 end)>1 --select st.sno,st.sname,avg(sc.socre) as avg_socre --from student as st,sc --where st.sno = sc.sno --and sc.socre < 60 --group by st.sno,st.sname --having count(*) >=2; --select student.sno, student.sname, AVG(sc.socre) as avg_socre from student,sc --where student.sno = sc.sno and sc.socre<60 --group by student.sno,student.sname --having count(*)>1; --13.检索" 01 "课程分数小于 60，按分数降序排列的学生信息 select * from student s left join sc on s.sno=sc.sno where cno='1001' and socre<60 order by socre desc --14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 select * from student s left join sc on s.sno=sc.sno left join (select sno,avg(socre) as pingju from sc group by sno)as b on b.sno=sc.sno order by b.pingju desc -- 15.查询各科成绩最高分、最低分和平均分，选修人数，及格率，中等率，优良率，优秀率： select cno ,max(socre)as '成绩最高' ,min(socre)as '最低分', avg(socre)as '平均分',count(socre)as'选修人数', sum(case when sc.socre>=60 then 1 else 0 end)*100/count(*) as '及格率', sum(case when sc.socre>=70 and sc.socre<80 then 1 else 0 end )*100/count(*)as'中等率', sum(case when sc.socre>=80 and sc.socre<90 then 1 else 0 end )*100/count(*)as'优良率', sum(case when sc.socre>=90 then 1 else 0 end )*100/count(socre)as'优秀率' from sc group by cno ORDER BY count(*)DESC, sc.cno ASC --16.按各科成绩进行排序，并显示排名， socre 重复时保留名次空缺 select *,rank()over(partition by cno order by socre desc) as 名次 from sc -- order by cno ,socre desc select *,DENSE_RANK()over(partition by cno order by socre desc) as 名次 from sc select *,ROW_NUMBER()over(partition by cno order by socre desc) as 名次 from sc --17.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺 select *,ROW_NUMBER()over(order by su desc) as 名次 from student s left join(select sno, sum(socre) as su from sc group by sno)as b on b.sno=s.sno --18.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比 select sc.cno as'课程编号',course.cname as '课程名称', sum(case when sc.socre>=85 and sc.socre<101 then 1 else 0 end )as'[100-85]',sum(case when sc.socre>=85 and sc.socre<101 then 1 else 0 end )*100/count(*)as'所占百分比', sum(case when sc.socre>=70 and sc.socre<85 then 1 else 0 end )as'[85-70]',sum(case when sc.socre>=70 and sc.socre<85 then 1 else 0 end )*100/count(*)as'所占百分比', sum(case when sc.socre>=60 and sc.socre<70 then 1 else 0 end )as'[70-60]',sum(case when sc.socre>=60 and sc.socre<70 then 1 else 0 end )*100/count(*)as'所占百分比', sum(case when sc.socre<61 then 1 else 0 end) as '[60-0]',sum(case when sc.socre<61 then 1 else 0 end)*100 /count(*) as '所占百分比' from sc left join course on sc.cno=course.cno group by sc.cno,course.cname order by sc.cno --19.查询各科成绩前三名的记录 select * from(select *, ROW_NUMBER() over (partition by cno order by socre desc) as '名次' from sc )as s where s.名次<4 --20.查询出只选修两门课程的学生学号和姓名 --联合查询 select s.sname, sc.sno,count(*) as menshu from sc,student s where s.sno=sc.sno group by sc.sno,s.sname having count(*)=2 --嵌套查询 --select sno,sname --from student --where student.sno in (select sc.sno from sc group by sc.sno having count(sc.cno)=2); --21. 查询同名学生名单，并统计同名人数 select sname ,count(sno) from student group by sname select sname,count(sname) as 同名人数 from student group by sname having count(*) >1; --嵌套查询列出同名的全部学生的信息 select st.* from student as st where st.sname in (select sname from student group by sname having count(*) > 1) --23.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 select cno ,avg(socre)as pingjun from sc group by cno order by avg(socre) desc ,cno --24.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 select sc.sno,s.sname,avg(socre)as pingjun from student s left join sc on sc.sno=s.sno group by sc.sno,s.sname having avg(socre)>85 --25.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 select s.sno,s.sname,c.cname,sc.socre from student s left join sc on sc.sno=s.sno left join course c on c.cno=sc.cno where c.cname='math' and sc.socre<60 --26.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） select * from (select * from student s)as s left join (select * from sc )as scc on s.sno=scc.sno select student.sname,cno,socre from student left join sc on student.sno = sc.sno ---联合查询下---没选课的不会被查询出来 --select st.sname,cno,socre --from student as st,sc --where st.sno = sc.sno; ----group by st.sname,cno,socre; ----级联查询下===没选课的情况也会根据用户人数显示选课为空 --select student.sname,cno,socre --from student --left join sc --on student.sno = sc.sno --27.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 select s.sname ,sc.cno,c.cname ,sc.socre from student s left join sc on s.sno=sc.sno left join course c on c.cno=sc.cno where sc.socre>70 select student.sname,course.cname,sc.socre from student,course,sc where sc.socre >70 and student.sno = sc.sno and sc.cno = course.cno --28. 查询存在不及格的课程 select s.sname ,sc.cno,c.cname ,sc.socre from student s left join sc on s.sno=sc.sno left join course c on c.cno=sc.cno where sc.socre<60 select cno from sc where socre < 60 group by cno; select distinct sc.cno from sc where sc.socre <60; --29.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名 select student.sno,sname,sco.socre from student left join ( select sc.cno,sc.sno,sc.socre from sc where sc.socre>79 and cno='1001')as sco on sco.sno=student.sno where sco.socre is not null -- select st.sno,st.sname,sc.socre --from student as st,sc --where st.sno =sc.sno --and sc.cno = '01' --and sc.socre >=80; --30.成绩有重复的情况下，查询选修「王海」老师所授课程的学生中，成绩最高的学生信息及其成绩 select top 1 socre, sno from sc, (select cno from course , (select tno from teacher where tname='王海')as t where t.tno=course.tno)as c where c.cno=sc.cno order by socre desc --31.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select * from sc a left join sc b on a.sno=b.sno left join student s on a.sno=s.sno where a.socre=b.socre and a.cno!=b.cno --32.查询每门功成绩最好的前两名 select * from (select * ,rank()over(partition by cno order by socre desc)as paiming from sc) as ss where ss.paiming<3 select a.sno,a.cno,a.socre from sc as a left join sc as b --此处使用cno进行级联，因为判定的分组依据是每门成绩 on a.cno = b.cno and a.socre < b.socre group by a.sno,a.cno,a.socre having count(b.cno) <2 order by a.cno; --33. 统计每门课程的学生选修人数（超过 5 人的课程才统计） select cno, count(*) from sc group by cno having count(*) >5 --34.检索至少选修两门课程的学生学号 select sno ,count(*)from sc group by sno having count(*)>1 --35.查询选修了全部课程的学生信息 select cno,c.cou from sc,(select count(*)as cou from course)as c group by cno,c.cou having c.cou=count(*) --select student.* --from student,sc --where student.sid = sc.sid --group by student.sid,student.sname,student.sage,student.ssex --having count(sc.cid) = (select distinct count(*) from course); --select * --from sc --36. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一 --select student.sid,student.sname,datediff(year,student.sage,getdate()) as 学生年龄 --from student; --37.查询本周过生日的学生 --备注：37，38题在测试过程中建议添加当前（你练习时间）周的周一，周日，上一周的周日，下一周的周一，周日，下下周的周一 这些sage时间数据的学生，便于理解查询代码中的参数含义 ---- 本周一 --declare @dt1 datetime --set @dt1 = DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) ---- 下周一 --declare @dt2 datetime --set @dt2 = DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) + 1, -1) ----此处的-1是为了调整一周的时间段，若为0，则会包含下周一，为-1时，则仅包含当周周末 --print @dt1 --select * from student --WHERE DATEADD(Year,DATEDIFF(Year,student.sage,@dt1),student.sage) --BETWEEN @dt1 AND @dt2 --OR DATEADD(Year,DATEDIFF(Year,student.sage,@dt2),student.sage) --BETWEEN @dt1 AND @dt2 --38.查询下周过生日的学生 ---- 下周一 --declare @dt1 datetime --set @dt1 = DATEADD(week, DATEDIFF(week, 0, GETDATE())+1, 0) ---- 下下周一 --declare @dt2 datetime --set @dt2 = DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) + 2, -1) --print @dt2 --select * from student --WHERE DATEADD(Year,DATEDIFF(Year,student.sage,@dt1),student.sage) --BETWEEN @dt1 AND @dt2 --OR DATEADD(Year,DATEDIFF(Year,student.sage,@dt2),student.sage) --BETWEEN @dt1 AND @dt2; --39.查询本月过生日的学生 --select * --from student --where month(student.sage) = month(getdate()) --40. 查询下月过生日的学生 --select * --from student --where month(student.sage) = month(getdate())+1