Database exercise (continued from last blog post)

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

Tags: Database MySQL

Posted on Wed, 24 Nov 2021 20:07:33 -0500 by c0le

Programmer Help

Database exercise (continued from last blog post)

Hot Tags