Here I provide Interview questions on SQL which will help you to face the interview and give some confidence…SQL is a very important topic for Computer Science students. Every Software engineer must know these basic interview questions on SQL. All the best!
Table of Contents
Basic Interview Questions on SQL for Freshers
1. What is SQL?
Ans: SQL stands for ‘Structured Query Language’ which was developed by IBM in 1974. It is used for maintaining data in a rational database management system. SQL allows us to retrieve multiple data entries from a database using a single command.
2. What are Queries in SQL?
Ans: In SQL, we write commands in the form of queries. These queries are responsible for making any changes that will happen in our database. We can write queries to create a database, add/ delete/modify our data, drop the database. SQL queries can be written in uppercase as well as lowercase format.
3. How is data stored in a database?
The data is stored in a database in the form of tables. The tables consist of similar types of related data which stores data in the form of columns and rows.
Columns define the type of data that it will store. A column stores similar types of data in a vertical format. Columns are also known as ‘records’.
For example, if a column has been set as an integer data type, then it will only store integer data and not any other data type.
Rows are the actual data that is stored as input to the database. A row stores data in a horizontal format. Rows are also known as ‘tuples’.
4. What are the comments in SQL?
Comments are used as a tool to describe the code. A comment has no meaning to the query compiler. It is used to explain the purpose of the queries which are to be executed on the database. The compiler will not execute these comments, they are written to provide the information about the code which can be used later to understand the purpose of the code or to notify other database developers about the same.
Note:- Comments are a very important part of any computer language. So, our team has decided to let you know about comments first before writing any queries. It is also a good habit to write comments when writing queries.
- Comments are of two types in SQL:
(a) Single Line Comment-
Single Line Comment begins with two hyphens (–). Anything was written in this line after it will not be read by the compiler. The queries above and below of a single-line comment will be executed.
— This is a Single Line Comment
- (b) Multi-Line Comment-
Multi-Line Comments begins with a forwarding slash and an asterisk (/) and ends with an asterisk and a forward slash (/). Anything written between it will not be read by the compiler.
5. What are data types in SQL?
The data type defines the type of data that can be stored inside a column. Every column must have a data type as it is very important for SQL to know what kind of data is stored and will be retrieved from that column.
Data Types are of many types out of which some are commonly used like text, integer, number, boolean, date, time, long and double.
6. What is an INSERT INTO statement?
An INSERT INTO statement allows us to insert data into our database in the form of rows.
INSERT INTO tableName VALUES (‘value1’, ‘value2’, ‘value3’);
7. What is a SELECT statement?
The select statement allows us to select the data which we want to be retrieved from the database.
SELECT columnName FROM tableName;
To select the entire table from the database we can use the following query, which will retrieve every column from the table.
SELECT * FROM tableName;
— Asterisk (*) will select every column and all rows
8. What is the WHERE clause?
The WHERE clause allows us to filter the data according to the condition applied. Only the data which satisfied the condition will be retrieved.
SELECT columnName FROM tableName WHERE value>20;
— This condition will return data that has a value greater than 20
9. What are AND and OR operators?
Both AND and OR operators are used to adding multiple conditions to the WHERE clause.
In AND operator, all conditions need to be satisfied for the query to return the data.
SELECT columnName FROM tableName WHERE value>20 AND value<30;
— This query will return only when both conditions are met,
— those records whose value is greater than 20 and less than 30 will be printed,
–all other values will be ignored.
In the OR operator, the query will return the data if anyone condition is satisfied.
SELECT columnName FROM tableName WHERE value>20 OR value<30;
— In this query, only one condition needed to be true for execution
— If somehow 1st condition is true and 2nd is false or vice-versa, the query will be executed
10. What is the ORDER BY statement?
ORDER BY statement allows us to sort the order of the data either in ascending or descending order. By default, SQL sorts the data in ascending order.
For sorting the data in descending order we’ll use the following query:
SELECT columnName FROM tableName WHERE value>20 ORDER BY personName DESC;
11. What is the GROUP BY statement?
THE GROUP BY statement allows us to group up records with the same values. The main purpose of the GROUP BY statement is that it filters data and shows us only distinctive data.
SELECT statesColumn FROM tableName GROUP BY statesColumn;
— This query will return the name of each state once even if there are multiple rows with the same state name
12. What is the TOP clause?
The TOP clause is to return top records from a column. This clause is used in a database that has a huge amount of data i.e., thousands of data rows. Returning the entire column will negatively impact the performance of the query. So, we use the TOP clause to filter and print only a limited number of records.
SELECT TOP 100 statesColumn FROM tableName;
— This query will return only the top 100 states from the column
By default, the TOP clause will return records in ascending order(from the top of the column), if we want to print the bottom records of the column, then we can use a group by clause and put it in descending order.
SELECT TOP 100 statesColumn FROM tableName GROUP BY statesColumn DESC;
— This query will return only the bottom 100 states from the column
In MySQL Database Management Software we use LIMIT instead of TOP, both have the same functionalities but have different syntax.
13. What is the IN operator?
The IN operator is an alternate OR operator. It also allows us to apply multiple conditions to our queries just like OR, but it is much easier to write multiple conditions using IN.
SELECT statesColumn FROM tableName WHERE statesColumn IN (‘Delhi’, ‘Bihar’, ‘Uttar Pradesh’);
— This query will return records from statesColumn where states name are Delhi, Bihar, Uttar Pradesh
14. What is an ALIAS?
An ALIAS is used to provide a temporary name to a column, table, or even a database. When we are working with a huge amount of data in an organization, we keep descriptive names for our databases, tables, and columns for references but executing queries becomes difficult and non-readable as the names are of several characters which makes it harder to operate queries. Therefore, the concept of ALIAS was used.
SELECT statesColumn AS sC FROM countryName AS CN;
— Here both column and table have been renamed using ALIAS
15. What is a Having clause?
HAVING clause works just like a WHERE clause, but the WHERE clause doesn’t allow us to perform aggregate functions in its conditions. So, to have aggregate functions in our conditions we use a HAVING clause.
SELECT statesColumn FROM countryName HAVING SUM(population)>5000;
— This query will only return records where the sum of the population is more than 5000
16. What is BETWEEN operator?
The BETWEEN Operator will return all the records which fall under a specific range. This operator accepts two values: starting and ending values.
SELECT columnName FROM tableName WHERE condition BETWEEN starting value AND ending value;
SELECT statesColumn FROM countryName WHERE population BETWEEN 1000 AND 5000;
— This query will show only those states whose population lies between 1000 to 5000
17. What is the UNION operator?
The UNION operator is used to combine two or more select statements.
For UNION to operate following conditions must be satisfied:
* Every select statement must have the same number of columns in their tables.
* The positioning of columns must be the same in both tables
* Every column must have the same data type as the other table’s column.
SELECT columnName1 from tableName1
SELECT columnName2 from tableName2;
By default, UNION only combines distinct values between two tables. To load duplicate data too, we must use UNION ALL.
SELECT columnName1 from tableName1
SELECT columnName2 from tableName2;
18. What is the use of CASE?
The CASE statement is used to set up multiple conditions in our queries. The CASE acts exactly like if/else conditional statements. So, if the condition is true then the following result will be executed, otherwise, if the condition is not true then it’ll check other if statements if in case all conditions aren’t true then the else statement will be executed.
WHEN condition1 THEN result1;
WHEN condition2 THEN result2;
WHEN condition3 THEN result3;
ELSE default result
— The CASE must begin with the keyword CASE and end with the END keyword.
19. What are JOINS?
A JOIN is used to combine two or more tables into a single table. Two tables can only be joined if they have a common/related column between them.
There are 4 types of JOINS:
(i) INNER JOIN
(ii) LEFT JOIN
(iii) RIGHT JOIN
(iv) FULL JOIN
20. What are INNER JOINS?
INNER join is a join that returns only those values which match in both tables and skips non-matching data records.
INNER JOIN can also be referred to as ‘intersection’ which we must have studied during our time in school.
SELECT * from tableName1 INNER JOIN tableName2 ON tableName1.matchingColumn = tableName2.matchingColumn;
— Here matchingColumn is that column which has matching data records in both tables.
21. What is LEFT JOIN?
LEFT JOIN is a join that returns all records from the first table(left table) and only the matching records from the second table(right table). The records of all unmatching values return NULL.
SELECT * from tableName1 LEFT JOIN tableName2 ON tableName1.matchingColumn = tableName2.matchingColumn;
22. What is RIGHT JOIN?
RIGHT JOIN is a join that returns all records from the second table(right table) and only the matching records from the first table(first table). The records of all unmatching values return NULL. It is the opposite of LEFT Join but has the same functionality.
SELECT * from tableName1 RIGHT JOIN tableName2 ON tableName1.matchingColumn = tableName2.matchingColumn;
23. What is FULL JOIN?
FULL JOIN or FULL OUTER JOIN allows us to return records of both tables when it has matching data and returns NULL in place of non-matching data. The amount of data returned through FULL JOIN is relatively much larger in size as compared to LEFT or RIGHT JOIN.
SELECT * from tableName1 FULL JOIN tableName2 ON tableName1.matchingColumn = tableName2.matchingColumn;
24. What is the use of ANY and ALL?
ANY and ALL operators are used with conditional clauses like WHERE and HAVING.
The ANY operator returns the result if any of the conditions mentioned in the sub-query are true/satisfied.
Whereas, the ALL operator returns the result only if all the conditions mentioned in the sub-query are satisfied.
The syntax for ALL:
SELECT columnName FROM tableName WHERE condition OPERATOR ALL (SELECT columnName1 FROM tableName1 WHERE condition1);
The syntax for ANY:
SELECT columnName FROM tableName WHERE condition OPERATOR ANY (SELECT columnName1 FROM tableName1 WHERE condition1);
25. How to delete a database/table?
To delete an existing database we use a DROP DATABASE statement.
Note: By dropping a database you’ll lose all the data stored inside that database. So, make sure that you are sure when dropping the database.
DROP DATABASE databaseName;
26. How to add, delete, modify columns using ALTER table?
The ALTER TABLE statement is used to add, delete and modify columns in an existing database.
The syntax for Adding Columns:
ALTER TABLE tableName ADD column-name data-type;
The syntax for Deleting Columns:
ALTER TABLE tableName DROP COLUMN columnName;
The syntax for Modifying Columns:
ALTER TABLE tableName ALTER COLUMN column-name data-type;
— The ALTER COLUMN keywords allows us to change the data type of an existing column
27. What are Constraints in SQL?
The Constraints are rules which can be applied to columns and tables of a database. Generally, constraints are applied at the time of creating tables and columns of a database but we can also add constraints by altering the tables and columns.
Constraints allow us to make sure that the data which is stored in our database is of the right type and also reliable. If the data we insert into our database does not follow the rules set up by constraint, it simply does not get inserted into our database.
The constraints are of the following types:
* NOT NULL
* UNIQUE KEY
* PRIMARY KEY
* FOREIGN KEY
28. What is the use of UNIQUE constraints?
The UNIQUE constraint makes sure that the values stored inside a column are all unique, which means no two-column values can be the same. In a table, multiple columns can have unique constraints, also column values can be null in unique.
CREATE TABLE tableName(RollNo int UNIQUE, Name text);
— This query will make sure that all roll number entries are unique
29. What is the use of PRIMARY KEY constraints?
The PRIMARY KEY constraint works exactly like the UNIQUE constraint, no two-column values can be the same. But in this case, only one column in the table can have a Primary Key constraint. No column can have null values having Primary Key constraint.
CREATE TABLE tableName(RollNo int PRIMARY KEY, Name text);
30. What is the use of a Foreign Key?
The FOREIGN KEY allows us to link two tables together. A table containing a foreign key refers to another table containing a primary key. No other column of a table can have the same data as a column with a primary key constraint, so to link the data to the primary key value we use the concept of the foreign key.
CREATE TABLE table one (RollNo int PRIMARY KEY, Name text);
CREATE TABLE table two (SNo int REFERENCES table one(RollNo), Address text);
31. What is the use of CHECK constraints?
CHECK constraint allows us to check the value stored in the column or table in the form of range. A CHECK constraint is applied on the column of a table, then only those values which satisfy the conditions will be stored in the column.
CREATE TABLE tableName (RollNo int PRIMARY KEY, Name text, CHECK (RollNo BETWEEN 1 AND 100);
— This table will store values till the Roll No values are between 1 to 100
32. What is the use of DEFAULT constraints?
The DEFAULT constraint allows us to specify the default value for a column. This default value will be stored to all entries of the column if no other value is added.
CREATE TABLE tableName (Name text, Occupation text DEFAULT ‘Student’);
INSERT INTO tableName values (‘Gary’, DEFAULT);
— This default keyword will store student as the occupation of Gary by default
33. What is AUTO-INCREMENT?
AUTO-INCREMENT allows a unique number to get generated automatically whenever a new record entry is made into the table. Note that a column must be defined as a key for it to be auto-incremented.
CREATE TABLE tableName (RollNo int key AUTO_INCREMENT, Name text);
— Here a new sequential number will be stored in the roll no column, whenever an entry is made
Interview Questions on SQL Frequently Asked Queries
SQL queries t find the second highest salary of employees.
Ans: select MAX(Salary)
WHERE Salary NOT IN (select MAX(Salary) from Employees);
How do you find all employees which are also managers?
Ans: SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;
Get employee details from the employee table whose joining year is “2013”.
Ans: Select *from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103)7,4)=’2013′
Get department, no of employees in a department, total salary with respect to a department from employee table order by total salary descending.
Ans: Select DEPARTMENT, count(FIRST_NAME), sum(SALARY) Total_Salary from employee group by Department order by Total_Salary descending.
Get department-wise average salary from employee table order by salary ascending.
Ans: select DEPARTMENT,avg (SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc
Get department-wise maximum salary from employee table order by salary ascending.
Ans: select DEPARTMENT, max(SALARY)MaxSalary from employee group by DEPARTMENT order by MaxSalary asc
Get department-wise minimum salary from employee table order by salary ascending.
Ans: select DEPARTMENT, min(SALARY)MinSalary from employee group by DEPARTMENT order by MinSalary asc
How to fetch data that are common in two query results?
Ans: select *from EMPLOYEE where EMPLOYEE_ID INTERSECT select *from EMPLOYEE where EMPLOYEE_ID < 4
Write Syntax to drop the primary key on the employee table.
Ans: ALTER TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;
Write Syntax to drop the foreign key on the employee table.
Ans: ALTER TABLE INCENTIVES drop CONSTRAINTS INCENTIVES_FK;
Most Important Interview Questions on SQL
What is SQL injection?
Ans: SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL Server for parsing and execution. All statements have to check for vulnerabilities as it executes all syntactically valid queries that it receives. Even parameters can be manipulated by skilled and experienced attackers.
What is CDC?
Ans: CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server 2008.
What do you mean by XML datatype?
Ans: XML Data type is used to stored XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
Which command is used for user-defined error messages?
Ans: RAISEERROR is the command used to generate and initiates error processing for a given session. Those user-defined messages are stored in sys.messages table.
What is TABLESAMPLE?
TABLESAMPLE is used to extract samples of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.
How to delete duplicates rows in SQL Server?
Ans: Duplicates rows can be deleted using the CTE and ROW NUMBER feature of SQL Server.
What is the use of @@SPID?
Ans: A @@SPID returns the session ID of the current user process.
What is Collation?
Ans: Collation is defined to specify the sort order in a table. There are three types of sort order –
- Case Sensitive
- Case Insensitive
What is Bulkcopy in SQL?
Ans: Bulkcopy is a tool used to copy a large amount of data from Tables. This tool is used to load a large amount of data in SQL Server.
What are the types of triggers?
There are four types of triggers and they are:
- Instead of
Online Quiz Interview Questions on SQL
1. PL/SQL is the procedural extension of SQL.
a) is True
b) is False
c) None of the above
2. A two-tier model works between a browse and a database server.
a) is True
b) is False
c) None of the above
3. Which of the following is not a part of an Extended events package?
4. Which of the following functions can be used in a CATCH block to return information about the error?
c) CATCH block
5. Which of the option is used, If rows are deleted from the parent table, referenced rows field will automatically be set to null in the child table.
a) ON UPDATE CASCADE
b) ON DELETE CASCADE
c) ON DELETE SET NULL
Hope you have now some good idea on Interview Questions on SQL. It is written by @TechNark and @Shree++. If you want some more download the app in play store SQL for Beginner and SQL Interview Queries app which will design by @TechNark and @Shree++