1. Data Types and Functions

In SQL, data types are used to define the structure and format of data in a database. Different data types have different properties and constraints that affect how the data is stored and processed. Common data types in SQL are:

INT: This is an integer number that can be positive or negative.
VARCHAR: These are variable-length strings of characters that can store up to a specified number of characters.
DATE: This is a date value in the format YYYY-MM-DD.
DECIMAL: A decimal number that can have a fixed or variable number of digits after the decimal point.

SQL functions are predefined operations that can be applied to data in a database. They can perform various tasks, such as calculations, conversions, formatting, or manipulation of data. There are many types of SQL functions, depending on the data type they operate on or the purpose they serve. Functions can be applied to one or more columns or values in a query. Common functions in SQL are:

a. Aggregate functions: These functions take a set of values as input and return a single value that summarizes them. Aggregate functions are often used with the GROUP BY clause to calculate statistics for each group of rows. These functions include:

COUNT() — returns the number of rows in a table
SUM() — returns the total sum of a numeric column
AVG() — returns the average value of a numeric column
MIN() and MAX() — return the minimum and maximum values of a column

b. String functions: String functions deal with character data and perform various operations on strings, such as concatenation, extraction, replacement, conversion, and comparison. They are:

CONCAT() — joins two or more strings together
SUBSTRING() — returns a part of a string
REPLACE() — replaces a substring with another string
UPPER() and LOWER() — convert a string to uppercase or lowercase
LENGTH() — returns the number of characters in a string.

c. Date and time functions: These functions handle date and time data and perform various operations on them like extraction, conversion, addition, subtraction, and comparison.

DATE() — returns the date part of a datetime value
TIME() returns the time part of a datetime value
YEAR(), MONTH(), and DAY() — return the year, month, and day components of a date value
HOUR(), MINUTE(), and SECOND() — return the hour, minute, and second components of a time value
CURRENT_DATE() and CURRENT_TIME() — return the current date and time of the system.

Interview Question 1: What are the differences between CHAR and VARCHARdata types in SQL?

Answer: CHAR and VARCHAR are both character data types that store strings of characters. The main difference is that CHAR has a fixed length, while VARCHAR has a variable length. This means that CHAR always uses the same amount of storage space for each value, regardless of its actual length, while VARCHAR only uses the space needed for the value plus some overhead. In other words, CHAR(10)will always use 10 bytes for each value, even if the value is only 3 characters long. On the other hand, VARCHAR(10)uses 4 bytes for a 3-character value, 5 bytes for a 4-character value, and so on, up to a maximum of 11 bytes for a 10-character value.

Interview Question 2: Write a query to display the first name, last name, and email address of all customers whose email address ends with ‘.com’ and whose first name starts with ‘A’. (Assume the ‘customers’ table has customer details like full names, email addresses, phone numbers, and dates of birth)

Answer:

SELECT first_name, last_name, email
FROM customers
WHERE email LIKE '%.com' AND first_name LIKE 'A%';

Interview Question 3: Write a query to display the name and salary of the employee who has the highest salary in each department. (Assume the ‘employee’ table has columns named emp_name, emp_salary, and dept_id, and there is another table named ‘department’ with columns named dept_id and dept_name)

Answer:

SELECT e.emp_name, e.emp_salary, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
WHERE e.emp_salary = (
  SELECT MAX(emp_salary)
  FROM employee
  WHERE dept_id = e.dept_id
);

2. Joins and Subqueries

Joins are used to combine data from multiple tables based on a common column or condition. They allow you to create more complex and meaningful queries that involve multiple sources of data. Most common types of joins in SQL are:

INNER JOIN—returns only the rows that match in both tables.
LEFT JOIN — returns all the rows from the left table and the matching rows from the right table. If there is no match, the right column will be NULL.
RIGHT JOIN— returns all the rows from the right table and the matching rows from the left table. If there is no match, the left column will be NULL.
FULL JOIN—returns all the rows from both tables. If there is no match, the corresponding column will be NULL.

