- There is a department table, employee table, jobs table and job table as follows_ Grade table, locations table
Create the above five tables first, and then do some exercises;
-- Subquery select * from employees; -- 1.Query the Department whose minimum wage is greater than the minimum wage of department No. 50 id And its minimum wage select min(salary) from employees where department_id = 50; -- 50 Minimum wage of department No select department_id,min(salary) from employees group by department_id; -- Minimum wage for each division grouped select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50); -- Add filter: the minimum wage is greater than the minimum wage of No. 50 department -- 2.Query the employee number of employees in each department whose salary is higher than the average salary of the Department, Name and salary select department_id,avg(salary) from employees group by department_id; -- Group query the average salary of each department, virtual table select e.employee_id,e.first_name,e.last_name,e.salary,e.department_id from employees e inner join (select department_id de,avg(salary) ag from employees group by department_id) avs on e.department_id = avs.de -- For internal connection, it is the Department ID The numbers are equal and correspond where e.salary > avs.ag; -- Plus conditions: the salary of each department is higher than the average salary of the Department -- 3.Query in Department location_id Employee number of the employee working for the 1700 department select * from departments; select department_id from departments where location_id = 1700; -- Query first location_id 1700 Department ID select employee_id from employees where department_id = any (select department_id from departments where location_id = 1700); -- As long as the Department in the employee list ID Amount found ID Corresponding to one any -- 4.The query contains letters in the name u Employee number and name of the employee in the same department select employee_id,last_name,department_id from employees where last_name like '%u%'; -- First query the name u Employee number, name, department number select distinct department_id from employees where last_name like '%u%'; -- Remove the duplicate department number, as long as there is a corresponding department number select employee_id,last_name from employees where department_id in (select distinct department_id from employees where last_name like '%u%'); -- As long as the department number is inside in -- 5.Query the salary grade of the average salary of each department select department_id,avg(salary) from employees group by department_id -- Group query the average salary of each department, virtual table select * from job_grade; -- Grade table select avs.*, g.job_level from (select department_id de,avg(salary) ag from employees group by department_id) avs -- The query is for each department id And average wage, corresponding grade inner join job_grade g on avs.ag between lowest_sal and highest_sal; -- Internal connection, so that the average wage corresponds to the lowest and highest in the grade table, and the grade is dividedExercise 02
Student form:
Chart of accounts:
Transcript:
Grade table:
Create the above four tables first, and then do the following exercises:
- Data modification and deletion
1. Update the address of Xi'an to Xi'an Keji 2nd road
2. Change the email of S1001 to an empty string
3. Update the name of the second course to java foundation, the class hour is 60, and the class is grade two
4. Increase the score of S1001 with course No. 2 by 5 points
5. Update the grade of S1004 with course No. 3 to 60 and the examination time to October 10, 2015
6. The absence of S1004 course No. 2 is verified
7. Delete the computer network course - Data query
1. Query the information of all first grade students.
2. Check the names and telephone numbers of all second graders.
3. Query the information of all first grade female students.
4. Query the account information with more than 60 class hours.
5. Query the subject name of grade 2
6. Check the names and addresses of male students in grade two.
7. Query the name and grade information of students without e-mail.
8. Query the name and grade information of male students whose birth date is after 1992.
9. Score information of "basic principles of computer" subject examination dated July 3, 2015
10. Query the first grade student information according to the date of birth.
11. Query the examination information of the subject with No. 1 in the order of high score to low score.
12. Query the score information of the top 2 students who took the "MySQL in-depth" test on July 1, 2015.
13. Query the subject name and class hour with the most class hours.
14. Query the grade and name of the youngest student.
15. Query the subject in which the lowest score of the exam appears
16. Query all examination information of students with student number "s1001" and display it in chronological order.
17. Query the information of students over the age of 25.
18. Query the students' birthday information in January
19. Check the name and grade of the students whose birthday is today.
20. Freshmen are assigned an Email address when they enter the school. The rules are as follows: S1 + current date + @bd.com
21. Query the name, telephone number and address of students whose address is "Yanta District"
22. The query name contains the words "computer", subject name, class hour and grade, and is displayed from low to high by grade.
23. The inquiry telephone contains the student's name, address and telephone number beginning with "130".
24. Inquire the student number, name and address of the surname "Zhao".
25. Count the total number of girls in grade one.
26. Query Li Si's total score
27. Total scores of students with student number s1003 in all courses
28. Average score of students with student number s1003.
29. Query the highest score, lowest score and average score of the first grade subject "Mysql".
30. Query the total class hours of each grade and arrange them in ascending order.
31. Query the average score of each student participating in the examination. (Group by student number)
32. Query the average score of each course and arrange it in descending order. (group by course)
33. Query the total scores of all exams each student takes and arrange them in descending order. (group by student number)
34. Query the average age of grade one.
35. Query the number of students in Xi'an of each grade.
36. Query the records of students who have passed the average score among the students participating in the examination, and arrange them in descending order according to the average score
37. Query the student number, name and failure times of students who failed in at least one examination.
38. Inquire the student's name, grade name and contact number.
39. Query the subject name, grade name and class hours with grade No. 1.
40. Query the name, score and test date of students participating in the test with subject No. 1.
41. Query the subject name, score and test date of the student with student number s1001.
42. Query the reference information of all subjects (some subjects may not have been tested)
43. Query the information of subjects that have not been tested.
-- Data modification and deletion -- 1.Update the address of Xi'an to Xi'an Keji second road update student set address = 'Xi'an Keji 2nd Road' where address = 'Xi'an City'; select * from student; -- 2.take S1001 of email Modify empty string update student set email = '' where studentno = 's1001'; -- 3.Update the name of the second course to java Basic, class hour is 60, and the class is grade two update subjects set subjectname = 'java Basics',classhour = 60, gradeid = 2 where subjectid = 2; select * from subjects; -- 4.take S1001 ，The grade of course No. 2 will be increased by 5 points select * from mark; update mark set studentscore = studentscore + 5 where studentno = 's1001'and subjectid = 2; -- 5.take S1004 ,The grade of course No. 3 is updated to 60 and the examination time is updated to 2015-10-10 update mark set studentscore = 60,examdate = '2015-10-10' where studentno = 's1004' and subjectid = 3; -- 6.Verified S1004 Absence of course number 2 update mark set studentscore = 0 where studentno = 's1004' and subjectid = 2; -- 7.Delete the computer network course delete from subjects where subjectname = 'computer network'; -- Data query -- 1,Query the information of all first grade students. select * from student where gradeid = 1; -- 2,Check the names and telephone numbers of all second graders. select studentname,phone from student where gradeid = 2; -- 3,Query the information of all first grade female students. select * from student where gradeid = 1 and sex = 'female'; -- 4,Query the subject information with more than 160 class hours. select * from subjects where classhour>60; -- 5,Query the subject name of grade 2 select * from subjects where gradeid = 2; -- 6,Check the names and addresses of male students in grade two. select studentname,address from student where gradeid = 2 and sex = 'male'; -- 7,Query the name and grade information of students without e-mail. select studentname,gradeid from student where email is null or email = ''; -- 8,Query the name and grade information of male students whose birth date is after 1992. select studentname,gradeid from student where sex = 'male' and born>1992; -- 9,Score information of "basic principles of computer" subject examination dated July 3, 2015 select * from mark where examdate = '2015-07-03'; select subjectid,subjectname from subjects where subjectname = 'Basic principles of computer'; select * from mark where subjectid = (select subjectid sd from subjects where subjectname = 'Basic principles of computer') and examdate = '2015-07-03'; -- 10,Query the first grade student information according to the date of birth. select * from student where gradeid = 1 order by born; -- 11,Query the examination information of the subject with No. 1 in the order of high score to low score. select * from mark where subjectid = 1 order by studentscore desc; -- 12,Query July 1, 2015“ MySQL Score information of the top 2 students in the "in-depth" examination. select subjectid,subjectname from subjects where subjectname = 'MySQL thorough'; select studentno,studentscore,subjectid from mark where subjectid = (select subjectid from subjects where subjectname = 'MySQL thorough') and examdate = '2015-07-01' order by studentscore desc limit 0,2; -- 13,Query the subject name and class hour with the most class hours. select subjectname,classhour from subjects order by classhour desc limit 0,1; -- 14,Query the grade and name of the youngest student. select studentname,gradeid from student order by born desc limit 0,1; -- 15,Query the subject in which the lowest score of the exam appears select * from mark order by studentscore limit 0,1; select subjectname from subjects where subjectid = (select subjectid from mark order by studentscore limit 0,1); -- 16,The inquiry student number is“ s1001"All exam information that students have participated in will be displayed in chronological order. select * from mark where studentno = 's1001' order by examdate; -- 17,Query the information of students over the age of 25. select * from student where now()-born > 25; -- 18,Query the students' birthday information in January select * from student where born like '%-01-%'; -- 19,Check the name and grade of the students whose birthday is today. -- 20,New students are enrolled and assigned one Email Address, the rules are as follows: S1+current date [email protected](To be supplemented) -- 21,Query the name, telephone number and address of students whose address is "Yanta District" select studentname,phone,address from student where address like '%Yanta District%'; -- 22,The query name contains the words "computer", subject name, class hour and grade, and is displayed from low to high by grade. select subjectname,classhour,gradeid from subjects where subjectname like '%computer%' order by gradeid; -- 23,The inquiry telephone contains the student's name, address and telephone number starting with "130". select studentname,address,phone from student where phone like '130%'; -- 24,Inquire the student number, name and address of the surname "Zhao". select studentno,studentname,address from student where studentname like 'Zhao%'; -- 25,Count the total number of girls in grade one. select count(1) from student where sex = 'female'; -- 26,Query Li Si's total score select studentno, studentname from student where studentname = 'Li Si'; select studentno, studentscore from mark where studentno = (select studentno from student where studentname = 'Li Si'); -- 27,Student number is s1003 Students' total scores in all classes select sum(studentscore) from mark where studentno = 's1003'; -- 28,Student number is s1003 The average score of students in the exam. select avg(studentscore) from mark where studentno = 's1003'; -- 29,Query first grade subjects“ MySQL The highest score, lowest score and average score of "in-depth". select subjectid, subjectname from subjects where subjectname = 'MySQL thorough'; select max(studentscore),min(studentscore),avg(studentscore) from mark where subjectid = (select subjectid from subjects where subjectname = 'MySQL thorough'); -- 30,Query the total class hours of each grade and arrange them in ascending order. select gradeid, sum(classhour) from subjects group by gradeid order by sum(classhour); -- 31,Query the average score of each student taking the exam( Group by (student number) select studentno, avg(studentscore) from mark group by studentno; -- 32,Query the average score of each course and arrange it in descending order( group by Course) select subjectid, avg(studentscore) from mark group by subjectid order by avg(studentscore) desc; -- 33,Query the total scores of all exams each student takes and arrange them in descending order( group by (student number) select studentno, sum(studentscore) from mark group by studentno order by sum(studentscore) desc; -- 34,Query the average age of the first grade. select -- 35,Query the number of students in Xi'an for each grade. select gradeid, count(1) from student where address like '%Xi'an%' group by gradeid; -- 36,Query the records of students who have passed the average score among the students participating in the examination, and arrange them in descending order according to the average score select studentno, avg(studentscore) from mark group by studentno having avg(studentscore) >= 60 order by avg(studentscore) desc; -- 37,Query the student number, name and failure times of students who failed in at least one examination. select studentno, count(studentscore) from mark a where studentscore < 60 group by studentno having count(studentScore) >= 1; select studentno, studentname from student where stude; select studentno, (select b.studentname from student b where b.studentno = a.studentno)studentName, count(studentscore) from mark a where studentscore < 60 group by studentno having count(studentScore) >= 1; -- 38,Check the student's name, grade name and contact number. select studentname,(select g.gradename from grade g where g.gradeid = s.gradeid)gradename,phone from student s; -- 39,Query the subject name, grade name and class hours with grade No. 1. select (select g.gradename from grade g where g.gradeid = s.gradeid )gradeid,subjectname,classhour from subjects s where s.gradeid = 1; -- 40,Query the name, score and test date of students participating in the test with subject No. 1. select (select s.studentname from student s where s.studentno = m.studentno)studentname,studentscore,examdate from mark m where subjectid = 1; -- 41,The inquiry student number is s1001 The name of the examination subjects, scores and date of the examination. select (select s.subjectname from subjects s where s.subjectid = m.subjectid)subjectname,studentscore,examdate from mark m where studentno = 's1001'; -- 42,Query the reference information of all subjects (some subjects may not have been tested) select studentno,(select s.subjectname from subjects s where s.subjectid = m.subjectid)subjectname,studentscore,examdate from mark m left join subjects s on m.subjectid = s.subjectid; select * from subjects; -- 43,Query the information of subjects that have not been tested. select s.subjectname 'Account name' from mark m right join subjects s on m.subjectid = s.subjectid where m.studentscore is null and m.studentno is null;