droptableTeacher;
droptableCourse;
createtableCourse(
c_idnumberprimarykey,
namevarchar2(32)
);
createtableTeacher(
t_idnumberprimarykey,
namevarchar2(32),
c_idnumber
);
insertintoCourse(c_id,name)values(1,'java');
insertintoCourse(c_id,name)values(2,'C#');
insertintoCourse(c_id,name)values(3,'ASP.NET');
insertintoCourse(c_id,name)values(4,'Oracle');
insertintoCourse(c_id,name)values(5,'C++');
insertintoTeacher(t_id,name,c_id)
values(1,'zhangsan',1);
insertintoTeacher(t_id,name,c_id)
values(2,'lisi',2);
insertintoTeacher(t_id,name,c_id)
values(3,'wangwu',3);
insertintoTeacher(t_id,name)
values(4,'zhaoliu');
要求：按上列要求建好表初始数据
1.查出所有课程,与老师之间的关系?
select*
fromteachertrightouterjoincoursec
ont.c_id=c.c_id
2.查出所有,老师与课程之间的关系?
select*
fromteachertinnerjoincoursec
ont.c_id=c.c_id
3.查出暂时还没有老师任教的课程?
select*
fromteachertrightouterjoincoursec
ont.c_id=c.c_id
wheret.t_idisnull
4.查出暂时还没有授课任务的老师?
select*
fromteachertleftouterjoincoursec
ont.c_id=c.c_id
wheret.c_idisnull
5.统计每门课程有几名授课教师?
selectcount(*)
fromteachertinnerouterjoincoursec
ont.c_id=c.c_id
groupbyt.c_id
---------------------------------------------------------
droptableEmployee;
createtableEmployee(
idnumberprimarykey,
uNamevarchar2(32),
dept_idint,
salarynumber
);
insertintoEmployee(id,uName,dept_id,salary)values(1,'ZhangShan',null,10000);
insertintoEmployee(id,uName,dept_id,salary)values(2,'LiShi',1,8000);
insertintoEmployee(id,uName,dept_id,salary)values(3,'WangWu',2,6000);
insertintoEmployee(id,uName,dept_id,salary)values(4,'ZhaoLiu',3,4000);
insertintoEmployee(id,uName,dept_id,salary)values(5,'QianQi',4,2000);
要求:
<1>使用自连接查出表中员工的及其领导的?
selecte.uName,d.uName
fromEmployeee,Employeed
wheree.id=d.dept_id
<2>使用外连接查出表中员工的及其领导的?
selecte.uName,b.uName
fromEmployeeeleftouterjoinEmployeebone.id=b.dept_id
<3>用一条查询语句实现(union/unionall)
给2部门员工将工资降至原来80%
给4部门员工将工资减至原来70%
给135部门员工将工资涨至原来150%
union与unionall区别：union在进行表连接后会筛选掉重复的记录，所以在表连接后会对
所产生的结果集合进行排序
selectid,uName,salary*0.8,dept_idfromEmployeewheredept_id=2
union
selectid,uName,salary*0.7,dept_idfromEmployeewheredept_id=4
union
selectid,uName,salary*1.5,dept_idfromEmployeewheredept_idin(1,3,5)
<4>利用SQL语句查出Employee表中最大的领导?
selectuNamefromEmployeewheredept_idisnull
---------------------------------------------------
行列转换问题(case...when...then)
--建表
createtableStudent(
idnumberprimarykey,
namevarchar2(32)notnull,
coursevarchar2(32)notnull,
scorenumbernotnull
);
--模拟数据
insertintoStudent(id,name,course,score)values(1,'aaa','java',89);
insertintoStudent(id,name,course,score)values(2,'aaa','cpp',88);
insertintoStudent(id,name,course,score)values(3,'bbb','java',83);
insertintoStudent(id,name,course,score)values(4,'bbb','cpp',95);
转换前：
idnamecoursescore
1aaajava89
2aaacpp88
3bbbjava83
4bbbcpp95
转换后：
namejavacpp
aaa8988
bbb8395
----------------------------------------------------
查询员工表中第11条到第20条的记录(rownum,rowid)
select*from(selectrownumrn,first_namefroms_emp)
wherernbetween11and20
----------------------------------------------------
用一条查询语句实现(casewhenthen)
给2部门员工将工资降至原来80%
给4部门员工将工资减至原来70%
给135部门员工将工资涨至原来150%
其他部门工资不变
selectid,casewhendept_id=2thensalary*0.8
whendept_id=4thensalary*0.7
whendept_idin(1,3,5)thensalary*1.5
elsesalaryend
fromemployee
----------------------------------------------------
面试题:
createtableTestTable(
idnumber,
namevarchar2(32)
);
insertintoTestTablevalues(1,'jack');
删除语法：
deletefromtesttablewhere删除条件(id=1)
显示内容如下：
TestTable
idname
1jack
1jack
1jack
1jack
1jack
1jack
使用一条sql语句将数据进行排重处理(ROWID),结果如下
TestTable
idname
1jack
deletefromtesttableawhererowidnotin(
selectmax(b.rowid)fromtesttablebwherea.id=b.id
)
-------------------------------------------
deletefromtesttablewhererowid<>(
selectrowidfromtesttablewhererownum=1
)
-------------------------------------------------------
Person表与IDCard表之间是一对一的关系
1.创建一个Person表，人员基本信息
2.创建一个IDCard表，表示身份证编号
insertintoidcardnumber=123
insertintoPersonperson_id=1,idcard=123
insertintoPersonperson_id=2,idcard=123（X）
FK+UK:
CREATETABLEperson(
idnumberprimarykey,
namevarchar2(30),
idnonumberunique,
foreignkey(idno)referencesidcard(id)
)
CREATETABLEidcard(
idnumberprimarykey
)
FK+PK:
CREATETABLEperson(
idnumberprimarykey,
namevarchar2(30),
foreignkey(id)referencesidcard(id)
)
CREATETABLEidcard(
idnumberprimarykey
)
-------------------------------------------------------
MyUser表与Cars表之间是一对多的关系
CREATETABLEmyuser2(
idnumberprimarykey,
namevarchar2(30)
)
CREATETABLEcars(
idnumberprimarykey,
namevarchar2(30),
u_idnumberreferencesmyuser2(id)
)
-------------------------------------------------------
多对多关联关系：
createtablestudent(
idnumberprimarykey,
namevarchar2(30)
)
createtablecourse(
idnumberprimarykey,
namevarchar2(30)
)
createtablestu_cou(
stu_idreferencesstudent(id),
cou_idreferencescourse(id),
primarykey(stu_id,cou_id)
)
insertintostudentvalues(1,'a')
insertintostudentvalues(2,'b')
insertintocoursevalues(10,'java')
insertintocoursevalues(20,'android')
insertintostu_cou(stu_id,cou_id)values(1,10)
insertintostu_cou(stu_id,cou_id)values(2,10)
insertintostu_cou(stu_id,cou_id)values(1,20)
-------------------------------------------------------
SQL笔试题目：
--1.
表名：商品表
名称产地进价
苹果烟台2.5
苹果云南1.9
苹果四川3
西瓜江西1.5
西瓜北京2.4
……
（其他用户实验的记录大家可自行插入）
给出平均进价在2元以下的商品名称
select名称from商品表groupby名称havingavg(进价)<2
2.
namecoursescore
张青语文72
王华数学72
张华英语81
张青物理67
李立化学98
张燕物理70
张青化学76
查询出“张”姓学生中平均成绩大于75分的学生信息
select*fromstudentwherenamein(selectnamefromstudentwherenamelike'张%'groupbynamehavingavg(score)>75)
--3.
表名：team
ID(number型)Name(varchar2型)
1a
2b
3b
4a
5c
6c
要求:执行一个删除语句，当Name列上有相同时,只保留ID这列上值小的
例如：删除后的结果应如下:
ID(number型)Name(varchar2型)
1a
2b
5c
请写出SQL语句。
deletefromteamwhereidnotin(selectmin(id)fromteamgroupbyname)
--4.
学生表
student
s_idint
s_namevarchar2
课程表
class
c_idint
c_namevarchar2
选课表
xuanke
idint
s_idint
c_idint
查询出选了2门课以上的学生的信息
select*fromstudentwheres_idin(selects_idfromxuankegroupbys_idhavingcount(c_id)>2)
