SQL Queries

The assignment content consists of SQL queries and database operations related to employee, product, and customer information. The queries include selecting distinct products by price, updating employee salaries, retrieving recent pay slips, and more. Additionally, the content includes inserting data into tables such as address, customer, brand, product, inventory, and order line, as well as updating a product's brand ID. The output tables are also provided for each query.

1804ICT – DATA MANAGEMENT
7003ICT – DATABASE DESIGN
School of Information & Communication Technology
Trimester 2, 2018
Assignment Part 2:
Designing a Database for Commonwealth
Transport Services
ASSIGNMENT TITLE: _________________________________________________________
Student ID _________________Student Name ____________________________________________
Course Code ______________Course Name ____________________________________________
Date Submitted ____________Lecturer’s Name __________________________________________
Tutor’s Name ________________________________________________________________
Marks obtained: ________. [For marker to fill up.]

Acknowledgements:
I would like to acknowledge all those who facilitated this work, especially the fellow students
who helped me throughout the project. I would also like to acknowledge the college facility
for providing me continuous guidance to ease my work.

Table of Contents
List of Illustrations.................................................................................................................................4
Reports on SQL Queries in Task 3..........................................................................................................7
Reports on SQL Queries in Task 4........................................................................................................10
Bibliography.........................................................................................................................................12

List of Illustrations
ADDRESS:
ADDRESSTYPE:
CUSTOMER:
JOBTYPE:
DEPARTMENT:
STORE:

STOREDEPARTMENT:
BRAND:
EMPLOYEE:
PAYSLIP:
PRODUCT:
INVENTORY:
CUSTOMERORDER:

ORDERLINE:

