SQL Queries for Product, Customer, and Order Tables

The assignment requires creating SQL queries to create tables for Product, Customer, and Order, as well as inserting data into these tables and creating six different types of queries (1-6) using the provided database schema. The queries must be implemented in a small chunk by small task implementation and not all tasks have been completed.

ASSIGNMENT QUESTION / SOALAN TUGASAN
1. Create a Database CBDB4103, or any suitable name.
2. Create Product, Customer and Order tables.
Product Table
create table Product(ProductId int,ProductName varchar2(30),Price decimal(10,2),Category
varchar2(30));

Customer Table
create table Customer(CustomerId int,CustomerName varchar2(30),CustomerAddress
varchar2(30),CustomerContact number(15));
Orders Table
create table Orders(OrderId int,Date1 varchar2(30),Total_Amount decimal(10,2),ProductId
int,CustomerId int);

3. Insert the above given data into the created tables.
Product Table
insert into Product values(1,'Router',945.00,'Network');
insert into Product values(2,'Laptop',3400.00,'Hardware');
insert into Product values(3,'Microsoft Software',450.00,'Software');
insert into Product values(4,'Switch',567.00,'Network');

Customer Table
insert into Customer values(101,'Halinda','Shah Alam',0192345333);
insert into Customer values(102,'Rafeq','Subang Jaya',0198089888);
insert into Customer values(103,'Jason','Kepong',0186789543);
insert into Customer values(104,'Lee Kim Siew','Shah Alam',0145567865);
insert into Customer values(105,'Joshua','Shah Alam',0132244567);
insert into Customer values(106,'Zarina','Subang Jaya',0145567783);
insert into Customer values(107,'Amin','Kepong',0186675433);

Orders Table
insert into Orders values(1,'12 Jan 2017',1000.00,1,101);
insert into Orders values(2,'14 Jan 2017',3400.00,2,102);
insert into Orders values(3,'15 Jan 2017',6400.00,2,102);
insert into Orders values(4,'20 Jan 2017',567.00,3,104);
insert into Orders values(5,'22 Jan 2017',567.00,4,105);

4. List all the records in Customer Table.
select * from Customer;
5. List product name, price and category from Product.
select ProductName,Price,Category from Product;

6. List customer name and contact number who is from Shah Alam.
select CustomerName, CustomerContact from Customer where CustomerAddress='Shah Alam';
7. List product name and product price that was ordered from 12 Jan 2017 to 20 Jan 2017.
select p.ProductName,p.Price from Product p where p.productId in(select Productid from Orders
where Date1 BETWEEN '12 Jan 2017' AND '20 Jan 2017');

8. Update the customer address from ‘Kepong’ to ‘Serdang’ for the CustomerID which ends
with number 3.
update Customer set CustomerAddress='Serdang' where MOD(CustomerId,10)=3 AND
CustomerAddress='Kepong';
9. List customer name, customer address, and total amount for customer from Shah Alam
and purchased product from category ‘Network’ and ‘Hardware’.
select c.CustomerName, c.CustomerAddress, o.Total_Amount from Product p, Customer c, Orders o
where c.CustomerAddress='Shah Alam' AND p.Category IN('Network','hardware');

(50)

ASSIGNMENT RUBRICS
CDDB3303 INTERMEDIATE DATABASE / JANUARY 2017
Criteria Weightage
Low Fair Above average Excellent
Max
Marks
0 1 2 3 4
Database is created
using a suitable
name.
0.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
2
Write the SQL query
to create Product,
Customer and Order
tables. 1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6
Write the SQL code
to insert data into
the tables. 1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6
Write the SQL code
to create query 1
1.5 No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6

Write the SQL code
to create query 2
1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6
Write the SQL code
to create query 3
1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6
Write the SQL code
to create query 4
1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6

Write the SQL code
to create query 5
1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6
Write the SQL code
to create query 6
1.5
No
implementation
of the tasks
Implemented only a small
chunk of this task
Moderate implementation
of the task
Most of the task was
implemented
The task was implemented
successfully
6
Total 12.5 50