SQL Interview Quick Guide 2024: Ace It in Minutes!

• Writing data integration scripts by the developers and database administrator.
• Setting and running analytical queries on the regular basis and making new datasets from the original data.
• Retrieving subsets of information within an original database for analytics and visualization purposes
• Most common use is Adding, updating, and deleting rows and columns of data in a database

The SELECT command is used to display the rows from the database based on the query. The SELECT command is a data manipulation language (DML) command

For example:. We have a student database for a school and we have multiple columns some are StuID, and StuName, and the query is to display the student name.

Query: SELECT * from student;

• Data definition language (DDL): DDL consists of SQL commands which can be used for defining database schema. DDL deals with the description of the database, update, and delete of the database where it consists of commands like CREATE, ALTER, TRUNCATE, and COMMENT.
• Data manipulation language (DML): DML is used to manipulate the existing data in the database. The DML commands are SELECT, UPDATE, INSERT, etc.
• Data control language (DCL): DCL controls the access to the data stored in the database and the DCL commands are GRANT and REVOKE.
• Transaction Control Language (TCL): TCL is used to deal with the transaction operations in the database. The TCL commands are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

There are 5 subsets in SQL:

1. Data Definition Language

DDL stands for Data Definition Language where the commands are used to define the database schema. DDL is mostly used to describe the database schema to developers and to create, and modify the overall structure of the database.

The examples of DDL commands are: 

• CREATE – Using the create command we can create the database or its objects such as a table, index, function, and views.
• DROP – Using the drop command we can delete objects from the database.
• ALTER – Using the alter command we can change the structure of the database.
• TRUNCATE – Using the truncate command we can remove all the records from a table, including all spaces allocated for the records are removed.
• COMMENT – Using the comment command we can add comments to the data dictionary.
• RENAME – Using the rename command we can rename an object which exists in the database.

2. Data Manipulation Language

DML stands for Data Manipulation Language where the commands are used for manipulating the data in the database.

The examples of DML commands are:

• SELECT – Using the select command to retrieve data from the database.
• INSERT– Using the insert command to insert data into a table.
• UPDATE– Using the update command to update existing data within a table.
• DELETE– Using the delete command to delete records from a database table.

A JOIN clause is used to combine rows from more than one table based on the same column from both tables.  The two tables are merged and we will retrieve new data from that.

Inner Join: Most of the common types of SQL is Inner Join. Inner Join will return all the rows from multiple tables when the condition is satisfied.

Syntax Inner Join:

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

Left Join: In Left Join of SQL only rows from the left table are returned and the union of left and right table where the condition is satisfied.

Syntax Left Join:

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

Right Join: In Right Join of SQL all the rows from the right table are returned but only the matching rows from the left table where the join condition is fulfilled.

Syntax right Join:

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

Full Join: In Full join of SQL all the records are returned when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

Syntax Full Join:

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

There are a plethora of types of joins, as you can refer to below. Below is a cheat sheet for various types of JOINT in SQL.

We can define a primary key in a student table as follows:

CREATE TABLE Student (
roll_number INT PRIMARY KEY,
name VARCHAR(45),
);

The foreign key is also known as the referencing key. We use a foreign key to link one or more tables together from the database.

A foreign key is often specified as a key that is related to the primary key of another table in simple terms it means that the foreign key field in one table refers to the primary key field of another table. It maintains referential integrity. ACID properties are maintained by the primary key-foreign key relationship. Foreign key also prevents actions that would destroy links between the child and parent tables from the database.

For example, we have student data of a university or college where the columns are roll number and name and we want to display the unique kids having their roll number. Here, the ROLL Number can be treated as the primary key for a student.

We can define a foreign key in a student table as follows:

CREATE TABLE Students ( 
roll_number INT NOT NULL 
name VARCHAR(255) 
LibraryID INT 
PRIMARY KEY (roll_number) 
FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) 
);

C. HeidiSQL

D. All of the above

ANSWER: D (All of the above)

A. Structured Query Language

B. Structured Query List

C. Sample Query Language

D. None of these.

ANSWER: A (Structured Query Language)

C. INSERT TABLE

D. SELECT TABLE

ANSWER: B (CREATE TABLE)

Explanation: CREATE TABLE function is used to create the table in SQL database.

A. FLOAT

B. NUMERIC

C. DECIMAL

D. CHARACTER

ANSWER: C (DECIMAL)

Explanation: DECIMAL is not a valid SQL type because in SQL it is a numeric type.

C. DROP

D. ALTER

ANSWER: B (TRUNCATE)

Explanation: TRUNCATE command is used to delete all the rows without removing the individual rows from the table. TRUNCATE statement is similar to the DELETE statement in SQL just without the WHERE clause in the query.

C. Both

D. None of this

ANSWER: C (Both)

Explanation: REVOKE and GRANT are the commands for the Data control language.

C. COCNAT

D. FIND

ANSWER: B(SOUNDEX)

B. Not Null

C. Both Null and Not Null

D. Null

ANSWER: D(Null)

Explanation: A primary key is a field or the combination of fields that uniquely identify each record in the table. The primary key is a unique key as the table can have only one primary key and it can not be null.

C. Depends on the Columns

D. Depends on the situation

ANSWER: A (Only 1)

Explanation: A primary key is a field or the combination of fields that uniquely identify each record in the table. The primary key is a unique key as the table can have only one primary key and it can not be null.

C. Entity

D. None of this

ANSWER: A(Tuple)
Explanation: The collection of rows & columns is called the table, whereas a table is known as the relation in the SQL therefore in a relation rows are called the tuples.

C. Data definition language

D. Detailed data language

ANSWER: C(Data definition language)

A. COMMIT and ROLLBACK

B. UPDATE and INSERT

C. SELECT and INSERT

D. GRANT and COMMIT

ANSWER: A (COMMI and ROLLBACK)

A. DESC

B. ASC

C. NOT DEFINED FORMAT

ANSWER: B (ASC)

Explanation: In the command, if we are not specifying ASC or DESC after a SQL ORDER BY clause, the result is displayed in which order.

C. PRODUCT

D. MULTIPLY

ANSWER: A (JOIN)

C. CHECK

D. B and C

ANSWER: B (UNION)

Conclusion on SQL Questions