5. There are 37 mysql exercises. Once you finish these mysql exercises, you will be advanced immediately. (with answers)

Keywords: Database MySQL SQL

1, The table structure in the existing database casemanage is shown in the following figure

TABLENAME:afinfo

Idnameagebirthsexmemo
1Xu Hongguo371979-03-23malehigh school
2Wang Fangfang261988-02-06femaleundergraduate
3Xu Xiaosheng241990-04-02malemaster
4Chen Xiao301984-09-12femaledoctor
5Zheng Kai271987-12-30malejunior college

1) Please write sql statements to sort the ages in ascending order

2) Please write an sql statement to query the list of personnel starting with "Xu"

3) Please write an sql statement to change the age of "Chen Xiao" to "45"

4) Please write sql to delete the data record of Wang Fangfang.

2, The following tables are available:

Student information form (student)

1) Query all student information, how to write SQL?

2) Xiao Ming, a new student with student number 005, needs to write information into the student information table. How to write SQL statements?

3) Li Si's Chinese score was registered incorrectly, and the actual score was 85 points. It was updated to the examination information table. How to write SQL statements?

4) Query the average score of each subject, and the display fields are: subject and average score. How to write SQL?

5) Query the scores of all students in each subject. The display fields are: name, student number, subject and score, and sort them by student number and subject. Students without scores also need to be listed. How to write SQL?

6) Query the highest score in a single subject, and the display fields are: name, student number, subject and score. How to write SQL?

3, Write SQL statements as required.

Student (s_no, sname, sage, sex)
Teacher (t_no,tname) teacher table
Course (c_no,cname,t_no) curriculum (t_no is associated with t_no of teacher table)
Sc(s_no,c_no,score) grade sheet (c_no is related to c_no of the curriculum)

The data is added according to the following topics

1. Query the student numbers of all students whose grades in "001" course are higher than those in "002" course.

2. Query the student number and average score of students whose average score is greater than 60.

3. Query the student number, name, number of courses selected and total score of all students.

4. Query the number of teachers surnamed Li.

5. Query the student number and name of the students who have not studied "Ye Ping" teacher's class

6. Query the student number and name of students who have studied "001" and also studied the course numbered "002".

7. Query the student number and name of all students whose course score is less than 60.

8. Query the student number and name of students who have not learned all classes.

10. Query the student numbers and names of other students who have studied at least one course with student number "001".

11. Change the grades of the courses taught by "Ye Ping" in the "sc" table to the average grade of this course.

12. Query the student numbers and names of other students who are exactly the same as the courses studied by "1002".

13. Delete the sc table record of learning "Ye Ping" teacher's class.

14. Insert some records into the sc table, which are required to meet the following conditions: no students have attended the course numbered "003"

15. Query the highest and lowest scores of each subject: displayed in the following form: course ID, highest score and lowest score.

16. Query the average score of different courses taught by different teachers from high to low.

17. Count the scores of each subject and the number of people in each score segment: course ID, course name, [100-85], [85-70], [70-60], [< 60]

18. Query the number of students selected for each course

19. Find out the student numbers and names of all students who have taken only one course

20. Query the number of boys and girls

21. Query the list of students surnamed "Zhang"

22. Query the list of same-sex students with the same name and count the number of students with the same name.

23. Query the list of students born in 1994 (Note: the type of sage column in the student table is datatime)

24. Query the average score of each course. The results are arranged in ascending order according to the average score. If the average score is the same, they are arranged in descending order according to the course number.

25. Query the student number, name and average score of all students whose average score is greater than 85

26. Query the names and scores of students whose course name is "database" and whose score is lower than 60

27. Query the course selection of all students

28. Query the failed courses and sort them according to the course number from large to small.

29. Query the student number and name of students with course number 003 and course score above 80.

30. Number of students taking elective courses.

31. Query the names and grades of the students with the highest scores among the students who have taken the courses taught by "teacher Feng".

32. Query each course and the corresponding number of electives.

33. Query the top two of the best in each course.

34. Query the number of students in each course (only courses with more than 10 students can be counted). It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number.

35. Search the student numbers of students taking at least two courses.

36. Query the course number and course name of the courses that all students take.

