SQL Viva Questions: A Must-Read for Tech Students

SQL Viva Questions

SQL Viva Questions are a crucial part of interviews for students and professionals working with databases. These questions test your knowledge of SQL concepts, commands, and real-world applications. Whether you are a beginner or an advanced learner, preparing for SQL Viva Questions helps you gain confidence and improve your problem-solving skills.

In this Interview tutorial, we will explore the most common SQL Viva Questions to help you ace your exams and interviews.

Top 50+ SQL Viva Questions and Answers

Q 1. What is SQL?

SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It is used for querying, updating, and managing data in a structured format.

Q 2. What are the different types of SQL commands?

SQL commands are categorized into five types:

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
5. DQL (Data Query Language).

Q 3. What is a Primary Key?

A Primary Key in SQL is a unique identifier for each record in a database table. It ensures that no two records have the same value for the primary key column(s). A table can only have one primary key.

Q 4. What is a Foreign Key?

A Foreign Key in SQL is a column (or set of columns) in one table that uniquely identifies a row in another table, establishing a relationship between the two tables.

Q 5. What is the difference between `WHERE` and `HAVING` clauses?

Let's understand the difference between the WHERE clause in SQL and the Having Clause in SQL.

Q 6. What is a Join in SQL?

A Join in SQL is used to combine records from two or more tables based on a related column between them. The most common types of joins are

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN

Q 7. What is the difference between INNER JOIN and LEFT JOIN?

Let's understand the key difference between INNER JOIN and LEFT JOIN.

Q 8. What is the difference between UNION and UNION ALL?

Let's understand the key differences between UNION and UNION ALL.

Q 9. What is an Index in SQL?

An Index in SQL is a database object that improves the speed of data retrieval operations on a table. It reduces the amount of data the database needs to scan, making queries faster. However, it may slow down write operations.

Q 10. What is a Subquery in SQL?

A Subquery in SQL is a query nested inside another query, typically within the WHERE or HAVING clause. It allows you to perform more complex queries by breaking them down into smaller, manageable parts.

Example:

SELECT * FROM employees WHERE salary > (
SELECT AVG(salary) FROM employees);

This query retrieves all employees whose salaries are greater than the average salary of all employees.

Q 11. What is a View in SQL?

A View in SQL is a virtual table created by a query that selects data from one or more tables. It helps to simplify complex queries and can also be used to enhance security by restricting access to certain data.

Q 12. What is the difference between DELETE and TRUNCATE?

Let's understand the key difference between DELETE and TRUNCATE Command.

Q 13. What is a Self-Join?

A Self Join in SQL is when a table is joined with itself. It is useful when you need to compare rows within the same table.

Example:

 SELECT e1.name AS Employee, e2.name AS Manager 
 FROM employees e1, employees e2 
 WHERE e1.manager_id = e2.employee_id;

This query retrieves the list of employees along with their managers' names.

Q 14. What is a Transaction in SQL?

A Transaction in SQL is a set of SQL operations that are executed as a single unit. It ensures that either all operations succeed or none of them are applied, maintaining data consistency. Transactions are controlled by commands such as COMMIT and ROLLBACK.

Example:

 BEGIN TRANSACTION;
  UPDATE employees SET salary = salary * 1.1 
  WHERE department_id = 5;
  COMMIT;

This query increases the salary of all employees in department 5 by 10% and commits the transaction.

Q 15. What is a Trigger in SQL?

A Trigger in SQL is a set of SQL statements that are automatically executed (or "triggered") when a specified event occurs in a table or view, such as INSERT, UPDATE, or DELETE.

Example:

CREATE TRIGGER update_salary_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < OLD.salary THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be decreased';
   END IF;
END;

This trigger prevents a decrease in the salary of an employee when an UPDATE operation is performed.

Q 16. What is an Aggregate Function in SQL?

An Aggregate Function performs a calculation on a set of values and returns a single value. Common aggregate functions include `COUNT()`, `SUM()`, `AVG()`, `MAX()`, and `MIN()`.

