CDDB3303 Database Assignment

This practical assignment focuses on SQL database management. Students are tasked with creating a database, defining tables (Product, Customer, Order), inserting data, and executing various SQL queries to retrieve and manipulate information. The assignment includes creating queries to list records, filter data based on conditions, update records, and join tables to retrieve combined information. A rubric is provided, outlining the marking scheme based on the successful implementation of each query. The assignment provides a hands-on approach to learning SQL and database management.

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