SQL Interview Questions
What is SQL?
SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational databases.
What is a database?
A database is an organized collection of data storedand accessed electronically. It provides a way to store, organize, and retrieve large amounts of data efficiently.
What is a primary key?
A primary key is a column or combination of columns that uniquely identifies each row in a table. It enforces the entity integrity rule in a relational database.
What is a foreign key?
A foreign key is a column or combination of columns that establishes a link between data in two tables. It ensures referential integrity by enforcing relationships between tables.
What is the difference between a primary keyand a unique key?
A primary key is used to uniquely identify a row in atable and must have a unique value. On the otherhand, a unique key ensures that a column or combination of columns has a unique value but doesnot necessarily identify the row.
What is normalization?
Normalization is the process of organizing data in adatabase to minimize redundancy and dependency. It involves breaking down a table into smaller tables and establishing relationships between them.
What are the different types of normalization?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Fourth Normal Form (4NF)
- Fifth Normal Form (5NF) or Project-Join Normal Form (PJNF)
What is a join in SQL?
A join is an operation used to combine rows from two or more tables based on related columns. It allows you to retrieve data from multiple tables simultaneously.
What is the difference between DELETE and TRUNCATE in SQL?
TRUNCATE | DELETE |
---|---|
TRUNCATE, on the other hand, is used to remove allrows from a table. It cannot be rolled back, and it isfaster than DELETE as it deallocates the data pages instead of logging individual row deletions. | The DELETE statement is used to remove specific rows from a table based on a condition. It can be rolled back and generates individual delete operations for each row. |
What is the difference between UNION and UNION ALL?
UNION and UNION ALL are used to combine the result sets of two or more SELECT statements.
- UNION removes duplicate rows from the combined result set.
- Whereas UNION ALL includes all rows, including duplicates.
What is the difference between the HAVING clause and the WHERE clause?
- The WHERE clause is used to filter rows based on a condition before the data is grouped or aggregated.It operates on individual rows.
- The HAVING clause, on the other hand, is used to filter grouped rows based on a condition after the data is grouped or aggregated using the GROUP BY clause.
What is a transaction in SQL?
A transaction is a sequence of SQL statements that are executed as a single logical unit of work. It ensures data consistency and integrity by either committing all changes or rolling them back if an error occurs.
What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order of data in a table. It changes the way the data is stored on disk and can be created on only one column. A table can have only one clustered index.
A non-clustered index does not affect the physical order of data in a table. It is stored separately and contains a pointer to the actual data. A table can have multiple non-clustered indexes.
What is ACID in the context of database transactions?
ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee reliable processing of database transactions.
- Atomicity ensures that a transaction is treated asa single unit of work, either all or none of the changes are applied.
- Consistency ensures that a transaction brings the database from one valid state to another.
- Isolation ensures that concurrent transactions do not interfere with each other.
- Durability ensures that once a transaction iscommitted, its changes are permanent and survive system failures.
What is a deadlock?
A deadlock occurs when two or more transactions are waiting for each other to release resources, resulting in a circular dependency. As a result, none of the transactions can proceed, and the system may become unresponsive.
What is the difference between a databaseand a schema?
A database is a container that holds multiple objects, such as tables, views, indexes, and procedures. It represents a logical grouping of related data.
A schema, on the other hand, is a container within a database that holds objects and defines their ownership. It provides a way to organize and manage database objects.
What is the difference between a temporary table and a table variable?
A temporary table is a table that is created and exists only for the duration of a session or a transaction. It can be explicitly dropped or is automatically dropped when the session or transaction ends.
A table variable is a variable that can store a table-like structure in memory. It has a limited scope within a batch, stored procedure, or function. It is automatically deallocated when the scope ends.
What is the purpose of the GROUP BY clause?
The GROUP BY clause is used to group rows based on one or more columns in a table. It is typically used in conjunction with aggregate functions, such as SUM, AVG, COUNT, etc., to perform calculations on grouped data.
What is the difference between CHAR and VARCHAR data types?
CHAR is a fixed-length string data type, while VARCHAR is a variable-length string data type.
What is a stored procedure?
A stored procedure is a set of SQL statements that are stored in the database and can be executed repeatedly. It provides code reusability and better performance.
What is a subquery?
A subquery is a query nested inside another query. It is used to retrieve data based on the result of an inner query.
What is a view?
A view is a virtual table based on the result of an SQL statement. It allows users to retrieve and manipulate data as if
What is the difference between a cross join and an inner join?
A cross join (Cartesian product) returns the combination of all rows from two or more tables.
An inner join returns only the matching rows based on a join condition.
What is the purpose of the COMMIT statement?
The COMMIT statement is used to save changes made in a transaction permanently. It ends the transaction and makes the changes visible to other users.
What is the purpose of the ROLLBACK statement?
The ROLLBACK statement is used to undo changes made in a transaction. It reverts the database to its previous state before the transaction started.
What is the purpose of the NULL value in SQL?
NULL represents the absence of a value or unknown value. It is different from zero or an empty string and requires special handling in SQL queries.
What is the difference between a view and a materialized view?
A materialized view is a physical copy of the view's result set stored in the database, which is updated periodically. It improves query performance at the cost of data freshness.
What is a correlated subquery?
A correlated subquery is a subquery that refers to a column from the outer query. It executes once for each row processed by the outer query.
What is the purpose of the DISTINCT keyword?
The DISTINCT keyword is used to retrieve unique values from a column or combination of columns in a SELECT statement.
What is the difference between the CHAR and VARCHAR data types?
CHAR stores fixed-length character strings, while VARCHAR stores variable-length character strings. The storage size of CHAR is constant, while VARCHAR adjusts dynamically.
What is the difference between the IN and EXISTS operators?
The IN operator checks for a value within a set of values or the result of a subquery. The EXISTS operator checks for the existence of rows returned by a subquery.
What is the purpose of the TRIGGER statement?
The TRIGGER statement is used to associate a set of SQL statements with a specific event in the database. It is executed automatically when the event occurs
What is the difference between a unique constraint and a unique index?
A unique constraint ensures the uniqueness of values in one or more columns, while a unique index enforces the uniqueness and also improves query performance.
What is the purpose of the TOP or LIMIT clause?
The TOP (in SQL Server) or LIMIT (in MySQL) clause is used to limit the number of rows returned by a query. It is often used with an ORDER BY clause.
What is the difference between the UNION and JOIN operators?
UNION combines the result sets of two or more SELECT statements vertically, while JOIN combines columns from two or more tables horizontally based on a join condition.
What is a data warehouse?
A data warehouse is a large, centralized repository that stores and manages data from various sources. It is designed for efficient reporting, analysis, and business intelligence purposes.
What is the difference between a primary keyand a candidate key?
A primary key is a chosen candidate key that uniquely identifies a row in a table.
A candidate key is a set of one or more columns that could potentially become the primary key.
What is the purpose of the GRANT statement?
The GRANT statement is used to grant specific permissions or privileges to users or roles in adatabase.
What is a correlated update?
A correlated update is an update statement that refers to a column from the same table in a subquery. It updates values based on the result of the subquery for each row.
What is the purpose of the CASE statement?
The CASE statement is used to perform conditional logic in SQL queries. It allows you to return different values based on specified conditions.
What is the purpose of the COALESCE function?
The COALESCE function returns the first non-null expression from a list of expressions. It is often used to handle null values effectively.
What is the purpose of the ROW_NUMBER() function?
The ROW_NUMBER() function assigns a unique incremental number to each row in the result set.
It is commonly used for pagination or ranking purposes.ll values effectively.
ROW_NUMBER()
OVER(
[PARTITION BY VALUE_EXPERSSION, ..[N]]
ORDER_BY_CLAUSE
)
What is the difference between a natural join and an inner join?
A natural join is an inner join that matches rows based on columns with the same name in the joined tables. It is automatically determined by the database.
What is the purpose of the CASCADE DELETE constraint?
The CASCADE DELETE constraint is used to automatically delete related rows in child tables when a row in the parent table is deleted.
What is the difference between the EXISTS and NOT EXISTS operators?
The EXISTS operator returns true if a subquery returns any rows, while the NOT EXISTS operator returns true if a subquery returns no rows.
What is the purpose of the CROSS APPLY operator?
The CROSS APPLY operator is used to invoke a table-valued function for each row of a table expression. It returns the combined result set.
What is a self-join?
A self-join is a join operation where a table is joined with itself. It is useful when you want to compare rows within the same table based on related columns. It requiresbined result set.
What is an ALIAS command?
ALIAS command in SQL is the name that can be given to any table or a column. This alias name can be referred in WHERE clause to identify a particular table or a column.
Why are SQL functions used?
SQL functions are used for the following purposes:
- To perform some calculations on the data
- To modify individual data items
- To manipulate the output
- To format dates and numbers
- To convert the data types
SQL is a standard language for storing, manipulating and retrieving data in databases.
What is SQL ?
- SQL Stands for Structured query language.
- SQL let you access and manipulate database.
- SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
- SQL can execute queries against a database.
- SQL can retrieve data from a database
- SQL can insert, update, delete records in a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Look at the "EMPLOYEES" table:
SELECT * FROM EMPLOYEES
SQL Statements
Most of the actions you need to perform on a database are done with SQL statements.
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Employees" or "Salary"), and contain records (rows) with data.
Note :
- SQL keywords are NOT case sensitive: select is the same as SELECT
Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
Some of The Most Important SQL Commands
- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index
SQL SELECT Statement
The SELECT statement is used to select data from a database.
Syntax;
SELECT column1, column2, ...
FROM table_name;
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name;
Count Distinct
By using the DISTINCT keyword in a function called COUNT, we can return the number of different countries.
Syntax:
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
SQL WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SELECT emp_name, emp_dob, emp_address
FROM employee
WHERE emp_city = 'Mumbai';
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
SELECT emp_name, emp_dob, emp_address
FROM employee
WHERE emp_code = 10001;
SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
DESC
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
Order Alphabetically
For string values the ORDER BY keyword will order alphabetically:
Alphabetically DESC
To sort the table reverse alphabetically, use the DESC keyword:
ORDER BY Several Columns
The following SQL statement selects all employees from the "employees" table, sorted by the "Country" and the "emp_name" column. This means that it orders by Country, but if some rows have the same Country, it orders them by emp_name:
Using Both ASC and DESC
The following SQL statement selects all employees from the "employees" table, sorted ascending by the "Country" and descending by the "emp_name" column:
The SQL AND Operator
The WHERE clause can contain one or many AND operators.
The AND operator is used to filter records based on more than one condition, like if you want to return all employees from Spain that starts with the letter 'G':
Example:
Select all employees from Spain that starts with the letter 'G':
SELECT *
FROM employees
WHERE Country = 'Spain' AND emp_name LIKE 'G%';
AND vs OR
The AND operator displays a record if all the conditions are TRUE.
The OR operator displays a record if any of the conditions are TRUE.
All Conditions Must Be True
The following SQL statement selects all fields from employees where Country is "Germany" AND City is "Berlin" AND PostalCode is higher than 12000:
Combining AND and OR
You can combine the AND and OR operators.
The following SQL statement selects all employees from India that starts with a "G" or an "R".
Make sure you use parenthesis to get the correct result.
Example
Select all Indian employees that starts with either "G" or "R":
SELECT * FROM employees
WHERE Country = 'India' AND (employees LIKE 'G%' OR employees LIKE 'R%');
Without parenthesis, the select statement will return all employees from India that starts with a "G", plus all employees that starts with an "R", regardless of the country value:
Example
Select all employees that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Employees
WHERE Country = 'India' AND emp_name LIKE 'G%' OR emp_name LIKE 'R%';
SQL OR Operator
The WHERE clause can contain one or more OR operators.
The OR operator is used to filter records based on more than one condition, like if you want to return all employees from Odisha but also those from Delhi:
Example:
Select all employees from Odisha or Delhi:
SELECT *
FROM employees
WHERE state = 'Odisha' OR Country = 'Delhi';
OR vs AND
The OR operator displays a record if any of the conditions are TRUE.
The AND operator displays a record if all the conditions are TRUE.
At Least One Condition Must Be True
The following SQL statement selects all fields from employees where either City is "Bhubaneswar", emp_name starts with the letter "G" or Country is "Delhi":
Example
SELECT * FROM Employees
WHERE City = 'Bhubaneswar' OR emp_name LIKE 'G%' OR Country = 'India';
Combining AND and OR
You can combine the AND and OR operators.
The following SQL statement selects all Employees from Spain that starts with a "G" or an "R".
Make sure you use parenthesis to get the correct result.
Example
Select all Spanish Employees that starts with either "G" or "R":
SELECT * FROM employees
WHERE Country = 'India' AND (emp_name LIKE 'G%' OR emp_name LIKE 'R%');
Without parenthesis, the select statement will return all employees from India that starts with a "G", plus all employees that starts with an "R", regardless of the country value:
Example
Select all Employees that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Employees
WHERE Country = 'Spain' AND emp_name LIKE 'G%' OR emp_name LIKE 'R%';
SQL NOT Operator
The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
In the select statement below we want to return all employees that are NOT from Spain:
Example
Select only the employees that are NOT from India:
SELECT * FROM Customers
WHERE NOT Country = 'India';
In the example above, the NOT operator is used in combination with the = operator, but it can be used in combination with other comparison and/or logical operators. See examples below.
NOT LIKE
Example
Select employees that does not start with the letter 'A':
SELECT * FROM Employees
WHERE emp_name NOT LIKE 'A%';
NOT BETWEEN
Example
Select employees with a emp_id not between 10 and 60:
SELECT * FROM Employees
WHERE emp_id NOT BETWEEN 10 AND 60;
NOT IN
Select employees that are not from Paris or London:
SELECT * FROM employees
WHERE City NOT IN ('Paris', 'London');
NOT Greater Than
Example
Select employees with a cust_id not greater than 50:
SELECT * FROM employees
WHERE NOT emp_id > 50;
Note: There is a not-greater-then operator: !> that would give you the same result.
NOT Less Than
Example
Select employees with a emp_id not less than 50:
SELECT * FROM Employees
WHERE NOT emp_id < 50;
SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (emp_code, emp_name, Address, City, PostalCode, Country)
VALUES (10001, 'Rajesh', 'Khandagiri', 'Bhubaneswar', 'Khorda', '751001', 'India');
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "emp_name", "City", and "Country" columns (emp_id will be updated automatically):
Example
INSERT INTO employees (Name, City, Country)
VALUES ('Rajesh', 'Bhubaneswar', 'India');
Insert Multiple Rows
It is also possible to insert multiple rows in one statement.
To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:
Example
INSERT INTO Customers (empcode, name, Address, City, PostalCode, Country)
VALUES
(10001, 'Rajesh', 'Bhubaneswar', 'Khurda', '751001', 'India'),
(10002, 'Rajesh', 'Bhubaneswar', 'Khurda', '751001', 'India'),
(10003, 'Rajesh', 'Bhubaneswar', 'Khurda', '751001', 'India'),
(10004, 'Rajesh', 'Bhubaneswar', 'Khurda', '751001', 'India'),
What is a Primary Key?
The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.
A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).
What is a UNIQUE constraint?
A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.
What is a Foreign Key?
A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
The table with the foreign key constraint is labeled as the child table, and the table containing the candidate key is labeled as the referenced or parent table.
What is a Join? List its different types.
The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.
- (INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.
- LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
- RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
- FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.
What is a Self-Join?
A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.
What is a Cross-Join?
Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.
What is an Index? Explain its different types.
A database index is a data structure that provides a quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.
-
Unique and Non-Unique Index: Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.
Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently. -
Clustered and Non-Clustered Index: Clustered indexes are indexes whose order of the rows in the database corresponds to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table.
The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index.
Clustering indexes can improve the performance of most query operations because they provide a linear-access path to data stored in the database.
What is the difference between Clustered and Non-clustered index?
- Clustered index modifies the way records are stored in a database based on the indexed column. A non-clustered index creates a separate entity within the table which references the original table.
- Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower.
- SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.
What is Data Integrity?
Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
What is a Query?
A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.
What is a Subquery? What are its types?
A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively.
There are two types of subquery - Correlated and Non-Correlated.
- A correlated subquery cannot be considered as an independent query, but it can refer to the column in a table listed in the FROM of the main query.
- A non-correlated subquery can be considered as an independent query and the output of the subquery is substituted in the main query.
What are some common clauses used with SELECT query in SQL?
- WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
- ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).
- GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
- HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.