Example:

SELECT AVG(salary) FROM employees WHERE department_id = 3;

This query calculates the average salary of employees in department 3.

Q 17. What is the difference between `CHAR` and `VARCHAR`?

Q 18. What is the purpose of the `DISTINCT` keyword?

The `DISTINCT` keyword is used to return only unique values from a column, eliminating duplicate rows in the result set.

Example:

SELECT DISTINCT department FROM employees;

This query retrieves a list of unique departments from the employees table, removing duplicates.

Q 19. What is the difference between `IN` and `EXISTS`?

The key differences between the 'IN' and 'EXISTS'.

Q 20. What is normalization in SQL?

Normalization in SQL is the process of organizing data in a database to avoid redundancy and improve data integrity. It involves dividing large tables into smaller, manageable ones while maintaining relationships using foreign keys.

Q 21. What is denormalization in SQL?

Denormalization in SQL is the process of combining tables that were previously separated during normalization to improve performance. This can reduce the number of joins needed in queries at the cost of potential data redundancy.

Q 22. What is the difference between `UNION` and `JOIN`?

Let's understand the difference between the 'UNION' and 'JOIN in SQL'.

Q 23. What is the `BETWEEN` operator in SQL?

The `BETWEEN` operator is used to filter the result set within a specified range. The range can be numbers, text, or dates.

Example:

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

This query retrieves all employees whose salary is between 3000 and 5000.

Q 24. What is the `LIKE` operator in SQL?

The `LIKE` operator is used to search for a specified pattern in a column. It is often used with wildcards, such as `%` (any sequence of characters) and `_` (any single character).

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

This query retrieves all employees whose name starts with 'J'.

Q 25. What is the `IS NULL` operator in SQL?

The `IS NULL` operator is used to check for NULL values in a column. It is used when you want to find records where the value of a column is not defined.

Example:

SELECT * FROM employees WHERE department_id IS NULL;

This query retrieves all employees who have not been assigned to any department.

Q 26. What is the `CASE` statement in SQL?

The `CASE` statement is used to create conditional logic in SQL queries. It allows you to return specific values based on conditions, similar to an IF-ELSE statement in programming languages.

Example:

SELECT name, 
                  CASE 
                      WHEN salary > 5000 THEN 'High'
                      WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
                      ELSE 'Low'
                  END AS salary_range
     FROM employees;

This query classifies employees into "High", "Medium", or "Low" salary categories based on their salary values.

Q 27. What is the `ALTER` statement used for?

The `ALTER` statement is used to modify an existing database object, such as a table. You can use it to add, delete, or modify columns in a table or change the structure of other database objects.

Example:

ALTER TABLE employees ADD age INT;

This query adds a new column `age` to the `employees` table.

Q 28. What is the `DROP` statement used for in SQL?

The `DROP` statement is used to remove a database object, such as a table, view, or index. It permanently deletes the object and all the data within it.

Example:

DROP TABLE employees;

This query deletes the `employees` table and all the data in it.

Q 29. What is the `TRUNCATE` statement in SQL?

The `TRUNCATE` statement is used to remove all rows from a table but retains the table structure for future use. It is faster than `DELETE` but cannot be rolled back unless in a transaction.

Example:

TRUNCATE TABLE employees;

This query removes all rows from the `employees` table but keeps the table structure intact.

Q 30. What is a `Stored Procedure` in SQL?

A `Stored Procedure in SQL` is a precompiled collection of SQL statements that can be executed as a single unit. Stored procedures are used to encapsulate repetitive tasks and improve performance by reducing the amount of SQL code executed.

Example:

CREATE PROCEDURE getEmployeeDetails (IN emp_id INT)
    BEGIN
        SELECT * FROM employees WHERE employee_id = emp_id;
    END;

This stored procedure retrieves the details of an employee based on the employee's ID.

Q 31. What is a `Function` in SQL?

A `Function` in SQL is similar to a stored procedure but returns a value. Functions are often used to encapsulate logic that returns a value, like mathematical operations or string manipulation.

