Preparing for SQL interview or exam is easy if you have good resources. In this post you can find frequently asked interview questions and answers with examples for SQL (tested on MySQL). This post will be helpful for students, junior DBAs, business analysts, programmers who want to refresh or enrich their SQL knowledge.
The examples will be divided in several sections:
- theoretical questions
- practical questions
- SQL Advanced problems
- DB design problems
- SQL Online tests
- SQL Resources and tutorials
Usually theoretical questions about SQL are 20 - 30 % of the SQL exam or interview. Some of them are very easy and can be answered intuitively. Some of the questions could be tricky or misleading. Example questions:
What is the difference between UNION and UNION ALL?
The difference between UNION and UNION ALL is that UNION ALL will include duplicate records while UNION will omit duplicate records. It is important to note that the performance of UNION ALL will typically be better than UNION, due to additional work of removing any duplicates.
What is DDL?
Data Definition Language (DDL) statements are used to define the database structure or schema. Operation included are: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
What is DML?
Data Manipulation Language (DML) statements are used for managing data within schema objects. Operation included are: SELECT, INSERT, DELETE, MERGE
Which SQL statement is used to delete data from a database?
Which SQL statement is used to return only different values?
- SELECT DISTINCT
- SELECT DIFFERENT
- SELECT UNIQUE
What is the difference between the WHERE and HAVING clauses?
WHERE clause can be used with - Select,Insert and Update statements, Where as HAVING Can be used only with select statement
Where filters rows before aggregation(GROUPING),Where as,HAVING filter groups,after aggregations are performed.).
SQL Practical questions
Practical questions usually require more time and focus. You need to read the question two times to be sure about the problem. Examples:
With SQL, how can you return all name from a table named "Companies" sorted descending by "CompanyName"?
SELECT * FROM Companies ORDER BY CompanyName DESC SELECT * FROM Companies SORT BY 'CompanyName' DESC SELECT * FROM Companies ORDER CompanyName DESC SELECT * FROM Companies SORT 'CompanyName' DESC
How can you change "Google" to "Alphabet" in the "CompanyName" column in the Companies table?
MODIFY Companies SET LastName='Alphabet' WHERE CompanyName='Google' UPDATE Companies SET LastName='Google' INTO CompanyName='Alphabet' MODIFY Companies SET LastName='Google' INTO CompanyName='Alphabet UPDATE Companies SET LastName='Alphabet' WHERE CompanyName='Google'
Swap true and false records (table Companies and column public) in a table with single query? ( Consider that all values are True or False)
UPDATE Companies SET public = CASE public WHEN TRUE THEN FALSE ELSE TRUE END
Get companies from Companies table whose income is between 10000 and 800000000
Select * from Companies where income between 10000 and 800000000
Select CompanyName from Companies table if data exists in Customer table ? ( Two tables are related by: Companies - Customer : customer_id = cus.id
select CompanyName from Companies com where exists (select * from Customer cus where com.customer_id = cus.id)
SQL Advanced Problems
Find the busy and the free rooms (as date intervals) from this table for the period of '2018-03-06' and '2018-03-14'?
- Find busy rooms
SELECT * from room WHERE bookIn >= '2018-03-06' AND bookOut <= '2018-03-14'
- Find free rooms
SELECT * FROM room r WHERE NOT EXISTS ( SELECT * FROM room rm WHERE bookIn >= '2018-03-06' AND bookOut <= '2018-03-14' AND r.id = rm.id)
Explanation: for given two periods:
[StartDate and EndDate], [ReservationStart and ReservationDate]
The periods are not covering if:
StartDate > ReservationDate OR
ReservationStart > EndDate
The cover of the periods would be:
StartDate <= ReservationDate AND
ReservationStart <= EndDate
DB design problems
Designing a student and Teacher DB as follows:
DB for student and teacher following these rules:
- Student can work with many teachers
- Teacher teaches many students
- Each teacher assess a student with one and only one grade
Steps to solve
- Initially you place all the information in one table
- denormalize to three
SQL Online Tests
SQL Resources and tutorials
- SQL Tutorial from w3schools - simple tutorial which contains the basics of SQL. Every person who works with DB and SQL should know the basic SQL operation and how to work with them. Advantage of the course is that is divided in sections, contains examples and you can test your SQL queries online.
- Youtube tutorial - SQL tutorial in video format for beginners
- MySQL useful tips and reference project - a collection of useful and tricky questions related to SQL and MySQL. This one is attempt to collect all important queries of SQL in a single project.