List of top 50 most asked SQL interview questions

Author: Shrey Reddy | 19-11-2024 10:42 AM

SQL Database

SQL stands for "Structured Query Language". Here, we are trying to list some important SQL interview questions that will help you to find a better way to explore your career in the SQL database field.

List of Top Most Asked SQL interview questions

Q1 What is SQL?

SQL stands for STRUCTURED QUERY LANGUAGE as mentioned in the upper line. It is a database computer language that is used to store data in a database table with the help of queries.

The SQL database is designed for the retrieval and management of data in a relational database. Basically, a program is considered when it comes to maintaining, creating, deploying, controlling as well as monitoring the use of a database. It can also be considered as a file manager which is good enough to be trusted for managing the data kept in a database than a file system.

Q2 What is a query?

A SQL QUERY Or SQL DB QUERY is a code written in order to get the information back from the database.

The query can be designed in such a way that it matched our expectation of the result set. Simply, we can say that ask a question to the database by using the query.

Q3 What is SUBQUERY?

The SQL SUBQUERY is also called 'Inner Query'. SUBQUERY is always executed first and the result of SUBQUERY is passed on to the main query. SUBQUERY is always inside the main query, and the main query is also called 'Outer Query' "Basically, A subquery is a query within another query".

Q4 What is a SQL HEAP?

A SQL HEAP is a table that does not contain any clustered file OR Non clustered file index.

Q5 What is the difference between SQL and PLSQL?

SQL is a single query or command executive& PL/SQL is not a command executive language
SQL is not a full programming languagebut PLSQL is a full programming language
SQL data source for reports, web pages but PLSQL application language to build, format, and display report, web pages
SQL declarative in nature and Manipulating data but PLSQL procedural in nature and Creating Applications

Q6 What is a PRIMARY KEY in SQL?

A PRIMARY KEY is a combination of fields that uniquely specify a row. The PRIMARY KEY is a special kind of unique key & it has implicit NOT NULL constraint [That means, the Primary key values cannot be NULL].

PRIMARY KEY Important Conditions
A Primary key is a set of columns that uniquely identifies each row in the table.
Uniquely identifies a single row in the table
Null values not allowed in the PRIMARY KEY column

Q7 What is a FOREIGN KEY in SQL?

A FOREIGN KEY is one table that can be related to the PRIMARY KEY of another table, relationship needs to be created between two tables by referencing FOREIGN KEY with the PRIMARY KEY of another table.

Q8 What is the DBMS?

A Database Management System (DBMS) is a software application that interacts with the user, applications, and the database itself to capture and analyze data.

Q9 What are the different types of DBMS?

RDMS stands for "Relational Database Management System" [The data is stored in relations (tables) for example – MySQL].
NON-RDMS stands for "Non-Relational Database Management System" [There is no concept of relations, tuples, and attributes for example – Mongo].

Q10 How to check the SQL SERVER version?

We can check the SQL SERVER version by running the following command:

SELECT @@Version;

Q11 What are the uses of SQL?

SQL is responsible for maintaining the relational data and the data structures present in the database.
To Execute queries against a database
To RETRIEVE data, INSERT records, UPDATE records, DELETE records, CREATE new databases, CREATE new table, CREATE views in a database.

Q12 What are Fields and Tables? How they are useful?

A table is a set of different rows & columns and it is organized in a model. The manner of columns & rows is vertical and horizontal.

There are some specific numbers of columns that remain present and it's generally known as fields in a table & there is no strict upper limit on the overall number of records that are defined by rows in the table.

Q13 What is a SQL join?

An SQL JOIN clause combines rows from two or more tables. It creates a set of rows in a temporary table.

Q14 What are the types of join?

Mostly, Four types of Joining are available in the SQL database.

Inner Join
Left Join OR Left Outer Join
Right Join OR Right Outer Join
Full Join

Q15 What is the difference between Cross and Natural Join?

CROSS JOIN - The Cross Join delivers the cross-product result of two tables.
NATURAL JOIN - The Natural Join result depends on every one of the columns having a similar name and information types in both the tables.

