SQL Statements

Question 1

What is the INSERT Statement?

Answer 1

May insert a single, or many rows

Question 2

How would you insert a single row?

Answer 2

Column names are optional, if column names are not specified, all columns must have values.

Question 3

How would you insert multiple rows?

Answer 3

Question 4

What is the DELETE Statement?

Answer 4

May delete a single or many (all) rows. The WHERE clause is optional. If not specified all rows will be deleted without warning.

Question 5

How would you delete a single row?

Answer 5

Question 6

How would you delete multiple rows?

Answer 6

Question 7

How would you delete all rows?

Answer 7

Question 8

What is the UPDATE Statement?

Answer 8

May update a single or many(all) rows. Where clause is optional. If not specified all rows will be updated without warning.

Question 9

How would you update a single row?

Answer 9

The WHERE clause uses primary key to locate one record

Question 10

How would you update multiple rows?

Answer 10

WHERE clause located many records

Question 11

What is the GO Statement?

Answer 11

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.

Question 12

How would you set the date format in an SQL script?

Answer 12

SET DATEFORMAT ymd

Sets the date format to YYYY-MM-

Question 13

What is a JOIN?

Answer 13

Frequently it is useful to join the data from multiple tables together.

SELECT column(s)

FROM firsttable

JOIN secondtable

ON firsttable.column = secondtable.colum

Question 14

What are the different join types?

Answer 14

Inner join (default), left outer join, right outer join, full outer join, cross join.

Question 15

How would you do an inner join?

Answer 15

Question 16

How would you do a left outer join?

Answer 16

Question 17

How would you do a right outer join?

Answer 17

Question 18

How would you do a full outer join?

Answer 18

Question 19

How would you do a cross join?

Answer 19

Question 20

How would you join 3 tables?

Answer 20

Question 21

How would you join a table with itself?

Answer 21

Question 22

What is the proper date format to use?

Answer 22

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

Question 23

What is the DATEDIFF function?

Answer 23

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

Question 24

How would you properly someones age with the datediff function?

Answer 24

SELECT FLOOR(DATEDIFF(DAY, ‘1999-11-15’, ‘2018-10-02’) / 365.25)

Question 25

What is the DATEADD function?

Answer 25

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

Question 26

How would you subtract date instead of add?

Answer 26

There is no DATESUB, use DATEADD with negative values

Question 27

What is the GETDATE function?

Answer 27

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

Question 28

How would you extract the date parts?

Answer 28

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

Question 29

What is a Predicate?

Answer 29

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

Question 30

Why would you use the BETWEEN Predicate?

Answer 30

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

Question 31

How would you use the EXISTS Predicate?

Answer 31

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.

Question 32

What is the IN Predicate?

Answer 32

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.

Question 33

How would you use the LIKE predicate?

Answer 33

Question 34

How would you use the NULL Predicate?

Answer 34

Question 35

How would you use the TOP clause?

Answer 35

Question 36

How would you use the TOP PERCENT clause?

Answer 36

Question 37

How would you use the HAVING clause?

Answer 37