SQL语言

1 create schema "S-T" authorization wang;

1 create table student1 (
2     sno char(8),
3     sname char(8),
4     sbirth date default '0000-00-00',
5     ssex char(2) default '?',
6     sdept char(30)
7 );

1 drop table sc;
2 drop table course;
3 drop table student;

1 -- 增加“入学时间”列
2 alter table student1 add stime date;
3 -- 删除“入学时间”列
4 alter table student1 drop stime;

1 -- 查询全部不同的生日
2 select distinct sbirth from student1;

1 -- 查询所有出生日期在1996的信息
2 select * 
3 from student1
4 where sbirth like '1997%';

1 -- 注意是 is null 或者 is not null 
2 select *
3 from sc
4 where sno='980001' and
5     (grade>=60 or grade<60 or grade is null);

1 /*
2     查询所有学生信息
3     结果按所在系逆序排序，
4     同系的学生则按学号顺序排序
5 */
6 select *
7 from student1
8 order by sdept desc,sno asc;

1 select sno 学号,sname 姓名,sbirth 出生日期
2 from student1
3 where sbirth like '1996%'
4 order by sno desc;

1 /*
2     查询选修3号课程的学生人数，最高分和平均分
3 */
4 select count(*),max(grade),avg(grade)
5 from sc
6 where cno='3';

1 /*
2     查询选修了三门以上课程的学生学号及该生的平均成绩
3 */
4 select sno,avg(grade)
5 from sc
6 group by sno
7 having count(*)>3;

 1 /*
 2     求数据结构课程成绩大于85分的学生姓名和成绩
 3     结果按成绩降序排列
 4 */
 5 select student.sname, grade
 6 from student, sc, course
 7 where student.sno = sc.sno and
 8     sc.sno = course.cno and
 9     course.cname = '数据结构' and
10     sc.grade > 85
11 order by grade desc;

1 /*
2     求离散数学的先修课的先修课的课号与名称
3 */
4 select z.cno, z.cname
5 from course x, course y, course z
6 where x.pcno = y.cno and
7     y.pcno = z.cno and
8     x.cname = '离散数学';

1 /*
2     查询所有学生的学号、姓名、所选课程的课程号以及这门课的成绩
3     left join 返回符合连接条件的数据行以及左边表中不符合条件的数据行
4     right join, full join 类似
5 */
6 select student.sno, sname, cno, grade
7 from
8 student left outer join sc
9 on student.sno = sn.sno;

1 /* 
2     查询与学号为95003的学生同系的学生学号和姓名
3 */
4 select sno, sname
5 from student
6 where sdept = (select sdept
7 from student where sno='95003');