Practice of query operation of database table (Experiment 3)

Time:2021-2-25

Following the previous two experiments, this experiment is to master the use of select statements for various query operations: single table query, multi table connection and query, nested query, set query, to consolidate the database query operation.
Now follow Xiaobian to practice together!
Based on the data table (student, course, SC, teacher, TC) created and inserted in Experiment 1, the following operations were completed.
(1) Change the name of the teacher ‘Lori’ to ‘Lori’.

Copy codeThe code is as follows:
Update teacher set tname =’luo Lili ‘where tname =’luo Li’

(2) Insert the scores of two courses of two students (the data is set temporarily by themselves and deleted after use) into the score table in the form of running SQL program file. This question is used to verify, understand and master the integrity rules of 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’)

Query:

Copy codeThe code is as follows:
Select SnO student 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) Calculate the average score of each course, and save the results in 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 age of the student “Mary” to 24;

Copy codeThe code is as follows:
Update student set 2014 year (birth) age where sname =’Mary ‘

(5) Fill in the szipcode attribute column values of all students;

Copy codeThe code is as follows:
update Student set szipcode=’221000′

(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 grade record with the course number of ‘C007’ in the average table;

Copy codeThe code is as follows:
delete from average where cno=’C007′

(8) Delete all average records;

Copy codeThe code is as follows:
delete from average;

(9) Establish a temporary student information table (tstudent) and 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 ‘001011%’;

(10) Query the student number and name of all students;

Copy codeThe code is as follows:
Select SnO student number, sname name from student

(11) Inquire the student number, name and Department of all students;

Copy codeThe code is as follows:
Select SnO, sname, sdept from student

(12) Query the detailed records of all students;

Copy codeThe code is as follows:
select * from Student

(13) Search 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 name and year of birth of all students;

Copy codeThe code is as follows:
Select sname name, year (sbirth) year of birth from student

(15) Query the student number of all the students who have taken 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) Query the list of all students in the “computer department” class;

Copy codeThe code is as follows:
Select SnO, sname from student where sdept =’computer department ‘

(17) Search 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) Inquire the student number of the students who failed in the examination;

Copy codeThe code is as follows:
select distinct sno from Score where grade<60;

(19) Search the name, department and age of students aged between 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) Search the name, department and age of students who are not between 20 and 22 years old;
 

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) inquire 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 department ‘or sclass =’e-commerce department’

(22) query the names and class information of students who are neither “Ji 11” nor “Ji 61”;

Copy codeThe code is as follows:
Select sname, class from student where class not in (‘Count ‘,’Count’);
(23) to inquire about the details of the student whose student number is “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) query the information of students whose student number starts with “04262”;

Copy codeThe code is as follows:
select * from Student where sno like ‘04262%’

(25) inquire the student number, name, gender and age of all students surnamed “Zhang”;

Copy codeThe code is as follows:
Select SnO, sname name, sex, 2011 year (sbirth) age from student where sname like ‘Wang%’

(26) search the student number, name, gender and age of the student whose second word is “Hai”;

Copy codeThe code is as follows:
Select SnO, sname name, sex, 2011 year (sbirth) age from student where sname like ‘_ Tian% ‘

(27) check the names of all students not surnamed Liu;

Copy codeThe code is as follows:
Select name from student where name not like ‘Liu%’

(28) query the course number and course name with the last two letters of “05” beginning with “C”;

Copy codeThe code is as follows:
select cno,cname from Course where cno like ‘C%05’

(29) some students do not take an exam after taking a course, so they have records of elective courses, but have no exam results. Try to find the students who lack exam results 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) find out all the student numbers and course numbers with record of achievement;

Copy codeThe code is as follows:
select sno, cno from Score where grade is not NULL;

(31) search the student number and name of students under 22 years old in the computer department;

Copy codeThe code is as follows:
Select SnO, sname from student where sdept =’computer department ‘and 2014 year (sbirth) < 22

(32) find the student number and score of the course “C001”, and the query results are sorted in descending order;

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 all the students, the query results are arranged in ascending order by department, and the students in the same department are arranged in descending order by age;

Copy codeThe code is as follows:
select * from student order by sdept asc,2014-year(sbirth) desc;

(34) inquire about the total number of students;

