create table tb_stu(
sid int primary key,
sname varchar(50) not null,
birthday datetime not null,
sex varchar(10) not null
);
insert into tb_stu
select 1,'李四','2017-12-25','女'union
select 2,'李云','1990-12-06','男'union
select 3,'钱电','1990-12-21','男'union
select 4,'孙风','1990-12-20','男'union
select 5,'孙七','2014-06-01','女'union
select 6,'吴兰','1992-01-01','女'union
select 7,'张三','2017-12-20','女'union
select 8,'赵雷','1990-01-01','男'union
select 9,'赵六','2013-06-13','女'union
select 10,'郑竹','1989-01-01','女'union
select 11,'周梅','1991-12-01','女'
create table TEACHER(
tid int primary key,
tname varchar(50) not null
);
insert into TEACHER
select 1,'李四'union
select 2,'王五'union
select 3,'张三'
create table course(
cid int not null,
cname varchar(50) not null,
tid int not null
);
insert into course
select 1,'数学',1union
select 2,'英语',3union
select 3,'语文',2union
select 4,'化学',1union
select 5,'物理',1union
select 6,'生物',2
create table SC(
scid int primary key ,
sid int null,
cid int null,
value float null
);
insert into SC
select 1,1,1,80.0union
select 2,1,2,90.0union
select 3,1,3,99.0union
select 4,2,1,70.0union
select 5,2,2,60.0union
select 6,2,3,80.0union
select 7,3,1,80.0union
select 8,3,2,80.0union
select 9,3,3,80.0union
select 10,4,1,50.0union
select 11,4,2,30.0union
select 12,4,3,20.0union
select 13,5,1,76.0union
select 14,5,2,87.0union
select 15,6,1,31.0union
select 16,6,3,34.0union
select 17,7,2,89.0union
select 18,7,3,98.0
--1.查看年龄在15岁以上的学生
select * from tb_stu where DATEDIFF(YY,birthday,GETDATE())>15
--2.查询所有的老师的信息
select *from TEACHER
--3.查看所有的课程的信息
select *from course
--4.查看课程与每门课程对应的老师
select a.tname,b.cid,b.cname from TEACHER a join course b on a.tid=b.tid;
--5.查看张三授课的科目
select a.tname,b.cid,b.cname from TEACHER a join course b on a.tid=b.tid where tname='张三';
--6.查看老师与负责的课程数量
select a.tname as '负责人' ,count(*) as '认课数量' from TEACHER a join course b on a.tid=b.tid group by a.tname;
--7.查看带课最少的老师(课程数量最多)
select top 1 a.tname as '负责人' ,COUNT(*) as '认课数量' from TEACHER a join course b on a.tid=b.tid group by a.tname
order by COUNT(*) ;
--8.查看每门科目与对应的考试成绩记录
select a.cid,a.cname,b.cid,b.value from course a join SC b on a.cid=b.cid ;、
select *from course where cid in(
select cid from SC
)
--9.查看没有成绩记录的科目
--10.查看具备成绩记录的科目，并计算该科目的总分与平均分
select cname as '考试科目',SUM(value)as '总分',count(*)as '考试人数',AVG(value) as '平均分'from course a join SC b on a.cid=b.cid
group by cname;
--11.查看平均分最高的科目
select top 1 cname,SUM(value),count(*),AVG(value) from course a join SC b on a.cid=b.cid
group by cname order by AVG(value) desc
--12.查看1课程中没及格的学生
select a.sid,a.sname,b.sid,b.cid,b.value from tb_stu a join SC b on a.sid=b.sid where a.sid in(
select sid from SC where value<60
)
--13.查看每一个学生参与的考试的数
select a.sname,COUNT(*)as'考试次数' from tb_stu a full join SC b on a.sid=b.sid full join course c on b.cid=c.cid group by a.sname
--14.查看参加1课程考试同时年龄超过15的学生
select a.sname from tb_stu a join SC b on a.sid=b.sid
where DATEDIFF(YY,birthday,GETDATE())>15
group by a.sname having COUNT(*)>1
--15.查看李四参加的考试中成绩最高分
select top 1 MAX(value) from SC where sid in (
select sid from tb_stu where sname='李四'
) group by value
order by MAX(value) desc
---------------------------------------------------------------
select top 1 a.sname,cid,value from tb_stu a join SC b on a.sid=b.sid where a.sid in (
select sid from SC where sname='李四'
)group by value ,a.sname,cid
order by value desc
--查询2课程成绩最高的学生的信息及分数
select * from tb_stu a join SC b on a.sid=b.sid where a.sid in (
select sid from SC where sname='李四'
)group by value ,a.sname,cid
--a.sname,cid,value
--一、设有一数据库，包括四个表：
--学生表（Student）、课程表（Course）、
--成绩表（Score）以及教师信息表（Teacher）。
--四个表的结构分别如表1-1的表（一）~表（四）所示，
--数据如表1-2的表（一）~表（四）所示。
--用SQL语句创建四个表并完成相关题目。
create table Student(
Sno char(3) not null primary key,
Sname char(8) not null,
Ssex char(2) not null,
Sbirthday datetime ,
Class char(5)
);
insert into Student
select '108','曾华','男','1977-09-01','95033'union
select '105','匡明','男','1975-10-02','95031'union
select '107','王丽','女','1976-01-23','95033'union
select '101','李军','男','1976-02-20','95033'union
select '109','王芳','女','1975-02-10','95031'union
select '103','陆君','男','1974-06-03','95031'
create table Courses (
Cno char(5) not null primary key,
Cname varchar(10) not null ,
Tno char(5) not null
);
insert into Courses
select '3-105','计算机导论','825'union
select '3-245','操作系统','804'union
select '6-166','数字电路','856'union
select '9-888','高等数学','831'
create table Score(
Sno char(3) not null,
Cno char(5) not null,
Degree decimal(4,1)
);
insert into Score
select '103','3-245','86'union
select '105','3-245','75'union
select '109','3-245','68'union
select '103','3-105','92'union
select '105','3-105','88'union
select '109','3-105','76'union
select '101','3-105','64'union
select '107','3-105','91'union
select '108','3-105','78'union
select '101','6-166','85'union
select '107','6-166','79'union
select '108','6-166','81'
create table Teach(
Tno char(3) not null primary key ,
Tname varchar(4) not null,
Tsex char(2) not null ,
Tbirthday datetime null,
Prof varchar(6) null,
Depart varchar(10) not null
);
insert into Teach
select '804','李城','男','1958-12-02','副教授','计算机系'union
select '856','张旭','男','1969-03-12','讲师','电子工程系'union
select '825','王萍','女','1972-05-05','助教','计算机系'union
select '831','刘冰','女','1977-08-14','助教','电子工程系'
--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from Student
--2、 查询教师所有的单位即不重复的Depart列。
select distinct Depart from Teach
--3、 查询Student表的所有记录。
select *from Student
--4、 查询Score表中成绩在60到80之间的所有记录。
select *from Score where Degree between '60' and '80'
--5、 查询Score表中成绩为85，86或88的记录。
select *from Score where Degree='85' or Degree='86' or Degree='88'
--6、 查询Student表中“95031”班或性别为“女”的同学记录。
select *from Student where Class='95031' or Ssex= '女';
--7、 以Class降序查询Student表的所有记录。
select *from Student order by Class desc
--8、 以Cno升序、Degree降序查询Score表的所有记录。
select *from Score order by Cno ,Degree desc
--9、 查询“95031”班的学生人数。
select count(*)as '95031班人数' from Student where Class='95031' group by Class
--10、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select *from Score where Degree =(
select MAX(Degree) from Score
)
select top 1 Sno,Cno from Score order by Degree desc
--11、查询‘3-105’号课程的平均分。
select Cno,AVG(Degree) from Score where Cno='3-105'group by Cno;
SELECT Cno,ROUND(AVG(Degree),1) FROM Score
GROUP BY Cno
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select Cno ,AVG(Degree)
from Score where Cno like '3%'
group by Cno
having COUNT(Sno)>=5;
--13、查询最低分大于70，最高分小于90的Sno列。
select * from Score
where degree>70 and degree<90
select Sno from Score group by
Sno having MAX(Degree)<90 and MIN(Degree)>70
--14、查询所有学生的Sname、Cno和Degree列。
select a.Sname,b.Cno,b.Degree from Student a join Score b on a.Sno=b.Sno
--15、查询所有学生的Sno、Cname和Degree列。
select a.Sno,b.Degree,c.Cname from Student a join Score b on a.Sno=b.Sno join Courses c on b.Cno=c.Cno
--16、查询所有学生的Sname、Cname和Degree列。
select a.Sname,b.Degree,c.Cname from Student a join Score b on a.Sno=b.Sno join Courses c on b.Cno=c.Cno
--17、查询“95033”班所选课程的平均分。
select Courses.Cno,Courses.Cname,ROUND( AVG(Degree),1)as'平均分' from
Student inner join Score
on (Student.Sno=Score.Sno) inner join Courses
on Score.Cno=Courses.Cno
where Class='95033'
group by Courses .Cname,Courses.Cno
order by Courses.Cno
--18、假设使用如下命令建立了一个grade表：
create table grade(low int,upp int,ranks char(1));
insert into grade values(90,100,'A')
insert into grade values(80,89,'B')
insert into grade values(70,79,'C')
insert into grade values(60,69,'D')
insert into grade values(0,59,'E')
--现查询所有同学的Sno、Cno和rank列。
select Sno,Cno,ranks from Score inner join grade on
Score.Degree>=grade.low and Score.Degree<=grade.upp
order by Sno;
select a.Sno,b.Cno,c.ranks from Student a full join Score b on a.Sno=b.Sno full join grade c on b.Cno=c.ranks
--19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *from Student inner join
(select *from Score where Cno='3-105' and Degree>
(select Degree from Score where Sno='109' and Cno='3-105'))
as s2 on Student.Sno=s2.Sno order by Student.Sno
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select Score.Sno,Cno,Degree,md From Score inner join(select Sno,MAX(Degree)as md from Score
group by Sno having COUNT(*) >1)
b on Score.Sno=b.Sno and Degree <md order by Sno
--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select a.Sno,a.Degree
from Score a inner join Score b on a.Cno=b.Cno and a.Degree>b.Degree
where a.Cno='3-105' and b.Sno='109' order by a.Sno
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select a.Sno ,a.Sname,a.Sbirthday from Student a join Student b on
YEAR(a.Sbirthday)=YEAR(b.Sbirthday) where b.Sno='108'
--23、查询“张旭“教师任课的学生成绩。
select Sno Degree
from Score a inner join Courses b
on a.Cno=b.Cno join Teach c on b.Tno=c.Tno where c.Tname='张旭';
--24、查询选修某课程的同学人数多于5人的教师姓名。
select distinct Tname from Score a join Courses b on
a.Cno=b.Cno join Teach c on b.Tno=c.Tno where b.Cno in (
select Cno from Score group by (Cno) having COUNT(Sno)>5);
--25、查询95033班和95031班全体学生的记录。
select * from student
where class='95033' or class='95031'
select sno,sname as s1 from Student where class='95033'
union
SELECT sno,sname as s2 FROM Student WHERE class='95031'
--26、查询存在有85分以上成绩的课程Cno.
select cno from Score where degree>85
--27、查询出“计算机系“教师所教课程的成绩表。
select Tname ,Cname ,Sname ,Degree
from Teach a join Courses b on a.Tno=b.Tno join Score c
on b.Cno=c.Cno join Student d on c.Sno=d.Sno where a.Depart='计算机系'
order by Tname,Cname,Cname,Degree desc;
--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select Tname ,Prof from Teach
where Depart='计算机系' and Prof Not in(
select distinct Prof
from Teach
where Depart='电子工程师');
--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select Cno,Sno,Degree from Score where
Cno='3-105' and Degree> any(
select Degree from Score where Cno ='3-245')
order by Degree desc;
--30、 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select Cno ,Sno Degree from Score
where Cno='3-105' and Degree >all(
select Degree from Score where Cno='3-245')
order by Degree desc;
--31、查询所有教师和同学的name、sex和birthday.
select tname name,Tsex sex,Tbirthday birthday from Teach
union
select sname,ssex,sbirthday from student
--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select tname,Tsex,Tbirthday from Teach where tsex='女'
union
select sname,ssex,sbirthday from Student where ssex='女'
--33、查询成绩比该课程平均成绩低的同学的成绩表。
select a.*from Score a join (select Cno ,AVG(Degree) as aDegree
from Score group by Cno) b on
a.Cno=b.Cno and a.Degree<b.aDegree;
--34、查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM Teach
WHERE tno IN ( SELECT DISTINCT tno FROM courses )
--35、 查询所有未讲课的教师的Tname和Depart.
SELECT tname,depart FROM Teach
WHERE tno NOT IN ( SELECT DISTINCT tno FROM Courses )
--36、查询至少有2名男生的班号。
select Class ,COUNT(1) as boyCount from Student
where Ssex='男'
group by Class having COUNT(1) >=2;
--37、查询Student表中不姓“王”的同学记录。
select *from Student where Sname not like '王%';
--38、查询Student表中每个学生的姓名和年龄。
select Sname ,YEAR(getdate())-YEAR(sbirthday) as Sage from Student;
--39、查询Student表中最大和最小的Sbirthday日期值。
select MIN(Sbirthday),MAX(sbirthday) from Student;
--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select *from Student order by Class Desc ,Sbirthday asc;
--41、查询“男”教师及其所上的课程。
select a.Tname ,b.Cname from Teach a join Courses b on a.Tno=b.Tno
where a.Tsex='男'
--42、查询最高分同学的Sno、Cno和Degree列。
select *from Score group by Cno having Degree=MAX(Degree)
--43、查询和“李军”同性别的所有同学的Sname.
select a.Sname from Student a join Student b on a.Ssex=b.Ssex where b.Sname='李军';
--44、查询和“李军”同性别并同班的同学Sname.
select a.Sname from Student a join Student b on a.Sno=b.Sno and a.Class=b.Class
where b.Sname='李军';
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT * FROM Score
WHERE cno=(SELECT cno FROM Courses WHERE cname='计算机导论')
ORDER BY Degree DESC;
--tb-user 用户表
create table tb_users
(
uid float,
uname nvarchar(max),
upwd nvarchar(max),
usf nvarchar(max),
uage int null,
usex varchar(3) null
);
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (1, N'黎明', N'111', N'前台',23,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (2, N'刘德华', N'222', N'前台',63,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (3, N'张学友', N'333', N'经理',83,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (10, N'张三', N'111', N'前台',43,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (6, N'李四', N'111', N'经理',26,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (20, N'yishanjing', N'123', N'前台',23,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (15, N'要', N'aa', N'老板',23,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (16, N'小李子', N'111', N'前台',13,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (20, N'yishanjing', N'123', N'前台',53,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (10, N'name', N'pwd', N'sf',23,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',15,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',46,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',73,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',24,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',64,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (100, N'aa', N'111111', N'老板',23,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',11,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',23,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',10,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',23,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',46,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',13,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',73,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',23,'女');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',43,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',10,'男');
INSERT INTO tb_users (uid, uname, upwd, usf,uage,usex) VALUES (200, N'众', N'aa', N'前台',19,'女');
--tb-客房类型表
create table tb_type
(
tid float,
tname nvarchar(max),
tmenu nvarchar(max)
);
INSERT INTO tb_type (tid, tname, tmenu) VALUES (1, N'总统套房', N'最贵的');
INSERT INTO tb_type (tid, tname, tmenu) VALUES (2, N'豪华间', null);
INSERT INTO tb_type (tid, tname, tmenu) VALUES (3, N'单间', null);
INSERT INTO tb_type (tid, tname, tmenu) VALUES (4, N'双人间', null);
INSERT INTO tb_type (tid, tname, tmenu) VALUES (5, N'三人间', null);
--tb——客房表o
create table tb_kefang
(
fid float,
tid float,
fprice float,
fkzrs float,
fyzrs float,
fzhutai nvarchar(max),
fcnt float,
fmenu nvarchar(max)
);
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (101, 5, 50, 3, 3, N'住满', 100, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (102, 5, 50, 3, 3, N'住满', 50, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (103, 5, 60, 3, 2, N'未住满', 80, N'电视,热水器,风扇');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (104, 5, 60, 0, 0, N'维修', 30, N'电视,热水器,风扇');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (105, 5, 60, 3, 0, N'空房', 100, N'电视,热水器,风扇');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (201, 4, 80, 2, 2, N'住满', 100, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (202, 4, 80, 2, 0, N'空房', 200, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (203, 4, 100, 2, 0, N'空房', 201, N'电视,热水器,空调');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (204, 4, 100, 2, 0, N'空房', 20, N'电视,热水器,空调');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (205, 4, 120, 2, 1, N'未住满', 100, N'电视,热水器,电话,空调');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (301, 3, 150, 1, 1, N'住满', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (302, 3, 150, 1, 0, N'空房', 500, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (303, 3, 150, 1, 0, N'空房', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (304, 3, 150, 0, 0, N'维修', 50, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (305, 3, 150, 1, 1, N'住满', 50, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (401, 2, 200, 1, 1, N'住满', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (402, 2, 200, 1, 1, N'住满', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (403, 2, 180, 1, 0, N'空房', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (404, 2, 200, 1, 1, N'住满', 500, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (405, 2, 200, 0, 0, N'维修', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (501, 5, 1000, 1, 0, N'空房', 500, N'无');
--tb_登记表
create table tb_dengji
(
did float,
kname nvarchar(255),
fid float,
sfz nvarchar(255),
yajin float,
ruzhu datetime,
tufang datetime
);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (1, N'小猫', 101, N'123', 200, N'2007-05-12 07:22:18.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (2, N'阿狗', 101, N'123', 200, N'2007-05-14 10:22:39.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (3, N'阿牛', 101, N'123', 200, N'2007-05-15 14:23:02.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (4, N'王昭君', 102, N'123', 200, N'2007-05-15 10:23:31.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (5, N'西施', 102, N'123', 200, N'2007-05-15 03:23:46.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (6, N'昭婵', 102, N'123', 200, N'2007-05-13 00:23:57.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (8, N'鲁迅', 103, N'123', 300, N'2007-05-15 00:24:31.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (9, N'刘德华', 201, N'111', 500, N'2007-05-15 00:25:10.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (10, N'张学友', 201, N'111', 500, N'2007-05-12 10:25:22.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (11, N'成龙', 205, N'222', 800, N'2007-05-15 00:25:57.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (12, N'李世民', 301, N'333', 1000, N'2007-05-11 02:26:35.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (13, N'朱元璋', 305, N'444', 1000, N'2007-05-15 00:27:09.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (14, N'张曼玉', 401, N'55', 100, N'2007-05-15 00:27:24.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (15, N'张柏芝', 402, N'777', 1000, N'2007-05-14 11:10:12.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (16, N'小猫', 102, N'123', 200, N'2007-03-12 07:22:00.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (17, N'小羊', 201, N'222', 100, N'2007-03-14 00:00:00.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (18, N'喜来乐', 201, N'222', 100, N'2007-04-01 00:31:04.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (19, N'小李子', 404, N'111', 500, N'2007-05-15 00:00:00.000', null);
--二、在数据库中建表如下，并根据实际要求建立约束
---四、建立基于客户类型表以及客户表的视图
create view view_a
as
select *from tb_type
create view view_k
as
select *from tb_users
select *from view_k
select *from view_a
--五、要求用代码创建视图，要求得到如下数据：2012年一月入住的所有姓“张”的女性
create view view_g
as
select *from tb_dengji
select *from view_g
select *from view_c where kname like '%张%' and YEAR(ruzhu)='2007'
--六、要求在作业六视图的基础上，查询出年龄在20-30之间的客户
select *from view_k where uage between 10 and 23
--七、要求用代码创建视图，要求如下：要求查询出可以住人的三人间
create view view_m
as
select *from tb_kefang
select *from view_m where fkzrs=3
--八、要求用代码创建视图，要求如下：查询出名字当中含有“豆”字的用户，并且密码中的1、2、3分别用a、b、c代替
select *from view_k where uname like '%豆%'
--九、要求用代码创建视图，要求如下：要求所有没有住满，并且只能住一个人的客房
select *from view_m where fkzrs-fyzrs!=0 and fkzrs=1
--十、要求用代码创建视图，要求如下：要求所有房间的入住统计，如果为null的话则用0代替
select ISNULL(ruzhu,0) from view_g
create table changk(
ckid varchar(10) primary key,
ckcs varchar(40) not null,
rea int not null
);
insert into changk
select 'WH1','北京',370 union
select 'WH2','上海',500 union
select 'WH3','广州',200 union
select 'WH4','武汉',400
create table ding(
zhigong varchar(10) not null,
gong varchar(10) not null,
dgdh varchar(50) not null primary key,
tims datetime null
);
insert into ding
select 'E3','S7','OR67','2021-06-23'union
select 'E1','S4','OR73','2021-07-28'union
select 'E5','S4','OR76','2021-05-25'union
select 'E6','S6','OR77','2021-05-26'union
select 'E3','S4','OR79','2021-06-13'union
select 'E1','S2','OR80','2021-08-29'union
select 'E3','S3','OR90','2021-09-01'union
select 'E3','S3','OR91','2021-07-13'
create table gysb(
gysh varchar(10) not null primary key,
gname varchar(50) not null,
address varchar(100) not null
);
insert into gysb
select 'S2','名硕电子公司','苏州'union
select 'S3','振华电子厂','西安'union
select 'S4','华通电子公司','北京'union
select 'S6','607厂','郑州'union
select 'S7','爱华电子厂','北京'
create table zgb(
cgh varchar(10) not null,
zgh varchar(10) primary key not null,
money int not null
);
insert into zgb
select 'WH2','E1',1220union
select 'WH1','E3',1210union
select 'WH2','E4',1250union
select 'WH3','E5',1230union
select 'WH1','E6',1250
--使用SQL语句完成：
--DDL
--1.写出创建上述表的语句(并设定好主外键，以及其他约束)
--命令：
--2.给出插入上述数据的insert语句
--命令：
--单表查询
--3.检索职工关系中的所有信息
--结果：
SELECT *from zgb
--4.检索供应商关系中的所有信息
select *from gysb
--5.检索六月之后的所有订单
select *from ding where MONTH(tims)>5
--6.检索面积大于400的仓库
select *from changk where rea>400
--7.检索哪些职工的工资多于1210
select *from zgb where money>1210
--8.检索仓库是“WH1”或“WH2”并且面积大于400的城市
select ckcs from changk where rea>400 and ckid='WH1'or ckid='WH2'
--9.找出仓库面积在400到600的仓库
select *from changk where rea between 400 and 600
--10.找出名中包含 “厂”的所有供应商的名
select gname as '供应商名' from gysb where gysb.gname like '%厂%'
--11.找出不在西安的供应商
select *from gysb where not address='西安'
--12.找出不在北京的仓库
select *from changk where changk.ckcs!='北京'
--13.按工资降序排列出所有职工的信息
select *from zgb order by money desc;
--14.先按仓库号升序排列，再按工资降序排列
select *from zgb order by cgh , money desc
--15.在仓库表中统计一下有几个仓库
select COUNT(*)as '仓库数' from changk
-- 116.在职工表中统计一下有几个仓库
--17.求总的仓库面积
select SUM(rea)as'总面积' from changk
--18.每个职工的订单数
select zhigong as'职工号',COUNT(*)as'订单数' from ding group by ding.zhigong
--19.订单数大于3的职工
select zhigong as'职工号',COUNT(*)as'订单数' from ding group by ding.zhigong having COUNT(*)>3
--20.找出在面积大于400的仓库中工作的职工
select a.zgh from zgb a join changk b on a.cgh=b.ckid where rea>400
--21.找出在北京工作的职工和他们的工资情况
select zgh,money from zgb a join changk b on a.cgh=b.ckid where ckcs='北京'
--22.找出工资大于1215的职工和他们所在的城市
select ckcs,zgh from zgb a join changk b on a.cgh=b.ckid where money>1215
--23.哪些城市至少有一个订单（从仓库角度考虑）
select distinct a.ckcs from changk a join zgb b
on a.ckid =b.cgh join ding c
on b.zgh=c.zhigong group by a.ckcs having COUNT(*)>=1
select distinct ckcs from ding join zgb on ding.zhigong=zgb.zgh join changk on zgb.cgh=changk.ckid
--24.找出没有任何订单的城市
select c.ckcs from ding a join zgb b
on a.zhigong=b.zgh full join changk c
on b.cgh=c.ckid group by c.ckcs having COUNT(*)=1
select ckcs from changk except select distinct ckcs from ding join zgb on ding.zhigong=zgb.zgh join changk on zgb.cgh=changk.ckid
--25.找出和E4有同样工资的所有职工
select *from zgb where money=(
select money from zgb where zgb.zgh='E4'
)
--26.找出仓库面积大于400的仓库的所有职工
select *from zgb where zgb.cgh=(
select changk.ckid from changk where rea>400
)
--27.找出供应商在西安的职工和他们的工资情况
select address,money from gysb a join ding b on a.gysh=b.gong join zgb c
on b.zhigong=c.zgh where address='西安' group by address,money
--28.找出不在北京仓库里工作的职工
select zgh,money from zgb a join changk b
on a.cgh=b.ckid
except select zgh,money from
zgb join changk c on zgb.cgh=c.ckid
where c.ckcs='北京'
--29.找出不在北京仓库里工作的职工
select zgh ,money from zgb join changk on zgb.cgh=changk.ckid where changk.ckcs='北京'
--30.求广州和上海仓库职工的总工资
select SUM(money) as '总工资' from zgb join changk on zgb.cgh=changk.ckid
--31.求所有职工工资都大于1210的仓库的平均面积
select AVG(rea) from changk join zgb on changk.ckid=zgb.cgh where money>1210
select AVG(rea)as'平均面积' from
(select cgh from zgb except select cgh from zgb where money<1210)
a join changk on a.cgh=changk.ckid
--32.求上海仓库中职工的最高工资
select MAX(money)as'最高工资' from zgb join changk on zgb.cgh=changk.ckid where ckcs='上海'
--33.订单数大于或等于2的职工的工资
select a.money from (select zgb .zgh,money from zgb join ding on zgb.zgh=ding.zhigong
group by zgb.zgh,money having COUNT(*)>=2)a
create table stuInfo
(
stuNo varchar(6) not null primary key,
stuName varchar(10) not null,
stuSex varchar(2) not null,
stuAge int not null,
stuSeat int not null identity (1, 1),
strAddress varchar(255) default ('地址不详')
)
--创建学生成绩表
create table stuMarks
(
ExamNo varchar(7) not null primary key,
stuNo varchar(6) not null references stuInfo (stuNo),
writtenExam int null,
LabExam int null
)
--往学生信息表内插入测试数据
insert into stuInfo(stuNo, stuName, stuSex, stuAge, strAddress)
select 's25301', '张秋丽', '男', 18, '北京海淀'
union
select 's25303', '李斯文', '女', 22, '河阳洛阳'
union
select 's25302', '李文才', '男', 85, '地址不详'
union
select 's25304', '欧阳俊雄', '男', 28, '新疆'
union
select 's25318', '梅超风', '女', 23, '地址不详'
--往学生成绩表内插入测试数据
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59
union
select 's271813', 's25302', 63, 91
union
select 's271816', 's25301', 90, 83
union
select 's271817', 's25318', 63, 53
--聚合函数的使用
select COUNT(*) from stuInfo
--表关联操作
select *from stuInfo a join stuMarks b on a.stuNo=b.stuNo
--查看年龄比“李斯文”大的学员
select * from stuInfo where stuAge>(
select stuAge from stuInfo where stuName='李斯文'
)
--查看性别和“李斯文”一致的学员
select *from stuInfo where stuSex=(
select stuSex from stuInfo where stuName='李斯文'
)
--查删除别和“李斯文”一致的学员
delete from stuInfo where stuSex=(
select stuSex from stuInfo where stuName='李斯文'
)
--查询年龄最大的学生信息
select *from stuInfo where stuAge=(
select MAX(stuAge) from stuInfo
)
--查询年龄最小的学生信息
select *from stuInfo where stuAge=(
select MIN(stuAge) from stuInfo
)
--查询笔试成绩成绩最高的学生
select *from stuMarks where writtenExam=(
select MAX(writtenExam) from stuMarks
)
--查询笔试成绩大于全班笔试平均成绩的学生记录
select * from stuMarks a join stuInfo b on a.stuNo=b.stuNo where writtenExam>(
select AVG(writtenExam) from stuMarks
)
--查询笔试成绩在70分以上的学生信息(禁止联表)
select *from stuInfo where stuNo in(
select stuNo from stuMarks where writtenExam>70
)
--查看那些人没有考试
select *from stuInfo where stuNo not in (
select stuNo from stuMarks
)
create view a
as
select *from stuInfo
select *from a where stuName like '%丽'
--like % 可以放任意的字
--like _ 只可以放一个字
select *from stuInfo where stuName like '张__';
-- 想知道每个学生对应的成绩
create view view_a
as
select * from stuInfo a inner join stuMarks b
on a.stuNo=b.stuNo;
create view view_b
as
select * from stuMarks;
create view view_c
as
select a.*,labexam, writtenexam, examno
from stuInfo a full join stuMarks b
on a.stuNo=b.stuNo;
select *from view_c;
select * from view_c where stuName='李斯文';
create view view_d
as
select stuSex,count(*) as mount
from stuInfo group by stuSex;
create table region(
id varchar(20) primary key, --主键,区域编号
name varchar(20) not null, --区域名称
pid varchar(20) not null --区域附属编号 0省份
);
insert into region
select 's2309','广东省','0'union
select 's2098','湖南省' ,'0'union
select 's2033','广西省' ,'0'union
select 's2034','永州市' ,'s2098'union
select 's2056','长沙市' ,'s2098'union
select 's2012','广东市' ,'s2309'union
select 's2089','东莞市' ,'s2309' union
select 's2037','怀化市' ,'s2098'
--查询所有的省分
select *from region where pid='0'
---- 查询湖南省下素有的市
select *from region where pid='s2098'
select *from region where pid in(
select id from region where name ='湖南省'
)
select a.name,count(b.name)
from region a full join region b
on a.id=b.pid where a.name like '%省%'
group by a.name;
-- 查询性张的人: 张x,张xx,张xxx
select * from stuInfo where stuName like '张%';
-- 查询性张的人: 张xx
select * from stuInfo where stuName like '张__';
-- 查询以丽结尾的人: xx丽
select * from stuInfo where stuName like '%丽';
-- 查询名字带秋的人: 秋xx,x秋x,xx秋
select * from stuInfo where stuName like '%秋%';