SQL Interview Questions

 

Test yourself with real SQL interview questions:

Question 1

Given the table below, write a SQL query that retrieves the personal data about alumni who scored above 16 on their calculus exam.

alumni

student_id	name	surname	birth_date	faculty
344	Aishani	Lopes	1990-04-26	Med School
345	Robert	Roy	1990-03-09	Physics

evaluate

student_id	class_id	exam_date	grade
344	74	2014-06-19	17
344	87	2014-06-06	20
345	74	2014-06-19	11

curriculum

class_id	class_name	professor_id	semester
74	algebra	430	2014_summer
87	calculus	531	2014_summer
46	statistics	626	2014_winter

There can be different answers. This is one of them.

SELECT a.name, a.surname, a.birth_date, a.faculty
  FROM alumni AS a
 INNER JOIN evaluate AS e
       ON a.student_id=e.student_id
 INNER JOIN curriculum AS c
       ON e.class_id = c.class_id
 WHERE c.class_name = 'calculus' AND e.grade > 16;

Question 2

There is a beverages table. 

id	name	launch_year	fruit_punch	fizz
1	Pepsi	2006	35	medium
2	Coke	2005	40	high
3	Sprite	2010	42	low
4	Limca	2008	32	Very low
5	Thumsdown	2006	38	Very high

Write a query to extract only beverages where fruit_punch is between 35 and 40 (including both ends).

There can be different answers. This is one of them.

SELECT *
  FROM beverages
 WHERE fruit_punch BETWEEN 35 AND 40;

Question 3

Let's work with the beverages table again. 

id	name	launch_year	fruit_punch	fizz
1	Pepsi	2006	35	medium
2	Coke	2005	40	high
3	Sprite	2010	42	low
4	Limca	2008	32	Very low
5	Thumsdown	2006	38	Very high

Write a query to extract only beverages where fizz is high or very high

SELECT *
  FROM beverages
 WHERE fizz LIKE '%high%';

Question 4

Take a look at the query given below:

SELECT column, AGG_FUNC(column_or_expression), …
  FROM a_table
 INNER JOIN some_table
       ON a_table.column = some_table.column
 WHERE a_condition
 GROUP BY column
HAVING some_condition
 ORDER BY column
 LIMIT 5;

In what order does SQL run the clauses? Select the correct option from the list of choices below:

1. SELECT, FROM, WHERE, GROUP BY
2. FROM, WHERE, HAVING, SELECT, LIMIT
3. SELECT, FROM, INNER JOIN, GROUP BY
4. FROM, SELECT, LIMIT, WHERE

Answer: The correct option is 2. It goes like this:

1. The SQL engine fetches the data from the tables (FROM and INNER JOIN)
2. Filters it (WHERE)
3. Aggregates the data (GROUP BY)
4. Filters the aggregated data (HAVING)
5. Selects the columns and expressions to display (SELECT)
6. Orders the remaining data (ORDER BY)
7. Limits the results (LIMIT)

Question 5

What are the differences between local and global temporary tables?

• Local temporary tables are visible when there is a connection and are deleted when the connection is closed.

CREATE TABLE #<tablename>

• Global temporary tables are visible to all users and are deleted when the connection that created it is closed.

CREATE TABLE ##<tablename>

Author: Jilpa Gambhir
Software Developer working in IT industry from last 18 yrs. Worked in C#.Net, 
Sql Server, Windows Services, WCF, WPF, Biztalk, Sharepoint.