MySQL Database 45 Exercises

--The first question queries the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student;
--The second question queries all the teachers ' units that are not duplicates of the depart column.
Select distinct depart from Teacher;
--third question, query all records of student table
SELECT * from Student;
--The fourth question, query the score table in the score from 60 to 80 of all records.
SELECT * from score where degree between and 80;
--The fifth question, the query score table in the score is 85, 86 or 88 records.
SELECT * FROM score where degree = or degree = or degree = 88;
--The sixth question, inquires the student table "95031" class or the sex is "the female" the classmate record.
SELECT * from Student where Class = 95031 or Ssex = ' female ';
--question seventh, querying all records of the student table in descending order of class.
SELECT * from Student ORDER by Class ASC;
--eighth, all records of the score table are queried in CNO ascending, degree descending order.
SELECT * FROM Score ORDER by Cno ASC, Degree desc;
--The Nineth question, the number of students inquiring about the "95031" class.
Select COUNT (*) from Student where Class = 95031;
--The tenth question, query the highest score in the score table student number and course number. (sub-query or sort)
Select Sno,cno from score where degree = (select Max (degree) from score);
--The 11th question, the average score of each course of inquiry.
Select AVG (degree) from score where cno= ' 3-245 ';
Select AVG (degree) from score where Cno = ' 3-105 ';
Select AVG (degree) from score where Cno = ' 6-166 ';
Select distinct Cno from score;
Select Cno,avg (degree) from score where select distinct Cno from score;
-This is the wrong course-------------------------------------------------------------------------------------
Select Cno,avg (degree) from score Group by Cno;
--the average score for the 12th and the 5 students enrolled in the score table and the course starting with 3.
Select Cno,avg (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >4;
--First take the first letter of 3 after the grouping is completed, the filter condition is that the number of CNO is greater than or equal to 5;
You cannot add aggregate functions after--where-----------------------------------------------------------------------------------------
--The 13th question, the query score is greater than 70, less than 90 sno column.
Select Sno,degree from score where degree>70 and degree<90;
--Question 14th, query all students sname, CNO and degree columns.
Select Sname,cno,degree from Student join score on student.sno = Score.sno;
--Method Two
Select Student.sname,score.cno,score.degree from student,score where student.sno = Score.sno;
--Question 15th, query All students ' sno, CNAME and degree columns.
Select Sno,cname,degree from Course join score on (score.cno = COURSE.CNO);
--Question 16th, query all students ' sname, CNAME and degree columns.
Select Sname,cname,degree from Student join score on (Student.sno = Score.sno) join Course on (course.cno = SCORE.CNO);
--the 17th question, query "95033" class student's average score.
Select Class,avg (degree) from score joins Student on (Student.sno = Score.sno) where Class = 95033;
--the 18th question, assuming that a grade table is created using the following command:
CREATE table grade (low int (3), Upp int (3), rank char (1))
Insert into grade values (90,100, ' A ');
Insert into grade values (80,89, ' B ')
Insert into grade values (70,79, ' C ')
Insert into grade values (60,69, ' D ')
Insert into grade values (0,59, ' E ')
--Sno, CNO and rank columns are now available for all students.
Select Sno,cno,rank from grade join score;
Select Sno,cno,rank from grade join score on (degree between low and UPP);
--the 19th question, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.
Select degree from score where Sno = 109 and Cno = ' 3-105 ';

SELECT * from score where degree > (select degree from score where Sno = 109 and Cno = ' 3-105 ') group by Sno have Cno = ' 3-105 ';

Select Sno from score where Cno = ' 3-105 ' and degree > (select MAX (degree) from score where Sno = 109 and Cno = ' 3-105 ' );

--the 20th question, inquires score the student to choose to learn many courses the grade is the non-highest score record.
Select Sno from Score GROUP by Sno have Count (Cno) >1--Choose a number of students
Select Max (degree) from score--highest score
SELECT * FROM score where degree < (select Max (degree) from score)-Student information less than the highest score
--Selecting students with multiple courses and not top grades
Select Sno from score where degree < (select Max (degree) from score) GROUP by Sno have Count (Cno) >1;
--All information of the school number
SELECT * FROM score where Sno in (select Sno from score where degree < (select Max (degree) from score) GROUP by Sno hav ing count (Cno) >1)
--The 21st question, the query result is higher than the study number is "109", the course number is "3-105" all records of the result.
SELECT * from score where degree > (select MAX (degree) from score where Sno = 109) and Cno = ' 3-105 ';
--The 22nd question, inquiry and student number for 108 of all students born in the same year Sno, Sname and Sbirthday column.

Select Sno,sname,sbirthday from Student where year (sbirthday) = (select year (sbirthday) from Student where Sno = 107);

--The 23rd question, inquires "Zhang Xu" the Teacher classroom student achievement.
Select Tno from Teacher where tname = ' Zhang Xu '
Select Cno from Course where Tno = (select Tno from Teacher where tname = ' Zhang Xu ')
Select degree from score where Cno = (select Cno from Course where Tno = (select Tno from Teacher where tname = ' Zhang Xu '))
--24th, the name of the teacher who has more than 5 students who have enrolled in a course.
Select Cno from Score GROUP by Cno have Count (Cno) >5
Select Tno from Course where Cno in (select Cno from Score GROUP by Cno have Count (Cno) >5)
Select Tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score GROUP by Cno has Cou NT (Cno) >5))

