关系数据库标准语言SQL实验

关系数据库标准语言SQL实验

要求：数据库的建立、SQL语句
题目：
Accessing the Database

The first laboratory exercise is to connect to a database, populate it with data, and run very simple SQL queries.
• Create your own database.
• The next step is to create tables according to the schema presented in the next two pictures. (Note the primary key, foreign key and data type)
• Load sample data. Scripts for these tasks can be found in the QQ files.
• Write the SQL statements.

Basic SQL

Schema Diagram for University Database

Write queries in SQL, on the University schema, to answer each of the following questions:

1. Find the names of all the instructors from Biology department
2. Find the names of courses in Computer science department which have 3 credits
3. For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student.
4. As above, but show the total number of credits for such courses (taken by that student). Don’t display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student.
5. As above, but display the total credits for each of the students, along with the ID of the student; don’t bother about the name of the student. (Don’t bother about students who have not registered for any course, they can be omitted)
6. Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)
7. Display the IDs of all instructors who have never taught a couse (Notesad1); ( interpret “taught” as “taught or is scheduled to teach”)
8. As above, but display the names of the instructors also, not just the IDs.

Intermediate SQL

Using the university schema, write the following queries. In some cases you need to insert extra data to show the effect of a particular feature – this is indicated with the question. You should then show not only the query, but also the insert statements to add the required extra data.

1. Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don’t worry about those that had no students taking that section
2. Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.
3. As in in Q1, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this using aggregation on a left outer join (use the SQL natural left outer join syntax)
4. Find all courses whose identifier starts with the string “CS-1”
5. Insert each instructor as a student, with tot_creds = 0, in the same department
6. Now delete all the newly added “students” above (note: already existing students who happened to have tot_creds = 0 should not get deleted)
7. Update the salary of each instructor to 10000 times the number of course sections they have taught.

Advanced SQL

In this assignment, you will write more complex SQL queries, using the University schema by default. Again, you may have to add required data to test your queries.

1. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).
2. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.
3. Grades are mapped to a grade point as follows: A:10, B:8, C:6, D:4 and F:0. Create a table to store these mappings [grade_points(grad_e,points)], and find the total grade-points earned by the student with ID 12345, across all courses taken by the student.
4. Find the grade point average (GPA) for the above student, that is, the total grade-points divided by the total credits for the associated courses. [grade point average (GPA) is the total grade-points divided by the total credits for the associated courses.]
5. Write a query to find the grade point average (GPA) of each student, using this table.
6. Make sure students who have not got a non-null grade in any course are displayed with a GPA of null.
7. Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.
8. Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates your database allows on views; explain what happens.

实验内容：

Accessing the Database
建立11个表并执行，执行largeRelationsInsertFile

Basic SQL
1.1
Find the names of all the instructors from Biology department

/*1*/
select name
from instructor
where dept_name='Biology';

1.2
Find the names of courses in department which have 3 credits

/*2*/
select title
from course
where dept_name='Comp. Sci.' and credits=3; 

1.3
For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student.

/*3*/
select takes.ID,course.course_id,course.title
from course,takes
where takes.ID=2561 and takes.course_id=course.course_id; 

1.4
As above, but show the total number of credits for such courses (taken by that student). Don’t display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student.

/*4*/
select takes.ID,sum(credits)
from course,takes
where takes.course_id=course.course_id and takes.ID='2561'
group by takes.ID;
 

1.5
As above, but display the total credits for each of the students, along with the ID of the student; don’t bother about the name of the student. (Don’t bother about students who have not registered for any course, they can be omitted)

/*5*/
select ID,tot_cred
from student

1.6
Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)

/*6*/
select distinct name
from course,takes,student
where takes.ID=student.ID and takes.course_id=course.course_id and course.dept_name='Comp. Sci.'

1.7
Display the IDs of all instructors who have never taught a couse (Notesad1); ( interpret “taught” as “taught or is scheduled to teach”)

/*7*/
select distinct instructor.ID
from instructor,teaches
where not exists
(
select *
from teaches
where instructor.ID=ID
)

1.8

/*8*/
select distinct instructor.ID, instructor.name
from instructor,teaches
where not exists
(
select *
from teaches
where instructor.ID=ID
)

Intermediate SQL
2.1
Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don’t worry about those that had no students taking that section

