This database exercise is a part of hands-on operation after I summarized the database knowledge points released last time. The focus is to deepen the understanding of the database. In the face of the preparation of sentences, colleagues can criticize and correct more.
1. Create database tables.
CREATE DATABASE school;(Create database school) USE school; CREATE TABLE student( `sno` VARCHAR(20) NOT NULL, `sname` VARCHAR(20) NOT NULL, `ssex` VARCHAR(20) NOT NULL DEFAULT 'male', `sbirthday` DATETIME, `sclass` VARCHAR(20)); CREATE TABLE teacher( tno VARCHAR(20) NOT NULL, tname VARCHAR(20) NOT NULL, tsex VARCHAR(20) NOT NULL DEFAULT 'male', tbirthday DATETIME, prof VARCHAR(20) NOT NULL, depart VARCHAR(20) ); CREATE TABLE course( cno VARCHAR(20) NOT NULL, cname VARCHAR(20) NOT NULL, tno VARCHAR(20) NOT NULL); USE school; CREATE TABLE score( sno VARCHAR(20) NOT NULL, cno VARCHAR(20) NOT NULL, degree DECIMAL(4,1) NOT NULL); ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(sno); ALTER TABLE student ADD CONSTRAINT FOREIGN KEY(sno) REFERENCES student(sno); ALTER TABLE student ADD CONSTRAINT FOREIGN KEY(cno) REFERENCES course(cno); ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(sno,cno); ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(sno); ALTER TABLE teacher ADD CONSTRAINT PRIMARY KEY(tno); ALTER TABLE course ADD CONSTRAINT PRIMARY KEY(cno); ALTER TABLE course ADD CONSTRAINT FOREIGN KEY(tno) REFERENCES teacher(tno); ALTER TABLE score ADD CONSTRAINT PRIMARY KEY(sno,cno); ALTER TABLE score ADD CONSTRAINT FOREIGN KEY(cno) REFERENCES course(cno);
2. Insert data
INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(108,'Zeng Hua','male','1997-09-01',95033); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(105,'Kuang Ming','male','1975-10-02',95031); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(107,'Wang Li','female','1976-01-23',95033); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(101,'Li Jun','male','1976-02-20',95033); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(109,'Wang Fang','female','1975-02-10',95031); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(103,'Lu Jun','male','1974-06-03',95031); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(804,'Li Cheng','male','1958-12-02','associate professor','Computer Department'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(856,'Xu Zhang','male','1969-03-12','lecturer','Department of Electronic Engineering'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(825,'Wang Ping','female','1972-05-05','assistant','Computer Department'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(831,'Bing Liu','female','1977-08-14','assistant','Department of Electronic Engineering'); INSERT INTO course(cno,cname,tno) VALUES('3-105','Introduction to computer',825); INSERT INTO course(cno,cname,tno) VALUES('3-245','operating system',804); INSERT INTO course(cno,cname,tno) VALUES('6-166','Data circuit',856); INSERT INTO course(cno,cname,tno) VALUES('19-888','Advanced mathematics',831); INSERT INTO score(sno,cno,degree) VALUES(103,'3-245',86); INSERT INTO score(sno,cno,degree) VALUES(105,'3-245',75); INSERT INTO score(sno,cno,degree) VALUES(109,'3-245',68); INSERT INTO score(sno,cno,degree) VALUES(103,'3-105',92); INSERT INTO score(sno,cno,degree) VALUES(105,'3-105',88); INSERT INTO score(sno,cno,degree) VALUES(109,'3-105',76); INSERT INTO score(sno,cno,degree) VALUES(101,'3-105',64); INSERT INTO score(sno,cno,degree) VALUES(107,'3-105',91); INSERT INTO score(sno,cno,degree) VALUES(108,'3-105',78); INSERT INTO score(sno,cno,degree) VALUES(101,'6-166',85); INSERT INTO score(sno,cno,degree) VALUES(107,'6-166',79); INSERT INTO score(sno,cno,degree) VALUES(108,'6-166',81);
task
1) Query table
query school All tables under use school select * from student; select * from teacher; select * from course; select * from score;
student:
teacher:
course:
score:
2) Insert data:
Insert a data into the grade sheet insert into score(sno,cno,degree) values(110,'19-888',99.0)
3) Connection query
Question 1: query the student's student number, class, gender, grade and teaching teacher
Step 1:
//Linked table query -- connect student table and grade table together select a.sno,a.sclass,a.ssex,b.cno,b.degree from student a join score b where a.sno=b.sno
Step 2:
//The teacher table is linked to the curriculum select c.cno,c.cname,d.tname from course c join teacher d where c.tno=d.tno
Step 3:
SELECT E.sno AS 'Student number',E.sclass AS 'class',E.ssex AS 'Gender', F.cname AS 'curriculum', F.tname AS 'Instructor', E.degree AS 'achievement' FROM( SELECT a.sno,a.sclass,a.ssex,b.cno,b.degree FROM student a JOIN score b WHERE a.sno=b.sno)E LEFT JOIN( SELECT c.cno,c.cname,d.tname FROM course c JOIN teacher d WHERE c.tno=d.tno )F ON E.cno=F.cno
Question 2: query student number, name, course scores and total scores.
SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`
SELECT S.sno AS 'Student number' ,S.sname AS 'full name', MAX(CASE WHEN G.cname='Advanced mathematics' THEN degree ELSE 0 END) AS 'Advanced mathematics', MAX(CASE WHEN G.cname='Introduction to computer' THEN degree ELSE 0 END) AS 'Introduction to computer', MAX(CASE WHEN G.cname='operating system' THEN degree ELSE 0 END) AS 'operating system', MAX(CASE WHEN G.cname='Data circuit' THEN degree ELSE 0 END) AS'Data circuit', SUM(degree) AS Total score, AVG(degree) AS average FROM ( SELECT * FROM student)S LEFT JOIN( SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`)G ON S.sno=G.sno GROUP BY S.sno
Question 3: on the basis of question 2, make a fuzzy query about the student surnamed "Wang".
SELECT S.sno AS 'Student number' ,S.sname AS 'full name', MAX(CASE WHEN G.cname='Advanced mathematics' THEN degree ELSE 0 END) AS 'Advanced mathematics', MAX(CASE WHEN G.cname='Introduction to computer' THEN degree ELSE 0 END) AS 'Introduction to computer', MAX(CASE WHEN G.cname='operating system' THEN degree ELSE 0 END) AS 'operating system', MAX(CASE WHEN G.cname='Data circuit' THEN degree ELSE 0 END) AS'Data circuit', SUM(degree) AS Total score, AVG(degree) AS average FROM ( SELECT * FROM student)S LEFT JOIN( SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`)G ON S.sno=G.sno WHERE S.sname LIKE 'king%'//Add criteria to judge and filter. GROUP BY S.sno
Question 4: time stamp the date on the teacher's table, and the format is: day month year;
FROM_UNIXTIME(UNIX_TIMESTAMP(t.`tbirthday`),'%D-%M-%Y') AS 'Date of birth after conversion' FROM teacher t GROUP BY tno;
Question 5: Based on question 3, use the Round function to Round the student's score and retain the corresponding decimal places.
SELECT S.sno AS 'Student number' ,S.sname AS 'full name', ROUND(MAX(CASE WHEN G.cname='Advanced mathematics' THEN degree ELSE 0 END),0) AS 'Advanced mathematics',//Rounding ROUND(MAX(CASE WHEN G.cname='Introduction to computer' THEN degree ELSE 0 END),1)AS 'Introduction to computer',//Keep one decimal place ROUND(MAX(CASE WHEN G.cname='operating system' THEN degree ELSE 0 END),2) AS 'operating system',//Keep two decimal places ROUND(MAX(CASE WHEN G.cname='Data circuit' THEN degree ELSE 0 END),3)AS'Data circuit',//Keep three decimal places ROUND(SUM(degree),0)AS Total score,//Reserved integer ROUND(AVG(degree),2) AS average//Keep two decimal places FROM ( SELECT * FROM student)S LEFT JOIN( SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`)G ON S.sno=G.sno GROUP BY S.sno
Question 6: because the school has expanded its enrollment and added a new class, another administrator has created a new student table Sudent1. Please use the Union method to combine the two tables into one table and sort them with order by.
(1) To create a new student table:
CREATE TABLE Newstudent( `sno` VARCHAR(20) NOT NULL, `sname` VARCHAR(20) NOT NULL, `ssex` VARCHAR(20) NOT NULL DEFAULT 'male', `sbirthday` DATETIME, `sclass` VARCHAR(20));
(2) Import student information
INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(102,'Xiao Ming Zhang','male','1997-09-01',95033); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(104,'Kuang Zhengyi','male','1995-10-02',95031); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(106,'Wang Lijuan','female','1992-01-23',95033); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(108,'Xiao Jun Li','male','1990-02-20',95033); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(111,'Wang Fangfang','female','1997-02-10',95031); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(112,'Lu Yijun','female','1999-06-03',95031);
(3) Using the Union method, combine the two tables into one table and sort the student numbers with order by.
SELECT * FROM student UNION SELECT * FROM newstudent ORDER BY sno ;
The above database is a very basic school database. There are many on the Internet. Focus on the joint table operation of each database. In order to train my knowledge of database syntax, many topics are written by me to train an unfamiliar knowledge point, which is not comprehensive. For database statements, if they are not good enough, please criticize and correct them