SQL Interview Questions

What is SQL?

SQL stands for structured query language. It is a database language used for database creation, deletion, fetching rows and modifying rows etc.

What are the usages of SQL?

• To execute queries against a database
• To retrieve data from a database
• To inserts records in a database
• To updates records in a database
• To delete records from a database
• To create new databases
• To create new tables in a database
• To create views in a database

 What are the subsets of SQL?

1. Data definition language (DDL)
2. Data manipulation language (DML)
3. Data control language (DCL)

 What is data definition language?

Data definition language(DDL) allows you to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.

What is data manipulation language?

Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.

• Insert data into database
• Retrieve data from the database
• Update data in the database
• Delete data from the database

 What is data control language?

Data control language allows you to control access to the database. It includes two commands GRANT and REVOKE.

GRANT: to grant specific user to perform specific task.

REVOKE: to cancel previously denied or granted permissions.

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

There are mainly two type of indexes in SQL, Clustered index and non-clustered index. The differences between these two indexes is very important from SQL performance perspective.

1) One table can have only one clustered index but it can have many non-clustered index. (approximately 250).

2) clustered index determines how data is stored physically in table. Actually clustered index stores data in cluster, related data is stored together so it makes simple to retrieve data.

3) reading from a clustered index is much faster than reading from non-clustered index from the same table.

4) clustered index sort and store data rows in the table or view based on their key value, while non cluster have a structure separate from the data row.

What is "TRIGGER" in SQL?

Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table.

Actually triggers are special type of stored procedures that are defined to execute automatically in place or after data modifications.

What is a constraint? Tell me about its various levels.

Constraints are representations of a column to enforce data entity and consistency. There are two levels:

column level constraint

table level constraint

What is the difference between DELETE and TRUNCATE statement in SQL?

No.	DELETE	TRUNCATE
1)	DELETE is a DML command.	TRUNCATE is a DDL command.
2)	We can use WHERE clause in DELETE command.	We cannot use WHERE clause with TRUNCATE
3)	DELETE statement is used to delete a row from a table	TRUNCATE statement is used to remove all the rows from a table.
4)	DELETE is slower than TRUNCATE statement.	TRUNCATE statement is faster than DELETE statement.
5)	You can rollback data after using DELETE statement.	It is not possible to rollback after using TRUNCATE statement.

What is ACID property in database?

ACID property is used to ensure that the data transactions are processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.

Difference between group by clause and having clause.

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns
Aggregate functions are like SUM, COUNT, MIN, MAX and AVG

The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns

The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

How to find second highest or maximum salary of Employee in SQL?

 Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery.

SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);

Find the nth highest salary using the TOP keyword in SQL Server

We can also use the TOP keyword (for databases that support the TOP keyword, like SQL Server) to find the nth highest salary. Here is some fairly simply SQL that would help us do that:

SELECT TOP 1 Salary

FROM (

      SELECT DISTINCT TOP N Salary

      FROM Employee

      ORDER BY Salary DESC

      ) AS Emp

ORDER BY Salary

To understand the query above, first look at the subquery, which simply finds the N highest salaries in the Employee table and arranges them in descending order.

Find the nth highest salary in MySQL

In MySQL, we can just use the LIMIT clause along with an offset to find the nth highest salary. If that doesn’t make sense take a look at the MySQL-specific SQL to see how we can do this:

SELECT Salary FROM Employee

ORDER BY Salary DESC LIMIT n-1,1

Note that the DESC used in the query above simply arranges the salaries in descending order – so from highest salary to lowest. Then, the key part of the query to pay attention to is the “LIMIT N-1, 1″. The LIMIT clause takes two arguments in that query – the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. So, it’s saying that the offset of the first row to return should be N-1, and the max number of rows to return is 1. What exactly is the offset? Well, the offset is just a numerical value that represents the number of rows from the very first row, and since the rows are arranged in descending order we know that the row at an offset of N-1 will contain the (N-1)th highest salary.

Find the nth highest salary in SQL Server

In SQL Server, there is no such thing as a LIMIT clause. But, we can still use the offset to find the nth highest salary without using a subquery – just like the solution we gave above in MySQL syntax. But, the SQL Server syntax will be a bit different. Here is what it would look like:

SELECT Salary FROM Employee

ORDER BY Salary DESC OFFSET N-1 ROW(S)

FETCH FIRST ROW ONLY

Note that I haven’t personally tested the SQL above, and I believe that it will only work in SQL Server 2012 and up. Let me know in the comments if you notice anything else about the query.

Find the nth highest salary in Oracle using rownum

Oracle syntax doesn’t support using an offset like MySQL and SQL Server, but we can actually use the row_number analytic function in Oracle to solve this problem. Here is what the Oracle-specific SQL would look like to find the nth highest salary:

select * from (

  select Emp.*,

row_number() over (order by Salary DESC) rownumb

from Employee Emp

)

where rownumb = n;  /*n is nth highest salary*/

Find the nth highest salary in Oracle using RANK

Oracle also provides a RANK function that just assigns a ranking numeric value (with 1 being the highest) for some sorted values. So, we can use this SQL in Oracle to find the nth highest salary using the RANK function:

select * FROM (

select EmployeeID, Salary

,rank() over (order by Salary DESC) ranking

from Employee

)

WHERE ranking = N;

The rank function will assign a ranking to each row starting from 1. This query is actually quite similar to the one where we used the row_number() analytic function, and works in the same way as well.

Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
You can use the following query to select distinct records:
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)

to Delete:
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

Joins

SQL joins are used to combine rows from two or more tables.

Different SQL JOINs

Before we continue with examples, we will list the types of 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

 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 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.

Most tables should have a primary key, and each table can have only ONE primary key.

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.