Retrieving data from a single table

Question 1

True or False

SQL is case sensitive?

Answer 1

False

SQL is not case sensitive but keywords are usually CAPITALIZED.

Question 2

USE

Answer 2

to select what database to query and must be terminated ;

Question 3

SELECT

Answer 3

which columns to query or * for all columns

Question 4

FROM

Answer 4

what table to query

Question 5

MYSQL how to execute a high-lighted sub query (Windows)

Answer 5

shift + ctrl + enter

Question 6

WHERE

Answer 6

used to filter the data on a conditional basis

Question 7

True or False

This SQL query is in the correct order of operations?

USE

SELECT

FROM

WHERE

ORDER BY

Answer 7

True

Question 8

ORDER BY

Answer 8

determines sort order ascending is the default

Question 9

How do you make a comment in SQL

Answer 9

– this is a comment in SQL crtl + /

Question 10

True or False

in SQL the FROM, WHERE, and ORDER BY clauses are optional?

Answer 10

True

Question 11

What does this code produce?

USE

SELECT 1, 2

Answer 11

creates two columns 1 and 2

Question 12

True or False

The order doesn’t matter in SQL?

Answer 12

False

order matters when querying a database

Question 13

True or False

White space is ignored in SQL?

Answer 13

True

Question 14

This code does what?

SELECT
last_name,
first_name,
points,
points + 10
FROM customers

Answer 14

selects columns last_name, first_name, and points columns from customers table and adds 10 points to the points column values then creates a column points + 10 to hold those values.

Question 15

How do you create an alias in SQL

Answer 15

AS

FROM customers c

Question 16

How would you change the order of operation in an expression?

Answer 16

with parathesis (points + 10) * 100

Question 17

True or False

It’s a good idea to use an alias on SQL arithmetic expressions?

Answer 17

True

Question 18

In SQL this “discount factor” is seen as a

Answer 18

string

Question 19

SELECT DISTINCT does what?

Answer 19

removes or ignores duplicated values

Question 20

What is the WHERE clause doing in this query?

• *SELECT** *
• *FROM** Customers
• *WHERE** points > 3000

Answer 20

filtering customers whose points are greater than 3000

Question 21

What are the SQL comparison operators?

Answer 21

> greater than

>= greater than or equal

< less than

<= less than or equal

= equal

!=, <> not equal

Question 22

What is the WHERE clause doing in this query?

SELECT *

FROM Customers

WHERE state = ‘VA’

Answer 22

filtering customers by the state of ‘VA’

Question 23

What is the WHERE clause doing in this query?

• *SELECT** *
• *FROM** customers
• *WHERE** state = ! ‘VA’

Answer 23

filtering customers that are NOT in the state of ‘VA’

Question 24

What is the WHERE clause doing in this query?

• *SELECT** *
• *FROM** customers
• *WHERE** birth_date > ‘1990-01-01’

Answer 24

filtering customer whose birth date is greater than 01-01-1990

Question 25

When comparing multiple conditions in SQL what's needed?

Answer 25

AND, OR, NOT operators

Question 26

How many conditions does this SQL query possess? ## Footnote * *SELECT** \* * *FROM** Customers * *WHERE** birth\_date \> '1990-01-01' **AND** points \> 1000

Answer 26

there are two conditions (see the WHERE clause)

Question 27

True or False In SQL the **AND** operator is always executed first as a first-order operator?

Answer 27

True

Question 28

In SQL the NOT clause does what? ## Footnote * *SELECT** \* * *FROM** Customers * *WHERE NOT** birth\_date \> '1990-01-01' **OR** points \> 1000

Answer 28

negates a condition \> negates to \< **OR** negates to **AND** \> negates to \<

Question 29

In SQL arithmetic expressions are?

Answer 29

simple calculations performed within a query ## Footnote * *SELECT** \* * *FROM** order\_items * *WHERE** order\_id = 6 **AND** (quantity \* unit\_price \> 30)

Question 30

Why can't you do this in SQL? ## Footnote **WHERE** state = 'VA' **OR**'GA' **OR**'FL'?

Answer 30

the conditions are boolean values and **OR**'GA' **OR**'FL' are string values

Question 31

How would you refactor this query? ## Footnote * *SELECT** \* * *FROM** Customers * *WHERE** state = 'VA' **OR** state = 'GA' **OR** state = 'FL'

Answer 31

* *SELECT** \* * *FROM** Customers * *WHERE** state **IN** ('VA', 'GA', 'FL') similiar to the python membership operator IN

