SQL Interview Questions - SQL Interview

SQL Interview Questions - SQL Interview

SQL stands for Structured Query Language. SQL is a standard language for storing, manipulating and retrieving data in databases.

In this article, you will get the most common and useful SQL interview questions and answers. If you prepare all these questions properly you can easily clear the interview. In this article, we have covered all the questions and programs of SQL.

Let’s start the interview.

Que 1:- What do you mean by SQL?

Ans:- SQL stands for Structured Query Language. SQL is a standard language for storing, manipulating and retrieving data in databases.

Que 2:- What is the difference between DELETE and TRUNCATE query in SQL?

Ans:- DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

Que 3:- What is a Database?

Ans:- A database is a collection of data organized in tables. A flat database is where all the information about any one event or transaction is stored in a single row in one large single table. However, a database can use Structured Query Language (SQL) to access and update data.

Que 4:- What is DBMS?

Ans:- A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

Que 5:- What are the tables?

Ans:- The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format.

Que 6:- What are different types of statements in SQL?

Ans:-  Data Definition Language (DDL) Statements

Data definition statement are used to define the database structure or table.

Statement Description
CREATE Create new database/table.
ALTER Modifies the structure of database/table.
DROP Deletes a database/table.
TRUNCATE Remove all table records including allocated table spaces.
RENAME Rename the database/table.

Data Manipulation Language (DML) Statements

Data manipulation statement is used for managing data within the table object.

Statement Description
SELECT Retrieve data from the table.
INSERT Insert data into a table.
UPDATE Updates existing data with new data within a table.
DELETE Deletes the records rows from the table.
MERGE MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not.
LOCK TABLE LOCK TABLE statement to lock one or more tables in a specified mode. Table access denied toa otherusers for the duration of your table operation.
CALLEXPLAIN PLAN Statements are supported in PL/SQL only for executed dynamically. CALL a PL/SQL program or EXPLAIN PATH access the data path.

Data Control Language (DCL) Statements

Data control statement are use to give privileges to access limited data.

Statement Description
GRANT Gives privileges to user for accessing database data.
REVOKE Take back for given privileges.
ANALYZE ANALYZE statement to collect statistics information about index, cluster, table.
AUDIT To track the occurrence of a specific SQL statement or all SQL statements during the user sessions.
COMMENT Write comment to the data table.

Transaction Control Statement (TCS)

Transaction control statement are useto apply the changes permanently save into database.

Statement Description
COMMIT Permanent work save into database.
ROLLBACK Restore database to original form since the last COMMIT.
SAVEPOINT Create SAVEPOINT for later use ROLLBACK the new changes.
SET TRANSACTION SET TRANSACTION command set the transaction properties such as read-write/read only access.

Session Control Statements (SCS)

Session control statement are manage properties dynamically of a user session.

Statement Description
ALTER SESSION ALTER SESSION statement to modify conditions or parameters that are affect to your database connection.
SET ROLE SET ROLE statement to enable or disable the roles that are currently enabled for the session.

Que 7:- What are the differences between SQL and PL/SQL?

Ans:-

SQL PL/SQL
SQL is a Structured Query Language used to issue a single query or execute a single insert/update/delete. PL-SQL is a  programming language SQL, used to write full programs using variables, loops,operators etc. to carry out multiple selects/inserts/updates/deletes.
SQL may be considered as the source of data for our reports, web pages and screens. PL/SQL can be considered as the application language similar to  Java or PHP. It might be the language used to build, format and display those reports, web pages and screens.
SQL is a data oriented language used to select and manipulate sets of data. PL/SQL is a procedural language used to create applications.
SQL is used to write queries, DDL and DML statements. PL/SQL is used to write program blocks, functions, procedures triggers,and packages.
SQL is executed one statement at a time. PL/SQL is executed as a block of code.
SQL is declarative, i.e., it tells the database what to do but not how to do it. Whereas, PL/SQL is procedural, i.e., it tells the database how to do things.
SQL can be embedded within a PL/SQL program. But PL/SQL can’t be embedded within a SQL statement.

Que 8:- What is a primary key?

