SQL Interview Questions and Answer

Clustered and Non-Clustered Index

What is an Index?

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval.

Primary Key Constraint

A table column with this constraint is called as the key column for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. A table can have only one Primary key. Multiple columns can participate on the primary key column. Then, the uniqueness is considered among all the participant columns by combining their values.

Clustered Index

The primary key created for the StudId column will create a clustered index for the Studid column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the Studid column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Student and then expand the Indexes. With the use of the binary tree, now the search for the student based on the studid decreases the number of comparisons to a large amount. There are only one or two Childs. The left side will always have a lesser value and the right side will always have a greater value when compared to parent. The tree can be constructed in the reverse way also. That is, left side higher and right side lower.

Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, AccountType column of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table.

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

How Does a Non-Clustered Index Work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column on the database.

This can be easily explained with the concept of a book and its index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says all about C#. When you glanced at the book, it has all beautiful color pages and shiny papers. But, that is not only the eligibility for a good book right? One you are impressed, you want to see your favorite topic of Regular Expressions and how it is explained in the book. What will you do? I just peeped at you from behind and recorded what you did as below:

1.       You went to the Index page (it has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on page Number 17.

2.       Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.

3.       Your first target is Page 407. You opened a page in the middle, the page is greater than 500.

4.       Then you moved to a somewhat lower page. But it still reads 310.

5.       Then you moved to a higher page. You are very lucky you exactly got page 407. [Yes man you got it. Otherwise I need to write more. OK?]

Different index configurations a table

A table can have one of the following index configurations:

1.    No indexes

2.    A clustered index

3.    A clustered index and many nonclustered indexes

4.    A nonclustered index

5.    Many nonclustered indexes

Difference between a primary key and a unique key

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Difference between DELETE and TRUNCATE commands

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command

1.    TRUNCATE:

1.    TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

2.    TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

3.    TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

4.    You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

5.    TRUNCATE cannot be rolled back.

6.    TRUNCATE is DDL Command.

7.    TRUNCATE Resets identity of the table

2.    DELETE:

1.    DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

2.    If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

3.    DELETE Can be used with or without a WHERE clause

4.    DELETE Activates Triggers.

5.    DELETE can be rolled back.

6.    DELETE is DML Command.

7.    DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

What type of joins have you used?

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can use:

·    INNER JOIN: Returns all rows when there is at least one match in BOTH tables

·    LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

·    RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

·    FULL JOIN: Return all rows when there is a match in ONE of the tables

INNER JOIN Keyword

INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

PS! INNER JOIN is the same as JOIN.

---

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

---

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

---

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

---

Sub Query

• A sub query is a SELECT statement which is embedded in another SELECT statement .
• Sub queries are very useful to achieve complex requirements.
• We can write a sub query in any part of the select statement.
• Sub queries are always enclosed with in parenthesis.
• We can write any number of sub queries in a select statement.
• Execution of the select statement will always start from its sub queries.

Simple sub query

SQL> SELECT * FROM emp                       WHERE job  = (SELECT job FROM emp WHERE ename=’KING’);

Sub Query In From Clause

• In the FROM clause of the SELECT statement we specify tables or views as data sources. How ever along with tables and views we can as well specify sub queries as data sources which are called as inline views
• Inline view is a named sub query in the FROM clause of the main query.

·         In the below example FROM clause contains sub query as a data source.

SQL>SELECT ename,      sal,      rownum rank      FROM (SELECT * FROM emp ORDER BY sal);

Sub Query In Select Clause

• Sub-Query in SELECT clause is nothing but providing SELECT statement in the place of column name.
• Sub query in SELECT should always return one value.
• It can be Independent query or it can be dependent by joining with one of table/view in from clause.

Example2:-

In this example we are trying to fetch highest and lowest salary for each department.

SQL>SELECT empno, ename, sal, deptno, (SELECT MIN (sal) FROM emp WHERE deptno=e.deptno)losal, (SELECT MAX (sal) FROM emp WHERE deptno=e.deptno) hisal   FROM EMP e ORDER BY deptno;

Sub Query In Where Clause

• Sub-Query in WHERE clause is basically used for conditions. i.e. if a condition has to be driven based on a value retrieved by query then we use sub query in where clause.

Example1:-

Display the employees who are working in location ‘CHICAGO’. (this query can be written in different ways and below is one of the way)

SQL>SELECT empno, ename, sal, deptno FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc=’CHICAGO’);

When to Use Joins and Subqueries

