Question

SECTION B: SQL Data Manipulation Language 1. The following are parts of tables from customer sales databases schema at the AWIE Sdn. Bhd. Company SALES CustID Date SaleAmount 2. Jun 2014 100.22 CUSTOMER CustID FirstName LastName DOB Phone 1 July 2014 909.95 어 1 John Smith 2/4/1968 012 222-2222 3 July 2014 522.95 2 Steven Goldfish 4/4/1974 013 455-4545 3 Dec 2014 100.00 3 Paula Brown 5/24/1978 017 323-3232 4 Smith 4 James 20/10/1980 010 323-8888 Dec 2014 555.55 a) Create and fill the data of CUSTOMER and SALES table. b) Get the difference between sales amount of CustID=4 and CustID=2. c) Update the DOB for Paula to 17/04/1983. d) Create view so that sales manager can see the details of sales amount for the month of December 2014. e) Find minimum, maximum and average Sale Amount. f) Display all customer information and sales amount for Sale Amount less than 200.00. g) List the details (FirstName, LastName, Phone) of customers who shopped on December 2014. h) List all customers who either has last name of “Smith” or make a purchase on July 2014

Answer 1

cust_sales_MS_SQL...RHCF\Ganesh (52))* X --answer to question a CREATE DATABASE Customer Sales GO USE Customer Sales GO CREATE TABLE Customer (CustID INT PRIMARY KEY, FirstName VARCHAR(10), LastName VARCHAR(10), DOB date, Phone varchar(11)) GO CREATE TABLE Sales (CustID INT, SalesDate varchar(15), SalesAmount Decimal(9,2)) GO ALTER TABLE Sales ADD CONSTRAINT fk_customer_sales FOREIGN KEY (CustID) REFERENCES Customer (CustID) GO O INSERT INTO CUSTOMER VALUES (1,'John' Smith', '2/4/1968', '012-222-2222') INSERT INTO CUSTOMER VALUES (2, 'Steven', 'GoddFish', '4/4/1974', '013-455-4545') INSERT INTO CUSTOMER VALUES (3, 'Paula', 'Brown', '5/24/1978', '017-323-3232') INSERT INTO CUSTOMER VALUES (4,'James', 'Smith', '10/20/1980', '610-323-8888') INSERT INTO Sales VALUES (2, 'June 2014', 100.22) INSERT INTO Sales VALUES (1,'July 2014',909.95) INSERT INTO Sales VALUES (3,'July 2014',522.95) INSERT INTO Sales VALUES (3, 'Dec 2014',100.00) INSERT INTO Sales VALUES (4, 'Dec 2014' 555.55)

cust_sales_MS_SQL...RHCF\Ganesh (52))* X INSERT INTO CUSTOMER VALUES (1,'John', 'Smith', '2/4/1968', '012-222-2222') INSERT INTO CUSTOMER VALUES (2, 'Steven', 'GoddFish', '4/4/1974', '913-455-4545') INSERT INTO CUSTOMER VALUES (3, 'Paula', 'Brown', '5/24/1978', '917-323-3232') INSERT INTO CUSTOMER VALUES (4, 'James', 'Smith', '10/20/1980', '610-323-8888') INSERT INTO Sales VALUES (2, 'June 2014', 100.22) INSERT INTO Sales VALUES (1,'July 2014',909.95) INSERT INTO Sales VALUES (3,'July 2014',522.95) INSERT INTO Sales VALUES (3, 'Dec 2014',100.00) INSERT INTO Sales VALUES (4, 'Dec 2014',555.55) SELECT SELECT FROM CUSTOMER FROM Sales 100 % Results CustID Messages First Name John 1 1 2 2 Steven 3 3 Paula James 4 4 CustID 2 Sales Date June 2014 Last Name DOB Phone Smith 1968-02-04 012-222-2222 Godd Fish 1974-04-04 013-455-4545 Brown 1978-05-24 017-323-3232 Smith 1980-10-20 010-323-8888 SalesAmount 100.22 909.95 522.95 100.00 555.55 1 2 1 July 2014 July 2014 3 3 4 3 Dec 2014 Dec 2014 5 4

