SQL Syntax

Question 1

What is the structure of a basic SQL query?
And what do they refer to?

Answer 1

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)

Question 2

How would you return ALL columns?

Answer 2

SELECT *

Question 3

What special character do you use at the end of a SQL statement and what is it’s purpose/name?

Answer 3

; (terminator)

Question 4

What is a query?

Answer 4

A request for a certain set of data/information from a database

Question 5

What is the FROM syntax?

Answer 5

Dataset.TableName

Question 6

What is the SELECT syntax for multiple columns

Answer 6

Column names with comma separator (except for final column)

SELECT
columnA,
columnB,
columnC

Quirky: DON’T use comma separators for multiple WHERE criteria

Question 7

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?

Answer 7

Use a column alias.
For spaces, must a b or [a b]

SELECT 
     CustomerID AS NewID,
     Badname AS `Better Name`,
     Crapname AS [New Name]

Question 8

Is SQL case-sensitive?

Answer 8

No, but using caps and indentation can make it more readable.
Column names - use_snake_case
Table names - Camel_Case

Question 9

What is the comment syntax?

Answer 9

– comment (most common)
or
/* comment */

Question 10

WHERE: What is syntax for an exact match? Text? Number?

Answer 10

WHERE
CustomerID = 149;

WHERE
CustomerName = ‘Bob’

Question 11

WHERE: How do you search using Starts with? Ends with? Contains? What is the special character called?

Answer 11

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%’

Question 12

WHERE: How would you search for records where the third character of first name is a?

Answer 12

WHERE Firstname LIKE ‘__a%’

Use two underscore characters.

Question 13

How do you find records with a blank value in columnA? or all non-blank records?

Answer 13

WHERE columnA IS NULL
WHERE columnA IS NOT NULL

Question 14

How do you filter records in a SQL query?

Answer 14

WHERE clause is where all filtering criteria go (uses boolean logic)

Question 15

How do you sort records (ascending or descending) that are being output from a SQL statement? How would you sort with multiple criteria?

Answer 15

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;

Question 16

What’s ORDER BY for and where is it placed in a SQL statement?

Answer 16

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

Question 17

How do you restrict the number of rows to be returned and when might you need to do this?

Answer 17

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

Question 18

Why might you want to create a new table? How do you do it?

Answer 18

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