SQL part 2

Question 1

2 primary command types for data manipulation language (DML)

Answer 1

data retrieval (“standard” queries) and action queries

Question 2

example of data retrieval query in DML

Answer 2

select

Question 3

example of DML action queries

Answer 3

insert, update, delete

Question 4

general format for select statement

Answer 4

SELECT [DISTINCT | ALL] {* | column1 [AS new_name] }
FROM table_name [alias]
[WHERE conditions]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]

Question 5

for the select statement format, can you do both for the [DISTINCT | ALL] statement

Answer 5

no

Question 6

which of the [] {} are required, and which are optional

Answer 6

{} is required, [] optional

Question 7

“*” is called a

Answer 7

wildcard

Question 8

where clause is a ______ and includes the expressions:

Answer 8

conditional filter,
- arithmetic operators
- logical operators
- clauses

Question 9

logical operators

Answer 9

and, or, not

Question 10

clauses

Answer 10

between, like, in

Question 11

you select WHAT from WHAT

Answer 11

column names from table name

Question 12

“*” can replace

Answer 12

list of column names (lists entire table/full details)

Question 13

to use the where clause to make a comparison between a field/attribute and a value, you must

Answer 13

use the correct syntax to indicate the (matching) data types
- creditlimit < 25
- hiredate = “2017-01-01”

Question 14

using the where clause, you combine instances of _________ into _______

Answer 14

arithmetic comparisons, logical statements

Question 15

what where clause checks to see if something does not equal a value

Answer 15

…WHERE NOT (X = “Thomas”)

Question 16

using BETWEEN in a where clause is used with _____ to _______

Answer 16

logical operator AND, specify a range of values

…WHERE X BETWEEN Y AND Z

Question 17

using where clause, IN can be used to ______
(plus example)

Answer 17

combine logical statements separated by ORs

instead of:
…WHERE (X = ‘first’) OR (X = ‘last’)

do:
…WHERE X IN (‘first’, ‘last’)

Question 18

t/f: where clause can identify NULL values

Answer 18

true
(where x is/is not null)

Question 19

using where clause, LIKE gives the ability to

Answer 19

do simple pattern matching

Question 20

LIKE pattern always expressed as

Answer 20

string

Question 21

with LIKE, MS Access uses

Answer 21

different characters than standard SQL

Question 22

using LIKE to match zero or more characters in MySQL and Access

Answer 22

MySQL: %
Access: *

Question 23

using LIKE to match exactly one character in MySQL and Access

Answer 23

MySQL: __
Access: #

Question 24

using % for like

Answer 24

…WHERE Emp_LastName LIKE “Z%”

Question 25

using ___ for like

Answer 25

...WHERE Emp_Phone LIKE "___9"

Question 26

where clause: RLIKE

Answer 26

- based on regular expressions - uses either RLIKE or REGEXP

Question 27

t/f: LIKE operator more powerful than RLIKE

Answer 27

false. RLIKE more powerful

Question 28

t/f: RLIKE is available in Access

Answer 28

false.

Question 29

fundamental difference between like and rlike

Answer 29

- LIKE matches entire pattern against an attribute value and returns true if they are exactly the same - RLIKE looks for the pattern within the attribute value and returns true if that pattern exists anywhere within it

Question 30

some RLIKE patterns

Answer 30

. - matches any single character t* - matches zero of more instances of letter t t+ - matches one or more instances of the letter t t? - matches zero or one instance of letter t ^A - matches given pattern at beginning of string e$ - matches preceding pattern at beginning of string [A-T] - matches a range of characters [a-dx] - matches any character that is a, b, c, d, or x