· 6 years ago · Dec 06, 2019, 02:40 PM
1const name = 'Louis Lacoste';
2const promo = "B2B";
3
4const q1 = `
5SELECT *
6 FROM Track
7 WHERE Milliseconds <
8 (
9 SELECT Milliseconds
10 FROM Track
11 WHERE TrackId = '3457'
12 );`;
13
14const q2 = `
15SELECT *
16FROM Track
17WHERE MediaTypeId =
18(
19SELECT MediaTypeId
20FROM Track
21WHERE Name = 'Rehab'
22);`;
23
24const q3 = `
25SELECT pl.PlaylistId, pl.Name AS 'Playlist Name', COUNT(track.TrackId) AS 'Tracks Number', SUM(track.Milliseconds)/1000 AS 'Playlist Duration(seconds)',
26AVG(track.Milliseconds)/1000 AS 'Average Duration Per Track(seconds)'
27 FROM Playlist AS pl
28 JOIN PlaylistTrack AS ptk
29 ON pl.PlaylistId = ptk.PlaylistId
30 JOIN Track as track
31 ON track.TrackId = ptk.TrackId
32 GROUP BY pl.PlaylistId, pl.Name;`;
33
34const q4 = `
35SELECT plr.PlaylistId, plr.[Playlist Name]
36 FROM (SELECT pl.PlaylistId, pl.Name AS 'Playlist Name', SUM(track.Milliseconds)/1000 AS PlaylistDuration
37 FROM Playlist AS pl
38 JOIN PlaylistTrack AS ptk
39 ON pl.PlaylistId = ptk.PlaylistId
40 JOIN Track as track
41 ON track.TrackId = ptk.TrackId
42 GROUP BY pl.PlaylistId, pl.Name)
43 AS plr
44WHERE plr.PlaylistDuration > (SELECT AVG(PlaylistDuration) AS PlaylistAverage
45FROM
46(SELECT pl.PlaylistId, pl.Name AS 'Playlist Name', SUM(track.Milliseconds)/1000 AS PlaylistDuration
47 FROM Playlist AS pl
48 JOIN PlaylistTrack AS ptk
49 ON pl.PlaylistId = ptk.PlaylistId
50 JOIN Track as track
51 ON track.TrackId = ptk.TrackId
52 GROUP BY pl.PlaylistId, pl.Name)
53 AS Result);`;
54
55const q5 = `
56SELECT *
57FROM
58(SELECT pl.PlaylistId, pl.Name AS 'Playlist Name', COUNT(track.TrackId) AS TracksCount
59 FROM Playlist AS pl
60 JOIN PlaylistTrack AS ptk
61 ON pl.PlaylistId = ptk.PlaylistId
62 JOIN Track as track
63 ON track.TrackId = ptk.TrackId
64 GROUP BY pl.PlaylistId, pl.Name)
65 AS tk
66 WHERE tk.TracksCount =
67 (SELECT COUNT(track.TrackId)
68 FROM Playlist AS pl
69 JOIN PlaylistTrack AS ptk
70 ON pl.PlaylistId = ptk.PlaylistId
71 JOIN Track as track
72 ON track.TrackId = ptk.TrackId
73 WHERE pl.PlaylistId = 1
74 GROUP BY pl.PlaylistId, pl.Name)
75 OR
76 tk.TracksCount =
77 (SELECT COUNT(track.TrackId)
78 FROM Playlist AS pl
79 JOIN PlaylistTrack AS ptk
80 ON pl.PlaylistId = ptk.PlaylistId
81 JOIN Track as track
82 ON track.TrackId = ptk.TrackId
83 WHERE pl.PlaylistId = 13
84 GROUP BY pl.PlaylistId, pl.Name)`;
85
86const q6 = `
87SELECT CONCAT(customer.FirstName, ' ', customer.LastName) AS 'Customer Name', MAX(invoice.Total) AS 'Max Invoice'
88FROM Customer AS customer
89JOIN Invoice AS invoice
90ON invoice.CustomerId = customer.CustomerId
91WHERE invoice.Total >
92(SELECT TOP 1 MAX(invoice.Total) AS Total
93FROM Customer AS customer
94JOIN Invoice AS invoice
95ON invoice.CustomerId = customer.CustomerId
96WHERE invoice.BillingCountry = 'France'
97GROUP BY customer.FirstName, customer.LastName
98ORDER BY Total DESC)
99GROUP BY customer.FirstName, customer.LastName`;
100
101const q7 = `
102SELECT invoice.BillingCountry, MAX(invoice.Total) AS MAX, MIN(invoice.Total) AS MIN, AVG(invoice.Total) AS AVG, COUNT(invoice.Total) AS COUNT
103FROM Invoice as invoice
104GROUP BY invoice.BillingCountry;`;
105
106const q8 = `
107SELECT track.Name, track.UnitPrice, media.Name
108FROM Track as track
109JOIN MediaType as media
110ON track.MediaTypeId = media.MediaTypeId
111WHERE UnitPrice >
112(SELECT AVG(UnitPrice)
113FROM Track)
114GROUP BY track.Name, track.UnitPrice, media.Name`;
115
116const q9 = `
117SELECT track.Name
118FROM Track AS track
119JOIN
120(SELECT genre.GenreId, AVG(track.UnitPrice) AS 'GenreAVGTrackPrice'
121FROM Track AS track
122JOIN Genre AS genre
123ON genre.GenreId = track.GenreId
124GROUP BY genre.GenreId)
125AS genreavg
126ON track.GenreId = genreavg.GenreId
127WHERE track.UnitPrice < genreavg.GenreAVGTrackPrice`;
128
129const q10 = `
130SELECT Pays, COUNT(*)
131FROM
132(SELECT BillingCountry as Pays
133FROM Invoice
134UNION ALL
135SELECT Country as Pays
136FROM Customer
137UNION ALL
138SELECT Country as Pays
139FROM Employee) as result
140GROUP BY Pays
141ORDER BY Pays`;
142
143const q11 = `
144SELECT Country, COUNT(Country) AS CountryCount
145FROM Customer
146GROUP BY Country
147UNION ALL
148SELECT BillingCountry, COUNT(BillingCountry) AS CountryCount
149FROM Invoice
150GROUP BY BillingCountry
151UNION ALL
152SELECT Country, COUNT(Country) AS CountryCount
153FROM Employee
154GROUP BY Country`;
155
156const q12 = `
157SELECT result.Pays, result.Total, result.Employee, result.Customer, COUNT(inv.InvoiceId) as Invoice
158FROM
159(SELECT result.Pays, result.Total, result.Employee, COUNT(c.CustomerId) as Customer
160FROM
161(SELECT result.Pays, result.Total, COUNT(emp.EmployeeId) as Employee
162FROM
163(SELECT Pays, COUNT(*) as Total
164FROM
165(SELECT BillingCountry as Pays
166FROM Invoice
167UNION ALL
168SELECT Country as Pays
169FROM Customer
170UNION ALL
171SELECT Country as Pays
172FROM Employee) as result
173GROUP BY Pays) as result
174LEFT JOIN Employee as emp
175ON result.Pays = emp.Country
176GROUP BY result.Pays, result.Total) as result
177JOIN Customer as c
178ON result.Pays = c.Country
179GROUP BY result.Pays, result.Total, result.Employee) as result
180JOIN Invoice as inv
181ON result.Pays = inv.BillingCountry
182GROUP BY result.Pays, result.Total, result.Employee, result.Customer
183ORDER BY result.Pays`;
184
185const q13 = `
186SELECT inv.InvoiceId, track.Milliseconds
187FROM Invoice as inv
188JOIN InvoiceLine as invl
189ON inv.InvoiceId = invl.InvoiceId
190JOIN Track as track
191ON invl.TrackId = track.TrackId
192WHERE track.Milliseconds IN
193(SELECT MAX(track.Milliseconds)
194FROM Track as track
195JOIN Genre as genre
196ON track.GenreId = genre.GenreId
197GROUP BY genre.Name)
198ORDER BY inv.InvoiceId`;
199
200const q14 = `
201SELECT inv.InvoiceId, AVG(track.UnitPrice) as TrackAvgPrice, SUM(track.Milliseconds)/1000 as 'Total Command Duration (s)', AVG(track.UnitPrice)/(SUM(track.Milliseconds)/1000) as 'Command Price Per Second Of Track'
202FROM Invoice as inv
203JOIN InvoiceLine as invl
204ON inv.InvoiceId = invl.InvoiceId
205JOIN Track as track
206ON invl.TrackId = track.TrackId
207GROUP BY inv.InvoiceId
208ORDER BY inv.InvoiceId`;
209
210const q15 = ``;
211
212const q16 = `
213SELECT TOP 1 *
214FROM
215(SELECT CONCAT(emp.FirstName, ' ', emp.LastName) as Name, COUNT(c.CustomerId) as Total
216FROM Employee as emp
217FULL JOIN Customer as c
218ON emp.EmployeeId = c.SupportRepId
219GROUP BY emp.FirstName, emp.LastName) as result
220WHERE result.Total <> 0`;
221
222const q17 = ``;
223
224const q18 = `
225/*******************************************************************************
226 Drop database if it exists
227********************************************************************************/
228IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'PartieDeux')
229BEGIN
230 ALTER DATABASE [PartieDeux] SET OFFLINE WITH ROLLBACK IMMEDIATE;
231 ALTER DATABASE [PartieDeux] SET ONLINE;
232 DROP DATABASE [PartieDeux];
233END
234
235GO
236
237/*******************************************************************************
238 Create database
239********************************************************************************/
240CREATE DATABASE [PartieDeux];
241GO
242
243USE [PartieDeux];
244GO
245
246/*******************************************************************************
247 Create Tables
248********************************************************************************/
249CREATE TABLE [dbo].[Group]
250(
251 [id] INT IDENTITY PRIMARY KEY,
252 [name] VARCHAR(255) NOT NULL,
253 [display_name] VARCHAR(255) NOT NULL,
254 [description] TEXT NOT NULL,
255);
256GO
257
258CREATE TABLE [dbo].[Role]
259(
260 [id] INT IDENTITY PRIMARY KEY,
261 [name] VARCHAR(255) NOT NULL,
262 [display_name] VARCHAR(255) NOT NULL,
263 [description] TEXT NOT NULL,
264);
265GO
266
267CREATE TABLE [dbo].[Permission]
268(
269 [id] INT IDENTITY PRIMARY KEY,
270 [name] VARCHAR(255) NOT NULL,
271 [display_name] VARCHAR(255) NOT NULL,
272 [description] TEXT NOT NULL,
273);
274GO
275
276CREATE TABLE [dbo].[User]
277(
278 [id] INT IDENTITY PRIMARY KEY,
279 [username] VARCHAR(255) NOT NULL,
280 [email] VARCHAR(255) NOT NULL,
281 [superuser] BIT NOT NULL,
282);
283GO
284
285CREATE TABLE [dbo].[User_Group]
286(
287 [user_id] INT NOT NULL,
288 [group_id] INT NOT NULL,
289 CONSTRAINT fk_user_group_id FOREIGN KEY ([user_id])
290 REFERENCES [dbo].[User](id),
291 CONSTRAINT fk_group_user_id FOREIGN KEY ([group_id])
292 REFERENCES [dbo].[Group](id),
293);
294GO
295
296CREATE TABLE [dbo].[Group_Role]
297(
298 [group_id] INT NOT NULL,
299 [role_id] INT NOT NULL,
300 CONSTRAINT fk_group_role_id FOREIGN KEY ([group_id])
301 REFERENCES [dbo].[Group](id),
302 CONSTRAINT fk_role_group_id FOREIGN KEY ([role_id])
303 REFERENCES [dbo].[Role](id),
304);
305GO
306
307CREATE TABLE [dbo].[Role_Permission]
308(
309 [user_id] INT NOT NULL,
310 [permission_id] INT NOT NULL,
311 CONSTRAINT fk_user_permission_id FOREIGN KEY ([user_id])
312 REFERENCES [dbo].[User](id),
313 CONSTRAINT fk_permission_user_id FOREIGN KEY ([permission_id])
314 REFERENCES [dbo].[Permission](id)
315);
316GO
317
318CREATE TABLE [dbo].[User_Role]
319(
320 [user_id] INT NOT NULL,
321 [role_id] INT NOT NULL,
322 CONSTRAINT fk_user_role_id FOREIGN KEY ([user_id])
323 REFERENCES [dbo].[User](id),
324 CONSTRAINT fk_role_user_id FOREIGN KEY ([role_id])
325 REFERENCES [dbo].[Role](id),
326);
327GO
328`;
329
330const q19 = `
331USE [Chinook];
332GO
333
334INSERT INTO [dbo].[Artist] ([Name]) VALUES (N'[BDS.U]');
335INSERT INTO [dbo].[Genre] ([Name]) VALUES (N'Lofi');
336INSERT INTO [dbo].[Album] ([Title], [ArtistId]) VALUES (N'Lighter', 276);
337
338INSERT INTO [dbo].[Track] ([Name], [AlbumId], [MediaTypeId], [GenreId], [Composer], [Milliseconds], [Bytes], [UnitPrice]) VALUES (N'french inhale', 348, 1, 26, N'[BSD.U]', 108000, 3539131, 0.99);
339INSERT INTO [dbo].[Track] ([Name], [AlbumId], [MediaTypeId], [GenreId], [Composer], [Milliseconds], [Bytes], [UnitPrice]) VALUES (N'memories of you', 348, 1, 26, N'[BSD.U]', 96000, 3145894, 0.99);
340INSERT INTO [dbo].[Track] ([Name], [AlbumId], [MediaTypeId], [GenreId], [Composer], [Milliseconds], [Bytes], [UnitPrice]) VALUES (N'regret', 348, 1, 26, N'[BSD.U]', 80000, 2621578, 0.99);
341GO`;
342
343const q20 = `
344USE [Chinook];
345GO
346
347INSERT INTO [dbo].[Employee] ([LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (N'Thierry', N'Thomas', N'Sales Support Agent', 2, '20/9/19', '2016/5/3', N'24 Avenue des bons enfants', N'Créon', N'FR', N'France', N'33800', N'(33+)6 23 06 81 50', N'+1 (403) 467-8772', N'th.thomas@chinookcorp.com');
348INSERT INTO [dbo].[Employee] ([LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (N'Bernard', N'Marc', N'Sales Support Agent', 2, '1999/11/6', '2014/5/3', N'13 Rue Pierre Benauges', N'Eysine', N'FR', N'France', N'33400', N'(33+)6 80 60 80 60', N'+1 (403) 262-3322', N'be.marc@chinookcorp.com');
349GO`;
350
351const q21 = `
352DELETE FROM Invoice
353WHERE InvoiceDate >= '2010' AND InvoiceDate < '2011'
354GO`;
355
356const q22 = ``;
357
358const q23 = `
359UPDATE Invoice
360SET BillingCountry = c.Country
361FROM Invoice inv
362FULL JOIN Customer c
363ON c.CustomerId = inv.CustomerId
364WHERE inv.BillingCountry <> c.Country
365GO`;
366
367const q24 = `
368ALTER TABLE [dbo].[Employee]
369ADD [Salary] INT;
370GO`;
371
372const q25 = `
373UPDATE [Employee]
374SET Salary = RAND(CHECKSUM(NEWID()))*(100000-30000)+30000
375GO`;
376
377const q26 = `
378ALTER TABLE [dbo].[Invoice]
379 DROP COLUMN [BillingPostalCode]
380GO`;