Day27 SQL enhancement and Practice

day27 SQL enhancement and Practice

Course objective: to practice the design of common SQL statements and table structures.

Course overview:

  • SQL hardening
  • Table structure design (blog system)

1. SQL enhancement

  1. Create database & table structure and input data according to the above figure (you can create data by yourself)

    create database day27db default charset utf8 collate utf8_general_ci;
use day27db;

drop database day27db;
drop database IF EXISTS day27db;

    Use the import database command:

    • Import

      mysql -u root -p  day27db < /Users/wupeiqi/day27db.sql

    • export

      # Structure + data
mysqldump -u root -p  day27db > /Users/wupeiqi/day27db2.sql

# structure
mysqldump -u root -p -d day27db > /Users/wupeiqi/day27db3.sql

    create table class(
	cid int not null auto_increment primary key,
    caption varchar(16) not null
)default charset=utf8;

INSERT INTO class VALUES ('1', 'Class two of three years'), ('2', 'Three years and three classes'), ('3', 'Two classes a year'), ('4', 'Class 9 of the second year');


create table student(
	 sid int not null auto_increment primary key,
    gender char(1) not null,
    class_id int not null,
    sname varchar(16) not null,
    constraint fk_student_class foreign key (class_id) references class(cid)
)default charset=utf8;

INSERT INTO student VALUES ('1', 'male', '1', 'understand'), ('2', 'female', '1', 'Steel egg'), ('3', 'male', '1', 'Zhang San'), ('4', 'male', '1', 'Zhang Yi'), ('5', 'female', '1', 'Zhang Er'), ('6', 'male', '1', 'Zhang Si'), ('7', 'female', '2', 'Hammer'), ('8', 'male', '2', 'Li San'), ('9', 'male', '2', 'Li Yi'), ('10', 'female', '2', 'Li Er'), ('11', 'male', '2', 'Li Si'), ('12', 'female', '3', 'Like flowers'), ('13', 'male', '3', 'Liu San'), ('14', 'male', '3', 'Liu Yi'), ('15', 'female', '3', 'Liu er'), ('16', 'male', '3', 'Liu Si');


create table teacher(
	 tid int not null auto_increment primary key,
    tname varchar(16) not null
)default charset=utf8;

INSERT INTO `teacher` VALUES ('1', 'Mr. Zhang Lei'), ('2', 'Miss Li Ping'), ('3', 'Miss Liu Haiyan'), ('4', 'Miss Zhu Yunhai'), ('5', 'Miss Li Jie');


create table course(
	   cid int not null auto_increment primary key,
    cname varchar(16) not null,
    teacher_id int not null,
    constraint fk_course_teacher foreign key (teacher_id) references teacher(tid)
)default charset=utf8;

INSERT INTO `course` VALUES ('1', 'biology', '1'), ('2', 'Physics', '2'), ('3', 'Sports', '3'), ('4', 'Fine Arts', '2');


