Click “SQL Database Development” above,
Set it as “top or star mark” to deliver dry goods in the first time.
50 SQL Classic interview questions (part 1)\
Continue to make up the remaining 25 questions for your friends today.
To make it easier to read, we’ll fill in the table structure and test data for you.
Table structure
1. Student table
Student(Sid,Sname,Sage,Ssex)
Student id, student name, date of birth, student gender
2. Class schedule
Course(Cid,Cname,Tid)
Course number, course name, teacher number
3. Teacher table
Teacher(Tid,Tname)
Teacher number, teacher name
4. Transcript
SC(Sid,Cid,Score)
Student number, course number, grade
Mysql > alter table relationships
The relationship between the four tables is shown below:
Let’s interpret the above relationship: \
1. Course number (Cid) of Course is used as the primary key, and the Course scores of one or more students can be seen in the score table (SC). The relationship between the two tables is 1: N. In the same way, the relationship between Student and SC is 1: n
2, Teacher table Teacher’s Teacher number (Tid) as the primary key, in the Course can bring one or more courses, the relationship between the two tables also belongs to 1: N.
Iii. Test data
1. Student table
CREATE TABLE Student (SID VARCHAR)10),
Sname nvarchar (10),
Sage datetime,
Ssex nvarchar (10INSERT INTO Student VALUES()'01' , N'zhao' , '1990-01-01' , N'male')
INSERT INTO Student VALUES('02' , N'money electricity' , '1990-12-21' , N'male')
INSERT INTO Student VALUES('03' , N'Sun Feng' , '1990-05-20' , N'male')
INSERT INTO Student VALUES('04' , N'liu yun' , '1990-08-06' , N'male')
INSERT INTO Student VALUES('05' , N'Zhou Mei' , '1991-12-01' , N'woman')
INSERT INTO Student VALUES('06' , N'nuss' , '1992-03-01' , N'woman')
INSERT INTO Student VALUES('07' , N'Zheng Zhu' , '1989-07-01' , N'woman')
INSERT INTO Student VALUES('08' , N'Wang Ju' , '1990-01-20' , N'woman')
Copy the codeThe results are as follows:
2. Class schedule
CREATE TABLE Course (CID VARCHAR (10),
Cname nvarchar (10),
TID VARCHAR (10INSERT INTO score VALUES()'01' , N'Chinese' , '02')
INSERT INTO Course VALUES('02' , N'mathematics' , '01')
INSERT INTO Course VALUES('03' , N'English' , '03')
Copy the codeThe results are as follows: \
3. Teacher table
CREATE TABLE Teacher (TID VARCHAR (10),
Tname nvarchar (10INSERT INTO Teacher VALUES()'01' , N'Joe')
INSERT INTO Teacher VALUES('02' , N'bill')
INSERT INTO Teacher VALUES('03' , N'Cathy')
Copy the codeThe results are as follows: \
4. Transcript
CREATE TABLE SC (SID VARCHAR (10),
CID VARCHAR (10),
score DECIMAL (18.1INSERT 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)
Copy the codeThe results are as follows: \
Interview questions and reference answers
Select student id and name from only two courses
SELECT SID,Sname FROM Student WHERE SID in (SELECT SID FROM (SELECT SID,COUNT(CID) FROM SC GROUP BY SID) A WHERE SID Course number =2
)
Copy the code27, Query the number of boys and girls
SELECT Ssex,COUNT(Ssex) FROM Student GROUP BY SsexCopy the codeSelect * from student whose name contains “wind”
SELECT * FROM Student
WHERE Sname like '% % wind'
Copy the code29, Check the list of students with the same name and same sex and count the number of students
SELECT * FROM Student A LEFT JOIN (SELECT Sname,Ssex,COUNT(*) FROM Student GROUP BY Sname,Ssex) B ON A.Sname=B.Sname and A.Ssex=B.Ssex WHERE B. The number >1
Copy the codeQuery the list of students born in 1990
SELECT * FROM Student
WHERE YEAR(Sage)=1990
Copy the codeQuery the average score of each course, the results are in descending order according to the average score, if the average score is the same, according to the course number in ascending order
SELECT AVG(score) FROM SC GROUP BY CID ORDER BY CID DESC,CIDCopy the codeSelect * from student where id, name, and grade point average = 85
SELECT a.id, a.name FROM Student A LEFT JOIN (SELECT AVG(score) FROM SC GROUP BY SID) B on a.id = b.id WHERE B. Average grade >85
Copy the codeSelect * from student whose course name is’ math ‘and whose score is less than 60
SELECT B.Sname,A.score FROM
(SELECT * FROM SC
WHERE score<60
and CID=
(SELECT CID FROM Course
WHERE Cname='mathematics'
)
) A
LEFT JOIN Student B on A.SID=B.SID
Copy the code34. Query the courses and scores of all students (there are cases where students have no grades and no courses)
SELECT A.SID,B.CID,B.score
FROM Student A
LEFT JOIN SC B on A.SID=B.SID
Copy the codeSelect name, course name, and grade from any course with a score of 70 or higher
SELECT A.Sname,D.Cname,D.score
FROM
(SELECT B.*,C.Cname
FROM
(SELECT * FROM SC WHERE score>70) B
LEFT JOIN Course C on B.CID=C.CID
) D
LEFT JOIN Student A on D.SID=A.SID
Copy the codeSelect student, course name and grade from student who failed the course
SELECT C.Sname,B.Cname,A.score FROM SC A
JOIN Course B ON A.CID=B.CID
JOIN Student C ON A.SID=C.SID
WHERE A.score<60
Copy the codeSelect * from student where id = 01 and score > 80; select * from student where id = 01 and score > 80
SELECT A.SID,B.Sname
FROM
(SELECT * FROM SC
WHERE score>80 and CID='01'
) A
LEFT JOIN Student B on A.SID=B.SID
Copy the code38, Find the number of students in each course (assuming that every student has taken an exam and has a score)
SELECT CID,COUNT(*) number of students FROM SC GROUP BY CIDCopy the codeResults do not repeat, query elective “Zhang SAN” teacher taught the course of students, the highest student information and results
SELECT TOP 1 * FROM SC
WHERE CID=
(SELECT CID
FROM Course
WHERE TID=
(SELECT TID FROM Teacher
WHERE Tname='Joe'
)
)
ORDER BY score DESC
Copy the code40, in the case of repeated results, query the students who take the course taught by the teacher “Zhang SAN”, the highest score of the student information and results
SELECT * FROM
( SELECT *,DENSE_RANK()OVER(ORDER BY score DESC) A
FROM SC
WHERE CID=
(
SELECT CID FROM Course
WHERE TID=
(SELECT TID FROM Teacher
WHERE Tname='Joe'
)
)
)B
WHERE B.A=1
Copy the codeQuery the first two best results of each door
SELECT * FROM
(SELECT *,ROW_NUMBER()OVER (PARTITION BY CID ORDER BY score DESC)A
FROM SC
)B
WHERE B.A<3
Copy the code42, statistics of the number of students enrolled in each course (more than 5 people of the course statistics), the output of the course number and the number of electives, query results in descending order according to the number of people, if the number of the same, according to the course number ascending order
SELECT COUNT(SID) FROM SC GROUP BY CID HAVING COUNT(SID)> SELECT COUNT(SID) FROM SC GROUP BY CID HAVING COUNT(SID)>5ORDER BY number of electives DESC,CIDCopy the codeRetrieve student IDS for at least two courses
SELECT SID FROM SC
GROUP BY SID
HAVING COUNT(CID)>=2
Copy the codeSelect * from student where all courses are selected
SELECT SID FROM SC
GROUP BY SID
HAVING COUNT(CID)=
(SELECT DISTINCT COUNT(1) a
FROM Course)
Copy the codeQuery the age of each student, only by year
SELECT SID,DATEDIFF(Year,Sage,GETDATE()) age FROM StudentCopy the code46. In terms of the date of birth, if the current month date is less than the date of birth, the age is reduced by one year
SELECT *,
(CASE WHEN
CONVERT(INT,'1'+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5.8))
< CONVERT(int.'1'+SUBSTRING(CONVERT(VARCHAR(10),GETDATE(),112),5.8))
THEN DATEDIFF(YY,Sage,GETDATE())
ELSE DATEDIFF(YY,Sage,GETDATE())- 1
END
)age
FROM Student
Copy the codeQuery this week’s birthday students
SELECT *,(
CASE WHEN DATENAME(wk,CONVERT
(DATETIME,
(CONVERT(VARCHAR(10),YEAR(GETDATE()))
+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5.8)
)
)
)=DATENAME(WK,GETDATE())
THEN 1 ELSE 0END) birthday reminder FROM StudentCopy the codeQuery next week’s birthday students
SELECT *,(
CASE WHEN DATENAME(wk,CONVERT
(DATETIME,
(CONVERT(VARCHAR(10),YEAR(GETDATE()))
+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5.8)
)
)
)=DATENAME(WK,GETDATE())+1
THEN 1 ELSE 0END) birthday reminder FROM StudentCopy the codeQuery this month’s birthday students
SELECT *,(
CASE WHEN MONTH(
CONVERT(DATETIME,
(CONVERT(VARCHAR(10),YEAR(GETDATE()))
+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5.8)
)
)
)=MONTH(GETDATE())
THEN 1 ELSE 0End) birthday reminder FROM StudentCopy the codeQuery student whose birthday is next month
SELECT *,(
CASE WHEN MONTH(
CONVERT(DATETIME,
(CONVERT(VARCHAR(10),YEAR(GETDATE()))
+SUBSTRING(CONVERT(VARCHAR(10),Sage,112),5.8)
)
)
)=MONTH(GETDATE())+1
then 1 else 0End) birthday reminder FROM StudentCopy the codeThat’s all for 50 classic SQL interview questions. If you have any questions, leave a comment below.
— the End —
“`php
Background reply keyword: 1024, get a carefully organized technical dry goods background reply keyword: into the group, take you into the master like clouds of communication group. Recommended reading
* [SQL Server commonly used function arrangement] (https://blog.csdn.net/liyue071714118/article/details/106740772) * [sad! Even the major programming languages were set up stalls!] (http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59586bfd21c90bf46d69cf57a5144f9ba416401b22b5a5b8d23cfa6 afd4f7ddc387e6960a\&idx=1\&mid=2457307058\&scene=21\&sn=e58715bff688e60a4797b8e97c2b136d#wechat_redirect) * [SELECT COUNT(\*) what is going on at the bottom? (http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59544bfd21c52809788273b193c5f3443ae3246e5f8921f3b823a4a E243433073b9d9de36 \ & independence idx = 1 \ & mid = 2457306992 \ & scene = 21 \ & sn = # e47b3a106c302bed4660cebb7542342d wechat_redirect) * [SQL queries, Are you still in SELECT \*? (http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59566bfd21c70746026c7edf3a98438c9f9a8f13d5cb999c193fa8d 19 e966c332f4b4cbaa \ \ & independence idx = 2 & mid = 2457306962 \ & scene = 21 \ & sn = 7 # c6cec1ecd753486de61c763f40e549a wechat_redirect) * * this is a number of the public can learn technology , welcome to **! [] (https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/659bbcbc43b64015837a02b437261127~tplv-k3u1fbpfcp-zoom-1.image), click on the * * Read ** to learn about SQL Boot CampCopy the code