SQL Query Interview Questions and Answers for Practice

Question No. 1

Suppose you have a database for an online store with two tables: Customers and Orders. The Customers table contains customer information, including customer_id, customer_name, and customer_email. The Orders table contains order information, including order_id, customer_id (which is a foreign key linking to the customer_id in the Customers table), and order_date.

Write an SQL query to retrieve the names and email addresses of customers who have placed an order in the year 2023. Your query should include a JOIN operation to connect the two tables and a WHERE clause to filter the results.

Solution -

-- Create the Customers table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255)
);

-- Create the Orders table
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Insert sample data into the Customers table
INSERT INTO Customers (customer_id, customer_name, customer_email)
VALUES
    (1, 'Pankaj Chouhan', '[e-mail]'),
    (2, 'Nishant Chouhan', '[e-mail]'),
    (3, 'Kiran Johnson', '[e-mail]');

-- Insert sample data into the Orders table
INSERT INTO Orders (order_id, customer_id, order_date)
VALUES
    (101, 1, '2023-03-15'),
    (102, 2, '2023-04-10'),
    (103, 1, '2023-05-20'),
    (104, 3, '2022-12-05'),
    (105, 2, '2023-08-30');

-- Retrieve the names and email addresses of customers who placed an order in 2023
SELECT c.customer_name, c.customer_email
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE YEAR(o.order_date) = 2023;

Output:

+--------------------+-----------------------------+
| customer_name      | customer_email              |
+--------------------+-----------------------------+
| Pankaj Chouhan     | [e-mail]  |
| Nishant Chouhan    | [e-mail]   |
+--------------------+-----------------------------+