· 6 years ago · May 07, 2019, 05:10 PM
1CREATING TABLES
2
3create table if not exists Customer(
4 LoginName varchar(50),
5 CPassword varchar(50),
6 CName varchar(30),
7 CType varchar(30),
8 BillingAddress varchar(50),
9 Str1 varchar(25),
10 Str2 varchar(25),
11 City varchar(25),
12 State varchar(75),
13 Zip varchar(5),
14
15 constraint CustomerPK primary key (LoginName)
16);
17
18create table if not exists Transactionn(
19 TransID int not null auto_increment,
20 TDate date,
21 CardNo varchar(20),
22 CardType varchar(20),
23 CardExpDate varchar(5),
24 TotalAmount double,
25 LoginName varchar(50),
26
27 constraint TransactionnPK primary key (TransID),
28 contstraint TransactionnFK foreign key (LoginName) references Customer(LoginName)
29
30);
31
32create table if not exists Photographer(
33 PName varchar(20),
34 PBDate date,
35 PBio varchar(100),
36 PAddress varchar(50),
37 PColor varchar(10),
38 PNationality varchar(50),
39
40 constraint PhotographerPK primary key (PName, PBDate)
41
42);
43
44create table if not exists Influences(
45 EPName varchar(20),
46 EPBDate date,
47 RPName varchar(20),
48 RPBDate date,
49
50 constraint InfluencesPK primary key (EPName, EPBDate, RPName, RPBDate),
51 constraint InfluencesFK1 foreign key (EPName, EPBDate) references Photographer(PName, PBDate),
52 constraint InfluencesFK2 foreign key (RPName, RPBDate) references Photographer(PName, PBDate)
53
54);
55
56create table if not exists Photo(
57 PhotoID int not null auto_increment,
58 Speed varchar(20),
59 Film varchar(50),
60 FStop varchar(50),
61 Color varchar(20),
62 Resolution varchar(20),
63 Price int,
64 dateTaken date,
65 TransID int,
66 PName varchar(20),
67 PBDate date,
68
69 constraint PhotoPK primary key (PhotoID),
70 constraint PhotoFKTrans foreign key (TransID) references Transactionn(TransID),
71 constraint PhotoFkPhotograhper foreign key (PName, PBDate) references Photographer(PName,PBDate)
72
73)
74
75create table if not exists Location(
76 Place varchar(25),
77 Country varchar(25),
78 Description varchar(150),
79
80 constraint LocationPK primary key (Place, Country)
81)
82
83create table if not exists Landscape(
84 PhotoID int not null,
85 Place varchar(25),
86 Country varchar(25),
87
88 constraint LandScapePK primary key (PhotoID),
89 constraint LandScapeFKPhoto foreign key(PhotoID) references Photo(PhotoID),
90 constraint LandScapeFKLocation foreign key(Place, Country) references Location(Place, Country)
91
92)
93
94create table if not exists Model(
95 MName varchar(20),
96 MBDate date,
97 MBio varchar(100),
98 MSex varchar(10),
99
100 constraint ModelPK primary key (Mname, MBDate)
101
102);
103
104create table if not exists Models(
105 PhotoID int,
106 MName varchar(20),
107 MBDate date,
108 Agency varchar(15),
109
110 constraint ModelsPK primary key (PhotoID, Mname, MBDate),
111 constraint ModelsFKPhoto foreign key (PhotoID) references Photo(PhotoID),
112 constraint ModelsFKModel foreign key (MName, MBDate) references Model(MName, MBDate)
113
114)
115
116create table if not exists Abstract(
117 PhotoID int,
118 abstractComment varchar(100),
119
120 constraint abstractPK primary key (PhotoID),
121 constraint abstractFK foreign key (PhotoID) references Photo(PhotoID)
122
123)
124
125INSERT INTO `Customer` (`LoginName`, `CPassword`, `CName`, `CType`, `BillingAddress`, `Str1`, `Str2`, `City`, `State`, `Zip`)
126VALUES ('SamiraMatilda', 'password', 'Matilda', 'Discover', '7188 S. Kirkland Drive, New Hyde Park, NY 11040', '7188 S. Kirkland Drive', NULL, 'New Hyde Park', 'NY', '11040');
127
128INSERT INTO `Customer` (`LoginName`, `CPassword`, `CName`, `CType`, `BillingAddress`, `Str1`, `Str2`, `City`, `State`, `Zip`)
129VALUES ('AnzoMartina', 'password', 'Anzo', 'Mastercard', '83 Carriage St. Hillsboro, OR 97124', '83 Carriage St. ', NULL, 'Hillsboro', 'OR', '97124');
130
131INSERT INTO `Customer` (`LoginName`, `CPassword`, `CName`, `CType`, `BillingAddress`, `Str1`, `Str2`, `City`, `State`, `Zip`)
132VALUES ('CarlosImrus', 'password', 'Carlos', 'Mastercard', '9543 Ashley Dr. Alabaster, AL 35007', '9543 Ashley Dr.', NULL, 'Alabaster', 'AL', '35007');
133
134INSERT INTO `Transactionn` (`TransID`, `TDate`, `CardNo`, `CardType`, `CardExpDate`, `TotalAmount`, `LoginName`)
135VALUES (NULL, '2019-04-01', '4916959515373746', 'visa', '09/23', '113.00', 'AnzoMartina');
136
137INSERT INTO `Transactionn` (`TransID`, `TDate`, `CardNo`, `CardType`, `CardExpDate`, `TotalAmount`, `LoginName`)
138VALUES (NULL, '2019-04-03', '5289648693845130', 'mastercard', '07/21', '22.00', 'SamiraMatilda');
139
140INSERT INTO `Transactionn` (`TransID`, `TDate`, `CardNo`, `CardType`, `CardExpDate`, `TotalAmount`, `LoginName`)
141VALUES (NULL, '2019-04-22', '6011691142230176', 'discover', '01/20', '101', 'CarlosImrus')
142
143INSERT INTO `Photographer` (`PName`, `PBDate`, `PBio`, `PAddress`, `PColor`, `PNationality`)
144VALUES ('Gaspard Antonios', '1976-04-10', 'Baseball fan, vegan, music blogger, Mad Men fan and communicator, collector, connector, creator. ', '106 Trenton St. Rahway, NJ 07065', 'Color', 'American');
145
146INSERT INTO `Photographer` (`PName`, `PBDate`, `PBio`, `PAddress`, `PColor`, `PNationality`)
147VALUES ('LyudmilaLonnie', '1995-02-01', 'Snowboarder, shiba-inu lover, music blogger, reclaimed wood collector and screen printer. ', '306 Sunset Ave. Detroit, MI 48205', 'B&W', 'Danish');
148
149INSERT INTO `Photographer` (`PName`, `PBDate`, `PBio`, `PAddress`, `PColor`, `PNationality`)
150VALUES ('Sameera Yakubu', '1993-11-06', 'Doing at the crossroads of beauty and sustainability to express ideas through design. ', '857 Liberty Ave. Barberton, OH 44203', 'Color', 'Japanese')
151
152INSERT INTO `Influences` (`EPName`, `EPBDate`, `RPName`, `RPBDate`)
153VALUES ('Gaspard Antonios', '1976-04-10', 'LyudmilaLonnie', '1995-02-01');
154
155INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
156VALUES (NULL, '1/1000', 'Kodak Ektar 100', 'f/3.5', 'Color', '1280', '36.0', '2019-04-08', '1', 'Gaspard Antonios', '1976-04-10');
157
158INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
159VALUES (NULL, '1/100', 'Kodak Portra 400', 'f/5', 'Color', '1920', '65', '2019-04-12', '1', 'Gaspard Antonios', '1976-04-10');
160
161INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
162VALUES (NULL, '1/500', 'Fujifilm Fujicolor Superia 1600', 'f/2', 'B&W', '1600', '22', '2019-04-01', '2', 'LyudmilaLonnie', '1995-02-01');
163
164INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
165VALUES (NULL, '1/800', 'Kodak Gold 200', 'f/10', 'Color', '1920', '61', '2019-04-29', '3', 'Sameera Yakubu', '1993-11-06');
166
167INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
168VALUES (NULL, '1/10', 'Fuji Pro 400H', 'f/5.6', 'Color', '1920', '51', '2019-04-21', '3', 'Sameera Yakubu', '1993-11-06');
169
170INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
171VALUES (NULL, '1/450', 'Fujifilm Fujicolor Superia 1600', 'f/7.8', 'B&W', '1920', '150', '2019-04-20', NULL, NULL, NULL);
172
173INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
174VALUES (NULL, '1/85', 'Off Brand', NULL, 'B&W', '1920', '8', '2019-04-12', NULL, NULL, NULL);
175
176INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
177VALUES (NULL, '1/5', 'Kodak Ektar 100', 'f/1000', 'Color', '1920', '69', '2019-04-08', NULL, NULL, NULL);
178
179INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
180VALUES (NULL, '1/60', 'Fuji Pro 400H', 'f/3.5', 'Color', '1920', '30', '2019-04-11', NULL, NULL, NULL);
181
182INSERT INTO `Photo` (`PhotoID`, `Speed`, `Film`, `FStop`, `Color`, `Resolution`, `Price`, `dateTaken`, `TransID`, `PName`, `PBDate`)
183VALUES (NULL, '1/100', 'Supreme', 'f/6.5', 'Color', '1920', '100', '2019-05-08', NULL, 'Bob Marley', '1993-11-06');
184
185INSERT INTO `Models` (`PhotoID`, `MName`, `MBDate`, `Agency`)
186VALUES ('11', 'Adriana Lima', '1981-06-12', 'LA Models');
187
188INSERT INTO `Model` (`MName`, `MBDate`, `MBio`, `MSex`)
189VALUES ('Miranda Kerr', '1983-04-20', 'My 60 year old son rates this shot very sleek, friend.', 'Female');
190
191INSERT INTO `Model` (`MName`, `MBDate`, `MBio`, `MSex`)
192VALUES ('Adriana Lima', '1981-06-12', 'Such colour, many lines, so sleek', 'Female');
193
194INSERT INTO `Model` (`MName`, `MBDate`, `MBio`, `MSex`)
195VALUES ('Heidi Klum', '1973-06-01', 'Strong. Adore the use of background image and fold!', 'Female');
196
197INSERT INTO `Abstract` (`PhotoID`, `abstractComment`)
198VALUES ('5', 'This is magical work :)');
199
200INSERT INTO `Abstract` (`PhotoID`, `abstractComment`)
201VALUES ('1', 'Killer. So amazing.');
202
203INSERT INTO `Abstract` (`PhotoID`, `abstractComment`)
204VALUES ('8', 'Mmh wondering if this comment will hit the generateor as well...');
205
206INSERT INTO `Abstract` (`PhotoID`, `abstractComment`)
207VALUES ('2', 'Mission accomplished. It\'s splendid.');
208
209INSERT INTO `Location` (`Place`, `Country`, `Description`)
210VALUES ('Lions Head', 'South Africa', 'Style, background, colour palette, shot – excellent!!');
211INSERT INTO `Landscape` (`PhotoID`, `Place`, `Country`)
212VALUES ('4', 'Lions Head', 'South Africa'), ('6', 'Niagara Falls ', 'Canada');
213
214INSERT INTO `Location` (`Place`, `Country`, `Description`)
215VALUES ('Niagara Falls ', 'Canada', 'Beautiful water falls');
216
217INSERT INTO `Landscape` (`PhotoID`, `Place`, `Country`)
218VALUES ('9', 'Lions Head', 'South Africa');
219
2201)
221select t.LoginName
222from transaction t
223where t.TotalAmount > 100
224
225
2262)
227select p.PhotoID
228from Photo p
229where p.TransID is null
230
2313)
232Select t.LoginName
233FROM Transactionn t
234where t.TransID =
235 (select p.TransID
236 from Photo p, Models m
237 where p.PhotoID = m.PhotoID and m.MName = "Heidi Klum")
238
2394)
240SELECT i.RPName
241FROM Influences i
242WHERE i.EPName = (Select p.PName
243 FROM Photographer p
244 WHERE p.PNationality = 'American')
245
2465)
247CREATE VIEW R1 AS
248SELECT DISTINCT p.PName
249FROM Photo p, Abstract a, Landscape l
250WHERE p.PhotoID = a.PhotoID or p.photoID = l.PhotoID
251
252CREATE VIEW R2 AS
253SELECT DISTINCT p.PName
254FROM Photo p, Models m
255WHERE p.PhotoID = m.PhotoID
256
257SELECT PName
258FROM R2
259WHERE PName NOT IN (SELECT PNAME FROM R1)
260
2616)
262Select p.transID
263FROM Photo p
264Group by p.TransID
265having Count(TransID) > 3;
266
2677)
268SELECT m.MName
269FROM Models m, Photo p
270WHERE m.PhotoID = p.PhotoID and p.PName = 'Bob Marley'
271
272
2738)
274SELECT pg.PName, SUM(Price) AS SumPrice
275FROM Photographer PG, Photo p
276WHERE pg.PName = P.PName AND pg.PBDate = P.PBDate
277GROUP BY PName
278ORDER BY SUM(PRICE) Desc
279
2809)
281DELETE FROM Photo
282Where PhotoID = 'X'
283
28410)
285UPDATE Photo
286SET PName = 'Bob Marley' PBDate = '1993-11-06'
287WHERE photoID = 'X'
288
28911)
290SELECT c.CName, SUM(t.TotalAmount)
291From Transactionn t, Customer c
292Where c.LoginName = t.LoginName
293Group by t.TransID
294
29512)
296SELECT p.PName, SUM(p.Price)
297FROM Photo p
298WHERE p.TransID is not null
299GROUP BY p.PName
300
30113)
302SELECT l.PhotoID, SUM(p.Price)
303FROM Landscape l, Photo p
304WHERE p.PhotoID = l.PhotoID
305GROUP BY p.PhotoID
306UNION
307SELECT m.PhotoID, SUM(p.Price)
308FROM Models m, Photo p
309WHERE p.PhotoID = m.PhotoID
310GROUP BY p.PhotoID
311UNION
312SELECT a.PhotoID, SUM(p.Price)
313FROM Abstract a, Photo p
314WHERE p.PhotoID = a.PhotoID
315GROUP BY p.PhotoID