[you in the future, will thank you for your efforts today] two questions a day, one difficult and one easy, and make a little progress every day, which may directly lead to the success of an interview or the easy completion of the work, so as to promote and raise salary, marry Bai Fumei, come on!
🍅 Venue: data warehouse exchange group ✖️ CSDN
🍅 Topic summary: https://blog.csdn.net/weixin_39032019/category_11459536.html
🍅 Post punch: https://bbs.csdn.net/forums/ITID?typeId=18647
🍅 Add punch in: https://app.yinxiang.com/fx/13ce6bbd-f36f-4e92-be53-92dd381ed729
catalogue
❤ 2, 30 questions, have a good fight!
🍅 Already have ideas? Start punching in!
❤ 1, Test data preparation
In order to facilitate the computer experiment, partners can directly execute the code and generate test data!
🍅 Data sheet introduction
--1. Transcript
SC(SId,CId,score)
--SId student number, CId course number, score
--2. Student form
Student(SId,Sname,Sage,Ssex)
--SId student ID, Sname student name, Sage date of birth, Ssex student gender
--3. Curriculum
Course(CId,Cname,TId)
--CId course number, Cname course name, TId teacher number
--4. Teacher list
Teacher(TId,Tname)
--TId teacher number, Tname teacher name
😋 Student form
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , 'Tomatoes' , '1990-01-01' , 'male');
insert into Student values('02' , 'Qian Dian' , '1990-12-21' , 'male');
insert into Student values('03' , 'Sun Feng' , '1990-12-20' , 'male');
insert into Student values('04' , 'Li Yun' , '1990-12-06' , 'male');
insert into Student values('05' , 'Zhou Mei' , '1991-12-01' , 'female');
insert into Student values('06' , 'Wu Lan' , '1992-01-01' , 'female');
insert into Student values('07' , 'Zheng Zhu' , '1989-01-01' , 'female');
insert into Student values('09' , 'Zhang San' , '2017-12-20' , 'female');
insert into Student values('10' , 'Tomatoes' , '2017-12-25' , 'female');
insert into Student values('11' , 'Li Si' , '2012-06-06' , 'female');
insert into Student values('12' , 'Zhao Liu' , '2013-06-13' , 'female');
insert into Student values('13' , 'Sun Qi' , '2014-06-01' , 'female');
😋 Chart of accounts Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , 'language' , '02');
insert into Course values('02' , 'mathematics' , '01');
insert into Course values('03' , 'English' , '03');
😋 Teacher table
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , 'Zhang San');
insert into Teacher values('02' , 'Li Si');
insert into Teacher values('03' , 'Wang Wu');
insert into Teacher values('04' , 'Tomatoes');😋 Transcript SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('08' , '03' , 95);
insert into SC values('08' , '03' , 94);
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
❤ 2, 30 questions, have a good fight!
-
Query the information and course scores of students with higher scores in "01" course than "02" course
-
Query the student number, student name and average score of students whose average score is greater than or equal to 60
-
Query the student number, student name, total number of courses selected and the total score of all courses of all students (null if there is no score)
-
Query the information of students who have learned "Zhang San"
-
Query the information of students who have not studied all courses
-
Query the information of other students who have exactly the same courses as the students of "01"
-
Query the student number, name and average score of students who fail two or more courses
-
Retrieve the student information in descending order of "01" course score less than 60
-
Displays the scores of all courses and average scores of all students from high to low
-
Query the highest score, lowest score and average score of each subject:
-
It is displayed in the following form: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate and excellent rate
-
Pass is > = 60, medium is 70-80, excellent is 80-90, excellent is > = 90
-
It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number
-
Sort according to the scores of each subject and display the ranking. If the Score is repeated, the ranking vacancy will be reserved
-
Count the number of students in each score segment of each subject: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage
-
Query the top three records of each subject
-
Query the number of students selected for each course
-
Find out the student number and name of students who only take two courses
-
Query the list of same-sex students with the same name and count the number of students with the same name
-
Query the average score of each course. The results are arranged in descending order according to the average score. If the average score is the same, they are arranged in ascending order according to the course number
-
Query the student number, name and average score of all students whose average score is greater than or equal to 85
-
Query the courses and scores of all students (there are cases where students do not have grades and do not choose courses)
-
Query the name, course name and score of any course with a score of more than 70
-
Query the student number and name of students with course number 01 and course score above 80, and find the number of students in each course
-
The results are not repeated. Query the information of the students with the highest scores and their scores among the students taking the courses taught by "Zhang San". In case of duplication, query the information of the students with the highest scores and their scores among the students taking the courses taught by "Zhang San"
-
Query the student number, course number and student score of students with the same score in different courses
-
Query the top two students with the best performance in each course and count the number of students in each course (only courses with more than 5 people can be counted).
-
Retrieve the student number of at least two courses
-
Query the student information of all courses
-
Query the age of each student, calculated by year only
🍅 Already have ideas? Start punching in!
Punch in address: https://bbs.csdn.net/forums/ITID?typeId=18647 Upper right corner [post]
❤ 3, Examination reward
🍅 prize
Reward 5 programmer calendars + 5 new programmer magazines (one out of two), first come first!
🍅 Lottery method
Like, collect and comment on this article. Draw 10 prizes!
Add the official account of "information technology think tank" at the end of the article:
🍅 Hard core materials: get PPT template, resume template and PDF of industry classic books.
🍅 Technical assistance: the leader of the technology group points out the maze. Your problem may not be a problem. Ask for resources to shout in the group.
🍅 Interview question bank: it is jointly contributed by small partners in the technology group. The hot real interview questions of large factories are constantly updated.
🍅 Knowledge system: including programming language, algorithm, big data ecosystem components (Mysql, Hive, Spark, Flink), data warehouse, front end, etc.
👇👇 Technical exchange, if you are the one 👇👇