Database Fundamental (TIS 1101) Tutorial 6: Buyer - ID Invoice - ID

Database Fundamental (TIS 1101)
Tutorial 6
Q1. Based on the relationship diagram below, answer the following questions:
Buyer
1
Buyer_ID
Invoice Buyer_Name
Invoice_ID Buyer_Phone
Invoice_Date ∞ Buyer_Status
Buyer_ID
Sales 1 Invoice_Qty ∞
Item_ID
Sales_ID ∞
Sales_ID
Sales_Name
Item
Sales_Phone 1
Item_ID
Item_Name
Item_Type
Item_Price
Item_Bal
Item_Details
(a) Create a new database call “Lab06” in DB2. Next create the above tables (clearly
define the primary and foreign keys).
create database lab06;
create table Buyer

(Buyer_ID integer not null,
Buyer_Name varchar(30),
Buyer_Phone varchar(11),
Buyer_Status varchar(10),
primary key (Buyer_ID)
);
create table Item

(Item_ID integer not null,
Item_Name varchar(50),
Item_Type varchar(20),
Item_Price decimal(7,2),
Item_Bal integer,
Item_Details varchar(100),
primary key (Item_ID)
);
create table Sales

(Sales_ID integer not null,
Sales_Name varchar(50),
Sales_Phone varchar(11),
primary key (Sales_ID)
);
create table Invoice

(Invoice_ID integer not null,
Invoice_Date date,
Buyer_ID integer,
Invoice_Qty integer,
Item_ID integer,
Sales_ID integer,
primary key (Invoice_ID),
foreign key (Buyer_ID) references Buyer,
foreign key (Item_ID) references Item,
foreign key (Sales_ID) references Sales
);
(b) Write SQL commands to insert the data into each table as shown below:
insert into Sales values (56523,'Florence', '60135698231');

insert into Sales values (23598,'Zelda', '60125479563');
insert into Sales values (98665,'Clarry', '60148597965');
insert into Sales values (13579,'Nasri', '60195624664');
insert into Sales values (68595,'Arshavin', '60168462649');
insert into Item values (659,'LCD Monitor', 'Computer', 900.99, 50, '24 inch
Samsung');
insert into Item values (965,'Pen', 'Stationary', 1.64, 3, 'Red ink');
insert into Item values (855,'Printer', 'Computer', 300.55, 1, 'Epson super jet');
insert into Item values (698,'Fan', 'Electrical', 86.11, 4, 'Hitachi');
insert into Item values (456,'Rubber', 'Stationary', 0.63, 98, 'Faber 3cm');
insert into Item values (598,'Hardisc', 'Computer', 250.81, 2, 'Maxtor 500GB');
insert into Item values (879,'Pen Drive', 'Computer', 98.65, 78, 'MyDrive 16GB
mini version');
insert into Item values (989,'Nokia Phone', 'Electrical', 980.21, 100, 'N85 super
series');
insert into Item values (888,'Camera', 'Electrical', 1230.36, 8, 'Sony 15
Megapixel');
insert into Buyer values (1,'Jerremy', '60125689766', 'Active');

insert into Buyer values (2,'Cech', '60135695469', 'Not Active');
insert into Buyer values (3,'Meluda', '60191346876', 'Active');
insert into Buyer values (4,'Jessica', '60168954615', 'Active');
insert into Buyer values (5,'Toure', '60136546586', 'Not Active');
insert into Buyer values (6,'Jesmon', '60145226569', 'Active');
insert into Buyer values (7,'Torres', '60125469788', 'Active');
insert into Buyer values (8,'Martin', '60135468798', 'Not Active');
insert into Buyer values (9,'Taylor', '60124659789', 'Active');
insert into Invoice values (10010,'2008-12-15', 1, 2, 659, 68595);

(c) Write the SQL statement to list buyer’s name and phone sort by buyer’s name in
ascending order.
select Buyer_Name, Buyer_Phone from Buyer

order by Buyer_Name Asc;
(d) Write the SQL statement to show the names of the sales person who handles the
invoice of buyer Jeremy.
select Sales_Name from Buyer, Invoice, Sales

where Buyer.Buyer_ID = Invoice.Buyer_ID
AND Sales.Sales_ID = Invoice.Sales_ID
AND Buyer.Buyer_Name = 'Jerremy';
(e) Write the SQL statement to show all sale person name and phone who issued
invoice involving item type computer in the month of December 2008.
select Sales_Name, Sales_Phone from Invoice, Sales, Item

where Sales.Sales_ID = Invoice.Sales_ID
AND Invoice_Date BETWEEN '2008-12-01' AND '2008-12-31'
AND Item.Item_Type ='Computer'
AND Item.Item_ID = Invoice.Item_ID;
(f) Write the SQL statement to show all item name, item price, item balance and a
new column named as total value (price * balance) of items which is not under
the category of computer.
select Item_Name, Item_Price, Item_Bal, Item_Price*Item_Bal as Total_Value

from Item
where Item_Type NOT IN 'Computer';
(g) Find all buyers name whose phone number starts with 6013.
Select Buyer_Name from buyer

Where buyer_phone LIKE '6013%';

Database Fundamental (TIS 1101) Tutorial 6: Buyer - ID Invoice - ID

Uploaded by

Copyright:

Available Formats

Database Fundamental (TIS 1101) Tutorial 6: Buyer - ID Invoice - ID

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Fundamental (TIS 1101) Tutorial 6: Buyer - ID Invoice - ID

Uploaded by

Copyright:

Available Formats

Database Fundamental (TIS 1101)

create database lab06;

create table Buyer

create table Item

create table Sales

create table Invoice

insert into Sales values (56523,'Florence', '60135698231');

insert into Buyer values (1,'Jerremy', '60125689766', 'Active');

insert into Invoice values (10010,'2008-12-15', 1, 2, 659, 68595);

select Buyer_Name, Buyer_Phone from Buyer

select Sales_Name from Buyer, Invoice, Sales

select Sales_Name, Sales_Phone from Invoice, Sales, Item

select Item_Name, Item_Price, Item_Bal, Item_Price*Item_Bal as Total_Value

Select Buyer_Name from buyer

You might also like