在图书数据库中,使用嵌套查询实现: 1)求借阅了《计算机文化》的读者姓名 2)查询每个读者的情况及其借阅书籍的书名和出版社3)求没有借阅了《计算机文化》的读者姓名 4)查询借阅了高教出版社计算机类书籍的读者姓名 5)求至少借阅了高教出版社所有计算机类书籍的读者姓名 4.使用函数查询 在学生课程数据库中: 1)求学生的总人数 2)求选修了课程的学生人数 3)求课程和选修该课程的学生人数 4)求选修课超过3门课的学生学号 5)求选修了C1课程的学生学号和成绩,并对查询结果按成绩的降序排列,成绩相同者按学号升序排列
时间: 2024-02-05 17:14:17 浏览: 45
1) 求借阅了《计算机文化》的读者姓名:
```
SELECT reader_name
FROM borrow
WHERE book_id = (SELECT book_id
FROM book
WHERE book_name = '计算机文化');
```
2) 查询每个读者的情况及其借阅书籍的书名和出版社:
```
SELECT reader.reader_name, reader.age, borrow.book_name, book.press
FROM reader, borrow, book
WHERE reader.reader_id = borrow.reader_id
AND borrow.book_id = book.book_id;
```
3) 求没有借阅了《计算机文化》的读者姓名:
```
SELECT reader_name
FROM reader
WHERE reader_id NOT IN (SELECT reader_id
FROM borrow
WHERE book_id = (SELECT book_id
FROM book
WHERE book_name = '计算机文化'));
```
4) 查询借阅了高教出版社计算机类书籍的读者姓名:
```
SELECT reader.reader_name
FROM reader, borrow, book
WHERE reader.reader_id = borrow.reader_id
AND borrow.book_id = book.book_id
AND book.press = '高教出版社'
AND book.category = '计算机';
```
5) 求至少借阅了高教出版社所有计算机类书籍的读者姓名:
```
SELECT reader.reader_name
FROM reader, borrow, book
WHERE reader.reader_id = borrow.reader_id
AND borrow.book_id = book.book_id
AND book.press = '高教出版社'
AND book.category = '计算机'
GROUP BY reader.reader_id
HAVING COUNT(DISTINCT book.book_id) = (SELECT COUNT(book_id)
FROM book
WHERE press = '高教出版社'
AND category = '计算机');
```
在学生课程数据库中:
1) 求学生的总人数:
```
SELECT COUNT(*)
FROM student;
```
2) 求选修了课程的学生人数:
```
SELECT COUNT(DISTINCT student_id)
FROM score;
```
3) 求课程和选修该课程的学生人数:
```
SELECT course.course_name, COUNT(DISTINCT score.student_id)
FROM course, score
WHERE course.course_id = score.course_id
GROUP BY course.course_id;
```
4) 求选修课超过3门课的学生学号:
```
SELECT student_id
FROM score
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) > 3;
```
5) 求选修了C1课程的学生学号和成绩,并对查询结果按成绩的降序排列,成绩相同者按学号升序排列:
```
SELECT score.student_id, score.score
FROM score, course
WHERE score.course_id = course.course_id
AND course.course_name = 'C1'
ORDER BY score.score DESC, score.student_id ASC;
```
相关推荐
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)