37. Query the student number and average score of students who have failed more than two courses.

Write your own query sentence answer (may not be the best)

I

create database casemanage
default charset utf8;

drop table if exists afinfo;
create table afinfo(
id int(10) primary key auto_increment,
`name` varchar(20) not null,
age int(10) not null,
birth date not null,
sex char(2) not null,
memo varchar(20)
)

insert afinfo values(1,'Xu Hongguo',37,'1979-03-23','male','high school'),
(2,'Wang Fangfang',26,'1988-02-06','female','undergraduate'),
(3,'Xu Xiaosheng',24,'1990-04-02','male','master'),
(4,'Chen Xiao',30,'1984-09-12','female','doctor'),
(5,'Zheng Kai',27,'1987-12-30','male','junior college')
-- sort order by
//1
select * from afinfo order by age asc;
//2
select * from afinfo where name like 'Xu%';
//3
update afinfo set age = 45 where name = 'Chen Xiao'; 
//4
delete from afinfo where name = 'Wang Fangfang';

II

create table student (
name varchar(20) not NULL,
code int(10) not null
)

create table exam(
code int(10) not null comment 'Student number',
subject varchar(20),
score int(10)
)

insert student values('Zhang San',001),('Li Si',002),('Wang Wu',003),('A six',004)
insert exam values(001,'mathematics',80),
(002,'mathematics',75),
(001,'language',90),
(002,'language',80),
(001,'English',90),
(002,'English',85),
(003,'English',80),
(004,'English',70)

//1
select s.name full name,s.code Student number,e.subject subject,e.score achievement from student s left join exam e on s.code = e.code;
select * from exam;
//2
insert student value('Xiao Ming',005)
//3
update exam set score = 85 where code = (select code from student where name = 'Li Si') and subject = 'language';
//4
select avg(score) average,subject subject from exam group by subject ;
//5
select * from student  
left join exam  on s.code = e.code order by e.code asc,subject asc;
//6
-- When two tables are aggregated and there are no columns that can be figured out by aggregation, you can query the existence of columns (two queries)
select s.code,s.name from student s join exam e on s.code = e.code where (e.`subject`,e.score) in 
(
select subject,max(score) from exam group by subject
)

III
(some table names have been changed)

create table Student1(
s_no int(10) primary key auto_increment,
sname varchar(25),
anage varchar(20),
sex varchar(20)
)

create table course(
c_no int(10) comment 'Course number',
cname VARCHAR(10),
t_no int(10) comment 'Teacher number'
)
drop table if exists sc
create table sc(
s_no int(10) comment 'Student number',
c_no int(10) comment 'Course number',
score int(10)
)
drop table if exists sc1
create table sc1(
s_no int(10),
c_no int(10),
score int(10)
)

INSERT INTO `sc1` VALUES (1001, 002, 85);
INSERT INTO `sc1` VALUES (1001, 003, 87);
INSERT INTO `sc1` VALUES (1001, 004, 83);
INSERT INTO `sc1` VALUES (1002, 001, 50);
INSERT INTO `sc1` VALUES (1002, 002, 45);
INSERT INTO `sc1` VALUES (1002, 004, 55);
INSERT INTO `sc1` VALUES (1003, 001, 91);
INSERT INTO `sc1` VALUES (1003, 002, 89);
INSERT INTO `sc1` VALUES (1003, 003, 92);
INSERT INTO `sc1` VALUES (1003, 004, 96);
INSERT INTO `sc1` VALUES (1004, 001, 55);
INSERT INTO `sc1` VALUES (1004, 002, 44);
INSERT INTO `sc1` VALUES (1004, 004, 36);
INSERT INTO `sc1` VALUES (1005, 001, 25);
INSERT INTO `sc1` VALUES (1005, 002, 21);
INSERT INTO `sc1` VALUES (1005, 003, 58);
INSERT INTO `sc1` VALUES (1005, 004, 21);



