30+ SQL Interview Questions

Q 1. What are the subsets of SQL? Explain them. 

The following are the three subsets of SQL: 

• Data Definition Language (DDL) – It allows end-users to CREATE, ALTER, and DELETE database objects.

• Data Manipulation Language (DML) – With this, you can access and manipulate data. It allows you to Insert, Update, Delete, and Retrieve data from the database.

• Data Control Language (DCL) – This lets you control access to the database. It includes the Grant and Revoke permissions to manipulate or modify the database.

Q 2. What is the primary key?

1. A primary key constraint uniquely identifies each row/record in a database table. 

2. Primary keys must contain unique values.

3. Null value and duplicate values are not allowed to be entered in the primary key column.

4. A table can have only one primary key. It can consist of single or multiple fields.

Q 3. What is a foreign key?

A foreign key (referencing key) is used to link two tables together. It is a column or a combination of columns whose values match a Primary Key in a different table. 

It acts as a cross-reference between tables because it references the primary key of another table and established a link between them.

Q 4. Explain the different types of indexes in SQL.

There are three types of indexes in SQL:

1. Unique Index – It does not allow a field to have duplicate values if the column is unique indexed.

2. Clustered Index – This index defines the order in which data is physically stored in a table. It reorders the physical order of the table and searches based on key values. There can be only one clustered index per table.

3. Non-Clustered Index – It does not sort the physical order of the table and maintains a logical order of the data. Each table can have more than one non-clustered index.

Q 5. What is the difference between TRUNCATE and DELETE?

This is one of the most commonly asked SQL interview questions.

Q 6. What is the difference between below cmds.

SELECT * FROM MyTable WHERE MyColumn <> NULL

SELECT * FROM MyTable WHERE MyColumn IS NULL

The first syntax will not work because NULL means ‘no value’, and you cannot use scalar value operators. This is why there is a separate IS – a NULL predicate in SQL.

Q 7. Can you name different types of MySQL commands?

SQL commands are divided into the following categories –

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Transaction Control Language (TCL)

Q 8. Explain different DDL commands in MySQL.

DDL commands include –

• CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.

• DROP – Used to delete objects

• ALTER – Used to change database structures

• TRUNCATE – It erases all records from a table, excluding its database structure

• COMMENT – Used to add comments to the data dictionary

• RENAME – Used to rename a database object

Q 9. Explain different DML commands in MySQL.

This is one of the most popularly asked SQL interview questions.

DML commands include –

• SELECT – Used to select specific database data

• INSERT – Used to insert new records into a table

• UPDATE – It helps in updating existing records

• DELETE – Used to delete existing records from a table

• MERGE – Used to UPSERT operation (insert or update)

• CALL – It is used when you need to call a PL/SQL or Java subprogram

• EXPLAIN PLAN – Used to interpret data access path

• LOCK TABLE – Used to control concurrency

Q 10. Explain different DCL commands in MySQL.

DCL commands are –

• GRANT – It provides user access privileges to the database

• DENY – Used to deny permissions to users

• REVOKE – Used to withdraw user access by using the GRANT command

Q 11. Explain different TCL commands in MySQL.

DCL commands include –

• COMMIT – Used to commit a transaction

• ROLLBACK – Used to roll back a transaction

• SAVEPOINT – Used to roll back the transaction within groups

• SET TRANSACTION – Used to specify transaction characteristics

Q 12. What are the different types of Database relationships in MySQL?

There are three types of Database Relationship –

• One-to-one – Both tables can have only one record

• One-to-many – The single record in the first table can be related to one or more records in the second table

• Many-to-many – Each record in both the tables can be related to any number of records

Q 13. What is Normalization?

Normalization is a database design technique to organize tables to reduce data redundancy and data dependency.

Q 14. What are the different types of Normalization?

There are six 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)

Q 15. How many TRIGGERS are allowed in the MySQL table?

Ans: 6 triggers are allowed in the MySQL table.

• BEFORE INSERT

• AFTER INSERT

• BEFORE UPDATE

• AFTER UPDATE

• BEFORE DELETE 

• AFTER DELETE

Q 16. What is a constraint, and how many levels of constraints are there?

Constraints are the representation of a column to enforce data entity and consistency. 

There are two levels of constraint – 

• Column level – Limits only column data

• Table level – Limits whole table data

Following are the most used constraints that can be applied to a table:

• NOT NULL

• UNIQUE

• CHECK

• DEFAULT

• PRIMARY KEY

• FOREIGN KEY

Q 17. What is Data Integrity?

