Unit 3

Question 1

SELECT * INTO Table

Answer 1

used for copy data to another.

Question 2

DELETE

Answer 2

remove row(s).

Question 3

TRUNCATE

Answer 3

remove all rows.

Question 4

DROP

Answer 4

remove table.

Question 5

UPDATE

Answer 5

change the values in row(s) for one table at a time. Used with SET and WHERE.

Question 6

SET

Answer 6

specific columns to contain specific values. Nested select statement can be used here to make that value and aggregate. Used with UPDATE.

Question 7

Which of these commands does not change the tables in a DB?
- INSERT
- DELETE
- UPDATE
- SELECT

Answer 7

SELECT

Question 8

LIKE

Answer 8

Used with the “%” character. Also used as NOT LIKE.

Question 9

SELECT CONCAT(attr1, attr2) FROM Table

Answer 9

Used to represent values of two attributes in one column.

Question 10

SELECT studentName AS Name FROM TABLE

Answer 10

Alias, used to label columns of results to clarify query results.

Question 11

UPPER(), LOWER()

Answer 11

change casing of results.

Question 12

LTRIM(), RTRIM()

Answer 12

take trailing spaces or specific characters off of values in column.

Question 13

LEFT(), RIGHT()

Answer 13

selecting only a part of the characters in values in column.

Question 14

CAST(attr1 AS INT)

Answer 14

Used to cast the datatypes of a column. Usually used with ROUND().

Question 15

WHERE empID IN (‘AJ01’, ‘GB01’)

Answer 15

Used to find a list of specific values in a column.

Question 16

Is CAST() the same as ROUND()

Answer 16

CAST() changes the datatype and chops off extra numbers instead of rounding, ROUND() rounds the value.

Question 17

ROUND(151.42) AS INT

Answer 17

Rounds the values to a less precise datatype.

Question 18

DATEDIFF(metric, attr1, attr2)

Answer 18

• Used to find difference between to dates according to a time metric.
• Ex: DATEDIFF(years, birthDate, GETDATE()) finds a person’s age in years.
• date2 - date1

Question 19

DATEADD(metric, metric added, attr1)

Answer 19

• Not used to edit values in DB; used to add values pulled from DB to format query.
• Ex: DATEADD(years, 1, hireDate) returns the 1 year anniversary for employee’s hire.
• Ex: DATEADD(months, -6, hireDate) returns the date 6 months before an employee was hired. DATEADD can be used to “DATESUBTRACT.”

Question 20

SELECT TOP 2 WITH TIES empID, firstName, lastName, salary

Answer 20

Returns even more than the top 2 values if the second highest ties with lower ranked values.

Question 21

What order is alphabetical ascending?

Answer 21

A to Z

Question 22

What order is numerical ascending?

Answer 22

0 to 100; increasing.

Question 23

OFFSET 3 rows

Answer 23

offsets query results. OFFSET 3 Rows: query results are everything omitting the top 3 rows that should have appeared without OFFSET; Used with FETCH NEXT

Question 24

FETCH NEXT 5 rows only;

Answer 24

Used with OFFSET; works with OFFSET; Finds n rows after OFFSET. In the above query it works with OFFSET to return rows 4, 5, 6, 7, 8.

Question 25

Boolean Order of Operations

Answer 25

Parenthesis
NOT
AND
OR

Question 26

CREATE Constraints

Answer 26

Not Null
Identity(start, increment)
Default _________
Unique
Check (attribute IN (“1”, “2”, “3”))

Question 27

CREATE Datatypes

Answer 27

Char(n)
Varchar(n)
Nvarchar(n),Nchar(n)
Int(n)
SmallInt(n)
TinyInt(n)
Date
Decimal(total(not including decimal point), nums after decimal)

Question 28

CREATE Refernence

Answer 28

Foreign Key (VINNo) References Car(VINNo)
Primary Key (BldgNum, UnitNum) references Booking (BldgNum, UnitNum)