SQL Statements Flashcards
What is the INSERT Statement?
May insert a single, or many rows
How would you insert a single row?
Column names are optional, if column names are not specified, all columns must have values.
How would you insert multiple rows?
What is the DELETE Statement?
May delete a single or many (all) rows. The WHERE clause is optional. If not specified all rows will be deleted without warning.
How would you delete a single row?
How would you delete multiple rows?
How would you delete all rows?
What is the UPDATE Statement?
May update a single or many(all) rows. Where clause is optional. If not specified all rows will be updated without warning.
How would you update a single row?
The WHERE clause uses primary key to locate one record
How would you update multiple rows?
WHERE clause located many records
What is the GO Statement?
When a “batch” of statements have been executed, the end of the batch can be signified by a GO statement. The GO statement is usually optional, but can be required in certain circumstances. Some CREATE statements require that they are the first statement in a batch.
How would you set the date format in an SQL script?
SET DATEFORMAT ymd
Sets the date format to YYYY-MM-
What is a JOIN?
Frequently it is useful to join the data from multiple tables together.
SELECT column(s)
FROM firsttable
JOIN secondtable
ON firsttable.column = secondtable.colum
What are the different join types?
Inner join (default), left outer join, right outer join, full outer join, cross join.
How would you do an inner join?
How would you do a left outer join?
How would you do a right outer join?
How would you do a full outer join?
How would you do a cross join?
How would you join 3 tables?
How would you join a table with itself?
What is the proper date format to use?
Consider the following date: 01/02/03
Americans would say January 2, 2003
British would say February 1, 2003
Chinese would say February 3, 2001
Canadians wouldn’t be sure
What is the DATEDIFF function?
SELECT DATEDIFF(DAY, ‘2017-01-01’, ‘2018-09-01’)
Result is 608 days
SELECT DATEDIFF(MONTH, ‘2017-01-01’, ‘2018-09-01’)
Result is 20 months
SELECT DATEDIFF(YEAR, ‘2017-01-01’, ‘2018-09-01’)
Result is 1 year
How would you properly someones age with the datediff function?
SELECT FLOOR(DATEDIFF(DAY, ‘1999-11-15’, ‘2018-10-02’) / 365.25)
What is the DATEADD function?
DATEADD(datepart, number, date)
SELECT DATEADD(DAY, 60, ‘2018-09-01’)
Result is 2018-10-31
SELECT DATEADD(MONTH, 5, ‘2018-09-01’)
Result is 2019-02-01
SELECT DATEADD(YEAR, 3, ‘2018-09-01’)
Result is 2021-09-01
How would you subtract date instead of add?
There is no DATESUB, use DATEADD with negative values
What is the GETDATE function?
The GETDATE function returns the current date with time
SELECT GETDATE()
To get the date without the time, the CONVERT function can be used
SELECT CONVERT(DATE, GETDATE())
Calculate patient age
SELECT birth_date,
FLOOR(DATEDIFF(DAY, birth_date, GETDATE()) / 365.25)
FROM patients
How would you extract the date parts?
DAY(date) extracts the day of the month
MONTH(date) extracts the month of the year
YEAR(date) extracts the year
SELECT birth_date,
YEAR(birth_date) AS [year],
MONTH(birth_date) AS [month],
DAY(birth_date) AS [day]
FROM patients
What is a Predicate?
A predicate specifies a condition that is true, false, or unknown about a given row or group
Used in WHERE clauses
Basic Predicate (=, <>, <, >, <=, >)
BETWEEN Predicate
EXISTS Predicate
IN Predicate
LIKE Predicate
NULL Predicate
Why would you use the BETWEEN Predicate?
The BETWEEN predicate compares a value inclusively with a range of values more compact version of >= AND <=
eg.
SELECT first_name, last_name, patient_height
FROM patients
WHERE patient_height BETWEEN 100 AND 125
VS
WHERE patient_height >= 100 AND patient_height <= 125
How would you use the EXISTS Predicate?
The EXISTS predicate tests for the existence of certain rows using a subquery. EXISTS returns true if a subquery contains any rows. eg. Find patients who take at least one medication.
What is the IN Predicate?
The IN predicate compares a value with a set of values. For many cases, whenever the EXISTS predicate can be used, the IN predicate can be used.
How would you use the LIKE predicate?
How would you use the NULL Predicate?
How would you use the TOP clause?
How would you use the TOP PERCENT clause?
How would you use the HAVING clause?
