Must Practice Interview Questions of SQL

Let’s brush up the most important interview questions of SQL

Saumya Singh

Published in

Coding Blocks

8 min readOct 20, 2020

1. What is SQL?

SQL : Structured Query Language

It is a language used to interact with databases e.g create database, create table, update table etc.It is standard language for relational database management system (rdbms). SQL is an ANSI standard.

Uses of SQL:

To retrieve data from a database
To insert records in a database
To update records in a database
To delete records from a database
To create new databases
To create new tables in a database

2. What is Database?

Database is organized collection of data.The data is stored and retrieved digitally from a remote or local computer system.

It is also collection of queries, views, schemas etc.

3. What is DBMS?

DBMS: Database Management System.

It allows the user to interact with database. It is a system software responsible for creation, updation and complete management of database.

Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

4. What is RDBMS? How is it different from DBMS?

RDBMS: Relational Database Management System

Main difference : RDBMS stores data in form of collection of table. Relation is defined between common fields of table.

Modern database management systems ( like MySQL, Microsoft SQL Server, Oracle ) are based on RDBMS.

5. What is difference between SQL and MySQL?

SQL as we studied is a language to manage the database. On the other hand, MySQL is a relational database management system ( like Oracle, SQL server ) that is used to manage SQL databases.

6. What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table.

NOT NULL — Restricts NULL value from being inserted into a column.
CHECK — Verifies that all values in a field satisfy a condition.
DEFAULT — Automatically assigns a default value if no value has been specified for the field.
UNIQUE — Ensures unique values to be inserted into the field.
INDEX — Indexes a field providing faster retrieval of records.
PRIMARY KEY — Uniquely identifies each record in a table.
FOREIGN KEY — Ensures referential integrity for a record in another table

Constraints are used to limit the type of data that can go into a table.

7. Which is the only constraint that enforces rules at column level?

NOT NULL is the only constraint that works at the column level.

8. What is a UNIQUE constraint?

Uniques constraint ensures all the values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

9. What are the different subsets of SQL?

DDL (Data Definition Language) — It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
DML ( Data Manipulation Language) — It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
DCL ( Data Control Language) — It allows you to control access to the database. Example — Grant, Revoke access permissions.

10. What do you mean by table and field in SQL?

A table refers to a collection of data in an organised manner in form of rows and columns. A field refers to the number of columns in a table.

For example:

Table: StudentDetails
Field: Stu Id, Stu Name, Stu Marks

11. What are joins in SQL?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:

Inner Join
Right Join
Left Join
Full Join

12. What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length.

For example, char(10) can only store 10 characters and it adds trailing zeroes if length is less than 10 whereas varchar2(10) can store any length i.e 6,8,2 and it do not add trailing zeroes.

The CHAR data type stores trailing blanks up to a fixed column length for all column values, whereas the VARCHAR2 data type does not add extra blanks. Then to store data more efficiently, use the VARCHAR2 data type.

13. Explain the join types in detail.

(INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.

SELECT * 
FROM Table_A 
JOIN Table_B; SELECT * 
FROM Table_A 
INNER JOIN Table_B;

LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.

SELECT * 
FROM Table_A A 
LEFT JOIN Table_B B 
ON A.col = B.col;

RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.

SELECT * 
FROM Table_A A 
RIGHT JOIN Table_B B 
ON A.col = B.col;

FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.

SELECT * 
FROM Table_A A 
FULL JOIN Table_B B 
ON A.col = B.col;

14. What is a Primary key?

A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
Uniquely identifies a single row in the table
Null values not allowed

Example- In the Student table, Stu_ID is the primary key.

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

16. What is a Foreign key?

Foreign key maintains referential integrity by enforcing a link between the data in two tables.
The foreign key in the child table references the primary key in the parent table.
The foreign key constraint prevents actions that would destroy links between the child and parent tables

17. What do you mean by data integrity?

Data Integrity defines the accuracy as well as the consistency of the data stored in a database.

18. 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.

19. 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 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.

SELECT stu.name, sub.subject 
FROM students AS stu
CROSS JOIN subjects AS sub;

20. What is the difference between DROP and TRUNCATE statements?

If a table is dropped, all things associated with the tables are dropped as well. This includes — the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has. To create and use the table again in its original form, all these relations, checks, constraints, privileges and relationships need to be redefined. However, if a table is truncated, none of the above problems exist and the table retains its original structure.

21. What is Normalization and what are the advantages of it?

Normalization is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:

Better Database organization
More Tables with smaller rows
Efficient data access
Greater Flexibility for Queries
Quickly find the information
Easier to implement Security
Allows easy modification
Reduction of redundant and duplicate data
More Compact Database
Ensure Consistent data after modification

22. What is ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.

Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.

Isolation: The main goal of isolation is concurrency control.

Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

23. List the ways to get the count of records in a table?

To count the number of records in a table, you can use the below commands:

1. SELECT * FROM table12. SELECT COUNT(*) FROM table13. SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

24. Write a SQL query to find the names of employees that begin with ‘A’?

SELECT * FROM Table_name WHERE EmpName like 'A%'

25. How can you fetch common records from two tables?

You can fetch common records from two tables using INTERSECT.

Do clap 👏🏻👏🏻 50 times and share the article if you like it. Thank you for giving your valuable time