Following the previous two experiments, this experiment to master the use of SELECT statements for a variety of query operations: single-table query, multiple table connection and query, nested query, set query, etc., consolidate database query operations.
Now follow the small weave together to practice it!
On the basis of a table (Student, COURSE,SC,TEACHER,TC) that creates and inserts data, complete the following actions.
(1) Change the name of the teacher ' Lori ' to ' Luolili '.
Copy Code code as follows:
Update Teacher set tname= ' Luolili ' where tname= ' Lori '
(2) The results of the two courses with 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. This problem is used to verify, understand and master the integrity rules of the relational model.
Insert:
Copy Code code as follows:
Insert into Score (Sno,cno,grade) VALUES (' 04261006 ', ' C003 ', ' 64 ')
Insert into Score (Sno,cno,grade) VALUES (' 04261007 ', ' C004 ', ' 79 ')
Inquire:
Copy Code code as follows:
Select Sno Number, CNO course number, grade fraction from Score where sno=04261006 or sno=04261007;
Delete:
Copy Code code as follows:
Delete from Score where sno=04261006 or sno=04261007;
(3) To seek the average score of each course, and to deposit the results into the average table (designed and created by ourselves);
Copy Code code 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 Code code as follows:
Update Student set 2014-year (Sbirth) age where sname= ' Mary '
(5) to fill in the values of all the students ' Szipcode attributes;
Copy Code code as follows:
Update Student set szipcode= ' 221000 '
(6) Placing the average score of all courses in the average table at 0;
Copy Code code as follows:
Update average set avscore= ' 0 '
(7) To delete the average score record of the course number ' C007 ' in the average table;
Copy Code code as follows:
Delete from average where cno= ' C007 '
(8) To delete the average score record in all average tables;
Copy Code code as follows:
(9) Establish a temporary Student information table (tstudent) to delete all student records in the form containing ' 101 '.
Copy Code code as follows:
CREATE TABLE Tstudent (Sno char (8) primary key, sname varchar (8) unique);
Delete from tstudent where Sno like ' 1011% ';
(10) Inquire all students ' numbers and names;
Copy Code code as follows:
Select Sno Number, sname name from Student
(11) Inquire the student number, name and department of the Whole school;
Copy Code code as follows:
Select Sno Number, sname name, sdept from Student
(12) To inquire the detailed records of all the students;
Copy Code code as follows:
(13) Inquire the names of all students and their ages;
Copy Code code as follows:
Select Sname name, 2014-year (Sbirth) age from Student
(14) Inquire all students ' names and year of birth;
Copy Code code as follows:
Select Sname name, year (Sbirth) birth from Student
(15) Check the number of all the students who have completed the course;
Copy Code code as follows:
Select DISTINCT Sno from Score
Select distinct Student.sno from Student,score where Student.sno=score.sno and score.grade>0;
(16) Check the list of all students in the "computer department" class;
Copy Code code as follows:
Select Sno,sname from Student where sdept= ' computer system '
(17) Inquire about the names and ages of all students under the age of 23 years;
Copy Code code as follows:
Select Sname name, 2014-year (Sbirth) age from Student where 2014-year (Sbirth) <23;
(18) Check the student number of the students who have failed in the examination results;
Copy Code code as follows:
Select distinct sno from Score where grade<60;
(19) Inquire the name, department and age of the students aged 20-22 years;
Copy Code code as follows:
Select Sname name, sdept system, 2014-year (Sbirth) age from student where 2014-year (Sbirth) between and 22;
(20) Inquire the name, department and age of the students who are not between 20-22 years of age;
Copy Code code as follows:
Select Sname name, Sdept, 2014-year (Sbirth) age from student where 2014-year (Sbirth) not between and 22;
(21) Enquiries about the names of the students in the "computer department" and "Electrical Business Department";
Copy Code code as follows:
Select Sname from Student where sdept= ' computer Department ' or sclass= ' electrical business system '
(22) The Name and class information of the students who are not "11" or "61" classes;
Copy Code code as follows:
Select Sname,sclass from Student where Sclass isn't in (' count ', ' count ');
(23) The details of the students who inquired for the "04262002" number;
[Code]select student.sno,sname,ssex,2014-year (Sbirth), Sclass,grade from Student,score where Student.sno=score.sno and student.sno= ' 04262002 ';
(24) Inquiry number to "04262" the beginning of the student information;
Copy Code code as follows:
SELECT * from Student where sno like ' 4,262% '
(25) Check the student number, name, sex and age of all the students whose surname is "Zhang";
Copy Code code as follows:
Select Sno Number, sname name, Ssex sex, 2011-year (Sbirth) age from Student where to sname like ' king '
(26) The number, name, sex and age of the student with the word "Hai" in the second word in the query name;
Copy Code code as follows:
Select Sno Number, sname name, Ssex sex, 2011-year (Sbirth) age from Student where sname like ' _ Tian% '
(27) Check the names of all students who do not have the surname "Liu";
Copy Code code as follows:
Select Sname name from Student where sname isn't like ' Liu% '
(28) The course number and course name of the last two letters "05" which begin with "C" are queried;
Copy Code code as follows:
Select Cno,cname from Course where CNO like ' c%05 '
(29) Some students did not take the examination after taking a course, so there are elective records, but no test results, try to find the lack of test results and the corresponding course number;
Copy Code code as follows:
Select Student.sno,sname,cno from Student,score where Student.sno=score.sno and grade is NULL;
(30) To find all the student's school number and course number with the achievement record;
Copy Code code as follows:
Select Sno, CNO from Score where grade are not NULL;
(31) Find the student number and name of "computer department" under the age of 22 years;
Copy Code code as follows:
Select Sno, sname from Student where sdept= ' computer Department ' and 2014-year (Sbirth) <22
(32) Find the student number and the result of the "C001" course, and the query results are sorted in descending order of the score;
Copy Code code as follows:
Select Student.sno,grade from Student,score where Student.sno=score.sno and cno= ' C001 ' ORDER by grade DESC;
(33) Query all students, the results of the query in ascending order, the same system of students in descending order of age;
Copy Code code as follows:
SELECT * FROM Student ORDER by Sdept Asc,2014-year (Sbirth) desc;
(34) To inquire the total number of students;
Copy Code code as follows:
Number of SELECT COUNT (*) from Student;
(35) The number of students enrolled in the course;
Copy Code code as follows:
The number of select COUNT (Distinct sno) from Score;
(36) To inquire the highest scores of student numbers and grades in all courses;
Copy Code code as follows:
Select Sno,grade from Score where grade = (select Max (grade) from Score)
Copy Code code as follows:
Select distinct a.* from Score a where A.sno to (select top 1 score.sno to Score where score.cno = A.cno ORDER BY grade Desc
(37) To inquire the highest scores of students studying "C001" courses;
Copy Code code 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 Code code as follows:
Select COUNT (SNO) Select the number of classes from Score Group by CNO;
(39) Inquiry "computer Department" elective two courses above the student number, name;
Copy Code code 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 has the having count (CNO) >=2);
(40) Natural connection student and score table;
Copy Code code as follows:
Select Student.*,score.grade from student, Score where Student.sno=score.sno;
(41) using its own connection to query the indirect first course of each course (ie, advance course of the first Class)
Copy Code code as follows:
Select A.cno,b.cpno from Course a,course b where a.cpno=b.cno;
(42) Use compound condition connection to inquire all students who take "c001" course and score above 90 points;
Copy Code code as follows:
Select Sname,grade from Student,score where Student.sno=score.sno and cno= ' C001 ' and grade>=90;
(43) Use compound condition connection to inquire each student elective course name and its result;
Copy Code code as follows:
Select Student.sno,sname,cname,grade from Course,score,student where Course.cno=score.cno and Student.sno=score.sno;
(44) The students who have enrolled in all courses are queried;
Copy Code code as follows:
Select Sname from Student where does exists (SELECT * from Course where NOT EXISTS (SELECT * from Score where sno=student. Sno and Cno=course.cno))
(45) Inquire all the student number and name of the C001 course;
Copy Code code as follows:
Select Student.sno,sname from Student,score where Student.sno=score.sno and cno= ' C001 ';
(46) Inquires 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) The students in the "computer department" and those who are not younger than 23 years of age are queried;
Copy Code code as follows:
Select Sno, Sname,2014-year (Sbirth) age, sclass from student where sdept= ' computer Department ' or 2014-year (Sbirth) <=23;
(48) Inquires both elective course C001 and elective the course C007 All student study number, the name;
Copy Code code as follows:
Select Student.sno,sname from Student,score where Student.sno=score.sno and cno= ' C001 ' and Student.sno in (select Student. Sno from Student,score where Student.sno=score.sno and cno= ' C007 ')
(49) Inquires the student number, name, gender, age of the course name "the database Principle";
Copy Code code 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 principles ';
(50) Check the list of students in other classes who are younger than the "computer department";
Copy Code code as follows:
Select Sno,sname, 2014-year (Sbirth) age from student where 2014-year (Sbirth) < (select min (2014-year (Sbirth)) from Student where sclass= ' meters ') and Sclass!= ' 61 ';
(51) Inquiry and "Summer" in the same department of students study number, name, sex, age;
Copy Code code as follows:
Select Sno,sname,ssex,2014-year (Sbirth) age from student where sdept= (select Sdept from student where sname= ' summer ') and Snam E!= ' Summer '
(52) The establishment of "computer Department" Students View 1;
Copy Code code as follows:
CREATE VIEW View_student
As select Sno,sname,ssex,sbirth,sclass from student where sclass= ' 13z network '
(53) The establishment of "computer Department" Students View 2, and asked to modify and insert, still need to ensure that the view only "computer department" class students;
Copy Code code 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" student's view, defined view named "V_cs_c001_student1";
Copy Code code 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 the score of 90 points above the student's view, defined view named "Cs_c001_student2";
Copy Code code 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 networks ' 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, defining the view named "V_birth_student";
Copy Code code as follows:
CREATE VIEW V_birth_student
As
Select Sno,sname,2014-year (Sbirth) age from student
(57) Define all the girls ' records in the student table as a view with the view named "V_female_student";
Copy Code code as follows:
CREATE VIEW V_female_student
As
SELECT * FROM student where ssex= ' female ';
(58) The student's school number and its average score are defined as a view, the view is named "V_average_student";
Copy Code code as follows:
CREATE VIEW V_average_student
As
Select Sno,avg (Grade) Avscore from Score Group by Sno;
(59) To identify students under the age of 22 years in the student view of the computer system;
Copy Code code as follows:
SELECT * from View_student where 2014-year (Sbirth) <=22;
(60) Using the View query "computer Department" elective "C001" curriculum students;
Copy Code code as follows:
SELECT * from V_cs_c001_student1;
(61) Modifying a student's name through the "computer system" view in (52);
Copy Code code as follows:
Update view_student set sname= ' Wang So-and-so ' where sno=04261001;
(62) Insert a new student record through the "computer system" view in (53).
Copy Code code as follows:
Insert into View_student2 (Sno,sname,ssex,sbirth,sclass) VALUES (' 04262004 ', ' Zhang So-and-so ', ' male ', ' 1987/11/09 ', ' count ');
(63) through the "computer system" view in (53), delete a student record.
Copy Code code as follows:
Delete from View_student2 where sno= ' 04262004 ' and Sname= ' Zhang So-and-so ';
Experimental class is over, I believe that through the practice of this lesson, the small partners have a better understanding of the operation of the database table.
The above is the Basic Query database table operation, almost covers a variety of query operations encountered by the situation, it is worth our personal operation, I believe that the learning of everyone help.