Example:

CREATE FUNCTION calculateSalary (emp_id INT)
    RETURNS DECIMAL(10,2)
    BEGIN
        DECLARE salary DECIMAL(10,2);
        SELECT salary INTO salary FROM employees WHERE employee_id = emp_id;
        RETURN salary;
    END;

This function calculates the salary of an employee based on their ID and returns it.

Q 32. What is `Auto Increment` in SQL?

`Auto Increment` is a property used in SQL to automatically generate unique values for a primary key column. It ensures that each new record gets a unique identifier.

Example:

CREATE TABLE employees (
        employee_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        department_id INT
    );

This query creates a table with an `employee_id` that automatically increments with each new record.

Q 33. What is the `GROUP BY` clause in SQL?

The `GROUP BY` clause in SQL is used to group rows that have the same values in specified columns. It is often used with aggregate functions to perform calculations on each group of rows.

Example:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

This query groups employees by their department ID and returns the number of employees in each department.

Q 34. What is the `ORDER BY` clause in SQL?

The `ORDER BY` clause in SQL is used to sort the result set in either ascending (`ASC`) or descending (`DESC`) order based on one or more columns.

Example:

SELECT name, salary FROM employees ORDER BY salary DESC;

This query retrieves the names and salaries of employees, sorted in descending order by salary.

Q 35. What is a `Composite Key` in SQL?

A `Composite Key` is a primary key that consists of two or more columns used together to uniquely identify a record in a table. It is used when a single column is not enough to uniquely identify records.

Example:

CREATE TABLE employee_projects (
        employee_id INT,
        project_id INT,
        PRIMARY KEY (employee_id, project_id)
    );

This table uses a composite key made up of `employee_id` and `project_id` to uniquely identify each record.

Q 36. What is the `WITH` clause in SQL?

The `WITH` clause in SQL is used to create temporary named result sets, often referred to as Common Table Expressions (CTEs). It helps to make queries easier to read and write, especially with complex joins and subqueries.

Example:

WITH DepartmentSummary AS (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    )
    SELECT * FROM DepartmentSummary WHERE avg_salary > 5000;

This query first creates a CTE `DepartmentSummary` that calculates the average salary per department and then filters departments with an average salary greater than 5000.

Q 37. What is the `LIMIT` clause in SQL?

The `LIMIT` clause in SQL is used to restrict the number of rows returned in a result set. It is often used when you only want to retrieve a subset of records from a larger table.

Example:

SELECT * FROM employees LIMIT 10;

This query retrieves the first 10 rows from the `employees` table.

Q 38. What is the difference between `LEFT JOIN` and `RIGHT JOIN`?

Let's understand the difference between `LEFT JOIN` and `RIGHT JOIN`.

Q 39. What are `wildcards` in SQL?

Wildcards in SQL are special characters used in conjunction with the `LIKE` operator to search for patterns in data. Common wildcards are `%` (representing any sequence of characters) and `_` (representing a single character).

Example:

SELECT * FROM employees WHERE name LIKE 'J_n%';

This query retrieves all employees whose name starts with 'J', followed by any character, and then contains 'n' and any sequence of characters.

Q 40. What is a `Self Join` in SQL?

A `Self Join in SQL` is a regular join, but the table is joined with itself. It is used when you need to compare rows within the same table.

Example:

SELECT e1.employee_id, e1.name, e2.name AS manager_name
    FROM employees e1
    JOIN employees e2 ON e1.manager_id = e2.employee_id;

This query joins the `employees` table with itself to find the manager of each employee.

Q 41. What is the `INNER JOIN` in SQL?

An `INNER JOIN` returns only the rows that have matching values in both tables. It eliminates rows that do not have a match in the other table.

Example:

SELECT employees.name, departments.name
    FROM employees
    INNER JOIN departments ON employees.department_id = departments.id;

This query returns the names of employees along with their corresponding department names, but only for employees who have a department assigned.

Q 42. What is the `FULL OUTER JOIN` in SQL?

