Database

SQL Categories:The Data Manipulation Language (DML) is NOT all about the rows in the table

• True

• False

• ...

• ...

Create Table:Which of the following are typically found on a separate line (or separated by a comma) when creating the table

Note: Multiple answers may apply

• The 3-part column definition

• A table constraint for the Primary Key

• A column "constraint" like Identity or Default

• A table constraint for the Foreign Key

Drop vs. Truncate:

When used on a table, what's the difference between the Drop and Truncate statements

Answer explanation

Truncate removes data but keeps structure. Drop removes data and selected structure.

Create Table:

Create an SQL statement that will create the table from this following relational notation

Note: Include the 3-part column notation and PK

Inventory (PartNumber, Description)

Answer explanation

create table Inventory (

 PartNumber int not null,

 Description nvarchar(50) not null,

 Constraint InventoryPK Primary Key(PartNumber),

)

Identity:

The "Identity" keyword is used to designate that this column is a Primary Key

• True

• False

• k

• k

Constraint Actions:

Consider an scenario where the FK constraint is using the "No Action" option. Describe what happens if you attempt to insert data into a child table without having a matching column in the parent

Answer explanation

Error message, this is not allowed as there is nothing to refer to. Fail.

Cascade:

Consider a typical 1:M relationship with a foreign key constraint that has "on delete cascade". Describe what happens if you delete a row on the "1" side that has matching rows on the "M" side

Answer explanation

Line the parent up and all the kids. 

Fire Away!!! LOL

Autonumber:

It is not considered good practice to use the autonumber/synthetic/surrogate feature on a Foreign Key

• True

• False

• l

• l

Create Default:

Create an SQL statement that will create the table from the following relational notation. Include a default with the value of TX for the State

Customer (CustID, City, State)

Answer explanation

Create table Customer (

 CustID int not null, 

City nvarchar(25) not null,

 State nchar(2) not null default 'TX',

Constraint CustPK PrimaryKey(CustID),

)

Create Column Constraint:

Create an SQL statement that will create the table from this following relational notation. Include a constraint to make sure the value for grade is not below 0 or greater than 100

Grades (StudentID, QuizNum, Grade)

Answer explanation

Create table Grades (

 StudentID int not null, 

QuizNum int not null,

 Grade int not null,

 Constraint GradesPK PrimaryKey(StudentID, QuizNum),

 Constraint GradeQuizCK Check (Grade between 0 and 100)

)