Top SQL Interview Questions & Answers

1. What is a Database?

A database is a structured storage space where the data is kept in many tables in an organized way so that its contents can be easily accessed, managed, and updated.

2. What is DBMS?

A database management system or DBMS is system software that can create, retrieve, update, and manage a database. The Database Management System allows a user to interact with the database. There are various types of DBMS, such as relational, hierarchical, network, graph, or object-oriented.

3. What is RDBMS?

RDMS stands for Relational Database Management System. It's the most common type of DBMS used for working with data stored in multiple tables and relations can be defined between the common fields of these tables.

4. What is SQL?

SQL stands for Structured Query Language. It is a standard programming language used to communicate with relational database management systems (RDBMS). This includes fetching, updating, inserting, and removing data from tables in the database.

5. What are SQL dialects? Give some examples.

The various versions of SQL are also called SQL dialects. Some examples are Microsoft SQL Server, PostgreSQL, MySQL, SQLite, T-SQL, Oracle, and IBM DB2.

6. What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. MySQL is the world’s most popular and widely used open-source relational database management system like SQL Server, and Oracle that is used to manage SQL databases. It is lightweight and specially designed for web applications, developed by Oracle.

7. What are the subsets of SQL?

Data definition language (DDL): It defines the data structure that consists of commands like CREATE, ALTER, DROP, etc.

Data manipulation language (DML): It is used to manipulate existing data in the database. It consists of commands like SELECT, UPDATE, INSERT, etc.

Data control language (DCL): It controls access to the data stored in the database. It consists of commands - GRANT and REVOKE.

Transaction Control Language (TCL): It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

8. What are Tables, Fields, and Records?

A Table is an organized collection of data stored in a tabular form i.e. rows and columns. Rows are horizontal and columns are vertical. The columns in a table are termed as fields and rows are termed as records.

9. What is data Integrity?

Data Integrity defines the accuracy and consistency of data stored in a database. The data stored in the database must satisfy certain types of procedures (rules) when it is entered into the application or database. DBMS provides different ways to implement such types of constraints (rules).

10. What are the Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table

11. What are the types of SQL constraints?

• DEFAULT
• CHECK
• UNIQUE
• INDEX
• NOT NULL
• PRIMARY KEY
• FOREIGN KEY

12. What is Auto Increment?

Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. The Auto Increment feature is supported by all the Databases. Mostly this keyword can be used whenever PRIMARY KEY is used.

13. What is the primary key?

A primary key is a combination of the NOT NULL and UNIQUE constraints. The primary key uniquely specifies each record of the table. Each table should contain a primary key and there can only be one primary key constraint per table. For example, the ROLL Number can be treated as the primary key for a student in a university or college.

14. What is foreign key?

A foreign key is used to create a relationship between two tables by referencing a foreign key with the primary key of another table. The purpose of foreign keys is to keep various tables of a database.

15. What is a Default constraint?

The DEFAULT constraint is used to fill a column with default and fixed values. The value will be added to all new records when no other value is provided.

16. What is the difference between a primary key and a unique key?

PRIMARY KEY

• The primary key acts as a unique identifier for each record in the table.
• We cannot store NULL values in the primary key column.
• We cannot change or delete the primary key column values.
• A table can have only one primary key.

UNIQUE KEY

• The unique key is also a unique identifier for records when the primary key is not present in the table.
• We can store a NULL value in the unique key column, but only one NULL is allowed.
• We can modify the unique key column values.
• There can be multiple unique keys on a table.

17. What is Index?

An index is simply a reference to data in a table and is used for storing its important parts and enabling faster data search and retrieval. These are useful for large databases.

18. What are the types of indexes used in SQL?

• Unique index – It doesn't allow duplicates in a table column
• Clustered index – It defines the physical order of records of a database table and performs data searching based on the key values. A table can have only one clustered index.
• Non-clustered index – It also defines the order of the table records but doesn't match the physical order of the actual data. It means that the data is stored in one place and a non-clustered index – in another one. A table can have multiple non-clustered indexes.

19. What is a Join?

SQL Joins is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.

20. What are the types of joins in SQL?

There are different types of JOINs in SQL.

• Inner Join
• Right Join
• Left Join
• Full Join
• Cross Join
• Self-Join

21. Explain the types of Joins.

• INNER Join – It returns only those records that satisfy a defined join condition in both (or all) tables. It's a default SQL join.
• LEFT Join – It returns all records from the left table and those records from the right table that satisfy a defined join condition
• RIGHT Join – It returns all records from the right table and those records from the left table that satisfy a defined join condition.
• FULL Join – It returns all records from both (or all) tables. It can be considered as a combination of left and right joins.
• Cross Join – It produces a table that merges each row from the first table with each second table row.
• Self-Join - It is used to create a table by joining itself as there were two tables. It makes temporary naming of at least one table in an SQL statement.

22. What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by organizing fields and tables of a database. This helps us in removing all the redundant data. Normalization breaks the table into small partitions by adding, deleting, or modifying fields and making a single table.

23. What are the types of Normalization?

Normal Forms are used to eliminate or reduce redundancy in database tables. There are 6 normal forms, but the first three are the main.

• 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)

24. What is the First Normal form (1NF)?

When every attribute in a relation is a single-valued attribute, it is said to be in the first normal form. This should remove all the duplicate columns from the table.

25. What is the Second Normal form (2NF)?

