Scribd
Upload
9 views

SQL Project

Uploaded by

aqsakhan14251
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views

SQL Project

Uploaded by

aqsakhan14251
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

-- ********************************* Easy ****************************

-- Q1: Who is the senior most employee based on job title?


-- SELECT *
-- FROM employee
-- ORDER BY levels DESC
-- LIMIT 1

-- Q2. Which countries have the most Invoices?


-- SELECT billing_country, COUNT(billing_country) AS invoice_cnt
-- FROM invoice
-- GROUP BY billing_country
-- ORDER BY invoice_cnt DESC

-- Q3. What are top 3 values of total invoice?


-- SELECT total
-- FROM invoice
-- ORDER BY total DESC
-- LIMIT 3

-- Q4. Which city has the best customers? We would like to throw a promotional
Music
-- Festival in the city we made the most money. Write a query that returns one city
that
-- has the highest sum of invoice totals. Return both the city name & sum of all
invoice
-- totals
-- SELECT billing_city, SUM(total) AS total_invoice
-- FROM invoice
-- GROUP BY billing_city
-- ORDER BY total_invoice DESC
-- LIMIT 1

-- Q5. Who is the best customer? The customer who has spent the most money will be
-- declared the best customer. Write a query that returns the person who has spent
the
-- most money
-- SELECT C.first_name, C.last_name, SUM(I.total) AS money_spent
-- FROM customer AS C
-- LEFT JOIN invoice AS I
-- ON C.customer_id = I.customer_id
-- GROUP BY C.customer_id
-- ORDER BY money_spent DESC
-- LIMIT 1

-- ********************************* Medium ****************************


-- Q1. Write query to return the email, first name, last name, & Genre of all Rock
Music
-- listeners. Return your list ordered alphabetically by email starting with A?
-- SELECT C.email, C.first_name, C.last_name, G.name
-- FROM customer AS C
-- LEFT JOIN invoice AS I
-- ON C.customer_id =I.customer_id
-- LEFT JOIN invoice_line AS IL
-- ON I.invoice_id = IL.invoice_id
-- LEFT JOIN track AS T
-- ON IL.track_id = T.track_id
-- LEFT JOIN genre AS G
-- ON G.genre_id = T.genre_id
-- WHERE G.name = 'Rock'
-- ORDER BY email

-- Q2. Let's invite the artists who have written the most rock music in our
dataset. Write a
-- query that returns the Artist name and total track count of the top 10 rock
bands

-- SELECT A1.artist_id, A1.name, COUNT(A1.artist_id) AS track_count


-- FROM track AS T
-- JOIN album AS A2
-- ON A2.album_id = T.album_id
-- JOIN artist AS A1
-- ON A2.artist_id = A2.artist_id
-- JOIN genre AS G
-- ON G.genre_id = T.genre_id
-- WHERE G.name = 'Rock'
-- GROUP BY A1.artist_id
-- ORDER BY track_count DESC
-- LIMIT 10;

-- Q3. Return all the track names that have a song length longer than the average
song length.
-- Return the Name and Milliseconds for each track. Order by the song length with
the
-- longest songs listed first
-- SELECT name, milliseconds
-- FROM track
-- WHERE milliseconds > (
-- SELECT AVG(milliseconds) AS avg_song_length
-- FROM track
-- )
-- ORDER BY milliseconds DESC

-- ************************ Hard ****************************


-- Q1. Find how much amount spent by each customer on artists? Write a query to
return
-- customer name, artist name and total spent
-- WITH artist_CTE AS (
-- SELECT A1.artist_id, A1.name
-- FROM artist AS A1
-- GROUP BY 1
-- )

-- SELECT C.first_name, C.last_name, art.name , SUM(IL.unit_price*IL.quantity) AS


money_spent
-- FROM customer AS C
-- JOIN invoice AS I ON C.customer_id = I.customer_id
-- JOIN invoice_line AS IL ON IL.invoice_id = I.invoice_id
-- JOIN track AS T ON T.track_id = IL.track_id
-- JOIN album AS A2 ON A2.album_id = T.album_id
-- JOIN artist_CTE AS art ON art.artist_id = A2.artist_id
-- GROUP BY 1,2,3
-- ORDER BY 4 DESC

You might also like