database table Query Operation Practice Walkthrough (Experiment III), database walkthrough

Following the previous two experiments, this experiment to skillfully grasp the use of SELECT statements for a variety of query operations: single-table query, multi-table connection and query, nested query, collection query, etc., to consolidate database query operations.
Follow the small series to practice it together!
On the basis of experiment one created and inserted data table (Student, COURSE,SC,TEACHER,TC), complete the following operations.
(1) Change the name of the teacher ' Lori ' to ' Luolili '.
Copy CodeThe code is as follows: Update Teacher set tname= ' Luolili ' where tname= ' Lori '
(2) The results of the two courses of two students (the data are temporarily set and deleted after use) are inserted into the score table in the form of running SQL Program files. The problem is used to verify, understand and master the integrity rules of the relational model;
Insert:
Copy CodeThe code is as follows: INSERT INTO score (Sno,cno,grade) VALUES (' 04261006 ', ' C003 ', ' 64 ')
Insert INTO score (Sno,cno,grade) VALUES (' 04261007 ', ' C004 ', ' 79 ')
Inquire:
Copy CodeThe code is as follows: Select Sno Number, CNO course number, grade score from score where sno=04261006 or sno=04261007;
Delete:
Copy CodeThe code is as follows: Delete from score where sno=04261006 or sno=04261007;
(3) The average score of each course is obtained, and the results are deposited into the average form (self-designed and created);
Copy CodeThe code is as follows: CREATE TABLE average
(
CNO CHAR (8),
Avscore Numeric (5,2),
Constraint A1 primary KEY (CNO),
Constraint A2 foreign key (CNO) references Course (CNO),
)
Insert into average (Cno,avscore)
SELECT DISTINCT CNO, AVG (grade) from score GROUP by CNO
(4) Change the age of the student "Mary" to 24;
Copy CodeThe code is as follows: Update Student set 2014-year (Sbirth) age where sname= ' Mary '
(5) Fill in the Szipcode attribute column value of all students;
Copy CodeThe code is as follows: Update Student set szipcode= ' 221000 '
(6) Place the average score of all courses in the average table at 0;
Copy CodeThe code is as follows: Update average set avscore= ' 0 '
(7) Delete the average score record of the course number "C007" in the average table;
Copy CodeThe code is as follows: Delete from average where cno= ' C007 '
(8) Delete the average score record in all average tables;
Copy CodeThe code is as follows: delete from average;
(9) Create a temporary Student information form (tstudent) to delete all student records with ' 101 ' in the table.
Copy CodeThe code is as follows: Create TABLE tstudent (Sno char (8) primary key, Sname varchar (8) unique);
Delete from tstudent where Sno like ' 1011% ';
(10) To inquire the student number and name of all students;
Copy CodeThe code is as follows: Select Sno Number, sname name from Student
(11) To inquire the student's number, name and affiliation of all students;
Copy CodeThe code is as follows: Select Sno Number, sname name, sdept from Student
(12) To inquire the detailed records of all students;
Copy CodeThe code is as follows: SELECT * FROM Student
(13) To inquire the names of all students and their age;
Copy CodeThe code is as follows: Select Sname name, 2014-year (Sbirth) age from Student
(14) To inquire the name and year of birth of all students;
Copy CodeThe code is as follows: Select Sname name, year of birth (Sbirth) from Student
(15) Check the number of all students who have been in the course;
Copy CodeThe code is as follows: SELECT distinct SNO from score
Select distinct Student.sno from Student,score where Student.sno=score.sno and score.grade>0;
(16) To inquire the list of all students in "computer department" class;
Copy CodeThe code is as follows: Select Sno,sname from Student where sdept= ' computer system '
(17) Enquiry for the names and ages of all students under 23 years of age;
Copy CodeThe code is as follows: Select Sname name, 2014-year (Sbirth) age from Student where 2014-year (Sbirth) <23;
(18) The number of students who have failed to check the exam results;
Copy CodeThe code is as follows: SELECT distinct SNO from score where grade<60;
(19) To inquire about the names, departments and ages of students aged between 20-22 years;
Copy CodeThe code is as follows: Select Sname name, Sdept Department, 2014-year (Sbirth) age from student where 2014-year (Sbirth) between and 22;
(20) To inquire about the names, departments and ages of the students who are not at the age of 20-22 years;
Copy CodeThe code is as follows: Select Sname name, Sdept Department, 2014-year (Sbirth) age from student where 2014-year (Sbirth) not between and 22;
(21) To inquire the names of students of "computer department" and "e-commerce Department";
Copy CodeThe code is as follows: Select Sname from Student where sdept= ' computer Department ' or sclass= ' e-commerce '
(22) The Name and class information of the students who are not "counted 11" or "61" classes;
Copy CodeThe code is as follows: Select Sname,sclass from Student where Sclass not in (' meter ', ' meter ');
(23) The details of the students who have queried the number "04262002";
[Code]select student.sno,sname,ssex,2014-year (Sbirth), Sclass,grade from Student,score where Student.sno=score.sno and student.sno= ' 04262002 ';
(24) The student information of the inquiry number with "04262";
Copy CodeThe code is as follows: SELECT * from Student where sno like ' 4,262% '
(25) To inquire about the student's number, name, gender and age of all surname "Zhang";
Copy CodeThe code is as follows: Select Sno Number, sname name, Ssex sex, 2011-year (Sbirth) age from Student where sname like ' Wang '
(26) The number of students who have "Hai" in the second word of the name is queried, their name, gender and age;
Copy CodeThe code is as follows: Select Sno Number, sname name, Ssex sex, 2011-year (Sbirth) age from Student where sname like ' _ Field% '
(27) To inquire the names of all students who do not have the surname "Liu";
Copy CodeThe code is as follows: Select Sname name from Student where sname don't like ' Liu '
(28) The course number and the course name of the last two letters "05" which begin with "C" are queried for the course number;
Copy CodeThe code is as follows: Select Cno,cname from Course where CNO like ' c%05 '
(29) Some students do not take the exam after taking a course, so there are elective records, but no test results, try to find the lack of test results of students and the corresponding course number;
Copy CodeThe code is as follows: Select Student.sno,sname,cno from Student,score where Student.sno=score.sno and grade are NULL;
(30) Find all students with record of the student number, course number;
Copy CodeThe code is as follows: Select Sno, CNO from score where grade are not NULL;
(31) Find "computer system" students under the age of 22 years, student number, name;
Copy CodeThe code is as follows: Select Sno, sname from Student where sdept= ' computer system ' and 2014-year (Sbirth) <22
(32) Find the student number of the course of "C001" and its achievement, and the results of the query are sorted in descending order of fractions;
Copy CodeThe code is as follows: Select Student.sno,grade from Student,score where Student.sno=score.sno and cno= ' C001 ' ORDER by grade DESC;
(33) To inquire all the students, the results of the inquiry are arranged in ascending order, and the students in the same department are sorted by age;
Copy CodeThe code is as follows: SELECT * FROM Student ORDER by Sdept Asc,2014-year (Sbirth) desc;
(34) To inquire the total number of students;
Copy CodeThe code is as follows: The number of SELECT COUNT (*) from Student;
(35) The number of students who have enrolled in the course;
Copy CodeThe code is as follows: The number of select COUNT (distinct sno) from score;
(36) To inquire the highest score of student's number and grade in all courses;
Copy CodeThe code is as follows: Select Sno,grade from score where grade = (select Max (grade) from score)

