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', 'pankaj@codeswithpankaj.com'),
(2, 'Nishant Chouhan', 'nishant@codeswithpankaj.com'),
(3, 'Kiran Johnson', 'kiran@codeswithpankaj.com');
-- 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 | pankaj@codeswithpankaj.com |
| Nishant Chouhan | kiran@codeswithpankaj.com |
+--------------------+-----------------------------+
Top comments (0)