Subqueries are used to nest one query inside another query to filter or aggregate data. They can be used in various clauses of a query, such as WHERE, HAVING, and SELECT. Subqueries can return a single value, a list of values, or a table. Some of the different types of subqueries in SQL are:

IN—checks if a value is in a list of values returned by a subquery.
EXISTS—checks if a subquery returns any rows.
ANY — compares a value to any value returned by a subquery using a comparison operator.
ALL— compares a value to all values returned by a subquery using a comparison operator.

Interview Question 4: Write a query to display the name and salary of all employees who work in the same department as the employee with the highest salary. (Schema: ‘employees’ table (id, name, salary, dept_id))

Answer:

-- Find the department id and salary of the employee with the highest salary
SELECT dept_id, MAX(salary) AS max_salary
FROM employees
-- Join the employees table with the subquery result
SELECT e.name, e.salary
FROM employees e
INNER JOIN (
  SELECT dept_id, MAX(salary) AS max_salary
  FROM employees
) m
ON e.dept_id = m.dept_id
-- Filter the employees who work in the same department as the employee with the highest salary
WHERE e.salary = m.max_salary

Interview Question 5: Write a SQL query to find the names and prices of the products that have a higher price than the average price of all products. (Use the following schema: products (id, name, price, category_id) categories (id, name))

Answer:

-- Find the average price of all products
SELECT AVG(price) AS avg_price
FROM products
-- Filter the products that have a higher price than the average price
SELECT p.name, p.price
FROM products p
WHERE p.price > (
  -- Use the subquery result as a single value
  SELECT AVG(price) AS avg_price
  FROM products
)

3. Indexes and Transactions

Indexes are used to speed up the execution of queries by creating a sorted structure of data that can be searched quickly. They reduce the need to scan the entire table for every query, which can improve performance and efficiency. Common indexes in SQL are:

Clustered index: Sorts and stores the data rows in the table based on the index key. There can be only one clustered index per table.
Non-clustered index: Creates a separate structure from the table that contains only the index key and a pointer to the corresponding row in the table. There can be multiple non-clustered indexes per table.
Unique index: Ensures that no two rows have the same value for the index key. A primary key is a special type of unique index that uniquely identifies each row in the table.

On the other hand, transactions are used to ensure the consistency and integrity of data by grouping a set of queries that either all succeed or all fail together. Transactions allow you to perform multiple operations as a single unit of work, which can prevent data corruption or inconsistency in case of errors or interruptions. Common commands related to transactions in SQL are:

BEGIN TRANSACTION—starts a new transaction.
COMMIT TRANSACTION—ends the transaction and saves the changes to the database.
ROLLBACK TRANSACTION—ends the transaction and discards the changes to the database.

Interview Question 6: Write a SQL query to create a clustered index on the employees table based on the department_id column. Explain why you chose this column and how it affects the performance of the query.

Answer:

-- Create a clustered index on the employees table
CREATE CLUSTERED INDEX idx_employees_dept_id
ON employees (department_id)

The reason for choosing the department_id column as the index key is that it is likely to be used frequently in queries that join the employees table with other tables, such as the departments table. By creating a clustered index on this column, the data rows in the employees table will be sorted and stored based on the department_id values, which can improve the speed and efficiency of the join operations.

