数据库SQL强化习题
表特点
- /*==============================================================*//* DBMS name: MySQL 5.0 *//* Created on: 2020 *//*==============================================================*/drop table if exists courses;drop table if exists scores;drop table if exists students;drop table if exists teachers;/*==============================================================*//* Table: courses *//*==============================================================*/create table courses( cno varchar(5) not null, cname varchar(10) not null, tno varchar(10) not null, primary key (cno));/*==============================================================*//* Table: scores *//*==============================================================*/create table scores( sno varchar(3) not null, cno varchar(5) not null, degree numeric(10,1) not null, primary key (sno, cno));/*==============================================================*//* Table: students *//*==============================================================*/create table students( sno varchar(3) not null, sname varchar(4) not null, ssex varchar(2) not null, sbirthday datetime, class varchar(5), primary key (sno));/*==============================================================*//* Table: teachers *//*==============================================================*/create table teachers( tno varchar(3) not null, tname varchar(4) not null, tsex varchar(2) not null, tbirthday datetime not null, prof varchar(6), depart varchar(10), primary key (tno));alter table courses add constraint FK_Reference_3 foreign key (tno) references teachers (tno) on delete restrict on update restrict;alter table scores add constraint FK_Reference_1 foreign key (sno) references students (sno) on delete restrict on update restrict;alter table scores add constraint FK_Reference_2 foreign key (cno) references courses (cno) on delete restrict on update restrict;
- INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','盘算机系');INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','盘算机系');INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'盘算机导论',825);INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'利用系统' ,804);INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
查询students表的所有纪录
查询students表中的所有纪录的sname、ssex和class列
查询存在有85分以上结果的课程cno
查询所有西席和同学的name、sex和birthday
查询所有“女”西席和“女”同学的name、sex和birthday
查询所有任课西席的tname和depart
查询所有未授课的西席的tname和depart
查询同名的同学纪录
查询学生的姓名和年岁
查询学生中最大和最小的出生年代
以班号和年岁从大到小的顺序查询student表中的全部纪录
查询男西席及其所上的课程
查询各门课程的最高分同学的sno、cno和degree列
查询课程对应的老师姓名、职称、所属系
查询课程对应的女老师姓名、职称、所属系
行列转置：各科最好结果
行列转置：每个学员各科最好结果
行列转置：课程编号和课程名称
课程号“3-105”的前3名学员排行
课程“盘算机导论”的前3名学员排行
课程号“3-105”的倒数最后3名学员排行
来源：https://blog.csdn.net/weixin_47792333/article/details/112062760
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
