sql 练习(1)
1.建立实验表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE TABLE STUDENT 2 (SNO VARCHAR2(3) NOT NULL, 3 SNAME VARCHAR2(40) NOT NULL, 4 SSEX VARCHAR2(20) NOT NULL, 5 SBIRTHDAY DATE, 6 CLASS VARCHAR2(20)) 7 / 8 CREATE TABLE COURSE 9 (CNO VARCHAR2(5) NOT NULL, 10 CNAME VARCHAR2(10) NOT NULL, 11 TNO VARCHAR2(10) NOT NULL) 12 / 13 CREATE TABLE SCORE 14 (SNO VARCHAR2(3) NOT NULL, 15 CNO VARCHAR2(5) NOT NULL, 16 DEGREE NUMERIC(10, 1) NOT NULL) 17 / 18 CREATE TABLE TEACHER 19 (TNO VARCHAR2(3) NOT NULL, 20 TNAME VARCHAR2(20) NOT NULL, TSEX VARCHAR2(20) NOT NULL, 21 TBIRTHDAY DATE , PROF VARCHAR2(60), 22 DEPART VARCHAR2(10) NOT NULL) 23 / 24 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'tom','man','',95033); 25 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'jon','man','',95031); 26 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'lily','woman','',95033); 27 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'mac','man','',95033); 28 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'mary','woman','',95031); 29 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'fuck','man','',95031); 30 31 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'computer science',825); 32 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'operate system' ,804); 33 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'digital circal' ,856); 34 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'maths' ,100); 35 36 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86); 37 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75); 38 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68); 39 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92); 40 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88); 41 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76); 42 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64); 43 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91); 44 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78); 45 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85); 46 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79); 47 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81); 48 49 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 50 VALUES (804,'Mr Li','man','','js','computer'); 51 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 52 VALUES (856,'Mrs Zhang','man','','te','elecitric'); 53 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 54 VALUES (825,'Mrs Wang','woman','','ta','computer'); 55 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
2.查询Score表中的最高分的学生学号和课程号。
select sno,cno from score where degree=(select max(degree) from score);
3.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
统计用分组:
select avg(degree) from score group by cno having count(cno)>5 and con like '3-%';
4.create table grade(low number(3,0),upp number(3),rank varchar(10));
SQL> insert into grade values(90,100,'A');
1 row created.
SQL> insert into grade values(80,89,'B');
1 row created.
SQL> insert into grade values(70,79,'C');
1 row created.
SQL> insert into grade values(60,69,'D');
1 row created.
SQL> insert into grade values(0,59,'E');
1 row created.
现查询所有同学的Sno、Cno和rank列。
计算使用子查询传递参数;
select Sno,Cno,rank from score,grade where degree between low and upp;
select sno,cno,(select rank from grade where sc.degree>low and sc.degree<upp) rank from score sc;
5.查询score中选学两门以上课程的同学中分数为非最高分成绩的记录。
把限制拆开来看;
select * from score where degree not in (select max(degree) from score group by cno) and cno not in (select cno from score group by cno having count(*)<=2);
6.查询选修某课程的同学人数多于5人的教师姓名。
多表分组(函数)转化为子查询分组;
select tname from teacher where tno in (select tno from score sc,course c where sc.cno=c.cno group by tno having count(*)>5);
7.查询成绩比该课程平均成绩低的同学的成绩表。
select sname ,cname ,degree from student s,score sc,course c where sc.sno=s.sno and c.cno=sc.cno and degree<(select avg(degree) from score scc where scc.cno=sc.cno);
8.查询每个班有哪些老师
等价挂载点问题;
班级转化为班里面的sno
老师转化为cno
然后加distinct即为一个班对应一个sno,一个老师对应一个cno
9.查询“c001”课程比“c002”课程成绩高的所有学生的学号;
select * from score a,score b where a.sno=b.sno and a.cno='3-105' and b.cno='3-245' and a.degree>b.degree;
select * from score a where a.sno in (select b.sno from score b where a.cno='3-105' and a.sno=b.sno and b.cno='3-245' and a.degree>b.degree);
10.查询没有学全所有课的同学的学号、姓名;
集合做差:
SQL> select * from student where sno in (select sno from (select stu.sno,c.cno from student stu cross join course c minus select sno,cno from score));
11.查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名;
集合操作:构造伪满集;
12.按各科平均成绩从低到高和及格率的百分数从高到低顺序
select avg(degree) ,(sum(case when degree>60 then 1 else 0 end)/count(*)) jige from score group by cno;
13.查询各科成绩前三名的记录:(不考虑成绩并列情况)
按内部分组编号:
select sno,cno,degree,row_number() over (partition by cno order by degree desc) rn from score;
14.查询全部学生都选修的课程的课程号和课程名
伪满集操作
15.列出至少有一个雇员的所有部门
内连接的定义:部门表要在雇员表中出现:俩个表中有相互联系的数据
16.列出按年薪排序的所有雇员的年薪
select (sal+nvl(comm,0))*12 as avn from emp order by avn
17.列出薪金水平处于第四位的雇员
Select * from (Select ename,sal, rank() over (order by sal desc) as grade from emp) where grade=4
18.找出不收取佣金或收取的佣金低于100的雇员
select * from emp where nvl(comm,0)<100;
19.找出各月最后一天受雇的所有雇员
select * from emp where hiredate= last_day(hiredate);
20.找出早于25年之前受雇的雇员
select * from emp where months_between(sysdate,hiredate)/12>25;
select * from emp where hiredate<add_months(sysdate,-12*25);
21显示只有首字母大写的所有雇员的姓名
select ename from emp where ename=initcap(ename);
22.显示不带有'R'的雇员姓名
Select ename from emp where ename not like ‘%R%’;
Select ename from emp where instr(ename,’R’)=0;
23.以年、月和日显示所有雇员的服务年限
Select months_between(sysdate,hiredate)/12 as “年”, months_between(sysdate,hiredate) as “月”, sysdate-hiredate as “日” from emp
24.显示每个员工每天是否有迟到和早退;
8:00--12:00 为迟到, 12:00--18:00为早退
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 打卡表 card 2 SQL> create table card( 3 cid number(20), 4 ctime date, 5 cuser number(20)); 6 7 人员表 person 8 create table person( 9 pid number(20), 10 name varchar2(10) 11 ) 12 --插入人员表的数据 13 insert into person values(1,'a'); 14 insert into person values(2,'b'); 15 16 17 18 --插入打卡的数据 19 insert into card values(1,to_date('20090719080200','yyyymmddhh24miss'),1); 20 insert into card values(2,to_date('20090719180200','yyyymmddhh24miss'),1); 21 insert into card values(3,to_date('20090719090200','yyyymmddhh24miss'),2); 22 insert into card values(4,to_date('20090719170200','yyyymmddhh24miss'),2); 23 24 insert into card values(5,to_date('20090720080200','yyyymmddhh24miss'),1); 25 insert into card values(6,to_date('20090720160200','yyyymmddhh24miss'),1); 26 insert into card values(7,to_date('20090720070200','yyyymmddhh24miss'),2); 27 insert into card values(8,to_date('20090720200200','yyyymmddhh24miss'),2); 28 29 --分析: 先分组统计出每个人,每天的上班时间和下班时间 即(id,day,mindate,maxdate) 30 select p.pid as id, 31 to_char(c.ctime,'yyyymmdd') as day, 32 to_char(min(c.ctime),'hh24mi') as mindate, 33 to_char(max(c.ctime),'hh24mi') as maxdate 34 from card c,person p where c.cuser = p.pid group by p.pid,to_char(c.ctime,'yyyymmdd'); 35 --把上面的分析做成一个视图,判断上班时间是否为迟到 和 下班时间是否为早退 36 -- 如 果 判 断 前 10 天 的 打 卡 记 录 , 就 改成 37 to_char(c.ctime,'yyyymmdd')<=to_char(sysdate-10,'yyyymmdd') 38 39 select p.name as person_name, 40 e1.day as work_day, 41 e1.mindate as AM, 42 e1.maxdate as PM, 43 --判断迟到 44 case 45 when e1.mindate between '0800' and '1200' then 'yes' 46 else 'no' 47 end as later, 48 --判断早退 49 case 50 when e1.maxdate between '1201' and '1800' then 'yes' 51 else 'no' 52 end as leave_early 53 from 54 --员工表 55 person p, 56 --上面那张视图表 57 (select 58 p.pid as id, 59 to_char(c.ctime,'yyyymmdd') as day, 60 to_char(min(c.ctime),'hh24mi') as mindate, 61 to_char(max(c.ctime),'hh24mi') as maxdate 62 from card c,person p 63 where 64 c.cuser = p.pid and 65 to_char(c.ctime,'yyyymmdd')<=to_char(sysdate-1,'yyyymmdd') 66 group by p.pid,to_char(c.ctime,'yyyymmdd') 67 ) e1 68 where p.pid = e1.id; 69 70
25.删除一张表重复记录(ID 是自增唯一,重复记录:其他字段都是一样)
非常经典的一道面试题(可能存在很多数据,要求性能比较高)
1 louis 20
2 louis 20
3 jimmy 30
4 louis 20
------------------------------------------------------------------
delete from aa where id not in(select min(id) from aa group by name,age);
select a1.id
from a a1,
a a2
where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age and a1.sex=a2.sex