SQL Interview Questions for Database Developers in 2024

What is SQL?

SQL means structured query language used in database management systems to store and retrieve data from a relational database. The relational database refers to the data stored in the form of rows and columns, which represent attributes and relations. An attribute refers to any object, and a relation refers to the relationship between two or more objects. To retrieve or store the database, some SQL query statements such as SQL INSERT command to store the data and SELECT command to select the values from the stored database.

How does SQL work?

SQL implementation involves creating, storing, and retrieving a database. The server machine processes the database queries and retrieves the enquired data in this process. Let us understand SQl by a small example:

Example: Consider a table of data that contains the information of students; the table mainly consists of:

• Student Name
• Student Id
• Student Grade

Student Name	Student ID	Student Grade
Mahesh	200147	A
Rani	200148	A+
Pooja	200149	B

• For Retrieving the Data,  we use the (SELECT) command :

SELECT * FROM Student Data WHERE Student Grade = ‘A+’;

• We get the output as follows:

Rani 200148  A+ 

• Delete Data (DELETE):

If you want to remove “Mahesh” from the table:

• DELETE FROM Student Data WHERE Student Name = ‘Mahesh’;

This removes the row where the Student Name is “Mahesh.”

What are Commands in SQL?

The commands which are used to manipulate the data in database management are called SQL commands.  These commands are categorised into 5 commands that are:

1. DDL(Data definition language)
2. DQL(Data manipulation language)
3. DML(Data manipulation language)
4. DCL(Data control language)
5. TCL(Transaction control language)

Data Definition Language (DDL)

Data definition language (DDL) refers to SQL commands to design database structures. Database engineers use DDL commands to create and modify database values based on business requirements. For example, the database engineer uses the ALTER to change the structure of an existing database object, such as adding, deleting, or modifying columns in a table.

Data Query Language (DQL)

Data query language (DQL) is similar to DDL but has different commands. It consists of instructions for retrieving data stored in relational database systems. For Example, the SELECT statement retrieves data from a database. You can specify which columns to retrieve, filter records, sort the results, and more.

Data Manipulation Language (DML)

Data manipulation language (DML) statements write new information or modify existing records in a relational database. For example, an application uses the INSERT command to store a new record in the database.

Data Control Language (DCL)

Database administrators use data control language (DCL) to manage or authorise database access for other users. For example, they can use the GRANT command to permit specific applications to manipulate one or more tables.

Transaction control language (TCL)

The relational engine uses transaction control language (TCL) to make database changes automatically. For example, the database uses the ROLLBACK command to undo an erroneous transaction.

What are SQL interview questions?

1) What is SQL?

Ans: SQL is a structured query language that stores, retrieves, updates, and deletes data from a database. SQl mainly includes significant categories like DDL, DML, DCL, and DQL.

2) What are the different types of SQL commands?

Ans: DDL, DML, DCL, and TCL are some of the main commands in Structured Query Language

• DDL mainly consist of CREATE, ALTER, and DROP commands
• DML mainly consists of SELECT, INSERT, UPDATE, and DELETE commands.
• DCL mainly consists of GRANT and REVOKE commands.
• TCL mainly consists of COMMIT, ROLLBACK, and SAVEPOINT commands.

3) What is a primary key?

Ans: A primary key is a unique identifier for each record in a table.

4) What is the difference between WHERE and HAVING clauses?

 Ans: The WHERE filters rows before aggregation while HAVING filters rows after aggregation.

5) What is Normalisation?

Ans: Normalisation is a process by which the database is filtered. Some of the different normal forms are (1NF, 2NF, 3NF, and BCNF); it reduces redundancy and dependency in a database.

Advanced SQL Interview Questions:

• What is an index, and what are the types of indexes?
• What is the difference between UNION and UNION ALL commands?
• What are the ACID properties in a database management system?
• What is a trigger? Give an example.
• How does an EXISTS clause work?
• What is the difference between ROWNUM and ROW_NUMBER ()?
• How will you optimise SQL queries?
• What is a subquery?
• Explain the difference between RANK () and ROW_ NUMBER().
• What is sharding?
• How will you deal with Deadlocks?
• Explain Transactions in DBMS.
• Explain CTE and how it is different from a subquery.
• What are window functions?

Checkout the lastest article about: Artificial Intelligence in 2024