MySQL Assignment: Queries on the Classicmodels Database - Analysis

This assignment focuses on utilizing MySQL to analyze the classicmodels database, a retailer of scale models. The student was tasked with writing SQL queries to retrieve specific information from the database. The assignment involves retrieving customers who have not placed any orders and customers who have placed fewer than five orders. The solutions utilize subqueries, the NOT IN operator, the COUNT function, and the GROUP BY and HAVING clauses to efficiently retrieve and filter data. The assignment demonstrates the practical application of SQL for data analysis and database querying, including the use of MySQL Workbench. The student also provides a conclusion summarizing their learning experience and references relevant MySQL documentation.

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

MYSQL 6
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.
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/

MYSQL 8