Database Management System Assignment 2022

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University
Author Note

1DATABASE MANAGEMENT SYSTEM
Table of Contents1
AMC ERD..................................................................................................................................2
Normalised Schema...................................................................................................................3
Relational Schema Table............................................................................................................3
AMCDB Database Schema........................................................................................................9
Table Structure...........................................................................................................................9
Referral Integrity Constraints...................................................................................................25
Queries.....................................................................................................................................26

2DATABASE MANAGEMENT SYSTEM
AMC ERD
Figure 1: AMC Entity Relationship Diagram
Source: created by author

3DATABASE MANAGEMENT SYSTEM
Normalised Schema
Figure 2: AMC Normalised ERD
Source: created by author
Relational Schema Table
1. Patron

4DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Cnumber varchar 10 Primary
Fname varchar 50
Lname varchar 50
Phone int 10
Address varchar 50
2. Orders
Field Name Data type Length Key Constraints
Sl no int 11 Primary
Onumber varchar 10
Odate date
Customer_cnumber varchar 10 Primary, Foreign
3. Employee

5DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Enumber varchar 10 Primary
Fname varchar 50
Lname varchar 50
Address varchar 100
Mobile int 10
Email varchar 50
Tfn int 10
Join date
Department_Dnumber int 100 Primary, Foreign
Store_Snumber int 100 Primary, Foreign
4. Fulltime
Field Name Data type Length Key Constraints
Salary int 11
Designation varchar 45
Employee_Enumber varchar 10 Primary, Foreign
5. Casual
Field Name Data type Length Key Constraints
Hourlyrate int 11
Employee_Enumber varchar 10 Primary, Foreign
6. Department

6DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Dnumber int 100 Primary
Dname varchar 50
Email varchar 50
Phone int 10
7. Inventory
Field Name Data type Length Key Constraints
Store_Snumber int 100 Primary, Foreign
Product_Prnumber int 10 Primary, Foreign
Available int 11
Ordered int 11
8. Payslip
Field Name Data type Length Key Constraints
Employee_Enumber varchar 10 Primary, Foreign
Hours varchar 10
Grosspay int 10
9. Product

7DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Prnumber int 10 Primary key
Prtype varchar 50
Name varchar 50
Brand varchar 50
Description varchar 50
Price int 10
10. Store
Field Name Data type Length Key Constraints
Snumber int 100 Primary
Sname varchar 50
PhoneNumber int 50
Email varchar 50
Fax int 50
Street_no varchar 10
Suburb varchar 50
State varchar 50
Postcode int 6
11. Suppliers

8DATABASE MANAGEMENT SYSTEM
Field Name Data type Length Key Constraints
Spid int 11 Primary
Name varchar 50
Phone int 10
12. Supplier_has_product
Field Name Data type Length Key Constraints
Suppliers_spid int 11 Primary, Foreign
Product_prnumber int 10 Primary, Foreign
13. Orders_has_product
Field Name Data type Length Key Constraints
Order_sl no int 11 Primary, Foreign
Product_prnumber int 10 Primary, Foreign
Quantity int 11

9DATABASE MANAGEMENT SYSTEM
AMCDB Database Schema
Figure 3: Relational Schema of AMCDB
Source: created by author
Table Structure
1. Patron

10DATABASE MANAGEMENT SYSTEM
Describe Patron;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Cnumber | varchar(10) | NO | PRI | NULL | |
| Fname | varchar(50) | NO | | NULL | |
| Lname | varchar(50) | NO | | NULL | |
| Phone | int(10) | NO | | NULL | |
| Address | varchar(50) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
2. Orders
Describe Orders;
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| Sl no | int(11) | NO | PRI | NULL | auto_increment |
| Onumber | varchar(10) | NO | | NULL | |
| Odate | date | NO | | NULL | |
| Customer_Cnumber | varchar(10) | NO | PRI | NULL | |
+------------------+-------------+------+-----+---------+----------------+
3. Employee

