Database---Experiment three nested query and view operations

(a) Nested query 1. Ask for the ' MA ' number and name. Sql> Select Sno,sname from student where Sno in (select Sno from SC where cno= (select CNO from course where cname= ' math ')) ; 2. Check with Liu Ming in the same department to study students. Sql> SELECT * FROM student where sdept= (select Sdept from student where Sname= ' Liu Ming '); 3. The result of elective course 1th is higher than that of Liu Chen (the result of all the courses that Liu Ming elective)   ) Student number and grade. Sql> Select Sno,grade from SC where cno= ' 1 ' and Grade>all (select grade from SC where sno= (select Sno from Student W   Here sname= ' Liu Ming '); 4. Students in other departments who are younger than the computer department (i.e. those who are younger than the oldest computer department). Sql> SELECT * FROM student where sdept!= ' CS ' and Sage<any (select Sage from student where sdept= ' CS '); 5. The year of the students in other departments of computer science   The name and age of the student who is younger. Sql> Select Sname,sage from student where sdept!= ' CS ' and Sage<all (select Sage from student where sdept= ' CS '); 6. No choice   Name of the student who has completed course No. 3rd.       Sql> Select sname from student where Sno not in (select Sno from SC where cno= ' 3 '); 7. Check the name of the student who took all the courses. Sql> Select sname from student where isn't exists (SELECT * From course where isn't exists (SELECT * from SC where sno=stude Nt.sno and CNO=COURSE.CNO)); There is no universal quantifier ∨ (, all) in the SQL language. However, predicates with Universal quantifiers can be converted to equivalent predicates with existential quantifiers. (∨x) P≡∟ (exists x (∟p)) Try: Query the course name that all students take in sql> select CNAME from course where NOT EXISTS (SELECT * from student where NOT exists (       SELECT * FROM SC where sc.sno=student.sno and SC.CNO=COURSE.CNO); 8. Ask for at least the student number and name of the students who have enrolled in the course number 20070002. Sql> Select Sno,sname from student where Sno in (SELECT DISTINCT Sno from SC Scx where NOT EXISTS (SELECT * FROM SC scy W Here scy.sno= ' 20070002 ' and NOT EXISTS (SELECT * from SC SCZ where Scz.sno=scx.sno and Scz.cno=scy.cno)); 9. For students with more than 2 elective courses      School number and name. Sql> Select Sno,sname from student where Sno in (select Sno from SC GROUP by SNO have Count (*) >=2); second, data Update 1. Insert data 1) to S Insert 2 rows of data into the Tudent table, 1 act on your message, and the other line will be customized. INSERT into student (sno,sname,ssex,sage,sdept) VALUES (20143985, ' Chen Jianjun ', ' Male ', ' CS '), insert into student (Sno,sname, ssex,sage,sdept) VALUES (20144065, ' Xu Cheng ', ' Male ', ' m ', ' CS '), as follows: 2) Insert data into the course table, 1 conduct the information of this course, and the other line will be customized. Sql> INSERT INTO course (Cno,cname,cpno,ccredit) VALUES (8, ' Introduction to Database Systems ', 5,5); Sql> insert into course (Cno,cname,cpno,ccredit) VALUES (9, ' JAVA ', 7,6); Insert the data into the SC table and insert the information for the course you choose.  sql> INSERT INTO SC (Sno,cno,grade) VALUES (20143985,5,98), as follows: 2. Modify data 1) Delete the classmate surnamed Liu. sql> Delete from student where sname like ' Liu '; as follows: 2) "CS" Students in the selected course information in the results of 0. Sql> Update SC set grade=0 where Sno in (select Sno from student where sdept= ' CS '); 3. Delete data 1) Delete and ' Lijia ' information about students in the same department. Sql> Delete from the student where sdept= (select Sdept from student where sname= ' Lijia '), as follows: 2) Delete the ' CS ' class selection information.  sql> Delete from SC where Sno in (select Sno from student where sdept= ' CS '), as follows:

Experimental Analysis:

In this database experiment, I completed the experimental requirements. This experiment is about nested query, in the classroom, the teacher taught the nested query related knowledge, I also use pen practice to write SQL statements, but the impression is not very deep, some do not understand. In the experimental Class I practiced the SQL statement, the knowledge learned in the classroom has a deeper understanding, a lot of harvest. In the experiment, I encountered some problems, through the inquiry material and the teacher classmate help finally solved. The following problems are encountered:

1, in the nested query, I feel a bit confused, do not know how to write, then I later step by step analysis, first write a small query as another query criteria, step by step, the query is completed.

2, in writing about exists nested query, I feel very difficult to consult the relevant information, understand that division is often implemented with exists. In general, like at least test instructions, the relational algebra is implemented by division, and then the division can be transformed into a dual not EXISTS implementation in the SQL statement.

In this experiment feel a lot of harvest, very happy.

Database---Experiment three nested query and view operations