前期准备

1.创建数据库

SQL

CREATE DATABASE mydb;

2.创建学生表

SQL

USE mydb;
CREATE TABLE IF NOT EXISTS Student(
	Sno VARCHAR(20) PRIMARY KEY NOT NULL,
  Sname VARCHAR(20) NOT NULL,
  Ssex VARCHAR(20) NOT NULL,
  Sbirthday DATETIME,
  Class VARCHAR(20)
);

3.创建课程表

SQL

USE mydb;
CREATE TABLE IF NOT EXISTS Course(
	Cno VARCHAR(20) PRIMARY KEY NOT NULL,
  Cname VARCHAR(20) NOT NULL,
  Tno VARCHAR(20) NOT NULL,
  CONSTRAINT ky_fk_tno FOREIGN KEY(Tno) REFERENCES Teacher(Tno)
);

4.创建成绩表

SQL

USE mydb;
CREATE TABLE IF NOT EXISTS Score(
	Sno VARCHAR(20) NOT NULL,
  Cno VARCHAR(20) NOT NULL,
  Degree DECIMAL(4,1),
  CONSTRAINT ky_pri_score PRIMARY KEY(Sno,Cno),
  CONSTRAINT ky_fk_sno FOREIGN KEY(Sno) REFERENCES Student(Sno),
  CONSTRAINT ky_fk_cno FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

5.创建教师表

SQL

USE mydb;
CREATE TABLE IF NOT EXISTS Teacher(
	Tno VARCHAR(20) PRIMARY KEY NOT NULL,
  Ttime VARCHAR(20) NOT NULL,
  Tsex VARCHAR(20) NOT NULL,
  Tbirthday DATETIME,
  Prof VARCHAR(20),
  Depart VARCHAR(20) NOT NULL
);

6 写入学生表数据

SQL

USE mydb;
INSERT INTO Student VALUES
('108','曾华','男','1977-09-01','95033'),
('105','匡明','男','1975-10-02','95031'),
('107','王丽','女','1976-01-23','95033'),
('101','李军','男','1976-02-20','95033'),
('109','王芳','女','1975-03-01','95031'),
('103','陆君','男','1974-06-10','95031');

7 写入课程表数据

SQL

USE mydb;
INSERT INTO Course VALUES
('3-105','计算机导论','825'),
('3-245','操作系统','804'),
('3-166','数字电路','856'),
('9-888','高等数学','831');

8 写入成绩表数据

SQL

USE mydb;
INSERT INTO Score VALUES
('103','3-245','86'),
('105','3-245','75'),
('109','3-245','68'),
('103','3-105','92'),
('105','3-105','88'),
('109','3-105','76'),
('101','3-105','64'),
('107','3-105','91'),
('108','3-105','78'),
('101','3-166','85'),
('107','3-166','79'),
('108','3-166','81');

9 写入教师表数据

SQL

USE mydb;
INSERT INTO Teacher VALUES
('804','李成','男','1958-12-02','副教授','计算机系'),
('856','张旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系');

问题

1.查询 Student 表中的所有记录的 Sname、Ssex 和 Class 列。

SQL

SELECT Sname,Ssex,Class FROM Student;

查询教师所有的单位即不重复的 Depart 列。

SQL

SELECT DISTINCT Depart FROM Teacher;

查询 Student 表的所有记录。

SQL

SELECT * FROM Student;

查询 Score 表中成绩在 60 到 80 之间的所有记录。

SQL

SELECT * FROM Score WHERE Degree >= 60 AND Degree <= 80;

5.查询 Score 表中成绩为 85，86 或 88 的记录。

SQL

SELECT * FROM Score WHERE Degree IN (85,86,88);

查询 Student 表中“95031”班或性别为“女”的同学记录.

SQL

SELECT * FROM Student WHERE class = '95031' AND Ssex = '女';

以 Class 降序查询 Student 表的所有记录。

SQL

SELECT * FROM Student ORDER BY class DESC;

以 Cno 升序、Degree 降序查询 Score 表的所有记录。

SQL

SELECT * FROM Score ORDER BY Cno ASC, Degree DESC;

9.查询“95031”班的学生人数。

SQL

SELECT COUNT(class) FROM Student WHERE class = '95031';

查询 Score 表中的最高分的学生学号和课程号。（子查询或者排序）

SQL

SELECT Sno,Cno FROM Score ORDER BY Degree DESC LIMIT 1;

查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数

SQL

SELECT

查询分数大于 70，小于 90 的 Sno 列。

SQL

SELECT Sno FROM Score WHERE Degree < 90 AND Degree > 70;

查询“95033”班学生的平均分。

SQL

SELECT IFNULL(AVG(score.Degree), 0) FROM student WHERE student.class = '95033';

创建grade表

SQL

CREATE TABLE IF NOT EXISTS grade(
  low INT,
  upp INT,
  rank VARCHAR(2)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

查询所有同学的Sno，Cno和rank列

SQL

SELECT Sno,Cno,grade.rank FROM Score JOIN grade ON Score.Degree BETWEEN grade.low AND grade.upp;

查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SQL

SELECT *FROM Score
WHERE Degree > (SELECT Degree FROM Score WHERE Sno = '109' AND Cno = '3-105');

查询 score 中选学多门课程的同学中分数为非最高分成绩的记录。

SQL

查询成绩高于学号为“109”,课程号为“3-105”的成绩的所有记录。

SQL

查询和学号为 108 的同学同年出生的所有学生的 Sno、Sname 和 Sbirthday 列。

SQL

SELECT Sno, Sname, Sbirthday
FROM Student
WHERE YEAR(Sbirthday) = (SELECT YEAR(Sbirthday) FROM Student WHERE Sno = '108');

查询“张旭“教师任课的学生成绩。

SQL

SELECT s.Sno, s.Cno, s.Degree
FROM Score s
JOIN Course c ON s.Cno = c.Cno
JOIN Teacher t ON c.Tno = t.Tno
WHERE t.Tname = '张旭';

查询选修某课程的同学人数多于 5 人的教师姓名。

SQL

SELECT DISTINCT t.Tname
FROM Teacher t
JOIN Course c ON t.Tno = c.Tno
JOIN (
    SELECT Cno
    FROM Score
    GROUP BY Cno
    HAVING COUNT(Sno) > 5
) AS temp ON c.Cno = temp.Cno;

查询 95033 班和 95031 班全体学生的记录。

SQL

SELECT *FROM Student
WHERE Class IN ('95033', '95031');

查询存在有 85 分以上成绩的课程 Cno.

SQL

SELECT DISTINCT Cno
FROM Score
WHERE Degree > 85;

查询出“计算机系“教师所教课程的成绩表。

SQL

SELECT s.Sno, s.Cno, c.Cname, s.Degree
FROM Score s
JOIN Course c ON s.Cno = c.Cno
JOIN Teacher t ON c.Tno = t.Tno
WHERE t.Depart = '计算机系';

查询“计算机系”与“电子工程系“不同职称的教师的 Tname 和 Prof。

SQL

SELECT t1.Tname, t1.Prof
FROM (SELECT Tname, Prof FROM Teacher WHERE Depart = '计算机系') t1
JOIN (SELECT Prof FROM Teacher WHERE Depart = '电子工程系') t2
ON t1.Prof <> t2.Prof;

查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的 Cno、 Sno
和 Degree,并按 Degree 从高到低次序排序。

SQL

SELECT Cno, Sno, Degree
FROM Score
WHERE Cno = '3-105' 
  AND Degree > (SELECT MIN(Degree) FROM Score WHERE Cno = '3-245')
ORDER BY Degree DESC;

查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno 和
Degree.

SQL

SELECT s1.Cno, s1.Sno, s1.Degree
FROM Score s1
JOIN Score s2 ON s1.Sno = s2.Sno  -- 同一学生WHERE s1.Cno = '3-105' 
  AND s2.Cno = '3-245' 
  AND s1.Degree > s2.Degree;

查询所有教师和同学的 name、sex 和 birthday.

SQL

SELECT Sname AS name, Ssex AS sex, Sbirthday AS birthday FROM Student
UNION ALLSELECT Tname AS name, Tsex AS sex, Tbirthday AS birthday FROM Teacher;

查询所有“女”教师和“女”同学的 name、sex 和 birthday.

SQL

SELECT Sname AS name, Ssex AS sex, Sbirthday AS birthday FROM Student WHERE Ssex = '女'UNION ALLSELECT Tname AS name, Tsex AS sex, Tbirthday AS birthday FROM Teacher WHERE Tsex = '女';

30.查询成绩比该课程平均成绩低的同学的成绩表。

SQL

SELECT s.Sno, s.Cno, s.Degree, avg_deg.avg_Degree
FROM Score s
JOIN (
    SELECT Cno, AVG(Degree) AS avg_Degree
    FROM Score
    GROUP BY Cno
) AS avg_deg ON s.Cno = avg_deg.Cno
WHERE s.Degree < avg_deg.avg_Degree;

查询所有任课教师的 Tname 和 Depart.

SQL

SELECT DISTINCT t.Tname, t.Depart
FROM Teacher t
JOIN Course c ON t.Tno = c.Tno;

查询所有未讲课的教师的 Tname 和 Depart.

SQL

SELECT t.Tname, t.Depart
FROM Teacher t
LEFT JOIN Course c ON t.Tno = c.Tno
WHERE c.Tno IS NULL;

查询至少有 2 名男生的班号。

SQL

SELECT Class
FROM Student
WHERE Ssex = '男'GROUP BY Class
HAVING COUNT(Sno) >= 2;

查询 Student 表中不姓“王”的同学记录。

SQL

SELECT *FROM Student
WHERE Sname NOT LIKE '王%';

查询 Student 表中每个学生的姓名和年龄。

SQL

SELECT Sname, YEAR(NOW()) - YEAR(Sbirthday) AS 年龄
FROM Student;

查询 Student 表中最大和最小的 Sbirthday 日期值。

SQL

SELECT MAX(Sbirthday) AS 最新出生年月, MIN(Sbirthday) AS 最早出生年月
FROM Student;

以班号和年龄从大到小的顺序查询 Student 表中的全部记录。

SQL

SELECT *, YEAR(NOW()) - YEAR(Sbirthday) AS 年龄
FROM Student
ORDER BY Class DESC, 年龄 DESC;

查询“男”教师及其所上的课程。

SQL

SELECT t.Tname, c.Cno, c.Cname
FROM Teacher t
JOIN Course c ON t.Tno = c.Tno
WHERE t.Tsex = '男';

查询最高分同学的 Sno、Cno 和 Degree 列。

SQL

SELECT Sno, Cno, Degree
FROM Score
WHERE Degree = (SELECT MAX(Degree) FROM Score);

查询和“李军”同性别的所有同学的 Sname.

SQL

SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军')
  AND Sname <> '李军';

查询和“李军”同性别并同班的同学 Sname.

SQL

查询所有选修“计算机导论”课程的“男”同学的成绩表。

SQL

数据库题1

前期准备

问题

喜欢这篇文章的人也看了

数据库题2