A relation is in second normal form if it meets the first normal form’s requirements and does not contain any partial dependencies means it has no non-prime attribute that is dependent on any suitable subset of any table candidate key. It is done by setting a single column Primary Key.

26. What is the Third Normal Form (3NF)?

If a relation meets the requirements for the second normal form and removes the columns that are not dependent on primary key constraints. Then it is said to be in the third normal form.

27. What is Denormalization?

Denormalization is a database optimization technique in which we add redundant data to one or more tables by incorporating data from the related tables. This can help us avoid costly joins in a relational database. It is an optimization technique that is applied after normalization.

Denormalization is the process opposite of normalization as it introduces data redundancy and combines data from multiple tables.

28. What is an SQL operator?

A reserved character or a keyword is used in SQL queries to perform a specific operation. SQL operators are a combination of characters that are commonly used with the WHERE clause to set a condition (or conditions) for filtering the data.

29. What are the different operators available in SQL?

• Arithmetic Operators
• Comparison Operators
• Compound Operators
• Logical Operators
• String Operators
• Set Operators

30. What are the types of set operators?

• UNION – It returns the records obtained by at least one of two queries excluding duplicates.
• UNION ALL – It returns the records obtained by at least one of two queries including duplicates.
• INTERSECT – It returns the records obtained by both queries.
• EXCEPT (called MINUS in MySQL and Oracle) – It returns only the records obtained by the first query but not the second one.

31. What is a function in SQL?

A database object representing a set of SQL statements frequently used for a certain task. A function takes in some input parameters, performs calculations or other manipulations on them, and returns the result.

32. What types of SQL functions are there?

Aggregate functions – work on multiple, usually grouped records for the provided columns of a table, and return a single value (usually by group). For example – AVG(), SUM(), MIN() etc.

Scalar functions – work on each value and return a single value. For example – LEN(), ROUND(), NOW() etc.

SQL functions can be built-in (defined by the system) or user-defined (created by the user for their specific needs).

33. What is the difference between local and global variables?

Local variables can be accessed only inside the function in which they were declared whereas global variables are declared outside any function stored in fixed memory structures and can be used throughout the entire program.

34. What is a View?

A view is a virtual table that consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. The view can have data from one or more tables combined, and it is depending on the relationship.

35. What is a query?

A database query is a code written to get the information back from the database. The query can be designed in such a way that it matches our expectation of the result set.

36. What is sub query?

A subquery is a query within another query. The outer query is called as main query, and the inner query is called the subquery. A subQuery is always executed first, and the result of the subquery is passed on to the main query.

37. What is an alias?

A temporary name is given to a table or column while executing a certain SQL query. Aliases are used to improve the code readability and make the code more compact. An alias is introduced with the AS keyword. In the example, col_1 is the alias to the column.

SELECT col_1 AS column
FROM table_name;

38. What is a clause?

A condition is imposed on a SQL query to filter the data to obtain the desired result. Some examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

39. What are the TRUNCATE, DELETE, and DROP statements?

• DELETE - Delete statement is used to delete the rows from a table.
• TRUNCATE – It is used to delete all the rows from the table and free the space containing the table.
• DROP – This command is used to remove an object from the database. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database.

40. What is the difference between DELETE and TRUNCATE statements?

• DELETE is a SQL command that removes one or multiple rows from a table using conditions. For delete statement, use the WHERE clause with the DELETE command to delete specific records from the table.
• TRUNCATE is a SQL command that removes all the rows from a table without using any condition. It is used to delete the complete data from the table without deleting the table structure.

41. What is the difference between TRUNCATE and DROP statements

• TRUNCATE removes all the rows from the table, and it cannot be rolled back.
• The DROP command removes the whole table from the database along with table indexes, data, etc. and the operation cannot be rolled back.

42. What are some common statements used with the SELECT query?

The most common ones are FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT, and HAVING.

43. Define SQL ORDER BY statement.

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.
By default ORDER BY sorts the data in ascending order. The ASC keyword is used to sort the data in ascending order and the DESC keyword to sort the data in descending order.

44. Explain SQL HAVING statement.

HAVING is used to specify a condition for a group or an aggregate function used in the select statement. The HAVING clause selects rows after grouping.

45. What is the difference between the HAVING and WHERE statements?

The HAVING clause works on aggregated data after they are grouped, while the WHERE clause checks each row individually and selects before grouping. Unlike the HAVING clause, the WHERE clause cannot contain aggregate functions.

If both statements are present in a query then the order will be: WHERE – GROUP BY – HAVING.

46. What is the difference between COMMIT and ROLLBACK commands?

COMMIT is applied when the transaction is successful and the commit command will permanently save the changes. The transaction cannot be undone after the COMMIT execution.

ROLLBACK occurs when the transaction is aborted. It undoes the changes by the current transaction and reaches its previous state after ROLLBACK.

47. How to add a column to a table?

You can add a column to a table using the ALTER TABLE statement in combination with ADD.

ALTER TABLE table_name
ADD column_name datatype;

48. How to rename a column of a table?

You can rename a column name of a table using the ALTER TABLE statement in combination with RENAME COLUMN oldname TO newname.

49. How to prevent duplicate records when making a query?

You can prevent duplicate records by using the DISTINCT statement in combination with SELECT or by creating a unique key for that table.

50. How to insert many rows in a table?

You can insert multiple rows in a table using the INSERT INTO statement in combination with VALUES.

INSERT INTO TableName
VALUES (value_1, value_2, ..),      (value_3, value_4,...),       
(value_5, value_6, ..),   ..;