Syntax Flashcards
What keyword do you use to filter BEFORE input rows?
WHERE
What keyword do you use to filter on the OUTPUT of a calculation, and does it go before or after the aggregate function?
HAVING
Used after the aggregate function
What are the five calculation operators and what do they do?
COUNT - counts how many records in a specified field
SUM - calculates total value
AVG - calculates mean of specified field
MIN - gives lowest value of field in a table
MAX - gives highest value of field in a table
What operators can be used with the WHERE clause?
OR
AND
BETWEEN
IN
In a join statement, which keyword comes last: WHERE or ORDER BY?
ORDER BY always comes last
What does wildcard % mean and what keyword is it used with?
Means ‘zero or more characters’
Used with the LIKE keyword
What’s the correct way to format a date in SQL?
‘YYYY-MM-DD’
What keyword can be used as a filter?
WHERE
What is the SQL SELECT statement used for?
Read data from a database
What are two ways to limit results by a specified number of fields?
SQL Server version:
SELECT TOP [number limit]
MySQL version:
FROM [table name] LIMIT [number limit]
Why is archiving required and what is the syntax to archive ‘deleted’ records?
Archiving is needed if a table becomes too large as performance will be affected.
SELECT * INTO tArchive
FROM tTable
WHERE deleteFlag = 1
To update records/fields, what order does the SQL keywords go in?
UPDATE
SET
WHERE
What is the basic syntax for adding a new column?
ALTER TABLE [table name]
ADD [new column name] [data type]
What is the basic syntax for inserting subsets of fields?
INSERT INTO [table name] ( [column names] ) Values ( )
What is the basic syntax for creating a new table?
SELECT * INTO [new table name]
FROM [original name]
Values (
)
