SQL interview test (Part 1), sqlpart

SQL interview test (Part 1), sqlpart

This article is inOn the basis of the original Cat Qi post, the notes implemented by myself in the MySql database by question are continuously updated...

Refer to the original post:Http://www.cnblogs.com/qixuejia/p/3637735.html

01 table structure

Student (Sno, Sname, Sage, Ssex) Student table
Course (Cno, Cname, Tno) Curriculum
SC (Sno, Cno, score) Orders table
Teacher (Tno, Tname) Instructor table

02 create a table and insert Test Data

(1) create a table:

1 drop table if exists student; 2 drop table if exists course; 3 drop table if exists SC; 4 DROP TABLE IF EXISTS teacher; 5 6 CREATE TABLE Student 7 (8 Sno int, 9 Sname varchar (32), 10 Sage int, 11 Ssex varchar (8) 12) ENGINE = InnoDB default charset = utf8 COLLATE = utf8_unicode_ci; 13 14 create table Course 15 (16 Cno INT, 17 Cname varchar (32), 18 Tno INT 19) ENGINE = InnoDB default charset = utf8 COLLATE = utf8_unicode_ci; 20 21 create table SC 22 (23 Sno INT, 24 Cno INT, 25 score INT 26) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci; 27 28 create table Teacher 29 (30 Tno INT, 31 Tname varchar (16) 32) ENGINE = InnoDB default charset = utf8 COLLATE = utf8_unicode_ci;View Code

[Note] You must add the "ENGINE = InnoDB default charset = utf8 COLLATE = utf8_unicode_ci" command when creating a table in the MySQL database. Otherwise, garbled characters may occur in Chinese.

(2) Insert test data:

1 insert into Student select 1, 'Liu yi', 18, 'male' union all 2 select 2, 'Qian 2', 19, 'female 'union all 3 select 3, 'zhang san', 17, 'male' union all 4 select 4, 'Li si', 18, 'female 'union all 5 select 5, 'wang wu', 17, 'male' union all 6 select 6, 'zhao liu', 19, 'female '7 8 insert into Teacher select 1, 'peiping' union all 9 select 2, 'He Gao' union all10 select 3, 'yang Yan 'union all11 select 4, 'zhou re'; 12 13 insert into Course select 1, 'China', 1 union all14 select 2, 'mat', 2 union all15 select 3, 'English ', 3 union all16 select 4, 'Physical', 4; 17 18 insert into SC 19 select 1, 1, 56 union all 20 select, 78 union all 21 select, 67 union all 22 select, 58 union all 23 select, 79 union all 24 select, 81 union all 25 select 2, 3, 92 union all 26 select 2, 4, 68 union all 27 select 3, 1, 91 union all 28 select 3, 2, 47 union all 29 select 3, 3, 88 union all 30 select 3, 4, 56 union all 31 select 4, 2, 88 union all 32 select 4, 3, 90 union all 33 select 4, 4, 93 union all 34 select 5, 1, 46 union all 35 select 5, 3, 78 union all 36 select 5, 4, 53 union all 37 select 6, 1, 35 union all 38 select 6, 2, 68 union all 39 select 6, 4, 71;View Code 03 problem and implementation code

(1) query the student IDs of all students whose scores are higher than those of the "2" course;

select a.sno from(select sno,score from sc where cno=1) a,(select sno,score from sc where cno=2) bwhere a.sno=b.sno and a.score>b.score;

(2) query the student ID and average score of students whose average score is greater than 60;

 select Sno,AVG(Score) as AvgScore  from SC group by Sno having AVG(Score)>60

(3) query the student ID, name, number of course selections, and total score of all students;

 select student.sno,student.sname,count(sc.cno),sum(sc.score) from student left outer join sc on student.sno = sc.sno group by student.sno order by student.sno;

(4) query the number of teachers surnamed "Li;

Select count (distinct tname) as count from teacher where tname like 'Lee % ';

(5) query the student ID and name of the student who has not learned the course "ye ping;

Select s. sno, s. sname from student s where s. sno not in (select distinct (SC. sno) from SC, course c, teacher twhere SC. cno = c. cno and c. tno = t. tno and t. tname = 'peiping ')

(6) query the student ID and name of the student who has learned "1" and has also learned the "2" course;

Select s. sno, s. sname from student s, (select sno from SC where cno = 1) a, (select sno from SC where cno = 2) B where s. sno =. sno and. sno = B. sno;

Method 2 use the exist Function

 select s.Sno,s.Sname from Student s,SC sc where s.Sno=sc.Sno and sc.Cno=1 and exists (     select * from SC sc2 where sc.Sno=sc2.Sno and sc2.Cno=2 )

(7) query the student ID and name of all students who have learned the courses taught by instructor ye Ping;

Select s. sno, s. sname from student s, teacher t, course c left outer join SC on c. cno = SC. cno where t. tname = "ye ping" and t. tno = c. cno and s. sno = SC. sno;

Or:

Select s. sno, s. sname from student s where s. sno in (select SC. snofrom SC, course c, teacher twhere c. cno = SC. cno and c. tno = t. tno and t. tname = "ye ping" group by SC. snohaving count (SC. cno) = (select count (c1.cno) from course c1, teacher t1where c1.tno = t1.tno and t1, tname = "ye ping "));

(8) query the student ID and name of all students whose scores are lower than those of course number "1;

Select s. sno, s. sname from student s where s. sno in (select. sno from (select sno, score from SC where cno = 2) a, (select sno, score from SC where cno = 1) bwhere. sno = B. sno and. score <B. score );

