write the following queries in sql, using the university schema.
时间: 2023-07-04 20:02:42 浏览: 155
### 回答1:
在使用大学模式(university schema)中编写以下查询的SQL语句。
1. 查询所有教授的姓名和工资:
```sql
SELECT pname, salary
FROM professor;
```
2. 查询所有学生的姓名和入学年份:
```sql
SELECT sname, enrollment_year
FROM student;
```
3. 查询所有课程的名称和学分:
```sql
SELECT cname, credits
FROM course;
```
4. 查询所有学生的姓名和他们所在的系名称:
```sql
SELECT s.sname, d.dname
FROM student s
JOIN department d ON s.d_id = d.d_id;
```
5. 查询所有教授的姓名和他们所在的系名称:
```sql
SELECT p.pname, d.dname
FROM professor p
JOIN department d ON p.d_id = d.d_id;
```
6. 查询学生数量和每个系的平均学分:
```sql
SELECT d.dname, COUNT(*) as student_count, AVG(c.credits) as avg_credits
FROM student s
JOIN department d ON s.d_id = d.d_id
JOIN takes t ON s.s_id = t.s_id
JOIN course c ON t.c_id = c.c_id
GROUP BY d.dname;
```
7. 查询至少有两门课程的学生的姓名和系名称:
```sql
SELECT s.sname, d.dname
FROM student s
JOIN department d ON s.d_id = d.d_id
JOIN takes t ON s.s_id = t.s_id
GROUP BY s.s_id, d.dname
HAVING COUNT(t.c_id) >= 2;
```
8. 查询所有没有注册课程的学生的姓名和系名称:
```sql
SELECT s.sname, d.dname
FROM student s
JOIN department d ON s.d_id = d.d_id
LEFT JOIN takes t ON s.s_id = t.s_id
WHERE t.c_id IS NULL;
```
### 回答2:
以下是在使用大学模式下用SQL编写的查询:
1. 找到所有学生的姓名和年龄:
```
SELECT name, age
FROM Students;
```
2. 找到所有专业为计算机科学的学生:
```
SELECT *
FROM Students
WHERE major = '计算机科学';
```
3. 找到所有课程的名称和学分:
```
SELECT course_name, credits
FROM Courses;
```
4. 找到所有学生所修课程的名称和学分:
```
SELECT Courses.course_name, Courses.credits
FROM Students
JOIN Course_Enrollment ON Students.student_id = Course_Enrollment.student_id
JOIN Courses ON Course_Enrollment.course_id = Courses.course_id;
```
5. 找到所有学生的姓名和他们所修课程的成绩:
```
SELECT Students.name, Course_Enrollment.grade
FROM Students
JOIN Course_Enrollment ON Students.student_id = Course_Enrollment.student_id;
```
6. 找到所有学生所在学部的名称和大学名称:
```
SELECT Departments.department_name, Universities.university_name
FROM Students
JOIN Departments ON Students.department_id = Departments.department_id
JOIN Universities ON Departments.university_id = Universities.university_id;
```
这些查询基于大学模式,如需进一步了解每个表的结构和关系,请查阅大学模式的文档。