Mysql50 basic exercises

data

--  Student 
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , ' Zhao Lei ' , '1990-01-01' , ' male ');
insert into Student values('02' , ' Qian Dian ' , '1990-12-21' , ' male ');
insert into Student values('03' , ' Sun Feng ' , '1990-05-20' , ' male ');
insert into Student values('04' , ' Li Yun ' , '1990-08-06' , ' male ');
insert into Student values('05' , ' Zhou Mei ' , '1991-12-01' , ' Woman ');
insert into Student values('06' , ' Wu Lan ' , '1992-03-01' , ' Woman ');
insert into Student values('07' , ' Zheng Zhu ' , '1989-07-01' , ' Woman ');
insert into Student values('08' , ' Wangju ' , '1990-01-20' , ' Woman ');

--  Course 
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , ' Chinese language and literature ' , '02');
insert into Course values('02' , ' mathematics ' , '01');
insert into Course values('03' , ' English ' , '03');

--  teacher 
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , ' Zhang San ');
insert into Teacher values('02' , ' Li Si ');
insert into Teacher values('03' , ' Wang Wu ');

--  fraction 
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

Student

teacher

Course

fraction

01

Inquire about "01" Course than "02" Information and course marks for students with high course grades

First query 01 Course

The query again 02

intersection

SELECT 
	* 
FROM 
  (SELECT * FROM SC WHERE cid='01') a
	JOIN
	(SELECT * FROM SC WHERE cid='02') b on a.sid = b.sid

Add restrictions ,01 fraction > 02 fraction

Association query course information

SELECT 
	* 
FROM 
  (SELECT * FROM SC WHERE cid='01') a
	JOIN
	(SELECT * FROM SC WHERE cid='02') b on a.sid = b.sid and a.score > b.score
	JOIN
	Course c on a.cid = c.cid
	JOIN
	Course d on b.cid = d.cid

Association query student information

SELECT 
	* 
FROM 
  (SELECT * FROM SC WHERE cid='01') a
	JOIN
	(SELECT * FROM SC WHERE cid='02') b on a.sid = b.sid and a.score > b.score
	JOIN
	Course c on a.cid = c.cid
	JOIN
	Course d on b.cid = d.cid
	JOIN
	Student e on a.sid = e.sid

Summary

-- 01
SELECT
	e.sid,
	e.sname,
	e.sage,
	e.ssex,
	a.cid AS cid01,
	c.cname AS cname01,
	a.score AS cscore01,
	b.cid AS cid02,
	d.cname AS cname02,
	b.score AS cscore02 
FROM
	( SELECT * FROM SC WHERE cid = '01' ) a
	JOIN ( SELECT * FROM SC WHERE cid = '02' ) b ON a.sid = b.sid AND a.score > b.score
	JOIN Course c ON a.cid = c.cid
	JOIN Course d ON b.cid = d.cid
	JOIN Student e ON a.sid = e.sid

02

Query the existence of students' course selection " 01 “ The course may not exist ” 02 " Course situation （ If it does not exist, it will be displayed as null）

SELECT
	* 
FROM
	( SELECT * FROM SC WHERE cid = '01' ) a
	LEFT JOIN ( SELECT * FROM SC WHERE cid = '02' ) b ON a.sid = b.sid

03

Query average score is greater than or equal to 60 Student number, student name and average score of each student

Query average score first

SELECT 
	*,
	AVG(score) avg
FROM sc
GROUP BY sc.sid
HAVING avg >= 60

More table Association queries

SELECT
	a.sid, b.sname, a.avg 
FROM
	( SELECT *, AVG( score ) avg FROM sc GROUP BY sc.sid HAVING avg >= 60 ) a
	JOIN Student b ON a.sid = b.sid