SQL Basic 45 Questions

--Query the sname, Ssex, and class columns of all records in the student table.
SELECT Sname,ssex,class from Student

--Query The teacher all the units that are not duplicated depart column.
SELECT distinct depart from teacher

--Query all records of the student table.
SELECT * FROM Student

--Query all records in the score table for scores from 60 to 80.
SELECT * from score where grade>60 and grade<80

--query for records with scores of 85, 86 or 88 in the score table.
SELECT * from score where grade in (85,86,88)

--Check the student table for "95031" class or sex for "female" student records.
SELECT * from Student where class= "95031" or Ssex=false

--Query all records of the student table in descending order of class.
SELECT * from Student ORDER BY Class Desc

--Query all records of the score table in CNO Ascending, degree descending order.
SELECT * FROM Score ORDER by Cno Asc,grade desc

--Check the number of students in "95031" class.
SELECT Count (*) from student where Class = ' 95031 '

--Query the student number and course number of the highest score in the score table. (sub-query or sort)
SELECT * from score order BY grade DESC

--Check the average score for each course.
SELECT Cno, AVG (grade) from score GROUP by Cno

--Check the average score of the course with at least 5 students enrolled in the score table and start with 3.
SELECT Cno, AVG (grade) from score where Cno like ' 3% ' GROUP by Cno have Count (Sno) >=5

SELECT avg (grade) from score where Cno in (
SELECT Cno from score where Cno like ' 3% '
and
Cno in (
SELECT Cno from score GROUP by Cno have Count (Sno) >=5
)
)
GROUP BY Cno
--SNO Columns with a query score greater than 70 and less than 90.
SELECT Sno from score where grade>70 and grade<90

--Query all students for sname, CNO and degree columns
SELECT Sname,cno,grade from score joins student on score. Sno=student. Sno

--Query all students for SNO, sname and degree columns
SELECT Sno,cname,grade from score joins course on score. Cno=course. Cno

--Query all students for sname, CNAME, and degree columns

SELECT Sname,cname,grade from score joins student on score. Sno=student. Sno
Join course on score. Cno=course. Cno

SELECT Sname,cname,grade from Student,course,score where score. Cno=course. Cno and
Score. Sno=student. Sno

--Check the average score of "95033" class students

SELECT avg (grade) from score joins student on score. Sno=student. Sno where class= "95033"
--
--Use the following command to create a grade table
CREATE TABLE Grad (
low int (3),
UPP int (3),
Rank char (1)
)
INSERT into Grad values (90,100, ' A ')
INSERT into Grad values (80,89, ' B ')
INSERT into Grad values (70,79, ' C ')
INSERT into Grad values (60,69, ' D ')
INSERT into Grad values (0,59, ' E ')

--Query all students for SNO, CNO and Rank columns
SELECT Sno,cno,rank from Score,grad where grade between low and UPP

--Check the records of all the students who have achieved more than 109th of the students in the elective 3-105 course

SELECT grade from score where cno= ' 3-105 ' and grade> (
SELECT grade from score where sno=109 and cno= ' 3-105 '
)

--a record of the number of students who have enrolled in multiple courses in the score score is not the highest grade
SELECT sum (grade) from score Group by Sno have Count (Sno) >=2
Order by sum (grade) desc

SELECT sum (grade) from score where Sno in (
SELECT Sno from score GROUP by Sno have Count (Sno) >=2
)
GROUP BY sno--Group SUM, otherwise the total is calculated

--All records of the results of the enquiry score higher than the academic number 109 and the class number 3-105
SELECT * FROM score where grade> (
SELECT grade from score where sno=109 and cno= ' 3-105 '
)

--Query the students ' achievement of Beijing-Tianjin teachers ' classroom
SELECT grade from score where Cno in (
SELECT Cno from course where Tno in (
SELECT Tno from teacher where Tname= ' Jing Jin '
)
)

SELECT Grade from score joins course on score. Cno=course. Cno
Join teacher on course. Tno=teacher. Tno where Tname= ' Beijing-Tianjin '

--to inquire about the names of teachers who have more than 5 students taking a course
SELECT tname from teacher where Tno in (
SELECT Tno from course where Cno in (
SELECT Cno from score GROUP by Cno have Count (Sno) >=5
)
)
--GROUP BY Cno

--Check the records of all students in class 95033 and class 95031
SELECT * from score where Sno in (
SELECT Sno from student where class in (95033,95031)
)

--Query for courses with more than 85 marks CNO

SELECT distinct Cname from course join score on course. Cno=score. Cno
where grade>=85

SELECT distinct Cno from score where grade>=85

--To find out the results of the courses taught by computer Department teachers
SELECT grade from score where Cno in (
SELECT Cno from course where Tno in (
SELECT Tno from teacher where depart= ' computer system '
)
)

SELECT Grade from score joins course on score. Cno=course. Cno
Join teacher on course. Tno=teacher. Tno
Where depart= ' computer Department '

