### SQL语句练习题解析及答案
#### 一、创建表
在数据库`school`中创建`student`、`sc`、`course`三个表。
1. **学生表** `Student`:
```sql
CREATE TABLE Student (
Sno CHAR(6) PRIMARY KEY,
Sname VARCHAR(8),
Ssex CHAR(2),
Sage SMALLINT,
Sdept VARCHAR(15)
);
```
2. **课程表** `Course`:
```sql
CREATE TABLE Course (
Cno CHAR(4) PRIMARY KEY,
Cname VARCHAR(20),
Cpno CHAR(4),
Ccredit TINYINT
);
```
3. **选课表** `SC`:
```sql
CREATE TABLE SC (
Sno CHAR(6),
Cno CHAR(4),
Grade DECIMAL(12,2),
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
```
#### 二、插入数据
向`student`表中插入数据:
```sql
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUES ('4001', '赵茵', '男', 20, 'SX'),
('4002', '杨华', '女', 21, 'JSJ');
```
#### 三、删除操作
1. **删除`student`表中的元组**:
- 直接删除指定条件的记录:
```sql
DELETE FROM Student WHERE Sno = '4001';
```
2. **删除`student`表**:
- 删除整个表:
```sql
DROP TABLE Student;
```
3. **添加属性**:
- 向`student`表添加新字段`sbirthdate`:
```sql
ALTER TABLE Student ADD COLUMN Sbirthdate DATETIME;
```
#### 四、更新操作
1. **修改学生系科**:
- 更新`0001`号学生的系科为`JSJ`:
```sql
UPDATE Student SET Sdept = 'JSJ' WHERE Sno = '0001';
```
2. **修改学生信息**:
- 将陈小明的年龄增加1岁，性别改为女:
```sql
UPDATE Student SET Sage = Sage + 1, Ssex = '女' WHERE Sname = '陈小明';
```
3. **修改成绩**:
- 将李文庆的`1001`课程的成绩改为`93`:
```sql
UPDATE SC SET Grade = 93 WHERE Sno = (SELECT Sno FROM Student WHERE Sname = '李文庆') AND Cno = '1001';
```
#### 五、查询操作
1. **单表查询**:
- 查询年龄在`19`至`21`岁之间的女生的学号、年龄，并按年龄降序排列:
```sql
SELECT Sno, Sage FROM Student WHERE Sage BETWEEN 19 AND 21 AND Ssex = '女' ORDER BY Sage DESC;
```
2. **统计查询**:
- 计算`JSJ`系的平均年龄及最大年龄:
```sql
SELECT AVG(Sage) AS AverageAge, MAX(Sage) AS MaxAge FROM Student WHERE Sdept = 'JSJ';
```
3. **连接查询**:
- 查询选修`1002`课程的学生的学生（不使用嵌套）:
```sql
SELECT S.Sno, S.Sname
FROM Student S INNER JOIN SC ON S.Sno = SC.Sno
WHERE SC.Cno = '1002';
```
4. **嵌套查询**:
- 查询没有选修`1002`课程的学生:
```sql
SELECT Sno, Sname FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno = '1002');
```
#### 六、其他复杂查询
1. **嵌套查询**:
- 查询平均分不及格的学生人数:
```sql
SELECT COUNT(*) FROM (
SELECT Sno, AVG(Grade) AS AvgGrade
FROM SC
GROUP BY Sno
HAVING AvgGrade < 60
) AS SubQuery;
```
2. **子查询**:
- 查询没有选修`1001`，`1002`课程的学生:
```sql
SELECT Sno FROM Student WHERE Sno NOT IN (
SELECT Sno FROM SC WHERE Cno IN ('1001', '1002')
);
```
3. **高级排序**:
- 查询平均分前三名的学生学号:
```sql
SELECT Sno
FROM (
SELECT Sno, AVG(Grade) AS AvgGrade
FROM SC
GROUP BY Sno
ORDER BY AvgGrade DESC
) AS SubQuery
LIMIT 3;
```
#### 七、综合案例分析
1. **多表关联**:
- 查询平均分不及格的学生的学号及平均分:
```sql
SELECT Sno, AVG(Grade) AS AvgGrade
FROM SC
GROUP BY Sno
HAVING AvgGrade < 60;
```
2. **条件过滤**:
- 查询女学生平均分高于`75`分的学生:
```sql
SELECT S.Sno, AVG(SC.Grade) AS AvgGrade
FROM Student S INNER JOIN SC ON S.Sno = SC.Sno
WHERE S.Ssex = '女'
GROUP BY S.Sno
HAVING AvgGrade > 75;
```
3. **多条件查询**:
- 查询男学生学号、课程号、成绩（包括未选修课程的男学生）:
```sql
SELECT S.Sno, SC.Cno, SC.Grade
FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno
WHERE S.Ssex = '男';
```
通过这些练习题的学习,可以帮助大家更好地掌握SQL的基本操作,包括表的创建、数据的插入与删除、数据的更新以及各种复杂的查询操作等。对于数据库管理和应用开发来说,这些都是非常基础且重要的技能。希望通过对这些题目进行实践,能够帮助大家进一步提升自己的SQL水平。
