Database Table query practice (experiment 3) and database Drill

Database Table query practice (experiment 3) and database Drill

Following the previous two experiments, this experiment has mastered various query operations using select statements: Single-Table query, multi-table join and query, nested query, and set query, consolidate database query operations.
Let's start with the small editor!
Perform the following operations on the tables (Student, Course, SC, Teacher, TC) created and inserted in Experiment 1.
(1) change the name of the instructor 'lily' to 'lily '.
Copy codeThe Code is as follows: update Teacher set tname = 'lily' where tname = 'lily'
(2) Insert the scores of the two courses (temporarily set data and deleted after use) into the score table in the form of an SQL program file. This question 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 ('000000', 'c003 ', '64 ')
Insert into Score (sno, cno, grade) values ('000000', 'c004 ', '79 ')
Query:
Copy codeThe Code is as follows: select sno student ID, 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) calculate the average score of each course and save the result to the average table (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 student's "Mary" age to 24;
Copy codeThe Code is as follows: Update Student set 2014-year (Sbirth) age where Sname = 'lily'
(5) Fill in the szipcode attribute column values of all students;
Copy codeThe Code is as follows: update Student set szipcode = '000000'
(6) set the average score of all courses in the average table to zero;
Copy codeThe Code is as follows: update average set avscore = '0'
(7) Delete the average score of the course number 'c007 'in the average table;
Copy codeThe Code is as follows: delete from average where cno = 'c007'
(8) delete average score records in all average tables;
Copy codeThe Code is as follows: delete from average;
(9) create a temporary student information table (tstudent) to delete all student records whose student IDs contain '123.
Copy codeThe Code is as follows: create table tstudent (Sno char (8) primary key, Sname varchar (8) unique );
Delete from tstudent where Sno like '20140901 ';
(10) query the student ID and name of all students;
Copy codeThe Code is as follows: select sno Student ID, sname name from Student
(11) query the student ID, name, and Department of all students;
Copy codeThe Code is as follows: select sno Student ID, sname name, sdept from Student
(12) query detailed records of all students;
Copy codeThe Code is as follows: select * from Student
(13) query the names and ages of all students;
Copy codeThe Code is as follows: select sname name, 2014-year (sbirth) age from Student
(14) query the names and birthyears of all students;
Copy codeThe Code is as follows: select sname name, year (sbirth) year of birth from Student
(15) query the student IDs of all students whose courses have been completed;
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) query the list of all students in the "Computer Science" class;
Copy codeThe Code is as follows: select sno, sname from Student where sdept = 'computer System'
(17) query the names and ages of all students under the age of 23;
Copy codeThe Code is as follows: select sname name, 2014-year (sbirth) age from Student where 2014-year (sbirth) <23;
(18) Check the student's student ID that has failed the examination;
Copy codeThe Code is as follows: select distinct sno from Score where grade <60;
(19) query the names, systems, and ages of students between the ages of 20 and 22;
Copy codeThe Code is as follows: select sname name, sdept series, 2014-year (sbirth) age from student where 2014-year (sbirth) between 20 and 22;
(20) query the names, systems, and ages of students between the ages of 20 and 22;
 Copy codeThe Code is as follows: select sname name, sdept series, 2014-year (sbirth) age from student where 2014-year (sbirth) not between 20 and 22;
(21) query the names of students in the computer department and E-Commerce Department;
Copy codeThe Code is as follows: select sname from Student where sdept = 'computer system' or sclass = 'e-commerce System'
(22) query the names and class information of students who are neither "11" nor "61;
Copy codeThe Code is as follows: select sname, sclass from Student where sclass not in ('Count', 'Count ');
(23) query the details of students whose student ID is 04262002;
[Code] select student. sno, sname, ssex, 2014-year (sbirth), sclass, grade from Student, Score where Student. sno = Score. sno and Student. sno = '000000 ';
(24) query the student information whose student ID starts with "04262;
Copy codeThe Code is as follows: select * from Student where sno like '201312'
(25) query the student ID, name, gender, and age of all students surnamed "Zhang;
Copy codeThe Code is as follows: select sno Student ID, sname name, ssex gender, 2011-year (sbirth) age from Student where sname like 'King %'
(26) query the student's student ID, name, gender, and age with the second word "sea" in the name;
Copy codeThe Code is as follows: select sno Student ID, sname name, ssex sex, 2011-year (sbirth) age from Student where sname like '_ Tian %'
(27) query the names of all students without the surname "Liu;
Copy codeThe Code is as follows: select sname name from Student where sname not like 'Liu %'
(28) query the course number and Course name with the last two letters "05" starting with "C;
Copy codeThe Code is as follows: select cno, cname from Course where cno like 'C % 5'
(29) Some students did not take the test after taking a certain course, so they recorded the elective course but did not have the test score. They tried to find the students who lack the test score 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 is NULL;
(30) Search for all student ID and course number records with scores;
Copy codeThe Code is as follows: select sno, cno from Score where grade is not NULL;
(31) Find the student ID and name of the computer department under the age of 22;
Copy codeThe Code is as follows: select sno, sname from Student where sdept = 'computer system' and 2014-year (sbirth) <22
(32) Find the student ID and score of the course "C001", and sort the query results in descending order of scores;
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) query the status of all students. The query results are listed in ascending order of their respective departments, and the students in the same department are listed in descending order of age;
Copy codeThe Code is as follows: select * from student order by sdept asc, 2014-year (sbirth) desc;
(34) query the total number of students;
Copy codeThe Code is as follows: select count (*) from Student;
(35) query the number of students taking the course;
Copy codeThe Code is as follows: select count (distinct sno) from Score;
(36) query the student ID and score for the highest score 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 grade desc)
(37) query the highest score of students studying the "C001" course;
 Copy codeThe Code is as follows: select max (grade) highest Score from Score where cno = 'c001'
