Download Appunti SQL Structured Query Language and more Study notes Computer Science in PDF only on Docsity!
Fundamentals of Databases and SQL https://analyticsga-euwest1.generalassemb.ly/browser/ bit.ly/databasesql [email protected] analyticsga SQL vs Excel SQL: Structured Quary Language Larger data sets / unlimited data Quicker More Complex data Live data Secure Cloud: lots of servers together ie. MySQL opensource T-SQL microsoft PostgreSQL opensource
- SQL one language but there are subtle differences for ex. English: British, American, Canadian Excel Data loss EXCEL VS SQL Computer <-> Server Spreadsheet <-> Database Tab/sheet <-> Table Column <-> Variable Formulas <-> Script
Bare minimum: ESSENTIAL [ SELECT (what do you want to see) FROM (where to get the data from?)
table * (show me everything)
ORDER BY ( How to sort data?) Columns ASC (ASCENDING) /DESC (DESCENDING) LIMIT (how many rows to show?) number
Specify which columns you want to see:
Select category_name, item_description
CASE INSENSITIVE: ILIKE
%: WILDCARD PARTIAL MATCH
NOT LIKE:
MORE FLEXIBLE:
**3) Which tequilas or scotch whiskies have a case cost of more than 100$?
- Which whiskies of any kind cost between $100 and $150?**
5) Which products except tequilas cost less than 100$ or more than 200$? REMINDER:
- ALWAYS 3 components for a condition: column, test, value
- Think about casing
- Is it a partial match? -> add %
- != not ILIKE
- In with Like
Filtering and Aggregating in SQL: CAST AND DISTINCT Text:
- letters - numbers 123 - symbols ie.
Getting rid of duplicates and achieving unique values: DISTINCT
The CEO OF IOWA LIQUOR STORES Ltd has a question for you to research: From The Iowa Liquor Datas I only want information about vendor 305. Can you get me the bottle price and proof for each distinct product? The bottle price should be less than 5 or the proof is greater than 100, either is fine Mistakes: proof > 100 : text > integer so (CAST (proof as integer) bottle_price < 5: money < integer so Cast (bottle_pice as numeric) < 5 vendor = 305
Introduction: AGGREGATIONS min, max, average, sum, count SELECT (what do you want to see)
FROM (where to get the data from?) table * (show me everything)
WHERE (How to filter rows) column test value condition AND/OR column, test ( =, !=,
,<, IN (…, …., ) , LIKE (case sensitive), ILIKE (case insensitive) GROUP BY (how to aggregate the data?) ORDER BY (How to sort data?) columns ASC/DESC LIMIT (how many rows to show?) number
SELECT categorical variables, (continuous variables) Apply aggregate function FROM GROUP BY categorical variables Aggregations Exercise: 1 ) What was the total revenue an total quantity sold? Hint: for revenue use column total and for quantity use column bottle qty
3) Which was the county with most transactions? MOST = Count
5) Show the last time that each store made a transaction
6) Which were the 3 top selling counties in terms of revenue
