Complex Retrieval Queries with SQL

Mekki MOURADI

Data Analyst

Rabat, Maroc

À Propos

Data Analyst Junior avec une solide expérience en marketing numérique et des compétences approfondies dans l’utilisation de Microsoft Excel, SQL, Tableau Software et Python.

Complex Retrieval Queries with SQL

Liens

Compétences & Outils

Description du projet

Utiliser des Requêtes Complexes de Récupération avec SQL :

WHERE CLAUSE
JOIN STATEMENT
AGGREGATE QUERIES
NESTED QUERIES

Objectifs de l'analyse

Utiliser des Requêtes Complexes de Récupération avec SQL pour les besoins de l’analyse

JOIN WITH THE WHERE CLAUSE

Billing department has requested a list of our USA customers and their invoices with dates and totals

				
					SELECT  customer_id
		invoice_id,
		invoice_date,
		total
FROM invoice
WHERE billing_country='USA'
LIMIT 10;

invoice_id	invoice_date	total
23	2009-01-11	13.86
16	2009-02-19	0.99
17	2009-03-04	1.98
19	2009-03-04	1.98
21	2009-03-05	3.96
25	2009-03-06	5.94
19	2009-04-14	13.86
17	2009-06-06	3.96
21	2009-06-07	5.94
27	2009-06-10	8.91

Billing department also requested the first and last names

				
					SELECT  invoice.customer_id,
		first_name,
		last_name,
		invoice_id,
		invoice_date,
		total
FROM invoice, customer
WHERE billing_country='USA'
	AND invoice.customer_id=customer.customer_id
LIMIT 10;

customer_id	first_name	last_name	invoice_id	invoice_date	total
23	John	Gordon	5	2009-01-11	13.86
16	Frank	Harris	13	2009-02-19	0.99
17	Jack	Smith	14	2009-03-04	1.98
19	Tim	Goyer	15	2009-03-04	1.98
21	Kathy	Chase	16	2009-03-05	3.96
25	Victor	Stevens	17	2009-03-06	5.94
19	Tim	Goyer	26	2009-04-14	13.86
17	Jack	Smith	37	2009-06-06	3.96
21	Kathy	Chase	38	2009-06-07	5.94
27	Patrick	Gray	39	2009-06-10	8.91

The Billing department has requested to add the unit price and quantity for each track purchased on each invoice

				
					SELECT  invoice.customer_id,
		first_name,
		last_name,
		invoice.invoice_id,
		invoice_date,
		total,
		track_id,
		unit_price,
		quantity
FROM invoice, customer, invoice_line
WHERE billing_country='USA'
	AND invoice.customer_id=customer.customer_id
	AND invoice.invoice_id=invoice_line.invoice_id
LIMIT 20;

customer_id	first_name	last_name	invoice_id	invoice_date	total	track_id	unit_price	quantity
23	John	Gordon	5	2009-01-11	13.86	99	0.99	1
23	John	Gordon	5	2009-01-11	13.86	108	0.99	1
23	John	Gordon	5	2009-01-11	13.86	117	0.99	1
23	John	Gordon	5	2009-01-11	13.86	126	0.99	1
23	John	Gordon	5	2009-01-11	13.86	135	0.99	1
23	John	Gordon	5	2009-01-11	13.86	144	0.99	1
23	John	Gordon	5	2009-01-11	13.86	153	0.99	1
23	John	Gordon	5	2009-01-11	13.86	162	0.99	1
23	John	Gordon	5	2009-01-11	13.86	171	0.99	1
23	John	Gordon	5	2009-01-11	13.86	180	0.99	1
23	John	Gordon	5	2009-01-11	13.86	189	0.99	1
23	John	Gordon	5	2009-01-11	13.86	198	0.99	1
23	John	Gordon	5	2009-01-11	13.86	207	0.99	1
23	John	Gordon	5	2009-01-11	13.86	216	0.99	1
16	Frank	Harris	13	2009-02-19	0.99	462	0.99	1
17	Jack	Smith	14	2009-03-04	1.98	463	0.99	1
17	Jack	Smith	14	2009-03-04	1.98	464	0.99	1
19	Tim	Goyer	15	2009-03-04	1.98	466	0.99	1
19	Tim	Goyer	15	2009-03-04	1.98	468	0.99	1
21	Kathy	Chase	16	2009-03-05	3.96	470	0.99	1

