Tezt 2

Question 1

By default, all duplicate values are included in an aggregate calculation unless you specify which keyword?

Answer 1

DISTINCT

Question 2

What do MIN and MAX correlate to in reference to non numeric data?

Answer 2

MIN is alphabetical order a-z
MAX is alphabetical order z-a

Question 3

Expressions coded in the HAVING clause__________________

can use either aggregate search conditions or non-aggregate search conditions

can use non-aggregate search conditions but can’t use aggregate search conditions

can use aggregate search conditions but can’t use non-aggregate search conditions

can refer to any column in the base table

Answer 3

can use either aggregate search conditions or non-aggregate search conditions

Question 4

A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn’t applied until after the grouping.

Answer 4

WHERE
HAVING

Question 5

How do WHERE clause expressions react to aggregate and non-aggregate search conditions

Answer 5

can use non-aggregate search conditions but can’t use aggregate search conditions

Question 6

These six clauses of a SELECT statement must be coded in which order?

SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING

Answer 6

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Question 7

A correlated subquery is one that _______________

Answer 7

is executed once for each row in the outer query

Question 8

A subquery is a/an ______________ statement that’s coded within another SQL statement.

ORDER BY

SELECT

FROM

WHERE

Answer 8

select

Question 9

16. A subquery can sometimes be restated as _______________

Answer 9

A join

Question 10

Which operator can you use to test whether any rows are returned by a subquery?

Answer 10

EXISTS

Question 11

Subqueries can be ________________ within other subqueries.

anchored

grandfathered

joined

nested

Answer 11

nested

Question 12

Which of the following statements deletes every row in the Vendors table?

DELETE Vendors WHERE ALL;

DELETE * Vendors;

DELETE ALL Vendors

DELETE Vendors;

Answer 12

DELETE Vendors;

Question 13

If you omit the WHERE clause from a DELETE statement __________

Show answer choices

all columns in the table will be deleted

all rows in the table will be deleted

only rows with null values will be deleted

the table definition will be deleted

Answer 13

all rows in the table will be deleted

Question 14

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the _________________ clause specifies which row or rows are to be updated.

WHERE

MERGE

HAVING

ORDER B

Answer 14

WHERE

Question 15

In an UPDATE statement, you can use a ________________ in the FROM clause if you need to specify column values or search conditions that depend on data from a table other than the one named in the UPDATE clause.

join

table

column

subquery

Answer 15

join

Question 16

When you use the SELECT INTO statement, the result set that’s defined by the SELECT statement is ________________ a new table.

copied into

deleted from

moved into

updated in

Answer 16

copied into

Question 17

If you use ________________ in the select list of a SELECT INTO statement, you must name the column since that name is used in the definition of the new table.

null values

calculated values

foreign keys

indexes

Answer 17

calculated values

Question 18

Write a SELECT statement that returns the LastName and JobTitle of each employee with a jobtitle other than ‘Engineer’ that has a SalaryWage greater than 65% of what the lowest paid employee with a jobTitle of engineer is paid.

Answer 18

SELECT LastName, JobTitle
FROM Employee
where SalaryWage > .65 *
(select min(salaryWage)
from Employee
where JobTitle = ‘Engineer’)

Question 19

Write a SELECT statement that returns the LastName and JobTitle of each employee that has a
SalaryWage greater than that of the highest paid employee with a jobTitle of “Programmer Analyst”.

Answer 19

SELECT LastName, JobTitle,
FROM Employee
WHERE SalaryWage >
(SELECT TOP 1 SalaryWage
FROM Employee
WHERE JobTitle like ‘Programmer Analyst’

Question 20

What is the command for deleting a table?

Answer 20

DROP TABLE (table)

Question 21

Difference between nchar and vchar?

Answer 21

Different charcater is an nchar(10) will always take up the same space. And supports unicode.

vchar(10) and then go, good, goo

All take up different amounts of space.

Question 22

syntax for insert into

Answer 22

INSERT INTO (new table)

SELECT (rows_needed)

FROM(table_taken

VALUES()

Make sure same amount for each

Question 23

Syntax for UPDATE

Answer 23

UPDATE talbe_name
SET Column

SET TermsID = 1

Question 24

Inner and Outer query?

Answer 24

Inner query is the stuff inside a subquery
Outer query is the otehr things