Question 1

1.
SELECT COUNT(*)
FROM CUSTOMER, NATION, REGION
WHERE c_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name='EUROPE'
      AND c_acctbal < 8000

2.
SELECT DISTINCT r_name
FROM partsupp,	supplier, nation, region
WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey
GROUP BY r_name
HAVING COUNT(DISTINCT ps_partkey)=(SELECT MAX(AUX.c_part)
				  FROM	(SELECT COUNT(DISTINCT ps_partkey) as c_part
			 	  FROM partsupp,	supplier, nation, region
				  WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey
				  GROUP BY r_name) AUX)

3.
SELECT AVG(DATEDIFF(day,l_shipdate,l_receiptdate)) as avg
FROM LINEITEM
WHERE l_shipmode='TRUCK'

SELECT l_orderkey
FROM LINEITEM
WHERE DATEDIFF(day,l_shipdate,l_receiptdate) > (SELECT AVG(DATEDIFF(day,l_shipdate,l_receiptdate)) as avg
FROM LINEITEM) AND l_shipmode='TRUCK'

4.
SELECT	l_orderkey
FROM	lineitem,orders, customer,nation, region
WHERE	l_orderkey=o_orderkey AND o_custkey=c_custkey AND c_nationkey=n_nationkey
        AND n_regionkey=r_regionkey AND r_name='EUROPE'AND l_discount = 0

5.
SELECT c_NAME
FROM customer
WHERE NOT EXISTS (SELECT *
              FROM ORDERS
              WHERE Customer.c_custkey = Orders.o_custkey)

6.
SELECT distinct l_linenumber
FROM LINEITEM,ORDERS,CUSTOMER,NATION
WHERE l_orderkey=o_orderkey AND o_custkey = c_custkey 
      AND c_nationkey = (SELECT n_nationkey
                         FROM SUPPLIER, NATION
                         WHERE s_nationkey=n_nationkey AND s_acctbal = (SELECT MAX(s_acctbal) FROM SUPPLIER))

7.
SELECT AVG(o_totalprice)
FROM ORDERS
WHERE o_orderkey NOT IN (
                          SELECT o_orderkey
                          FROM ORDERS, LINEITEM, PARTSUPP, PART
                          WHERE p_partkey = ps_partkey AND ps_partkey=l_partkey AND ps_suppkey=l_suppkey AND l_orderkey = o_orderkey
                          AND p_size > 40
                         )

8.
SELECT s_name, COUNT(DISTINCT p_partkey)
FROM PART,PARTSUPP,SUPPLIER
WHERE p_partkey = ps_partkey AND ps_suppkey = s_suppkey
GROUP BY s_name
HAVING COUNT(DISTINCT p_partkey)>5


9.
SELECT DISTINCT p_name
FROM PART,PARTSUPP,LINEITEM
WHERE p_partkey = ps_partkey AND ps_partkey=l_partkey AND ps_suppkey = l_suppkey
      AND l_discount = (SELECT MAX(l_discount) FROM LINEITEM)


Question 2

1.
SELECT Genre.GenreId,  Genre.Name, COUNT(Distinct PlaylistId)
FROM Genre, Track, PlaylistTrack
WHERE Genre.GenreId=Track.GenreID AND Track.TrackId = PlaylistTrack.TrackId
GROUP BY Genre.GenreId,  Genre.Name
HAVING COUNT(Distinct PlaylistId) = (SELECT MAX(aux.count)
                                     FROM (SELECT  COUNT(Distinct PlaylistId) as count
                                           FROM Genre, Track, PlaylistTrack
                                           WHERE Genre.GenreId=Track.GenreID AND Track.TrackId = PlaylistTrack.TrackId
                                           GROUP BY Genre.GenreId,  Genre.Name   ) aux)

2.
SELECT Track.Name
FROM Track,MediaType
WHERE Track.MediaTypeId = MediaType.MediaTypeId AND MediaType.Name LIKE '%audio%'              
                                                AND Track.Milliseconds > (SELECT AVG(Milliseconds) 
                                                                        FROM Track,MediaType
                                                                        WHERE Track.MediaTypeId=MediaType.MediaTypeId AND MediaType.Name LIKE '%audio%')

3.
SELECT P.PlaylistId, P.Name
FROM Playlist as P, PlaylistTrack as PL, Track, MediaType, Genre
WHERE MediaType.Name LIKE '%audio%' AND P.PlaylistId = PL.PlaylistId 
      AND PL.TrackId = Track.TrackId
      AND Track.MediaTypeId = MediaType.MediaTypeId
      AND Track.GenreId = Genre.GenreID AND Genre.Name='Pop'
