# Interview Questions

## Can you explain the difference between "HAVING" and "WHERE" in SQL?

🧑‍💻 Candidate: Sure! "WHERE" and "HAVING" are both used to filter data in SQL queries, but they are used in slightly different contexts.

🧑‍💻 Candidate: "WHERE" is used to filter rows in a query based on a specific condition, such as a column value being greater than a certain threshold. This is done before the query groups the data using a GROUP BY clause.

🧑‍💻 Candidate: "HAVING", on the other hand, is used to filter the results of a query after the data has been grouped using a GROUP BY clause. It allows you to apply a condition to the grouped data, such as summing a column and filtering the results based on that sum being greater than a certain threshold.

Interviewer: Can you give an example of how "WHERE" and "HAVING" are used in a query?

🧑‍💻 Candidate: Sure! Let's say we have a table of sales data with columns for "salesperson", "region", and "sales\_amount". We want to group the data by region and find the total sales amount for each region. We also want to filter the results to only include regions where the total sales amount is greater than $10,000.

🧑‍💻 Candidate: We would use a query like this:

```sql
SELECT region, SUM(sales_amount) as total_sales
FROM sales_data
GROUP BY region
HAVING total_sales > 10000
```

🧑‍💻 Candidate: In this query, the "GROUP BY" clause groups the data by region, and the "SUM" function calculates the total sales amount for each region. The "HAVING" clause filters the results to only include regions where the total sales amount is greater than $10,000.

## **What is the difference between LIKE and NOT LIKE operators in SQL?**

* LIKE operator is used to match a specific pattern in a column, using wildcard characters (% and \_).
* NOT LIKE operator is used to match rows that do not match the specified pattern in a column.

SQL Example: Suppose we have a table called Products with a column named name. We want to retrieve products with names starting with 'A' and products with names that do not contain 'XYZ'.

Using LIKE:

```sql
SELECT * FROM Products WHERE name LIKE 'A%';
```

In this example, the LIKE operator is used to retrieve products whose names start with 'A'. The '%' wildcard character matches any sequence of characters.

Using NOT LIKE:

```sql
SELECT * FROM Products WHERE name NOT LIKE '%XYZ%';
```

In this example, the NOT LIKE operator is used to retrieve products whose names do not contain the substring 'XYZ'. The '%' wildcard character matches any sequence of characters.

## **What is the difference between SELECT and SELECT DISTINCT in SQL?**

* SELECT retrieves all rows and columns from a table or tables.
* SELECT DISTINCT retrieves only the unique rows from the specified column(s) in the SELECT statement, eliminating duplicates.

SQL Example: Suppose we have a table called Customers with columns such as customer\_id and country. We want to retrieve all distinct countries from the Customers table.

Using SELECT:

```sql
SELECT country FROM Customers;
```

In this example, the SELECT statement is used to retrieve all rows from the Customers table, including duplicate countries.

Using SELECT DISTINCT:

```sql
SELECT DISTINCT country FROM Customers;
```

In this example, the SELECT DISTINCT statement is used to retrieve only the unique countries from the Customers table, eliminating duplicate entries.

## **What is the difference between INNER JOIN and OUTER JOIN operators in SQL?**

* INNER JOIN returns only the matching rows between the tables involved in the join.
* OUTER JOIN includes unmatched rows from one or both tables in the result set, based on the type of outer join (LEFT, RIGHT, or FULL).

SQL Example: Suppose we have two tables called Orders and Customers, both with a column named customer\_id. We want to retrieve all orders along with their corresponding customer information.

Using INNER JOIN:

```sql
SELECT Orders.order_id, Customers.customer_id, Customers.name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = Customers.customer_id;
```

In this example, the INNER JOIN operator is used to combine the Orders and Customers tables based on the matching customer\_id. Only the rows with matching customer\_id values in both tables will be included in the result set.

Using OUTER JOIN:

```sql
SELECT Orders.order_id, Customers.customer_id, Customers.name
FROM Orders
LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;
```

In this example, the LEFT JOIN operator is used to combine the Orders and Customers tables based on the matching customer\_id. It includes all rows from the left table (Orders) and unmatched rows from the right table (Customers). If a customer\_id in Orders does not have a match in Customers, the corresponding columns from Customers will contain NULL values.

