Most favorite topics of the interviewer for SQL are:
- SELECT and FROM: This is the “heart” of any SQL query – SELECT columns FROM table.
- WHERE: This acts as a filter and allows you to select only relevant rows based on conditions.
- GROUP BY: This helps you aggregate the data by grouping columns.
- AVG/SUM/MAX/MIN: These are aggregate functions that help you summarize the data.
- COUNT and DISTINCT: Count helps you find the number of entries and Distinct helps you find unique entries.
- CASE WHEN: This is a very useful way to create a new derived field or perform complex aggregate operations.
- JOIN: Join helps you combine data from different tables using join conditions. Please get familiar with inner and left/right/full outer joins.
- UNION: Union and Union All help you to combine data from multiple tables with similar structure.
- WINDOW FUNCTION: Allow you to perform calculations across a set of rows.
- 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.
- DDL(Data Definition Language): Consist of the commands that can be used to define the database schema.
- DML(Data Manipulation Language): consist of commands that deal with the manipulation of data present in the database.
- DCL(Data Control Language): Includes commands which deals with the rights, permissions and other controls of the database system
- 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:
- The RANK() function
- The DENSE_RANK() function
- THE ROW_NUMBER() function
- THE LEAD() function
- THE LAG() function
- 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!
To know more Information: meetyou
Read more about this website: chatrad
You should visit this site: newsinsightz