PHP Database 45 Problems Finishing ~ ~ ~ La La la

SELECT * FROM Student
--II: Querying all records in the student table
SELECT * FROM score WHERE degree>60 and degree<80
--Four: query all records from 60 to 80 in the score table
SELECT * FROM score WHERE degree in (' 85 ', ' 86 ', ' 88 ');
--Five: Check the records in the score table for scores 85, 86 or 88.
SELECT * FROM student WHERE Class = ' 95031 ' and Ssex = ' 0 ';
--Check the student table for "95031" class or sex for "female" student records.
SELECT * FROM student ORDER by Class;
--Query all records of the student table in descending order of class.
SELECT SUM (class = 95031) from student;
--Check the number of students in "95031" class. 、
SELECT avg (degree) from score;
--Check the average score of the "95033" class students.
SELECT Cno from score GROUP by Cno
--Group first
SELECT avg (degree) from score GROUP by Cno
--then use the average to find the average score for each course. Select
SELECT * FROM score where Cno = 3-105 and degree > 109
--19, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.
Select distinct depart from Teacher
--Query The teacher all the units that are not duplicated depart column.
Select Cno,avg (degree) from score where Cno like ' 3% ' GROUP by Cno have Count (Cno) >5;
---12. Check the average score of the course with at least 5 students enrolled in the score table and begin with 3.
Select Sno from score where degree >70 and degree<90
--13, the query score is greater than 70, less than 90 of the SNO column.


SELECT * FROM student Qwe joins score ZXC on Qwe. Sno=zxc. Sno;
--Merge the tables, merge the student and the score to join the left or right in front of the join;

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 ')
--Resume Grade form

SELECT * FROM score WHERE Sno = 109 and Cno = 3-105;


--The following
--41. Query "male" teachers and their courses.

Select Cname from Course where Tno in (select Tno from teacher where tsex= ' 1 ');

Select Tname,cname from Course,teacher where course. Tno = teacher. Tno and teacher tsex= ' 1 ';
Select Tname,cname from Course,teacher where course. Tno=teacher. Tno and teacher. tsex= ' 1 ';

--44, inquiries 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 ')

14. Sname, CNO and degree columns for all students are queried.

Select Sname,cno,degree from score join student on SCORE.SNO =student.sno---connection query

15. Check the SNO, CNAME and degree columns of all students.

Select Sname,sno,degree from score join course on SCORE.CNO =course.cno

16. Check the sname, CNAME and degree columns of all students.

Select Cname,sname,degree from score join student on Student.sno=score.sno join course on SCORE.CNO=COURSE.CNO

Select Cname,sname,degree from Score, student,course where Student.sno=score.sno and Score.cno=course.cno



--19, now query all students Sno, CNO and rank column.

Select Sno,cno,rank from score, grade where score.degree between Low and UPP

--20, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.

SELECT * FROM score where cno= ' 3-105 ' and degree> (select degree from score where sno= ' 109 ' and cno= ' 3-105 ')

SELECT * FROM score where cno= ' 3-105 ' and degree > (select MAX (degree) from score where sno= ' 109 ')

---21. Check the number of students who choose to learn more courses score score is a record of non-highest scores.

SELECT * FROM score where Sno in (select Sno from Score GROUP by SNO have Count (*) >1) and degree < (select Max (Degr EE) from score)

SELECT * FROM score a where Sno in (select Sno to score GROUP by SNO have Count (*) >1) and degree < (select Max (DE Gree) from score b where b.cno=a.cno)

--22, the query score is higher than the study number of "109", the course number is "3-105" all records of the results.

SELECT * FROM score where cno= ' 3-105 ' and degree> (select degree from score where sno= ' 109 ' and cno= ' 3-105 ')

--23, inquiry and study number for 108 of the students who were born in the same year Sno, Sname and Sbirthday column.

SELECT * FROM student where year (sbirthdy) = (select year (sbirthday) from student where sno= ' 108 ')

--24, query "Zhang Xu" teachers teaching students results.

Select degree from score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname= ' Zhang Xu '))

--25. The name of the teacher who has more than 5 students who have enrolled in a course.

Select Tname from teacher where TNO in (select Tno from Course where Cno in (select Cno from Score GROUP by Cno has COU NT (*) >5))

--26. Check the records of all students in class 95033 and 95031.

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

SELECT * from student where class in (' 95033 ', ' 95031 ')



--27, the inquiry has 85 points above the course CNO.

Select distinct CNO from score where degree>85

--28. Find out the grade table of "computer Department" teacher teaching course.

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

--29, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.

Select Tname,prof from Teacher where depart= ' computer system ' and Prof not in (select Prof from Teacher where depart= ' electronic Engineering Department ')

Union

Select Tname,prof from Teacher where depart= ' electronic engineering ' 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 B.depart!=a.depart)

--30, 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 * FROM score where cno= ' 3-105 ' and Degree>any (select degree from score where cno= ' 3-245 ') Order by degree desc--a NY any one, all of them



---31, the inquiry elective number is "3-105" and the result is higher than elective number is "3-245" the course schoolmate'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 ')

--32. Check the name, sex and birthday of all teachers and classmates.

Select Tname,tsex,tbirthday from Teacher


--Two:
Select Sname,ssex,sbirthday from Student

--33, to inquire all "female" teacher and "female" schoolmate's name, Sex and birthday.
--Student

Select Sname,ssex,sbirthday from Student where ssex= ' 0 ';


--Teacher
Select Tname,tsex,tbirthday from Teacher where tsex= ' 0 ';

--34. The results of the students who have scored lower than the average grade of the course.

SELECT * FROM Score a WHERE degree< (select AVG (degree) from score b where a.cno=b.cno);

--35, query all the teachers tname and depart.

Select Tname,depart from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score Group by CNO))

Select Tname,depart from teacher where TNO in (select Tno from Course where CNO in (select distinct CNO from score))
--36. Tname and depart of all teachers who have not been lectured.

Select Tname,depart from teacher where TNO in (select Tno from course where CNO not in (select distinct CNO from score))

--37. Check the class number of at least 2 boys.
Select class from student where ssex= ' 1 ' GROUP by class has count (*) >1;

---38, query the student table not the surname "Wang" the classmate record.

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


--39 questions will not be written, focus on the year;

--39. Query the maximum and minimum sbirthday date values in the student table.

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

--40. Query all records in the student table in the order of the class number and the age from large to small.

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

--41. 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 '

--42, the SNO, CNO and degree columns of the highest score of the students.

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

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

--43, inquiries and "Li June" with the gender of all students sname.

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

--44, inquiries 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 ');

--45. Check the scores of all the "male" students who have 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 ');

PHP Database 45 Problems Finishing ~ ~ ~ La La la