CREATE TABLE `score` (
  `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  `num` int NOT NULL,
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;


INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');

  2. Create a user luffy and give all permissions to this database.

    create user 'luffy'@'%' identified by 'root123';
grant all privileges on day27db.* TO 'luffy'@'%';
flush privileges;

  3. Query the number of teachers surnamed "Li".

    select * from teacher where tname like "Lee%";

  4. Check the list of students surnamed "Zhang".

    select * from student where sname like "Zhang%";

  5. Query the number of boys and girls.

    select gender,count(1) from student group by gender;

  6. Query the list of students with the same name and surname, and count the number of students with the same name.

    select sname,count(1) from student group by sname;
select sname,count(1) from student group by sname having count(1) > 1;

  7. Query all students in "class 2, grade 3".

    select * from student left join class on student.class_id = class.cid where class.caption="Class two of three years";

  8. Query the class name and class number of each class.

    select class_id,count(1) from student group by class_id;

select class.caption,count(1) from student left join class on student.class_id = class.cid group by class.caption;

  9. Query the student number, name, grade and course name of students whose score is less than 60.

    select * from score where num <60;

select 
	student.sid,
	student.sname,
	score.num,
	course.cname 
from 
	score 
	left join student on score.student_id=student.sid 
	left join course on score.course_id =course.cid 
where num <60;

  10. Query all student ID S, student names and grades of "biology course".

    select * from score left join course on score.course_id =course.cid where course.cname="biology";


select 
	student.sid,
	student.sname,
	score.num 
from 
	score 
	left join course on score.course_id =course.cid 
	left join student on score.student_id=student.sid 
where course.cname="biology";

  11. Query the ID, name and grade of all students who have taken the "biology course" and whose score is less than 60.

    select student.sid,student.sname,score.num from score left join course on score.course_id =course.cid left join student on score.student_id=student.sid where course.cname="biology" and score.num < 60;

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

    select student_id,count(1),sum(num) from score group by student_id;

select student_id,student.sname,count(1),sum(num) from score left join student on score.student_id=student.sid group by student_id;

  13. Query the number of elective students in each subject.

    select course_id,count(1) from score group by course_id;

select course_id,course.cname,count(1) from score left join course on score.course_id =course.cid group by course_id;

  14. Query the total score, maximum score and minimum score of each subject, and display: course ID, course name, total score, maximum score and minimum score.

    select course_id,course.cname,sum(num), max(num), min(num) from score left join course on score.course_id =course.cid group by course_id;

  15. Query the average score of each subject and display: course ID, course name and average score.

    select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id;

  16. Query the average score of each subject and display: course ID, course name and average score (sorted by average score from large to small).

    select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id order by avg(num) desc;


select course_id,course.cname,avg(num) as A from score left join course on score.course_id =course.cid group by course_id order by A desc;

  17. Query the average score and pass rate of each subject, and display: course ID, course name, average score and pass rate.

    10/20 = pass rate

select course_id,count(1) from score group by course_id;

    select 
	sid,
	course_id,
	num,
	case when score.num >= 60 then 1 else 0 end "Pass or not" 
from score;

    select sid,course_id,num,case when score.num > 60 then 1 else 0 end "Pass or not" from score;

    select 
	course_id,
	course.cname,
	avg(num),
	count(1) as total,
	sum(case when score.num > 60 then 1 else 0 end) 
from 
	score 
	left join course on score.course_id =course.cid 
group by 
	course_id;

    select 
	course_id,
	course.cname,
	avg(num),
	sum(case when score.num > 60 then 1 else 0 end)/count(1) *100 as percent 
from 
	score 
	left join course on score.course_id =course.cid 
group by 
	course_id;

  18. Query the student number and average score of all students whose average score is greater than 60;

    select student_id,avg(num) from score group by student_id having avg(num) > 60;

  19. Query the student number, average score and name of all students with an average score greater than 85.

    select student_id,avg(num) from score group by student_id having avg(num) > 85;

select student_id,avg(num),student.sname from score left join student on score.student_id=student.sid   group by student_id having avg(num) > 85;

  20. Query the student number, name, total score and average score of each student in "class two of three years".

    SELECT
	* 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id;

    SELECT
	* 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
WHERE
	class.caption = "Class two of three years";

    SELECT
	student_id,
	sname,
	sum( num ),
	avg( num ) 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
WHERE
	class.caption = "Class two of three years" 
GROUP BY
	student_id

  21. Query the class name, total score, average score and pass rate of each class (sorted by average score from large to small).

    SELECT
	class.cid,
	class.caption,
	sum( num ),
	avg( num ) as av,
	sum( CASE WHEN score.num > 60 THEN 1 ELSE 0 END ) / count( 1 ) * 100 as JG
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
GROUP BY
	class.cid
ORDER BY
	av desc

  22. Query the student number and name of the students who have studied "Bodo" teacher's class.

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
	teacher.tname = "Bodo"

  23. Query the student number and name of the students who have not learned the "Bodo" teacher's class.

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
	teacher.tname != "Bodo" 

    select * from student where sid not in(
    SELECT
        student.sid
    FROM
        score
        LEFT JOIN student ON score.student_id = student.sid
        LEFT JOIN course ON score.course_id = course.cid
        LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
        teacher.tname = "Bodo" 
)

  24. Query the names and grades of the students with the highest scores among the students taking the courses taught by the "sky" teacher (regardless of juxtaposition).

    SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = "blue sky" 
ORDER BY
	score.num DESC 
	LIMIT 1

  25. Query the names and grades of the students with the highest scores among the students taking the courses taught by the "sky" teacher (consider juxtaposition).

    SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = "blue sky" 
	AND score.num = (
        SELECT
            max( num ) 
        FROM
            score
            LEFT JOIN course ON score.course_id = course.cid
            LEFT JOIN teacher ON course.teacher_id = teacher.tid 
        WHERE
        teacher.tname = "blue sky" 
	)

  26. Query the student numbers and names of all students who have only taken one course.

    SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) =1

  27. Query the student number, student name and number of elective courses of at least two courses.

    SELECT
	student.sid,
	student.sname ,
	count(1)
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) >= 2

  28. Query the student number, student name and number of elective courses of students who fail two or more courses.

    SELECT
	student.sid,
	student.sname ,
	count(1)
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
where 
	num < 60
GROUP BY
	student_id 
HAVING
	count( 1 ) >= 2

  29. Query the student number and name of students who have taken all courses.

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) = ( SELECT count( 1 ) FROM course )

  30. Query the student number and name of students who do not take all courses.

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) != ( SELECT count( 1 ) FROM course )

  31. Query the course number and course name of the course that all students have taken.

    SELECT
	course.cid,
	course.cname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
GROUP BY
	course_id 
HAVING
	count( 1 ) = ( SELECT count( 1 ) FROM student )

  32. Query the student numbers and names of all students taking "biology" and "physics" courses.

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	course.cname in ("biology","Physics")
GROUP BY
	student_id
having 
	count(1) = 2;

  33. Query the student number and name of other students whose at least one course is the same as the course selected by the student with student number "1".

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	score.course_id in ( select course_id from score where student_id=1)
	and score.student_id != 1
GROUP BY
	student_id
HAVING
	count(1) > 1

  34. Query the student numbers and names of other students who are exactly the same as the elective courses of students with student number "2".

    SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	score.course_id in ( select course_id from score where student_id=2)
	and score.student_id in (
    	select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2
    )
GROUP BY
	student_id
HAVING
	count(1) = ( select count(1) from score where student_id=2 )
	
	
# If id=2, the student has the same number of courses as others.
select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2

select 
	student_id 
from 
	score 
where 
	student_id!=2 
group by 
	student_id 
having 
	count(1) = select count(1) from score where student_id=2

  35. Query the student numbers of all students whose grades in "biology" course are higher than those in "physics" course;

    SELECT
	* 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
where 
	cname in ("biology","Physics");

    SELECT
	*,
	case cname WHEN "biology" then num else -1 end sw,
	case cname WHEN "Physics" then num else -1 end wl
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
where 
	cname in ("biology","Physics");

    SELECT
	student_id,
	max(case cname WHEN "biology" then num else -1 end) as sw,
	max(case cname WHEN "Physics" then num else -1 end) as wl
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
where 
	cname in ("biology","Physics")
GROUP BY
	student_id;

    SELECT
	student_id,
	max( CASE cname WHEN "biology" THEN num ELSE 0 END ) AS sw,
	max( CASE cname WHEN "Physics" THEN num ELSE 0 END ) AS wl 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
WHERE
	cname IN ( "biology", "Physics" ) 
GROUP BY
	student_id 
HAVING
	sw > wl;

  36. Query the top three with the best scores in each course (regardless of the juxtaposition of scores).

    SELECT
	cid,
	cname,
	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 0) as "1st place",
	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 1) as "2nd place",
	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 2) as "3rd place"
FROM
	course;

  37. Query the top three with the best scores in each course (considering the juxtaposition of scores).

    SELECT
	cid,
	cname,
	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "Highest score",
	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "Second high score",
	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as "Third high score"
FROM
	course;

    select 
	* 
from 
	score 
	
	left join (
		SELECT
			cid,
			cname,
			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "Highest score",
			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "Second high score",
			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as third
		FROM
			course ) as C on score.course_id = C.cid 
WHERE
	score.num >= C.third

  38. Create a table sc, and then insert all the data in the score table into the sc table.

    CREATE TABLE `sc` (
  `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  `num` int NOT NULL,
  CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;

    INSERT INTO sc SELECT * from score;

  39. Insert some records into the sc table, which are required to meet the following conditions:

    • Student ID: the student number of the student who has not attended the course with the course ID of "2";
    • Course ID: 2
    • Score: 80
    -- Yes
select student_id from score where course_id =2;

-- Never
SELECT
	sid
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )


