bidbasequeries.txt

电子投标系统

Member Page:

Auction History:
select  auction_id,max_bid(SellingPrice), from auction
where auction_max_bid_buyer_login = "LoginName";

View Feedback:
select Auction_ID,about_login,FeedbackNotes from feedback
where giver_login="loginName";

Give Feedback:
insert into feedback values
(Auction_ID,LoginName,againstLogin,FeedbackNotes);

Sell an Item:

insert into auction values
(Auction_ID.NextVal,Start_Date,auction_duration,auction_minimum_bid,auction_reserved_bid,auction_start_time,auction_max_bid,auction_maxbid_buyer_login,payment_mode,0);

buysomething:
 select a.auction_id,i.item_title,i.item_location,i.item_country,i.item_picture,i.item_sound----- from auction a,item i
where i.item_category=category;

MainPage:
Categories:
  select category_name from category;

NewMember:
	insert into member values(member_login,member_password, 	member_fname,member_middle,member_last,member_email,member_dob		DATE,member_phone,member_stadd,member_city,member_postalcode, 	member_state,member_country,member_visanumber,member_visaname,
member_visaexpiry);			


====================================
create view category_view
as 
select a.auction_id ,i.item_title ,a.auction_start_date ,i.category_id,
(a.auction_start_date+a.auction_duration) end_date, a.auction_start_bid
from auction a,(
	select auction_id,item_title,category_id
	from auctiondetails,category c
	where c.category_id=i_category
	) i
where a.auction_id=i.auction_id 
and ( a.auction_start_date+a.auction_duration )>sysdate
;
============================
create view sold_items
as
select b.auction_id,b.buyer_login,s.member_login seller_login,b.auction_quantity,b.Max_bid_amount
from finalizedbids b,(select * from auctiondetails,member where seller_login=member_login) s
where b.auction_id=s.auction_id;
=========================================
create view hurry_auctions
as 
select auction_id from auction where (auction_start_date+auction_duration)=sysdate;	
===================================
create view waiting_auction_decision
as
select a.auction_id,b.auctionbid from auction a,dual,
(select auction_id, MAX(latest_bid_amount) auctionbid from bidding group by auction_id) b
where (a.auction_start_date+a.auction_duration) < sysdate and b.auction_id=a.auction_id
and b.auctionbid>= a.auction_reserved_bid;
===========================
===========================
create view expired_auctions
as
select auction_id,(auction_start_date+auction_duration) endingdate from auction
where  (auction_start_date+auction_duration)<sysdate;
===================================

=================================
create view waiting_auction_decision2
as 
select  w.auction_id,w.auctionbid,d.seller_login,member_buyer_login 
from waiting_auction_decision w,bidding b,auctiondetails d, finalizedbids f where
auctionbid=latest_bid_amount and w.auction_id=d.auction_id and w.auction_id=b.auction_id and w.auction_id<>f.auction_id; 
=========================
=================================
create view waiting_auction_decision3
as 
select  w.auction_id,w.auctionbid,w.seller_login,w.member_buyer_login 
from waiting_auction_decision2 w where
w.auction_id not in(select f.auction_id from finalizedbids f);
=========================
view not selling items
=======================
create view not_selling_items
as
select ad.seller_login,ad.auction_id,ad.item_title,aa.auction_start_bid,aa.auction_start_date,(aa.auction_start_date+aa.auction_duration) auction_end_date, aa.auction_reserved_bid
from auctiondetails ad,(
		select a.auction_id, a.auction_start_date,a.auction_duration,a.auction_start_bid,a.auction_reserved_bid
		from auction a
		where a.auction_id not in(
				select vs.auction_id
				from view_selling_items vs
				)
		and  a.auction_id not in(
				select vs2.auction_id
				from view_sold_items vs2
				)
		) aa
where  ad.auction_id = aa.auction_id
;
=============================
GO AUCTION
========================
SELLER
------

create view view_selling_items
as
select a.auction_id,seller_login,item_title,a.auction_start_bid,latest_bid_amount,a.auction_reserved_bid,(a.auction_start_date + a.auction_duration) ending_date
from auction a,
	(
	select bidding.auction_id,latest_bid_amount,item_title,seller_login
	from bidding,	
		(
		select auction_id,item_title,seller_login
		from auctiondetails		
		) d
	where bidding.auction_id=d.auction_id 
	and (d.auction_id,latest_bid_amount)=any
					(
					select bid.auction_id,max(bid.latest_bid_amount)
					from bidding bid
					group by bid.auction_id
					) 
	) b
where a.auction_id = b.auction_id and a.auction_id not in (
					select fbid.auction_id
					from finalizedbids fbid						
						)
;
----------------
BUYER
-------------
create view view_buying_items
as
select 	a.auction_id,addd.item_title,latest_bid_amount your_bid,max_bid_value,
	(a.auction_start_date+a.auction_duration) ending_date,member_buyer_login
from auction a,
	(
	select ad.auction_id,item_title,latest_bid_amount,max_bid_value,member_buyer_login
	from auctiondetails ad,
		(
		select bid.auction_id,bid.latest_bid_amount,max_bid_value,member_buyer_login
		from bidding bid,
			(select bid2.auction_id,max(bid2.latest_bid_amount) max_bid_value
			from bidding bid2
			group by bid2.auction_id
			) b
		where bid.auction_id=b.auction_id
		) b2
	where ad.auction_id=b2.auction_id
	) addd
where a.auction_id = addd.auction_id and (a.auction_id,addd.member_buyer_login) not in(
					select fbid.auction_id,buyer_login
					from finalizedbids fbid						
						)  
;

--------------------------------

=======================
HISTORY
========================
SELLER
------

create view view_sold_items
as
select a.auction_id,seller_login,item_title,a.auction_start_bid,latest_bid_amount,a.auction_reserved_bid,(a.auction_start_date + a.auction_duration) ending_date
from auction a,
	(
	select bidding.auction_id,latest_bid_amount,item_title,seller_login
	from bidding,	
		(
		select auction_id,item_title,seller_login
		from auctiondetails		
		) d
	where bidding.auction_id=d.auction_id 
	and (d.auction_id,latest_bid_amount)=any
					(
					select bid.auction_id,max(bid.latest_bid_amount)
					from bidding bid
					group by bid.auction_id
					) 
	) b
where a.auction_id = b.auction_id and a.auction_id = any(
					select fbid.auction_id
					from finalizedbids fbid						
						)
;
----------------
BUYER
-------------
create view view_bought_items
as
select 	a.auction_id,addd.item_title,addd.seller_login,latest_bid_amount your_bid,max_bid_value,
	(a.auction_start_date+a.auction_duration) ending_date,member_buyer_login
from auction a,
	(
	select ad.auction_id,item_title,ad.seller_login,latest_bid_amount,max_bid_value,member_buyer_login
	from auctiondetails ad,
		(
		select bid.auction_id,bid.latest_bid_amount,max_bid_value,member_buyer_login
		from bidding bid,
			(select bid2.auction_id,max(bid2.latest_bid_amount) max_bid_value
			from bidding bid2
			group by bid2.auction_id
			) b
		where bid.auction_id=b.auction_id
		) b2
	where ad.auction_id=b2.auction_id
	) addd
where a.auction_id = addd.auction_id and (a.auction_id,addd.member_buyer_login) = any(
					select fbid.auction_id,buyer_login
					from finalizedbids fbid						
						)  
;
15384500

--------------------------------