SQL Interview Questions

Most favorite topics of the interviewer for SQL are:

1. SELECT and FROM: This is the “heart” of any SQL query – SELECT columns FROM table.
2. WHERE: This acts as a filter and allows you to select only relevant rows based on conditions.
3. GROUP BY: This helps you aggregate the data by grouping columns.
4. AVG/SUM/MAX/MIN: These are aggregate functions that help you summarize the data.
5. COUNT and DISTINCT: Count helps you find the number of entries and Distinct helps you find unique entries.
6. CASE WHEN: This is a very useful way to create a new derived field or perform complex aggregate operations.
7. JOIN: Join helps you combine data from different tables using join conditions. Please get familiar with inner and left/right/full outer joins.
8. UNION: Union and Union All help you to combine data from multiple tables with similar structure.
9. WINDOW FUNCTION: Allow you to perform calculations across a set of rows.
10. CTE and SUBQUERY: They help you create more complex queries by combining smaller queries.

Here I will discuss 6 questions for each topic that are most relevant for SQL interview purposes.

These SQL Interview Questions were asked in different interviews for data science jobs and data analyst jobs so please focus on these questions and do not forget to practice more and more to become perfect. There is no shortcut to success. So, practice more and more to get acquainted with SQL.

SELECT and FROM:

Syntax: 

• SELECT column1, column2, column3,…,columnN FROM table_name;
• SELECT * FROM table_name;

Q1. Consider a table with ID, NAME, AGE, ADDRESS, SALARY. Select ID, NAME, AGE from the table Customers.

Sol. SELECT id, name, salary FROM CUSTOMERS;

Q2. Select all the columns from the table CUSTOMERS.

Sol. SELECT * FROM CUSTOMERS;

Q3. Select unique customers from the customer table.

Sol. SELECT DISTINCT * FROM CUSTOMERS;

Q4. Select a table from a csv file.

Sol. COPY CUSTOMER_TABLE from ‘path\of\file\file_name.csv’ delimiter ‘,’ csv header;

Q5. Select Customers with age greater than 30 from the table Customers.

Sol.  SELECT * FROM CUSTOMERS WHERE AGE>30;

Q6. Select Customers with age between 20 and 40 from the table Customers.

Sol. SELECT * FROM CUSTOMERS WHERE AGE<40 AND AGE>20;

WHERE:

• It is used to specify a condition while fetching the data from a single table or by joining with multiple tables. 
• If the given condition is satisfied, then only it returns a specific value from the table.
• SYNTAX:

• • SELECT “column_name” FROM “table_name” WHERE “condition”;

Equal to condition:

Q1. Select first_name from customer_table where age=25.

Sol. SELECT first_name FROM customer_table WHERE age=25;

Less than/Greater than condition:

Q2. Select first_name and age from customer where age is more than 25.

Sol. SELECT first_name FROM customer_table WHERE age>25;

Q3. Select first_name and age from customer where age is less than 25.

Sol. SELECT first_name, age FROM customer_table where age<25;

Q4. Select first_name and age from customer_table where age is between 25 to 40.

Sol. SELECT first_name, age FROM customer_table where age<40 and age>20;

Matching text condition:

Q5. Find the customers details whose first_name= ‘John”.

Sol. SELECT * FROM customer_table WHERE first_name=”John”;

 

BETWEEN:

• The BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.
• SYNTAX:

• • SELECT “column_name” FROM “table_name” WHERE “column_name” BETWEEN ‘lower_value1’ AND ‘upper_value2’;

Q1. Select the customer details whose age is between 20 and 30.

Sol1: SELECT * FROM customer WHERE age BETWEEN 20 AND 30;

Sol2: SELECT * FROM customer WHERE age>=20 AND age<=30;

Q2. Select the customer details whose age is not between 20 and 30.

Sol. SELECT * FROM customer WHERE age NOT BETWEEN 20 AND 30;

Q3. Select the sales details where the ship_date is between ‘2015-04-01’ and ‘2016-04-01’

Sol:  SELECT * FROM sales WHERE ship_date BETWEEN ‘2015-04-01’ AND ‘2016-04-01’;

 

IMPORTANT INTERVIEW QUESTIONS:

Q1. find the nth highest salary from a table.

select * from

select ename, sal, dense_rank()

over(order by sal desc)r from Employee)

where r=&n;

To find to the 2nd highest sal set n = 2

To find 3rd highest sal set n = 3 and so on.

Q2. top 4 salary from the table.

select * from employee where salary=

select min(sal) from employee

where sal in

select distinct top n=4

salary from employee

order by salary desc

Q3. To find the nth highest salary.

SELECT ename, sal FROM emoloyee e1 WHERE