Reports on SQL Queries in Task 3
Query 1:
SELECT DISTINCT CONCAT(a.Emp_FName,' ',a.Emp_LName) AS NAME, CONCAT(b.Address_St,'
',b.Address_City,' ',b.Address_State,' ',b.Address_Postcode) AS ADDRESS
FROM EMPLOYEE a INNER JOIN ADDRESS b
ON a.Address_ID_Postal = b.Address_ID;
Output table:
Query 2:
SELECT DISTINCT CONCAT(a.Cust_FName,' ',a.Cust_LName) AS NAME, b.CustOrd_Date AS
Date_of_Order
FROM CUSTOMER a INNER JOIN CUSTOMERORDER b
ON a.Cust_Number = b.Cust_Number
ORDER BY b.CustOrd_Date DESC LIMIT 1;
Output table:
Query 3:
SELECT DISTINCT a.Str_Name AS NAME,CONCAT(b.Address_St,' ',b.Address_City,' ',b.Address_State,'
',b.Address_Postcode) AS ADDRESS
FROM STORE a INNER JOIN ADDRESS b
ON a.Address_ID = b.Address_ID
ORDER BY a.Str_Name;
Output table:
Query 4:
SELECT DISTINCT a.Cust_Number AS Customer_Number,CONCAT(a.Cust_FName,' ',a.Cust_LName) AS
NAME
FROM CUSTOMER a
WHERE a.Cust_Number NOT IN (SELECT Cust_Number FROM CUSTOMERORDER);
Output table:
Query 5:
CREATE TABLE FINAL AS
SELECT DISTINCT CONCAT(a.Emp_FName,' ',a.Emp_LName) AS NAME,b.JobType_Name
FROM EMPLOYEE a INNER JOIN JOBTYPE b
ON a.Emp_JobTypeID = b.JobType_ID;
SELECT NAME FROM FINAL WHERE JobType_Name='Accountant';

Output table:
Query 6:
SELECT DISTINCT a.Prod_Num as Product_Number,a.Prod_Desc as Description,b.Inv_QntyOnHand as
Quantity_On_Hand
FROM PRODUCT a INNER JOIN INVENTORY b
ON a.Prod_Num = b.ProductNum;
Output table:
Query 7:
SELECT DISTINCT a.Prod_Num as Product_Number,a.Prod_Desc as Name,b.OrdLn_Qnty as Quantity_Sold
FROM PRODUCT a INNER JOIN ORDERLINE b
ON b.OrdLn_DatePicked <= '2018-05-20' AND a.Prod_Num = b.Prod_Num
ORDER BY a.Prod_Num,b.OrdLn_Qnty;
Output table:
Query 8:
SELECT DISTINCT a.Prod_Num as Product_Number,a.Prod_Desc as Description,a.Prod_Price as Price
FROM PRODUCT a
WHERE a.Prod_Price <= (SELECT AVG(Prod_Price) FROM PRODUCT);
Output table:
Query 9:
UPDATE EMPLOYEE SET Emp_HourlySalary = Emp_HourlySalary*1.075;
SELECT CONCAT(a.Emp_FName,' ',a.Emp_LName) as NAME,a.Emp_HourlySalary as SALARY
FROM EMPLOYEE a;
Output table:
Query 10:
SELECT CONCAT(a.Emp_FName,' ',a.Emp_LName) as NAME,b.Pay_num_of_hours as
Hours_Paid,b.Pay_amount_gross as Amount_Paid
FROM EMPLOYEE a, PAYSLIP b,(SELECT MAX(Pay_Date) AS Recent,Emp_ID FROM PAYSLIP GROUP
BY Emp_ID) AS c
WHERE c.Emp_ID = b.Emp_ID AND a.Emp_ID = b.Emp_ID AND c.Recent = b.Pay_Date;

Output table:
Query 11:
SELECT DISTINCT a.Prod_Desc as Product_Description,a.Prod_Price as PRICE
FROM PRODUCT a INNER JOIN BRAND b
ON a.Prod_BrandID = b.Brand_ID AND b.Brand_Name = 'Armani'
ORDER BY a.Prod_Price;
Output table:
Query 12:
CREATE TABLE TEST AS
SELECT SupvisorID AS Supervisor_ID,Emp_ID AS Employee_ID,CONCAT(Emp_FName, ' ',Emp_LName)
AS Employee_Name
FROM Employee;
SELECT a.Supervisor_ID,CONCAT(b.Emp_FName,' ',b.Emp_LName) AS
Supervisor_Name,a.Employee_ID,a.Employee_Name
FROM TEST a INNER JOIN EMPLOYEE b
ON a.Supervisor_ID = b.Emp_ID
ORDER BY a.Supervisor_ID;
Output table:

Reports on SQL Queries in Task 4
INSERT INTO ADDRESS(Address_St,Address_City,Address_State,Address_Postcode,AddType_ID)
VALUES('11 Fuller Road','Marsden','QLD','4132','R');
INSERT INTO CUSTOMER(Cust_FName,Cust_LName,Cust_Phone,Address_ID)
VALUES ('Daniel','Ortega','0431xxx668','6');
INSERT INTO BRAND(Brand_Name)
VALUES ('Prada');
INSERT INTO PRODUCT(Prod_Desc,Prod_Size,Prod_Price)
VALUES ('Shirts for man','XL','19.00');
UPDATE PRODUCT SET PRODUCT.Prod_BrandID = (SELECT Brand_ID FROM BRAND WHERE
Brand_Name = 'Prada') WHERE Prod_Desc = 'Shirts for man';
INSERT INTO INVENTORY(ProductNum,Str_Num,Inv_QntyOnHand,Inv_QtyOrdered)
VALUES ((SELECT Prod_Num FROM PRODUCT WHERE Prod_Desc = 'Shirts for man'),'3','45','0');
INSERT INTO CUSTOMERORDER(CustOrd_Date,Cust_Number,Str_Num)
VALUES ('2018-09-06',(SELECT Cust_Number FROM CUSTOMER WHERE Cust_Phone =
'0431xxx668'),'3');
INSERT INTO ORDERLINE(CustOrd_ID,Prod_Num,OrdLn_DateArrived,OrdLn_DatePicked,OrdLn_Qnty)
VALUES ((SELECT CustOrd_ID FROM CUSTOMERORDER WHERE Cust_Number = (SELECT
Cust_Number FROM CUSTOMER WHERE Cust_Phone = '0431xxx668')),(SELECT Prod_Num FROM
PRODUCT WHERE Prod_Desc = 'Shirts for man'),'2018-09-08','2018-09-10','2');
Output tables:
ADDRESS:
CUSTOMER:
BRAND:

PRODUCT:
INVENTORY:
CUSTOMERORDER:
ORDERLINE:

Bibliography
No resources apart from course material is used for this assignment.