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('','yyyymmddhh24miss'),1);
insert into card values(2,to_date('','yyyymmddhh24miss'),1);
insert into card values(3,to_date('','yyyymmddhh24miss'),2);
insert into card values(4,to_date('','yyyymmddhh24miss'),2); insert into card values(5,to_date('','yyyymmddhh24miss'),1);
insert into card values(6,to_date('','yyyymmddhh24miss'),1);
insert into card values(7,to_date('','yyyymmddhh24miss'),2);
insert into card values(8,to_date('','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 '' and '' then 'yes'
else 'no'
end as later,
--判断早退
case
when e1.maxdate between '' and '' 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