sql语句实验练习

create database Stud_y
on
(
  name=Stud_2021032479,
  filename='D:\Stud.mdf ',
  size=79,
  maxsize=500,
  filegrowth=79
)


log on
(
   name=Stud_Log,
   filename='D:\Stud.ldf',
   size=5,
   maxsize=500,
   filegrowth=79
)



create table Choice79
(
    s_no char(6),
	course_no char(5),
	score numeric(6,1)
)

create table Teacher79
(
    t_no char(6) primary key,
	t_name varchar(10) not null,
	t_sex char(2) check(t_sex='男'or t_sex='女'),
	t_birthday datetime,
	t_title char(10)
)

create table Teaching79
(
    course_no char(5),
	t_no char(6)
)


insert into Student79 values
--('991101','js9901','张彬','男','1981-10-1'),
--('991102','js9901','王蕾','女','1981-8-8'),
('991103','js9901','李建国','男','1981-4-5'),
('991104','js9901','李平方','男','1981-5-12'),
('991201','js9902','陈东辉','男','1980-2-8'),
('991202','js9902','葛鹏','男','1979-12-23'),
('991203','js9902','藩桃芝','女','1980-2-6'),
('991204','js9902','姚一峰','男','1981-5-7'),
('001101','js0001','宋大方','男','1980-4-9'),
('001102','js0001','许辉','女','1978-8-1'),
('001201','js0002','王一山','男','1980-12-4'),
('001202','js0002','牛莉','女','1981-6-9'),
('002101','xx0001','李丽丽','女','1981-9-19'),
('002102','xx0001','李王','男','1980-9-23');




insert into Class79 values
--('js9901','计算机99-1','计算机','计算机系'),
('js9902','计算机99-2','计算机','计算机系'),
('js0001','计算机00-1','计算机','计算机系'),
('js0002','计算机00-2','计算机','计算机系'),
('xx0001','信息00-1','信息','信息系'),
('xx0002','信息00-2','信息','信息系');

insert into Choice79 values
('991101','01001',88.0),
('991102','01001',null),
('991103','01001',91.0),
('991104','01001',78.0),
('991201','01001',67.0),
('991101','01002',90.0),
('991102','01002',58.0),
('991103','01002',71.0),
('991104','01002',85.0);

insert into Teacher79 values
('000001','李英','女','1964-11-3','讲师'),
('000002','王大山','男','1955-3-7','副教授'),
('000003','张朋','男','1960-10-5','讲师'),
('000004','陈为军','男','1970-3-2','助教'),
('000005','宋浩然','男','1966-12-4','讲师'),
('000006','许红霞','女','1951-5-8','副教授'),
('000007','徐永军','男','1948-4-8','教授'),
('000008','李桂菁','女','1940-11-3','教授'),
('000009','王一凡','女','1962-5-9','讲师'),
('000010','田峰','男','1972-11-5','助教');

insert into Course79 values
('01001' , '计算机基础' , '3') ,
('01002' , '程序设计语言'  , '5') ,
('01003' , '数据结构' , '6') ,
('02001' , '数据库原理与应用' , '6') ,
('02002' , '计算机网络' , '6') ,
('02003' , '微机原理与应用' , '8') ;

insert into Teaching79 values
('01001' , '000001') ,
('01002' , '000002') ,
('01003' , '000002') ,
('02001' , '000003') ,
('02002' , '000004') ,
('01001' , '000005') ,
('01002' , '000006') ,
('01003' , '000007') ,
('02001' , '000007') ,
('02002' , '000008') ;


--实验二
--（1）查询所有同学的基本信息
select * from Student79;

--（2）查询所有同学，要求显示其学号s_no，姓名s_name
select s_no,s_name from Student79;

--（3）查询所有男同学，要求显示其学号s_no，姓名s_name,出生日期s_birthday
select s_no,s_name,s_birthday from Student79 where s_sex='男';

--（4）查询所有出生日期在“1980-01-01”前的女同学，要求显示其学号s_no、姓名s_name、性别s_sex、出生日期s_birthday
select s_no,s_name,s_sex,s_birthday from Student79 where s_sex='女'and year(s_birthday)<1980;

--（5）查询所有“李”姓的男同学，要求显示其学号s_no,姓名s_name,性别s_sex,出生日期s_birthday
select s_no,s_name,s_sex,s_birthday from Student79 where s_sex='男'and  s_name like '李%';