Q16 What is the difference between SQL and MySQL?

SQL is a Standard Query Language based on the English language whereas MySQL is a Database Management System.

SQL is the core of a relational database which is used for accessing and managing databases.
MySQL is a Relational Database Management System (RDMS) such as SQL Server, Informix, etc.

Q17 Does SQL support programming?

SQL refers to the Standard Query Language that is not actually the programming language.
SQL doesn't have a loop, conditional statement, logical operations, it can not be used for anything other than data manipulation.
It is used like commanding (Query) language to access databases.
The primary purpose of SQL is to retrieve, manipulate, update and perform complex operations like joins on the data present in the database.

Q18 What is a DDL?

DDL stands for Data Definition Language that is the subset of the database which defines the data structure of the database in the initial stage when the database is about to be created. It has the following commands such as CREATE, ALTER, and DELETE database objects such as schema, tables, view, etc.

Q19 Why do we use SQL constraints?

The SQL constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Q20 Which constraint is used while creating the database in SQL?

Basically, there are Five constraints used in SQL to create a database:

NOT NULL - That indicates that the column must have some value and cannot be left null
UNIQUE - This constraint is used to ensure that each row and column has a unique value and no value is being repeated in any other row or column
PRIMARY KEY - This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.
FOREIGN KEY - It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
CHECK - It is used to ensure whether the value in columns fulfills the specified condition

Q21 What are the various levels of constraints?

Constraints are the representation of a column to enforce data entity and consistency. There are two levels of constraint used in SQL such as:

column-level constraint
table-level constraint

Q22 What is the difference b/w the ‘BETWEEN’ and ‘IN’ operators?

BETWEEN operator is used to displaying rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

BETWEEN Operator (Student Table)

SELECT * FROM table_name

WHERE ROLL_NO BETWEEN 10 AND 50;

IN Operator (Student Table)

SELECT * FROM table_name

WHERE ROLL_NO IN (8,15,25);

Q23 What are different Clauses used in SQL?

WHERE condition is used to define the condition extract and display only those records which fulfill the given condition.
GROUP BY is used with SELECT statement to group the result of the executed query using the value specified. It matches the value with the column name in tables and groups the end result accordingly.
HAVING clause is used in association with GROUP BY clause. It is applied to each group of results or the entire result as a single group and is much similar to the WHERE clause, the only difference is you cannot use it without the GROUP BY clause
ORDER BY clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.
USING clause comes into use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.

Syntax Example of Clause

WHERE CLAUSE

SELECT column_name(s)

FROM table_name

WHERE [condition];

GROUP BY CLAUSE

SELECT column_name(s)

FROM table_name

GROUP BY column_name;

HAVING CLAUSE

SELECT column_name(s)

FROM table_name

GROUP BY column_name HAVING [condition];

ORDER BY CLAUSE

SELECT column_name(s)

FROM table_name

WHERE [condition]

ORDER BY column_name ASC|DESC;

USING CLAUSE

SELECT column_name(s)

FROM table_name

JOIN table_name

USING column_name;

Q24 Is it possible to order the records in two columns?

Yes; It is possible to order the records by using two columns.

Syntax Used to ORDER two columns

SELECT * FROM table_name

ORDER BY column_name1, column_name2;

Q25 When are SQL functions used?

Perform some calculations on the data
Modify individual data items
Manipulate the output
Format dates and numbers
Convert the data types

Q26 What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify fields that can be made in a single table.

Q27 What are all the different normalizations?

1NF - This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
2NF - Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
3NF - This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
4NF - Fourth normal form (4NF) is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key. It builds on the first three normal forms (1NF, 2NF, and 3NF) and the Boyce-Codd Normal Form (BCNF).

Q28 What is Denormalization?

Denormalization is an approach to speeding up read-oriented data retrieval performance in a relational database, where the database administrator selectively adds back specific instances of redundant data after the data structure has been normalized.

A denormalized database should not be confused with a database that has never been normalized.
Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

Q29 What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Q30 What are the different types of indexes in SQL?

