SQL语句练习07

目录

一、建表

二、常见的SQL语句练习

一、建表

• 一、建立如下职工表 𝐸𝑀𝑃(𝐸#, 𝐸𝑁𝐴𝑀𝐸, 𝐴𝐺𝐸, 𝑆𝐸𝑋, 𝐸𝐶𝐼𝑇𝑌) (命名格式“姓名拼音_三位学号_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)