11DATABASE MANAGEMENT SYSTEM
Describe Employee;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| Enumber | varchar(10) | NO | PRI | NULL | |
| Fname | varchar(50) | NO | | NULL | |
| Lname | varchar(50) | NO | | NULL | |
| Address | varchar(100) | NO | | NULL | |
| Mobile | int(10) | NO | | NULL | |
| Email | varchar(50) | NO | | NULL | |
| Tfn | varchar(10) | NO | | NULL | |
| Join | date | NO | | NULL | |
| Department_Dnumber | int(100) | NO | PRI | NULL | |
| Store_Snumber | int(100) | NO | PRI | NULL | |
+--------------------+--------------+------+-----+---------+-------+
4. Fulltime
Describe Fulltime;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Salary | int(11) | YES | | NULL | |
| Designation | varchar(45) | YES | | NULL | |
| Employee_Enumber | varchar(10) | NO | PRI | NULL | |
+------------------+-------------+------+-----+---------+-------+
5. Casual

12DATABASE MANAGEMENT SYSTEM
Describe Casual;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Hourlyrate | int(11) | YES | | NULL | |
| Employee_Enumber | varchar(10) | NO | PRI | NULL | |
+------------------+-------------+------+-----+---------+-------+
6. Department
Describe Department;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Dnumber | int(100) | NO | PRI | NULL | |
| Dname | varchar(50) | NO | | NULL | |
| Email | varchar(50) | NO | | NULL | |
| Phone | int(10) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
7. Inventory

13DATABASE MANAGEMENT SYSTEM
Describe Inventory;
+------------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------+------+-----+---------+-------+
| Store_Snumber | int(100) | NO | PRI | NULL | |
| Product_prnumber | int(10) | NO | PRI | NULL | |
| Available | int(11) | YES | | NULL | |
| Ordered | int(11) | YES | | NULL | |
+------------------+----------+------+-----+---------+-------+
8. Payslip
Describe Payslip;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| Employee_Enumber | varchar(10) | NO | PRI | NULL | |
| Hours | varchar(10) | NO | | NULL | |
| Grosspay | int(10) | NO | | NULL | |
+------------------+-------------+------+-----+---------+-------+
9. Product

14DATABASE MANAGEMENT SYSTEM
Describe Product;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Prnumber | int(10) | NO | PRI | NULL | auto_increment |
| Prtype | varchar(50) | NO | | NULL | |
| Name | varchar(50) | NO | | NULL | |
| Brand | varchar(50) | NO | | NULL | |
| Description | varchar(50) | NO | | NULL | |
| Price | int(10) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
10. Store
Describe Store;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| Snumber | int(100) | NO | PRI | NULL | auto_increment |
| Sname | varchar(50) | NO | | NULL | |
| PhoneNumber | int(50) | NO | | NULL | |
| Email | varchar(50) | NO | | NULL | |
| Fax | int(50) | NO | | NULL | |
| Street_no | varchar(10) | NO | | NULL | |
| Suburb | varchar(50) | NO | | NULL | |
| State | varchar(50) | NO | | NULL | |
| Postcode | int(6) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
11. Suppliers

