MySQL Complex usage

Today I learned a few of the complex usage of MySQL, specifically what the name has not been remembered very clearly =-= but the grammar is still remembered. The grammar that I learned today is not the most important thing, logic is. As long as the logic is clear like a problem divided into a few steps to find out which is the first step, which is the second step, which is the third step and so the rest is to put together the statements of each step. But I feel that this is a bit stupid method, but the personal feeling is clearer, after all, not so strong logical ability is not like some big God directly one step to fix.

Tomorrow to write the requirements of the document, feel a little egg pain. As a standard otaku, communication skills are really worrying. Do not know what to ask some questions, slightly show the egg pain, there are sometimes too small to ask the customer will be pressed on the ground friction AH embarrassing.

Let's talk about the current understanding of the requirements document:

Mainly for customers, to determine the specific needs of customers.

Business Requirements Overview: Why companies develop what type of software is capable of what.

Function Description: What this software can do (roughly).

Business process: What this software can do (specifically).

Now can only understand here, do not know whether there is no misguided =-=.

The rest of the exercise top ...

1. query The sname, Ssex, and class columns of all records in the student table.

Select sname, Ssex, class from student;

2, The inquiry teacher all units namely does not duplicate depart column.

Select distinct depart from teacher;

3. query All records of student table.

Select * from student;

4. query all records from 60 to 80 of the scores in the score table.

Select * from score where degree between and 80;

5. Check The record of 85, 86 or 88 in the score table.

Select * FROM score where degree = or degree = or degree = 88;

6, inquires the student table "95031" class or the sex is "the female" the classmate record.

SELECT * FROM student where class = "95031" or ssex = "female";

7. Query the student table for all records in descending order of class.

SELECT * FROM student order by class;

8, in cno Ascending, Degree descending query score all records of the table.

SELECT * FROM Score order by CNO, degree desc;

9. Check The number of students in "95031" class.

Select COUNT (*) from student where class = "95031";

10. Check The student number and course number of the highest score in the score table. (sub-query or sort)

Select Sno, CNO from score where degree = (select Max (degree) from score);

11. Check the average score of each course.

Select CNO, AVG (degree) from score Group by CNO;

12. Check The average score of the course with at least 5 students enrolled in the score table and begin with 3.

Select AVG (degree) from score where CNO in (select CNO from Score GROUP by CNO have count (CNO) > 5) and CNO like "3%" ;

13, the query score is greater than 70, less than 90 of the SNO column.

Select Sno from score where degree > degree < 90;

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

Select A.sname, B.cno, b.degree from student A, score b where A.sno = B.sno;

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

Select A.sno, A.degree, B. CName from score A, course b where a.cno = B.cno;

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

Select A.sname, B.cname, c.degree from student A, course B, score c where A.sno = C.sno and b.cno = C.cno;

17. Check The average score of "95033" class students.

Select AVG (degree) from score where Sno in (select Sno from student where class = "95033");

18. Assume 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 ')

now check all the students Sno, CNO, and rank columns.

Select A.sno, A.cno, B.rank from score a

Join Grade B

-On A.degree >= B.low and A.degree <= B.upp;

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

20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores.

SELECT * from score where degree not in (select Max (degree) from score where CNO on (select CNO from Score group by CNO Ha Ving Tsun (CNO) > 1));

21, The results of the inquiry is higher than the number of "109", the course number is "3-105" of all records.

SELECT * from score where degree > (select degree from score where Sno = 109 and CNO = "3-105")

22. The SNO, sname and Sbirthday of all students who were born in the same year were queried and studied for 108.

Select Sno, Sname, sbirthday from student where year (sbirthday) = (select year (sbirthday) from student where Sno = 108);

23, inquires "Zhang Xu" the Teacher classroom student achievement.

SELECT * FROM score where CNO = (select Cno from course where TNO = (select Tno from teacher where Tname = "Zhang Xu"));

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

Select Tname from teacher where Tno = (select Tno from course where CNO = (select CNO from Score GROUP by CNO have count (CNO) > 5));

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

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

26, the inquiry existence has 85 points above the course CNO.

Select CNO from score where degree > 85;

27. Find out The results table of the "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"));

28, Query "computer Department" and "Electronic Engineering department" different titles of teachers Tname and Prof.

Select Tname, prof from teacher where depart in ("Computer system", "electronic Engineering") and Prof in (select Prof from teacher group by Prof Havin G Count (Prof) < 2);

29, the inquiry elective number is "3-105" course and the result is at least higher than the elective number "3-245" Students

SELECT * FROM score where cno = "3-105" and degree >= (select MAX (degree) from score where cno = "3-245");

30, Cno, Sno and degree, and sorted by degree from highest to lowest order.

30, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree.

Select CNO, sno, degree from score order by degree;

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

Select Tname, Tsex, tbirthday from teacher;

Select Sname, Ssex, sbirthday from student;

32, query all "female" teacher and "female" classmate's name, Sex and birthday.

Select Tname, Tsex, tbirthday from teacher where tsex = "female";

Select Sname, Ssex, sbirthday from student where ssex = "female";

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

SELECT * FROM score where degree < (select AVG (degree) from score where CNO on (select CNO from Score) and CNO in (sel ECT CNO from score);

34. Check the Tname and depart of all classroom teachers .

Select Tname, depart from teacher where TNO in (select TNO from course);

35. Inquire about the Tname and depart of all teachers who have not lectured.

Select Tname, depart from teacher where TNO isn't in (select TNO from course);

36. Check the class number of at least 2 boys.

Select class from student where Ssex >= 2;

37, inquires the student table the surname "the king" the classmate record.

SELECT * FROM student where sname isn't like ("% king%");

38. Check the name and age of each student in the student table.

Select Sname, sbirthday from student;

39. query The maximum and minimum sbirthday date values in the student table.

Select min (sbirthday) from student;

Select Max (sbirthday) from student;

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

SELECT * FROM student order BY class DESC, Sbirthday desc;

41. query "male" teachers and their courses.

Select A.*, b.cname from teacher A, course b where B.tno = A.tno and A.tno in (select TNO from teacher where tsex = "male");

42. Check the Sno, CNO and degree columns of the students with the highest score .

SELECT * from score where degree in (select Max (degree) from score);

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 class = (select Class from student where sname = "Li June") and Ssex = (select Ssex from Studen t where sname = "Li June");

45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.

SELECT * FROM score where Sno in (select Sno from student where ssex = "male") and CNO = (select CNO from course where cname = "Introduction to Computers");

MySQL Complex usage