SQL Syntax Flashcards
(18 cards)
What is the structure of a basic SQL query?
And what do they refer to?
A basic SQL query contains 3 clauses…
SELECT - columns you want to use/see
FROM - table you want to reference
WHERE - criteria you want to use to filter rows (using operators)
How would you return ALL columns?
SELECT *
What special character do you use at the end of a SQL statement and what is it’s purpose/name?
; (terminator)
What is a query?
A request for a certain set of data/information from a database
What is the FROM syntax?
Dataset.TableName
What is the SELECT syntax for multiple columns
Column names with comma separator (except for final column)
SELECT
columnA,
columnB,
columnC
Quirky: DON’T use comma separators for multiple WHERE criteria
How do you rename ugly/long column names to be more readable? What do you call this? How do you include spaces in the name?
Use a column alias.
For spaces, must a b or [a b]
SELECT
CustomerID AS NewID,
Badname AS `Better Name`,
Crapname AS [New Name]
Is SQL case-sensitive?
No, but using caps and indentation can make it more readable.
Column names - use_snake_case
Table names - Camel_Case
What is the comment syntax?
– comment (most common)
or
/* comment */
WHERE: What is syntax for an exact match? Text? Number?
WHERE
CustomerID = 149;
WHERE
CustomerName = ‘Bob’
WHERE: How do you search using Starts with? Ends with? Contains? What is the special character called?
Wildcard character % used for searching text strings. Almost always used with LIKE operator (some databases use * instead)
WHERE Name LIKE ‘A%’
WHERE Name LIKE ‘%a’
WHERE Name LIKE ‘%a%’
WHERE: How would you search for records where the third character of first name is a?
WHERE Firstname LIKE ‘__a%’
Use two underscore characters.
How do you find records with a blank value in columnA? or all non-blank records?
WHERE columnA IS NULL WHERE columnA IS NOT NULL
How do you filter records in a SQL query?
WHERE clause is where all filtering criteria go (uses boolean logic)
How do you sort records (ascending or descending) that are being output from a SQL statement? How would you sort with multiple criteria?
ORDER BY clause (at end of statement with ; terminator after). Default sort order is ascending (ASC) but can be changed to descending with DESC… separate multiple criteria with comma separtor
ORDER BY
Column1Name DESC,
Column2Name ASC;
What’s ORDER BY for and where is it placed in a SQL statement?
ORDER BY is for sorting rows based on a particular column value. It belongs after FROM and WHERE - if used, but before LIMIT. Default is ascending, but can use DESC for descending…
WHERE ColumnName = ‘value’
ORDER BY ColumnName DESC
LIMIT 1000
How do you restrict the number of rows to be returned and when might you need to do this?
LIMIT the number of rows returned (placed at very end of query - after any WHERE and ORDER BY)
SELECT * FROM dataset.table
WHERE ColumnName < 50
ORDER BY ColumnName
LIMIT 1000
Really large datasets (save query time), preview results
Why might you want to create a new table? How do you do it?
You may want to create a table if the original dataset is changing and you want to capture the data at a point in time OR if you know you just want to work on specific subsets of a larger dataset for simplicity (rather than one really complex query that is continually updated for different problems, you can create a new table - and conduct queries on that new table). Save results to New Table or change query settings to output to New Table