Question 32

What is this SQL query doing? ## Footnote * *SELECT** \* * *FROM** Customers * *WHERE** state **NOT IN** ('VA', 'FL', 'GA')

Answer 32

selecting all records from the Customers table and filtering out states VA, GA, and FL

Question 33

Refactor this code ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** points \>= 1000 **AND** points \<= 3000

Answer 33

* *SELECT** \* * *FROM** customers * *WHERE** points **BETWEEN** 1000 **AND** 3000

Question 34

What type of data type is the birth\_date column? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** birth\_date **BETWEEN**'1990-01-01' **AND**'2000-01-01'

Answer 34

string

Question 35

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** last\_name **LIKE**'%y'

Answer 35

all the last names from the customer's table that end in a y

Question 36

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** last\_name **LIKE**'%y%'

Answer 36

all customers where the last name has a letter y in it

Question 37

In MySQL following the **LIKE** statement what does '-' represent in a string?

Answer 37

a single character

Question 38

what is any number of characters in MySql regex?

Answer 38

%

Question 39

what is a represents a single character?

Answer 39

\_

Question 40

What is this query returning? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** phone **NOT LIKE**'%9'

Answer 40

all customers whose phone numbers that do not end in 9

Question 41

What MySQL statement is used for searching string values?

Answer 41

**REGEXP** regular expression

Question 42

True or False ## Footnote This: **WHERE** last\_name **LIKE**'%field%' is comparable to this: **WHERE** last\_name **REGEXP**'field'

Answer 42

True

Question 43

This ^ represents what in a MySQL **REGEXP**

Answer 43

the beginning of a string ## Footnote '^waters'

Question 44

This $ represents what in a MySQL **REGEXP**

Answer 44

the end of a string ## Footnote 'field$' would return Brush**field**.

Question 45

This | represents what in a MySQL **REGEXP**

Answer 45

a separator to perform multiple string searches simultaneously ## Footnote **WHERE** last\_name **REGEXP**'field | mac | rose'

Question 46

True or False ## Footnote This is 'legal' in MySQL * *SELECT** \* * *FROM** customers * *WHERE** last\_name **REGEXP**'field | mac | rose'

Answer 46

False no spacing allowed between 'field|mac|rose'

Question 47

What is this query returning? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** last\_name **REGEXP**'[gim]e'

Answer 47

all customers whose last name ends with e but have a g, i, or m that precedes the e in it. *ge, ie, me*

Question 48

True or False ## Footnote you can use a range of values in a MySQL REGEXP much like: **WHERE** last\_name **REGEXP**'[a-h]e'

Answer 48

True

Question 49

List some MySQL REGEXP?

Answer 49

**^** beginning **$** end **|** logical or (called a pipe) **[abcd]** **[a-f]** range

Question 50

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** first\_name **REGEXP**'elka|ambur'

Answer 50

all customers whose first name are Elka or Ambur

Question 51

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** last\_name **REGEXP**'ey$|on$'

Answer 51

all customers whose last name ends in ey or on.

Question 52

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** last\_name **REGEXP** ‘^my|se’

Answer 52

all customers whose last name starts with my or contains se.

Question 53

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** last\_name **REGEXP**'b[ru]'

Answer 53

all customers whose last name has a b followed by r or u

Question 54

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *WHERE** phone **IS NULL**

Answer 54

all the customers whose phones values are missing (null)

Question 55

What is this query returning? ## Footnote * *SELECT** \* * *FROM** orders * *WHERE** shipped\_date **IS NULL**

Answer 55

all orders that haven't shipped

Question 56

True or False the **ORDER BY** clause changes a table's default (primary key) sorting order

Answer 56

True by ascending order unless **DESC** clause is used.

Question 57

True or False This query is legal in MySQL * *SELECT** first\_name, last\_name * *FROM** customers * *ORDER BY** birth\_date

Answer 57

True ordering by a column not in the selection query!

Question 58

True or False Sorting data by columns positions is a good practice? * *SELECT** first\_name, last\_name * *FROM** customers * *ORDER** **BY** 1, 2

Answer 58

False if another column is added to the table, changing the order, you could be screwing yourself!

Question 59

This query is returning what? ## Footnote * *SELECT** \* * *FROM** customers * *ORDER BY** points **DESC** * *LIMIT** 3

Answer 59

the top three customers in desc order

Question 60

True or False the **LIMIT** clause should be the last line in a query if used.

Answer 60

True limits the # of records to return