CDDB3303: SQL Database Queries

This practical assignment, part of the CDDB3303 Intermediate Database course, assesses students' understanding of relational databases and SQL commands. Students are provided with three tables (Product, Customer, Order) containing sample data and are tasked with writing and executing SQL queries to perform various operations. These include creating the database and tables, inserting data, querying data based on specific criteria (e.g., location, date range, category), updating data, and combining data from multiple tables. The assignment requires the use of Oracle Application Express (Oracle APEX) for query execution, with screenshots of each query required as proof of completion. A rubric is provided outlining the marking scheme based on the successful implementation of each query.

ASSIGNMENT QUESTION / SOALAN TUGASAN
PURPOSE
The purpose of this assignment is to strengthen learner’s understanding on the relational database
using SQL commands.
REQUIREMENT / ASSIGNMENT QUESTION
The questions will be based on the relational database tables as given below.
PRODUCT
ProductID ProductName Price Category
1 Router 945.00 Network
2 Laptop 3400.00 Hardware
3 Microsoft Software 450.00 Software
4 Switch 567.00 Network
CUSTOMER
CustomerID Customer_Name Customer_Address Customer_Contact
101 Halinda Shah Alam 0192345333
102 Rafeq Subang Jaya 0198089888
103 Jason Kepong 0186789543
104 Lee Kim Siew Shah Alam 0145567865
105 Joshua Shah Alam 0132244567
106 Zarina Subang Jaya 0145567783
107 Amin Kepong 0186675433
ORDER
OrderID Date Total_Amount ProductID CustomerID
1 12 Jan 2017 1000.00 1 101
2 14 Jan 2017 3400.00 2 102
3 15 Jan 2017 6400.00 2 102
4 20 Jan 2017 567.00 3 104
5 22 Jan 2017 567.00 4 105

Based on the tables, you are required to execute SQL queries for the given questions:
1. Create a Database CBDB4103, or any suitable name.
2. Create Product, Customer and Order tables.
3. Insert the above given data into the created tables.
4. List all the records in Customer Table.
5. List product name, price and category from Product.
6. List customer name and contact number who is from Shah Alam.
7. List product name and product price that was ordered from 12 Jan 2017 to 20 Jan 2017.
8. Update the customer address from ‘Kepong’ to ‘Serdang’ for the CustomerID which ends
with number 3.
9. List customer name, customer address, and total amount for customer from Shah Alam
and purchased product from category ‘Network’ and ‘Hardware’.
Note:
1. You are required to use Oracle Application Express (Oracle APEX) at
https://apex.oracle.com/i/index.html. If you do not have an account with Oracle
Application Express, do register yourself first to get a free workspace.
2. For every SQL query created for the above questions, capture the screen and paste it in a
MS-Word, as a proof that you are using Oracle APEX.
(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