BN204 – Database Technologies Assignment 1 – Developing and Managing Databases T1,2020
Part A
1. Create Database
Created Tables.
Inserted records.
Customers table:
INSERT INTO Customers VALUES (‘MIT190733’, ‘Mansimar’);
INSERT INTO Customers VALUES (‘MIT190734’, ‘Makineni’);
INSERT INTO Customers VALUES (‘MIT190735’, ‘Ray’);
INSERT INTO Customers VALUES (‘MIT190736’, ‘Steve’);
INSERT INTO Customers VALUES (‘MIT190737’, ‘Sam Smith’);
MIT192438
Damanpreet Chadha
Daman
Customer_Purchase
Purchase_ID
Customer_ID
Service_Code
Customers
Customer_ID
Cust_Name
Accounts
Account_ID
Balance
Customer)ID
Transactions
Transaction_ID
Account_ID
Purchase_ID
Transaction_Details
Transaction_Date
INSERT INTO Customers VALUES (‘MIT190733′,’Daman’);
INSERT INTO Customers VALUES (‘MIT190737′,’ Sam Smith);
INSERT INTO Customers VALUES (‘MIT190734′,’Makineni’);
INSERT INTO Customers VALUES (‘MIT190735′,’Ray’);
INSERT INTO Customers VALUES (‘MIT190736′,’ Steve ‘);
Accounts table:
INSERT INTO Accounts VALUES (‘ACC0001’, ‘2050’,’MIT190733′);
INSERT INTO Accounts VALUES (‘ACC0002’, ‘4000’,’MIT190734′);
INSERT INTO Accounts VALUES (‘ACC0003’, ‘10704’,’MIT190735′);
INSERT INTO Accounts VALUES (‘ACC0004’, ‘6500’,’MIT190736′);
INSERT INTO Accounts VALUES (‘ACC0005’, ‘5499’,’MIT190737′);
Customer_Purchase table:
INSERT INTO Customer_Purchase VALUES (‘PUR0001′,’MIT190733′,’SRV105’);
INSERT INTO Customer_Purchase VALUES (‘PUR0002′,’MIT190734′,’SRV109’);
INSERT INTO Customer_Purchase VALUES (‘PUR0003′,’MIT190735′,’SRV100’);
INSERT INTO Customer_Purchase VALUES (‘PUR0004′,’MIT190736′,’SRV604’);
INSERT INTO Customer_Purchase VALUES (‘PUR0005′,’MIT190737′,’SRV931’);
INSERT INTO Accounts VALUES (‘ACC0001’, ‘2050’,’MIT190733′);
INSERT INTO Accounts VALUES (‘ACC0003’, ‘10704’,’MIT190735′);
INSERT INTO Accounts VALUES (‘ACC0002’, ‘4000’,’MIT190734′);
INSERT INTO Accounts VALUES (‘ACC0004’, ‘6500’,’MIT190736′);
INSERT INTO Accounts VALUES (‘ACC0005’, ‘5499’,’MIT190737′);
INSERT INTO Customer_Purchase VALUES (‘PUR0001′,’MIT190733′,’SRV105’);
INSERT INTO Customer_Purchase VALUES (‘PUR0002′,’MIT190734′,’SRV109’);
INSERT INTO Customer_Purchase VALUES (‘PUR0003′,’MIT190735′,’SRV100’);
INSERT INTO Customer_Purchase VALUES (‘PUR0004′,’MIT190736′,’SRV604’);
INSERT INTO Customer_Purchase VALUES (‘PUR0005′,’MIT190737′,’SRV931’);
Customer_Purchase table:
INSERT INTO Transactions VALUES (‘TR12596′,’ACC0001′,’PUR0001′,’At McD’,’04-04-2020′);
INSERT INTO Transactions VALUES (‘TR16493′,’ACC0002′,’PUR0002′,’At Crossword’,’01-20-2020′);
INSERT INTO Transactions VALUES (‘TR19543′,’ACC0003′,’PUR0003′,’At Library’,’02-15-2020′);
INSERT INTO Transactions VALUES (‘TR20564′,’ACC0004′,’PUR0004′,’At School’,’03-09-2020’);
INSERT INTO Transactions VALUES (‘TR20596′,’ACC0006′,’PUR0005′,’At McD’,’02-27-2020’);
2. Write SQL Queries
a. Display details of all Customers recorded in the database. Your result set should be sorted on ascending order of
the Customer_id.
SELECT *
FROM Customers
ORDER BY Customers.Customer_ID;
b. Assume you need to find details of the customers (customer_ID) whose Account balance is greater than $4000.
SELECT Customer_ID
FROM Accounts
WHERE Balance > 4000;
c. Display number of customers registered in the system.
SELECT count (Customer_ID) as Count_of_Customers
FROM Customers;
The post BN204 – Database Technologies Assignment 1 appeared first on My Assignment Online.