15DATABASE MANAGEMENT SYSTEM
Describe Suppliers;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Spid | int(11) | NO | PRI | NULL | |
| Name | varchar(50) | NO | | NULL | |
| Phone | int(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
12. Supplier_has_product
Describe Suppliers_has_product;
+------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| Suppliers_spid | int(11) | NO | PRI | NULL | |
| Product_prnumber | int(10) | NO | PRI | NULL | |
+------------------+---------+------+-----+---------+-------+
13. Orders_has_product
Describe Orders_has_product;
+------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| Orders_sl no | int(11) | NO | PRI | NULL | |
| Product_prnumber | int(10) | NO | PRI | NULL | |
| Quantity | int(11) | NO | | NULL | |
+------------------+---------+------+-----+---------+-------+

16DATABASE MANAGEMENT SYSTEM
Tables Data
1. Patron
Select * from Patron;
+---------+-------+--------+-------+---------------+
| Cnumber | Fname | Lname | Phone | Address |
+---------+-------+--------+-------+---------------+
| 10001 | Mike | Hugh | 64876 | Ohio |
| 10002 | Julie | Kum | 64876 | New York |
| 10003 | Jame | Wright | 64876 | Uptown |
| 10004 | Merry | Paul | 6583 | Bakers Street |
| 10005 | Luke | Flamel | 65464 | Ohio |
+---------+-------+--------+-------+---------------+
2. Orders
Select * from Orders;
+-------+---------+------------+------------------+
| Sl no | Onumber | Odate | Customer_Cnumber |
+-------+---------+------------+------------------+
| 1 | O1005 | 2017-09-08 | 10001 |
| 2 | O1003 | 2019-07-17 | 10003 |
| 3 | O1004 | 2017-08-10 | 10004 |
| 4 | O1001 | 2019-10-15 | 10005 |
| 5 | O1002 | 2019-10-25 | 10005 |
+-------+---------+------------+------------------+
3. Employee

17DATABASE MANAGEMENT SYSTEM
Select * from Employee;
+---------+--------+---------+--------------+-----------+-----------------+-------
+------------+--------------------+---------------+
| Enumber | Fname | Lname | Address | Mobile | Email | Tfn |
Join | Department_Dnumber | Store_Snumber |
+---------+--------+---------+--------------+-----------+-----------------+-------
+------------+--------------------+---------------+
| E001 | Jack | Hugh | Baker Street | 547853474 | gf@.com | 24485 |
2019-07-01 | 100 | 1 |
| E002 | Julie | Kum | Ohio | 5468 | hg@.com | 245 |
2019-07-15 | 101 | 1 |
| E003 | Jame | Maize | California | 53468 | fd@.com | 0124 |
2019-07-24 | 102 | 3 |
| E004 | Hannah | Shaw | Philadelphia | 3248 | dss@.com | 4686 |
2019-07-11 | 103 | 4 |
| E005 | Joana | Mendis | Florida | 32454 | das2.com | 54458 |
2019-07-08 | 100 | 4 |
| E006 | Pika | Paul | Fransisco | 54655 | [e-mail] | 1854 |
2019-07-18 | 101 | 2 |
| E007 | Shane | Efres | Newvada | 5484 | sdfd@jf | 3465 |
2019-07-03 | 102 | 3 |
| E008 | Sasha | Grey | Fransisco | 54655 | [e-mail] | 6565 |
2019-08-18 | 100 | 4 |
| E009 | Paul | Sheeran | California | 58785 | [e-mail] | 1545 |
2019-07-18 | 101 | 2 |
| E010 | Jojo | Martin | Fransisco | 47454 | [e-mail] | 1864 |
2019-11-18 | 102 | 1 |
| E011 | Lex | Luther | Narnia | 455586 | [e-mail] | 5456 |
2019-12-18 | 101 | 3 |
| E012 | Linda | Bettany | New Jerssey | 54687 | [e-mail] | 1857 |
2019-03-18 | 103 | 1 |
+---------+--------+---------+--------------+-----------+-----------------+-------
+------------+--------------------+---------------+
4. Fulltime

18DATABASE MANAGEMENT SYSTEM
Select * from Fulltime;
+--------+-------------+------------------+
| Salary | Designation | Employee_Enumber |
+--------+-------------+------------------+
| 12547 | Manager | E001 |
| 46486 | Manager | E003 |
| 46486 | Supervisor | E004 |
| 4546 | Manager | E005 |
| 76766 | Manager | E006 |
| 68666 | Employee | E007 |
| 76764 | Supervisor | E008 |
| 123354 | Supervisor | E009 |
| 32155 | Supervisor | E010 |
| 876986 | Emplpoyee | E011 |
+--------+-------------+------------------+
5. Casual
Select * from Casual;
+------------+------------------+
| Hourlyrate | Employee_Enumber |
+------------+------------------+
| 22 | E002 |
| 25 | E012 |
+------------+------------------+
6. Department

19DATABASE MANAGEMENT SYSTEM
Select * from Department;
+---------+----------+-------------+---------+
| Dnumber | Dname | Email | Phone |
+---------+----------+-------------+---------+
| 100 | HR | [e-mail] | 6546515 |
| 101 | Accounts | [e-mail] | 3456486 |
| 102 | Finance | [e-mail] | 16546 |
| 103 | Sales | [e-mail] | 456658 |
+---------+----------+-------------+---------+
7. Inventory

20DATABASE MANAGEMENT SYSTEM
Select * from Inventory;
+---------------+------------------+-----------+---------+
| Store_Snumber | Product_prnumber | Available | Ordered |
+---------------+------------------+-----------+---------+
| 1 | 1 | 3 | 6 |
| 1 | 2 | 3 | 2 |
| 1 | 3 | 1 | 1 |
| 1 | 4 | 1 | 2 |
| 2 | 1 | 2 | 2 |
| 2 | 2 | 1 | 2 |
| 2 | 3 | 2 | 6 |
| 2 | 4 | 12 | 10 |
| 3 | 1 | 1 | 12 |
| 3 | 2 | 12 | 12 |
| 3 | 3 | 12 | 11 |
| 3 | 4 | 4 | 45 |
| 4 | 1 | 1 | 12 |
| 4 | 2 | 12 | 45 |
| 4 | 3 | 78 | 45 |
+---------------+------------------+-----------+---------+
8. Payslip

21DATABASE MANAGEMENT SYSTEM
Select * from Payslip;
+------------------+-------+----------+
| Employee_Enumber | Hours | Grosspay |
+------------------+-------+----------+
| E001 | 8 | 12547 |
| E002 | 168 | 3696 |
| E003 | 8 | 46486 |
| E004 | 7 | 46486 |
| E005 | 8 | 4546 |
| E006 | 8 | 76766 |
| E007 | 9 | 68666 |
| E008 | 7 | 76764 |
| E009 | 7 | 123354 |
| E010 | 7 | 32155 |
| E011 | 9 | 876986 |
| E012 | 120 | 3000 |
+------------------+-------+----------+
9. Product

22DATABASE MANAGEMENT SYSTEM
Select * from Product;
+----------+-------------+---------------+-------+--------------------------
+-------+
| Prnumber | Prtype | Name | Brand | Description |
Price |
+----------+-------------+---------------+-------+--------------------------
+-------+
| 1 | Fashion | Gown | Zara | Clothing Silk |
99 |
| 2 | Health | Paracetamol | Zen | Medicine |
12 |
| 3 | Electronics | Fitness watch | Apple | Smartwatch |
1000 |
| 4 | Electronics | Fitness watch | MI | Digital capacitive touch |
999 |
| 5 | Electronics | Fitness watch | Intex | Not available |
100 |
+----------+-------------+---------------+-------+--------------------------
+-------+
10. Store

23DATABASE MANAGEMENT SYSTEM
Select * from Store;
+---------+---------+-------------+--------------------+----------+------------
+--------------+--------------+----------+
| Snumber | Sname | PhoneNumber | Email | Fax | Street_no |
Suburb | State | Postcode |
+---------+---------+-------------+--------------------+----------+------------
+--------------+--------------+----------+
| 1 | Stevens | 452542151 | [e-mail] | 67656786 | 1/7 GeorGe |
Auckland | Upstate | 7451545 |
| 2 | Revisit | 584797 | [e-mail] | 456786 | 21 |
Christchurch | Christchurch | 354 |
| 3 | Pro | 156456 | daf@fhd | 423654 | 10 George |
Newland | Ohio | 54754 |
| 4 | Elitmus | 5379 | df@ge | 32546 | 12 |
Admence | Latina | 543 |
| 5 | Jeanies | 543645 | [e-mail] | 6545 | 1/78 |
Lowstate | Ohio | 6546 |
+---------+---------+-------------+--------------------+----------+------------
+--------------+--------------+----------+
11. Suppliers
Select * from Suppliers;
+------+----------+--------+
| Spid | Name | Phone |
+------+----------+--------+
| 1 | John | 654547 |
| 2 | Mike | 654541 |
| 3 | Scarlett | 654542 |
| 4 | Winda | 576723 |
| 5 | Mindy | 576723 |
+------+----------+--------+
12. Supplier_has_product

24DATABASE MANAGEMENT SYSTEM
Select * from Suppliers_has_product;
+----------------+------------------+
| Suppliers_spid | Product_prnumber |
+----------------+------------------+
| 1 | 1 |
| 2 | 1 |
| 4 | 1 |
| 1 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 1 | 3 |
| 2 | 3 |
| 5 | 3 |
| 1 | 4 |
| 2 | 4 |
| 5 | 4 |
+----------------+------------------+
13. Orders_has_product

25DATABASE MANAGEMENT SYSTEM
Select * from Orders_has_product;
+--------------+------------------+----------+
| Orders_sl no | Product_prnumber | Quantity |
+--------------+------------------+----------+
| 1 | 1 | 1 |
| 1 | 2 | 10 |
| 1 | 3 | 3 |
| 2 | 4 | 10 |
| 3 | 3 | 2 |
| 3 | 4 | 2 |
| 4 | 5 | 1 |
| 5 | 5 | 1 |
+--------------+------------------+----------+
Referral Integrity Constraints
Figure 4: Referral Integrity Constraints for orders table
Source: created by author

26DATABASE MANAGEMENT SYSTEM
Queries
Question 1:
SQL: select Cnumber, concat(fname,' ', lname) as Name, Phone, Address
from patron order by Cnumber DESC;
Output: +---------+-------------+-------+---------------+
| Cnumber | Name | Phone | Address |
+---------+-------------+-------+---------------+
| 10005 | Luke Flamel | 65464 | Ohio |
| 10004 | Merry Paul | 6583 | bakers street |
| 10003 | Jame Wright | 64876 | uptown |
| 10002 | Julie Kum | 64876 | new york |
| 10001 | Mike Hugh | 64876 | Ohio |
+---------+-------------+-------+---------------+
Question 2:
SQL: SELECT * FROM `product` WHERE price<100;
Output: +----------+---------+-------------+-------+---------------+-------+
| Prnumber | Prtype | Name | Brand | Description | Price |
+----------+---------+-------------+-------+---------------+-------+
| 1 | Fashion | Gown | Zara | Clothing Silk | 99 |
| 2 | Health | Paracetamol | Zen | Medicine | 12 |
+----------+---------+-------------+-------+---------------+-------+
2 rows in set (0.00 sec)
Question 3:

27DATABASE MANAGEMENT SYSTEM
SQL: select Prnumber, name as Product_Title, Quantity, price as
Unit_Price,(price*quantity) as Total_Amount from product p
inner join
orders_has_product os ON p.prnumber=os.product_prnumber
inner join
orders o on os.`orders_sl no`=o.`sl no` where Onumber='O1005';
Output: +----------+---------------+----------+------------+--------------+
| Prnumber | Product_Title | Quantity | Unit_Price | Total_Amount |
+----------+---------------+----------+------------+--------------+
| 1 | Gown | 1 | 99 | 99 |
| 2 | Paracetamol | 10 | 12 | 120 |
| 3 | Fitness watch | 3 | 1000 | 3000 |
+----------+---------------+----------+------------+--------------+
3 rows in set (0.01 sec)
Question 4:
SQL: select Sname,Fax,concat(street_no,' ',suburb, ' ', state) as
Full_address from store where BINARY street_no like '%GeorGe%';
Output: +---------+----------+-----------------------------+
| Sname | Fax | Full_address |
+---------+----------+-----------------------------+
| Stevens | 67656786 | 1/7 GeorGe Auckland Upstate |
+---------+----------+-----------------------------+
1 row in set (0.00 sec)
Question 5:

28DATABASE MANAGEMENT SYSTEM
SQL: select o.Onumber from orders_has_product os, orders o where
os.`orders_sl no`=o.`sl no` HAVING COUNT(product_prnumber)>1;
Output: +---------+
| Onumber |
+---------+
| O1005 |
+---------+
1 row in set (0.00 sec)
Question 6:
SQL: select c.Cnumber, c.Address, concat(c.fname,' ', c.lname) as Name
from patron c
left outer join orders o
on c.Cnumber=o.customer_Cnumber where o.customer_Cnumber is null ;
Output: +---------+----------+-----------+
| Cnumber | Address | Name |
+---------+----------+-----------+
| 10002 | New York | Julie Kum |
+---------+----------+-----------+
1 row in set (0.00 sec)
Question 7:

29DATABASE MANAGEMENT SYSTEM
SQL: select e.Enumber,e.Fname, e.Lname, d.Dname, s.Sname from employee
e, store s, department d where e.store_Snumber=s.Snumber and
d.Dnumber=e.department_Dnumber;
Output: +---------+--------+---------+----------+---------+
| Enumber | Fname | Lname | Dname | Sname |
+---------+--------+---------+----------+---------+
| E001 | Jack | Hugh | HR | Stevens |
| E002 | Julie | Kum | Accounts | Stevens |
| E010 | Jojo | Martin | Finance | Stevens |
| E012 | Linda | Bettany | Sales | Stevens |
| E006 | Pika | Paul | Accounts | Revisit |
| E009 | Paul | Sheeran | Accounts | Revisit |
| E011 | Lex | Luther | Accounts | Pro |
| E003 | Jame | Maize | Finance | Pro |
| E007 | Shane | Efres | Finance | Pro |
| E005 | Joana | Mendis | HR | Elitmus |
| E008 | Sasha | Grey | HR | Elitmus |
| E004 | Hannah | Shaw | Sales | Elitmus |
+---------+--------+---------+----------+---------+
12 rows in set (0.00 sec)
Question 8:

30DATABASE MANAGEMENT SYSTEM
SQL: SELECT s.Sname, count(e.Enumber) as No_of_employees, d.Dname from
employee e,store s, department d where e.store_Snumber=s.Snumber
and
d.Dnumber=e.department_dnumber GROUP by s.Sname;
Output: +---------+-----------------+----------+
| Sname | No_of_employees | Dname |
+---------+-----------------+----------+
| Elitmus | 3 | HR |
| Pro | 3 | Accounts |
| Revisit | 2 | Accounts |
| Stevens | 4 | HR |
+---------+-----------------+----------+
4 rows in set (0.01 sec)
Question 9:
SQL: select Onumber, Odate from orders where Odate>= '2017-06-30' and
Odate <='2018-07-01';
Output: +---------+------------+
| Onumber | Odate |
+---------+------------+
| O1005 | 2017-09-08 |
| O1004 | 2017-08-10 |
+---------+------------+
2 rows in set (0.00 sec)
Question 10:

31DATABASE MANAGEMENT SYSTEM
SQL: select customer_Cnumber, count(Onumber) as Orders from orders GROUP
by customer_Cnumber;
Output: +------------------+--------+
| customer_Cnumber | Orders |
+------------------+--------+
| 10001 | 1 |
| 10003 | 1 |
| 10004 | 1 |
| 10005 | 2 |
+------------------+--------+
4 rows in set (0.00 sec)
Question 11:
SQL: select count(os.quantity) as Number, o.Onumber, p.Name,o.Odate from
orders o, orders_has_product os, product p where
p.prnumber=os.product_prnumber and os.`orders_sl no`=o.`sl no`
GROUP by Onumber DESC;
Output: +--------+---------+---------------+------------+
| Number | Onumber | Name | Odate |
+--------+---------+---------------+------------+
| 3 | O1005 | Gown | 2017-09-08 |
| 2 | O1004 | Fitness watch | 2017-08-10 |
| 1 | O1003 | Fitness watch | 2019-07-17 |
| 1 | O1002 | Fitness watch | 2019-10-25 |
| 1 | O1001 | Fitness watch | 2019-10-15 |
+--------+---------+---------------+------------+
5 rows in set, 1 warning (0.00 sec)