SQL Queries for Product, Customer, and Order Tables
VerifiedAdded on 2019/09/30
|12
|1173
|184
Report
AI Summary
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.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
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));
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));
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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);
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');
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);
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);
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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);
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;
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');
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');
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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');
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
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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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
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
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
1 out of 12
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
Unlock your academic potential
© 2024 | Zucol Services PVT LTD | All rights reserved.