(9) query the student ID and name of a student with a course score less than 60;

Select s. sno, s. sname from student s, SC where SC. score <60 and s. sno = SC. sno group by s. sno;

 

(10) query the student ID and name of the student who has not completed all the courses;

Select s. sno, s. sname from student s where s. sno not in (select SC. sno from scgroup by SC. snohaving count (distinct SC. cno) = (select count (distinct c. cno) from course c ));

(11) query the student ID and name of at least one course and the student whose student ID is "1;

Select distinct (s. sno), s. sname from student s, SC where s. sno = SC. sno and SC. cno in (select distinct (cno) from SC where sno = 1 );

(12) query all the courses whose student ID is "1"Other studentsStudent ID and name;

Select distinct (s. sno), s. sname from student s, SC where s. sno = SC. sno and s. sno! = 1 and SC. cno in (select distinct (cno) from SC where sno = 1 );

(13) change the average score of the Course taught by "ye ping" in the "SC" table;

 

Update SC set score = (select avg (sc1.score) from SC sc1, course c, teacher twhere sc1.cno = c. cno and c. tno = t. tno and t. tname = "ye ping") where cno in (select cno from course c, teacher t where c. tno = t. tno and t. tname = "ye ping ");

(14) query the student ID and name of other students whose courses are identical to those of "2;

Select s. sno, s. sname from student s where s. sno! = 2 and s. sno in (select distinct (sno) from scwhere cno in (select cno from SC where sno = 2) group by snohaving count (distinct cno) = (select count (distinct cno) from SC where sno = 2 ));

(15) Delete the SC table records for learning "ye ping;

Delete from SC where cno in (select c. cno from course c, teacher twhere c. tno = t. tno and t. tname = "ye ping ");

(16) insert some records into the SC table. These records must meet the following requirements: ① students who have not passed the "2" course number as the student ID; ② use the average score of course 2 as the score;

Insert into SC select s. sno, 2, (select avg (score) from SC where cno = 2) from student s where s. sno not in (select distinct (sno) from SC where cno = 2 );

(17) show the course scores of all students in Chinese, mathematics, and English on average, as shown in the following form: Student ID, language, mathematics, English, number of valid courses, and average valid score. [Deleted Data in 15 questions has been added here]

Select sc0.sno as "student ID", (select score from SC where sno = sc0.sno and cno = 1) as "language ", (select score from SC where sno = sc0.sno and cno = 2) as "Mathematics", (select score from SC where sno = sc0.sno and cno = 3) as "English ", count (sc0.cno) as "Number of valid courses", avg (sc0.score) as "average effective score" from SC sc0 group by sc0.sno order by avg (sc0.score );

(18) query the highest score and lowest score of each subject. The score is displayed as follows: course ID, highest score, lowest score;

Select cno as "course ID", max (score) as "highest score", min (score) as "lowest score" from SC group by cno;

(19) The average score of each subject ranges from low to high and the pass rate from high to low;

Select SC. cno as "course ID", c. cname as "Course name", avg (SC. score) as "average score", 100 * sum (case when SC. score> = 60 then 1 else 0 end)/count (SC. score) as "Percent (%)" from SC, course c where SC. cno = c. cno group by SC. cno order by avg (SC. score) desc;

(20) query the average score and pass percentage of the following courses (Note: The score must be displayed in 1 row): Enterprise Management (2), OO & UML (3), Database (4)

Select sum (case when cno = 2 then score else 0 end)/sum (case when cno = 2 then 1 else 0 end) as "average enterprise management score ", 100 * sum (case when cno = 2 and score> = 60 then 1 else 0 end)/sum (case when cno = 2 then 1 else 0 end) as "Enterprise Management pass rate (%)", sum (case when cno = 3 then score else 0 end)/sum (case when cno = 3 then 1 else 0 end) as "OO & UML average score", 100 * sum (case when cno = 3 and score> = 60 then 1 else 0 end) /sum (case when cno = 3 then 1 else 0 end) as "OO & UML pass rate (%)", sum (case when cno = 4 then score else 0 end) /sum (case when cno = 4 then 1 else 0 end) as "average database score ", 100 * sum (case when cno = 4 and score> = 60 then 1 else 0 end)/sum (case when cno = 4 then 1 else 0 end) as "database pass rate (%)" from SC;

(21) query the average scores of different courses taught by different teachers from high to low; 

Select t. tname as "instructor name", c. cname as "Course name", avg (SC. score) as "average score" from SC, teacher t, course c where t. tno = c. tno and c. cno = SC. cno group by t. tno order by avg (SC. score) desc;

(22) query the transcript of the following course scores of 3rd to 6th students: Enterprise Management (1), Marx (2), UML (3), Database (4) 

Select distinct SC. sno As "Student ID", Student. sname as "Student name", T1.score as "Enterprise Management", T2.score as "Marx", T3.score as "UML", T4.score as "Database", ifnull (T1.score, 0) + ifnull (T2.score, 0) + ifnull (T3.score, 0) + ifnull (T4.score, 0) as "total score" from Student, SC left join SC as T1 on SC. sno = T1.Sno and T1.Cno = 1 left join SC as T2 on SC. sno = T2.Sno and T2.Cno = 2 left join SC as T3 on SC. sno = T3.Sno and T3.Cno = 3 left join SC as T4 on SC. sno = T4.Sno and T4.Cno = 4 where student. sno = SC. sno order by ifnull (T1.score, 0) + ifnull (T2.score, 0) + ifnull (T3.score, 0) + ifnull (T4.score, 0) desc;