iframe-proxy | Sunbelt Computer Software

97 lines (63 loc) · 3.7 KB
-- Below are a bunch of "plain English" sentences.  Your job is to take a stab
-- at translating each one into a single SELECT query.  You can write your
-- query below each "plain English" sentence.
-- We've written some example queries as a way to both show you how and where
-- to type your work and also as a way to show you new SQL commands, clauses,
-- or syntax where necessary.
-- **Please** don't delete the "plain English" versions. Just write your query
-- on the line below it. :)
-- The lines in this file that begin with "--" are comments.  You're encouraged
-- to leave your own comments, reflecting on things you're confused about,
-- questons you have, or anything else.  The more we know about how you're
-- thinking the better feedback we'll be able to give!
-- "albums" table
-- Every album
SELECT * FROM albums;
-- Every album title (and only the title)
SELECT title FROM albums;
-- Every album ordered by album title in ascending order (A-Z)
-- Note: ORDER BY orders by lowest-to-highest by default
SELECT * FROM albums ORDER BY title;
-- Every album ordered by album title in descending order (Z-A)
SELECT * FROM albums ORDER BY title DESC;
-- Every album whose title starts with A in alphabetical order
SELECT * FROM albums WHERE title LIKE 'A%' ORDER BY title;
-- "invoices" table
-- Remember: run ".schema invoices" to see the structure of the "invoices" table
-- Every invoice
-- Every invoice ordered by total invoice amount ("total")
-- Every invoice with a total greater than 10
-- The 10 least expensive invoices
-- Remember: ORDER BY orders from lowest-to-highest by default
SELECT * FROM invoices ORDER BY total LIMIT 10;
-- The 10 most expensive invoices
-- The 15 most recent invoices
-- The 15 oldest invoices
-- The 10 most expensive invoices from the US
-- The 10 least expensive invoices from the US
-- The 10 most expensive invoices from outside the US
-- Hint: If "=" means equal, use "!=" to mean "not equal"
-- Every invoice from Chicago, IL
SELECT * FROM invoices WHERE billing_city = "Chicago" AND billing_state = "IL" AND billing_country="USA";
-- A list of all the invoices worth more than $5.00 from Chicago, IL
-- The billing addresses of the 5 most valuable invoices from Mountain View CA
-- Gotta reward those big spenders!
-- A list of the 10 most valuable invoices made before January 1st, 2010
-- Hint: Dates are formatted like 'YYYY-MM-DD' and you can compare them using '<', '>', '<=' and '>='
-- The number of invoices from Chicago, IL
SELECT COUNT(*) FROM invoices WHERE billing_city = "Chicago" AND billing_state = "IL" AND billing_country = "USA";
-- The number of invoices from the US, grouped by state
SELECT billing_state, COUNT(*) FROM invoices WHERE billing_country = "USA" GROUP BY billing_state;
-- The state in the US with the most invoices
SELECT billing_state, COUNT(*) FROM invoices WHERE billing_country = "USA" GROUP BY billing_state ORDER BY COUNT(*) DESC LIMIT 1;
-- The total invoice value from California
SELECT billing_state, SUM(total) FROM invoices WHERE billing_country = "USA" and billing_state = "CA";
-- The number of invoices and the invoice total from California
SELECT billing_state, COUNT(*), SUM(total) FROM invoices WHERE billing_country = "USA" and billing_state = "CA";
-- The count, total, and average of invoice totals from California
SELECT billing_state, COUNT(*), SUM(total), AVG(total) FROM invoices WHERE billing_country = "USA" and billing_state = "CA";
-- The count, total, and average of invoice totals, grouped by state, ordered by average invoice total from highest-to-lowest
-- A list of the top 5 countries by number of invoices
-- A list of the top 5 countries by gross/total invoice size
-- A list of the top 5 countries by average invoice size
Sunbelt Computer Software

PL/B Language Development and Support

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Sunbelt Computer Software

PL/B Language Development and Support

Uh oh!

FilesExpand file tree

queries.sql

Latest commit

History

queries.sql

File metadata and controls