create table teacher(
t_no int(10),
tname VARCHAR(10)
)
-- 1
insert course values(001,'language',1),(002,'mathematics',2),(003,'English',3)
insert sc values(2001,002,100),(2001,001,70),(2002,001,88),(2002,002,78)
1 -- Query the student numbers of all students whose grades in "001" course are higher than those in "002" course. Find out the maximum value, and then sort by course number to add conditions
-- select max(score) Highest score,s_no Student number,c_no Course number from sc GROUP BY c_no HAVING c_no = 1
-- select s_no from sc1 where score = (select max(score) Highest score from sc1 GROUP BY c_no HAVING c_no = 1)
-- select max(score) Highest score,c_no Course number from sc1 GROUP BY c_no HAVING c_no = 1

-- 0 Query the student numbers of all students whose grades in "001" course are higher than those in "002" course. Find the maximum value

select sc1.s_no from sc1 sc1 JOIN sc1 sc2 ON sc1.s_no=sc2.s_no
WHERE sc1.c_no='001' AND sc2.c_no='002' AND sc1.score>sc2.score


select sc2.s_no from sc1 sc2 join sc1 sc3 on sc2.s_no = sc3.s_no
where sc2.c_no = '001' and sc3.c_no = '002' and sc2.score > sc3.score 

select * from sc1

2 -- Query the student number and average score with an average score greater than 60
select s_no,avg(score) from sc1 group by s_no having avg(score) > 60;

3 -- Query the student number, name, number of courses selected and total score of all students.
insert Student1 values(1002,'Li Si','20','male');
insert Student1 values(1003,'Wang Si','20','female');
insert Student1 values(1004,'Zhao Liu','18','female');
insert Student1 values(1005,'Yang Qi','17','male');

select t.s_no Student number,sname full name,count(c_no) Number of courses,sum(score) Total score 
from Student1 t left join sc1 s on t.s_no = s.s_no group by
t.s_no

-- 4  Query the number of teachers surnamed Li.
insert teacher values(4,'Ye Ping'),(2,'Li Chunlei'),(3,'Tie Zhu Wang'),(4,'Ye Ping')


select count(tname) number FROM teacher where tname like 'Lee%'


-- 5  Query the student number and name of the students who have not studied "Ye Ping" teacher's class
select * from teacher
select * from student1
select * from course
select * from sc1
insert course values(1,'java','4')
update course set c_no = 4 where t_no = 4
         -- Find out the students of Ye Ping's teacher
select t_no from teacher where tname = 'Ye Ping'
				-- Check the course number c_no
select c_no from course c 
join teacher t
on c.t_no = t.t_no 
where t.tname = 'Ye Ping'
       -- By checking it out c_no check s_no
select s_no from sc1 sc
join (
select c_no from course c 
join teacher t
on c.t_no = t.t_no 
where t.tname = 'Ye Ping'
) c
on  sc.c_no = c.c_no


select s_no from sc1 where s_no not in  
		(
		select s_no from  sc1
		where c_no in (
				(select DISTINCT c_no from course c 
				join teacher t
				on c.t_no = t.t_no 
				where t.tname = 'Ye Ping')
		)
)


      

-- 6  Query the student number and name of students who have studied "001" and also studied the course numbered "002".
select s_no from sc1 where c_no = 1 or c_no = 2

select sc.s_no from (select s_no from sc1
where c_no = 1) n 
join sc1 sc 
on sc.s_no = n.s_no
join student1 st1
on sc.s_no = st1.s_no
where sc.c_no = 2

-- 7 Query the student number and name of students whose course score is less than 60. All students 
select st1.s_no,sname,m.c_no from student1 st1
join 
(select s_no,max(score),c_no from sc1 group by c_no having max(score) < 60) m
on st1.s_no = m.s_no


-- When there is an aggregate function in the query, this table cannot be used as a sub query, which is illogical
-- 	select c.s_no Student number,max(score) credit from
--  (select s_no,max(score) from sc1 group by sc1.c_no )  c
-- 	where c.max(score) > 60;



-- 8 Query the student number and name of students who have not learned all classes.
select * from sc1 where s_no != 1001

--    select s_no,sname from student1 where s_no not in (
-- 		select sc01.s_no from sc1 sc01 join sc1 sc02 on sc01.s_no = sc02.s_no join sc1 sc03 
-- 		on sc01.s_no = sc03.s_no join sc1 sc04 on sc01.s_no = sc04.s_no
-- 		where sc01.c_no = 1 and sc02.c_no = 2 and sc03.c_no = 3 and sc04.c_no = 4
-- 		)


