7003Ict Database Table With Customer Assessment Answer
- Task 1: Create a text file named Create_<StudentID>.sql (for example, Create_s3087654.sql) that will contain SQL statements to:
- Create a database named BigM_<StudentID>.
- Create all of the tables for the database according to the Database schema given with this document (separately attached).
- Task 2: Create a text file named Insert_<StudentID>.sql that will contain SQL statements to:
- Insert at least five records in each of the tables. The test data inserted into the table must ensure that each of the queries, specified in Task 3, outputs at least one record.
- Task 3: Create a text file named Query_<StudentId>.sql that will contain all the queries to display the following
- List of names and complete postal address of all employees sorted by their salary.
- The date on which the most recent customer order has been The customer name and date of the order will be sufficient.
- List of all the store names and their addresses, sorted in dictionary order of the store
- A list of all customers that have not placed an order Displaying customer number and name will be sufficient.
- A list containing the name of employees, which work as
- A list containing the total quantity on hand for each product (product number and description) regardless of
- A list showing each product sold (picked) on or before May 20, Show product number, name and quantity sold, sorted by product number and then quantity sold.
- A list of products (show product number, description and price) whose price is less than or equal to the average product
- Increase each employee’s salary by 5% and show the updated salary of all employees (name and salary).
- Show the pay information (employee name, hours paid, amount paid) of all employees in the most recent pay
Additional queries for 7003ICT students only:
- Make a list of all products of brand “Armani” and their price in ascending order of price. Show the product description and
- A list of supervisors (employee id, name) and all of their subordinates (employee id, name).
- Task 4: Create a text file named Update_<StudentId>.sql that will perform the followings. Insert additional data in the tables appropriately if
A customer named Daniel Ortega, from 11 Fuller Road, Marsden QLD 4132 and having a mobile number 0431xxx668, orders two shirts of size “XL” of brand “Prada” on 6 Sep 2018. The full order is ready on 8 Sep 2018 for pickup, but the customer picks up on 10 Sep 2018.
You should insert data into appropriate tables for the above order and make sure you update the data in the relevant tables after the order is picked up.
Answer:
The join statements according to Li et.al (2012) and Kroenke et.al (2010)
SELECTEMPLOYEE.Emp_FnameEMPLOYEE.Emp_Lname,EMPLOYEE.Emp_HourlySalary,ADDRESS.Address_St, ADDRESS.Address_City
FROM EMPLOYEE
INNER JOIN ADDRESS ON EMPLOYEE.Address_ID_Postal = ADDRESS.Address_ID
ORDER BY Emp_HourlySalary Desc;
SELECT CUSTOMER.Cust_Fname, CUSTOMER.Cust_Lname, CUSTOMERORDER.CustOrd_Date
FROM CUSTOMER
INNER JOIN CUSTOMERORDER ON CUSTOMER.Cust_Number=CUSTOMERORDER.Cust_Number
WHERE CUSTOMERORDER.CustOrd_Date = (SELECT MAX(CustOrd_Date) FROM CUSTOMERORDER);
Query 3:
SELECT STORE.Str_Name, ADDRESS.Address_St, ADDRESS.Address_City
FROM STORE
INNER JOIN ADDRESS ON STORE.Address_ID=ADDRESS.Address_ID
ORDER BY STORE.Str_Name Asc;
SELECT CUSTOMER.Cust_Fname, CUSTOMER.Cust_Lname, CUSTOMER.Cust_Number
FROM CUSTOMER
LEFT JOIN CUSTOMERORDER ON CUSTOMER.Cust_Number=CUSTOMERORDER.Cust_Number
WHERE CUSTOMERORDER.Cust_Number IS NULL;
SELECT EMPLOYEE.Emp_Fname, EMPLOYEE.Emp_Lname
FROM EMPLOYEE
LEFT JOIN JOBTYPE ON EMPLOYEE.Emp_JobTypeID=JOBTYPE.JobType_ID
WHERE JOBTYPE.JobType_ID = '1';
SELECT PRODUCT.Prod_Num, PRODUCT.Prod_Desc, INVENTORY.Inv_QntyOnHand
FROM PRODUCT
LEFT JOIN INVENTORY ON PRODUCT.Prod_Num=INVENTORY.ProductNum;
SELECT PRODUCT.Prod_Num, PRODUCT.Prod_Desc, ORDERLINE.OrdLn_Qnty
FROM PRODUCT
INNER JOIN ORDERLINE ON PRODUCT.Prod_Num=ORDERLINE.Prod_Num
WHERE ORDERLINE.OrdLn_DatePicked <= '2018-05-20'
ORDER BY PRODUCT.Prod_Num Asc, ORDERLINE.OrdLn_Qnty Desc;
SELECT Prod_Num, Prod_Desc, Prod_Price
FROM PRODUCT
WHERE Prod_Price <= 'SELECT AVG(Prod_Price)';
SELECT Emp_Fname, Emp_Lname, 1.075*Emp_HourlySalary
FROM EMPLOYEE;
SELECT EMPLOYEE.Emp_Fname, EMPLOYEE.Emp_Lname, PAYSLIP.Pay_num_of_hours, PAYSLIP.Pay_amount_gross, PAYSLIP.Pay_Date
FROM EMPLOYEE
INNER JOIN PAYSLIP ON EMPLOYEE.Emp_ID = PAYSLIP.Emp_ID
WHERE PAYSLIP.Pay_Date = (SELECT MAX(Pay_Date) FROM PAYSLIP);
Bibliography
Colossi, N. G., Dekimpe, D. M., Dere, J., & Sit, S. (2011). U.S. Patent No. 7,895,191. Washington, DC: U.S. Patent and Trademark Office.
Kroenke, D. M., Auer, D. J., Vandenberg, S. L., & Yoder, R. C. (2010). Database concepts. (7th Edition) Boston. Prentice Hall.
Li, M., Li, H. F., Sun, Y. F., & Zhao, S. (2012). U.S. Patent No. 8,122,008. Washington, DC: U.S. Patent and Trademark Office.
Özsu, M. T., & Valduriez, P. (2011). Principles of distributed database systems. (4th Edition). Boston. Springer Science & Business Media.
Buy 7003Ict Database Table With Customer Assessment Answers Online
Talk to our expert to get the help with 7003Ict Database Table With Customer Assessment Answers from Assignment Hippo Experts to complete your assessment on time and boost your grades now
The main aim/motive of the finance assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignment help Australia. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at www.assignmenthippo.com are so much skilled, capable, talented, and experienced in their field and use our best and free Citation Generator and cite your writing assignments, so, for this, they can effectively write the best economics assignment help services.
Get Online Support for 7003Ict Database Table With Customer Assessment Answer Assignment Help Online
Want to order fresh copy of the Sample 7003Ict Database Table With Customer Assessment Answers? online or do you need the old solutions for Sample 7003Ict Database Table With Customer Assessment Answer, contact our customer support or talk to us to get the answers of it.
Assignment Help Australia
Want to order fresh copy of the 7003Ict Database Table With Customer Assessment Answers? online or do you need the old solutions for Sample 7003Ict Database Table With Customer Assessment Answer, contact our customer support or talk to us to get the answers of it.
