SQL Syntax Flashcards
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
