目录
一、建表
- 一、建立如下职工表 𝐸𝑀𝑃(𝐸#, 𝐸𝑁𝐴𝑀𝐸, 𝐴𝐺𝐸, 𝑆𝐸𝑋, 𝐸𝐶𝐼𝑇𝑌) (命名格式“姓名拼音_三位学号_week9emp”,如LBJ_023_week9emp),其属性分别表示职工工号、姓名、年龄、性别和籍贯。注意要建表时要定义主键和外键。贴出建表语句
create table LYL_116_week9emp(E# varchar(5),ENAME varchar(10),AGE int,
SEX varchar(2),ECITY varchar(20) primary key(E#))
- 二、建立如下公司表 𝐶𝑂𝑀𝑃(𝐶#, 𝐶𝑁𝐴𝑀𝐸, 𝐶𝐼𝑇𝑌) (命名格式“姓名拼音_三位学号_week9comp”,如LBJ_023_week9comp),其属性分别表示公司编号、公司名称和公司所在城市。注意要建表时要定义主键和外键。贴出建表语句
create table LYL_116_week9comp(C# varchar(5),CNAME varchar(50),
CITY varchar(20),primary key(C#))
- 三、建立如下工作表 𝑊𝑂𝑅𝐾𝑆(𝐸#, 𝐶#, 𝑆𝐴𝐿𝐴𝑅𝑌) (命名格式“姓名拼音_三位学号_week9works”,如LBJ_023_week9works),其属性分别表示职工工号、工作的公司编号和工资。注意要建表时要定义主键和外键。贴出建表语句
create table LYL_116_week9works(E# varchar(5),C# varchar(5),SALARY int,
primary key(E#,C#),
foreign key(E#) references LYL_116_week9emp(E#),
foreign key(C#) references LYL_116_week9comp(C#))
- 四、根据自己喜好和后面查询的需要,在三张表中插入数据。贴出插入语句后三张表的数据的查询结果。(这里我给出了我根据自己喜好插入的数据,读者这里可以结合下面查询需要自行发挥喔)
insert into LYL_116_week9comp values('C0','0点点','北京')
insert into LYL_116_week9comp values('C2','2点点','上海')
insert into LYL_116_week9comp values('C4','4点点','广州')
insert into LYL_116_week9comp values('C8','5点点','深圳')insert into LYL_116_week9emp values('E0','喜羊羊',15,'男','青青草原')
insert into LYL_116_week9emp values('E1','沸羊羊',16,'男','青青草原')
insert into LYL_116_week9emp values('E2','懒羊羊',14,'男','青青草原')
insert into LYL_116_week9emp values('E3','慢羊羊',56,'男','青青草原')
insert into LYL_116_week9emp values('E4','羊果果',56,'男','青青草原')
insert into LYL_116_week9emp values('E5','刀羊',57,'男','青青草原')
insert into LYL_116_week9emp values('E6','妹爷',73,'男','上海')
insert into LYL_116_week9emp values('E7','陈翔',39,'男','上海')
insert into LYL_116_week9emp values('E8','冷檬',28,'女','上海')
insert into LYL_116_week9emp values('E9','球球',27,'女','上海')
insert into LYL_116_week9emp values('E10','吴妈',58,'女','上海')
insert into LYL_116_week9emp values('E11','蘑菇头',30,'男','上海')insert into LYL_116_week9works values('E0','C0',3000)
insert into LYL_116_week9works values('E1','C2',2000)
insert into LYL_116_week9works values('E2','C4',5000)
insert into LYL_116_week9works values('E3','C8',6000)
insert into LYL_116_week9works values('E4','C2',4000)
insert into LYL_116_week9works values('E5','C2',1000)
insert into LYL_116_week9works values('E6','C2',8000)
insert into LYL_116_week9works values('E7','C2',2000)
insert into LYL_116_week9works values('E8','C4',5000)
insert into LYL_116_week9works values('E9','C4',3000)
insert into LYL_116_week9works values('E10','C2',6000)
insert into LYL_116_week9works values('E11','C4',4000)
insert into LYL_116_week9works values('E2','C8',8000)
insert into LYL_116_week9works values('E8','C8',6000)
insert into LYL_116_week9works values('E6','C0',5000)
insert into LYL_116_week9works values('E4','C0',3000)
insert into LYL_116_week9works values('E4','C8',4000)
insert into LYL_116_week9works values('E5','C0',9000)
二、常见的SQL语句练习
- 1)检索超过50岁的男职工的工号和姓名
select emp.E#,emp.ENAME from LYL_116_week9emp
emp where emp.AGE>50
- 2)检索在单个公司工资超过5000元的男性职工的工号和姓名
select emp.E#,emp.ENAME from
LYL_116_week9emp emp,LYL_116_week9works work1,
(select w.E# from LYL_116_week9works w
group by w.E# having count(w.E#)=1)work
where emp.E#=work.E# and work1.E# = work.E#
and work1.SALARY>5000 and emp.SEX='男'
- 3)假设每个职工可以在多个公司工作,检索至少在编号为C4和C8的公司兼职的职工工号和姓名
select emp.E#,emp.ENAME from LYL_116_week9emp emp,
(select work.E# from
LYL_116_week9works work
where work.C#='C4' or work.C#='C8'
group by work.E# having count(work.E#)>1)work1
where work1.E# = emp.E#
- 4)检索在“2点点”公司工作、工资超过3500元的男性职工的工号和姓名
select work.E#,emp.ENAME from
LYL_116_week9comp comp,LYL_116_week9works work,
LYL_116_week9emp emp where comp.CNAME='2点点' and
work.C#=comp.C# and emp.E#=work.E# and work.SALARY>3500
and emp.SEX='男'
- 5)检索每个职工的工号、兼职公司数目和工资总数
select work.E#,count(work.E#)'兼职公司数',sum(work.SALARY)'总工资'
from LYL_116_week9works work group by work.E#
- 6)检索至少在工号为E6的职工兼职的所有公司工作的职工工号
select distinct work1.E# from LYL_116_week9works work1
where not exists(
select * from LYL_116_week9works work2
where work2.E#='E6'
and not exists(
select * from LYL_116_week9works work3
where work3.E#=work1.E#
and work3.C#=work2.C#)
)
- 7)检索2点点公司中低于本公司平均工资的职工工号和姓名
select w.E#,e.ENAME from LYL_116_week9works w,LYL_116_week9emp e,
(select avg(work.SALARY)'avgS' from LYL_116_week9comp comp,LYL_116_week9works work
where comp.CNAME='2点点' and comp.C#=work.C# )com,
(select C# from LYL_116_week9comp where CNAME='2点点')c
where w.C#=c.C# and e.E#=w.E# and w.SALARY<com.avgS
- 8)在每一公司中为28岁以上的职工加薪100元(若职工为多个公司工作,可重复加)
update LYL_116_week9works set SALARY = SALARY+100 where E# in
(select e.E# from LYL_116_week9emp e where e.AGE>28)