sql 练习（1）

　　1.建立实验表

 CREATE TABLE STUDENT
(SNO VARCHAR2(3) NOT NULL,
   SNAME VARCHAR2(40) NOT NULL,
   SSEX VARCHAR2(20) NOT NULL,
   SBIRTHDAY DATE,
   CLASS VARCHAR2(20))
/
CREATE TABLE COURSE
(CNO VARCHAR2(5) NOT NULL,
   CNAME VARCHAR2(10) NOT NULL,
   TNO VARCHAR2(10) NOT NULL)
/
CREATE TABLE SCORE
(SNO VARCHAR2(3) NOT NULL,
   CNO VARCHAR2(5) NOT NULL,
   DEGREE NUMERIC(10, 1) NOT NULL)
/ 
CREATE TABLE TEACHER
(TNO VARCHAR2(3) NOT NULL,
   TNAME VARCHAR2(20) NOT NULL, TSEX VARCHAR2(20) NOT NULL,
   TBIRTHDAY DATE , PROF VARCHAR2(60),
   DEPART VARCHAR2(10) NOT NULL)
/
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'tom','man','',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'jon','man','',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'lily','woman','',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'mac','man','',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'mary','woman','',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'fuck','man','',95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'computer science',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'operate system' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'digital circal' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'maths' ,100);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'Mr Li','man','','js','computer');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'Mrs Zhang','man','','te','elecitric');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'Mrs Wang','woman','','ta','computer');
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为早退

打卡表  card 
SQL> create table card( 
    cid number(20), 
    ctime date, 
    cuser number(20)); 
 
人员表  person 
create table person( 
 pid number(20), 
 name varchar2(10) 
) 
 --插入人员表的数据 
insert into person values(1,'a'); 
insert into person values(2,'b'); 
 
 
 
--插入打卡的数据 
insert into card values(1,to_date('20090719080200','yyyymmddhh24miss'),1); 
insert into card values(2,to_date('20090719180200','yyyymmddhh24miss'),1); 
insert into card values(3,to_date('20090719090200','yyyymmddhh24miss'),2); 
insert into card values(4,to_date('20090719170200','yyyymmddhh24miss'),2); 
 
insert into card values(5,to_date('20090720080200','yyyymmddhh24miss'),1); 
insert into card values(6,to_date('20090720160200','yyyymmddhh24miss'),1); 
insert into card values(7,to_date('20090720070200','yyyymmddhh24miss'),2); 
insert into card values(8,to_date('20090720200200','yyyymmddhh24miss'),2); 

--分析：  先分组统计出每个人，每天的上班时间和下班时间  即（id,day,mindate,maxdate) 
select  p.pid as id, 
 to_char(c.ctime,'yyyymmdd') as day, 
 to_char(min(c.ctime),'hh24mi') as mindate, 
 to_char(max(c.ctime),'hh24mi') as maxdate   
 from card c,person p where c.cuser = p.pid group by p.pid,to_char(c.ctime,'yyyymmdd'); 
 --把上面的分析做成一个视图，判断上班时间是否为迟到  和  下班时间是否为早退 
-- 如 果 判 断 前 10 天 的 打 卡 记 录 ， 就   改成
to_char(c.ctime,'yyyymmdd')<=to_char(sysdate-10,'yyyymmdd') 
 
select  p.name as person_name, 
 e1.day as work_day, 
 e1.mindate as AM, 
 e1.maxdate as PM, 
 --判断迟到 
 case  
  when e1.mindate between '0800' and '1200' then 'yes'  
  else 'no' 
 end as later, 
 --判断早退 
 case  
  when e1.maxdate between '1201' and '1800' then 'yes'  
  else 'no' 
 end as leave_early 
 from    
  --员工表 
  person p, 
  --上面那张视图表 
  (select    
   p.pid as id, 
   to_char(c.ctime,'yyyymmdd') as day, 
   to_char(min(c.ctime),'hh24mi') as mindate, 
   to_char(max(c.ctime),'hh24mi') as maxdate 
      from card c,person p  
   where  
    c.cuser = p.pid and  
    to_char(c.ctime,'yyyymmdd')<=to_char(sysdate-1,'yyyymmdd')  
   group by p.pid,to_char(c.ctime,'yyyymmdd') 
  ) e1  
 where p.pid = e1.id; 
 
 

 

　　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