Top 15 Basic SQL Interview Questions You Should Know

While trying to secure a dream job in the IT sector, the most formidable task is to clear the interview phase. A phase where an aspirant needs to prove his/her worth. Increase in demand gives an upper hand to hiring Managers as the expectations are higher than ever, more aspirants, less job availability & a defined budget. So, an aspirant shouldn’t lose out on any opportune moment offered to him or her.

HR’s make it difficult for programmers to clear an interview because they want to shortlist quality candidates. To make your job easier, here’s an elaborative questionnaire for SQL that is asked commonly in an interview. So let’s get started!

TIP for any Interviewee:

To make an interview successful, you need to be sincere & answer the tricky questions thrown at you. In case you don’t know an answer, before admitting that you don’t know the answer, try to clarify the question if you haven’t got it fully. Secondly, try to figure out the solution using basics & other resources. Because in interviews, HR’s are more concerned about how you handle situations.

Can a person find solutions & resolve problems on their own? Because a person who can resolve issues is a far better candidate than who is just bookish (and can’t apply it practically). If you aren’t able to figure out the answer, politely admit it by saying, you will surely find answers after the interview. This attitude might prove your transparency & honesty.

QUESTION 1

• What is the difference between DBMS & RDBMS?
• DBMS & RDBMS are the same or different & by which means?

ANS:

Overall, RDBMS is an extension or advanced version of DBMS. Today, there are many types of software which are compatible with DBMS, RDBMS or both. That’s all!

QUESTION 2

What is SQL & MySQL? Is there any difference between them?

ANS:

QUESTION 3

• What are the tables & fields?
• What do you mean by tables & fields? How are they different?

ANS:

• A set of data organized in rows & columns is known as “Table”. Rows are horizontal whereas columns are vertical.
• The Number of columns in a table is referred/known as Fields. So, in fields we are concerned about vertical columns.
• Example:
  • Report on students
  • Table : StudentInfo
  • Fields: StuID, StuName, StuGR, etc

QUESTION 4

• What does “Key” mean/refer1 to in SQL? Explain a few types of keys in SQL.
• What are the keys in SQL? How many keys are there in SQL?

ANS:

Keys are features/attribute or set of such features which allow you to find the relation between 2 tables effortlessly. These attributes help you to sort out things with ease. There is a total of 10 types of Keys as follows:

1. Primary Key
2. Unique Key
3. Foreign Key
4. Super Key
5. Alternate Key
6. Composite/Compound Key
7. Candidate Key
8. Minimal Super Key
9. Natural Key
10. Surrogate Key

Tables to explain keys better:

TABLE 1: CATEGORY INFO

TABLE 2: STUDENT INFO

Super Key:
Set of one or more keys that can identify a record uniquely in the database table. Primary, Unique & Alternate Keys are a subset of Super Key.

Super Keys in Category_INFO Table:

• Category_ID
• Category_Name
• Category_Code
• [Category_Name , Category_Code]
• [Category_Id, Category_Code]

Super Keys in Student_INFO Table:

• Student_ID
• College_GR
• Roll_No
• [Rtu_Roll_No, Session]
• [College_Id, Category_Id]
• [Student_Id, Student_Name]

Primary Key:
Set of one or more fields which uniquely identify the specified record in the database table. A column can be sorted out quickly from a database using the primary key. However, it will not accept duplicate values & null values (zero, void).

• Primary Key in Category_INFO table: Category_ID
• Primary Key in Student_INFO table: College_GR

Unique Key:
Set of one or more fields which uniquely identify a specified record in the database table. It’s quite similar to the primary key but can accept one null value. And, it cannot allow duplicate values too.

• Possible Unique Key in Category_INFO table: Category_Name
• Possible Unique Key in Student_INFO table: Roll_No

Foreign Key:
Similar to primary key but can accept multiple null & duplicate values.

Category_ID is a foreign key in Student_INFO table that primary key exists in Category_ID of Category_INFO table.

Candidate Key:
Set of one or more fields which uniquely identify a specified record in the database table. The main difference between other keys & candidate key is that there can be multiple candidate keys in one table.

Candidate Keys in Category_INFO table:

• Category_ID
• Category_Name
• Category_Code

Candidate keys in Student_INFO table.

• Student_ID
• College_GR
• Roll_No

Alternate Key:
The alternate key can work as a primary key. In simple words, it’s a candidate key that isn’t the primary key currently. Also known as “Secondary key.”

Alternate Key in Category_INFO table:

• Category_Name
• Category_Code

Alternate Key in Student_INFO table:

Student_GR
Roll_No

Composite/Compound Key:
Integration of more than one attributes which can uniquely identify each record. Also known as “Compound Key,” it can be primary or candidate key.

• Composite Key in Category_INFO table.
• [Category_Name, Category_Code]
• Composite Key in Student_INFO table:
• [Student_ID, Student_Name]

QUESTION 5

Explain the types of joins in SQL.

ANS:

There are 4 essential types of joins in SQL as follows:

• Inner Join: Most commonly used to join in SQL. Usually used to return all rows from tables if the join condition is satisfied.
• Left Join: Used to return rows from the left table only but the ones who are matching with the right table where the join condition is fulfilled.
• Right Join: Used to return rows from the right table only but the ones who are matching with the left table where the join condition is met.
• Full Join: This join returns the entire record if the requirement fulfills. Thus, returning all rows from the right & left the table if the condition is fulfilled.

QUESTION 6

