SQL basic data query Practice II

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')
)

Tags: Database SQL Server T-SQL

Posted by definewebsites on Sat, 16 Apr 2022 14:45:44 +0930

Programs Wiki