Unique Index - Prevents duplicate entries within uniquely indexed columns. They are automatically generated if a Primary Key is available.
Clustered Index - Used to organize or edit the arrangement within the table, with respect to the key value. Each table is only allowed to have a single clustered index only.
Non Clustered Index - NonClustered Index only manages the order of logic within entries. It does not manage the arrangement and tables can have multiple NonClustered Indexes.

Q31 What is the UNION & UNION ALL keyword in SQL?

The UNION operator in SQL combines multiple sets highlighted in the SELECT statements.

Column number must be identical,
Data Types in the set must be the same,
The order of the column highlighted in the SELECT statement must be the same. It automatically eliminates duplicate rows within the results highlighted in the SELECT statement.

UNION ALL does the same function as the UNION, but it includes all, including the duplicate rows.

C1, C2, C3 & C4 → Column_Name
T1 & T2 → Table_Name
SELECT C1, C2 FROM T1 UNION SELECT C3, C4 FROM T2;

Q32 What is View in SQL?

A VIEW can be defined as a virtual table that contains rows and columns with fields from one or more tables.

Syntax Example of VIEW

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE [condition];

Q33 What is distinct?

Distinct is used to suppress duplicate rows in select queries.

Q34 What is the difference between nested subquery & Co-related subquery?

Nested Sub Query

A Nested subquery executes only once for the entire nesting query.
The nested subquery does not contain any reference to the outer query row.

Co-related Subquery

Co-related subquery executes a single time for every row which is selected by the outer query.
Co-related subquery has a reference to a value from the row selected by the outer query.

Q35 What are the different types of queries in SQL?

Three types of queries are used in SQL that are explained below:

DDL - Data Definition Language
DML - Data Manipulation Language
DCL - Data Control Language

Data Definition Language - This allows to work with the Structure or Definition of the data. SQL commands [CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME].

Data Manipulation Language - It is a query that is used to insert, update, or delete the data from a database table. SQL commands [ INSERT INTO, UPDATE, SELECT, and DELETE] queries

Data Control Language - It is used to control the data. SQL Commands [GRANT, REVOKE, DENY]. The administrative tasks include the creation of users, partitioning of the database, providing grants to the user, assigning roles to the user, and creating users as well.

Q36 What are aliases in SQL?

Aliases in SQL are used to provide alternate names for columns and tables in SQL queries. It is used when the name of a column or table is used other than their original names but the modified name is only temporary.

Aliases are created to make table or column names more readable. It helps in resolving anonymity when trying to join tables. If two different tables contain the columns of the same name, aliases help in selecting a specific column only in the select clause.

Q37 What are SQL functions?

SQL defines certain standard functions that can be used to perform mathematical manipulations. There are two types of SQL functions, aggregate functions, and scalar(non-aggregate) functions.

SQL Aggregate Function - Aggregate functions are Count, Sum, Avg, Max, Min functions
SQL Arithmetic Fuction - Arithmetic functions are abs(), ceil(), floor(), exp(), In(), mod(), power(), and sqrt()

Q38 What is a wildcard?

A wildcard character is used to substitute any other character(s) in a string. It is used with the LIKE operator to provide a wildcard match of values.

Q39 What is the difference between Truncate and Delete in SQL?

TRUNCATE but A trigger can be executed DELETE command.
TRUNCATE is a Data Definition Language command & DELETE is a Data Manipulation Language command.
TRUNCATE will not store data in rollback space and will directly delete it but when you use DELETE to delete the data, at that time it stores the whole data in rollback space from where you can get the data back after deletion

Q40 What is Trigger?

A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.
A trigger is a process of firing an action when some event like Insert, Update, or Delete occurs.
Triggers can be defined on the table, view, schema, or database with which the event is associated.

Q41 What are ACID properties?

ACID properties are combination properties:

A - Atomicity
C - Consistency
I - Isolation
D - Durability

Q42 What are the Local variables in SQL?

The local variables are not referred by some other function OR out of the function. The local variables can be used just inside the same function, where the local variable is placed.