Question

use workbench please

Task 1: Create a database and name it practical2DB. (2 marks)
Task 2: Create all the four tables according to the relational scheme in Figure 1. (8 marks)
Task 3: Insert 5 records to into each table. (5 marks)
Task 4: Write a query to list alphabetically ordered names, addresses, and IDs of all the customers whose postcode is in (40150, 40400, 47500).
(10 marks)
Task 5: Delete 1 record from Products table using their primary keys. (5 marks)
Task 6: Add a column by the name CustPhone with datatype INTEGER. (5 marks)
Task 7: Delete the column Customer address from the table Customer. (5 marks)
Task 8: Increase by 2 the value of the attribute QuantityOrdered of one of the records in the table "LineItems". (10 marks)
Task 9: Create a report to list all customer ID, order numbers, product id, product description, product price, quantity, and order total price for all of the available orders records. (13 marks)
Task 10: Write a query to list the total number of products, minimum price, maximum price, and average price of the products. (10 marks)
Task 11: Select the top 2 products that have been ordered more. (10 marks)
Task 12: Create a View with name "outOfStock" in which a list of all products (if there is any) that are out of stock will be reported. Test this View and prove it is working properly. (12 marks)
Task 13: Delete the View by using proper command. (5 marks)

can we fast not have time

Customer Length Column NameType CustID CustName Custaddress CustPostCode Nulls Integer Varchar Varchar Char No 4 25 No No No 25 8 Products Column Name ProductID Description StorageAddress StoragePostCode VarChar Reorderquantity QuantityInStock Unitprice Total quantitySold Integer Nulls No No Туре Integer Varchar VarChar Length 4 25 25 12 Integer Integer Decimal 4 No No 8,2 No Yes 4 Orders Column Name Type OrderID CustID OrderDate PromiseDate Nulls Length Integer Integer Date Date 4 No No No No 4 Lineltems Column Name ProductID OrderNo QuantityOrdered Integer Length 4 |Туре Integer Integer Nulls No No No 4 4 Figure 1

Answer 1

TASK 1:

CREATE DATABASE practical2DB

TASK 2:

CREATE TABLE Customer
(
CustID INT PRIMARY KEY NOT NULL,
CustName VARCHAR(25)NOT NULL,
Custaddress VARCHAR(45) NOT NULL,
CustPostCode CHAR(8)NOT NULL
)

CREATE TABLE Products
(
ProductID INT PRIMARY KEY NOT NULL,
Description VARCHAR(25)NOT NULL,
StorageAddress VARCHAR(25) ,
StoragePostCode VARCHAR(25),
Reorderedquantity INT(4) NOT NULL,
QuantityInStock INT(4) NOT NULL,
Unitprice DECIMAL,
Total_quantitySold INT
)
CREATE TABLE Orders
(
OrderID INT(4) PRIMARY KEY NOT NULL,
CustID INT(4) NOT NULL,
OrderDate DATE NOT NULL,
PromiseDate DATE NOT NULL
)

CREATE TABLE LineItems
(
ProductID INT(4) NOT NULL,
OrderNo INT(4) NOT NULL,
QuantityOrdered INT(4) NOT NULL
)

TASK 3:

INSERT INTO Customer VALUES(45678, "Vijay Kumar","108/87 road",'APY85120')

INSERT INTO Products VALUES(45678, "Laptop","108/87 road",'APY85120',5,5,25000,15)

INSERT INTO Orders VALUES(6758, 3478,12-03-2018,15-04-2019)

INSERT INTO LineItems VALUES(6758, 3489,7)

TASK 4:

SELECT CustName,CustID ,Custaddress
FROM Customer
WHERE CustPostCode IN (40150, 40400, 47500)
ORDER BY CustName

TASK 5:

DELETE FROM Products
WHERE ProductID=4

TASK 6:

ALTER TABLE Customer
ADD CustPhone INT

TASK 7:

ALTER TABLE Customer
DROP COLUMN Custaddress

TASK 8:

UPDATE TABLE LineItems
SET QuantityOrdered =QuantityOrdered*2