Question: Retrieve the names of customers who have placed orders.
Answer:
SELECT DISTINCT [Link]
FROM customers c
JOIN orders o ON [Link] = [Link];
Explanation: This query retrieves the distinct names of customers who have placed
orders by joining the "customers" and "orders" tables on the "customer_id" column.
Question: List the names of customers who have not placed any orders.
Answer:
SELECT [Link]
FROM customers c
LEFT JOIN orders o ON [Link] = [Link]
WHERE [Link] IS NULL;
Explanation: It lists the names of customers who have not placed any orders by
performing a left join between the "customers" and "orders" tables and filtering
for cases where there is no corresponding order.
Question: Display the total number of orders made by each customer.
Answer:
SELECT [Link] ,COUNT([Link]) AS total_orders
FROM customers c
LEFT JOIN orders o ON [Link] = [Link]
GROUP BY [Link];
Explanation: This query calculates the total number of orders made by each customer
by joining the "customers" and "orders" tables and using the COUNT function to
count the number of orders for each customer.
Question: Retrieve the customer names who have placed orders with an order date
after 1996-07-15.
Answer:
SELECT DISTINCT [Link]
FROM customers c
JOIN orders o ON [Link] = [Link]
WHERE [Link] > '1996-07-15';
Explanation: It retrieves the distinct names of customers who have placed orders
after 1996-07-15 by joining the "customers" and "orders" tables on the
"customer_id" column and applying a filter on the order date.
Question: List the customers who have placed orders from the UK.
Answer:
SELECT DISTINCT [Link]
FROM customers c
JOIN orders o ON [Link] = [Link]
WHERE [Link] = 'UK';
Explanation: This query lists the distinct names of customers who have placed
orders from the UK by joining the "customers" and "orders" tables and filtering for
customers from the UK.
Question: Retrieve the names of customers along with the number of orders they have
placed.
Answer:
SELECT [Link], COUNT([Link]) AS num_orders
FROM customers c
LEFT JOIN orders o ON [Link] = [Link]
GROUP BY [Link];
Explanation: This query retrieves the names of customers along with the number of
orders they have placed by joining the "customers" and "orders" tables and using
the COUNT function to count the orders for each customer.
Question: List the customers who have placed orders in Brazil or France.
Answer:
SELECT DISTINCT [Link]
FROM customers c
JOIN orders o ON [Link] = [Link]
WHERE [Link] IN ('Brazil', 'France');
Explanation: It lists the distinct names of customers who have placed orders in
Brazil or France by joining the "customers" and "orders" tables and filtering for
customers from either Brazil or France.
2900
966
966
483
483
966
reading
ashish Null
M 2069
P 2046
Question: Display the names of customers who have placed more than 3 orders.
Answer:
SELECT [Link]
FROM customers c
JOIN (
SELECT customerId, COUNT(orderId) AS order_count
FROM orders
GROUP BY customerId
HAVING COUNT(orderId) > 3
) sub ON [Link] = [Link];
Explanation: This query displays the names of customers who have placed more than 3
orders by joining the "customers" table with a subquery that calculates the order
count for each customer and filters for those with more than 3 orders.
Question: Get the average number of orders placed by customers.
Answer:
SELECT AVG(num_orders) AS average_orders_per_customer
FROM (
SELECT COUNT(orderId) AS num_orders
FROM orders
GROUP BY customerId
) sub;
Explanation: It calculates the average number of orders placed by customers by
first counting the number of orders for each customer in a subquery and then
computing the average of those counts.
Question: Find the top 3 customers who have placed the most orders.
Answer:
SELECT [Link], COUNT([Link]) AS order_count
FROM customers c
JOIN orders o ON [Link] = [Link]
GROUP BY [Link]
ORDER BY order_count DESC
LIMIT 3;
Explanation:
In this query:
We select the customer_name from the customer table and count the number of orders
for each customer.
We join the customer and orders tables using the customer_id column, which is
common between them.
Then, we group the results by customer_name.
After that, we order the results in descending order of order count using ORDER BY
order_count DESC.
Finally, we limit the output to the top 3 customers who have placed the most orders
using LIMIT 3.
This query allows us to identify the top 3 customers with the highest number of
orders placed. Adjustments may be required based on the actual schema and naming
conventions of the database.