A `FULL OUTER JOIN` returns all rows when there is a match in either the left or right table. It returns NULL for non-matching rows from both tables.

Example:

SELECT employees.name, departments.name
    FROM employees
    FULL OUTER JOIN departments ON employees.department_id = departments.id;

This query returns all employees along with their department names. If an employee does not have a department or a department has no employees, NULL values are shown for missing data.

Q 43. What is the `RIGHT JOIN` in SQL?

A `RIGHT JOIN` returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Example:

SELECT employees.name, departments.name
    FROM employees
    RIGHT JOIN departments ON employees.department_id = departments.id;

This query returns all departments along with the employees in each department. If a department has no employees, NULL will be shown for the employee's name.

Q 44. What is a `Subquery` in SQL?

A `Subquery` is a query nested inside another query. It is used to perform an operation or condition on the result set of the main query.

Example:

SELECT name
    FROM employees
    WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

This query retrieves the names of employees who work in the 'HR' department by using a subquery to get the department's ID.

Q 45. What is `HAVING` in SQL?

The `HAVING` clause is used to filter records after grouping them with the `GROUP BY` clause. It is similar to `WHERE` but works with aggregate functions.

Example:

SELECT department_id, COUNT(*) 
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 10;

This query groups employees by department and filters to return only those departments with more than 10 employees.

Q 46. What is `EXCEPT` in SQL?

The `EXCEPT` operator is used to return the rows that are in the first query but not in the second query. It removes duplicates between the two result sets.

Example:

SELECT employee_id FROM employees
    EXCEPT
    SELECT employee_id FROM contractors;

This query returns the employee IDs that are in the `employees` table but not in the `contractors` table.

Q 47. What is `INTERSECT` in SQL?

The `INTERSECT` operator returns only the rows that exist in both queries. It returns the common rows between two result sets.

Example:

SELECT employee_id FROM employees
    INTERSECT
    SELECT employee_id FROM contractors;

This query retrieves the employee IDs that are both in the `employees` table and the `contractors` table.

Q 48. What is the `MERGE` statement in SQL?

The `MERGE` statement, also known as "upsert," is used to perform INSERT, UPDATE, or DELETE operations in a single statement, based on a condition.

Example:

MERGE INTO employees AS e
    USING new_employees AS ne
    ON e.employee_id = ne.employee_id
    WHEN MATCHED THEN
        UPDATE SET e.salary = ne.salary
    WHEN NOT MATCHED THEN
        INSERT (employee_id, name, salary) VALUES (ne.employee_id, ne.name, ne.salary);

This `MERGE` statement updates the salary of existing employees and inserts new employees if they do not already exist in the `employees` table.

Q 49. What is the `UNION ALL` operator in SQL?

The `UNION ALL` operator is used to combine the result sets of two or more queries, but unlike `UNION`, it does not remove duplicates.

Example:

SELECT employee_id FROM employees
    UNION ALL
    SELECT employee_id FROM contractors;

This query returns all employee IDs from both the `employees` and `contractors` tables, including duplicates.

Q 50. What is the purpose of the `COALESCE` function in SQL?

The `COALESCE` function in SQL returns the first non-NULL value from a list of arguments. It is commonly used to handle NULL values in queries by providing a default value.

Example:

SELECT employee_id, COALESCE(phone, 'No Phone Provided') AS phone_number
FROM employees;

This query retrieves the employee ID and their phone number. If the phone number is NULL, it will display "No Phone Provided" instead.

Conclusion

SQL is a crucial skill for database management, making SQL Viva Questions important for exam and interview preparation. This guide helps you understand core SQL concepts through practical examples and real-world scenarios. Mastering SQL viva questions, including joins, subqueries, and functions, prepares you for problem-solving in database tasks. These concepts are frequently tested in both academic and professional settings.

Dear learners, join our Tech Trendy Masterclasses to help you learn and immerse yourself in the latest trending technologies and upgrade your tech skills with the latest skills trends, design, and practices.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE