This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
cheatsheets:sql [2017/02/03 16:17] admin created |
cheatsheets:sql [2020/06/13 20:09] (current) |
||
|---|---|---|---|
| Line 14: | Line 14: | ||
| SELECT FirstName, LastName, City FROM Customer WHERE Country = 'Brasil' OR (FirstName = 'Frank' AND LastName = 'Sinatra') | SELECT FirstName, LastName, City FROM Customer WHERE Country = 'Brasil' OR (FirstName = 'Frank' AND LastName = 'Sinatra') | ||
| + | |||
| + | * Get the first name, city and total invoice of clients living neither in Paris, Prague nor Rome and whose invoices are between 35 and 40 | ||
| + | |||
| + | SELECT FirstName, City, InvoiceTotal FROM Customer WHERE City NOT IN ('Paris', 'Prague', 'Rome') AND InvoiceTotal BETWEEN 35 AND 40 | ||
| + | |||
| + | * Get the first name and invoice ordered first by invoice total descending and later by first name ascending. | ||
| + | |||
| + | SELECT FirstName, InvoiceTotal FROM Customer ORDER BY InvoiceTotal DESC, FirstName ASC | ||
| + | |||
| + | * Get the customer id, country and total invoice ordered by country first and later by total invoice, list first those customers living in Canada, USA or Mexico | ||
| + | |||
| + | SELECT CustomerId, Country, InvoiceTotal FROM Customer ORDER BY Country IN ('Canada,' 'USA', 'Mexico') DESC, InvoiceTotal | ||
| + | |||
| + | * Get the first and last name of customers whose first name doesn't start with P | ||
| + | |||
| + | SELECT FirstName, LastName FROM Customer WHERE FirstName NOT LIKE 'P%' | ||
| + | |||
| + | * Get the first and last name of customers whose first name doesn't start with P, T or Z | ||
| + | |||
| + | SELECT FirstName, LastName FROM Customer WHERE FirstName LIKE '[!P,T,Z]%' | ||
| + | |||
| + | * Get the first and last name of customers whose second name is made of four letters | ||
| + | |||
| + | SELECT FirstName, LastName FROM Customer WHERE LastName LIKE '____' | ||
| + | |||
| + | * Get the name and bitrate (from the Byte and Millisecond columns), then order the result by bitrate from the highest to the lowest | ||
| + | |||
| + | SELECT Name, (Byte * 8) / (Millisecond / 1000) AS bitrate FROM Track ORDER BY bitrate DESC | ||
| + | |||
| + | * Get the total number of tracks along the space in Gb they take on HD | ||
| + | |||
| + | SELECT COUNT(TrackId), SUM(Bytes/1024/1024/1024) FROM Track | ||
| + | |||
| + | * Calculate the difference between the longest and shortest track in seconds | ||
| + | |||
| + | SELECT MAX(Milliseconds/1000) - MIN(Milliseconds/1000) FROM Track | ||
| + | |||
| + | * For all the countries in the customer table, display the name and total income generated by country | ||
| + | |||
| + | SELECT Country, SUM(InvoiceTotal) FROM Customer GROUP BY Country | ||
| + | |||
| + | * Just for Brasil, display the name of the state alongside the number of customers in each of them. | ||
| + | |||
| + | SELECT State, COUNT(CustomerId) FROM Customer WHERE Country = 'Brasil' GROUP BY State | ||
| + | |||
| + | * List all the countries having customers alonside the income these countries generate. Ensure the top earning countries come first. | ||
| + | |||
| + | SELECT Country, SUM(InvoiceTotal) AS TotalRevenue FROM Customer GROUP BY Country ORDER BY TotalRevenue DESC | ||
| + | |||
| + | * List all the customers having customers. Ensure the top earning countries come first. | ||
| + | |||
| + | SELECT Country FROM (SELECT Country, SUM(InvoiceTotal) FROM Customer GROUP BY Country ORDER BY 2 DESC) | ||
| + | |||
| + | * For countries listed in the customer table and generating more than $300, display their name alongside their revenue. | ||
| + | |||
| + | SELECT Country, SUM(InvoiceTotal) AS Revenue FROM Customer GROUP BY Country HAVING Revenue > 300 | ||
| + | |||
| + | * For countries with more than 4 customers display their name alongside the revenue they generate. | ||
| + | |||
| + | SELECT Country, SUM(InvoiceTotal) FROM Customer GROUP BY Country HAVING COUNT(CustomerId) > 4 | ||