SQL part 3

Question 1

order by clause can order by

Answer 1

column names or column indices

Question 2

t/f: with ORDER BY, you can use columns that have not been SELECT-ed

Answer 2

false

Question 3

column aliases

Answer 3

allows columns to be renamed
- fine-tuned output
- often used to assign name to computed column

Question 4

concat() operator + example

Answer 4

manipulates output data

select concat(x, y, z) as [new ouput]

Question 5

MS access uses ___ instead of concat()

Answer 5

&

Question 6

select distinct

Answer 6

removes duplicate values

Question 7

aggregate functions are applied to

Answer 7

an attribute or an expression

Question 8

examples of aggregate functions

Answer 8

min, max, sum, avg, count

Question 9

t/f: aggregate functions cannot be used directly in a where clause

Answer 9

true

Question 10

two options for COUNT

Answer 10

• count(attribute) / count(expression): returns number of non-NULL values
• count(*): returns total number of rows in resultset

Question 11

COUNT(DISTINCT attribute) is supported in

Answer 11

MySQL, not MS Access

Question 12

to replicate count(distinct), you can use

Answer 12

a subquery

Question 13

a normal SELECT pulls data from ______, while SELECT based on a subquery pulls data from _______

Answer 13

DB table (relation), a resultset or compares data to a single SELECTed value

Question 14

subquery format

Answer 14

select [columns]
from [tablename | (select…) ]
[where [criteria <=> (select…) ] ]

first (select) is table of values, second (select) is single value

Question 15

group by

Answer 15

used with aggregate functions, combines similar records and into a single record

Question 16

t/f: you must GROUP BY every SELECT-ed field that is not part of some aggregate function

Answer 16

true

Question 17

having clause

Answer 17

acts as a filter on the output of a group by clause. allows you to restrict results from aggregate fuctions

Question 18

action queries

Answer 18

insert, update, delete

Question 19

insert

Answer 19

insert a single record into a table

Question 20

insert format

Answer 20

insert into tableName
[ ( [Column1, Column2…] ) ]
values (value1 [, value2,…])

Question 21

update

Answer 21

change values within one or more rows in a table

Question 22

update format

Answer 22

update tablename
set column1=value [, column2=value2,…]
[where criteria (is true) ]

Question 23

delete

Answer 23

delete one or more rows from a table

Question 24

delete format

Answer 24

delete from table name
[where criteria (is true) ]

Question 25

t/f: delete statement does not always delete entire rows

Answer 25

false. it always deletes entire rows