-25th, check the records of 95033 classes and 95031 classes of all students.

SELECT * from Student where class = 95033 or class = 95031

--The 26th question, the inquiry existence has 85 points above the result course cno.

Select distinct Cno from score where degree > 85

--the 27th question, the questionnaire of "computer department" teacher's teaching course.

Select Tno from Teacher where depart = ' computer system '
Select Cno from Course where Tno in (select Tno from Teacher where depart = ' Computer Department ')
SELECT * FROM score where Cno in (select Cno from Course where Tno on (select Tno from Teacher where depart = ' computer system '))

--The 28th question, the query "computer department" and "Electronic Engineering department" different titles of teachers ' Tname and Prof.

Select Tname,prof from Teacher where Prof in (select Prof from Teacher where depart = ' computer system ' or depart = ' Electronic Engineering ' GROUP BY Prof having count (Prof) = 1)
--select Prof from Teacher where depart = ' computer Department ' or depart = ' Electronic Engineering ' GROUP by PROF have count (Prof) = 1

--the 29th question, the inquiry elective number is "3-105" the course and the result is at least higher than the elective number "3-245" Schoolmate's CNO, Sno and degree, and according to degree from high to low order.

Select Cno,sno,degree from score where Cno = ' 3-105 ' and degree >= (select MAX (degree) from score where Cno = ' 3-245 ')
ORDER BY degree ASC

--The 30th question, the inquiry elective number is "3-105" and the result is higher than the elective number "3-245" the classmate's CNO, Sno and degree.
Select Cno,sno,degree from score where Cno = ' 3-105 ' and degree >= (select MAX (degree) from score where Cno = ' 3-245 ')

--Question 31st, query all teachers and classmates name, sex and birthday.

Select Tname,tsex,tbirthday from Teacher Union select Sname,ssex,sbirthday from Student

--the 32nd question, query all "female" teacher and "female" classmate's name, Sex and birthday.

Select Tname,tsex,tbirthday from Teacher where tsex = ' Female ' union select Sname,ssex,sbirthday from Student where Ssex = ' female '

--The 33rd question, the results of the students who are less than the average grade of the course score table.

Select Cno,avg (degree) from score Group by Cno;
SELECT * FROM Score joins (select Cno as Pno,avg (degree) as PJ from score Group by Pno) as PJB on (Score.cno = PJB. Pno)
SELECT * FROM Score joins (select Cno as Pno,avg (degree) as PJ from score Group by Pno) as PJB on (Score.cno = PJB. Pno)
where degree < PJ;

--The 34th question, inquires all teachers ' tname and depart.

SELECT * FROM Score GROUP BY Cno

Select distinct Tname,depart from score joins Course on course.cno = Score.cno joins Teacher on teacher.tno = Course.tno whe Re Course.cno in
(select Cno from Score Group by Cno)

--The 35th question, inquires the tname and depart of all the teachers who have not lectured.
Select Cno from Score GROUP by Cno
Select Tno from Course where Cno not in (select Cno from Score Group by Cno)
Select Tname,depart from Teacher where Tno in (select Tno from Course where Cno not in (select Cno from Score GROUP by Cno ))

--the 36th question, check the class number of at least 2 boys.

SELECT * FROM Student GROUP by Class have count (Ssex) > 1 and Ssex = ' Male '
Select Class from Student where Sno in (select Sno to Student GROUP by Class have count (Ssex) > 1 and Ssex = ' male ')

--the 37th question, inquires the student table in the surname "Wang" the classmate record.

SELECT * from Student where Sname not in (select Sname from Student where Sname like ' li% ')

--the 38th question, inquires the student table in the surname "Wang" the classmate record.

Select Sname from Student where sname don't like ' Wang% ';

--Question 39th, querying the maximum and minimum sbirthday date values in the student table.

Select Max (sbirthday) as ' Maximum Value ', min (sbirthday) ' Minimum ' from Student

--The 40th question, the class number and the age from the big to the small order to query the student table all records.

Select Class,sbirthday from Student Order by class Desc,sbirthday ASC

--Question 41st, query "male" teachers and their courses.

Select CNAME from course where TNO in (select TNO from teacher where tsex= ' man ')

Select Tname,cname from teacher, course where teacher.tno=course.tno and tsex= ' men '

--The 42nd question, the query highest score schoolmate's Sno, CNO and degree column.

(1) SELECT * FROM score where degree = (select Max (degree) from score)

(2) Select top 1 * from score ORDER BY degree DESC

--The 43rd question, inquiry and "Li June" all the students of the same sex sname.

Select Sname from Student where ssex= (select Ssex from Student where Sname= ' Li June ')

--The 44th question, query and "Li June" with the same sex and classmates sname.

Select Sname from Student where ssex= (select Ssex from Student where Sname= ' Li June ') and class= (select class from Student where Sname= ' Li June ');

--The 45th question, the questionnaire of all the "male" students who have been enrolled in the "Introduction to Computer" course.

Select degree from score where Sno in (select Sno from student where ssex= ' 1 ') and Cno in (select Cno from course where Cna Me = ' Introduction to computer ');

MySQL Database 45 Exercises