--（6）查询所有姓名中含有“一”字的同学，要求显示其学号s_no,姓名s_name
select s_no,s_name from Student79 where  s_name like '%一%';

--（7）查询所有职称不是“讲师”的教师，要求显示其教师号，姓名，职称
select t_no,t_name,t_title from Teacher79 where t_title != '讲师';

--（8）查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号s_no
select s_no from Choice79 where score is null;

--（9）查询所有考试不及格的学生，要求显示出这些学生的学号、成绩。并按成绩降序排列
select s_no,score from Choice79 order by score desc;
select s_no,score from Choice79 where score<60;

--（10）查询出课程号为01001、01002、01003的所有课程，要求显示出出课程号、课程名称
select course_no,course_name from Course79 where course_no in (01001,01002,01003);

--（11）查询所有在1970年前出生的教师，显示教师号、姓名、出生日期
select t_no,t_name,t_birthday from Teacher79 where year(t_birthday)<1970;

--（12）查询出各个课程号course_no及相应选课人数
select course_no,count(*)人数 from Choice79 group by course_no;

--（13）查询出教授两门以上课程的教师号t_no
select t_no 教师号  from Teaching79 group by t_no having (count(*)>=2);

--（14）查询出选修了01001课程的学生平均分数、最低分数及最高分数
select AVG(score)平均分数,MIN(score)最低分数,MAX(score)最高分数 from Choice79 
where course_no = '01001';

--（15）查询1960年以后出生的，职称为讲师的教师姓名、出生日期、并按出生日期升序排列
	select t_name,CONVERT(varchar(100), t_birthday, 23)as 日期 from Teacher79  where year(t_birthday)>1960 and t_title ='讲师'
	order by t_birthday;



--实验三

select * from Choice79
 inner join Student79 on Student79.s_no =Choice79.s_no 
 inner join Course79 on Choice79.course_no = Course79.course_no




--（1）查询所有同学的选课及成绩情况，要求显示学生的学号s_no、姓名s_name、课程号course_no和课程的成绩score。
	select Choice79.s_no,s_name,course_no,score from Choice79
	inner join Student79 on Choice79.s_no = Student79.s_no;

--（2）查询所有同学的选课及成绩情况，要求显示学生的姓名s_name、课程名称course_name、课程的成绩score
--并将查询结果存放到一个新的数据表new_table中
	create table new_table
	(
		s_name varchar(10),
		course_name char(20),
		score numeric(6,1)
	);
	insert into new_table select Student79.s_name,Course79.course_name,Choice79.score from Choice79
	inner join Student79 on Choice79.s_no = Student79.s_no
	inner join Course79 on Course79.course_no = Choice79.course_no;

--（3）查询“计算机99—1”班的同学的选课及成绩情况
--要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、课程的成绩score。
select Student79.s_no,s_name,Choice79.course_no,course_name,score from Student79
inner join Class79 on Student79.class_no = Class79.class_no
inner join Choice79 on Student79.s_no = Choice79.s_no
inner join Course79 on Course79.course_no = Choice79.course_no
where Class79.class_name ='计算机99-1';

--（4）查询所有同学的学分情况（假设课程成绩>60分时可获得该门课程的学分）
--要求显示学生的学号s_no、姓名s_name、总学分（将该列定名为： total score ）
 select Student79.s_no,Student79.s_name,sum(course_score) as 总学分 from Student79
 inner join Choice79 on Student79.s_no = Choice79.s_no
 inner join Course79 on Choice79.course_no = Course79.course_no
 where Choice79.score>=60
 group by Student79.s_no,Student79.s_name;