## What is the difference between UNION and UNION ALL in SQL?

* UNION removes duplicate rows from the result set.
* UNION ALL keeps all rows, including duplicates.
* UNION performs an additional step to check and remove duplicates, so UNION ALL is faster.

SQL Example: Suppose we have two tables: Customers and Suppliers. We want to retrieve a combined list of all distinct names from both tables.

Using UNION:

```sql
SELECT name FROM Customers
UNION
SELECT name FROM Suppliers;
```

This query will remove any duplicate names and provide a result set with only distinct names.

Using UNION ALL:

```sql
SELECT name FROM Customers
UNION ALL
SELECT name FROM Suppliers;
```

This query will include all names from both tables, including duplicates.

## What is the difference between NOT NULL and NULL in SQL?

* NOT NULL means a column must have a value, it cannot be left empty.
* NULL means a column can be left empty and contain no value.

SQL Example: Suppose we have a table called Employees with a column called email. We want to ensure that the email column cannot be left empty.

Using NOT NULL:

```sql
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) NOT NULL
);
```

In this example, the NOT NULL constraint ensures that every row in the Employees table must have a non-empty value in the email column.

Using NULL:

```sql
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);
```

In this example, the email column allows NULL values, so it can be left empty.

## What is the difference between SQL Server and MySQL?

* SQL Server is a product from Microsoft.
* MySQL is an open-source database from Oracle.
* They support most common SQL functionality but have some differences in syntax, functions, and features.

SQL Example: The syntax and features may vary between SQL Server and MySQL. For example, to retrieve the current date and time in SQL Server, you would use the GETDATE() function:

```sql
SELECT GETDATE();
```

In MySQL, you would use the NOW() function:

```sql
SELECT NOW();
```

These differences in syntax and functions are common across various SQL operations and can impact the way queries and statements are written in each database management system.

## What is the difference between a PRIMARY KEY and a UNIQUE KEY in SQL?

* A PRIMARY KEY is used to uniquely identify each record in a table.
* A UNIQUE KEY also ensures that no two rows have the same value but does not have to identify the row.
* A table can have only one PRIMARY KEY but multiple UNIQUE KEYs.

SQL Example: Suppose we have a table called Customers with a column called customer\_id. We want to ensure that each customer\_id is unique.

Using PRIMARY KEY:

```sql
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);
```

In this example, the PRIMARY KEY constraint is used to uniquely identify each record in the Customers table based on the customer\_id column.

Using UNIQUE KEY:

```sql
CREATE TABLE Customers (
    customer_id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    UNIQUE (customer_id)
);
```

In this example, the UNIQUE constraint is used to ensure that no two rows have the same customer\_id, but it does not designate the column as the primary identifier.

## What is the difference between an INDEX and a KEY in SQL?

* An INDEX is a structure that speeds up data retrieval from tables.
* A KEY is a column or set of columns used to identify rows or enforce uniqueness.
* An INDEX can be created on one or more columns, including KEY columns.

SQL Example: Sup

pose we have a table called Products with a column called product\_name. We want to create an index to improve the query performance when searching for specific product names.

Using INDEX:

```sql
CREATE INDEX idx_product_name ON Products (product_name);
```

In this example, an index named idx\_product\_name is created on the product\_name column of the Products table. This index helps to speed up data retrieval when querying based on product names.

Using KEY:

```sql
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    UNIQUE (product_name)
);
```

In this example, the product\_name column is designated as a KEY by using the UNIQUE constraint. This KEY enforces uniqueness on the product\_name values and can also be used for faster data retrieval.

## What is the difference between COUNT(column) and COUNT(\*) in SQL?

* COUNT(column) counts non-NULL values in that column.
* COUNT(\*) counts all rows, including NULL values.

SQL Example: Suppose we have a table called Orders with a column called order\_status. We want to count the number of orders with a specific status.

Using COUNT(column):

```sql
SELECT COUNT(order_status) FROM Orders WHERE order_status = 'Completed';
```

In this example, COUNT(order\_status) will return the number of non-NULL values in the order\_status column where the status is 'Completed'.

Using COUNT(\*):

```sql
SELECT COUNT(*) FROM Orders WHERE order_status = 'Completed';
```

