createTable.sql

create table authorisation(
    username varchar(20) not null primary key,
    password varchar(20) not null
)


create table users(

username varchar(20) not null primary key,
fname varchar(20) not null,
lname varchar(20),
address varchar(20),
webaddress varchar(20),
email varchar(20),
phone varchar(20),
mobile varchar(20)
)

create table sailItems
(
itemId int identity primary key ,
itemName varchar(20),
username varchar(20),
price float(6),
bidDate smalldatetime, //not smalldate
isSailable boolean default true//added for history purpose
)

create table itemDescription
(
itemId int primary key,
type varchar(20),
description varchar(50)
)

create table bids
(
bidId int identity primary key,
itemId int not null,
username varchar(20) not null,
bidPrice float(10),
bidDate smalldatetime
)



//ADDING constraints
alter table users
add foreign key(username) references users(username)

alter table sailItems
add foreign key (username) references users(username);

alter table itemDescription
add foreign key (itemId) references sailItems(itemId);

alter table bids
add foreign key(itemId) references sailItems(itemId);

alter table bids
add foreign key(username) references users(username)


//Dropping tables
drop table users
drop table sailItems
drop table itemDescription


//VIEWing tables
select * from authorisation
select * from users



//POPULATIng tables

insert into users( username ,fname )
value('ballg','George') //Note the single quotes , this cause my so much time to waiste

insert into sailitems(itemname , price , biddate ,username )
values('fridge',111.12,'2010-09-21','ballg')

select * from sailitems;
ITEMID      ITEMNAME      USERNAME      PRICE      BIDDATE      ISSAILABLE
2    fridge    ballg    111.12    2010-09-21 00:00:00.0    TRUE
3    cooler    kinshukc    59.12    2010-09-20 00:00:00.0    TRUE
4    tv    richard    75.12    2010-09-22 00:00:00.0    TRUE
5    washing machine    rutuja    100.12    2010-09-23 00:00:00.0    TRUE
6    ipod    ballg    34.12    2010-09-24 00:00:00.0    TRUE
7    fridge    ballg    112.12    2010-09-24 00:00:00.0    TRUE
(6 rows, 4 ms)

insert into bids(itemId , bidPrice , biddate ,username )
values(2,112.12,'2010-09-24','kinshukc')

insert into bids(itemId , bidPrice , biddate ,username )
values(3,67.12,'2010-09-24','kinshukc')

insert into bids(itemId , bidPrice , biddate ,username )
values(4,76.12,'2010-09-25','rutuja')

insert into bids(itemId , bidPrice , biddate ,username )
values(6,33.12,'2010-09-25','rutuja')

insert into bids(itemId , bidPrice , biddate ,username )
values(6,35.12,'2010-09-25','rutuja')

insert into bids(itemId , bidPrice , biddate ,username )
values(6,34.13,'2010-09-25','ballg')