select count(*) from course

select count(*) from student1 sd join sc1 sc on sd.s_no = sc.s_no
join course e on e.c_no = sc.c_no
group by sd.s_no 

select d.s_no from student1 d join sc1 sc on d.s_no = sc.s_no
where (
select sd.s_no,count(*) from student1 sd join sc1 sc on sd.s_no = sc.s_no
join course e on e.c_no = sc.c_no
group by sd.s_no
) < (select count(*) from course)


-- 10 Query the student numbers and names of other students who have learned at least one course with student number "1001".
select c_no from sc1 where s_no = 1001

select s_no from sc1 where c_no in (select c_no from sc1 where s_no = 1001)

-- 11 Handle“ sc"The grades of the courses taught by "Ye Ping" in the table are changed to the average grade of this course.
-- Course number
select c_no from course c join teacher t on c.t_no = t.t_no where tname = 'Ye Ping'
-- Average score
select avg(score) from sc1 where c_no in
(select c_no from course c join teacher t on c.t_no = t.t_no where tname = 'Ye Ping')
group by c_no

select * from course

update sc1 set score = (
select bb.co from 
(
	select avg(score) co from sc1 sc where c_no in
	(select c_no  from course c join teacher t on c.t_no = t.t_no where tname = 'Ye Ping')
	group by c_no
) bb
)
where c_no in (
select c_no from course c join teacher t on c.t_no = t.t_no where tname = 'Ye Ping'
)


-- 12 Query the student numbers and names of other students who are exactly the same as the courses studied by "1002".
select s.c_no from sc1 s join course s1 
on s.c_no = s1.c_no
join student1 d
on s.s_no = d.s_no
where d.s_no = 1002

select s_no from sc1 where c_no not in (
select s.c_no from sc1 s join course s1 
on s.c_no = s1.c_no
join student1 d
on s.s_no = d.s_no
where d.s_no = 1002
)



-- 13 Delete the of learning "Ye Ping" teacher's class sc Table records.

select * from sc1

delete from sc1 where c_no = 
(select c_no from course c 
join teacher t 
on c.t_no = t.t_no 
where t.tname = 'Ye Ping')


-- 14 towards sc Insert some records into the table. These records are required to meet the following conditions: have not attended the course numbered "003"

？？？

-- 15 Query the highest and lowest scores of each subject: displayed in the following form: Course ID，Highest score, lowest score.
select max(score) as Highest score,min(score) Lowest score,c.c_no curriculum ID from sc1 s 
join course c
on c.c_no = s.c_no 
group by cname;


-- 16 Query the average score of different courses taught by different teachers from high to low
 
select c_no from course

select c.cname,avg(score) as a from sc1 s 
join course c on s.c_no = c.c_no 
group by cname order by a desc;


-- 17 Count the scores of each subject and the number of people in each score segment: Course ID，Course name, [100]-85]，[85-70]，[70-60]，
select * from sc1
-- Total number of selected courses
select count(*),c.c_no,c.cname from sc1 s 
join course c on s.c_no = c.c_no 
group by c.cname

select * from sc1 
-- Calculate the number of people in the interval
select count(*) from course c
join sc1 s on s.c_no = c.c_no 
where s.score < 100  and s.score > 85 group by cname

select * from sc1


select count(case when s.score < 100  and s.score > 85 then '1' end)
`[100-85]`,
 count(case when s.score < 85  and s.score > 70 then '1' end)
`[85-70]`,
 count(case when s.score < 70  and s.score > 60 then '1' end)
`[70-60]`,
 count(case when s.score < 60 then '1' else null end)
`[0-60]`, 
c.c_no,c.cname from sc1 s 
join course c on s.c_no = c.c_no 
group by c.cname


-- 18,Query the number of students selected for each course


select count(s.s_no) Number of elective courses,cname curriculum from sc1 s 
join course c
on s.c_no = c.c_no group by c.c_no



-- 19 Find out the student numbers and names of all students who have taken only one course


select sc.s_no,count(sc.c_no) c from student1 t 
join sc1 sc on sc.s_no = t.s_no group by sc.s_no
having c = 1


