· 5 years ago · Feb 26, 2020, 05:27 AM
1DROP DATABASE IF EXISTS db212lab4;
2create database db212lab4;
3use db212lab4;
4
5DROP TABLE IF EXISTS OrderBook;
6DROP TABLE IF EXISTS Book;
7DROP TABLE IF EXISTS Customer;
8
9
10create table Book(isbn Varchar(12) Primary key, title Varchar(50) NOT NULL, author Varchar(50) NOT NULL, qty_in_stock integer(10) NOT NULL, price Decimal (6,2) NOT NULL, year_published Integer(4));
11insert into Book values("A1234","Data Structures and Algorithms","Cormen",5,50.00, 2007);
12insert into Book values("A1235","Computer Networks","Stallings",7,500.00,2003);
13insert into Book values("A1236","Operating Systems","Stallings",3,800.00,2000);
14insert into Book values("A1237","C","Koffman", 10, 255.00, 2009);
15insert into Book values("A1238","Applied Mathematics","Chandler", 20, 300.00, 1995);
16
17create table Customer(cid Varchar(6) Primary key, cname Varchar(20) NOT NULL , address Varchar(50), age Integer(2));
18insert into Customer values("c1", "Amar"," 23, M.G. road, Ahmadabad", 20);
19insert into Customer values("c2", "Akbar","D-20, Sainivas, Mumbai",19);
20insert into Customer values("c3","Pooja","sector no. 23, Vashi, Mumbai",24);
21insert into Customer values("c4","Saloni","Hyderabad",22);
22insert into Customer values("c5","John","Pune, Shivajinagar",18);
23
24create table OrderBook(oisbn Varchar(12), ocid Varchar(6) NOT NULL, qty Integer(10),orderdate date,
25Foreign key(oisbn) references Book(isbn), Foreign key(ocid) references Customer(cid), primary key(oisbn, orderdate));
26
27insert into OrderBook values("A1234","c2",2,'2013-10-01');
28insert into OrderBook values("A1234","c1",1,'2012-07-02');
29insert into OrderBook values("A1236","c3",2 ,'2013-12-12');
30insert into OrderBook values("A1236","c5",4, '2012-12-30');
31insert into OrderBook values("A1236","c1",5, '2012-05-14');
32insert into OrderBook values("A1238","c4",10, '2012-06-15');
33
343)
35 select ocid from OrderBook where timestampdiff(year,orderdate,curdate())<7;
36 select ocid,timestampdiff(year,orderdate,curdate()) from OrderBook where timestampdiff(year,orderdate,curdate())<7;
374)
38 select ocid from OrderBook as a where EXISTS
39 (select ocid from OrderBook,Book as b where b.title = "Operating Systems" and a.oisbn = b.isbn);
405)
41 select cname from Customer as c where EXISTS
42 (select cname from Customer,OrderBook as o,Book as b where c.cid = o.ocid and b.title="Operating Systems" and b.isbn = o.oisbn) and
43 EXISTS
44 (select cname from Customer,OrderBook as o,Book as b where c.cid = o.ocid and b.title="Data Structures and Algorithms" and b.isbn = o.oisbn);
45
466)
47 select ocid from OrderBook as d where NOT EXISTS
48 (select ocid from OrderBook as o where EXISTS
49 (select ocid from OrderBook, Book as b where isbn = o.oisbn and title = "Operating Systems")
50 and
51 EXISTS
52 (select ocid from OrderBook, Book as b where isbn = o.oisbn and title ="Data Structures and Algorithms")
53 and o.oisbn = d.oisbn
54 );
55
56 select cid from Customer as c where NOT EXISTS
57 (select cid from Customer as d where EXISTS
58 (select cid from Customer,Book,OrderBook where d.cid = ocid and isbn = oisbn and title ="Operating Systems")
59 and
60 EXISTS
61 (select cid from Customer,Book,OrderBook where d.cid = ocid and isbn = oisbn and title ="Data Structures and Algorithms")
62 and d.cid = c.cid
63 );
64
65select ocid from OrderBook as c where EXISTS
66 (select ocid from OrderBook as a,Book where title = "Operating Systems" and a.oisbn = isbn and a.oisbn = c.oisbn )
67 and EXISTS
68 (select ocid from OrderBook as b, Book where title ="Data Structures and Algorithms" and b.oisbn = isbn and b.oisbn = c.oisbn)
69 ;
70
717)
72select distinct ocid from OrderBook as a where NOT EXISTS
73 (select ocid from OrderBook as b where NOT EXISTS
74 (select ocid from OrderBook,Book where b.oisbn = isbn and title = "Data Structures and Algorithms")
75 and NOT EXISTS
76 (select ocid from OrderBook,Book where b.oisbn = isbn and title ="Operating Systems")
77 and
78 a.ocid = b.ocid
79 );
80
818)
82-- select ocid from OrderBook as a where NOT EXISTS
83-- (select ocid from OrderBook as b where NOT EXISTS
84-- (select ocid from OrderBook,Book where b.oisbn = isbn and title = "Data Structures and Algorithms")
85-- and NOT EXISTS
86-- (select ocid from OrderBook,Book where b.oisbn = isbn and title ="Operating Systems")
87-- and
88-- a.ocid = b.ocid
89-- )
90-- and NOT EXISTS
91-- (select ocid from OrderBook as c where EXISTS
92-- (select ocid from OrderBook,Book where c.oisbn = isbn and title ="Data Structures and Algorithms")
93-- and EXISTS
94-- (select ocid from OrderBook,Book where c.oisbn = isbn and title ="Operating Systems")
95-- and
96-- a.ocid = c.ocid
97-- );
98
99-- select distinct ocid from OrderBook where ocid not in
100-- (select ocid from OrderBook,Book where oisbn = isbn and title = "Data Structures and Algorithms")
101-- and ocid in
102-- (select ocid from OrderBook,Book where oisbn = isbn and title ="Operating Systems")
103-- ;
104
105-- select distinct ocid from OrderBook where ocid not in
106-- (select ocid from OrderBook where ocid not in
107-- (select ocid from OrderBook,Book where oisbn = isbn and title ="Data Structures and Algorithms")
108-- and ocid not in
109-- (select ocid from OrderBook,Book where oisbn = isbn and title ="Operating Systems")
110-- );
111 select distinct ocid from OrderBook where ocid not in
112 (select ocid from OrderBook where ocid in
113 (select ocid from OrderBook,Book where oisbn = isbn and title ="Data Structures and Algorithms")
114 and ocid in
115 (select ocid from OrderBook,Book where oisbn = isbn and title ="Operating Systems")
116 )
117 and
118 ocid not in
119 (select ocid from OrderBook where ocid not in
120 (select ocid from OrderBook,Book where oisbn = isbn and title ="Data Structures and Algorithms")
121 and ocid not in
122 (select ocid from OrderBook,Book where oisbn = isbn and title ="Operating Systems")
123 );
124
125 -- select ocid from OrderBook as a where EXISTS
126 -- (select ocid from OrderBook as b where EXISTS
127 -- (select ocid from OrderBook,Book where isbn = b.oisbn and title ="Data Structures and Algorithms")
128 -- and EXISTS
129 -- (select ocid from OrderBook,Book where isbn = b.oisbn and title ="Operating Systems")
130 -- and a.oisbn = b.oisbn
131 -- );
132
1339)
134 select cname from Customer as c,OrderBook where c.cid = ocid group by ocid having sum(qty)<4;
135
13610)
137 -- select isbn,qty_in_stock from Book where qty_in_stock>=all(select qty_in_stock from Book);
138
139 -- select isbn,qty_in_stock from Book where isbn in
140 -- (select isbn from Book where isbn not in
141 -- (select isbn from Book where qty_in_stock>=all(select qty_in_stock from Book))
142 -- and
143 -- qty_in_stock>=all(select qty_in_stock from Book where isbn not in
144 -- (select isbn from Book where qty_in_stock>=all(select qty_in_stock from Book))
145 -- )
146 -- );
147 select isbn,qty_in_stock from Book where isbn not in
148 (select isbn from Book where qty_in_stock>=all(select qty_in_stock from Book))
149 and
150 qty_in_stock>=all(select qty_in_stock from Book where isbn not in
151 (select isbn from Book where qty_in_stock>=all(select qty_in_stock from Book))
152 )
153 ;