Basic SQL Interview Questions for Freshers
Here are some interview questions that can be asked of a fresher for any role.
1. What are the different types of SQL commands?
Ans: SQL commands can be categorized into several types based on their functionality. They are DDL, DML, DCL, and TCL.
DDL (Data Definition Language): These commands are used to define and modify database structures, such as tables and indexes.
Commands: CREATE, ALTER, DROP
DML (Data Manipulation Language): These commands are used to manipulate the data within the database
Commands: SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): These commands are used to control access to data within the database.
Commands: GRANT, REVOKE
TCL (Transaction Control Language): These commands are used to manage transactions in the database.
Commands: COMMIT, Roll Back, Save Point
2. Explain the differences between grouped and ordered in SQL.
Ans: GROUP BY: It is used to group rows that have the same values into summary rows. Group By is typically used with aggregate functions like COUNT, SUM, AVG, etc.
ORDER BY: It is used to sort the result set in ascending or descending order based on one or more columns. Order By is used for sorting the result set.
3. How do you find the nth Highest salary from a table in SQL ?
Ans: We can use the LIMIT clause in combination with the ORDER BY clause to find the nth Highest salary.
Example: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT n-1,1;
Note: We will replace “n” with the desired rank of the salary.
4. How can you find the second highest salary in a table without using the LIMIT clause?
Ans: We can use a subquery to find the maximum salary that is less than the overall maximum salary.
Example: SELECT MAX(salary) FROM employees WHERE salary<(SELECT MAX(salary) FROM employees);
5. Explain the differences between DELETE and TRUNCATE commands in SQL ?
Ans: DELETE: This command is used to delete rows from a table based on a condition. It is a DML operation. Each row deletion is logged individually, and you can use a WHERE clause to specify which rows to delete. It can be rolled back if used within a Transaction.
Example: DELETE FROM employees WHERE dept_id = 10;
TRUNCATE: This command is used to delete all rows from a table. It is a DDL operation. It is faster than DELETE because it deallocates the data pages rather than logging individual row deletions. It cannot be rolled back in some databases because it does not log individual row deletions.
Example: TRUNCATE TABLE employees;
DELETE can be more Flexible when you need to remove specific rows, while TRUNCATE is more efficient for removing all rows.
6. What are indexes in SQL, and why are they used?
Ans: Indexes are database objects created on tables and views that improve the speed of data retrieval
operations. They work by providing a fast way to look up rows based on the values of one or more
columns.
Types of indexes:
Clustered Index: Alters the physical order of the table and is limited to one per table. The table data
is sorted according to the clustered index.
Non-Clustered Index: Does not alter the physical order of the table. It creates a separate object
within the table that points back to the original table rows.
Example:
CREATE INDEX idx_employee_name ON employees(name);
Indexes can significantly speed up data retrieval, they can also slow down data modification
operations (INSERT, UPDATE, DELETE) because the indexes need to be maintained.
7. What is the difference between a primary key and a unique key in SQL?
Ans: Primary Key:
Uniquely identifies each record in a table.
Cannot contain NULL values.
A table can have only one primary key.
Often automatically creates a clustered index on the column(s).
Unique Key:
Ensures that all values in a column or a set of columns are unique across the table.
can contain NULL values, but each NULL is considered unique.
A table can have multiple unique keys.
– Often creates a non-clustered index on the column(s).
Example:
CREATE TABLE employees (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE);
A primary key is a more stringent constraint than a unique key because it does not allow NULL values.
8. What are aggregate and analytic functions in SQL? Provide examples of each.
Ans: Aggregate functions perform calculations on a set of values and return a single value. Examples
include SUM(), AVG(), COUNT(), MIN(), and MAX().
Example:
SELECT SUM(SalesAmount) AS TotalSales FROM Sales;
Analytic functions compute aggregate values based on a group of rows and return multiple rows for each group. Examples include ROW_NUMBER(), RANK(), LEAD(), LAG(), and SUM() OVER().
Example:
SELECT OrderID, OrderDate, TotalAmount, SUM(TotalAmount) OVER(PARTITION BY
OrderDate) AS TotalAmountPerDay FROM Orders;
Aggregate functions reduce multiple rows to a single value, while analytic functions operate on
groups of rows but return multiple rows.
9. What are window functions in SQL, and how are they different from aggregate functions? Provide an example.
Ans: Window functions perform calculations across a set of table rows that are related to the current
row. They are different from aggregate functions in which they do not cause rows to be grouped into a single
output row. Instead, they retain the individual rows.
Key Differences:
1. Window Functions:
Perform calculations across a set of table rows related to the current row.
Do not group rows into a single output row, allowing you to retain the detail of each row.
– Require an OVER() clause which defines the window or the subset of rows to perform the
calculation on.
2. Aggregate Functions:
Perform calculations on a set of rows and return a single value.
Typically used with GROUP BY to group rows into a single summary row.
10. Explain the difference between CHAR and VARCHAR data types in SQL. Provide examples to illustrate their usage.
Ans: CHAR Data Type:
Fixed-Length Character Data Type: CHAR stores fixed-length strings where the length is specified
during table creation.
It pads spaces to the right of the string if the actual data is shorter than the defined length.
Suitable for columns that always contain a fixed number of characters.
Example:
CREATE TABLE employees ( employee_id IN PRIMARY KEY, first_name CHAR(50), last_name
CHAR(50));
VARCHAR Data Type:
Variable-Length Character Data Type: VARCHAR stores variable-length strings where the length
can vary up to a maximum length specified during table creation.
It does not pad spaces, saving storage space compared to CHAR.
Suitable for columns where the length of the data varies significantly.
Example:
CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(100),
description VARCHAR(255));
These are some of the basic Interview Questions that can be asked in Interview for a Fresher and as well as for Experienced professional.