Copy CodeThe code is as follows: SELECT distinct a.* from score a where A.sno in (select top 1 score.sno from score where score.cno = A.cno ORDER by G Rade Desc)
(37) To inquire about the highest scores of students in the "C001" course;
Copy CodeThe code is as follows: select Max (grade) highest score from score where cno= ' C001 '
(38) Calculate each course number and the corresponding number of elective courses;
Copy CodeThe code is as follows: SELECT COUNT (SNO) number of courses from score Group by CNO;
(39) Query "computer Department" elective two courses above the student number, name;
Copy CodeThe code is as follows: Select Student.sno,sname from Student where Student.sno in
(select Student.sno from Student,score where
sdept= ' computer system ' and Student.sno=score.sno GROUP by Student.sno have Count (CNO) >=2);
(40) Natural connection of student and score tables;
Copy CodeThe code is as follows: Select Student.*,score.grade from student, score where student.sno=score.sno;
(41) To use their own connection to query each course of indirect first class (that is, pre-class lessons)
Copy CodeThe code is as follows: Select A.cno,b.cpno from Course a,course b where a.cpno=b.cno;
(42) using the compound Condition connection query the elective "c001" course and scores in 90 points above all students;
Copy CodeThe code is as follows: Select Sname,grade from Student,score where Student.sno=score.sno and cno= ' C001 ' and grade>=90;
(43) using compound condition connection to query each student's elective course name and its achievements;
Copy CodeThe code is as follows: Select Student.sno,sname,cname,grade from Course,score,student where Course.cno=score.cno and student.sno= Score.sno;
(44) To inquire the students who have enrolled in all the courses;
Copy CodeThe code is as follows: Select Sname from Student where is not EXISTS (SELECT * from Course where NOT EXISTS (SELECT * from score where sno=stu Dent. Sno and Cno=course.cno))
(45) To inquire all the students who have enrolled in the C001 course, their name and school number;
Copy CodeThe code is as follows: Select Student.sno,sname from Student,score where Student.sno=score.sno and cno= ' C001 ';
(46) Inquiring about the student number and name of the course C001 or C007;
[Code]select student.sno,sname,cno from Student,score where Student.sno=score.sno and CNO in (' C001 ', ' C007 '); [/code]
(47) Enquiries on "computer system" students and students who are not older than 23 years old;
Copy CodeThe code is as follows: Select Sno, Sname,2014-year (Sbirth) age, sclass from student where sdept= ' computer Department ' or 2014-year (Sbirth) <=23;
(48) The query has both elective courses C001 and elective courses C007 All students study number, name;
Copy CodeThe code is as follows: Select Student.sno,sname from Student,score where Student.sno=score.sno and cno= ' C001 ' and Student.sno in (select Stu Dent.sno from Student,score where Student.sno=score.sno and cno= ' C007 ')
(49) The student's number, name, gender and age are enrolled in the course named "Database Principle";
Copy CodeThe code is as follows: Select Student.sno, Sname,ssex,cname,2011-year (Sbirth) age from Student,score,course where Student.sno=score.sno and Score.cno=course.cno and cname= ' database principle ';
(50) Check the list of students who are younger than the "computer department" in other classes;
Copy CodeThe code is as follows: Select Sno,sname, 2014-year (Sbirth) age from student where 2014-year (Sbirth) < (select min (2014-year (Sbirth)) From student where sclass= ' count ') and sclass! = ' Count 61 ';
(51) Inquiries and "summer" students in the same department study number, name, gender, age;
Copy CodeThe code is as follows: Select Sno,sname,ssex,2014-year (Sbirth) age from student where sdept= (select Sdept from student where sname= ' summer ') and Sname!= ' Summer '
(52) The establishment of "computer system" students view 1;
Copy CodeThe code is as follows: CREATE VIEW View_student
As select Sno,sname,ssex,sbirth,sclass from student where sclass= ' 13z network '
(53) To establish a "computer department" student's view 2, and require the modification and insertion, still need to ensure that the view only "computer department" class students;
Copy CodeThe code is as follows: CREATE VIEW View_student2
As select Sno,sname,ssex,sbirth,sclass from student where sclass= ' 13z network ' with CHECK option;
(54) The establishment of "computer Department" elective "C001" Students view, the definition of the view named "V_cs_c001_student1";
Copy CodeThe code is as follows: CREATE VIEW V_cs_c001_student1
As select Student.sno,sname,ssex,sbirth,sclass from student, score where
Student.sno=score.sno and sclass= ' 13z network ' and cno= ' C001 ';
(55) The establishment of "computer department" class elective "C001" course and scores in 90 points above the student's view, the definition view named "Cs_c001_student2";
Copy CodeThe code is as follows: CREATE VIEW Cs_c001_student2
As
Select Student.sno,sname, ssex,sbirth,sclass,cno from Student,score where
Student.sno=score.sno and cno= ' C001 ' and sclass= ' 13z network ' and Student.sno in (select Student.sno from Student,score where Stu Dent.sno=score.sno and Grade>90)
(56) Define a view that reflects the age of the student, and defines the view as "v_birth_student";
Copy CodeThe code is as follows: CREATE VIEW V_birth_student
As
Select Sno,sname,2014-year (Sbirth) age from student
(57) Define all female records in the student table as a view, the view is named "V_female_student";
Copy CodeThe code is as follows: CREATE VIEW V_female_student
As
SELECT * FROM student where ssex= ' female ';
(58) The student's school number and its average result are defined as a view, the view is named "V_average_student";
Copy CodeThe code is as follows: CREATE VIEW V_average_student
As
Select Sno,avg (grade) Avscore from score Group by Sno;
(59) in the "Computer system" student view to find out the age of less than 22 years old students;
Copy CodeThe code is as follows: SELECT * from View_student where 2014-year (Sbirth) <=22;
(60) Using the View query "computer Department" elective "C001" course students;
Copy CodeThe code is as follows: SELECT * from V_cs_c001_student1;
(61) Modify the name of a student by using the "computer system" view in (52);
Copy CodeThe code is as follows: Update view_student set sname= ' King xxx ' where sno=04261001;
(62) Insert a new student record by using the "computer system" view in (53).
Copy CodeThe code is as follows: INSERT into View_student2 (Sno,sname,ssex,sbirth,sclass) VALUES (' 04262004 ', ' Zhang ', ' Male ', ' 1987/11/09 ', ' meter ');
(63) Delete a student record by using the "computer system" view in (53).
Copy CodeThe code is as follows: Delete from View_student2 where sno= ' 04262004 ' and sname= ' Zhang xxx ';

The experiment class is over, I believe that through the practice of this lesson, the small partners have a further understanding of the operation of the database table.
The above is the basic operation of querying database tables, almost covered by a variety of query operations encountered situation, it is worth everyone to operate, I believe that everyone's learning has helped.

database table Query Operation Practice Walkthrough (Experiment III), database walkthrough