-- Construction data
SELECT
	sid,
	2,
	80
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );

    INSERT INTO sc ( student_id, course_id, num ) SELECT
sid,
2,
80 
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

  40. Insert some records into the sc table, which are required to meet the following conditions:

    • Student ID: the student number of the student who has not attended the course with course ID "2".
    • Course ID: 2.
    • The score is: the highest score of course ID 3.
    SELECT
sid,
2,
(select max(num) from score where course_id=3) as num
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );

    INSERT INTO sc ( student_id, course_id, num ) SELECT
sid,
2,
(select max(num) from score where course_id=3) as num
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

2. Design table structure

The corresponding table structure is designed according to the following business requirements, and the following functions need to be covered internally.

  • register
  • Sign in
  • Post a blog
  • View the blog list and display the blog title, creation time, number of reads, number of comments, number of likes, etc.
  • Blog details, showing blog details, comments, etc.
    • Comment
    • Like or step on
    • Number of readings + 1

Referring to the following pictures, please design the corresponding table structure according to the following functions.

Note: you only need to design the table structure, and you don't need to use python code to realize specific functions (you can better realize it after learning a little more knowledge).

Blog system - table structure design

drop database blog;
drop database IF EXISTS blog;
create database blog default charset utf8 collate utf8_general_ci;
use blog;

