【数据库简答题解析】
在数据库领域，SQL是用于管理和处理关系型数据库的主要语言。以下是对题目中涉及的SQL语句的详细解释：
1. 查询T1老师所授课程的课程号和课程名：
```sql
SELECT C.CNO, C-CN
FROM TC AS T INNER JOIN C ON T.CNO = C.CNO
WHERE T.TNO = 'T1'
```
2. 查询年龄大于18岁的男同学的学号、姓名、系别：
```sql
SELECT S.SNO, S.SN, S.Dept
FROM S
WHERE S.Age > 18 AND S.Sex = '男'
```
3. 查询“李力”老师所授课程的课程号、课程名和课时：
```sql
SELECT T.CNO, C.CN, TC.Lectures
FROM T, TC, C
WHERE T.TN = '李力' AND T.TNO = TC.TNO AND TC.CNO = C.CNO
```
4. 查询学号为S1的同学所选修课程的课程号、课程名和成绩：
```sql
SELECT SC.CNO, C.CN, SC.Score
FROM SC INNER JOIN C ON SC.CNO = C.CNO
WHERE SC.SNO = 'S1'
```
5. 查询“钱尔”同学所选修课程的课程号、课程名和成绩：
```sql
SELECT SC.CNO, C.CN, SC.Score
FROM S, SC, C
WHERE S.SNO = '钱尔' AND S.SNO = SC.SNO AND SC.CNO = C.CNO
```
6. 查询至少选修“刘伟”老师所授全部课程的学生姓名：
```sql
SELECT DISTINCT S.SN
FROM S, SC
WHERE S.SNO = SC.SNO AND SC.CNO IN (
SELECT T.CNO
FROM T, TC
WHERE T.TN = '刘伟' AND T.TNO = TC.TNO
)
```
7. 查询“李思”同学未选修的课程的课程号和课程名：
```sql
SELECT C.CNO, C.CN
FROM C
WHERE C.CNO NOT IN (
SELECT SC.CNO
FROM SC
WHERE SC.SNO = '李思'
)
```
8. 查询全部学生都选修了的课程的课程号和课程名：
```sql
SELECT C.CNO, C.CN
FROM C, (SELECT SC.CNO FROM SC GROUP BY SC.CNO HAVING COUNT(DISTINCT SC.SNO) = (SELECT COUNT(DISTINCT S.SNO) FROM S)) AS SubQuery
WHERE C.CNO = SubQuery.CNO
```
9. 查询选修了课程号为C1和C2的学生的学号和姓名：
```sql
SELECT S.SNO, S.SN
FROM S, SC
WHERE S.SNO = SC.SNO AND SC.CNO IN ('C1', 'C2')
```
10. 查询选修全部课程的学生的学号和姓名：
```sql
SELECT S.SNO, S.SN
FROM S
WHERE S.SNO NOT IN (
SELECT S1.SNO
FROM S AS S1
WHERE S1.SNO != S.SNO
GROUP BY S1.SNO
HAVING COUNT(DISTINCT S1.CNO) = (SELECT COUNT(DISTINCT C.CNO) FROM C)
)
```
11. 查询选修课程包含“程军”老师所授课程之一的学生学号：
```sql
SELECT DISTINCT SC.SNO
FROM SC, T, TC
WHERE TC.CNO = SC.CNO AND T.TN = '程军' AND T.TNO = TC.TNO
```
12. 查询选修课程包含学号S2的学生所修课程的学生学号：
```sql
SELECT S1.SNO
FROM SC AS S1, SC AS S2
WHERE S1.CNO = S2.CNO AND S2.SNO = 'S2'
```
## 设计题
1. 查找在“高等教育出版社”出版，书名为“操作系统”的图书的作者名：
```sql
SELECT B.BAuth
FROM Book AS B, Publish AS P
WHERE B.BNo = P.PNo AND B.BName = '操作系统' AND P.PName = '高等教育出版社'
```
2. 查找为作者“张欣”出版全部“小说”类图书的出版社的电话：
```sql
SELECT P.PTel
FROM Book AS B, Publish AS P
WHERE B.BNo = P.PNo AND B.BType = '小说' AND B.BAuth = '张欣'
GROUP BY P.PNo, P.PTel
HAVING COUNT(*) = (SELECT COUNT(*) FROM Book WHERE B.BType = '小说' AND B.BAuth = '张欣')
```
3. 查询“电子工业出版社”出版的“计算机”类图书的价格，同时输出出版社名称及图书类别：
```sql
SELECT B.BPrice, P.PName, B.BType
FROM Book AS B, Publish AS P
WHERE B.BNo = P.PNo AND P.PName = '电子工业出版社' AND B.BType = '计算机'
```
4. 查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息：
```sql
SELECT *
FROM Book
WHERE BName = '高等数学' AND BPrice < ANY (
SELECT BPrice
FROM Book AS B1, Publish AS P1
WHERE B1.BNo = P1.PNo AND P1.PName = '人民邮电出版社' AND B1.BName = '高等数学'
) AND PName <> '人民邮电出版社'
```
5. 查找书名中有“计算机”一词的图书的书名及作者：
```sql
SELECT B.BName, B.BAuth
FROM Book AS B
WHERE B.BName LIKE '%计算机%'
```
6. 在“图书”表中增加“出版时间”（BDate）项，其数据类型为日期型：
```sql
ALTER TABLE Book ADD BDate DATE
```
7. 在“图书”表中以“作者”建立一个索引：
```sql
CREATE INDEX BAuthIndex ON Book (BAuth) DESC
```
8. 建立存书表（BookStore）和销售表（BookSell）：
```sql
CREATE TABLE BookStore (
BNo CHAR(8) PRIMARY KEY,
BName VARCHAR(255),
Publish VARCHAR(255),
Version INT,
PDate DATE,
BAuth VARCHAR(255),
BPrice DECIMAL(10, 2),
BInPrice DECIMAL(10, 2),
BCount INT
);
CREATE TABLE BookSell (
SDate DATE PRIMARY KEY,
BNo CHAR(8),
SCount INT,
SMoney DECIMAL(10, 2),
FOREIGN KEY (BNo) REFERENCES BookStore (BNo)
);
```
以上解答详细阐述了SQL语句的逻辑和功能，涉及到了数据查询、联接、聚合函数、条件筛选、子查询以及数据库表的创建和索引的建立等核心概念。这些知识点对于理解和操作关系型数据库至关重要。
