❔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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

SELECT GETDATE();

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

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:

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:

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:

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:

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):

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(*):

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

PreviousWildcard CharacterNextTest Cases

Last updated