mysql database exercise two

Keywords: Database MySQL

Student table:

Chart of Accounts:
Achievement Sheet:
Data modification and deletion:
1. Renew the address of Xi'an City to Xi'an Science and Technology Second Road
2. Modify the email of S1001 to an empty string
3. Update the name of the second course to java basics, with 60 hours and a second grade class
4. Increase the S1001 and course number by 5 points
5. Update S1004, grade 3 to 60, exam time to 2015-10-10
6. Examination of S1004 Course Number 2
7. Delete computer network courses
Data Query:
1. Query the information of all the first grade students.
2. Query the names and phone numbers of all the second grade students.
3. Query the information of all female students in grade one.
4. Query subject information with class hours exceeding 60.
5. Query the names of subjects in second grade
6. Query the names and addresses of male students in second grade.
7. Query the names and grade information of students without e-mail.
8. Query the names and grades of male students born after 1992.
9. Information on the results of the Computer Basic Principles examination, which was held on July 3, 2015
10. Query the information of the first grade students according to their birth date.
11. Query the exam information of subject number 1 in the order of high to low grades.
12. Query the results of the first two students who took the "MySQL in-depth" test on July 1, 2015.
13. Query the names and hours of the subjects with the most hours.
14. Query the age and name of the youngest student.
15. Query which subjects have the lowest score on the exam
16. Query all the exam information that the student with the student number "s1001" has taken and display it in the order of time.
17. Query information about students over 25 years of age.
18. Query the information of students who have birthdays in January
19. Query the names of students who have birthdays today and all grades.
20. Enroll new students and assign them an Email address with the following rules: S1+Current Date+@bd.com
21. Query the names, phone numbers and addresses of students whose address is Yanta District
22. The name of the query contains the name of the computer-typed subject, the hours of study and the grade to which it belongs, and is displayed from lowest grade to highest grade.
23. The inquiry phone contains the name, address and telephone of the student starting with "130".
24. Query the academic number, name and address of the last name "Zhao".
25. Statistics the total number of girls in grade one.
26. Query Li's Fourth Total Results
27. Total results of all courses for students with student number s1003
28. Average score of students with student number s1003.
29. Query the highest, lowest and average score of the first grade subject "Mysql".
30. Query the total number of hours per grade and arrange them in ascending order.
31. Query the average score of each student taking the exam. (Group by number)
32. Query the average score of each course and arrange it in descending order. (group by course)
33. Query the total score of all the exams each student has taken and rank them in descending order. (group by number)
34. Query the average age of the first grade.
35. Query the number of students in Xi'an area for each grade.
36. Query the records of the students who passed the examinations on average and rank them in descending order according to their average scores
37. Query the number, name and number of failures of students who have failed at least one exam.
38. Query the name of the student, the name of the grade to which he belongs and the contact number.
39. Query the subject name, grade name and hours of grade 1.
40. Query the names, scores and dates of the students taking the examination with subject number 1.
41. Query the name, score and date of the exam for the student with the number s1001.
42. Query the reference information for all subjects (some may not have been tested yet)
43. Query information about subjects that have not been examined.

Insert a code snippet here update student set address = 'Xi'an Second Science and Technology Road' where address = 'Xi'an City';
select * from student;
update student set email = '' where studentno = 's1001';
update subjects set subjectname = 'java Basics',classhour = 60, gradeid = 2 where subjectid = 2;
select * from subjects;
select * from mark;
update mark set studentscore = studentscore + 5 where studentno = 's1001'and subjectid = 2;
update mark set studentscore = 60,examdate = '2015-10-10' where studentno = 's1004' and subjectid = 3;
update mark set studentscore = 0 where studentno = 's1004' and subjectid = 2;
delete from subjects where subjectname = 'computer network';





select * from student where gradeid = 1; 
select studentname,phone from student where gradeid = 2;
select * from student where gradeid = 1 and sex = 'female';
select * from subjects where classhour>60; 
select * from subjects where gradeid = 2;
select studentname,address from student where gradeid = 2 and sex = 'male'; 
select studentname,gradeid from student where email is null or email = '';
select studentname,gradeid from student where sex = 'male' and born>1992;
select * from mark where examdate = '2015-07-03';
select subjectid,subjectname from subjects where subjectname = 'Fundamentals of Computers';
select * from mark where subjectid = (select subjectid sd from subjects where subjectname = 'Fundamentals of Computers') and examdate = '2015-07-03';
select * from student where gradeid = 1 order by born;
select * from mark where subjectid = 1 order by studentscore desc;
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;
select subjectname,classhour from subjects order by classhour desc limit 0,1;
select studentname,gradeid from student order by born desc limit 0,1;
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);
select * from mark where studentno = 's1001' order by examdate;
select * from student where now()-born > 25;
select * from student where born like '%-01-%';
select studentname,phone,address from student where address like '%Yanta District%';
select subjectname,classhour,gradeid from subjects where subjectname like '%Computer%' order by gradeid;
select studentname,address,phone from student where phone like '130%';
select studentno,studentname,address from student where studentname like 'Zhao%';
select count(1) from student where sex = 'female';
select studentno, studentname from student where studentname = 'Li Si';
select studentno, studentscore from mark where studentno = (select studentno from student where studentname = 'Li Si');
select sum(studentscore) from mark where studentno = 's1003';
select avg(studentscore) from mark where studentno = 's1003';
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');
select gradeid, sum(classhour) from subjects group by gradeid order by sum(classhour);
select studentno, avg(studentscore) from mark group by studentno;
select subjectid, avg(studentscore) from mark group by subjectid order by avg(studentscore) desc;
select studentno, sum(studentscore) from mark group by studentno order by sum(studentscore) desc;
select gradeid, count(1) from student where address like '%Xi'an%' group by gradeid;
select studentno, avg(studentscore) from mark group by studentno having avg(studentscore) >= 60 order by avg(studentscore) desc;
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,Query 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;
select (select g.gradename from grade g where g.gradeid = s.gradeid )gradeid,subjectname,classhour from subjects s where s.gradeid = 1;
select (select s.studentname from student s where s.studentno = m.studentno)studentname,studentscore,examdate from mark m where subjectid = 1;
select (select s.subjectname from subjects s where s.subjectid = m.subjectid)subjectname,studentscore,examdate from mark m where studentno = 's1001';
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;
select s.subjectname 'Account Name' from mark m right join subjects s on m.subjectid = s.subjectid where m.studentscore 

Posted by kiss-o-matic on Fri, 12 Nov 2021 11:59:02 -0800