Interview Question 7: Write a SQL query to transfer 1000 units of currency from account A to account B. (Schema: accounts (id, name, balance)

-- Start a new transaction
BEGIN TRANSACTION

-- Update the balance of account A by subtracting 1000 units
UPDATE accounts
SET balance = balance - 1000
WHERE id = 'A'

-- Update the balance of account B by adding 1000 units
UPDATE accounts
SET balance = balance + 1000
WHERE id = 'B'

-- Check if the transfer was successful
IF @@ERROR = 0
  -- Commit the transaction and save the changes
  COMMIT TRANSACTION
ELSE
  -- Rollback the transaction and discard the changes
  ROLLBACK TRANSACTION

Interview Question 8: Write a query to create a unique index on the email column of the customers table. Then write a transaction that inserts a new customer with an email that already exists in the table. What will happen when you try to commit the transaction?

-- Create a unique index on the email column of the customers table
CREATE UNIQUE INDEX idx_customers_email
ON customers (email)
-- Start a new transaction
BEGIN TRANSACTION

-- Insert a new customer with an email that already exists
INSERT INTO customers (id, name, email)
VALUES (101, 'Alice', 'alice@example.com')

-- Commit the transaction
COMMIT TRANSACTION

The transaction will fail because it will violate the unique constraint on the email column. The INSERTstatement will generate an error message and the transaction will be rolled back. No new customer will be added to the table.

4. Constraints and Triggers

Constraints are rules that are applied to the columns or tables to ensure the validity and accuracy of the data. They can be defined at the column level or the table level. Constraints are declarative, meaning that they specify what the data should be like, but not how to enforce it. For example, a constraint can say that a column cannot have null values or that a column must have unique values.

Constraints are checked by the database system before any data modification takes place, and if the constraint is violated, the modification is aborted and an error is returned. They are usually defined when the table is created using the CREATE TABLEstatement, or after the table is created using the ALTER TABLE statement. Common constraints in SQL are:

NOT NULL—prevents null values from being inserted into the column.
CHECK—ensures that the values in the column satisfy a specific condition.
DEFAULT—specifies a default value for the column when no value is provided.
UNIQUE—ensures that the values in the column are unique across the table.
PRIMARY KEY—a combination of NOT NULL and UNIQUE constraints that uniquely identifies each row in the table.
FOREIGN KEY—a reference to a primary key of another table that establishes a relationship between the tables.

Triggers are special procedures that are executed automatically when a specific event occurs on a table or a view. Triggers can be used to perform various tasks, such as auditing, logging, validation, or synchronization of data. For example, a trigger can say that when a new row is inserted into a table, another row should be inserted into another table or when a row is updated in a table, a log should be written to a file.

Triggers are executed by the database system after or before the data modification takes place, depending on the type of trigger. They are usually defined using the CREATE TRIGGERstatement, and they can be activated by INSERT, UPDATE, or DELETE statements on a table. Some of the common types of triggers in SQL are:

BEFORE—executes before the triggering event occurs.
AFTER—executes after the triggering event occurs.
INSTEAD OF—replaces the triggering event with a custom action.
FOR EACH ROW—executes for each row affected by the triggering event.
FOR EACH STATEMENT—executes once for each statement that causes the triggering event.

Interview Question 9: Write a SQL query to create a table called students with the following columns and constraints:

~ id: an integer that is the primary key of the table.

~ name: a string that cannot be null and has a maximum length of 50 characters.

~ age: an integer that cannot be null and must be between 18 and 25.

~ email: a string that must be unique and have a valid email format.

Answer

-- Create a table called students
CREATE TABLE students (
  -- Define the id column as an integer and the primary key
  id INT PRIMARY KEY,
  -- Define the name column as a string that cannot be null and has a maximum length of 50
  name VARCHAR(50) NOT NULL,
  -- Define the age column as an integer that cannot be null and must be between 18 and 25
  age INT NOT NULL CHECK (age >= 18 AND age <= 25),
  -- Define the email column as a string that must be unique and have a valid email format
  email VARCHAR(255) UNIQUE CHECK (email LIKE '%@%.%')
)

Interview Question 10: Write a SQL query to create a trigger called log_update that logs every update on the employees table to a file called update_log.txt. The trigger should write the old and new values of the name and salary columns, as well as the date and time of the update.

Answer:

-- Create a trigger called log_update
CREATE TRIGGER log_update
-- Execute after every update on the employees table
AFTER UPDATE ON employees
-- Execute for each row affected by the update
FOR EACH ROW
-- Define the trigger action
BEGIN
  -- Write to a file called update_log.txt
  WRITE TO FILE 'update_log.txt'
  -- Write the old and new values of the name and salary columns, as well as the date and time of the update
  'Old name: ' || OLD.name || ', New name: ' || NEW.name || '\n' ||
  'Old salary: ' || OLD.salary || ', New salary: ' || NEW.salary || '\n' ||
  'Update date and time: ' || CURRENT_DATE || ' ' || CURRENT_TIME || '\n'
END

Interview Question 11: Write a query to create a table named ‘products’ with the following columns and constraints: id (int, primary key, auto-increment), name (varchar, not null, unique), price (decimal, not null, check if greater than zero), category (varchar, default ‘General’). Then write a trigger that inserts a record into a table named ‘audit’ with the current date and time whenever a new product is inserted into the products table.

-- Create a table named products
CREATE TABLE products (
  -- Define the id column as an integer, primary key, and auto-increment
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- Define the name column as a varchar that cannot be null and must be unique
  name VARCHAR(255) NOT NULL UNIQUE,
  -- Define the price column as a decimal that cannot be null and must be greater than zero
  price DECIMAL(10,2) NOT NULL CHECK (price > 0),
  -- Define the category column as a varchar that has a default value of 'General'
  category VARCHAR(255) DEFAULT 'General'
)
-- Create a trigger named log_insert
CREATE TRIGGER log_insert
-- Execute after every insert on the products table
AFTER INSERT ON products
-- Execute for each row inserted into the products table
FOR EACH ROW
-- Define the trigger action
BEGIN
  -- Insert a record into the audit table with the current date and time
  INSERT INTO audit (date_time)
  VALUES (CURRENT_TIMESTAMP)
END

5. Views and Stored Procedures

Views are virtual tables that are created by querying one or more base tables or views. They do not store any data of their own, but only display the data from the underlying tables or views. Views can be used to simplify complex queries, provide security, or hide irrelevant details from users.It is defined by the CREATE VIEW statement.

Advantages of Views:

Provide a consistent and logical view of data regardless of how it is stored or changed in the base tables or views.
Reduce the complexity and redundancy of queries by encapsulating them in a single view.
Provide access control and data protection by restricting users to see only certain columns or rows of data.

Stored procedures are precompiled and reusable SQL code blocks that can perform one or more tasks. These sets of SQL statements accept parameters, return values, and use variables, loops, conditions, and other programming constructs. Stored procedures can be used to improve performance, security, and modularity of SQL code. It is defined by the CREATE PROCEDURE statement and executed by either EXECUTE or CALL statements.

Advantages of stored procedures

Stored procedures are compiled once and stored in the database server, which reduces the network traffic and execution time.
They can encapsulate complex logic and business rules in a single procedure, making them easier to maintain and debug.
Prevent SQL injection attacks by using parameterized queries and validating user input.

Interview Question 12: Write a SQL query to create a view called top_customers that shows the names and total amounts of the customers who have spent more than 1000 units of currency in the orders table. (Schema: customers (id, name, email), orders (id, customer_id, amount, date))

Answer:

-- Create a view called top_customers
CREATE VIEW top_customers AS
-- Select the names and total amounts of the customers
SELECT c.name, SUM(o.amount) AS total_amount
-- Join the customers table with the orders table
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id
-- Group by the customer id and name
GROUP BY c.id, c.name
-- Filter the customers who have spent more than 1000 units of currency
HAVING SUM(o.amount) > 1000

Interview Question 13: Write a SQL query to create a stored procedure called get_customers_by_category that accepts a category name as a parameter and returns the names and emails of the customers who have ordered products from that category. Use the following schema:

customers (id, name, email)
orders (id, customer_id, product_id, amount, date)
products (id, name, price, category_id)
categories (id, name)

Answer:

-- Create a stored procedure called get_customers_by_category
CREATE PROCEDURE get_customers_by_category
-- Accept a category name as a parameter
(@category_name VARCHAR(255))
-- Define the procedure body
AS
BEGIN
  -- Select the names and emails of the customers
  SELECT DISTINCT c.name, c.email
  -- Join the customers table with the orders table
  FROM customers c
  INNER JOIN orders o
  ON c.id = o.customer_id
  -- Join the orders table with the products table
  INNER JOIN products p
  ON o.product_id = p.id
  -- Join the products table with the categories table
  INNER JOIN categories ca
  ON p.category_id = ca.id
  -- Filter the customers who have ordered products from the given category name
  WHERE ca.name = @category_name
END

Interview Question 14: Write a query to create a view named top_customers that shows the name and total amount spent by the top 10 customers who have made at least one purchase in the last month. Then write a stored procedure named get_top_customers that accepts a parameter named month and returns the same result as the view for any given month.

-- Create a view named top_customers
CREATE VIEW top_customers AS
-- Select the name and total amount spent by the customers
SELECT c.name, SUM(o.amount) AS total_amount
-- Join the customers table with the orders table
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id
-- Filter the customers who have made at least one purchase in the last month
WHERE o.date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
-- Group by the customer id and name
GROUP BY c.id, c.name
-- Order by the total amount spent in descending order
ORDER BY total_amount DESC
-- Limit the result to the top 10 customers
LIMIT 10

-- Create a stored procedure named get_top_customers
CREATE PROCEDURE get_top_customers
-- Accept a month as a parameter
(@month INT)
-- Define the procedure body
AS
BEGIN
  -- Select the name and total amount spent by the customers
  SELECT c.name, SUM(o.amount) AS total_amount
  -- Join the customers table with the orders table
  FROM customers c
  INNER JOIN orders o
  ON c.id = o.customer_id
  -- Filter the customers who have made at least one purchase in the given month
  WHERE MONTH(o.date) = @month
  -- Group by the customer id and name
  GROUP BY c.id, c.name
  -- Order by the total amount spent in descending order
  ORDER BY total_amount DESC
  -- Limit the result to the top 10 customers
  LIMIT 10
END

Conclusion

This curated guide has covered some of the most common and challenging SQL technical interview questions and answers. The article has provided you with key SQL concepts recruiters are likely to test you in. It also provides the reader with a well-guided approach on how to answer some of the toughest SQL technical interview questions in these testable areas. Therefore, by following this tutorial, you will be able to improve your SQL skills and knowledge and ace your next SQL interview.

Garbage Collection as a Memory Management Technique in Python

Demystifying the concept of Garbage Collection in Python

medium.com

Caching in PHP Web Applications: A Developer Guide

Caching improves the performance and scalability of web applications by reducing the server load and database queries

medium.com

How to Implement a Data-Driven Customer Experience: Looking Beyond CX Software

Customer Personalization As the Future of e-commerce

medium.com

Top 5 Reasons To Learn Data Science In 2023 (Why Data Science?)

Why Learn Data Science Now?

medium.com

Top 5 SQL Topics to Master for Technical Interview in 2023

Ace your next SQL Interview like a pro with these well-explained SQL concepts and curated sample SQL technical interview questions

1. Data Types and Functions

2. Joins and Subqueries

3. Indexes and Transactions

4. Constraints and Triggers

5. Views and Stored Procedures

Conclusion

Garbage Collection as a Memory Management Technique in Python

Demystifying the concept of Garbage Collection in Python

Caching in PHP Web Applications: A Developer Guide

Caching improves the performance and scalability of web applications by reducing the server load and database queries

How to Implement a Data-Driven Customer Experience: Looking Beyond CX Software

Customer Personalization As the Future of e-commerce

Top 5 Reasons To Learn Data Science In 2023 (Why Data Science?)

Why Learn Data Science Now?

Written by Joseph Osoo