GROUP BY P.PlaylistId, P.Name
HAVING COUNT(*) =  (SELECT MAX(aux.count)
                   FROM (SELECT COUNT(*) as count
                         FROM Playlist as P, PlaylistTrack as PL, Track, MediaType, Genre
                         WHERE MediaType.Name LIKE '%audio%' AND P.PlaylistId = PL.PlaylistId 
                         AND PL.TrackId = Track.TrackId
                         AND Track.MediaTypeId = MediaType.MediaTypeId
                         AND Track.GenreId = Genre.GenreID AND Genre.Name='Pop'
                         GROUP BY P.PlaylistId      ) aux)

4.
SELECT CustomerId,COUNT(EmployeeId)
FROM Employee RIGHT OUTER JOIN Customer ON Employee.City = Customer.City AND Employee.State = Customer.State and Employee.Country = Customer.Country
GROUP BY CustomerId
ORDER BY COUNT(EmployeeId) DESC

5.
SELECT Artist.Name
FROM Artist, Album, Track, Genre
WHERE Artist.ArtistID=Album.ArtistId AND Album.AlbumId = Track.AlbumId AND Track.GenreId = Genre.GenreId
      AND Genre.Name = 'JAZZ'
GROUP BY Artist.Name
HAVING COUNT(*) =  (SELECT max(aux.count)
                   FROM (SELECT COUNT(*) as count
                         FROM Artist, Album, Track, Genre
                         WHERE Artist.ArtistID=Album.ArtistId AND Album.AlbumId = Track.AlbumId AND Track.GenreId = Genre.GenreId
                         AND Genre.Name = 'JAZZ'
                         GROUP BY Artist.Name) aux )

6.
SELECT  FirstName, LastName
FROM Customer AS C, Invoice
WHERE C.CustomerId= Invoice.CustomerId AND Country='Germany' AND Company is NULL
GROUP BY C.CustomerId, FirstName, LastName
HAVING SUM(Total) =  (SELECT Max(AUX.sum)
                     FROM (SELECT SUM(Total) as sum
                           FROM Customer AS C, Invoice
                           WHERE C.CustomerId= Invoice.CustomerId AND Country='Germany' AND Company is NULL
                           GROUP BY C.CustomerId, FirstName, LastName) AUX)

7.
SELECT Employee.LastName, Employee.FirstName, CAST(DATEDIFF(hh, Employee.BirthDate, GETDATE()) / 8766 AS int)
FROM Employee, Customer
WHERE EmployeeId = SupportRepId
GROUP BY EmployeeId, Employee.LastName, Employee.FirstName, Employee.BirthDate
HAVING COUNT(*) > 5

8.
SELECT E2.LastName, E2.FirstName
FROM Employee AS E1,Employee AS E2
WHERE E1.ReportsTo = E2.EmployeeID AND E2.ReportsTo IS NULL
GROUP BY E2.EmployeeID, E2.LastName, E2.FirstName
HAVING COUNT(*) = (SELECT MAX(AUX.count)
                   FROM (SELECT COUNT(*) as count
                         FROM Employee AS E1,Employee AS E2
                         WHERE E1.ReportsTo = E2.EmployeeID AND E2.ReportsTo IS NULL
                         GROUP BY E2.EmployeeID) AUX)


9.
SELECT Artist.Name, COUNT(*) as countTrack
FROM Track,Album,Artist
WHERE Album.AlbumId = Track.Albumid AND Artist.ArtistId = Album.ArtistId 
GROUP BY Artist.ArtistId,Artist.Name
HAVING COUNT(*) > 5

10.

SELECT P.Name, COUNT(*) as count
FROM Playlist AS P, PlaylistTrack AS PL,Track,Album,Artist
WHERE P.PlaylistId = PL.PlaylistId AND PL.TrackId = Track.TrackId AND Album.AlbumId = Track.Albumid AND Artist.ArtistId = Album.ArtistId AND Artist.Name='AC/DC'
GROUP BY P.PlaylistId, P.Name
HAVING COUNT(*) =   (SELECT MAX(AUX.count)
                    FROM (SELECT COUNT(*) as count
                    FROM Playlist AS P, PlaylistTrack AS PL,Track,Album,Artist
                    WHERE P.PlaylistId = PL.PlaylistId AND PL.TrackId = Track.TrackId AND Album.AlbumId = Track.Albumid AND Artist.ArtistId = Album.ArtistId AND Artist.Name='AC/DC'
                    GROUP BY P.PlaylistId) AUX)