SQL Interview Questions for Data Jobs- 2023

Spread the love

SQL is the most important computer language if you want to get hired in any type of company nowadays. Without SQL it is impossible to work with big data. If your Job has any relation to data. I think it is very important to master SQL and basic database fundamentals.

Table of Contents

Here are some important questions you should be able to answer for entry-level or mid-tier jobs in 2023

Questions are in no particular order!

1 ) Name subsets of SQL

This is most commonly asked question. It tells the interviewer that you know how to handle basic database operations.

Here is that they stand for
DDL : Data definition language
DML : Data manipulation language
DCL :Data Control language
TCL : Transaction control language
DQL: Data query language

2) What is the difference between DROP, DELETE, TRUNCATE?

DROP and TRUNCATE clauses of SQl are part of the DDL subset while on the other hand DELETE clause is part of DML subset.
DROP clause is used in SQL queries to delete the table(s) and its support structure. TRUNCATE clause is used to empty the table but preserve its structure. DELETE clause is often used in queries to delete the rows which fullfil the given condition.

3) What are SQL Wildcards?

SQL wildcard characters are used with LIKE operator and WHERE clause to get desired rows from the table.

SELECT *
FROM DEMO_TABLE
WHERE NAME LIKE 'Lon%'

4) Name some SQL Date functions

DATEPART()
DATEADD()
DATEDIFF()

5) What are Temporary Tables?

Temporary table is a table that is created and used within the context of a specific session or transaction in a database management system. It is designed to store temporary data that is needed for a short duration and does not require a permanent storage solution.
Temp tables are mostly dropped after their use

6) How can we create Temperorry tables in SQL Server?

CREATE TEMPORARY TABLE TEMP_DEMO AS 
SELECT * FROM DEMO_TAB;

DROP TABLE TEMP_DEMO;

7) What are views ?

SQL views are used to create virtual tables. They can be created using one or several tables. They are helpful in increasing Database security. They are queried like normal tables. DROP keyword is used to drop existing views.

Syntax

CREATE VIEW VIEW_NAME AS
{ SQL statements}

8) What are stored procedures?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

CREATE PROCEDURE PROC_NAME
BEGINE
AS
{ SQL Statements
}
END

9) what are SQL triggers?

A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when specific table columns are updated in simple words a trigger is a collection of SQL statements with particular names that are stored in system memory.

10)What is the Difference between WHERE Clause and HAVING clause?

The major difference between WHERE and HAVING clause is where and how will you use them.
WHERE clause is basically used when we want our rows to statisfy a particular condition(s)

SELECT *
FROM DEMO_TAB
WHERE AGE = 18

HAVING clause is used when we have to use aggregate in the conditions/expression.
Example below:

SELECT *
FROM DEMO_TAB
HAVING AVG(MARKS) > 50

11) Define SQL order of Execution

Order of Execution tells us about which part of the SQL syntax is executed first.
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT .

12) define SQL UNION and what is the difference between UNION and UNION ALL

Its is used to combine result set of two or more select statements

SELECT *
FROM TAB1
UNION
SELECT *
FROM TAB2

The difference between UNION and UNION ALL is that when we use UNION ALL it prints even the repeating elements.

13) What are window functions? Name some window functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.
Few Windows Functions are RANK(), DENSE_RANK(), ROW_NUMBER(), ETC.

14) What happens when you use Delete without any expression/condition

When we use DELETE keyword without any expression/condition , it deletes all the rows since all of them satisfy the condition.