Today I participated in the preliminary exam of the school's Sodi Cup, and when I saw the Japanese on the exam paper, I was confused. Do we have to take this exam in Japanese? It's like the American GRE exam requiring knowledge of "The Analects". It's really crazy!! However, there are two classic SQL questions on the exam paper that are worth sharing with everyone, especially for beginners learning SQL. It seems that I'm just a beginner myself, so let's take a look at the questions.
Question 1: Use SQL to determine whether this year or a specific year is a leap year. Explanation: SQL? Leap year? They don't seem to have much to do with each other, just like taking an exam in Japanese. I really admire the examiners. However, friends who have used SQL Server 2000 in detail must know the function NOW(). That's what I thought at first too, but NOW() contains too much useless information and is difficult to handle. So I thought of another function, GETDATE(), which is currently available in MSSQL2000, but I'm not sure about others. You can refer to the user manual. GETDATE() returns today's date.
For example:
SELECT GETDATE() AS DATE
The result is:
Dec 18 2008 22:20PM
Including the date and time. Okay, so we need this year.
I will also introduce another function, DATEPART(), which is used to extract specific data using a specific index. I currently know that year and month are available. For specific usage, please refer to the user manual.
The usage is as follows:
DATEPART(month, GETDATE())
So how do we determine if a year is a leap year? If the year is divisible by 4 and not a multiple of 100, it is a leap year. If it is a multiple of 100, then it needs to be divided by 400.
So we can determine it like this:
DECLARE @year INT
IF (@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR (@YEAR % 400 = 0)
SELECT 'YES'
ELSE
SELECT 'NO'
Therefore, the SQL statement is as follows:
DECLARE @year INT
SET @year = DATEPART(month, GETDATE())
IF (@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR (@YEAR % 400 = 0)
SELECT 'YES'
ELSE
SELECT 'NO'
Question 2: There is a table of student course grades (SCG) which includes columns for student ID (SID, int), course ID (CID, int), and grade (grade, int). Find the top two grades for each subject among these students, sorted by student ID, without considering tied grades.
Analysis: The main task is to find the TOP 2 grades. Additionally, we need to analyze each course separately because there is more than one grade for each course. Therefore, multiple nested queries are required, which may have lower efficiency. If anyone has better ideas, please feel free to discuss.
The SQL statement is as follows:
SELECT t.SID AS StudentID, t.CID AS CourseID, t.grade AS Score
FROM SCG AS t
WHERE grade IN
(
SELECT TOP 2 grade
FROM SCG
WHERE t.CID = SCG.CID
ORDER BY grade DESC
)
ORDER BY SID
*Note: After being reminded by a user named ' 汉子 ', I have made some modifications to the statement.