Relational schema and MySQL database
1) Relational Schema: -
Relation |
Attribute |
Data type |
length |
Key constraints |
Referencing table (column) |
Null/ not null |
location |
locationId |
INT |
- |
PK |
- |
NOT NULL, UNIQUE |
streetNo |
INT |
- |
- |
- |
NOT NULL | |
streetName |
VARCHAR |
20 |
- |
- |
NOT NULL | |
suburb |
VARCHAR |
20 |
- |
- |
NOT NULL | |
stateName |
VARCHAR |
20 |
- |
- |
NOT NULL | |
postCode |
INT |
- |
- |
- |
NOT NULL | |
stores |
storeNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
storeName |
VARCHAR |
25 |
- |
- |
NOT NULL | |
mobile |
VARCHAR |
12 |
- |
- |
NOT NULL | |
|
VARCHAR |
30 |
- |
- |
NOT NULL | |
faxNo |
VARCHAR |
10 |
- |
- |
NULL | |
locationId |
INT |
- |
FK |
location (locationNo) |
NOT NULL | |
departments |
departmentNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
title |
VARCHAR |
20 |
- |
- |
NOT NULL | |
mobile |
VARCHAR |
12 |
- |
- |
NOT NULL | |
|
VARCHAR |
30 |
- |
- |
NOT NULL | |
storeDepartment |
storeNo |
VARCHAR |
10 |
PK, FK |
store(storeNo) |
NOT NULL |
departmentNo |
VARCHAR |
10 |
PK, FK |
department (departmentNo) |
NOT NULL | |
employee |
employeeNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
storeNo |
VARCHAR |
10 |
FK |
storeDepartment (storeNo) |
NOT NULL | |
departmentNo |
VARCHAR |
10 |
FK |
storeDepartment (departmentNo) |
NOT NULL | |
firstName |
VARCHAR |
20 |
- |
- |
NOT NULL | |
lastName |
VARCHAR |
20 |
- |
- |
NOT NULL | |
address |
VARCHAR |
50 |
- |
- |
NOT NULL | |
mobile |
VARCHAR |
12 |
- |
- |
NOT NULL | |
|
VARCHAR |
30 |
- |
- |
NOT NULL | |
tfn |
VARCHAR |
10 |
- |
- |
NULL | |
joinDate |
DATE |
- |
- |
- |
NOT NULL | |
workRole |
VARCHAR |
10 |
- |
- |
NOT NULL | |
fullTime |
employeeNo |
VARCHAR |
10 |
PK, FK |
employee (employeeNo) |
NOT NULL, UNIQUE |
annualSalary |
DOUBLE |
(10,2) |
- |
- |
NOT NULL | |
casual |
employeeNo |
VARCHAR |
10 |
PK, FK |
employee (employeeNo) |
NOT NULL |
hourlyRate |
DOUBLE |
(10, 2) |
- |
- |
NOT NULL | |
payslips |
payslipNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
totalHours |
DOUBLE |
(10, 2) |
- |
- |
NOT NULL | |
grossPay |
DOUBLE |
(10, 2) |
- |
- |
NOT NULL | |
employeeNo |
VARCHAR |
10 |
FK |
employee (employeeNo) |
NOT NULL | |
product |
productNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
productName |
VARCHAR |
20 |
- |
- |
NOT NULL | |
productType |
VARCHAR |
20 |
- |
- |
NOT NULL | |
brand |
VARCHAR |
30 |
- |
- |
NOT NULL | |
price |
DOUBLE |
(10, 2) |
- |
- |
NOT NULL | |
description |
VARCHAR |
200 |
- |
- |
NULL | |
suppliers |
supplierNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
supplierName |
VARCHAR |
30 |
- |
- |
NOT NULL | |
address |
VARCHAR |
50 |
- |
- |
NOT NULL | |
mobile |
VARCHAR |
12 |
- |
- |
NOT NULL | |
|
VARCHAR |
30 |
- |
- |
NOT NULL | |
inventory |
productNo |
VARCHAR |
10 |
PK, FK |
product(productNo) |
NOT NULL |
stotreNo |
VARCHAR |
10 |
PK, FK |
store(storeNo) |
NOT NULL | |
quantity |
DOUBLE |
(10, 2) |
- |
- |
NOT NULL | |
supply |
supplyNo |
INT |
- |
PK |
- |
NOT NULL, UNIQUE |
storeNo |
VARCHAR |
10 |
FK |
inventory (storeNo) |
NOT NULL | |
productNo |
VARCHAR |
10 |
FK |
inventory (productNo) |
NOT NULL | |
supplierNo |
VARCHAR |
10 |
FK |
supplier (supplierNo) |
NOT NULL | |
quantity |
INT |
- |
- |
- |
NOT NULL | |
supplyDate |
DATE |
- |
- |
- |
NOT NULL | |
customers |
customerNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
firstName |
VARCHAR |
15 |
- |
- |
NOT NULL | |
lastName |
VARCHAR |
15 |
- |
- |
NOT NULL | |
mobile |
VARCHAR |
12 |
- |
- |
NOT NULL | |
address |
VARCHAR |
50 |
- |
- |
NOT NULL | |
orders |
orderNo |
VARCHAR |
10 |
PK |
- |
NOT NULL, UNIQUE |
customerNo |
VARCHAR |
10 |
FK |
customer (customerNo) |
NOT NULL | |
orderDate |
DATE |
- |
- |
- |
NOT NULL | |
orderItems |
orderNo |
VARCHAR |
10 |
PK, FK |
orders(orderNo) |
NOT NULL |
productNo |
VARCHAR |
10 |
PK, FK |
product (productNo) |
NOT NULL | |
quantity |
INT |
- |
- |
- |
NOT NULL |
2) MYSQL database: -
· Database schema: -
Figure 2: database schema
· referential integrity constraints: -
Figure 3: casual
Figure 4: customers
Figure 5: departments
Figure 6:employee
Figure 7:fullTime
Figure 8: inventory
Figure 9: location
Figure 10: orderItems
Figure 11: orders
Figure 12: paySlips
Figure 13: product
Figure 14: supply
Figure 15: stores
Figure 16: storeDepartment
Figure 17: supplier
· structure of each table: -
Describe orderItems; |
+-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | orderNo | varchar(10) | NO | PRI | NULL | | | productNo | varchar(10) | NO | PRI | NULL | | | quantity | int(11) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) |
Describe orders; |
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | orderNo | varchar(10) | NO | PRI | NULL | | | orderDate | date | NO | | NULL | | | customerNo | varchar(10) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
Describe customers; |
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | customerNo | varchar(10) | NO | PRI | NULL | | | firstName | varchar(15) | NO | | NULL | | | lastName | varchar(15) | NO | | NULL | | | mobile | varchar(12) | NO | | NULL | | | address | varchar(50) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) |
Describe supply; |
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | supplyNo | int(11) | NO | PRI | NULL | | | storeNo | varchar(10) | NO | | NULL | | | productNo | varchar(10) | NO | MUL | NULL | | | supplierNo | varchar(10) | NO | MUL | NULL | | | quantity | int(11) | NO | | NULL | | | supplyDate | date | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) |
Describe inventory; |
+-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | productNo | varchar(10) | NO | PRI | NULL | | | storeNo | varchar(10) | NO | PRI | NULL | | | quantity | int(11) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
Describe suppliers; |
+--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | supplierNo | varchar(10) | NO | PRI | NULL | | | supplierName | varchar(30) | NO | | NULL | | | address | varchar(50) | NO | | NULL | | | mobile | varchar(12) | NO | | NULL | | | email | varchar(30) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) |
Describe product; |
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | productNo | varchar(10) | NO | PRI | NULL | | | productName | varchar(20) | NO | | NULL | | | productType | varchar(20) | NO | | NULL | | | brand | varchar(30) | NO | | NULL | | | price | double(10,2) | NO | | NULL | | | description | varchar(200) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) |
Describe paySlips; |
+------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | payslipNo | varchar(10) | NO | PRI | NULL | | | totalHours | double(10,2) | NO | | NULL | | | grossPay | double(10,2) | NO | | NULL | | | employeeNo | varchar(10) | NO | MUL | NULL | | +------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) |
Describe casual; |
+------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | employeeNo | varchar(10) | NO | PRI | NULL | | | hourlyRate | double(10,2) | NO | | NULL | | +------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
Describe fullTime; |
+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | employeeNo | varchar(10) | NO | PRI | NULL | | | annualSalary | double(10,2) | NO | | NULL | | +--------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
Describe employee; |
+--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | employeeNo | varchar(10) | NO | PRI | NULL | | | storeNo | varchar(10) | NO | MUL | NULL | | | departmentNo | varchar(10) | NO | | NULL | | | firstName | varchar(20) | NO | | NULL | | | lastName | varchar(20) | NO | | NULL | | | address | varchar(50) | NO | | NULL | | | mobile | varchar(12) | NO | | NULL | | | email | varchar(30) | NO | | NULL | | | tfn | varchar(10) | YES | | NULL | | | joinDate | date | NO | | NULL | | | workRole | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 11 rows in set (0.00 sec) |
Describe storeDepartment; |
+--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | storeNo | varchar(10) | NO | PRI | NULL | | | departmentNo | varchar(10) | NO | PRI | NULL | | +--------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
Describe departments; |
+--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | departmentNo | varchar(10) | NO | PRI | NULL | | | title | varchar(20) | NO | | NULL | | | mobile | varchar(12) | NO | | NULL | | | email | varchar(30) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) |
Describe stores; |
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | storeNo | varchar(10) | NO | PRI | NULL | | | storesName | varchar(25) | NO | | NULL | | | mobile | varchar(12) | NO | | NULL | | | email | varchar(30) | NO | | NULL | | | faxNo | varchar(10) | YES | | NULL | | | locationId | int(11) | NO | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) |
Describe location; |
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | locationId | int(11) | NO | PRI | NULL | | | streetNo | int(11) | NO | | NULL | | | streetName | varchar(20) | NO | | NULL | | | suburb | varchar(20) | NO | | NULL | | | stateName | varchar(20) | NO | | NULL | | | postCode | int(11) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) |
3) Insert data into tables: -
SELECT *FROM orderItems; |
+---------+-----------+----------+ | orderNo | productNo | quantity | +---------+-----------+----------+ | 1005 | 1 | 3 | | 1005 | 2 | 4 | | 1005 | 3 | 2 | | 2 | 1 | 4 | | 2 | 2 | 4 | | 2 | 3 | 2 | | 5 | 1 | 3 | | 5 | 2 | 1 | | 5 | 3 | 3 | +---------+-----------+----------+ 9 rows in set (0.00 sec) |
SELECT *FROM orders; |
+---------+------------+------------+ | orderNo | orderDate | customerNo | +---------+------------+------------+ | 1005 | 2017-07-03 | 1 | | 2 | 2017-08-04 | 1 | | 5 | 2017-09-05 | 1 | | 6 | 2017-10-06 | 2 | +---------+------------+------------+ 4 rows in set (0.00 sec) |
SELECT *FROM customers; |
+------------+-----------+----------+------------+---------------+ | customerNo | firstName | lastName | mobile | address | +------------+-----------+----------+------------+---------------+ | 1 | nji | Caan | 6787634521 | nji@gmail.com | | 2 | mni | Caan | 6787634522 | mni@gmail.com | | 3 | fgi | Caan | 6787634523 | fgi@gmail.com | +------------+-----------+----------+------------+---------------+ 3 rows in set (0.00 sec) |
SELECT *FROM supply; |
+----------+---------+-----------+------------+----------+------------+ | supplyNo | storeNo | productNo | supplierNo | quantity | supplyDate | +----------+---------+-----------+------------+----------+------------+ | 1 | 1 | 1 | 1 | 3 | 2013-01-01 | | 2 | 1 | 2 | 2 | 2 | 2013-01-02 | | 3 | 2 | 3 | 3 | 4 | 2013-01-03 | | 4 | 2 | 1 | 1 | 1 | 2013-01-04 | +----------+---------+-----------+------------+----------+------------+ 4 rows in set (0.03 sec) |
SELECT *FROM inventory; |
+-----------+---------+----------+ | productNo | storeNo | quantity | +-----------+---------+----------+ | 1 | 1 | 10 | | 1 | 2 | 11 | | 1 | 3 | 10 | | 2 | 1 | 11 | | 2 | 3 | 15 | | 3 | 1 | 13 | | 3 | 2 | 16 | +-----------+---------+----------+ 7 rows in set (0.04 sec) |
SELECT *FROM suppliers; |
+------------+--------------+---------+------------+----------------+ | supplierNo | supplierName | address | mobile | email | +------------+--------------+---------+------------+----------------+ | 1 | bhi | US | 9890987876 | bhi@gmail.com | | 2 | nki | US | 9890987877 | nki@gmail.com | | 3 | miko | US | 9890987816 | miko@gmail.com | +------------+--------------+---------+------------+----------------+ 3 rows in set (0.10 sec) |
SELECT *FROM product; |
+-----------+-------------+-------------+-------+--------+-------------+ | productNo | productName | productType | brand | price | description | +-----------+-------------+-------------+-------+--------+-------------+ | 1 | nini | fashion | dfg | 456.00 | NULL | | 2 | jkmo | fashion | dfc | 567.00 | NULL | | 3 | nmkl | fashion | ertg | 56.00 | NULL | | 4 | bhnj | fashion | fgv | 76.00 | NULL | | 5 | yuhi | fashion | rfghy | 456.00 | NULL | +-----------+-------------+-------------+-------+--------+-------------+ 5 rows in set (0.12 sec) |
SELECT *FROM paySlips; |
+-----------+------------+----------+------------+ | payslipNo | totalHours | grossPay | employeeNo | +-----------+------------+----------+------------+ | 1 | 45.90 | 744.09 | 1 | | 2 | 45.87 | 345.67 | 2 | | 3 | 87.09 | 567.09 | 3 | +-----------+------------+----------+------------+ 3 rows in set (0.04 sec) |
SELECT *FROM casual; |
+------------+------------+ | employeeNo | hourlyRate | +------------+------------+ | 10 | 300.00 | | 11 | 500.00 | | 12 | 200.00 | | 7 | 600.00 | | 8 | 500.00 | | 9 | 200.00 | +------------+------------+ 6 rows in set (0.02 sec) |
SELECT *FROM fullTime; |
+------------+--------------+ | employeeNo | annualSalary | +------------+--------------+ | 1 | 56789678.00 | | 2 | 56789876.00 | | 3 | 56789987.00 | | 4 | 78987688.00 | | 5 | 56784567.00 | | 6 | 34567569.00 | +------------+--------------+ 6 rows in set (0.12 sec) |
SELECT *FROM employee; |
+------------+---------+--------------+-----------+----------+---------+------------+-----------------+---------+------------+------------+ | employeeNo | storeNo | departmentNo | firstName | lastName | address | mobile | email | tfn | joinDate | workRole | +------------+---------+--------------+-----------+----------+---------+------------+-----------------+---------+------------+------------+ | 1 | 1 | 1 | dfio | Caan | US | 1234323430 | dfio@gmail.com | fd45 | 2015-01-01 | supervisor | | 10 | 3 | 2 | ert | Caan | US | 1234323439 | ert@gmail.com | t566 | 2015-01-10 | employee | | 11 | 3 | 3 | rty | Caan | US | 1234323134 | rty@gmail.com | g56 | 2015-01-11 | employee | | 12 | 3 | 4 | rtyu | Caan | US | 1234322434 | rtyu@gmail.com | 6g5 | 2015-01-12 | employee | | 2 | 1 | 2 | huio | Caan | US | 1234323431 | huio@gmail.com | dr56 | 2015-01-02 | supervisor | | 3 | 1 | 3 | rtyu | Caan | US | 1234323432 | rtyu@gmail.com | gh67 | 2015-01-03 | supervisor | | 4 | 1 | 4 | fghj | Caan | US | 1234323433 | fgghj@gmail.com | gtfr456 | 2015-01-04 | supervisor | | 5 | 2 | 1 | dfg | Caan | US | 1234323434 | dfg@gmail.com | dgt64 | 2015-01-05 | manager | | 6 | 2 | 2 | sdf | Caan | US | 1234323435 | sdf5@gmail.com | grr4556 | 2015-01-06 | manager | | 7 | 2 | 3 | asd | Caan | US | 1234323436 | asd@gmail.com | g355 | 2015-01-07 | manager | | 8 | 2 | 4 | qwer | Caan | US | 1234323437 | qwer@gmail.com | gr434 | 2015-01-08 | manager | | 9 | 3 | 1 | wer | Caan | US | 1234323438 | wer@gmail.com | tr45 | 2015-01-09 | employee | +------------+---------+--------------+-----------+----------+---------+------------+-----------------+---------+------------+------------+ 12 rows in set (0.01 sec) |
SELECT *FROM storeDepartment; |
+---------+--------------+ | storeNo | departmentNo | +---------+--------------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 1 | 2 | | 2 | 2 | | 3 | 2 | | 1 | 3 | | 2 | 3 | | 3 | 3 | | 1 | 4 | | 2 | 4 | | 3 | 4 | +---------+--------------+ 12 rows in set (0.04 sec) |
SELECT *FROM departments; |
+--------------+----------+------------+--------------------+ | departmentNo | title | mobile | email | +--------------+----------+------------+--------------------+ | 1 | HR | 5434543453 | HR789hh@gmail.com | | 2 | accounts | 5434543455 | ACCtgw@gmail.com | | 3 | finance | 5434543456 | FM234gju@gmail.com | | 4 | supply | 5434543457 | SM2rte@gmail.com | +--------------+----------+------------+--------------------+ 4 rows in set (0.13 sec) |
SELECT *FROM stores; |
+---------+-------------+------------+------------------+-------+------------+ | storeNo | storesName | mobile | email | faxNo | locationId | +---------+-------------+------------+------------------+-------+------------+ | 1 | hiyna koko | 1234567890 | hiyna@gmail.com | er45 | 1 | | 2 | jihono jiji | 1234567891 | jihono@gmail.com | fgv4 | 2 | | 3 | kio hinop | 1234567892 | kio@gmail.com | gfv56 | 3 | +---------+-------------+------------+------------------+-------+------------+ 3 rows in set (0.11 sec) |
SELECT *FROM location; |
+------------+----------+---------------+--------+-----------+----------+ | locationId | streetNo | streetName | suburb | stateName | postCode | +------------+----------+---------------+--------+-----------+----------+ | 1 | 6759 | MKLAS | HJNK | LM | 1234 | | 2 | 7898 | George | JKML | NM | 1232 | | 3 | 6709 | George IJKIPO | KML | ML | 6787 | +------------+----------+---------------+--------+-----------+----------+ 3 rows in set (0.03 sec) |
Part D: SQL: -
Question 1:
SQL: |
SELECT CONCAT(firstName,' ',lastName) AS "customers full name", mobile, address FROM customers ORDER BY customerNo; |
Output: |
+---------------------+------------+---------------+ | customers full name | mobile | address | +---------------------+------------+---------------+ | nji Caan | 6787634521 | nji@gmail.com | | mni Caan | 6787634522 | mni@gmail.com | | fgi Caan | 6787634523 | fgi@gmail.com | +---------------------+------------+---------------+ 3 rows in set (0.00 sec) |
Question 2:
SQL: |
SELECT *FROM product WHERE price<100; |
Output: |
+-----------+-------------+-------------+-------+-------+-------------+ | productNo | productName | productType | brand | price | description | +-----------+-------------+-------------+-------+-------+-------------+ | 3 | nmkl | fashion | ertg | 56.00 | NULL | | 4 | bhnj | fashion | fgv | 76.00 | NULL | +-----------+-------------+-------------+-------+-------+-------------+ 2 rows in set (0.00 sec) |
Question 3:
SQL: |
SELECT product.productNo, product.brand as title, orderItems.quantity, product.price, (product.price*orderItems.quantity) AS "total amount" FROM product INNER JOIN orderItems ON product.productNo=orderItems.productNo INNER JOIN orders ON orders.orderNo=orderItems.orderNo WHERE orders.orderNo='1005'; |
Output: |
+-----------+-------+----------+--------+--------------+ | productNo | title | quantity | price | total amount | +-----------+-------+----------+--------+--------------+ | 1 | dfg | 3 | 456.00 | 1368.00 | | 2 | dfc | 4 | 567.00 | 2268.00 | | 3 | ertg | 2 | 56.00 | 112.00 | +-----------+-------+----------+--------+--------------+ 3 rows in set (0.00 sec) |
Question 4:
SQL: |
SELECT stores.storesName, CONCAT(location.streetNo,' ',location.streetName,' ',location.suburb, ' ',location.statename,' ',location.postcode) as "Full address", stores.faxNo FROM stores INNER JOIN location ON stores.locationId=location.locationId WHERE LOWER(location.streetName) LIKE'%george%'; |
Output: |
+-------------+--------------------------------+-------+ | storesName | Full address | faxNo | +-------------+--------------------------------+-------+ | jihono jiji | 7898 George JKML NM 1232 | fgv4 | | kio hinop | 6709 George IJKIPO KML ML 6787 | gfv56 | +-------------+--------------------------------+-------+ 2 rows in set (0.00 sec) |
Question 5:
SQL: |
SELECT *FROM orders WHERE orderNo IN (SELECT orderNo FROM (SELECT orderNo, COUNT(productNo) FROM orderItems GROUP BY orderNo HAVING COUNT(productNo)>1) A); |
Output: |
+---------+------------+------------+ | orderNo | orderDate | customerNo | +---------+------------+------------+ | 1005 | 2017-07-03 | 1 | | 2 | 2017-08-04 | 1 | | 5 | 2017-09-05 | 1 | +---------+------------+------------+ 3 rows in set (0.00 sec) |
Question 6:
SQL: |
SELECT customerNo, CONCAT(firstName,' ',lastName) AS customersName, address FROM customers WHERE customerNo NOT IN (SELECT customerNo FROM orders); |
Output: |
+------------+---------------+---------------+ | customerNo | customersName | address | +------------+---------------+---------------+ | 3 | fgi Caan | fgi@gmail.com | +------------+---------------+---------------+ 1 row in set (0.03 sec) |
Question 7:
SQL: |
SELECT employee.employeeNo, employee.firstName,employee.lastName, departments.title as deptName, stores.storesName FROM employee, departments, stores, storedepartment WHERE employee.storeNo=storedepartment.storeNo AND employee.departmentNo=storedepartment.departmentNo AND stores.storeNo=storedepartment.storeNo AND departments.departmentNo=storedepartment.departmentNo; |
Output: |
+------------+-----------+----------+----------+-------------+ | employeeNo | firstName | lastName | deptName | storesName | +------------+-----------+----------+----------+-------------+ | 1 | dfio | Caan | HR | hiyna koko | | 10 | ert | Caan | accounts | kio hinop | | 11 | rty | Caan | finance | kio hinop | | 12 | rtyu | Caan | supply | kio hinop | | 2 | huio | Caan | accounts | hiyna koko | | 3 | rtyu | Caan | finance | hiyna koko | | 4 | fghj | Caan | supply | hiyna koko | | 5 | dfg | Caan | HR | jihono jiji | | 6 | sdf | Caan | accounts | jihono jiji | | 7 | asd | Caan | finance | jihono jiji | | 8 | qwer | Caan | supply | jihono jiji | | 9 | wer | Caan | HR | kio hinop | +------------+-----------+----------+----------+-------------+ 12 rows in set (0.09 sec) |
Question 8:
SQL: |
SELECT stores.storesName, COUNT(employee.employeeNo) AS "number of employees" FROM employee, departments, stores, storedepartment WHERE employee.storeNo=storedepartment.storeNo AND employee.departmentNo=storedepartment.departmentNo AND stores.storeNo=storedepartment.storeNo AND departments.departmentNo=storedepartment.departmentNo AND departments.title LIKE '%account%' GROUP BY stores.storesName; |
Output: |
+-------------+---------------------+ | storesName | number of employees | +-------------+---------------------+ | hiyna koko | 1 | | jihono jiji | 1 | | kio hinop | 1 | +-------------+---------------------+ 3 rows in set (0.03 sec) |
Question 9:
SQL: |
SELECT *FROM orders WHERE orderDate BETWEEN '2017-06-03' AND '2018-07-01'; |
Output: |
+---------+------------+------------+ | orderNo | orderDate | customerNo | +---------+------------+------------+ | 1005 | 2017-07-03 | 1 | | 2 | 2017-08-04 | 1 | | 5 | 2017-09-05 | 1 | | 6 | 2017-10-06 | 2 | +---------+------------+------------+ 4 rows in set (0.00 sec) |
Question 10:
SQL: |
SELECT customers.customerNo, COUNT(orders.orderNo) AS "total orders" FROM customers LEFT JOIN orders ON customers.customerNo=orders.customerNo GROUP BY customers.customerNo; |
Output: |
+------------+--------------+ | customerNo | total orders | +------------+--------------+ | 1 | 3 | | 2 | 1 | | 3 | 0 | +------------+--------------+ 3 rows in set (0.00 sec) |
Question 11:
SQL: |
SELECT orders.orderNo, orders.orderDate, SUM(quantity) AS "total number of products" FROM orders, orderItems WHERE orders.orderNo=orderItems.orderNo GROUP BY orders.orderNo, orders.orderDate ORDER BY orders.orderNo DESC; |
Output: |
+---------+------------+--------------------------+ | orderNo | orderDate | total number of products | +---------+------------+--------------------------+ | 5 | 2017-09-05 | 7 | | 2 | 2017-08-04 | 10 | | 1005 | 2017-07-03 | 9 | +---------+------------+--------------------------+ 3 rows in set (0.00 sec) |
Part E: Personal Report –
While working on this assignment, we learnt a lot of new things. The first phase of the assignment was requirement gathering and analysis. We went through all the requirements provided to us in the requirements file and then analysed these requirements. After analysing the requirements, we prepared our Entity Relationship Diagram (ERD). We had made sure that the ERD prepared by us is covering all the requirements and it is prepared as per the requirements file only.
While working on the assignment, we faced several issues. We were stuck while preparing the ERD but after spending some time on the analysis part, we were finally able to complete the ERD. Also, we faced issues while identifying the primary keys in some of the tables. So, we have added surrogate keys in some of the tables as per our understanding. We also faced some issues while working on the queries and we couldn’t get them right in the first go. But, after spending some time and going through various online resources, we were finally able to get the correct result for all the queries.
Our database design is fully normalised and extendable. Any new requirements which may come in the future can easily be integrated into our database.
MYSQL Tutorials
- Create MySQL Database
- Create Table in MySQL
- Insert data to MySQL
- Mysql Queries
- Update data in MYSQL
- Delete data in MySQL
- Default Database
- Primary Key
- Foreign Keys
- Views
- Export MySQL Database
- Import data into Mysql
- Create Website with MySQL
MYSQL Sample Assignments
- COIT20247 Database Design and Development
- Relational schema and MySQL database
- Mysql Project
- ITICT107A Introduction to Databases
- MySql Assignment Question
MYSQL Sample Solutions
- Solution - COIT20247 Database Design
- Solution - Relational schema and MySQL
- Solution - Mysql Project
Testimonials
Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More