iRaylab

Monday, September 29, 2014

SQL DDL & DML interview questions and answers

1. What is the difference between TRUNCATE, DELETE and DROP?

Keyword:
DELETE removes some or all rows based on WHERE clause, can ROLLBACK;
TRUNCATE removes all rows, cannot ROLLBACK;
DROP removes a table, cannot ROLLBACK.

Answer:
DELETE is a DML statement used to remove some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Delete operation will cause all DELETE triggers on the table to fire.

TRUNCATE is a DDL statement used to remove all rows from a table. TRUNCATE operation cannot be rolled back and no triggers will be fired, so it is faster and doesn't use as much undo space as a DELETE.

DROP is a DDL statement used to remove a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. DROP operation cannot be rolled back.

2. What is the difference between WHERE clause and HAVING clause?

Keyword:
HAVING specifies a search condition for an aggregate, used after a GROUP BY.

Answer:
WHERE clause is used to specify a search condition for the rows returned. WHERE can be used with SELECT, UPDATE and DELETE. WHERE is used before a GROUP BY clause.
HAVING clause is used to specify a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used after a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

An example of using WHERE without GROUP BY: get all departments whose department id is greater than 2
SELECT department_id, department_name
FROM departments
WHERE department_id > 2

An example of using HAVING and aggregation function: get all departments with sales greater than $1000
SELECT department, SUM(sales)
FROM orders
GROUP BY department
HAVING SUM(sales) > 1000;

3. How to find all employees containing the word "Tom", regardless of whether it was TOM, Tom or tom?

Answer:
Use LIKE operator and UPPER() function to build the WHERE condition:
SELECT * FROM employee WHERE UPPER(employee_name) LIKE '%TOM%'

4. How to find the highest salary in each department from employee table?

Keyword:
MAX, GROUP BY

Answer:
Use MAX function and GROUP BY clause:
SELECT department_id, MAX(salary) AS max_salary FROM employee GROUP BY department_id;

5. How to select TOP n records from a table?

Answer:
In SQL Server, use SELECT TOP N clause:
SELECT TOP n * FROM employee;

In Oracle, use RUWNUM pseudo-column:
SELECT * FROM employee WHERE ROWNUM <= n;

In MySQL / PostgreSQL, use LIMIT N clause:
SELECT * FROM employee LIMIT n;

6. How to convert data types in SQL?

Keyword:
CAST()
CONVERT()

Answer:
To convert an expression of one data type to another, we can use CAST() or CONVERT() function.
The syntax for CAST:
CAST(expression AS data_type [(length)])
The syntax for CONVERT:
CONVERT(data_type [(length)], expression[, style])

For example, the following query find the records that have a 3 in the first digit of their price:
SELECT product_name, price
FROM product
WHERE CAST(price AS int) LIKE '3%';

or:
SELECT product_name, price
FROM product
WHERE CONVERT(int, price) LIKE '3%';

7. How to get department information and department total salary from table employee and department where total salary greater than 10,000?

Keyword:
INNER JOIN + GROUP BY + HAVING

Answer:

Let's assume employee and department table structures are as follow:
employee table: employee_id, first_name, last_name, salary, department_id
department table: department_id, department_name

To get the result, use SUM() function, INNER JOIN, GROUP BY clause and HAVING clause:
SELECT e.department_id, d.department_name, SUM(e.salary)
FROM employee e INNER JOIN department d ON e.department_id = d.department_id
GROUP BY e.department_id
HAVING SUM(e.salary) > 10000;

8. How to add and remove columns in an existing table?

Keyword:
ALTER TABLE tablename ADD COLUMN / DROP COLUMN

Answer:
Use the ALTER TABLE to add and remove columns in an existing table.

For example, to add a new column "department_id" with default value "1" to employee table:
ALTER TABLE employee ADD COLUMN department_id INTEGER DEFAULT 1 NOT NULL;
To remove the column "department_id" from employee table:
ALTER TABLE employee DROP COLUMN department_id;

9. How to find the nth highest record in a table?

Answer:
Let's assume we have an employee table and we need to find the nth highest salary from this table.

In SQL Server, use subquery + DISTINCT + TOP:
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) e ORDER BY salary;

In Oracle, use ROW_NUMBER() function:
SELECT salary FROM (SELECT e.salary, row_number() OVER (ORDER BY salary DESC) rn FROM employee e) WHERE rn = n;

In MySQL / PostgreSQL, use LIMIT clause:
SELECT salary FROM employee ORDER BY salary DESC LIMIT n - 1, 1;

10. How to copy data from one table to another table?

Keyword:
INSERT INTO SELECT
SELECT INTO

Answer:
INSERT INTO SELECT statement is used to copy data from one table to an existing table.

For example:
INSERT INTO employee_backup SELECT * FROM employee;
INSERT INTO user (user_name, address) SELECT name, address FROM employee;

SELECT INTO statement is used to copy data from one table to a new table.
For example:
SELECT * INTO employee_backup FROM employee;
SELECT employee_id, employee_name INTO employee_backup FROM employee WHERE department_id = 1;

More SQL DDL & DML interview questions and answers: SQL Interview Notes

What is the difference between EXSITS and IN?
How to get the current date and time in SQL?
How to combine two columns into one column in a SQL query?
How to get department information and department total salary from table employee and department?
How to find duplicate records in a table?
How to create FOREIGN KEY Constraint on a table?
How to create an index on a table?
How to use Subquery with EXISTS?
......

SQL Interview Notes

100+ frequently asked SQL and Database interview questions with concise summaries and detailed answers.

Topics include: SQL Basic Concepts, SQL DDL & DML, Advanced SQL, Database Design and Performance Tuning.

Download on the AppStore Get it On Google Play

JavaScript Interview Notes

100+ frequently asked JavaScript interview questions with concise summaries and detailed answers. Topics include: JavaScript Basics, DOM, BOM, Object-Oriented JavaScript, Function, Scope, Closure, JSON, XML, Ajax, jQuery.
Download on the AppStore Get it On Google Play

Java Interview Notes

300+ frequently asked Java interview questions with concise summaries and detailed answers. Topics include: Java & OOP, Strings & Collections, IO JVM & GC, Multithreading, Generics Reflection & Annotations, Design Patterns, Java EE, Spring, JPA & Hibernate.

Download on the AppStore Get it On Google Play

SQL Basic Concepts interview questions and answers

1. What is Index?

Keyword:
A data structure that improves the speed of data retrieval operations,
Avoid full table scan,
Need extra storage space.

Answer:
An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

2. What are the differences between Clustered Index and Nonclustered Index?

Keyword:
Clustered Index determines the physical storage order of records.
Nonclustered Index stores the column's value and a pointer to the actual record.
One clustered index per table, multiple nonclustered indexes per table.

Answer:
Clustered indexes sort and store the records in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the records themselves can be sorted in only one order.
The only time the records in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its records are stored in an unordered structure called a heap.

Nonclustered indexes have a structure separate from the records. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the record that contains the key value. There can be more than one nonclustered index per table.

Clustered indexes are faster to read than non clustered indexes as records are physically stored in index order. Nonclustered indexes are quicker for insert and update operations than a clustered index.

3. What is the difference between Primary Key Constraint and Unique Constraint?

Keyword:
A table can have multiple unique constraints, only one primary key.
unique constraints allows NULL value.

Answer:
You can use unique constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a unique constraint and a primary key constraint enforce uniqueness, use a unique constraint instead of a primary key constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Multiple unique constraints can be defined on a table, whereas only one primary key constraint can be defined on a table.
Also, unlike primary key constraints, unique constraints allow for the value NULL. However, as with any value participating in a unique constraint, only one null value is allowed per column.
A unique constraint can be referenced by a foreign key constraint.

4. What is INNER JOIN?

Keyword:
return only matched rows in both tables

Answer:
INNER JOIN is a commonly used join operation. It returns all rows from both tables only when there is a match between the columns. If there are rows in one table that do not have matches in other table, these records will NOT be returned.

For example, a INNER JOIN query on table employee and table department:
SELECT * FROM employee
INNER JOIN department
ON employee.department_id = department.department_id;

5. What is LEFT OUTER JOIN?

Keyword:
return left table all rows + right table matched rows

Answer:
LEFT OUTER JOIN (or simply LEFT JOIN) returns all rows from the left table, with the matching rows in the right table. For the unmatched rows in the left table, the value for each column of the right table is NULL.

For example, a LEFT OUTER JOIN query on table employee and table department:
SELECT * FROM employee
LEFT OUTER JOIN department
ON employee.department_id = department.department_id;

6. What is RIGHT OUTER JOIN?

Keyword:
return right table all rows + left table matched rows

Answer:
RIGHT OUTER JOIN (or simply RIGHT JOIN) returns all rows from the right table, with the matching rows in the left table. For the unmatched rows in the right table, the value for each column of the left table is NULL.

For example, a RIGHT OUTER JOIN query on table employee and table department:
SELECT * FROM employee
RIGHT OUTER JOIN department
ON employee.department_id = department.department_id;

7. What is the difference between JOIN and UNION?

Answer:

A Join is used for displaying columns with the same or different names from different tables. The output displayed will have all the columns shown individually.
The UNION set operator is used for combining data from two tables which have columns with the same data type. When a UNION is performed the data from both tables will be collected in a single column having the same data type.

8. What is the difference between CHAR and VARCHAR?

Keyword:
fix length vs variable length

Answer:
CHAR is a fixed-length character data type. The storage size of the CHAR value is equal to the maximum size for this column.
VARCHAR is a variable-length character data type. The storage size of the VARCHAR value is the actual length of the data, not the maximum size for this column.

Use CHAR when the data entries in a column are expected to be the same size, such as phone number column. Use VARCHAR when the data entries in a column are expected to vary considerably in size, such as description column.