--Query Computer department and electronic Engineering department different titles of Tname and Prof Teachers
SELECT tname,prof from teacher where depart= ' computer system ' and Prof not in (
SELECT prof from teacher where depart= ' electronic Engineering Department '
)
UNION ALL
SELECT tname,prof from teacher where depart= ' electronic Engineering Department ' and Prof not in (
SELECT prof from teacher where depart= ' computer system '
)

SELECT tname,prof from teacher a where Prof
Not in (SELECT Prof from teacher b where a.depart! = B.depart)
--Query the elective number "3-105" course and the result is at least higher than the elective number
--CNO, SNO and degree for students of "3-245", sorted by degree from highest to lowest order.
SELECT * from score where cno= ' 3-105 '
> Grade
Any (SELECT grade from score where cno= ' 3-245 '
)
Order BY grade ASC
--All and any
--Query the CNO, Sno and degree of the students with the elective number "3-105" and higher than the elective number "3-245" course.

SELECT * from score where cno= ' 3-105 '
> Grade
(SELECT max (grade) from score where cno= ' 3-245 '
)

--Check the name, sex and birthday of all teachers and classmates.
SELECT Tname,sex from teacher
UNION ALL
SELECT Sname,ssex from Student

--Query the name, sex and birthday of all "female" and "female" students.
SELECT tname,sex from teacher where sex=0
UNION ALL
SELECT sname,ssex from student where ssex=0

--Check the scores of students who have a lower average score than the course.

SELECT * from score where cno= ' 3-245 ' and grade<
(SELECT avg (grade) from score where cno= ' 3-245 '
)
Union
SELECT * from score where cno= ' 3-105 ' and grade<
(SELECT avg (grade) from score where cno= ' 3-105 '
)
Union
SELECT * from score where cno= ' 6-116 ' and grade<
(SELECT avg (grade) from score where cno= ' 6-116 '
)

SELECT * FROM Score joins (select Cno,avg (grade) as a from score group by Cno) as-B on
(B.cno=score. Cno) where Grade<a

SELECT * FROM Score a WHERE grade< (select AVG (grade) from score B where a.cno=b.cno)
--Query the Tname and depart of all instructors.
SELECT Tname,depart from teacher where Tno in (
SELECT Tno from course where Cno in (
SELECT Cno from score GROUP by Cno
)
)

--Query the Tname and depart of all teachers who have not lectured.

SELECT Tname,depart from teacher where Tno not in (
SELECT Tno from course where Cno in (
SELECT Cno from score GROUP by Cno
)
)

SELECT Tname,depart from teacher where Tno not in (
SELECT Tno from course where Cno in (
SELECT distinct Cno from score
)
)

--Check the class number of at least 2 men.

SELECT distinct class from student where Ssex in (
SELECT Ssex from student where Ssex=1 and Ssex in (
SELECT Ssex from student group by Ssex have Count (Ssex) >=2
)
)
SELECT class from student where ssex= ' 1 ' GROUP by class has count (*) >=2

--Query The student table of the students who do not have the surname "Wang" record.

SELECT * FROM student where Sname don't like ' King% '

--Query the name and age of each student in the student table.

SELECT Sno,sname from Student

ALTER TABLE student Add column birthday varchar (30);
Select Sname as ' Name ', (Year (date (today))-year (birthday)) as ' age ' from student;
Select Sname, (Year (today ()))-year (birthday)) from student;
--Query the maximum and minimum sbirthday date values in the student table.
SELECT Max (Sno) from student
Union
SELECT min (Sno) from student

SELECT Max (Sno) as ' Maximum Value ', min (Sno) ' Minimum ' from student
--Query all records in the student table in order of class number and age from large to small.
SELECT * FROM Student Order by class Desc,sno desc

--Query "male" teachers and their courses.

SELECT Cname from course where Tno in (
SELECT Tno from teacher where sex=1
)

--Query the SNO, CNO, and degree columns of the highest scores of students.

SELECT Sno,cno,grade from score where grade in (
SELECT Max (grade) from score
)

--Query and "Li June" with the sex of all the students sname.

SELECT Sname from student where Ssex in (
SELECT Ssex from student where Sname= ' Zhang San '
)

--Query and "Li June" with the same sex and classmates sname.

SELECT Sname from student where class in (
SELECT class from student where Sname= ' Zhang San '
)
and
Ssex in (
SELECT Ssex from student where Sname= ' Zhang San '
)

--Check out the scores of all the "male" students who took the "Introduction to Computer" course.
SELECT grade from score where Cno in (
SELECT Cno from course where cname= ' Introduction to Computers '
)
and
Sno in (
SELECT Sno from student where
Ssex in (
SELECT Ssex from student where ssex=1
)
)

Look, it's okay, just check it out. The logic is not particularly difficult to do when you don't want to move, watch and protect your eyes.

SQL Basic 45 Questions