sql经典练习

1: 创建数据库
create database ldb；

2：进入创建的数据库
\c ldb;

3: 创建表格并插入数据
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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);

4: 查看创建的所有表格
\d

练习题目：
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select * from (select sid, score from sc where sc.cid = '01')as t1, (select sid, score from sc where sc.cid = '02')as t2 where t1.sid = t2.sid  and t1.score > t2.score;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select * from (select sid, score from sc where sc.cid = '01')as t1, (select sid, score from sc where sc.cid = '02')as t2 where t1.sid = t2.sid;

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from (select sid, score from sc where sc.cid = '01')as t1 left join (select sid, score from sc where sc.cid = '02')as t2 on t1.sid = t2.sid;

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc where sc.cid not in (select sid from sc where sc.cid = '01') and sc.cid = '02';

 

2:查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select student.*, avgscore from student inner join (select sid, AVG(sc.score)as avgscore from sc group by sc.sid having AVG(sc.score)>=60)as t1 on student.sid = t1.sid;

3：查询在sc表中存在成绩的学生信息

select distinct student.* from student, sc where sc.sid =student.sid;

4: 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

select student.sid, student.sname, t1.sumcourse, t1.sumscores from student, (select sc.sid, Count(sc.cid) as sumcourse, Sum(sc.score) as sumscores from sc group by sc.sid)as t1 where student.sid = t1.sid;