In this example, COUNT(\*) will return the total number of rows in the Orders table where the status is 'Completed', including rows where the order\_status column is NULL.

## What is the difference between DELETE and TRUNCATE in SQL?

* DELETE removes rows from a table. The table structure remains.
* TRUNCATE empties the entire table. The table structure and metadata are preserved.

SQL Example: Suppose we have a table called Customers with multiple rows of data. We want to remove all the rows from the table.

Using DELETE:

```sql
DELETE FROM Customers;
```

In this example, the DELETE statement removes all rows from the Customers table, but the table structure and metadata remain intact.

Using TRUNCATE:

```sql
TRUNCATE TABLE Customers;
```

In this example, the TRUNCATE TABLE statement empties the entire Customers table. It removes all rows and resets any auto-increment counters associated with the table. The table structure and metadata are preserved.

## What is the difference between ORDER BY and SORT BY in SQL?

* ORDER BY sorts the result set returned by the SELECT statement.
* SORT BY physically rearranges the records in the table, while ORDER BY does not change the table.

SQL Example: Suppose we have a table called Employees with columns like employee\_id, name, and salary. We want to retrieve employee records sorted by their salaries in descending order.

Using ORDER BY:

```sql
SELECT employee_id, name, salary FROM Employees ORDER BY salary DESC;
```

In this example, the ORDER BY clause is used to sort the result set based on the salary column in descending order. The original table remains unchanged.

Using SORT BY:

```sql
SELECT employee_id, name, salary FROM Employees SORT BY salary DESC;
```

There is no direct SORT BY clause in SQL. Sorting is achieved through the ORDER BY clause, as shown in the previous example.

## What is the difference between IN and EXISTS in SQL?

* IN is an operator used to compare a value to a list of values.
* EXISTS is a condition used to check if a subquery returns any rows.
* IN is easier to write, but EXISTS sometimes performs better.

SQL Example: Suppose we have two tables: Customers and Orders. We want to retrieve all customers who have placed orders.

Using IN:

```sql
SELECT * FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders);
```

In this example, the IN operator is used to compare the customer\_id column in the Customers table with the customer\_id column returned by the subquery, which retrieves customer IDs from the Orders table.

Using EXISTS:

```sql
SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Customers.customer_id = Orders.customer_id);
```

In this example, the EXISTS condition is used to check if any rows are returned by the subquery. It correlates the customer\_id column between the Customers and Orders tables.

## What is the difference between a JOIN and a UNION in SQL?

* A JOIN combines rows from two or more tables based on related column values.
* A UNION combines the result set of two or more SELECT statements.

SQL Example: Suppose we have two tables: Customers and Orders. We want to retrieve a list of customers who have placed orders, combining the data from both tables.

Using JOIN:

```sql
SELECT Customers.customer_id, Customers.name, Orders.order_id
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id;
```

In this example, the JOIN operation is used to combine rows from the Customers and Orders tables based on the customer\_id column. The resulting rows will contain customer information along with the corresponding order IDs.

Using UNION:

```sql
SELECT customer_id, name FROM Customers
UNION
SELECT customer_id, '' AS name FROM Orders;
```

In this example, the UNION operator combines the result sets of two SELECT statements. The first SELECT retrieves customer\_id and name columns from the Customers table, while the second SELECT retrieves customer\_id from the Orders table and uses a placeholder ('' AS name) to align with the column structure. The resulting rows will contain customer information from both tables.

## What is the difference between VARCHAR and CHAR in SQL?

* VARCHAR uses only as many characters as are stored in the column. It is a variable length data type.
* CHAR reserves a fixed number of characters for the column. It is a fixed length data type.

SQL Example: Suppose we have a table called Employees with a column called address. We want to store employee addresses with varying lengths.

Using VARCHAR:

```sql
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(200)
);
```

In this example, the VARCHAR data type is used for the address column. It can store addresses with varying lengths, up to a maximum of 200 characters.

Using CHAR:

```sql
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    address CHAR(200)
);
```

In this example, the CHAR data type is used for the address column. It reserves a fixed length of 200 characters for each address, padding shorter addresses with spaces.

## What is the difference between DELETE and TRUNCATE in SQL?

* DELETE removes rows from a table.
* TRUNCATE empties the entire table and resets the auto-increment counter.

