logo

Classic Models Database

   

Added on  2022-10-18

7 Pages790 Words256 Views
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.

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Assignment on the MYSQ 2022
|8
|780
|11

THE ACCOUNTING INFORMATION SYSTEM
|14
|1370
|16

Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertations
|4
|744
|354

Assignment On Functional Dependencies In ER Diagram
|7
|813
|260

Customer Name and Phone Number for All Students Name of the University Author Note
|22
|2019
|68

Database Management System
|8
|948
|176