Experimental content
According to database db_ TB in student_ Student table, tb_score,tb_course, complete the following query statement
Table structure
The structure of the table is as follows:
- tb_student(sno,sn,dept,sex,birthday,polity)
- tb_score(sno,cno,score)
- tb_cource(cno,cn,ct)
Task solution
Task one
Query the gender information of students and remove duplicate records.
use db_student20 select distinct sex -- distinct duplicate removal from tb_student
Task 2
Query TB_ There are several departments in the student table
use db_student20 select COUNT(distinct dept) as Total number of lines from tb_student
Task three
Calculate TB_ The number of students in each department in the student table.
select dept as Line name,COUNT(*) as Total number from tb_student group by dept
Task 4
Check the number of people in your class.
-- Method 1 select count(*) as Total number from tb_student where sno like 'XXXXXXX%' -- Method 2 select count(sno) Total number from tb_student where LEFT(sno,8)='XXXXXXXX'
Task five
It is required to display the student number, name, class number and grade of each student
select stu.sno as Student number,sn as full name,cno Course number,score achievement from tb_student stu,tb_score sco -- Same student grade information,Avoid conflict,Rename table reference for the same field where stu.sno=sco.sno
Task 6
Display the scores of each student with more than 90 points. It is required to display the student number, name, class number and score
select stu.sno as Student number,sn as full name,cno Course number,score achievement from tb_student stu,tb_score sco where stu.sno=sco.sno and score > 90 -- Add performance constraints
Task 7
Each subject has the highest score and the lowest score, and the subject name is required to be displayed.
select cn Course name,MAX(score) as Highest score,MIN(score) as Lowest score from tb_course co,tb_score sc where co.cno=sc.cno group by cn
Task 8
How many people in each subject need to take a make-up examination, and the subject name is required to be displayed.
select cn Course name,COUNT(*) as Number of make-up examination from tb_course c,tb_score sc where c.cno=sc.cno and score<60 -- cno Course number group by cn
Task 9
Each class has the highest and lowest score in basic computer science.
select LEFT(sno,8) as class,cn Course name,MAX(score) Highest score,MIN(score) Lowest score from tb_course c,tb_score sc where c.cno=sc.cno group by LEFT(sno,8),cn having cn='MATLAB And system simulation' -- Select the basic course name
Task 10
Query the course selection of all students, including students who have not selected courses (realized by left connection and right connection)
--left join select * from tb_student s left join tb_score sc on(s.sno=sc.sno) --right join select * from tb_score sc right join tb_student s on(sc.sno=s.sno)
Task 11
List tb_student number and name of students with the same department as "XXX" in student table
--Subquery select sno Student number,sn full name from tb_student where dept =any(select dept from tb_student where sn='XXX')--'XXX Department' --Self connection select s1.sno,s1.sn from tb_student s1,tb_student s2 where s1.dept=s2.dept and s2.sn='XXX'
Task 12
Query the names of students taking c03 courses
--Subquery select sn from tb_student where sno in(select sno from tb_score where cno='c03') --connect select sn from tb_student s,tb_score sc where s.sno=sc.sno and cno='c03'
Task 13
Query the student number and name of students who do not choose c04 courses
select sno,sn from tb_student where sno not in(select sno from tb_score where cno='c04')
Task 14
Query the student number and subject of the student with the highest score in each subject
select sno Student number,cn Course name,score achievement from tb_score sc,tb_course c, ( select cno, MAX(score) as Highest score from tb_score group by cno ) as b where sc.cno=c.cno and c.cno=b.cno and score=b.Highest score
Task 15
Displays the number and name of the subject to be retested
-- Subquery select cno,cn from tb_course where cno in(select distinct cno from tb_score where score<60) -- connect select distinct c.cno,cn from tb_course c,tb_score sc where c.cno=sc.cno and score<60
Task 16
Subjects and number of persons failing more than one.
select cn,COUNT(*)as Number of failed from tb_course c,tb_score sc where c.cno=sc.cno and score<60 group by cn having COUNT(*)>1
Task 17
Displays the student number of c01 subject failing, corresponding to the information in all columns of the student table.
select * from tb_student where sno in(select sno from tb_score where cno='c03'and score<60)
Task 18
List the highest score, lowest score and average score of each subject in the score sheet, and display the names of subjects with an average score greater than 75 in the score sheet, and sort them in descending order according to the average score
select cn,MAX(score) Highest score,MIN(score) Lowest score,AVG(score) average from tb_course c,tb_score sc where c.cno=sc.cno group by cn having AVG(score)>75 order by average desc
Task 19
Query the student number and name of the students who took the elective course of "database principle and application".
-- Subquery select sno,sn from tb_student where sno in ( select sno from tb_score where cno=(select cno from tb_course where cn='Principle and application of database') ) select sno,sn from tb_student where sno in ( select sno from tb_score sc,tb_course c where sc.cno=c.cno and cn='Principle and application of database' ) -- connect select sn,sn from tb_student s,tb_score sc,tb_course c where s.sno=sc.sno and sc.cno=c.cno and cn='Principle and application of database'
Task 20
Inquire about the student number and grades of students whose course number is "C02" and whose grades are higher than "Wang Lei".
select sno,score from tb_score where cno='c03'and score> ( select score from tb_score sc,tb_student s where sc.sno=s.sno and cno='c03' and sn='XXX' -- Multi tier subquery select sno,score from tb_score where cno='c03'and score> ( select score from tb_score where cno='c03'and sno=(select sno from tb_student where sn='XXX' ) ) )
Task 21
Query the student number of students with the same score as "database principle and application" course of "Wang Lei".
select distinct sno from tb_score sc,tb_course c where sc.cno=c.cno and cn='Principle and application of database' and score= ( select score from tb_score where sno= ( select sno from tb_student where sn='XXX' )and cno=(select cno from tb_course where cn='Principle and application of database') ) select distinct sno from tb_score where score= ( select score from tb_score where sno= ( select sno from tb_student where sn='XXX' ) and cno=(select cno from tb_course where cn='Principle and application of database') )