N-1 = (select count(distinct sal) from employee e2

where e2.sal> e1.sal)

Q4. Write an sql query to fetch only the even rows from the table.

Sol.

• if we have autoincrement field

SELECT * FROM employeedetails

WHERE MOD(empid,2)=0;

• if we do not have auto increment field

SELECT e.empid, e.project, e.salary

FROM (

SELECT *, ROW_NUMBER() OVER(ORDER BY empid) AS rownumber

FROM employeesalary

) e

WHERE e.rownumber %2=0;

Q5. write an sql query to fetch records that are present in one table but not in another table.

Sol.

SELECT employeesalary.* FROM employeesalary

LEFT JOIN

managersalary USING (empid) WHERE managersalary.empid IS NULL;

Q6. DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE.

WHERE	HAVING
WHERE is used in the selection of rows according to the given condition	HAVING is used in column operations and is applied to aggregate rows or groups
If GROUPBY is used then it is executed after the WHERE clause is executed. It means it selects the rows before grouping is done or aggregate calculations are performed.	GROUPBY is executed before the execution of the HAVING clause. It means it selects the rows after aggregate calculations are performed.
WHERE clause can be used with SELECT, UPDATE, DELETE, etc.	We cannot use HAVING clause without SELECT statement
Aggregate functions can never be used with WHERE clause.	We can use aggregate functions like SUM, MIN, MAX, AVG, etc, with the HAVING clause

Q7. DIFFERENCE BETWEEN DELETE AND TRUNCATE.

DELETE	TRUNCATE
DELETE command is used to delete a row in a table	TRUNCATE is used to delete all the rows from a table.
You can rollback data after using DELETE statement	You cannot rollback data
It is a DML (Data Manipulation Language) Command	It is a DDL (Data Definition Language) command
It is slower than TRUNCATE	It is faster

Q8. WHAT ARE THE DIFFERENT SUBSET OF SQL.

1. DDL(Data Definition Language): Consist of the commands that can be used to define the database schema.
2. DML(Data Manipulation Language): consist of commands that deal with the manipulation of data present in the database.
3. DCL(Data Control Language): Includes commands which deals with the rights, permissions and other controls of the database system
4. TCL(Transaction control Language): Includes the commands which mainly deal with the transaction of the database.

Q9. What is the sequence of operation of SQL queries.

FROM

JOIN

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

WINDOW FUNCTIONS:

1. The RANK() function
2. The DENSE_RANK() function
3. THE ROW_NUMBER() function
4. THE LEAD() function
5. THE LAG() function
6. THE RUNNING TOTAL

Q1. Query to display the columns name, category, and ranking_score. It also ranks the rows using the RANK() function in the order defined by the column ranking_score.

Sol.

select rank() over(order by ranking_score) as rank_number,

name, category, ranking_score

from product;

1. DENSE_RANK()

• DENSE_RANK() is similar to RANK(), but it does not allow gaps in the way RANK() does.

select dense_rank() over(order by ranking_score DESC) as dense_rank_number, name, category, ranking_score

from product;

2. ROW_NUMBER()

• Another popular ranking function used in databases is ROW_NUMBER().
• It simply assigns consecutive numbers to each row in a specific order.

select row_number() over(order by ranking_score) as row_number,

name, category, ranking_score

from product;

3. Ranking within partition

• You can assign ranks separately within each partition with RANK().DENSE_RANK(),ROW_NUMBER()
• This is done by dividing row into partitions by a column or a combination of columns then assigning ranks independently within each partition.

SELECT RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number, name, category, ranking_score

FROM product;

SELECT DENSE_RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number,

name, category, ranking_score

FROM product;

SELECT ROW_NUMBER() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number,

name, category, ranking_score

FROM product;

4. LEAD()

SELECT toy_name, month, sale_value, LEAD(sale_value) OVER(PARTITION BY toy_name ORDER BY month)

AS next_month_value

FROM toys_sale;

5. LAG()

SELECT toy_name, month,sale_value, LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS prev_month_value,

LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month)- sale_value AS difference FROM toys_sale;

If you want to get latest news then you can visit this site parishiltonblog.org and you can also check here newshubpages.com. Click here newsvibes.net for getting most popular news.

6. RUNNING TOTAL

SELECT toy_name, month, sale_value,

SUM(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS total_toy_value

FROM toys_sale;

SELECT toy_name, month, sale_value,

SUM(sale_value) OVER(ORDER BY sale_value) AS total_toy_value

FROM toys_sale;

This brings us to the end of the blog on SQL Interview Questions. These are the most commonly asked SQL interview questions in various interviews for different job roles as well. Learning these will surely help you land your dream job. All the best!