Vaidikalaya
Privious Next

SQL Interview - Questions

Q1. What is SQL?

SQL stands for Structured Query Language. It is a standard language used to communicate with relational databases like MySQL, PostgreSQL, SQL Server, Oracle, etc. It is used to store, retrieve, update, and manage data in a database.

Q2. What are the types of SQL Commands?

SQL commands are categorized into 4 main types, each serving a different purpose in managing data and database structures:

Type Use Command
DDL(Data Definition Language) Used to define or change the structure of the database like tables, columns, etc. CREATE, ALTER, DROP, TRUNCATE, RENAME
DML(Data Manipulation Language) Used to manipulate data stored in tables. SELECT, INSERT, UPDATE, DELETE
DCL(Data Control Language) Used to control access to data. GRANT, REVOKE
TCL(Transaction Control Language) Used to manage transactions (groups of SQL operations). COMMIT, ROLLBACK, SAVEPOINT

Q3. What is the difference between WHERE and HAVING?

⮚ The WHERE clause is used to filter rows before any grouping. When you write a SQL query, and you want to select only certain rows based on conditions (like filtering out users under age 18), you use the WHERE clause. It checks each individual row in the table.

Example:

SELECT * FROM employees
WHERE department = 'IT';

⮚ The HAVING clause is used to filter groups after the GROUP BY clause has aggregated data.

Example:

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 10000;

This query find the departments where the highest salary is more than ₹10000.

Q4. What is the difference between UNION and UNION ALL?

⮚ The UNION operator is used to combine the result sets of two or more SELECT queries and removes the duplicate rows from the final result. It performs an extra sorting step to check for duplicates, which can make it slower.

Example:

SELECT name FROM students
UNION
SELECT name FROM teachers;

If both tables have the same names like 'John', 'John' will appear only once.

⮚ The UNION ALL operator also combines the result sets of multiple SELECT queries, but theis does not remove duplicate rows from the final result. It is faster than UNION because it doesn't check for duplicate rows.

Example:

SELECT name FROM students
UNION ALL
SELECT name FROM teachers;

If 'John' exists in both, it will appear twice in the result.

Q5. What is a primary key and can a table have multiple primary keys?

A Primary Key is a column or a combination of columns in a database table that uniquely identifies each row in that table. A table can have only one primary key and primary key columns cannot have NULL values.

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
Q6. What is composite key?

A composite key is a primary key that consists of two or more columns used together to uniquely identify each row in a table, when a single column isn't sufficient.

Example:

If you have a table for student enrollments, the combination of student_id and course_id could be a composite key to ensure that a student can’t enroll in the same course more than once.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)  -- Composite key
);

In this case, student_id and course_id together form the composite key.

Q7. What is a foreign key?

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the Primary Key or Unique Key in another table.

Key Points

⮚ A foreign key ensures that only valid values are inserted into the child table.

⮚ Foreign keys support cascading actions like ON DELETE CASCADE or ON UPDATE CASCADE. This means you can automatically delete or update related records when a parent record is deleted or updated, reducing the need for manual intervention.

⮚ Foreign key columns are often indexed automatically (or can be indexed manually), which can improve performance when performing searches and JOINs between tables.

Example:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
Q8. What are the constraints in MySql?

Constraints are rules that are applied to the columns or tables to ensure the integrity and validity of the data. Constraints help enforce specific conditions on the data to ensure that it is accurate, reliable, and consistent.

⮚ The following constraints are commonly used in SQL:

PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, AUTO_INCREMENT, DEFAULT, CHECK

Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
    email VARCHAR(255) UNIQUE
    phone VARCHAR(255) UNIQUE
    status VARCHAR(20) DEFAULT 'active'
    CHECK (phone >= 10)
);
Q9. What is Stored Procedure?

Stored procedure is a predefined set of SQL quires or statements stored in the database, which can be executed multiple times.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Example 1: Simple Stored Procedure

DELIMITER $$

CREATE PROCEDURE GetUserDetails()
BEGIN
    SELECT * FROM z_mg_service_user_master;
END $$

DELIMITER ;

-- Call The Stored Procedure
CALL GetUserDetails();

Example 2: Stored Procedure with Parameters

DELIMITER $$

CREATE PROCEDURE GetUserById(IN employeeId INT)
BEGIN
    SELECT * FROM user_master WHERE employee_id = employeeId;
END $$

DELIMITER ;

-- Call The Stored Procedure
CALL GetUserById(99990021);
Q10. What is trigger in MySql?

A trigger in MySQL is a special type of stored procedure that is automatically executed in response to certain events such as INSERT, UPDATE, or DELETE on a table.

Triggers are typically used to perform automated actions like enforcing business rules, maintaining data integrity, or logging changes.

There are two ways to use trigger in MySql: Before OR After

Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- Triggered action
END;

Example: To implement a rule where the value of is_adult is set to 1 if the user's age is greater than 18 and otherwise set 0

DELIMITER $$

CREATE TRIGGER set_is_adult_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.age > 18 THEN
        SET NEW.is_adult = 1;
    ELSE
        SET NEW.is_adult = 0;
    END IF;
END $$

DELIMITER ;
Q11. What is VIEW in MySql?

A view in MySQL is a virtual table created by a SELECT query. It is similar to a regular table, but it doesn’t store data itself; instead, it displays data dynamically from one or more tables based on the query you define.

Key Points:

⮚ Views can simplify complex SQL queries by encapsulating them in a reusable object.

⮚ Once defined, a view can be used multiple times in queries, reducing the need for repetitive code.

⮚ Views can restrict access to sensitive data by exposing only specific columns or rows of the underlying tables.

⮚ You cannot define indexes and triggers in view.

Example:

CREATE VIEW users_view AS
SELECT id, CONCAT(first_name, ' ', last_name) AS full_name, email
FROM users;
Privious Next