dfg

/* EFRAS SOPUTAN FINAL EXAM IT775 FALL 2019*/

/* a)   Insert 'Anna Kendrick' and 'Owl City' in the Artists table.  In a follow-up query, report the ArtistId assigned to each of these two new artists. */

INSERT INTO artist (Name, ArtistId)
VALUES
('276','Anna Kendrick'),
('277','Owl City');

SELECT name, artistid FROM artist
WHERE name = 'Anna Kendrick' OR name = 'Owl City';

/* b)   Find each artist's sales to Norwegians.  If no sales were made to Norwegians by an artist, that artist need not be reported.  (Output should have only the ArtistName and Total Sales.) */


/* c)   Generate a report summarizing the number of tracks in each genre and provide a table with two columns--the number of tracks and genre name. */

SELECT
genre.name,
COUNT(*) AS '# of tracks' FROM genre
INNER JOIN track ON track.genreid = genre.genreid
GROUP BY genre.name;

/* d)   Which audio track has the longest length? */

SELECT name, MAX(milliseconds) FROM track;


/* e)   What is the space, in bytes, occupied by the playlist "Classical," and how much would it cost?  (Assume that the cost of a playlist is the sum of the price of its constituent tracks.) */

SELECT
    playlist.name,
    SUM(track.bytes) AS 'Space',
    SUM(track.unitprice) AS 'Cost' FROM playlist
INNER JOIN playlisttrack ON playlisttrack.playlistid =  playlist.playlistid
INNER JOIN track ON track.trackid = playlisttrack.trackid
WHERE playlist.name = 'classical';


/* f)   Find the first and last names of the employees who are older than their corresponding supervisor.  (Output should have only the FirstName and LastName in the table.) */


/* g)   Create a table called Bonuses to store employee bonuses when tracks are sold to customers.  The table should have three columns (EmployeeId, InvoiceId, and BonusAmt).  Ensure that the table created will comply with the existing schema. */



/* h)   How many tracks exist where at least one of the composers has the name "John"?  (Note that "Johnson" is not a valid result--"John" must be the first, middle or last name of the composer.) */

SELECT COUNT(NAME) FROM track
WHERE composer LIKE '%john %'


/* i)   Create a view named Sales to obtain the album names along with the sales amounts.  (Output should have two columns—AlbumName and TotalSales.) */


/* j)   Report the media type that is most favored by customers through their purchases.  (Output should be MediaType.Name.) */