5道SQL经典练习题。

数据表介绍

• 1.学⽣表 Student(SId,Sname,Sage,Ssex)

  • SId 学⽣编号

  • Sname 学⽣姓名

  • Sage 出⽣年⽉

  • Ssex 学⽣性别

• 2.课程表 Course(CId,Cname,TId)

  • CId 课程编号

  • Cname 课程名称

  • TId 教师编号

• 3.教师表 Teacher(TId,Tname)

  • TId 教师编号

  • Tname 教师姓名

• 4.成绩表 SC(SId,CId,score)

  • SId 学⽣编号

  • CId 课程编号

  • score 分数

建表语句

• 学⽣表 Student

      create table Student(
          SId varchar(10),
          Sname varchar(10),
          Sage datetime,
          Ssex varchar(10)
      ); 
  

• 课程表 Course

  create table Course(
      CId varchar(10),
      Cname nvarchar(10),
      TId varchar(10)
  );
  

• 教师表 Teacher

  create table Teacher(
      TId varchar(10),
      Tname varchar(10)
  );
  

• 成绩表 SC

  create table SC(
      SId varchar(10),
      CId varchar(10),
      score decimal(18,1)
  );
  

插入数据

• 学⽣表 Student

  -- 学生表 Student
  insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); 
  insert into Student values('02' , '钱电' , '1990-12-21' , '男'); 
  insert into Student values('03' , '孙⻛' , '1990-12-20' , '男'); 
  insert into Student values('04' , '李云' , '1990-12-06' , '男'); 
  insert into Student values('05' , '周梅' , '1991-12-01' , '⼥'); 
  insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥'); 
  insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥'); 
  insert into Student values('09' , '张三' , '2017-12-20' , '⼥'); 
  insert into Student values('10' , '李四' , '2017-12-25' , '⼥'); 
  insert into Student values('11' , '李四' , '2012-06-06' , '⼥'); 
  insert into Student values('12' , '赵六' , '2013-06-13' , '⼥'); 
  insert into Student values('13' , '孙七' , '2014-06-01' , '⼥'); 
  

• 课程表 Course

  -- 科⽬表 Course 
  insert into Course values('01' , '语⽂' , '02'); 
  insert into Course values('02' , '数学' , '01'); 
  insert into Course values('03' , '英语' , '03');
  

• 教师表 Teacher

  -- 教师表 Teacher 
  insert into Teacher values('01' , '张三'); 
  insert into Teacher values('02' , '李四'); 
  insert into Teacher values('03' , '王五'); 
  

• 成绩表 SC

  -- 成绩表 SC 
  insert into SC values('01' , '01' , 80); 
  insert into SC values('01' , '02' , 90); 
  insert into SC values('01' , '03' , 99); 
  insert into SC values('02' , '01' , 70); 
  insert into SC values('02' , '02' , 60); 
  insert into SC values('02' , '03' , 80); 
  insert into SC values('03' , '01' , 80); 
  insert into SC values('03' , '02' , 80); 
  insert into SC values('03' , '03' , 80); 
  insert into SC values('04' , '01' , 50); 
  insert into SC values('04' , '02' , 30); 
  insert into SC values('04' , '03' , 20); 
  insert into SC values('05' , '01' , 76); 
  insert into SC values('05' , '02' , 87); 
  insert into SC values('06' , '01' , 31); 
  insert into SC values('06' , '03' , 34); 
  insert into SC values('07' , '02' , 89); 
  insert into SC values('07' , '03' , 98);
  

练习题目

1.查询在 SC 表存在成绩的学⽣信息

select * from Student 
	where sid in (
		select distinct sid from SC)

2.查询同时存在" 01 “课程和” 02 "课程的情况

select 
	t1.*
	,t2.CId as t2_cid
	,t2.score as t2_score
from SC t1
join SC t2  #内连接inner join
on t1.SId=t2.SId and t1.cid='01' and t2.cid='02' ;

3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select 
	t1.*
	,t2.CId t2_cid
	,t2.score t2_sco
from 
(select * from SC where cid='01') t1
left join   #左连接 left join
(select * from SC where cid='02') t2
on t1.SId=t2.SId;

4.查询不存在" 01 “课程但存在” 02 "课程的情况

select * from SC 
	where cid='02' and sid not in (
		select sid from SC where cid='01')

5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩

--两种方法 1.先合并 在聚合
-- 2.先聚合 再合并

-- 先查询平均成绩 再合并
select 
	t1.*
	,t2.sname 
from 
	(select 
		sid
		,avg(score) avg_sco
	from SC 
	group by sid #用唯一的sid分组
	having avg_sco>=60) t1  # 平均成绩>=60分的表作为t1
join Student t2 #内连接 学生表t2
on t1.sid=t2.sid; #连接条件学号相等