(38) Calculate the course number and number of students;
Copy codeThe Code is as follows: select count (sno) selected students from Score group by cno;
(39) query the student's student ID and name who have taken more than two courses in the computer department;
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 having count (cno)> = 2 );
(40) naturally connected student and score tables;
Copy codeThe Code is as follows: select student. *, Score. grade from student, Score where student. sno = Score. sno;
(41) use your own connection to query the indirect first course of each course (that is, the first course of the first course)
Copy codeThe Code is as follows: select a. cno, B. cpno from Course a, Course B where a. cpno = B. cno;
(42) use compound conditions to connect all students who have taken the "c001" course and have scored more than 90 points;
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 conditions to query the names and scores of the courses selected by each student;
 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) query the students who have selected all courses;
Copy codeThe Code is as follows: select Sname from Student where not exists (select * from Course where not exists (select * from Score where Sno = Student. Sno and Cno = Course. Cno ))
(45) query the student ID and name of all students taking course C001;
Copy codeThe Code is as follows: select student. sno, sname from student, Score where student. sno = Score. sno and cno = 'c001 ';
(46) query the student ID 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) query "Computer Science" students and students not later than 23 years old;
Copy codeThe Code is as follows: select sno, sname, 2014-year (sbirth) age, sclass from student where sdept = 'computer system' or 2014-year (sbirth) <= 23;
(48) query the student ID and name of all students who have taken both the course C001 and the course C007;
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 student. sno from student, Score where student. sno = Score. sno and cno = 'c007 ')
(49) query the student's student ID, name, gender, and age for the course "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 print ';
(50) query the list of students younger than all students in 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 = '61 ') and sclass! = '61 ';
(51) query the student ID, name, gender, and age of the same series of students in the summer;
Copy codeThe Code is as follows: select sno, sname, ssex, 2014-year (sbirth) age from student where sdept = (select sdept from student where sname = 'sume') and sname! = 'Summer'
(52) create a "Computer System" Student 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) Create view 2 for "Computer System" students and ensure that this view is only for "Computer System" students during modification and insertion;
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) create a view for students who have taken the "C001" course in the "Computer System" and define the view name as "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) set up a view for students who have taken the "C001" course and have scored more than 90 points, and define the view name as "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 student. sno = Score. sno and grade> 90)
(56) define a view that reflects the age of a student, and define the view name 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 girls' records in the student table as a view named "v_female_student ";
Copy codeThe Code is as follows: create view v_female_student
As
Select * from student where ssex = 'femal ';
(58) define the student ID and average score as a view 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) Identify students younger than 22 in the "Computer Science" student view;
Copy codeSample Code: select * from view_student where 2014-year (sbirth) <= 22;
(60) View query of students who have taken the "C001" course in the "computer system;
Copy codeThe Code is as follows: select * from v_cs_C001_student1;
(61) modify the name of a student in the "Computer System" view in (52;
Copy codeThe Code is as follows: update view_student set sname = '王' where sno = 04261001;
(62) Insert a new student record through the "Computer System" view in (53.
Copy codeThe Code is as follows: insert into view_student2 (sno, sname, ssex, sbirth, sclass) values ('000000', 'zhang moumou ', 'male', '2017/09 ', 'Count ');
(63) delete a student record from the "Computer System" view in (53.
Copy codeThe Code is as follows: delete from view_student2 where sno = '000000' and sname = 'zhang moumou ';

The lab course is over. I believe that through the practical operations in this course, our friends will have a better understanding of database table operations.
The above is the basic operation for querying database tables, covering almost all the situations encountered by various query operations. It is worth your personal operations and I believe it will be helpful for your learning.