-
Notifications
You must be signed in to change notification settings - Fork 0
/
eazybus_sql_commands.txt
101 lines (85 loc) · 2.38 KB
/
eazybus_sql_commands.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
Database
--------
create database eazybus;
use eazybus;
Customer table
--------------
create table customers(
cid int primary key auto_increment,
cpass varchar(12) not null,
cname varchar(12) not null,
contact varchar(10) not null
);
Bus table
---------
create table buses(
bid int primary key auto_increment,
type varchar(6) not null,
dtime time not null,
ddate date not null,
atime time not null,
adate date not null,
available_seats int not null,
total_seats int not null,
source varchar(20) not null,
destination varchar(20) not null,
contact_us varchar(10) not null,
price int not null
);
Ticket table
-------------
create table tickets(
bid int not null,
cid int not null,
cname varchar(12) not null,
seat_no int primary key,
contact varchar(10) not null,
source varchar(20) not null,
destination varchar(20) not null,
foreign key (bid) references buses (bid),
foreign key (cid) references customers (cid)
);
Used queries
-------------
==========
Admin DAO
==========
insert into buses(type,dtime,ddate,atime,adate,available_seats,total_seats,source,destination,contact_us,price) values(?,?,?,?,?,?,?,?,?,?,?);
-----
select * from buses where bid=?;
-----
select dtime, ddate from buses where bid=?;
-----
select source, destination from buses where bid=?;
-----
select c.cname, c.contact, t.bid, t.seat_no, t.source, t.destination from customers c inner join tickets t on c.cid=t.cid and t.seat_no=? and t.bid=?;
-----
select c.cname, c.contact, t.bid, t.seat_no, t.source, t.destination from customers c inner join tickets t on c.cid=t.cid and t.bid=?;
-----
select available_seats from buses where bid=?;
=============
Customer DAO
=============
insert into customers(cname,cpass,contact) values(?,?,?);
-----
select cname from customers where cid=? and cpass=?;
-----
select cname, cid, bid, seat_no, source, destination, contact from tickets where cid=?;
-----
select available_seats from buses where bid=?;
-----
select source, destination, contact_us from buses where bid=?;
-----
select cname from customers where cid=?;
-----
insert into tickets(bid, cid, seat_no, contact, source, destination, cname) values(?,?,?,?,?,?,?);
-----
update buses set available_seats=? where bid=?;
-----
delete from tickets where seat_no=?;
-----
update buses set available_seats=available_seats+1 where bid=?
-----
select cpass from customers where cid=?
-----
select * from buses where ddate=? and source=? and destination=?