SQL Example: Suppose we have a table called Customers with multiple rows of data. We want to either remove specific rows or empty the entire table.

Using DELETE:

```sql
DELETE FROM Customers WHERE customer_id = 5;
```

In this example, the DELETE statement removes the row from the Customers table where the customer\_id is 5.

Using TRUNCATE:

```sql
TRUNCATE TABLE Customers;
```

In this example, the TRUNCATE TABLE statement empties the entire Customers table. It removes all rows and resets any auto-increment counters associated with the table.

## What is the difference between a LEFT JOIN and a RIGHT JOIN in SQL? 🤔🔗

Answer: A LEFT JOIN returns all the rows from the left table and the matching rows

from the right table, while a RIGHT JOIN returns all the rows from the right table and the matching rows from the left table.

SQL Example: Suppose we have two tables: Customers and Orders. We want to retrieve a list of all customers and their corresponding orders, regardless of whether they have placed any orders.

Using LEFT JOIN:

```sql
SELECT Customers.customer_id, Customers.name, Orders.order_id
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;
```

In this example, the LEFT JOIN combines all rows from the Customers table with matching rows from the Orders table based on the customer\_id column. The result will include all customers, whether they have orders or not.

Using RIGHT JOIN:

```sql
SELECT Customers.customer_id, Customers.name, Orders.order_id
FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;
```

In this example, the RIGHT JOIN combines all rows from the Orders table with matching rows from the Customers table based on the customer\_id column. The result will include all orders, whether they have corresponding customers or not.

## What is the difference between a GROUP BY and an ORDER BY clause in SQL? 🤔🔗

Answer: A GROUP BY clause is used to group rows in a table based on a specific column, while an ORDER BY clause is used to sort the resulting rows in a specific order.

SQL Example: Suppose we have a table called Orders with columns like order\_id, customer\_id, and order\_date. We want to retrieve the total order count for each customer, sorted by the highest count.

Using GROUP BY and ORDER BY:

```sql
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
ORDER BY order_count DESC;
```

In this example, the GROUP BY clause is used to group the rows based on the customer\_id column. The COUNT() function is used to calculate the order count for each customer. The ORDER BY clause then sorts the resulting rows in descending order based on the order\_count.

## What is the difference between a view and a table in SQL? 🤔🔗

Answer: A table is a physical storage structure that contains data, while a view is a virtual table that is based on the result of a SELECT statement.

SQL Example: Suppose we have a table called Employees with columns like employee\_id, name, and department. We want to create a view that retrieves specific information from the Employees table.

Creating a view:

```sql
CREATE VIEW EmployeeSummary AS
SELECT employee_id, name, department
FROM Employees
WHERE department = 'Sales';
```

In this example, the EmployeeSummary view is created based on the SELECT statement. It selects the employee\_id, name, and department columns from the Employees table, filtering for the 'Sales' department.

Using a table:

```sql
SELECT employee_id, name, department
FROM Employees
WHERE department = 'Sales';
```

In this example, the query directly selects the information from the Employees table, achieving the same result as the view. However, a view provides a way to save and reuse complex queries or subsets of data without duplicating the underlying query logic.

## What is the difference between the LIKE and the IN operators in SQL? 🤔🔗

Answer: The LIKE operator is used to match a pattern in a column, while the IN operator is used to match a value to a list of possible values for a column.

SQL Example: Suppose we have a table called Products with a column called product\_name. We want to retrieve products that match a specific pattern or are in a specific category.

Using LIKE:

```sql
SELECT * FROM Products WHERE product_name LIKE 'Apple%';
```

In this example, the LIKE operator is used to match product\_names that start with 'Apple'. The '%' wildcard represents any number of characters after 'Apple'.

Using IN:

```sql
SELECT * FROM Products WHERE category IN ('Electronics', 'Appliances')\
```

In this example, the IN operator is used to match products that belong to the 'Electronics' or 'Appliances' category. The list of values is enclosed in parentheses and separated by commas.

Note: The specific syntax and wildcard characters may vary depending on the database system being used.

## What is the difference between a database and a schema in SQL? 🤔🔗

Answer: A database is a collection of related data that is stored together, while a schema is a logical container for database objects such as tables, views, and procedures.