-- 20 Query the number of boys and girls
select count(
case 
when sex = 'male' then 1
end) male,
count(case
when sex = 'female' then 1
end) female
from student1



-- 21,Query the list of students surnamed "Zhang"

select * from student1 where sname like 'Zhang%'

-- 22,Query the list of same-sex students with the same name and count the number of students with the same name.


select sname,s_no,count(*) from student1 where sname like '___' and sex like '_'



-- 23,Check the list of students born in 1994 (Note: student In the table sage The type of column is datatime)

select * from student1
update student1 set anage = '1994-01-10 10:01:10'
alter table student1 change anage anage datetime default '1994-01-10 10:01:10'


select * from student1 where anage > '1994-01-01 00:00:00' and anage < '1995-01-01 00:00:00'

-- 24,Query the average score of each course. The results are arranged in ascending order according to the average score. If the average score is the same, it is arranged in descending order according to the course number.

select c_no,avg(score) co from sc1 group by c_no order by co asc,c_no desc





-- 25,Query the student number, name and average score of all students whose average score is greater than 85

select t.s_no,sname co from sc1 s,student1 t where s.s_no = t.s_no group by s_no having avg(score) > 85


-- 26,Query the names and scores of students whose course name is "database" and whose score is less than 60
select s.s_no,sname from sc1 s,student1 t,course c 
where s.s_no = t.s_no and s.c_no = c.c_no and s.score < 60 and cname = 'database'

-- 27,Query the course selection of all students
select * from student1 t,sc1 s,course c 
where s.s_no = t.s_no and s.c_no = c.c_no

-- 28,Query the failed courses and sort them according to the course number from large to small.

select DISTINCT(t.c_no),cname from course t join sc1 s on s.c_no = t.c_no  
where score < 60 order by t.c_no desc

-- 29,Query the student number and name of students with course number 003 and course score above 80.
select s.s_no,sname from sc1 s,student1 t,course c where s.s_no = t.s_no and s.c_no = c.c_no and s.score > 80 and c.c_no = 3


-- 30,Find the number of students who have taken courses.
-- Number of elective courses
select s.s_no,count(c.c_no) z from sc1 s,course c where s.c_no = c.c_no 
group by s.s_no having z > 0

select count(*) from (
select s.s_no s,count(c.c_no) z from sc1 s,course c where s.c_no = c.c_no 
group by s.s_no having z > 0
)b


-- 31,Query the names and grades of the students with the highest scores among the students who have taken the courses taught by "teacher Feng".

select max(score),sname from student1 t,sc1 s,
course c,teacher e
 where t.s_no = s.s_no and s.c_no = c.c_no and c.t_no = e.t_no and tname = 'Miss Feng'



-- 32,Query each course and the corresponding number of electives.
select count(*),cname from course join sc1 on sc1.c_no =course.c_no  group by cname



-- 33,Query the top two of the best in each course.


-- 34,Query the number of elective students for each course (only the courses with more than 10 students are counted). It is required to output the course number and the number of elective students. The query results are arranged in descending order by the number of students, and the query results are arranged in descending order by the number of students. If the number of students is the same, they are arranged in ascending order by the course number.

select count(*) c,cname from course join sc1 on sc1.c_no =course.c_no  
group by cname having c > 10 order by c desc
-- 35,Retrieve the student number of at least two courses.
select sc.s_no,count(sc.c_no) c from student1 t 
join sc1 sc on sc.s_no = t.s_no group by sc.s_no
having c > 1

-- 36,Query the course number and course name of the course that all students take.
select count(*) from course 

select DISTINCT(count(sc1.c_no)) s,sc1.c_no,cname from course 
join sc1 
on sc1.c_no = course.c_no group by sc1.s_no 
having s = (select count(*) from course )

-- 37,Query the student number and average score of students who have failed more than two courses.


select s.s_no,count(s.score) Number of failed subjects,avg(score) Average score from sc1 s,student1 t,course c 
where s.s_no = t.s_no and s.c_no = c.c_no 
and s.score < 60 group by s.s_no having count(s.score) >2

Posted by disconne on Fri, 03 Dec 2021 03:42:56 -0800

Hot Keywords

©2024 Programmer Group