Database --- Experiment 2 Data Query --- Data Query

Database --- Experiment 2 Data Query --- Data Query

 

(1) Multi-table join query I. query the Student name, course number, and score of each selected student. (2 table join) SQL> select student. sname, SC. cno, SC. grade from student, SC where student. sno = SC. sno; ii. student ID, name, and course name and score. (3 table join) SQL> select student. sno, student. sname, course. cname, SC. grade from student, course, SC where student. sno = SC. sno and SC. cno = course. cno; iii. the name and student ID of the student whose elective course number is 1 or 2. SQL> select distinct student. sname, student. sno from student, SC where student. sno = SC. sno and (SC. cno = 1 or SC. cno = 2); iv. query the number of the indirect first course of each course. SQL> select first. cno, second. cpno from course first, course second where first. cpno = second. cno; v. query students in the same department as 'Liu chen. SQL> select * from student where sdept = (select sdept from student where sname = ''); vi. query the student ID and name selected for the course "C language. SQL> select student. sno, student. sname from student, SC, course where student. sno = SC. sno and SC. cno = course. cno and course. cname = 'pascal '; vii. query the student ID and average score with an average score of more than 80. SQL> select sno, avg (grade) avg_grade from SC group by sno having avg (grade)> = 80; viii. query the student ID of a course with an average score of less than 85. SQL> select distinct SC. sno from student, SC where student. sno = SC. sno and cno in (select cno from SC group by cno having avg (grade) <85); ix. displays student information and course selection information based on the student. SQL> select * from student, SC where student. sno = SC. sno (+); x. Makes a natural connection between the student table and the Selected Course table, and outputs the result. SQL> select student. sno, student. sname, student. ssex, student. sage, student. sdept, SC. cno, SC. grade from student, SC where student. sno = SC. sno; xi. name and student ID of a student whose student ID is greater than 'Liu Chen. SQL> select sname, sno from student where sno> (select sno from student where sname = 'Liu ming'); xii. query the average score of each course selected by each student in the mathematics department. SQL> select SC. sno, avg (SC. grade) from (select sno from student where sdept = 'map') S, SC where S. sno = SC. sno group by SC. sno; xiii. query the records of all students who have taken course 2 and whose scores are higher than those of student 20070001 (course 2 scores. SQL> select student. * from student, SC where student. sno = SC. sno and SC. cno = 2 and SC. grade> (select grade from SC where cno = 2 and sno = '000000'); (2) select (using the from clause, using inner join, left join, right join, cross join completes multi-table join query) 1. displays student information and course selection information based on the student. SQL> select * from student left join SC on student. sno = SC. sno; 2. query the names of students who have taken course 2. SQL> select student. sname from student inner join SC on student. sno = SC. sno and SC. cno = 2; 3. Perform Cartesian projects on the student table and curriculum. SQL> select * from student cross join course; (3) questions: 1. Can Aggregate functions be used as conditional expressions in the Where clause. A: No. 2. What should I do if one attribute appears in more than one table in a multi-table join query. A: You should add a vertex to the table name and a property name.

 

Experiment analysis and discussion:

In this database test, I completed the experiment requirements. This experiment is about multi-table join query, projection, and selection conditions. In the class, I learned how to query multi-Table connections. I also wrote SQL statements using a pen, but I was not very impressed and I didn't quite understand them. In the lab class, I practiced SQL statements and gained a lot from my understanding of what I learned in the class. In the experiment, I encountered some problems and finally solved the problem by querying materials and helping my classmates. The problem is as follows:

1. During Multi-table queries, I don't know how to get an alias for a table. In the experiment, I found that an alias can be directly added after a table is queried.

2. When I was doing the second question, I was not quite clear about the external connection. I learned about inner join through one test,

Left join, right join, and cross join.