SQL Job Title

Job Title:
1. Check all records of elective course ' 3-105 ' and score 60 to 80.
Note: Used to specify a range using between and, you can also use an and connector;
Answer:
Law 1:select * from SC where course number = ' 3-105 ' and score between;
Law 2:select * from SC where course number = ' 3-105 ' and score > score <;
2. Search for records with 85, 86, or 88 results.
Note: Used to make a collection use the In keyword, or you can use the or connector;
Answer:
Method 1:select * from SC where score =85 or score =86 or score =88;
Method 2:select * from SC where score in (85,86,88);
3. Check the number of students in ' 95031 ' classes.
Note: Count (*) is used to calculate the total number of results;
Answer:
Select COUNT (Class) as student number from student where class = ' 95031 ';
4. The query minimum score is greater than 70, and the highest score is less than 90 of the study number column.
Note: Having the following aggregate function: The avg,min,max,count;having statement can only follow the following: GROUP BY statement;
Answer:
Select study Number, min (score) as the lowest score, Max (score) as the highest score from the SC GROUP by study number having min (score) >70 and Max (score) <;
5. Check the average score of courses that have at least 5 students and begin with 3.
Note: The group BY statement is placed behind the where statement to indicate what conditions are grouped;
Answer:
Select AVG (score) as average score from SC where course number like ' 3% ' GROUP by course number having count (course number) >= 5;
6. Questionnaire for students with average scores greater than 80 points
Note: Having the following aggregation function, AVG ();
Answer:
SELECT * FROM SC GROUP BY study number having AVG (score) >;
7. Check the average score for each student's selected course in ' 95033 ' class.
Note: This problem is based on the ' 95033 ' class student's number of groups, using the where statement to restrict GROUP BY statement grouping conditions;
Answer:
Select study number, AVG (score) as average score from SC where study number in (select number from student where class = ' 95033 ') group by study number;
8. Take elective ' 3-105 ' as an example to check the records of all students who have a higher score than ' 109 '.
Note: This topic uses a typical nested query, layers in depth;
Answer:
SELECT * FROM student where study number in (select number from SC where course number = ' 3-105 ' and score >
(select result from SC where study number = ' 109 ' and course number = ' 3-105 '));
9. Search for the student's school number, name and age of the students of the same school number as ' 108 '.
Note : When the result set of a query returns only one time, the function of the keyword in is equivalent to the function of ' = ', but note that ' = ' can only be used with the return result set only one, while in can have multiple results;
Answer:
Law 1:select number, name, age from student where Age = (select age from student where study number = ' 108 ');
Law 2:select number, name, age from student where age in (select age from student where study number = ' 108 ');
10. Inquire about the course number of the ' Zhang Xu ' instructor and take the student's number and grade of the course.
Note: This topic uses a connection to a table: inner joins, which concatenate multiple tables together to form a new table, with the criteria for the connection being the same column;
Answer:
FA 1:select Course number from course where teacher number = (Select teacher number from teacher where name = ' Zhang Xu '); Select study number, score from SC where course number in (select Course number from course where teacher number in
(select teacher number from teacher where name = ' Zhang Xu '));
fa 2:select teacher. Name as teacher name, course. Course number, student. Name as student name, student. Academic number, score from teacher inner join (course INNER JOIN ( SC INNER JOIN student on student. =sc. Number) on course. Course number =SC. Course number) on course. Teacher number =teacher. Teacher number where teacher. Name = ' Zhang Xu ';
11. Find out the names of teachers who have more than 5 students in their courses.
registration: This problem uses nested query and join table two methods, showing the same effect;
Answer:
Law 1:select name as teacher name from teacher where teacher number in (select teacher number from course where course number in (select course number from SC GROUP by course number H aving count (*) > 5));
Law 2:select name as teacher name from teacher inner JOIN (course INNER join SC on course. Course number =SC. Course number) on teacher. Teacher number =course. Teacher Number Gro Up by SC. Course number having count (*) > 5;
12. Search for the names and titles of teachers with different titles in the Department of Computer Science and electronic engineering.
Note: Different job titles mean that there is only one returned result set after grouping;
Answer:
select name as teacher name, level as title from teacher Group by level having count (*) = 1;
13. Inquire about the course number, number and grade of the class with the elective number ' 3-105 ' and at least higher than the number of students enrolled in the ' 3-245 ' course, ranked in order from highest to lowest score.
Note: any represents any one;
Answer:
SELECT * FROM SC where course number = ' 3-105 ' and score > any (select result from SC where course number = ' 3-245 ') Order by result Desc;
14. Check the course number, number and results of the class with the elective number ' 3-105 ' which is higher than the elective number ' 3-245 ' course.
Note: all means all;
Answer:
SELECT * FROM SC where course number = ' 3-105 ' and score > all (select result from SC where course number = ' 3-245 ') Order by result Desc;
15. List the names, genders and ages of all teachers and classmates.
Note: This topic joins all tables together:
Answer:
select student. Name as student's name, student. Gender as student gender, student. Age as student age,
teacher. Name as teacher's name, teacher. Gender as teacher sex, teacher. Age as teacher age from
student INNER JOIN (SC INNER JOIN (course inner join teacher on course. Teacher number =teacher. Teacher number)
On SC. Course number =course. Course number) on student. Study number =SC.
16. Check the score table for students with a lower average score than the ' 3-105 ' course.
Answer:
SELECT * from SC where score < (select AVG (score) from SC where course number = ' 3-105 ') and course number = ' 3-105 ';
17. Check the scores of students who have a lower average score than the course.
Note: This problem is the hardest of all problems, the following method is very classic, please carefully consider, first in accordance with the course of the group, to find out the average score of each course is smaller than all the results;
Answer:
SELECT * from SC where score < any (select AVG (score) from the SC Group by course number) Order by course number ASC;
18. List all instructors ' names and majors.
Note: Names and majors involve two tables course tables and teacher tables, just connect the two tables to solve the problem;
Answer:
Select teacher. Name as teacher name, professional from teacher where teacher number in (select teacher number from course where course number in (select course number from SC Group By course number));
19. List the names and professions of all teachers who have not been given lectures.
Note: Answer the previous question;
Answer:
Select teacher. Name as teacher name, professional from teacher where teacher No in
(select teacher number from course where course number in (select course number from the SC group by Course number));
20. List the class number of at least 2 boys.
Note: The student table can be grouped according to the condition of sex = ' man ';
Answer:
Select Class as class from student where sex = ' Male ' GROUP by class having count (*) >= 2;
21. Check the student records for the surname ' Wang '.
Answer:
Law 1:select * from student where name is not like ' King% ';
Method 2:select * from student where name not in (select name from student where name like ' King% ');
22. Check the student's number, course number and results for the highest score of each course.
Note: The technique of this problem is to check the score table according to the highest score;
Answer:
SELECT * from SC where score in (select Max (score) from the SC Group by course number);
23. Check with ' Li June ' for the same sex and classmates name.
Note: nested queries;
Answer:
select name from student where sex = (select sex from student where name = ' Li June ') and
class = (select Class from student where name = ' Li June ');
24. Search for ' male ' teachers and their courses.
Note: First analyze the table involved in the problem;
Answer:
select name as teacher name, course name as course from teacher inner join course on teacher. Teacher number =course. Teacher number where gender = ' male ';

SQL Job Title