create table user(
	id int not null auto_increment primary key,
    username varchar(16) not null,
    nickname varchar(16) not null,
	mobile char(11) not null,
    password varchar(64) not null,
    email varchar(64) not null,
    ctime datetime not null
)default charset=utf8;


create table article(
	id int not null auto_increment primary key,
    title varchar(255) not null,
    text text not null,
	read_count int default 0,
	comment_count int default 0,
	up_count int default 0,
	down_count int default 0,
    user_id int not null,
    ctime datetime not null,
    constraint fk_article_user foreign key (user_id) references user(id)
)default charset=utf8;


create table comment(
    id int not null auto_increment primary key,
    content varchar(255) not null,
    user_id int not null,
	article_id int not null,
    ctime datetime not null,
    constraint fk_comment_user foreign key (user_id) references user(id),
    constraint fk_comment_article foreign key (article_id) references article(id)
)default charset=utf8;


create table up_down(
    id int not null auto_increment primary key,
    choice tinyint not null,
    user_id int not null,
	article_id int not null,
    ctime datetime not null,
    constraint fk_up_down_user foreign key (user_id) references user(id),
    constraint fk_up_down_article foreign key (article_id) references article(id)
)default charset=utf8;

Keywords: MySQL

Added by academy. on Fri, 04 Feb 2022 09:08:23 +0200

Popular Keywords

©2024 Programming VIP