04-27 Mysql Exam 55 minutes simple answer record

Second question table

#新建学生表
drop table if exists setudent;
CREATE TABLE Setudent (
Sno Int (TEN) NOT null primary key comment ' study number ',
sname varchar (a) NOT null comment ' name ',
ssex varchar (TEN) NOT null comment ' sex '
);
#给学生表添加数据
INSERT INTO setudent values (1, ' Jiang ', ' Male '), (2, ' Chao Shuwen ', ' Male '), (3, ' Ruishan ', ' Female '), (4, ' Yu Hao ', ' Male ');

#新建课程表
drop table if exists coures;
CREATE TABLE Coures (
Cnoc Int (TEN) NOT null primary key comment ' course ID ',
name varchar (+) NOT null comment ' course name '
);
#给课程表添加数据
INSERT INTO coures values (1, ' Java '), (2, ' Oracle '), (3, ' JS '), (4, ' jquery ');
#新建选课表
drop table if exists selclass;
CREATE TABLE Selclass (
Selno Int (TEN) NOT null primary key auto_increment comment ' elective id ',
Sno Int (TEN) not NULL comment ' student ID ',
CNO Int (ten) NOT NULL comment ' course ID ',
count Int (TEN) not null comment ' score '
);
#给选课表添加数据
INSERT into Selclass values (1,1,1,88), (2,1,2,77), (3,2,1,78), (4,2,2,91), (5,3,1,55), (6,3,2,65), (7,3,3,75), ( 10,4,3,74), (9,4,4,64);

First question table

drop table if exists student;
CREATE TABLE Student (
Sno int NOT null primary key comment ' student ID ',
sname varchar (a) NOT null comment ' name ',
ssex varchar (a) NOT null comment ' sex ',
splace varchar (a) NOT null comment ' birthplace ',
Syxid varchar (a) NOT NULL comment ' faculty ID '
);
drop table if exists yxinfo;
CREATE TABLE Yxinfo (
YXID int NOT null primary key comment ' faculty ID ',
yxname varchar (a) NOT null comment ' faculty name ',
yxplace varchar (a) NOT null comment ' address ',
yxphone varchar (p) NOT null comment ' contact phone '
);
INSERT into student values
(' 1 ', ' Wen Huiqing ', ' female ', ' Jiangsu ', ' 1 '),
(' 2 ', ' Zhao and Tang ', ' Male ', ' Chongqing ', ' 2 '),
(' 3 ', ' Zhaoxiuping ', ' Male ', ' Hebei ', ' 1 '),
(' 4 ', ' Qin Yi ', ' Male ', ' Fujian ', ' 3 '),
(' 5 ', ' He ling Quan ', ' Woman ', ' Fujian ', ' 3 '),
(' 6 ', ' Zhou Hailong ', ' Male ', ' Shandong ', ' 1 ');
INSERT INTO yxinfo values
(' 1 ', ' computer Department ', ' Research Building 608 ', ' 0533-2168068 '),
(' 2 ', ' Mathematics department ', ' Executive Building 203 ', ' 0533-2157068 '),
(' 3 ', ' Physics department ', ' Scientific research Building 607 ', ' 0533-3153606 ');
First question
1. Find out all student information of ' computer Department '
SELECT * FROM student where Syxid = (select Yxid from yxinfo where yxname = ' computer system ');
2. Find out the department information of ' Zhao and Tong '
SELECT * from yxinfo where Yxid = (select Syxid from student where sname = ' Zhao and Tang ');
3. Find out the names of the faculties in the ' Administrative Building ' office;
Select Yxname from Yxinfo where yxplace like ' Executive Building% ';
4. How many boys and girls are found
Select Ssex,count (*) from student group by Ssex;
5. The largest number of faculties found
SELECT * from yxinfo where Yxid = (select Syxid from student group by Syxid ORDER by COUNT (*) DESC limit 1);
6. How many men and women were found in the largest number of faculties in the Department
Select Ssex,count (*) from student where Syxid = (select Syxid from student group by Syxid ORDER by COUNT (*) DESC LIMIT 1) Group BY Ssex;
7. Identify all people with the same native origin as ' Qin Yi '
Select sname from student where Splace = (select Splace from student where sname = ' Qin Yi ');
8. Find out the department information of ' Hebei ' people studying
SELECT * from yxinfo where Yxid = (select Syxid from student where splace = ' Hebei ');
9. Find out all the student information of the same department as ' Fujian schoolgirl ';
SELECT * FROM student where Syxid = (select Syxid from student where splace = ' Fujian ' and ssex = ' female ');
second question
1. Query the name of the student who has been enrolled in ' Oracle '
Select Sname from Setudent where Sno in (select Sno from Selclass where CNO in (select Cnoc from coures where name = ' Oracl E '));
2. Query the name of the course Jiang students have elective
Select name from Coures where Cnoc in (select CNO from Selclass where Sno in (select Sno from setudent where sname = ' Jiang ' ));
3. The enquiry only took the student number and name of one course;
Select Sno,sname from Setudent where Setudent.sno in (select Selclass.sno from Selclass GROUP by Selclass.sno have count (*) = 1);
4. Enquiries about students who have enrolled at least 3 courses
SELECT * from Setudent where Setudent.sno in (select Selclass.sno from Selclass GROUP by SELCLASS.SNO have count (*) > = 3);
5. Enquiry for students who have enrolled in all courses
SELECT * from Setudent where Setudent.sno in (select Selclass.sno from Selclass GROUP by SELCLASS.SNO have count (*) = (SE Lect Count (*) from coures));
6. Number of students inquiring for elective courses
Select Cno,count (*) from Selclass GROUP by SELCLASS.CNO;
7. Search for a course at least one of the same student information as the Jiang course
Select Setudent.sno,sname,ssex from setudent join selclass on setudent.sno = Selclass.sno where selclass.cno in (select SE Lclass.cno from Selclass join setudent on setudent.sno = selclass.sno where sname = ' Jiang ');
8. Check the average score of two or more failed students
Select Sname,avg (count) from Selclass a joins setudent on setudent.sno = A.sno where A.sno = (select B.sno from Selclass b Where Count < GROUP by B.SNO have Count (*) >=2) group by A.sno;

04-27 Mysql Exam 55 minutes simple answer record