Need to practice your MS SQL Server skills for an upcoming job interview? Try solving these SQL Server interview questions that test knowledge of T-SQL concepts such as stored procedures and joins. We’ll provide feedback on your answers, and you can use a hint if you get stuck.
These SQL Server interview questions are examples of real tasks used by employers to screen job candidates such as database administrators, back-end developers, data analysts, and others that require knowledge of SQL Server in order to interface with and access Microsoft SQL Server efficiently.
1. Items List
The following table is used in an application that tracks the items its users bought:
TABLE items id INTEGER NOT NULL PRIMARY KEY name VARCHAR(30) NOT NULL dateBought DATE NOT NULL
Finish the ItemsBought procedure so that it returns only items whose dateBought is between @date and one month after @date (both inclusive). For each item it should return:
- The day of the month the item was bought [1-31].
- The number of the month the item was bought [1-12].
- The name of the item.
See the example case here for more details.
- Example case: Wrong answer
- All items are bought in a time period less than or equal to a month: Exception.
- Some items are bought in a time period greater than a month: Wrong answer
2. Parse Messages
In a company, several different programs insert rows into a messages table which should later be parsed and inserted into appropriate tables. Consider the tables:
TABLE messages id INTEGER NOT NULL PRIMARY KEY type VARCHAR(30) NOT NULL data VARCHAR(100) NOT NULL TABLE notifications id INTEGER NOT NULL PRIMARY KEY message VARCHAR(100) NOT NULL TABLE alerts id INTEGER NOT NULL PRIMARY KEY shortMessage VARCHAR(15) NOT NULL
Create a procedure called ParseMessages, in the default schema, which should do the following for all rows in the messages table:
- Insert all rows where the type is 'notification' to the notifications table with the message field being set to the data field of messages table.
- Insert all rows where the type is 'alert' to the alerts table with the shortMessage field being set to the first 15 characters of the data field of messages table.
In both cases the id field should be the same as in the messages field.
See the example case here for more details.
- Example case: Could not find stored procedure 'ParseMessages'.
- The messages table contains only notifications: Could not find stored procedure 'ParseMessages'.
- The messages table contains only alerts with less than 15 characters: Could not find stored procedure 'ParseMessages'.
- The messages table contains notifications and alerts longer than 15 characters: Wrong answer