Ans:- A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.

Que 9:- What is a unique key?

Ans:- Unique Key in SQL. A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. You can say that it is little like a primary key but it can accept only one null value and it cannot have duplicate values.

Que 10:- What is the difference between BETWEEN and IN operators in SQL?

Ans:- BETWEEN:-

The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition will return the records where expression is within the range of value1 and value2.

SELECT Fname, Lname
FROM Employee
where DOB
BETWEEN '1985-01-01' AND '1990-12-30';

IN:-

IN operator allows you to easily test if the expression matches any value in the list of values. It is used to remove the need of multiple OR condition in SELECT, INSERT, UPDATE or DELETE. You can also use NOT IN to exclude the rows in your list.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (list_of_values);

Que 11:- How do we use DISTINCT clause?

Ans:- The distinct keyword is used in conjunction with the select keyword. It is helpful when there is a need for avoiding the duplicate values present in any specific columns/table. When we use distinct keyword only the unique values are fetched.

SELECT DISTINCT column1,column2 FROM table_name
column1 , column2: names of the fields of the table
table_name: from where we want to fetch
This query will return all the unique combination of rows in the table with fields
column1 , column2. 

Que 12:- What are joins in SQL?

Ans:- A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

LEFT JOIN: This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

RIGHT JOIN: RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

FULL JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

Que 13:-What is a foreign key?

