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