What's more

Experiment 1 of 2020 database system of Shandong University
Experiment 2 of 2020 database system of Shandong University
Experiment 3 of 2020 database system of Shandong University
Experiment 4 of 2020 database system of Shandong University
Experiment 5 of 2020 database system of Shandong University
Experiment 6 of 2020 database system of Shandong University
2020 Database System Experiment 7 of Shandong University
Experiment 8 and 9 of 2020 database system of Shandong University

Write in front

When doing database experiments, you must be calm, so that you can find the errors and correct them. At the same time, if you find that SQL statements always report errors, "you must be wrong, but you just don't know where it is!"

Secondly, the more complex points in SQL statements have been annotated by bloggers. I hope you must read the idea and write it yourself instead of blindly Ctrl+C and Ctrl+V. remember!!

Experiment 6

Experiment 6 is slightly less difficult than the previous experiment, but it is also necessary to understand the differences between views and tables. After that, it will be more convenient to do the questions. The investigation mainly focuses on query statements. I must be very familiar with them in previous experiments

• 6-1 for example: find out the student number, name and age of all students younger than 20 years old
  Execute correctly: create view test6_00 as select sid，name，age from pub.student where age>20
  Easy to write after Oracle expansion:
  create or replace view test6_00 as select sid，name，age from pub.student where age>20
  Straight line select count(*) from test6_00 check whether all results can be queried within 5 minutes. If the timeout indicates that there may be an error, it is strictly prohibited to execute "update dbtest set test=6" to hand in the paper.
  Find out the student number, name and department name of students younger than 20 years old who are "School of physics", and sort them by student number
  Idea:
  1. Find the corresponding attribute value in pub.student;
  2. Note that order by sid is added at the end;

create view test6_01 as
	select sid, name, dname
	from pub.student
	where age < 20
	and sid in
		(select sid
		from pub.student_course
		where dname = 'School of Physics')
	order by sid

• 6-2 query and count the student number, name and total score of each student in 2009 grade and Software College (list sum_score) (if a student does not choose a course, the total score is null)
  Idea:
  1. First find the sid, name of the student who meets the conditions;
  2. Because some students do not choose a course, but this student should also be included, they use natural left outer join to connect;
  3. Find the corresponding total score, and then connect it;

create view test6_02 as
	select sid, name, sum_score
	from 
		(select distinct sid, name
		from pub.student
		where class = '2009'
		and dname = 'Software College') 
	natural left outer join
		(select distinct sid, sum(score) sum_score
		from pub.student_course
		group by sid)

• 6-3 query the student transcript of grade 2010, School of computer science and technology and operating system, including student number, name and score.
  Idea:
  1. Query according to conditions;

create view test6_03 as
	select sid, name, score
	from pub.student natural join pub.student_course
	where class = '2010'
	and dname = 'School of computer science and technology'
	and cid = (select cid from pub.course where name = 'operating system')

• 6-4 find out the student number and name of students who take the course of "database system" and have a score of more than 90.
  Idea:
  1. Query according to conditions;
  2. Two tables can be connected naturally;

create view test6_04 as
	select sid, name
	from pub.student natural join pub.student_course
	where cid = (select cid from pub.course where name = 'database system')
	and score > 90

• 6-5 find out the student number of the student named "Li Long" and the course number, course name and score of all elective courses.
  Idea:
  1. There can be more than one student named "Li Long"!!
  2. After the two tables are connected naturally, you can query the elements that meet the conditions;

create view test6_05 as
	select sid, cid, name, score
	from pub.student_course natural join pub.course
	where sid in (select distinct sid from pub.student where name = 'Li Long')

• 6-6 find out the student number and name of students who have taken all courses.
  Idea:
  1. Use the existence test: not exists... except(minus)... To test all courses and students' course selection;

create view test6_06 as
	select distinct sid, name
	from pub.student S
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course T
		where S.sid = T.sid))

• 6-7 find out the student number and name of students who have taken all courses and passed each examination of each course. (extension and consolidation of question 6)
  Idea:

  1. Similarly, existence detection can be used to obtain the sid, name of students who have taken all courses;
  2. Then add the condition where score > 60;

  Note: failure here means that failure in one exam will not be counted!!!

create view test6_07 as
	select distinct sid, name
	from pub.student S
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course T
		where S.sid = T.sid))
	and sid not in
		(select sid
		from pub.student_course
		where score < 60)

• 6-8 find out the student number and name of students who have taken all courses and obtained credits for all courses (i.e. passed the examination at least once for each course). (extension and consolidation of question 6).
  Idea:
  1. First, the Sid and name of students who have taken all courses are obtained by using existence detection;
  2. The opposite of "pass at least one examination" is "fail all", so we can remove these students who fail at the maximum;

create view test6_08 as
	select distinct sid, name
	from pub.student S
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course T
		where S.sid = T.sid))
	and sid not in
		(select sid
		from 
			(select distinct sid, cid, max(score) max_score
			from pub.student_course
			group by sid, cid)
		where max_score < 60)

• 6-9 query and count the total credit table of students of grade 2010 and College of chemistry and chemical engineering, including student number, name and total credit sum_credit.
  Idea:
  1. The first step is to find the sid, name of the student who meets the conditions;
  2. The second step is to calculate the total credits of students by using the previous method; (note that the maximum value > 60 is included in the credits!!)

create view test6_09 as
	select t0.sid, t0.name, t1.sum_credit
	from 
		(select distinct sid, name
		from pub.student
		where class = '2010'
		and dname = 'College of chemistry and chemical engineering') t0,
		
		(select distinct sid, sum(credit) sum_credit
		from (select distinct sid, cid, max(score) max_score
		from pub.student_course
		group by sid, cid) natural join pub.course
		where max_score >= 60
		group by sid)t1
	where t0.sid = t1.sid

• 6-10 find out the course number and course name of all courses with indirect advance courses.
  Tips: the so-called indirect antecedent course: the antecedent Course C of the antecedent Course B of A course, so C is the indirect antecedent course of A;
  Idea:
  1. Use existence to detect exists;
  2. where conditions are the comparison of fcid and cid;

create view test6_10 as
select cid, name
from pub.course t0
where exists
	(select cid
	from pub.course t1
	where t0.fcid = t1.cid
and exists
	(select cid
	from pub.course t2
	where t1.fcid = t2.cid))

Emphasize again: you must practice yourself after understanding your ideas~~
Please correct the problem!