Ans:- SQL Foreign Key. A foreign key is a column (or columns) that references a column (most often the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

Que 14:- What is the difference between CHAR and VARCHAR?

Ans:- CHAR is a fixed length string data type, so any remaining space in the field is padded with blanks. CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds.

VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information.  For example, if you set a VARCHAR(100) data type = ‘Jen’, then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all.

Que 15:- What are different Clauses used in SQL?

Ans:- 

CONSTRAIN :- A CONSTRAINT clause is an optional part of a CREATE TABLE statement or ALTER TABLE statement.  A constraint is a rule to which data must conform. Constraint names are optional.

FOR UPDATE:-  Clause is an optional part of a SELECT statement. Cursors are read-only by default. The FOR UPDATE clause specifies that the cursor should be updatable, and enforces a check during compilation that the SELECT statement meets the requirements for an updatable cursor.

FROM:- clause is a mandatory clause in a Select Expression. It specifies the tables (TableExpression) from which the other clauses of the query can access columns for use in expressions.

GROUP BY:- clause, part of a SelectExpression, groups a result into subsets that have matching values for one or more columns. In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes.

HAVING:- clause restricts the results of a GROUP BY in a Select Expression. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause. It can, however, refer to constants, aggregates, and special registers.

ORDER BY:- clause is an optional element of a SELECT statement. An ORDER BY clause allows you to specify the order in which rows appear in the ResultSet.

The result offset clause:- provides a way to skip the N first rows in a result set before starting to return any rows. The fetch first clause, which can be combined with the result offset clause if desired, limits the number of rows returned in the result set. The fetch first clause can sometimes be useful for retrieving only a few rows from an otherwise large result set, usually in combination with an ORDER BY clause. The use of this clause can give efficiency benefits. In addition, it can make programming the application simpler.

WHERE clause is an optional part of a SelectExpression, DELETE statement, or UPDATE statement. The WHERE clause lets you select rows based on a boolean expression. Only rows for which the expression evaluates to TRUE are returned in the result, or, in the case of a DELETE statement, deleted, or, in the case of an UPDATE statement, updated.

WHERE CURRENT OF:- clause is a clause in some UPDATE and DELETE statements. It allows you to perform positioned updates and deletes on updatable cursors. For more information about updatable cursors, see SELECT statement.

Que 16:- What are Constraints?

Ans:- Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy andreliability of the data in the database. Constraints could be either on a column level or a table level.

Que 17:- Write an SQL query to find names of employee start with ‘D’?

Ans:- SQL query to find names of employee start with ‘D’  is following.

select employee_name 
from employees
where employee_name LIKE 'D%'
order by employee_name

Que 18:- What is normalization in SQL?

Ans:- Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

It divides larger tables to smaller tables and links them using relationships.

Que 19:- What is Denormalization?

Ans:- Denormalization is a strategy that database managers use to increase the performance of a database infrastructure. It involves adding redundant data to a normalized database to reduce certain types of problems with database queries that combine data from various tables into a single table. The definition of denormalization is dependent on the definition of normalization, which is defined as the process of organizing a database into tables correctly to promote a given use.

Que 20:- What are all the different normalizations?

Ans:- Normalization rules are divided into the following normal forms:

  1. First Normal Form
  2. Second Normal Form
  3. Third Normal Form
  4. BCNF
  5. Fourth Normal Form

First Normal Form (1NF)

For a table to be in the First Normal Form, it should follow the following 4 rules:

  1. It should only have single(atomic) valued attributes/columns.
  2. Values stored in a column should be of the same domain
  3. All the columns in a table should have unique names.
  4. And the order in which data is stored, does not matter.

Second Normal Form (2NF)

For a table to be in the Second Normal Form,

  1. It should be in the First Normal form.
  2. And, it should not have Partial Dependency.

Third Normal Form (3NF)

A table is said to be in the Third Normal Form when,

  1. It is in the Second Normal form.
  2. And, it doesn't have Transitive Dependency.

Boyce and Codd Normal Form (BCNF)

Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

  1. R must be in 3rd Normal Form
  2. and, for each functional dependency ( X → Y ), X should be a super Key.

Fourth Normal Form (4NF)

A table is said to be in the Fourth Normal Form when,

  1. It is in the Boyce-Codd Normal Form.
  2. And, it doesn't have Multi-Valued Dependency.

Que 21:- What is a View?

Ans:- In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Que 22:-What is the difference between clustered and non clustered index in SQL?

Indexes are used to speed-up the query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow.

The same applies to indexes in databases. Without indexes, a DBMS has to go through all the records in the table in order to retrieve the desired results. This process is called table-scanning and is extremely slow. On the other hand, if you create indexes, the database goes to that index first and then retrieves the corresponding table records directly.

There are two types of Indexes in SQL Server:

  1. Clustered Index
  2. Non-Clustered Index

Clustered Index

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in the only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

Non-Clustered Indexes

A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.

It is important to mention here that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index data is stored in the specified order. The index contains column values on which the index is created and the address of the record that the column value belongs to.

When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.

Que 23:- What are transaction and its controls?

Ans:- Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.

Incomplete steps result in the failure of the transaction. A database transaction, by definition, must be atomic, consistent, isolated and durable. These are popularly known as
ACID properties.

implement Transactions

Following commands are used to control transactions. It is imortant to note that these statements cannot be used while creating tables and are only used with the DML Commands such as – INSERT, UPDATE and DELETE.

  1. SET TRANSACTION: Places a name on a transaction.
    Syntax:
    SET TRANSACTION [ READ WRITE | READ ONLY ];
    
  2. COMMIT: If everything is in order with all statements within a single transaction, all changes are recorded together in the database is called committed. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
  3. ROLLBACK: If any error occurs with any of the SQL grouped statements, all changes need to be aborted. The process of reversing changes is called rollback. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
  4. SAVEPOINT: creates points within the groups of transactions in which to ROLLBACK.
    A SAVEPOINT is a point in a transaction in which you can roll the transaction back to a certain point without rolling back the entire transaction.
  5. RELEASE SAVEPOINT:- This command is used to remove a SAVEPOINT that you have created.

Que 24:-What are properties of the transaction?

Ans:- A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

  • Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.

  • Consistency − The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

  • Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

  • Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

Que 25:- How many Aggregate Functions are available there in SQL?

Ans:- In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

  1. Count()
  2. Sum()
  3. Avg()
  4. Min()
  5. Max()

Count():

Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary):  Return number of distinct Non Null values over the column salary.

Sum():

sum(salary):  Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

Avg():

Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4

Min():

Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.

Que 26:- Difference between where and having clause ?

Ans:- HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

WHERE Clause

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comments

Sunil Kumar

Very nice content. http://www.phpinterview.in/web-development/php-interview-questions-and-answers

Your Rating: