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