Session 2 - Creating, querying data

Question 1

What is the syntax for adding a column to the end of a table?

Answer 1

ALTER TABLE tableName

ADD columnName;

Question 2

What is the syntax for updating the column type?

Answer 2

ALTER TABLE tableName

ALTER COLUMN columnName datatype

Question 3

What is the DELETE Syntax?

Answer 3

DELETE
FROM
WHERE

Question 4

Syntax for adding Unique constraint to column

Answer 4

Alter table MyTable

ADD CONSTRAINT nameOfConstraint UNIQUE (columns)

Question 5

Syntax for adding a Default Constraint to a new table

Answer 5

columnName dataType (not) null CONSTRAINT constraintName DEFAULT defaultValue

Question 6

Syntax for adding a Default constraint to an existing column

Answer 6

ALTER TABLE tblName

ADD CONSTRAINT constraintName DEFAULT defaultValue FOR columnName

Question 7

Syntax for adding a check constraint but to not check the previous rows to verify that they validate the check

Answer 7

ALTER TABLE myTable WITH NOCHECK

ADD CONSTRAINT myConstraint CHECK (col1 = ‘test’)

Question 8

What is an identity?

Answer 8

Used with PRIMARY KEY and will automatically set a number value. IDENTITY(startingValue, incrementValue)

Question 9

What is a Primary Key?

Answer 9

Identifying value of a table. It will cluster (order) the table based on this value.
Not Nullable
One Per table
–Surrogate keys are typically numbers that have nothing to do with real life
–Natural Key (Such as Email Address) are naturally keys

Question 10

Syntax for creating Primary Key

Answer 10

CREATE TABLE tableName (
employeeNumber int CONSTRAINT constraintName PRIMARY KEY IDENTITY(startingValue, incrementValue)
)

Question 11

Difference between DELETE TABLE and TRUNCATE TABLE regarding Primary Key IDENTITY

Answer 11

DELETING all records allows IDENTITY to keep the next value and TRUNCATE TABLE starts the Identity over

Question 12

Syntax for adding Primary Key to existing column

Answer 12

ALTER TABLE tableName
ADD CONSTRAINT constraintName PRIMARY KEY (columnName)

You can add NONCLUSTERED if you don’t want it to be clustered (ordered)

ALTER TABLE tableName
ADD CONSTRAINT constraintName PRIMARY KEY NONCLUSTERED (columnName)

Question 13

What are the effects that you can set when changing a primary key that has foreign keys attached?

Answer 13

NO ACTION
CASCADE
SET NULL
SET DEFAULT

Question 14

what is NO ACTION for Primary Key change?

Answer 14

Nothing will change in the foreign keys referencing the primary key. Raises error

Question 15

what is CASCADE for Primary Key change?

Answer 15

Changes will reflect on the foreign keys columns

Question 16

what is SET NULL for Primary Key change?

Answer 16

The foreign key column will be set to null

Question 17

what is SET DEFAULT for Primary Key change?

Answer 17

It will be set to the default value specified

Question 18

How do you produce Sub Total Rows from query?

Answer 18

SELECT COL1, COL2, COL3
FROM TABLE
GROUP BY ROLLUP(COL1, COL2, COL3)

Question 19

How do you show if a column is grouped in a ROLLUP row?

Answer 19

SELECT COL1, COL2, COL3, GROUPING(COL1) AS COL1_GRP
FROM TABLE
GROUP BY ROLLUP(COL1, COL2, COL3)

WILL SHOW 1 IF COLUMN

Question 20

What are the two types of primary keys?

Answer 20

Surrogate keys are typically numbers that have nothing to do with real life
Natural Keys (Such as Email Address) are naturally keys

Question 21

How can you tell which columns are being grouped when using ROLLUP, CUBE OR GROUPING SETS?

Answer 21

USE GROUPING_ID(COL1, COL2, COL3)

The return will be binary from right to left:

COL3 grouped would be 1,
COL2 - 2
COL1 - 4

if COL1 and COL3 were grouped then the return value would be 5.

Question 22

How can you group according to sets specified?

Answer 22

use GROUP BY GROUPING SETS((COL1, COL2, COL3), (COL2, COL3), ())

*() denotes all columns

Question 23

What is the SQL-92 Syntax for joins?

Answer 23

That is the currently accepted standard:

SELECT *
FROM TABLE1 A
JOIN TABLE2 B
ON A.COL1 = B.COL1

Question 24

What is the SQL-89 Syntax for Joins

Answer 24

That is the older, legacy join syntax:

SELECT *
FROM TABLE1 A, TABLE2 B
WHERE A.COL1 = B.COL1

Question 25

What are the benefits of using SQL-92 for joins over SQL-89?

Answer 25

SQL-92 syntax is easier to understand
SQL-92 is the current best practice
SQL-92 is less error prone

Question 26

Is SQL-92 more efficient than SQL-89 for joining two or more tables?

Answer 26

No, there is no proven efficiency and it is no less code than SQL-89.