Use a join or a subquery any time that you reference information from multiple tables. Joins and subqueries are often used together in the same query. In many cases, you can solve a data retrieval problem by using a join, a subquery, or both. Here are some guidelines for using joins and queries.

       If your report needs data that is from more than one table, then you must perform a join. Whenever multiple tables (or views) are listed in the FROM clause, those tables become joined.

       If you need to combine related information from different rows within a table, then you can join the table with itself.

       Use subqueries when the result that you want requires more than one query and each subquery provides a subset of the table involved in the query.

       If a membership question is asked, then a subquery is usually used. If the query requires a NOT EXISTS condition, then you must use a subquery because NOT EXISTS operates only in a subquery; the same principle holds true for the EXISTS condition.

       Many queries can be formulated as joins or subqueries. Although the PROC SQL query optimizer changes some subqueries to joins, a join is generally more efficient to process.

SQL UNION Operator

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

PS: The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

---

Difference between Union All and Full Outer Join

Union:

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

The following are basic rules for combining the result sets of two queries by using UNION:

·         The number and the order of the columns must be the same in all queries.

·         The data types must be compatible.

• select * from @t1
  union all
  select * from @t2

it will output:

 1
2
2
3

Full Outer Join:

 Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.

·         select *
from @t1 a full join @t2 b
 on a.id = b.id

1      NULL
2        2
NULL  3

SELECT INTO Statement

With SQL, you can copy information from one table into another.

The SELECT INTO statement copies data from one table and inserts it into a new table.

SQL SELECT INTO Syntax

We can copy all columns into the new table:

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Or we can copy only the columns we want into the new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

Tip: The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2013
FROM Customers;

INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

---

Example

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

 If there is any violation between the constraint and the data action, the action is aborted by the constraint

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

we have the following constraints:

·    NOT NULL - Indicates that a column cannot store NULL value

·    UNIQUE - Ensures that each row for a column must have a unique value

·    PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly

·    FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

·    CHECK - Ensures that the value in a column meets a specific condition

·    DEFAULT - Specifies a default value when specified none for this column

UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Removing the constraints from table;

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values.

The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

-      The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

-      The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Views

A view is a virtual table. 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.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

---

What is WHERE CLAUSE

where clause we can restrict or filter the records that we get from select statement.
Let say from the select statement we are fetching 10 records but we wanted to display 3 records.

What is Having CLAUSE

Before we discuss ‘Having’ clause lets understand ‘Group by’ clause first.
Using group by, we can create groups of related information.
Columns used in ‘select’ must be used with ‘group by’, otherwise system does not recognize it as a group by expression.

In the below example we are trying to display sum of salaries at each dept level.
When SQL statement is run, data fetched from the database is grouped first based on deptno and then sum() function is applied to get the desired result.

Ex:      SQL> SELECT deptno, SUM(sal)            FROM emp            GROUP BY deptno;    DEPTNO   SUM(SAL) ---------- ----------         10       8750         20      10875         30       9400

Having Clause:

-       Having clause is used to restrict the records that are grouped using group by clause.

-        Having clause is just like where clause but it can be used only with group by as we cannot use where clause in group by.

-       WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

Difference among UNION, MINUS and INTERSECT?

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.

Self Join and why is it required?

Self Join is the act of joining one table with itself.

In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:

SELECT e.name EMPLOYEE, m.name MANAGER

FROM EMPLOYEE e, EMPLOYEE m

WHERE e.mgr_id = m.id

What is a database transaction?

In databases, a transaction is a set of separate actions that must all be completely processed, or none processed at all. When you think of a transaction, you should think of the phrase “all or nothing”, because that is a defining feature of database transactions – either every part of the transaction is completed, or nothing at all.

An example would be transferring some funds from one bank customer to another. This scenario would have to both credit one customer and debit another – requiring updates to different rows in table, but would be considered a single transaction.

ACID

How can we transpose a table using SQL (changing rows to column or vice-versa) ?

The usual way to do it in SQL is to use CASE statement or DECODE statement.

Generate row number in SQL Without ROWNUM

Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num

FROM EMPLOYEE o

order by row_num

Select first 5 records from a table

It depends on many uncontrollable factors such as how database works at that moment of execution etc. So the question should really be – “how to select any 5 records from the table?”

In Oracle,

SELECT *

FROM EMP

WHERE ROWNUM <= 5;

In SQL Server,

SELECT TOP 5 * FROM EMP;

Another way without rownum and top

SELECT  name

FROM EMPLOYEE o

WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5