--（5）查询所有同学的平均成绩及选课门数
--要求显示学生的学号s_no、姓名s_name、平均成绩（将该列定名为average_score）、选课的门数（将该列定名为： choice_num）。
 select Student79.s_no,Student79.s_name,avg(Choice79.score) as average_score,count(*) choice_num from Student79
 inner join Choice79 on Student79.s_no= Choice79.s_no
 group by Student79.s_no,Student79.s_name;
 
 --（6）查询所有选修了课程但未参加考试的所有同学及相应的课程
 --要求显示学生的学号s_no、姓名s name、课程号course_no、课程名称course_name。
 select Student79.s_no,Student79.s_name,Choice79.course_no,Course79.course_name from Choice79 
 inner join Student79 on Student79.s_no =Choice79.s_no
 inner join Course79 on Choice79.course_no = Course79.course_no
 where Choice79.score is null

 --（7）查询所有选修了课程但考试不及格（假设<60分为不及格）的所有同学及相应的课程
 --要求显示学生的学号s_no、姓名s_name、课程号course_no、课程名称course_name、学分course_score。
 select Student79.s_no,Student79.s_name,Choice79.course_no,Course79.course_name,course_score from Choice79
 inner join Student79 on Student79.s_no =Choice79.s_no 
 inner join Course79 on Choice79.course_no = Course79.course_no
 where Choice79.score<60 or Choice79.score is null;

 --（8）查询选修了课程名为“程序设计语言”的所有同学及成绩情况
 --要求显示学生的姓名s_name、课程的成绩score。（使用ANY）
 select Student79.s_name,score from Choice79
 inner join Student79 on Student79.s_no =Choice79.s_no 
 inner join Course79 on Choice79.course_no = Course79.course_no
 where course_name='程序设计语言'

  select * from Student79,Choice79 where Student79.s_no=Choice79.s_no and Choice79.course_no in(select Course79.course_no from Course79
 where Course79.course_name ='程序设计语言')

 select * from Student79,(select * from Choice79 where Choice79.course_no in (select course_no from Course79
 where Course79.course_name ='程序设计语言')) b
where Student79.s_no=b.s_no

 
 select Student79.s_name,score from Student79,Choice79
 where Student79.s_no=ANY(select Choice79.s_no from Choice79 
 where Choice79.course_no = ANY(select Course79.course_no from Course79
 where Course79.course_name ='程序设计语言')) and Student79.s_no=Choice79.s_no  ;

 --（9）查询“计算机系”的所有同学及成绩情况
 --要求显示学生的学号s_no、姓名s name、班级名称class_name、课程号course_no、课程名称course_name、课程的成绩score。
 select Student79.s_no,Student79.s_name,Class79.class_name,Choice79.course_no,Course79.course_name,Choice79.score from Class79
 inner join Student79 on Student79.class_no =Class79.class_no
 inner join Choice79 on Student79.s_no =Choice79.s_no
 inner join Course79 on Course79.course_no =Choice79.course_no
 where Class79.class_dept='计算机系';

 --（10）查询所有教师的任课情况，要求显示教师姓名t_name、担任课程的名称course_name。
 select Teacher79.t_name,Course79.course_name from Teacher79
 inner join Teaching79 on Teacher79.t_no=Teaching79.t_no
 inner join Course79 on Teaching79.course_no=Course79.course_no;

 --（11）查询所有教师的任课门数，要求显示教师姓名t_name、担任课程的门数（将该列定名为course_number )
  select t_name,COUNT(*) course_number from Teacher79
  inner join Teaching79 on Teacher79.t_no=Teaching79.t_no
  group by t_name;

 --（12）查询和“李建国”是同一班级的同学的姓名。（使用子查询）
 select s_name from Student79 where class_no=(select class_no from Student79 where s_name ='李建国');
 
 
 --（13）查询没有选修“计算机基础”课程的学生姓名。（用NOT EXISTS）
 select Student79.s_name from Student79
 where (NOT EXISTS (select * from Choice79 inner join Course79 on Course79.course_no=Choice79.course_no
  where Course79.course_name='计算机基础' and Student79.s_no=Choice79.s_no));

 --（14）查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用UNION）
  select t_name from Teacher79
  inner join Teaching79 on Teacher79.t_no=Teaching79.t_no
  inner join Course79 on Course79.course_no=Teaching79.course_no
  where Course79.course_name ='数据库原理与应用'
  UNION
  select t_name from Teacher79
  inner join Teaching79 on Teacher79.t_no=Teaching79.t_no
  inner join Course79 on Course79.course_no=Teaching79.course_no
  where Course79.course_name ='数据结构';

 --（15）查询讲授了所有课程的教师的姓名。
Select t_name From Teacher79 join Teaching79 on Teacher79.t_no=Teaching79.t_no 
       where not exists (select course_name From Course79
	   where Course79.course_no=Teaching79.course_no)