JOIN WITH THE JOIN STATEMENT

Retrieve a list of the ship mode and how long (in seconds) it took to ship a product to a customer

				
					SELECT  name,
		title
FROM artist
JOIN album
ON artist.artist_id=album.artist_id
ORDER BY name, title
LIMIT 10;

name	title
AC/DC	For Those About To Rock We Salute You
AC/DC	Let There Be Rock
Aaron Copland & London Symphony Orchestra	A Copland Celebration, Vol. I
Aaron Goldberg	Worlds
Academy of St. Martin in the Fields & Sir Neville Marriner	The World of Classical Favourites
Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner	Sir Neville Marriner: A Celebration
Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair	Fauré: Requiem, Ravel: Pavane & Others
Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart	Bach: Orchestral Suites Nos. 1 - 4
Accept	Balls to the Wall
Accept	Restless and Wild

AGGREGATE QUERIES

Retrieve the number of invoices from each country

				
					SELECT billing_country, COUNT(*)
FROM invoice
GROUP BY billing_country;

billing_country	count
Argentina	7
Spain	7
Italy	7
Hungary	7
India	13
Czech Republic	14
Belgium	7
Sweden	7
Chile	7
Norway	7
France	35
USA	91
United Kingdom	21
Netherlands	7
Brazil	35
Austria	7
Poland	7
Australia	7
Ireland	7
Germany	28
Denmark	7
Canada	56
Finland	7
Portugal	14

Sales department want to know the top 10 customers who spend the most in our products

				
					SELECT  customer_id,
		SUM(total) AS total_spent
FROM invoice
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 10;

customer_id	total_spent
6	49.62
26	47.62
57	46.62
46	45.62
45	45.62
37	43.62
24	43.62
28	43.62
25	42.62
7	42.62

Sales department wants to know the smallest and largest invoice total and average as well

				
					SELECT  customer_id,
		SUM(total) AS total_spent,
		MAX(total) AS largest_invoice,
		MIN(total) AS smallest_invoice,
		ROUND(AVG(total),2) AS average_invoice
FROM invoice
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 10;

customer_id	total_spent	largest_invoice	smallest_invoice	average_invoice
6	49.62	25.86	0.99	7.09
26	47.62	23.86	0.99	6.80
57	46.62	17.91	0.99	6.66
46	45.62	21.86	0.99	6.52
45	45.62	21.86	0.99	6.52
37	43.62	14.91	0.99	6.23
24	43.62	15.86	0.99	6.23
28	43.62	13.86	0.99	6.23
25	42.62	18.86	0.99	6.09
7	42.62	18.86	0.99	6.09

NESTED QUERIES

Sales department wants to know what invoices have totals that are above the average sales total

				
					SELECT  invoice_id,
		total
FROM invoice
WHERE total > (SELECT AVG(total) FROM invoice)
LIMIT 10;

invoice_id	total
3	5.94
4	8.91
5	13.86
10	5.94
11	8.91
12	13.86
17	5.94
18	8.91
19	13.86
24	5.94

Sales department wants a list of these customers

				
					SELECT  first_name,
		last_name
FROM customer
WHERE customer_id IN
(
	SELECT customer_id
	FROM invoice
	WHERE total>(SELECT AVG(total) FROM invoice)
)
LIMIT 10;

first_name	last_name
Luís	Gonçalves
Leonie	Köhler
François	Tremblay
Bjørn	Hansen
František	Wichterlová
Helena	Holý
Astrid	Gruber
Daan	Peeters
Kara	Nielsen
Eduardo	Martins

Mekki MOURADI

Data Analyst

Rabat, Maroc

À Propos

Complex Retrieval Queries with SQL

Liens

Compétences & Outils

Description du projet

Objectifs de l'analyse

Home

Excel Projects

Tableau Projects

SQL Projects

Python Projects