Copy codeThe code is as follows:
Select count (*) number of students;

(35) query the number of students who have taken the course;

Copy codeThe code is as follows:
Select count (distinct SnO) from score;

(36) search the student number and grade of the highest score students 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 “C001” course;
 

Copy codeThe code is as follows:
Select max (grade) from score where CNO =’c001 ‘

(38) calculate each course number and the corresponding number of course selection;

Copy codeThe code is as follows:
Select count (SnO) from score group by CNO;

(39) inquire the student numbers and names of the students 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 department ‘ Student.sno=Score .sno group by Student.sno having count(cno)>=2);

(40) connect student and score tables naturally;

Copy codeThe code is as follows:
select student.*,Score.grade from student ,Score where student.sno=Score.sno;

(41) query the indirect antecedent courses of each course (i.e. antecedent courses of antecedent courses) using self connection

Copy codeThe code is as follows:
select a.cno,b.cpno from Course a,Course b where a.cpno=b.cno;

(42) query all the students who have taken the course “C001” and scored more than 90 points by using composite conditional connection;

Copy codeThe code is as follows:
select sname,grade from student,Score where Student.sno=Score.sno and cno=’C001′ and grade>=90;

(43) query the course name and score of each student by using composite conditional connection;
 

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 taken all the 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 numbers and names of all the students who have taken C001 course;

Copy codeThe code is as follows:
select student.sno,sname from student,Score where student.sno=Score.sno and cno=’C001′;
(46) inquire the student number and name of the students who have chosen 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) check the students of “computer department” and those under 23 years old;

Copy codeThe code is as follows:
Select SnO, sname, 2014 year (sbirth) age, class from student where sdept =’computer department ‘or 2014 year (sbirth) < = 23;

(48) query the student numbers and names of all students who have taken both course C001 and 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 number, name, gender and age of the students who have taken the course named “Database Principles”;

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) 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 class =’61 ‘) and class! =’61’;

(51) inquire about the student number, name, gender and age of the students studying in the same department as “Xia Xia”;

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) set up the view 1 of students in computer department;

Copy codeThe code is as follows:
create view view_student
As select SnO, sname, ssex, sbirth, sclass from student where sclass =’13z network ‘

(53) when setting up view 2 for students of “computer department” and requiring modification and insertion, it is still necessary to ensure that the view is only for students of “computer department” class;

Copy codeThe code is as follows:
create view view_student2
As select SnO, sname, ssex, sbirth, scrape from student where scrape ’13z network’ with check option;

(54) set up the view of the students who have chosen “C001” course in “computer department” and define the view 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) establish the view of the students who have taken the course “C001” and scored more than 90 points in the “computer department” class, and define the view 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 class =’13z network’ student.sno in (select student.sno from student,Score where student.sno=Score .sno and grade>90)

(56) define a view to reflect the age of students, and define 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) all the female students’ records in the student table were defined as a view named “V”_ female_ student”;

Copy codeThe code is as follows:
create view v_female_student
as
Select * from student where sex =’female ‘;

(58) define the student number and average score as a view called “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) students younger than 22 years old were found in the “computer department” student view;

Copy codeThe code is as follows:
select * from view_student where 2014-year(sbirth)<=22;

(60) using the view to query the students of “computer department” who have taken “C001” course;

Copy codeThe code is as follows:
select * from v_cs_C001_student1;

(61) modify a student’s name through the “computer department” view in (52);

Copy codeThe code is as follows:
update view_ Student set sname =’wang ‘where SnO = 04261001;

(62) insert a new student record through the “computer department” view in (53).

Copy codeThe code is as follows:
insert into view_ Student 2 (SnO, sname, ssex, sbirth, sclass) values (‘04262004 ‘,’ Zhang XX ‘,’ male ‘,’ 1987 / 11 / 09 ‘,’ total ‘);

(63) delete a student record through the “computer department” view in (53).

Copy codeThe code is as follows:
delete from view_ Student2 where SnO =’04262004’and sname =’Mr. Zhang ‘;

The experimental class is over, I believe that through the practical operation of this class, the partners have a better understanding of the operation of the database table.
The above is the basic operation of query database table, covering almost all kinds of query operations encountered, it is worth operating in person, I believe it is helpful for your learning.

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]