Data integrity attributes to the accuracy, completeness, and consistency of the data in a database. It also refers to the safety and security of data and is maintained by a collection of processes, rules, and standards that were implemented during the design phase. 

Three types of data integrity are:

• Column Integrity

• Entity Integrity

• Referential Integrity

Q 18. What is Referential Integrity?

Referential integrity is a relational database concept that suggests that the accuracy and consistency of data should be maintained between primary and foreign keys.

Q 19. What is the ACID property in SQL?

ACID is short for Atomicity, Consistency, Isolation, Durability. 

It ensures Data Integrity during a transaction.

Atomicity: It means either all the operations (insert, update, delete) inside a transaction take place or none.

Consistency: Consistency ensures that the data must meet all the validation rules. Irrespective of whatever happens in the middle of the transaction, Consistency property will never leave your database in a half-completed state.

Isolation: It means that every transaction is individual. One transaction can’t access the result of other transactions until the transaction is completed. 

Durability: It implies that maintaining updates of committed transactions is important. It refers to the ability of the system to recover committed transaction in case of failure.

Q 20. Explain string functions in SQL?

SQL string functions are used for string manipulation.

Following are the extensively used SQL string functions:

• UPPER(): Converts character data to upper case

• LOWER(): Converts character data to lower case

• SUBSTRING() : Extracts characters from a text field

• RTRIM(): Removes all whitespace at the end of the string

• LEN(): Returns the length of the value in a text field

• REPLACE(): Updates the content of a string.

• LTRIM(): Removes all whitespace from the beginning of the string

• CONCAT(): Concatenates function combines multiple character strings

Q 21. How can you create an SQL table from another table without copying any values from the old table?

Syntax: 

CREATE TABLE new_table

  AS (SELECT *

      FROM old_table WHERE 1=2);

This will create a new table with the same structure as the old table with no rows copied.

Q 22. Write the SQL query to update the student names by removing leading and trailing spaces.

This can be done by using ‘Update’ command with ‘LTRIM’ and ‘RTRIM’ function.

Syntax:

UPDATE StudentDetails 

SET FullName = LTRIM(RTRIM(FullName));

Q 23. Write the SQL query to fetch alternate records from a table

Records can be fetched for odd and even row numbers:

Syntax to fetch even numbers:

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0

Syntax to fetch odd numbers:

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

Q 24. How do you return a hundred books starting from the 15th?

The syntax will be: 

SELECT book_title FROM books LIMIT 15, 100. 

The first number in LIMIT is the offset, and the second is the number.

Q 25. Name the default port for the MySQL server.

The default port for the MySQL server is 3306.

Q 26. How do you implement Enums and sets internally in MySQL? 

To implement an ENUM column, use the given syntax: 

CREATE TABLE table_name ( … col ENUM (‘value1′,’value2′,’value3’), … );

Q 27. What are the different groups of data types in MySQL?

There are three groups of data types in MySQL, as listed below:

String Data Types – BINARY, VARBINARY, TINYBLOB, CHAR, NCHAR, VARCHAR, NVARCHAR, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT, ENUM, SET, MEDIUMTEXT.

Numeric Data Types – MEDIUMINT, INTEGER, BIGINT, FLOAT, BIT, TINYINT, BOOLEAN, SMALLINT, DOUBLE, REAL, DECIMAL.

Date and Time Data Types – TIMESTAMP, TIME, DATE, DATETIME, YEAR.

Q 28. What is the default storage engine in MySQL?

InnoDB is the default storage engine in MySQL.

Q 29. Explain the use of the NVL() function. 

The NVL() function converts the Null value to the other value.

Q 30. What is join in SQL?

Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

• INNER JOIN − Returns rows when there is a match in both tables.

• LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

• RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

• FULL OUTER JOIN − Returns rows when there is a match in one of the tables.

• SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Example of Inner Join:

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

INNER JOIN table2

ON table1.commonfield = table2.commonfield;

Similarly you can apply all joins.

Q 31. Difference between DBMS and RDBMS?

RDBMS: Data is stored in table format

DBMS: Data is stored in file format

RDBMS: Multiple data elements are accessible together

DBMS: Individual access of data elements is possible

RDBMS: Data in the form of a table are linked together

DBMS: There is no connection between the data

RDBMS: It supports multiple users

DBMS: It supports single user

RDBMS: It features multiple level of security while handling data

DBMS: There is low security while handling data

RDBMS: Example: Oracle, SQL Server

DBMS: Example: XML, Microsoft Access

These questions will definitely help you in preparing for interviews.

All the Best!!!