SQL Interview Questions and Answers Updated

Question: What is SQL?

Answer:

SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It is primarily used to interact with databases by querying, updating, inserting, and deleting data. SQL is essential for tasks related to database management and is widely used in various industries for backend development, data analysis, and more.

Key Features of SQL:

• Database Management: SQL is used to create, manage, and administer relational databases.
• Data Manipulation: SQL allows you to query data (retrieving specific information), update existing data, insert new data, and delete data.
• Data Definition: It is used to define the structure of data in a database, including creating tables, defining columns, data types, and establishing relationships between tables.
• Data Integrity and Security: SQL can enforce rules to ensure data integrity (such as constraints) and control access to the database using user roles and permissions.

Common SQL Commands:

1. SELECT: Retrieves data from one or more tables.

   SELECT * FROM Employees WHERE Age > 30;

   This command retrieves all columns from the “Employees” table where the age is greater than 30.

2. INSERT: Adds new data to a table.

   INSERT INTO Employees (Name, Age, Department)
   VALUES ('John Doe', 28, 'Sales');

3. UPDATE: Modifies existing data in a table.

   UPDATE Employees SET Age = 29 WHERE Name = 'John Doe';

4. DELETE: Removes data from a table.

   DELETE FROM Employees WHERE Name = 'John Doe';

5. CREATE: Defines new tables, databases, views, indexes, etc.

   CREATE TABLE Employees (
       ID INT PRIMARY KEY,
       Name VARCHAR(100),
       Age INT,
       Department VARCHAR(50)
   );

6. ALTER: Modifies the structure of an existing table (e.g., adding/removing columns).

   ALTER TABLE Employees ADD Salary DECIMAL(10, 2);

7. DROP: Deletes a table or database.

   DROP TABLE Employees;

8. JOIN: Combines rows from two or more tables based on a related column.

   SELECT Employees.Name, Departments.DepartmentName
   FROM Employees
   JOIN Departments ON Employees.DepartmentID = Departments.ID;

9. WHERE: Filters the records that meet certain conditions.

   SELECT * FROM Employees WHERE Age > 30;

10. ORDER BY: Sorts the result set based on one or more columns.

    SELECT * FROM Employees ORDER BY Age DESC;

11. GROUP BY: Groups records that have the same values in specified columns, often used with aggregate functions like COUNT, SUM, AVG, etc.

    SELECT Department, COUNT(*) AS NumberOfEmployees
    FROM Employees
    GROUP BY Department;

Types of SQL:

1. DML (Data Manipulation Language):

   • SELECT: Fetch data from tables.
   • INSERT: Add new data.
   • UPDATE: Modify existing data.
   • DELETE: Remove data.

2. DDL (Data Definition Language):

   • CREATE: Create new database objects like tables, views, etc.
   • ALTER: Modify existing database objects.
   • DROP: Delete database objects.

3. DCL (Data Control Language):

   • GRANT: Provide privileges to users.
   • REVOKE: Remove privileges from users.

4. TCL (Transaction Control Language):

   • COMMIT: Save the transaction.
   • ROLLBACK: Undo the transaction.
   • SAVEPOINT: Set a savepoint within a transaction.
   • SET TRANSACTION: Configure the transaction behavior.

SQL and Relational Databases:

SQL is designed for relational databases, which store data in tables (rows and columns). Tables can be related to each other via keys (primary keys, foreign keys), and SQL allows you to define these relationships.

Example of SQL Query:

Suppose you have a Customers table:

CustomerID	Name	City	Age
1	John	New York	28
2	Alice	Los Angeles	32
3	Bob	Chicago	40

You can use the following SQL query to retrieve all customers aged 30 or above:

SELECT Name, City FROM Customers WHERE Age >= 30;

Result:

Name	City
Alice	Los Angeles
Bob	Chicago

---

Conclusion:

SQL is a powerful and essential language for managing relational databases. It helps you store, retrieve, update, and manipulate data efficiently. Understanding SQL is fundamental for anyone working with databases, whether for backend development, data analysis, or working with cloud-based database systems.

Question: What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

Answer:

In SQL, JOIN operations are used to combine rows from two or more tables based on a related column between them. The four most commonly used types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Here’s a breakdown of each:

---

1. INNER JOIN:

Definition: The INNER JOIN returns only the rows that have matching values in both tables. If there is no match, the row is excluded from the result set.

Behavior:

• Combines rows from both tables where there is a match on the specified condition.
• If there is no match, that row is not included in the result.

Example:

Consider the following tables:

• Employees table:

  ID	Name	DepartmentID
  1	John	101
  2	Alice	102
  3	Bob	103

• Departments table:

  DepartmentID	DepartmentName
  101	HR
  102	IT
  104	Marketing

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Alice	IT

Explanation: Only rows where a matching DepartmentID exists in both tables are returned. Bob does not appear because there is no matching department for DepartmentID 103 in the Departments table.

---

2. LEFT JOIN (or LEFT OUTER JOIN):

Definition: The LEFT JOIN returns all rows from the left table (the first table in the query), and the matched rows from the right table (second table). If there is no match, the result will contain NULL for columns from the right table.

Behavior:

• All rows from the left table are included in the result.
• If a row in the left table has no corresponding match in the right table, the right table’s columns will be NULL.

Example:

Using the same tables:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Alice	IT
Bob	NULL

Explanation: All employees are listed, including Bob who does not have a department, so the DepartmentName is NULL.

---

3. RIGHT JOIN (or RIGHT OUTER JOIN):

Definition: The RIGHT JOIN returns all rows from the right table (the second table in the query), and the matched rows from the left table (the first table). If there is no match, the result will contain NULL for columns from the left table.

Behavior:

• All rows from the right table are included in the result.
• If a row in the right table has no corresponding match in the left table, the left table’s columns will be NULL.

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Alice	IT
NULL	Marketing

Explanation: All departments are listed, including Marketing, which has no employee assigned to it. The Name for Marketing is NULL.

---

4. FULL JOIN (or FULL OUTER JOIN):

Definition: The FULL JOIN (or FULL OUTER JOIN) returns all rows when there is a match in either the left or the right table. It returns NULL on the side where there is no match.

Behavior:

• Returns all rows from both tables.
• If there is no match between the tables, NULL values are returned for the columns of the table without a match.

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Alice	IT
Bob	NULL
NULL	Marketing

Explanation: The result includes all employees and all departments. Since Bob does not belong to any department, the DepartmentName for his row is NULL. Similarly, the Marketing department has no employees, so the Name is NULL.

---

Summary of Differences:

Type	Rows from Left Table	Rows from Right Table	Matching Rows	Non-Matching Rows
INNER JOIN	Only matching rows	Only matching rows	Yes	No
LEFT JOIN	All rows from left	Matching rows or NULL	Yes	No
RIGHT JOIN	Matching rows or NULL	All rows from right	Yes	No
FULL JOIN	All rows from left	All rows from right	Yes	Yes (NULLs for no match)

---

Conclusion:

• INNER JOIN: Only the matching rows from both tables.
• LEFT JOIN: All rows from the left table, with matching rows from the right table; NULL for non-matching right table rows.
• RIGHT JOIN: All rows from the right table, with matching rows from the left table; NULL for non-matching left table rows.
• FULL JOIN: All rows from both tables, with NULL for non-matching rows.

Each type of join serves different purposes depending on the desired result from the relationship between the tables.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.