cust_sales_MS_SQL...RHCF\Ganesh (52)) X --answer to question b DECLARE @SalesOne decimal(9,2), @SalesTwo decimal(9,2), @DifferenceSalesAmount decimal(9,2) SELECT @SalesOne-SalesAmount from Sales where CustID=4 SELECT @salesTwo-SalesAmount from Sales where CustID=2 SET @Difference SalesAmount - @salesOne - @salesTwo Print 'Difference between the salesamout of CustID= 4 and CustID=2 is : + CAST(@DifferenceSalesAmount AS varchar(10) 100 % Messages Difference between the salesamout of CustID= 4 and CustID=2 is : 455.33

cust_sales_MS_SQL...RHCF\Ganesh (52))* X answer to question C - WE CAN USE EITHER OF THE BELOW QUERY UPDATE CUSTOMER SET DOB = '04/17/1983' WHERE FirstName ='Paula' UPDATE CUSTOMER SET DOB = '04/17/1983' WHERE CustID = (SELECT CustID FROM CUSTOMER WHERE FirstName ='Paula') SELECT * FROM Customer WHERE FirstName ='Paula' 100 % Results CustID Messages First Name Paula Phone Last Name Brown DOB 1983-04-17 1 3 017-323-3232

x cust_sales_MS_SQL...RHCF\Ganesh (52))* x answer to question d AS CREATE VIEW view SalesAmount SELECT C.CustID, C.firstname, c.lastname, S.SalesDate, S. SalesAmount from Customer C inner join sales s on C.CustID = S. CustID WHERE S.SalesDate = 'Dec 2014 SELECT * FROM view_SalesAmount 100 % Results custID Messages firstname lastname Paula Brown James Smith 3 Sales Date Dec 2014 Dec 2014 Sales Amount 100.00 555.55 4 N

cust_sales_MS_SQL..RHCF\Ganesh (52))* X answer to question e SELECT MIN(salesamount) AS 'MINIMUM SALES AMOUNT' FROM Sales SELECT MAX(salesamount) AS 'MAXIMUM SALES AMOUNT ' FROM Sales SELECT AVG(salesamount) AS "AVERAGE SALES AMOUNT' FROM Sales 100 % Results Messages MINIMUM SALES AMOUNT 100.00 1 MAXIMUM SALES AMOUNT 909.95 1 AVERAGE SALES AMOUNT 437.734000 1 Query executed successfully. LAPTOP-MEU

cust_sales_MS_SQL...RHCF\Ganesh (52))* X answer to question f SELECT C.CustID,C.firstname, c.lastname, S. SalesAmount from Customer C inner join sales s on C.CustID = S. CustID WHERE S. SalesAmount <200.00 100 % Results custID 2 Messages firstname lastname Steven Godd Fish Paula Brown SalesAmount 100.22 100.00 2 3

cust_sales_MS_SQL...RHCF\Ganesh (52))* X answer to question & SELECT C. firstname, c.lastname, C.Phone from Customer C inner join sales s on C. CustID = S. CustID WHERE S.SalesDate = 'Dec 2014 100 % FER Results Phone Messages lastname Brown Smith firstname Paula James 1 017-323-3232 010-323-8888 2

cust_sales_MS_SQL...RHCF\Ganesh (52))* X answer to question h SELECT C.CustID,C.firstname, c.lastname, S.SalesDate, S. SalesAmount from Customer Cinner join sales s on C.CustID = S. CustID WHERE C. lastname ='Smith' OR S. SalesDate = 'July 2014 — 100 % Results custID Sales Amount 1 Messages firstname lastname John Smith Paula Brown James Smith Sales Date July 2014 July 2014 Dec 2014 2 نيا 909.95 522.95 555.55 نيا 4