How to prepare for SQL interview in 2018   

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

Theoretical questions

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?

• REMOVE
• COLLAPSE
• DELETE

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 quiz with 25 questions from w3schools

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.