/*1*/
select max(enrollment) as max_enrollment,min(enrollment) as min_enrollment
from 
(
	select count(distinct takes.ID) as enrollment
	from takes
	group by takes.sec_id,takes.course_id,takes.semester,takes.year
) AS C

2.2
Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.

/*2*/
with C(course_id,sec_id,semester,year,enrollment) as
( 
	select course_id,sec_id,semester,YEAR,COUNT(id)
	from takes
	group by course_id,sec_id,semester,year 
)
select C.course_id,C.sec_id,C.semester,C.year,enrollment
from C
where C.enrollment>=all(select enrollment from C)
group by C.course_id,C.sec_id,C.semester,C.year,enrollment
 

2.3
As in in Q1, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this using aggregation on a left outer join (use the SQL natural left outer join syntax)

/*3*/
select student.ID,student.name,count(distinct course_id) as course_num,count(sec_id) as sec_num
from student left outer join takes on student.ID=takes.ID
group by student.ID,student.name

2.4
Find all courses whose identifier starts with the string “CS-1”

/*4*/
select course_id
from course
where title like'CS-1%'

2.5
Insert each instructor as a student, with tot_creds = 0, in the same department

/*5*/
insert 
into student(ID,name,dept_name)
select ID,name,dept_name
from instructor
where not exists
(
	select *
	from student
	where instructor.ID=ID
)

2.6
Now delete all the newly added “students” above (note: already existing students who happened to have tot_creds = 0 should not get deleted)

/*6*/
delete
from student
where student.ID in
(
	select student.ID
	from instructor
	where student.ID=ID 
)

2.7
/7/

update instructor
set salary=10000*
(
	select count(*)
	from teaches
	where teaches.ID=instructor.ID
)

Advanced SQL
3.1
The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).

/*1*/
go
create view fail
as
select ID,course_id,sec_id,semester,year,grade
from takes
where grade='F';
go

3.2
Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.

/*2*/
select ID,grade
from fail
group by ID,grade
having count(grade)>=2

3.3
Grades are mapped to a grade point as follows: A:10, B:8, C:6, D:4 and F:0. Create a table to store these mappings [grade_points(grad_e,points)], and find the total grade-points earned by the student with ID 12345, across all courses taken by the student.

/*3*/
Create table grade_points
(
	grade_e char(2),
	points int
);
Insert
Into grade_points
Values('A',10);
Insert
Into grade_points
Values('B',8);
Insert
Into grade_points
Values('C',6);
Insert
Into grade_points
Values('D',4);
Insert
Into grade_points
Values('F',0);
Select sum(points)
From grade_points,takes
Where takes.ID='2561'

3.4
Find the grade point average (GPA) for the above student, that is, the total grade-points divided by the total credits for the associated courses. [grade point average (GPA) is the total grade-points divided by the total credits for the associated courses.]
/*4*/
select sum(points*credits)/sum(credits) as GPA
from takes,grade_points,course
where takes.ID='2561'
 
3.5
Write a query to find the grade point average (GPA) of each student, using this table.
/*5*/
select sum(points*credits)/sum(credits) as GPA,takes.ID
from takes,grade_points,course
where takes.course_id=course.course_id and grade_points.grade_e=takes.grade
group by takes.ID
 
3.6
Make sure students who have not got a non-null grade in any course are displayed with a GPA of null.
/*6*/
select points.ID as ID, points.tot_point/cast(credits.cred as decimal) as PC
from (
	select takes.ID as ID, sum(credits) as cred 
	from (student left outer join takes on student.ID=takes.ID)
	left outer join course on takes.course_id=course.course_id
	 group by takes.ID ) as credits
	 ,
	 (
	 select takes.ID as ID,sum(grade_points.points) as tot_point
	from takes left outer  join grade_points on takes.grade=grade_points.grade_e
	group by takes.ID ) as points 
where points.ID=credits.ID;
 
3.7
Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.
/*7*/
go
Create view CSinstructors
AS
Select instructor.ID,name,dept_name,teaches.sec_id
From instructor,teaches
where dept_name='Comp. Sci.' and instructor.ID=teaches.ID
Go

3.8 Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates your database allows on views; explain what happens.
/*8*/
insert Into fail
values('33107','123','2','Fall','2019','F')
insert
into CSinstructors
values
('021','Olivian','Comp. Sci.',12000)