This page is dedicated to SQL queries interview questions with answers. If you know any SQL query interview question, please contribute to this page via comment.
SQL Query refers to a SELECT statement that is written with conditions to retrieve row(s) from table(s). SQL queries are important when it is effectively written to obtain the desired results.
SQL supports nested queries. These queries are written within one another. A sub query is a SELECT statement that is nested within another SELECT statement. SQL clauses are also used in writing sub queries.
Let us understand how different types of sub queries are used in SQL.
Single-row Sub query in SQL
A single-row sub query returns one row from the inner nested query. These type of sub queries uses single-row operators (>, <,>=, <=, <>, =).
EXAMPLE 1: List the employees belonging to the department of JOHN.
In this query, if we analyze closely, we see that we do not know the number of department in which JOHN works. So, first we have to determine the department number of JOHN and then we can find out the employees working in that department. Let’s, first write two separate queries and then we will combine them.
Syntax: SELECT deptno FROM emp WHERE ename = ‘JOHN’;
Syntax: SELECT ename FROM emp WHERE deptno = 7;
Now, we combine the queries into one, we write as below:
Syntax: SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = ‘JOHN’);
EXAMPLE 2: List the name of the employees who do the same job as that of an employee number 5627.
Syntax: SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 5627);
Using GROUP functions in an SQL sub query
You can display data from a main query by using a GROUP function in a sub query to return a single value.
EXAMPLE 3: List the name and salary of the employee who gets salary greater than the minimum salary in the employee table.
Syntax: SELECT ename, sal FROM emp WHERE sal > (SELECT min(sal) FROM emp); ;
EXAMPLE 4: List the employee name and salary of the employee whose salary is greater than the average salary of employees whose doj (date of joining) is before 01-Jan-98.
Syntax: SELECT ename, sal FROM emp WHERE sal > (SELECT avg (sal) FROM emp WHERE doj < ‘01-Jan-98’ );
Using DISTINCT Clause in an SQL sub query
Distinct clause is used in some cases to force a sub query to generate a single value.
EXAMPLE 5: List the details of the department whose manager’s empcode is 6980.
Syntax: SELECT * from dept FROM emp WHERE deptno = (SELECT distinct deptno FROM emp WHERE mgr = ‘6980’ );
Multiple-row SQL sub queries
Sub queries that return more than one row are called multiple row sub queries. Instead of a single-row operator, multiple-row operator is used with a multiple-row sub query. The multiple-row operator accepts one or more values. The operators used are:
Let us understand how each operator is used using multiple-row sub queries.
EXAMPLE 6: List the employee name, salary and deptno of the employees who earn the same salary as the minimum salary for different departments
Syntax: SELECT ename, sal, deptno FROM emp WHERE sal IN (SELECT MIN (sal) FROM emp GROUP BY deptno);
The output is:
In the example above, the inner query is executed first, giving a query result containing three rows: 800, 950, and 1300. The main query block is then processed and uses the values returned by the inner query to complete its search condition.
Structured Query Language (SQL) is a language used to communicate with database. The language is written in the form of commands. These commands are called “Statements”.
In this article, we will understand some of the important SQL features such as types of SQL statements, JOINs and their types, Nested queries and Sub-queries.
Types of SQL Commands
SQL statements are used in different areas of database operations. Based on the database operation to be performed, the statements or commands are categorized into the following categories:
|Data Definition Language (DDL)||Data Manipulation Language (DML)||Data Manipulation Language (DML)|
|Definition||Defines structure of database, deals with how data should reside in the database||Managing data or manipulating data in the database||Controlling the access of database|
|Example||Create Table||Insert, Delete, Update from the table||Granting database access to a user, set privileges to create table|
|Syntax||CREATE||INSERT, DELETE, UPDATE||GRANT, REVOKE|
Let us understand some most commonly used features of SQL that are used to create result sets or retrieve data from the tables.
Using JOINs SQL
In SQL, the related information can be retrieved using “JOIN”. Rows in one table can be joined to the rows in another table according to common values existing in the corresponding columns.
A “JOIN” can be recognized in a SQL SELECT statement if it has more than one table after the FROM keyword.
-- Syntax: SELECT "list-of-columns" FROM table1, table2
To join “n” tables, we require a minimum of “n-1” join conditions. To join three tables, a minimum of two joins are required. This rule does not apply if your table has a concatenated primary key. In this case, more than one column is required to uniquely identify each row. In a “JOIN”, the table names are listed in the FROM clause, separated by comma.
There are four types of “JOINs”. These are:
- Equi Join or Inner Join
- Cartesian Join
- Outer Join
- Self Join
Equi JOIN or Inner JOIN in SQL
If the SQL query is relating to 2 tables using and equality operator, it is called “Equi Join” or “Inner Join”. It will return all the rows from both tables where there is a match.
-- Syntax: SELECT field1, field2, field3 FROM first_table, second_table WHERE first_table.keyfield = second_table.foreign_keyfield;
Cartesian JOIN in SQL
The result set is all combinations of rows when the join condition is omitted. This kind of join is called “Cartesian JOIN”. This kind of join tends to generate a large number of rows, as it involves no condition.
This “JOIN” is useful in finding all the possible combination of rows from different tables.
NOTE: This “JOIN” does not require the tables to have common columns between them.
-- Syntax: SELECT columnname1, columnname2……… FROM tablename1, tablename2…;
Outer Join in SQL
In the “Inner JOIN”, the records will not be considered if there are certain records in one table do not have corresponding values in the second table. Using “Outer JOIN”, these non-corresponding rows also will be considered.
-- Syntax: SELECT table1.column, table2.column,…………… FROM table1, table2 WHERE table1.column = table2.column (+);
In the syntax,
table1.column = is the condition that joins the tables together.
table2.column (+) is the “Outer JOIN” symbol, which can be placed on either
side of the “WHERE” clause condition.
NOTE: We cannot “OUTER JOIN” the same table to more than one other table in a single SELECT statement.
Self JOIN in SQL
It refers to a JOIN of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of command for joining a table to itself is almost the same as that for joining two different tables. In order to differentiate the column from one another, aliases are used, since both the tables have the same name.
-- Syntax: SELECT first.column_name, second.column_name... FROM table1 first, table1 second WHERE first.common_field = second.common_field;
Let us understand another important feature, “query” in SQL. It refers to an SQL statement written to obtain the desired result set(s) from database.
Nested Queries / Sub-queries in SQL
In SQL, you can write nested queries, query within the query. A sub-query is a SELECT statement, which is nested within another SELECT statement.
You can build powerful statements out of simple ones by using sub-queries. Sub-queries can be very useful when you want to select rows from a table with a specified condition, which depends on the data from the table itself.
The inner queries generate values, which are tested to tell the outer query. SQL first evaluates the nested query within the WHERE clause. The return value of inner query is then substituted in the condition of the outer query. The sub-query can be written with the number of SQL clauses as listed below.
- WHERE clause
- HAVING clause
- FROM clause
There are certain rules to be followed when you write nested queries:
- The nested queries must be enclosed in the parenthesis
- The query must appear on the right side of the comparison operator.
- You cannot use the “ORDER BY” clause within the sub-query
Types of Sub-Queries
Following table shows the types of sub-queries and their description:
|Single-row sub-queries||Queries that return only one value from the inner SELECT statement|
|Multiple-row sub-queries||Queries that return more than one value from the inner SELECT statement|
|Multiple-column sub-queries||Queries that return more than one column from the inner SELECT statement|
I have compiled a list important SQL Interview Questions and Answers from the internet. Feel free to correct the any answer or give your view if you feel the answer needs improvement.
1) What is the difference between SQL and SQL Server?
SQL is a language that is used to communicate with relational database management system, whereas SQL server is Microsoft Corporation’s relational database product used to store databases.
2) What do you mean by Embedded SQL statements?
The SQL commands or statements used to incorporate DDL, DML and transaction control statements within the body of a procedural language program, are known as Embedded SQL statements.
The following table provides an overview of commonly used Embedded SQL statements.
|Need and Usage||Embedded SQL Statement|
|Assign variable names||declare|
|Execute SQL statements||execute|
|Retrieve data from database||fetch|
3) What is a Unique Constraint in SQL?
Unique Constraint also referred to as a Unique Key. It allows you to enter unique values in the column. It can also be applied on a group of columns. When it is applied on a group of columns, then each value of the group should be unique. A table can have more than one unique key. The unique key constraint can be applied at a table level as well as a column level.
Column-level Syntax: columnname datatype (size) [constraint constraintname] UNIQUE
4) What is a Primary Constraint in SQL?
A primary key is a unique identifier of a table row. A primary key may be either consists of a single field or group of fields. It is a combination of both the UNIQUE key constraint as well as the NOT NULL constraint.
SQL supports primary keys directly with the Primary Key constraint. When primary key is applied on a single field it is called as Simple Key and when applied on multiple columns, it is called as Composite Primary Key. In a table, there is only one primary key.
Column-level Syntax: columnname datatype (size) [constraint_name] PRIMARY KEY
5) What is a Check Constraint in SQL?
Check constraints allows you to verify validity of data being entered in the table against a set of constants. These constants are valid values.
The check constraint consists of the keyword, CHECK followed by parenthesized conditions. The constraint is specified as logical expression and returns either TRUE or FALSE. If an SQL statement causes the condition to be FALSE, an error message will be displayed.
Column-level Syntax: columnname datatype (size) [constraint constraintname] CHECK (logical expression)
6) What are limitations of Check Constraints in SQL?
The limitations of check constraints in SQL are:
- It cannot refer to special keywords that can have values in them, such as user, sysdate, or rowid.
- It cannot refer to another column of any table.
7) What is a Default Constraint in SQL?
Default constraints allow you to insert default values in the columns where the user does not want to enter the value.
The most common example of this constraint is NULL value, which is inserted in columns not defined with the NOT NULL constraint.
If the user does not enter a value, the default constraint specifies that a value should be inserted in a column.
The default values assignments are defined at the time of create table command. The data type of the default value should match the data type of the column.
8) What is a Foreign Key Constraint in SQL?
Foreign Key constraint is used to establish a relationship among tables. It can be defined for a single column or the combinations of columns, which derive their values, based on the primary key or unique key values from another table.
It is also known as Referential Integrity Constraint as its values correspond to the actual values of the primary key of other table.
9) List types of SQL keys
Keys in SQL are used to relate tables and retrieve records from the related tables. Keys used in SQL are:
- Super Key
- Candidate Key
- Primary Key
- Alternate Key
- Composite Key
- Unique Key
- Foreign Key