📄 sql练习.sql
字号:
--drop table student
create table student(id int,name char(10))
insert into student(id,name) values(10,'Tom')
insert into student(id,name) values(20,'John')
insert into student(id,name) values(30,'Bob')
insert into student(id,name) values(40,'Helen')
insert into student(id,name) values(50,'Alan')
--select * from student
--drop table score
create table score(id int,score int)
insert into score(id,score) values(10,80)
insert into score(id,score) values(20,88)
insert into score(id,score) values(30,77)
insert into score(id,score) values(40,75)
insert into score(id,score) values(50,null)
--select * from score
--第一题、找出姓名为'tom'的学生的成绩
/*
name score
---------- -----------
Tom 80
*/
select name, score from student, score
where student.id=score.id and student.name='Tom'
--第二题、找出最高分的姓名和分数
/*
name score
---------- -----------
Tom 80
*/
select name, score from student, score
where student.id=score.id and
score.score=(select max(score) from score)
--第三题、看成绩最高的前2名学员姓名和成绩
/*
id name score
----------- ---------- -----------
20 John 88
10 Tom 80
*/
select top 2 student.id,name, score from student, score
where student.id=score.id
order by score.score desc
--第四题、查看所有学员的成绩
/*
name score
---------- -----------
Tom 80
John 88
Bob 77
Helen 75
Alan NULL
*/
select name, score from student, score
where student.id=score.id
--第五题、显示学员与平均分之间的关系
/*
name 描述
---------- ----------
Tom 等于平均分
John 大于平均分
Bob 小于平均分
Helen 小于平均分
Alan 缺考
*/
declare @avg int
select @avg=avg(score) from score
select name,
case when score=@avg then '等于平均分'
when score>@avg then '大于平均分'
when score is null then '缺考'
when score<@avg then '小于平均分'
end as 描述
from student ,score
where student.id=score.id
--第六题、将Tom的分改成98分
/*
id score name
----------- ----------- ----------
10 98 Tom
20 88 John
40 75 Helen
30 77 Bob
50 NULL Alan
*/
update score set score=98
where id=(select id from student where name='tom')
select score.id,score,name from student, score
where student.id=score.id
--第七题、把最高分的学员从student表删除
/*
select * from student --结果
id name
----------- ----------
20 John
30 Bob
40 Helen
50 Alan
*/
delete student
where id=(select id from score
where score=(select max(score)from score))
select * from student
--第八题、在score表中删除最高分记录
/*
select * from score--结果
id score
----------- -----------
20 88
30 77
40 75
50 NULL
*/
delete score where score=
(select max(score) from score)
select * from score
--第九题、将小于80分的学员信息和成绩写入到student_score中（使用select ..into命令）
/*
select * from student_score --结果
id name score
----------- ---------- -----------
30 Bob 77
40 Helen 75
*/
select score.id,name,score into student_score
from student,score
where student.id=score.id and score.score<80
select * from student_score
--第十题、在学员(student)表中增加一列（列名score），并将学员成绩写入到这列中
--使用一个update命令
/*
select * from student
id name score
----------- ---------- -----------
20 John 88
30 Bob 77
40 Helen 75
50 Alan NULL
*/
alter table student add score int
update student set score=
(select score.score from score
where student.id=score.id)
select *from student
--创建雇员表
create table emp
(id numeric(5) not null primary key,name varchar(20),phone varchar(30),
address varchar(50),mail varchar(30))
go
--创建销售表
drop table sales
create table sales
(orderid numeric(5) primary key,id numeric(5),product varchar(30),
saledate datetime default getdate(),counts numeric(5),customer varchar(30),
note varchar(1000),
constraint sales_id_fk foreign key(id) references emp(id))
--插入emp纪录
insert into emp values(111,'aaa','010-111','bj','aaa@easthome.com')
insert into emp values(222,'bbb','020-222','sh','bbb@hotmail.com')
insert into emp values(333,'ccc','022-333','sy','ccc@easthome.com')
insert into emp values(444,'ddd','010-444','bj','ddd@easthome.com')
--插入sales纪录
insert into sales values(1,111,'disk','2003-03-11',5,'sina',null)
insert into sales values(2,111,'cpu','2003-03-11',10,'sina',null)
insert into sales values(3,111,'disk','2003-03-22',15,'sohu',null)
insert into sales values(4,222,'disk','2003-03-15',3,'sohu',null)
insert into sales values(5,222,'cpu','2003-03-16',15,'yeah',null)
insert into sales values(6,222,'cpu','2003-03-22',2,'google',null)
insert into sales values(7,333,'disk','2003-03-12',11,'google',null)
insert into sales values(8,333,'cpu','2003-03-15',2,'google',null)
--查看emp结果
select * from emp
--查看sales结果
select * from sales
--1、 查看每个产品总共销售的数量
/*PRODUCT SUM(COUNTS)
------------------------------ -----------
cpu 29
disk 31
*/
select product as productname,sum(counts) as total
from sales
group by product
--2、 查看所有雇员的销售记录（显示雇员姓名、产品、销售日期和数量）
/*
NAME PRODUCT SALEDATE COUNTS
-------------------- ------------------------------ ---------- ----------
aaa disk 2003-03-11 5
aaa cpu 2003-03-11 10
aaa disk 2003-03-22 12
bbb disk 2003-03-15 3
bbb cpu 2003-03-16 15
bbb cpu 2003-03-22 2
ccc disk 2003-03-12 11
ccc cpu 2003-03-15 2
ddd
*/
select name ,product,saledate ,counts
from emp inner join sales
on emp.id=sales.id
--3、 查看disk的销售纪录排行榜（显示销售人员姓名、销售日期和数量），降序
/*
NAME SALEDATE COUNTS
-------------------- ---------- ----------
aaa 2003-03-22 12
ccc 2003-03-12 11
aaa 2003-03-11 5
bbb 2003-03-15 3
*/
select name,saledate,counts
from emp inner join sales
on sales.product='disk' and emp.id=sales.id
order by sales.counts desc
--4、 查看总共销售硬盘最多的雇员是谁？并且显示这个销售人员所销售硬盘的总数量（可以考虑使用临时表）
/*
NAME MAXC
-------------------- ----------
aaa 17
*/
select name,sum(counts) as maxc into #t
from sales,emp
where sales.id=emp.id and sales.product='disk'
group by name
select * from #t where maxc=(select max(#t.maxc) from #t)
--5、 查询哪个雇员没有销售记录
/*
ID NAME PHONE
---------- -------------------- ------------------------------
ADDRESS
--------------------------------------------------
MAIL
------------------------------
444 ddd 010-444
bj
ddd@easthome.com
*/
select distinct emp.id,name,phone,address,mail
from emp,sales
where emp.id not in (select sales.id from sales)
--6、 看每个产品销售数量最多的销售人员是谁？并且同时显示销售数量！(可使用临时表)
/*
NAME PRODUCT SC
-------------------- ------------------------------ ----------
aaa disk 17
bbb cpu 17
*/
drop table #tt
select sales.id,product,sum(counts) as sc into #tt
from sales
group by sales.product,sales.id
drop table #ttt
select emp.name,product,sc into #ttt
from emp,#tt
where emp.id=#tt.id
select *from #ttt
select name,product,sc from #ttt
where sc in (select max(sc) from #ttt group by product)
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -