SQL Interview Questions and Answers
SQL Interview Questions and Answers
SQL Interview Questions and Answers
The following are the most popular and useful SQL interview questions and answers for
fresher and experienced candidates. These questions are created specifically to
familiarise you with the types of questions you might encounter during your SQL
interview. According to our experiences, good interviewers rarely plan to ask any specific
topic during the interview. Instead, questioning usually begins with a basic
understanding of the subject, and based on your responses, further discussion
happened.
1) What is SQL?
SQL stands for the Structured Query Language. It is the standard language used to
maintain the relational database and perform many different data manipulation
operations on the data. SQL was initially invented in 1970. It is a database language
used for database creation, deletion, fetching and modifying rows, etc. sometimes, it is
pronounced as 'sequel.' We can also use it to handle organized data comprised of
entities (variables) and relations between different entities of the data.
The primary key act as a unique The unique key is also a unique identifier for
identifier for each record in the records when the primary key is not present
table. in the table.
We cannot store NULL values in We can store NULL value in the unique key
the primary key column. column, but only one NULL is allowed.
We cannot change or delete the We can modify the unique key column
primary key column values. values.
To read more information, click here.
The primary use of a view is to implement the security mechanism. It is the searchable
object where we can use a query to search the view as we use for the table. It only
shows the data returned by the query that was declared when the view was created.
We can create a view by using the following syntax:
1. CREATE VIEW view_name AS
2. SELECT column_lists FROM table_name
3. WHERE condition;
30) What are the differences between SQL, MySQL, and SQL
Server?
The following comparison chart explains their main differences:
SQL has no variables. MySQL can use variables SQL Server can use
constraints and data variables constraints
types. and data types.
SQL PL/SQL
SQL can execute only a single PL/SQL can execute a whole block of code at
query at a time. once.
SQL can directly interact with PL/SQL cannot directly interact with the
the database server. database server.
SQL is like the source of data PL/SQL provides a platform where SQL data
that we need to display. will be shown.
Clustered indexes store the data Non-clustered indexes stores only the
information and the data itself. information, and then it will refer you
to the data stored in clustered data.
There can only be one clustered index There can be one or more non-
per table. clustered indexes in a table.
35) Which are joins in SQL? Name the most commonly used SQL
joins?
SQL joins are used to retrieve data from multiple tables into a meaningful result set. It is
performed whenever you need to fetch records from two or more tables. They are used
with SELECT statement and join conditions.
The following are the most commonly used joins in SQL:
o INNER JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN
If we want to get retrieve the student_id and name from the table where student_id is
equal, and course_id is not equal, it can be done by using the self-join:
1. SELECT s1.student_id, s1.name
2. FROM student AS s1, student s2
3. WHERE s1.student_id=s2.student_id
4. AND s1.course_id<>s2.course_id;
Here is the result:
To read more information, click here.
A. UNION: It combines two or more results from multiple SELECT queries into a single
result set. It has a default feature to remove the duplicate rows from the tables. The
following syntax illustrates the Union operator:
1. SELECT columns FROM table1
2. UNION
3. SELECT columns FROM table2;
B. UNION ALL: This operator is similar to the Union operator, but it does not remove
the duplicate rows from the output of the SELECT statements. The following syntax
illustrates the UNION ALL operator:
1. SELECT columns FROM table1
2. UNION ALL
3. SELECT columns FROM table2;
C. INTERSECT: This operator returns the common records from two or more SELECT
statements. It always retrieves unique records and arranges them in ascending order by
default. Here, the number of columns and data types should be the same. The following
syntax illustrates the INTERSECT operator:
1. SELECT columns FROM table1
2. INTERSECT
3. SELECT columns FROM table2;
D. MINUS: This operator returns the records from the first query, which is not found in
the second query. It does not return duplicate values. The following syntax illustrates the
MINUS operator:
1. SELECT columns FROM table1
2. MINUS
3. SELECT columns FROM table2;
To read more information, click here.
46) How to write an SQL query to find students' names start with
'A'?
We can write the following query to get the student details whose name starts with A:
1. SELECT * FROM student WHERE stud_name like 'A%';
Here is the demo example where we have a table named student that contains two
names starting with the 'A' character.
47) Write the SQL query to get the third maximum salary of an
employee from a table named employees.
The following query is the simplest way to get the third maximum salary of an
employee:
1. SELECT * FROM `employees` ORDER BY `salary` DESC LIMIT 1 OFFSET 2
Here is the demo example that shows how to get the third maximum salary of an
employee.
The following are the alternative way to get the third-highest salary of an employee:
A. Using LIMIT Keyword
1. SELECT salary FROM employees
2. ORDER BY salary DESC
3. LIMIT 2, 1;
B. Using Subquery
1. SELECT salary
2. FROM
3. (SELECT salary
4. FROM employees
5. ORDER BY salary DESC
6. LIMIT 3) AS Temp
7. ORDER BY salary LIMIT 1;
C. Using TOP Keyword
1. SELECT TOP 1 salary
2. FROM
3. (SELECT DISTINCT TOP 3 salary
4. FROM employees
5. ORDER BY salary DESC) AS Temp
6. ORDER BY salary ASC;
No DELETE TRUNCATE
.
56) What are the syntax and use of the COALESCE function?
The COALESCE() function evaluates the arguments in sequence and returns the first
NON-NULL value in a specified number of expressions. If it evaluates arguments as
NULL or not found any NON-NULL value, it returns the NULL result.
The syntax of COALESCE function is given below:
1. COALESCE (exp1, exp2, .... expn)
Example:
1. SELECT COALESCE(NULL, 'Hello', 'Javatpoint', NULL) AS Result;
This statement will return the following output:
If we want to get the name column without any duplicate values, the DISTINCT keyword
is required. Executing the below command will return a name column with unique
values.
WHERE HAVING
It does not allow to work with aggregate It can work with aggregate
functions. functions.
This clause can be used with the SELECT, This clause can only be used with
UPDATE, and DELETE statements. the SELECT statement.
To know more differences, click here.