Shulou(Shulou.com)06/02 Report--

Xiaobian to share with you the SQL database case analysis, I believe most people do not know how, so share this article for your reference, I hope you read this article after a great harvest, let us go to understand it!

Data Sheet/*Navicat SQLite Data TransferSource Server : schoolSource Server Version : 30808Source Host : :0Target Server Type : SQLiteTarget Server Version : 30808File Encoding : 65001Date: 2021-12-23 16:06:04*/PRAGMA foreign_keys = OFF;-- ------------------------------ Table structure for Course-- ----------------------------DROP TABLE IF EXISTS "main". "Course";CREATE TABLE Course( courseid integer primary key autoincrement, courseme varchar(32), teacherid int);-- ------------------------------ Records of Course-- ----------------------------INSERT INTO "main". "Course" VALUES (3001, 'language', 1001);INSERT INTO "main". "Course" VALUES (3002, 'Mathematical Learning', 1002);-------------------------------- "Mark";CREATE TABLE Mark( userid integer, courseid integer not null, score int default 0);-- ------------------------------ Records of Mark-- ----------------------------INSERT INTO "main". "Mark" VALUES (2001, 3001, 89);INSERT INTO "main". "Mark" VALUES (2001, 3002, 90);INSERT INTO "main". "Mark" VALUES (2002, 3001, 66);INSERT INTO "main". "Mark" VALUES (2003, 3002, 85);-- ------------------------------ Table structure for sqlite_sequence-- ----------------------------DROP TABLE IF EXISTS "main". "sqlite_sequence";CREATE TABLE sqlite_sequence(name,seq);-- ------------------------------ Records of sqlite_sequence-- ----------------------------INSERT INTO "main". "sqlite_sequence" VALUES ('Teacher', 1002);INSERT INTO "main". "sqlite_sequence" VALUES ('Student', 2002);INSERT INTO "main". "sqlite_sequence" VALUES ('Course', 3002);-- ------------------------------ Table structure for Student-- ----------------------------DROP TABLE IF EXISTS "main". "Student";CREATE TABLE Student( userid integer primary key autoincrement, username varchar(32), userage int, usersex varchar(32));-- ------------------------------ Records of Student-- ----------------------------INSERT INTO "main". "Student" VALUES (2001, 'Xiaoming', 18, 'male');INSERT INTO "main". "Student" VALUES (2002, 'Xiaohong', 18, ' Female');-------------------------------- "Teacher";CREATE TABLE Teacher( teacherid integer primary key autoincrement, teachername varchar(32));-- ------------------------------ Records of Teacher-- ----------------------------INSERT INTO "main". "Teacher" VALUES (1001, 'Zhang San');INSERT INTO "main". "Teacher" VALUES (1002, 'Li Si'); Question:

1. Query the student numbers of all students whose grades in the "Chinese" course are lower than those in the "Mathematics" course

select a.userid from (select userid,score from Mark where courseid ='3001')a,(select userid,score from Mark where courseid ='3002')b where a.userid = b.userid and a.score60;

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

select s.userid ,s.username ,count_courseid as Number of courses selected, sum_score as Total achievements from Student sleft join (select userid,count(courseid ) as count_courseid,sum(score) as sum_score from Mark group by userid ) score s.userid = sc.userid;

4. Query the number of teachers surnamed 'Li':

select count(teachername )from Teacher where teachername like 'Zhang %';

5. Search for students with scores less than 60 in descending order of scores:

select userid ,scorefrom Markwhere courseid ='3001'and score80;

13. Query the average score of each course. The results are sorted in ascending order of average score. When the average score is the same, they are sorted in descending order of course number.

select courseid ,avg(score)from Mark group by courseid order by avg(score),courseid desc;

14. Query the name and score of students whose course name is "Mathematics" and whose score is higher than 85:

select c.courseme ,Student.userid ,Student.username ,Mark. score from Course cleave join Mark on Mark.courseid = c.courseid LEFT JOIN Student on Student.userid = Mark.userid where c.courseme = 'math' and Mark.score>85; Above is "SQL Database Case Analysis" All the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.