What is Normalization & Denormalization?

ANS:

• Normalization is the process of organizing data to avoid redundancy & data duplication. It leads to Better Database organization, efficient data access & reduced duplication. It aims to add, delete, or modify fields that can be shortening to a single table. Thus reducing space & access time.
• Denormalization:
• The process to access the data from higher to lower normal forms of the database. Used to add redundancy into the table by integrating data from related tables.

QUESTION 7

Explain all types of normalization.

ANS:

• Normal forms are divided into five types. Following is a short explanation on each.
• First Normal Form: Removes all duplicate columns from the table. Also, the creation of a table for related data & unique column identification.
• Second Normal Form: Everything of 1NF plus positioning subset of data in a separate table. Also, relates tables using primary tables.
• Third Normal Form: Similar to 2NF but eliminates columns which are not dependent on the primary key.
• Fourth Normal Form: All requirements of 3NF & it should never have multi-valued dependencies.

QUESTION 8

What is an Index in SQL? State its type.

ANS:

An index in SQL is used to enhance the performance of queries. It reduces the number of database data pages. In simple words, an entry is generated for each value & hence, faster retrieval of data.

Types of SQL:

• Unique Index: This index doesn’t allow duplication of the field if the column is unique indexed. Unique index gets applied automatically if the primary key is defined.
• Clustered Index: Rearranges physical order of the table & searches concerning key values. A table can have only 1 clustered index.
• Non-Clustered Index: This index doesn’t make changes in the physical order. Just maintains a logical hierarchy of data. A table can have only one non-clustered index.

QUESTION 9

What is a relationship & what are they?

ANS:

Database relationship refers to the relation between the tables in a database. There are four major types of relationships, as follows:

• One to One
• One to Many
• Many to One
• Self-Referencing

QUESTION 10

What do you mean by sub-query & give a short explanation of its types?

ANS:

Query within the query is known as a subquery. Outer query is known as “Main query” & the inner one is called “Subquery.” It’s the same as a nested loop of C language. While execution, SubQuery gets executed first & its result passes to the main query.

Types of Subquery:

• Correlated: This subquery is not an independent query. It’s a query that uses values of the outer query. Also known as “Synchronized subquery.”
• Correlated subquery executes for each row returned by the outer query because output depends on the comparison of data returned by one row to all other rows of the table. Thus, making it a bit slow & less usable.
• Non-Correlated: An independent query where the output of subquery is placed in the main query.

QUESTION 11

What’s the difference between DELETE & TRUNCATE?

ANS:

QUESTION 12

What is collation & its sensitivity?

ANS:

A set of rules or a protocol that decides how character data may be sorted or compared. Collation is used to compare like say A & another language character. ASCII values are usually used to compare data.

Types of collation sensitivity:

• Case Sensitivity: A & a are different
• Kana Sensitivity: Japanese Kana Characters
• Width Sensitivity: Single & Double-byte character
• Accent Sensitivity

QUESTION 13

What is OLTP & OLAP? What are their significant differences?

ANS:

OLTP:
OLTP or Online transaction processing is a 3-level/tier architecture which supports transaction-related applications. It also manages applications used for data entry, data processing & data retrieval.
Example: Bank Transaction

OLAP:
OLAP or Online Analytical Processing is a software tool used for the analysis of data for business decisions. It can analyze multiple databases & give a particular result.
Example: Analysis of sales of a company

Difference between OLTP & OLAP:

QUESTION 14

What is an ALIAS Command?

ANS:

This question is quite fundamental, but people tough to answer.

ALIAS Command is used to temporarily assign a name to table or column for the duration of SELECT query.

Alias Column Syntax:

SELECT column_name AS alias_name FROM table_name;

Alias Table Syntax:

SELECT column_name(s) FROM table_name AS alias_name;

In the above syntax “AS” can be replaced by another abbreviated word. Example:

Select em.EmployeeID, Ex.Rating from employee em, Rating as Ex where em.EmployeeID = Ex.EmployeeID

QUESTION 15

• What is ACID property in a database?
• Explain each abbreviated property of ACID.

ANS:

ACID or Atomicity, Consistency, Isolation & Durability. It ensures a reliable data transaction in a database system. Let’s discuss each property.

• Atomicity: Transactions that are entirely done or failed where it’s a single logical operation of a data. In simple words, if one part transaction fails, the entire transaction process fails to lead to no change in the database.
• Consistency: Checks & ensures that data meet all validation rules or not. In other words, a transaction cannot leave the database without completion of its state.
• Isolation: Ensures Concurrency control
• Durability: Transaction will complete even after circumstances like power loss, crash, or any other error. If a transaction is set & committed, it will occur.

BONUS Question:

QUESTION 16

What are the subsets of SQL?

ANS:

• DDL: Data Definition Language allows operations like CREATE, ALTER & DELETE objects.
• DML: Data Manipulation Language allows to access & manipulate data. Operations like INSERT, UPDATE DELETE & RETRIEVE data from the database are within DML.
• DCL: Data Control Language allows to control access to the database. Operations like GRANT, REVOKE come under DCL.

Few recommended questions:

• What is STUFF & REPLACE function?
• What is Auto-Increment in SQL?
• What are views & why do you use them?
• What’s the major difference between SQL & PL/SQL?
• Aggregate & Scalar Function

Practical questions:

• How to fetch alternate records from a table?
• Pattern Matching
• Fetching 1st or last five characters
• Fetching unique records
• Keys