Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

a. First, you need to create the above database in MS Access. (5 Marks)b. Populate those tables with suitable data (at least 3 records per table).i. You can use Datasheet view in MS Access OR SQL...

1 answer below »
a. First, you need to create the above database in MS Access. (5 Marks)b. Populate those tables with suitable data (at least 3 records per table).i. You can use Datasheet view in MS Access OR SQL statement (as given below) to enter suitable data records. INSERT into TableName VALUES (“..”,”..”,.....) ii. Include Proper foreign keys to create relationships in between tables. Hint: If you want to create a one-to-many relationship in your database, include one side primary key in the many side table as foreign keys XXXXXXXXXXMarks)Execute the following queries on the “Customer_ Delivery” database you created in MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions:(3 marks for each screen shot & remaining marks for the SQL query) a. Display details of all Employees recorded in the database. Your result set should be sorted on descending order of the employee_name. (5 Marks)b. Write a Query to find details of the Deliveries. (5 Marks)c. Write a query to find the details of the customer whose phone number is “ XXXXXXXXXX” (5 Marks)a. Prepare a list of all the records in the “Actual_Orders” table where actual _order _date is on 4th August 2020 which is not delivered. Assume “Order _status_code” is “Delivered” for all the orders already delivered and “Not_Delivered” for others. (5 Marks)b. Write a Query to find out staff details; employee_name and phone, who is responsible for delivering the order where “actual_order_ID ” is “C005”. (Hint: Here you need to join two tables XXXXXXXXXXMarks)c. Assume that you want to count how many orders are there in the “Actual_orders” table with product_id =124. Write a query to find the number. (5 Marks)d. Write a query to find out following information. Details of the delivery (Delivery _Id, delivery_status), employee who is handling these orders (employee_name, employee_phone) and details of the truck assigned for these deliveries (Truck_id, truck_licence_number XXXXXXXXXXMarks)
Answered Same Day Jul 31, 2021 MN405

Solution

Neha answered on Aug 04 2021
154 Votes
62672 - access/part A/Customer_Delivery.accd
        product_id        actual_order_id
        1        1
        2        2
        3        3
        actual_order_id        order_status_code        customer_Id        actual_order_date        order_details
        1        paid        1        7/28/20        order placed
        2        delivered        2        7/31/20        order delivered
        3        paid        3        8/1/20        order completed
        Customer_id        payment_method        customer_name        customer_phone        customer_email
        1        cash        Lucy        576767777        [email protected]
        2        card        Joseph        0477988779        [email protected]
        3        cash        henry        465657777        [email protected]
        delivery_id        actual_order_id        delivery_status        driver_employee_id        truck_id        delivery_date
        1        1        delivered        1        1        7/29/20
        2        2        shipped        2        2        7/31/20
        3        3        delivered        3        2        8/6/20
        Employee_Id        Employee_Name        Employee_phone
        1        Joy        567677777
        2        William        786577775
        3        Sarah        564566556
        product_id        supplier_id        product_name        product_price        product_description
        1        1        Laptop        ¤ 89.00        
        2        2        computer        ¤ 100.00        
        3        3        CPU        ¤ 50.00        
        Truck_Id        truck_licence_number        truck_details
        1        3445555        Large
        2        54545        Small
        3        45645        better mileage
SELECT Employee.[Employee_Id], Employee.[Employee_Name], Employee.[Employee_phone]
FROM Employee
ORDER BY Employee.[Employee_Name];
SELECT Deliveries.[delivery_id], Deliveries.[actual_order_id], Deliveries.[delivery_status], Deliveries.[driver_employee_id], Deliveries.[truck_id], Deliveries.[delivery_date]
FROM Deliveries;
SELECT Customer.[Customer_id], Customer.[payment_method], Customer.[customer_name], Customer.[customer_phone], Customer.[customer_email]
FROM Custome
WHERE Customer.[customer_phone] = '0477988779';
62672 - access/part A/Report.docx
SELECT Employee.[Employee_Id], Employee.[Employee_Name], Employee.[Employee_phone]
FROM Employee
ORDER BY Employee.[Employee_Name];
SELECT Employee.[Employee_Id], Employee.[Employee_Name], Employee.[Employee_phone]
FROM Employee
ORDER BY Employee.[Employee_Name];
SELECT Customer.[Customer_id], Customer.[payment_method], Customer.[customer_name], Customer.[customer_phone], Customer.[customer_email]
FROM Custome
WHERE Customer.[customer_phone] = '0477988779';
62672 - access/Part B/Customer_Delivery.accd
        product_id        actual_order_id
        123        C001
        124        C003
        124        C005
        order_status_code        customer_Id        actual_order_date        order_details        actual_order_id
        Not_Delivered        1        8/4/20        order placed        C001
        Delivered        2        7/31/20        order delivered        C003
        Not_Delivered        3        8/1/20        order...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here