TIS1101 labs

tis1101_lab05.sql

create database store;

connect to store;

-- create tables

create table sales_representatives
(
  id int primary key not null,
  name varchar(255),
  phone int
);

create table buyers
(
  id int primary key not null,
  name varchar(255),
  phone int,
  status varchar(60)
);

create table items
(
  id int primary key not null,
  name varchar(255),
  type varchar(255),
  price decimal(6,2) not null,
  balance smallint,
  details varchar(255)
);

create table invoices
(
  id int primary key not null,
  invoice_date date not null with default current date,
  quantity int,
  buyer_id int not null,
  foreign key(buyer_id) references BUYERS(id)
);

-- try altering tables
alter table sales_representatives
alter column phone
set data type bigint;

reorg table sales_representatives;

alter table buyers
alter column phone
set data type bigint;

reorg table buyers;

alter table invoices
add item_id int;

alter table invoices
add constraint fk_item_id foreign key(item_id) references items(id);

alter table invoices
add sales_representative_id int;

alter table invoices
add constraint fk_sales_rep_id foreign key(sales_representative_id) references sales_representatives(id);

reorg table invoices;

-- insert data

insert into items values 
(659, 'LCD Monitor', 'Computer', 900.99,50, '24 inch Samsung'),
(965, 'Pen', 'Stationery', 1.64, 3, 'Red ink'),
(855, 'Printer', 'Computer', 300.55, 1, 'Epson super jet'),
(698, 'Fan', 'Electrical', 86.11, 4, 'Hitachi'),
(598, 'Rubber', 'Stationery', 0.63, 98, 'Faber 3cm'),
(456, 'Hardisc', 'Computer', 250.81, 2, 'Maxtor 500GB'),
(879, 'Pen Drive', 'Computer', 98.65, 78, 'MyDrive 16GB mini version'),
(989, 'Nokia Phone', 'Electrical', 980.21, 100, 'N85 super series'),
(888, 'Camera', 'Electrical', 1230.36, 8, 'Sony 15 Megapixel');

insert into sales_representatives values
(56523, 'Florence', 60135698231),
(23598, 'Zelda', 60125479563),
(98665, 'Clarry', 60148597965),
(13579, 'Nasri', 60195624664),
(68595, 'Arshavin', 60168462649);

insert into buyers values
(1, 'Jeremy', 61255689766, 'Active'),
(2, 'Cech', 60135695469, 'Not Active'),
(3, 'Meluda', 60191346876, 'Active'),
(4, 'Jessica', 60168954615, 'Active'),
(5, 'Toure', 60136546586, 'Not Active'),
(6, 'Jesmon', 60145226569, 'Active'),
(7, 'Torres', 60125469788, 'Active'),
(8, 'Martin', 60135468798, 'Not Active'),
(9, 'Taylor', 60124659789, 'Active');

insert into invoices values
(10010, '2013-12-15', 2, 1, 659, 68595),
(10011, '2011-01-13', 26, 3, 879, 56523),
(10012, '2013-03-06', 8, 4, 989, 23598),
(10013, '2012-10-28', 10, 1, 965, 13579),
(10014, '2013-05-16', 6, 7, 888, 98665),
(10015, '2013-12-01', 1, 6, 456, 13579),
(10016, '2013-12-03', 7, 3, 659, 98665);

-- question (c)

select name from buyers order by name asc;

-- question (d)

select name from sales_representatives
where id in
  (select sales_representative_id from invoices where buyer_id in
    (select id from buyers where name = 'Jeremy')
  );

-- question (e)

select name, phone from sales_representatives
where id in
  (select sales_representative_id from invoices
   where invoice_date between '2013-12-01' and '2013-12-31'
   and item_id in (
    select id from items where type = 'Computer'
   ));
   
-- question (f)

select name, price, balance, (price * balance) as total 
from items
where type != 'Computer';

-- question (g)

select name
from buyers
where phone 
between 60130000000
and 60139999999;

-- question (h)

delete from buyers where name = 'Taylor';

tis1101_lab06.sql

-- question 1
alter table invoices
add column price decimal(7,2);

reorg table invoices;

-- question 2
update invoices
set price = quantity * (select price from items where id = item_id);

-- question 3
select name, price from items
order by price desc limit 0,1;

-- question 4
select count(*)
from buyers
where id in (select buyer_id from invoices);

-- question 5
select sum(price)
from invoices
where quantity >= 10;

-- question 6
update items
set price = price + 5
where type='Electrical';

-- question 7
create trigger question7
after insert on invoices
for each row mode db2sql
update invoices
set price = quantity * (select price from items where id = item_id);

-- question 8
insert into invoices
values (
  10018,
  '2008-01-26',
  2,
  (select id from buyers where name ='Martin'),
  (select id from items where name = 'Nokia Phone'),
  (select id from sales_representatives where name = 'Zelda'),
  NULL
);

select * from invoices; -- check q8