MySQL Assignment: SQL Queries for Customer and Order Data Analysis

This assignment is a comprehensive solution for a MySQL assignment, focusing on querying a classicmodels database. The solution includes two SQL queries designed to extract specific information about customers and their orders. The first query identifies customers who haven't placed any orders using a nested query and the NOT IN operator. The results are sorted by customer number. The second query lists customers who have placed fewer than five orders, also employing a subquery, the COUNT function, and the GROUP BY and HAVING clauses to filter the results. The assignment demonstrates practical application of SQL concepts like subqueries, aggregation, and filtering in a database context. The document concludes with a summary of the learning experience and a bibliography of relevant resources.

Running head: MYSQL 1
Assignment
Student
Course
Instructor
Date

MYSQL 2
Introduction
We are using the classicmodels database available at http://www.mysqltutorial.org/mysql-sample-
database.aspx. The classicmodels database is a retailer of scale models of classic cars database. It
contains typical business data like information about customers, products and sales orders. The
database has the following tables:
1. Customers: It stores the data of all the customers.
2. Products: It stores a list of scale model cars.
3. ProductLines: It stores a list of various categories of product lines.
4. Orders: It stores all the scale orders placed by various customers.
5. OrderDetails: It stores the sales order line items for each of the Sale order.
6. Payments: It stores the list of various payments made by the customers based on their accounts.
7. Employees: It stores all the information about various employees and their reporting
information.
8. Offices: It stores the sales offices data.

MYSQL 3
Queries
1. list the customers who have not placed any orders. Sort your results by customer number.
Ans.
Query:
SELECT * FROM customers WHERE
customerNumber NOT IN (SELECT customerNumber FROM Orders)
ORDER BY customerNumber;
Description:
In this Query, we are using a nested query and an order by clause for fetching the list of all the
customers who have not placed any order. The nested query is fetching all the customer numbers
from the orders table. The orders table contains the customer details of all the customers who have
placed an order. After fetching all the customer numbers who have placed an order, we are using
NOT IN for getting the details of all the customers where the customer Number is not fetched by
the subquery. So, in this step, we have got all the customers who have not placed any order. Finally,
we are using the ORDER BY clause to sort our results on the basis of Customer Number. As we
have not mentioned ASC/DESC with the order by clause. So, all the results will be sorted by
customer number in ascending order.

MYSQL 4
Result:

MYSQL 5
Q2. Modify your query to list customers who have placed fewer than five orders.
Ans.
Query:
SELECT *FROM customers WHERE
customerNumber IN (SELECT customerNumber FROM (SELECT customerNumber,
COUNT(orderNumber) AS orderNumber FROM Orders
GROUP BY customerNumber
HAVING COUNT(orderNumber)<5) TEMP)
ORDER BY customerNumber;
Description: In this query also, we are using a subquery and an Order by clause. We are also using
HAVING and COUNT in this query.
Here, the subquery is fetching the customer numbers of all the customers who have placed lesser
than 5 orders. To check that the customers have placed less than 5 orders, we are using COUNT
and GROUP BY clause. The group by is grouping all the rows by the customer Numbers of the
customers, then we are fetching the count of the orders and checking that the count must be less
than 5.
After fetching all the customer Numbers which have placed fewer than 5 orders, we are using NOT
IN for getting the details of all the customers where the customer Number is not fetched by the
subquery. So, in this step, we have got all the customers who have not placed any order. Finally,
we are using the ORDER BY clause to sort our results on the basis of Customer Number. As we

MYSQL 6
have not mentioned ASC/DESC with the order by clause. So, all the results will be sorted by
customer number in ascending order.
Result:

MYSQL 7
Conclusion
We have learnt a lot of things in this assignment. We have made the use of MYSQL workbench
for this assignment. We learnt about the use of HAVING, GROUP BY, COUNT and subqueries
while working on this assignment. The assignment helped us to learn SQL queries and instantiate
our database through the provided dump file.
Bibliography
MySQL :: MySQL Tutorial. (2019). Retrieved from https://dev.mysql.com/doc/mysql-tutorial-
excerpt/5.5/en/
MySQL Subquery. (2019). Retrieved from
https://www.quackit.com/mysql/examples/mysql_subquery.cfm
MySQL Tutorial - w3resource. (2019). Retrieved from
https://www.w3resource.com/mysql/mysql-tutorials.php
MySQL Tutorial. (2019). Retrieved from https://www.tutorialspoint.com/mysql/