Database Final Exam

Microsoft SQL Server is a [blank_start]one to many[blank_end] relational database system, which includes a [blank_start]one to one[blank_end] relationship as well.

The MDF file type holds the raw data for the database.

The [blank_start]LDF[blank_end] file type is the log file that holds transactions.

It is best practice for the MDF and LDF files to be stored on separate disk volumes if possible.

There are two types of indexes within SQL Server, one is [blank_start]clustered[blank_end], the other is [blank_start]non clustered[blank_end].

By default, the [blank_start]primary key[blank_end] is a clustered index.

Check off what is TRUE about a non clustered index within SQL Server:

Only one clustered index can exist on a single [blank_start]table[blank_end].

You can have as many non-clustered indexes in a table as you have [blank_start]columns[blank_end].

What type of SERVER-LEVEL roles will Microsoft SQL Server support? Check all that apply.

What type of DATABASE-LEVEL roles will Microsoft SQL Server support? Check all that apply.

Indexes are sorted using a [blank_start]Balanced Tree[blank_end].

Label the three levels in the image:

Assume we want to add a user called James into the B-Tree. What would have to be done?

When is the most ideal situation to use a B-Tree?

What is a "Commit"?

What is a "Checkpoint"?

A Simple Recovery model does not permanently keep transaction logs.

A Full Recovery model keeps the transaction logs, meaning they can be used in a restore.

The drawbacks of a Full Recovery model are: (check all that apply).

Which SQL statement will you use to add a table called tblFuck within your database? [blank_start]CREATE TABLE tblFuck[blank_end]

Match each Constraint with the appropriate Data Integrity Type: Default Constraint: [blank_start]Domain[blank_end] Check Constraint: [blank_start]Domain[blank_end] Foreign Key Constraint: [blank_start]Referential[blank_end] Unique Constraint: [blank_start]Entity[blank_end] Primary Key Constraint: [blank_start]Entity[blank_end]

Fill in the following SQL statement to show all items with a price ranging from and including $100, and ranging less than and including $200. [blank_start]SELECT[blank_end] Name [blank_start]from[blank_end] tblProducts [blank_start]WHERE[blank_end] Price [blank_start]>= 100[blank_end] [blank_start]AND[blank_end] Price [blank_start]<= 200[blank_end]

Add a column called Departments with varchar of 50 to the table tblStaff: [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] tblStaff [blank_start]ADD[blank_end] Departments [blank_start]varchar(50)[blank_end]

Remove a column called Departments from the tblStaff table. [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] tblStaff [blank_start]DROP[blank_end] [blank_start]COLUMN[blank_end] Departments

What is the proper naming convention for constraints? [blank_start]CK_ConstraintName[blank_end] - For CHECK Constraint [blank_start]PK_ConstraintName[blank_end] - For PRIMARY KEY Constraint [blank_start]FK_ConstraintName[blank_end] - For FOREIGN KEY Constraint [blank_start]DF_ConstraintName[blank_end] - For DEFAULT Constraint [blank_start]U_ConstraintName[blank_end] - For UNIQUE Constraint

A transaction started before and committed after a checkpoint. Then a system failure occurred. When the system comes back online, what would occur?

A transaction started and committed before a checkpoint. Then a system failure occurred. What is required?

A transaction started before the checkpoint. It never fully committed at the time of the crash. What action is required?

Fill in the blank so that only records where the Country is Canada are displayed. SELECT * FROM tblWhatever [blank_start]WHERE[blank_end] Country = [blank_start]'Canada'[blank_end]

Fill in the blanks for the statement so that it displays results from Detroit, USA. [blank_start]SELECT[blank_end] City, Country [blank_start]FROM[blank_end] Locations [blank_start]WHERE[blank_end] Country = [blank_start]'USA'[blank_end] [blank_start]AND[blank_end] City = [blank_start]'Detroit'[blank_end] [blank_start]ORDER[blank_end] [blank_start]BY[blank_end] Country, City

Fill in the statements so that it will return records where the country is either USA or Canada. [blank_start]SELECT[blank_end] City, Country [blank_start]FROM[blank_end] Locations [blank_start]WHERE[blank_end] Country = [blank_start]'USA'[blank_end] [blank_start]OR[blank_end] Country = [blank_start]'Canada'[blank_end] ORDER BY Country, City

Fill in the blanks so that tblStaff and tblDept are joined by the Name column. [blank_start]SELECT[blank_end] [blank_start]tblStaff.Name[blank_end], [blank_start]tblDept.Name[blank_end] [blank_start]FROM[blank_end] [blank_start]tblStaff[blank_end] [blank_start]JOIN[blank_end] [blank_start]tblDept[blank_end] [blank_start]ON[blank_end] [blank_start]tblStaff.ID_Dept[blank_end] [blank_start]=[blank_end] [blank_start]tblDept.ID_Dept[blank_end]

Fill in the blank to complete the SQL statement; so that it will return the average unit price. [blank_start]SELECT[blank_end] [blank_start]avg[blank_end]([blank_start]unitprice[blank_end]) [blank_start]FROM[blank_end] [blank_start]tblOrders[blank_end]

Write a SQL statement that will return the number of clients who live in London. [blank_start]SELECT[blank_end] [blank_start]COUNT[blank_end]([blank_start]*[blank_end]) [blank_start]FROM[blank_end] [blank_start]tblLocations[blank_end] [blank_start]WHERE[blank_end] [blank_start]City[blank_end] [blank_start]=[blank_end] [blank_start]'London'[blank_end]

Complete the SQL statement shown below so that you will show the total with 13% tax on the Cost and Quantity in a column labeled 'Total'. [blank_start]SELECT[blank_end] [blank_start]Cost[blank_end] [blank_start]*[blank_end] [blank_start]Quantity[blank_end] [blank_start]*[blank_end] [blank_start]1.13[blank_end] [blank_start]'Total'[blank_end] [blank_start]FROM[blank_end] [blank_start]tblOrders[blank_end]

Will this query run successfully? SELECT FirstName, LastName FROM tblEmployees GROUP BY FirstName

Will this query run successfully? SELECT ProductName, SUM(UnitPrice), SUM(Cost), UnitPrice * Cost FROM Orders GROUP BY ProductName

What is true about views? Select all that apply.

[blank_start]ALTER[blank_end] [blank_start]VIEW[blank_end] [blank_start]vEmployees[blank_end] [blank_start]AS[blank_end] [blank_start]Select[blank_end] [blank_start]ID_EMP[blank_end], [blank_start]FirstName[blank_end], [blank_start]LastName[blank_end] [blank_start]FROM[blank_end] [blank_start]Employees[blank_end]

What is true about stored procedures? Check all that apply.

Fill in the blanks that will complete the SQL statement used to create a stored procedure named USP_ViewEmp. [blank_start]CREATE[blank_end] [blank_start]PROCEDURE[blank_end] [blank_start]USP_ViewEmp[blank_end] [blank_start]AS[blank_end] [blank_start]BEGIN[blank_end] SELECT FirstName, LastName FROM tblEmployees SELECT DeptName FROM tblDepartments [blank_start]END[blank_end] [blank_start]GO[blank_end]

Which of the following help ensure data integrity? Select all that apply.

What is the command to alter a table to add a constraint to the salary column in the employees table so the max salary is $100,000? Ensure you use proper naming conventions. [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] Employee [blank_start]ADD[blank_end] [blank_start]CONSTRAINT[blank_end] [blank_start]CK_Salary[blank_end] [blank_start]CHECK[blank_end] ([blank_start]Salary[blank_end] [blank_start]<=[blank_end] [blank_start]100000[blank_end])

Assuming proper naming convention was followed. What is the SQL statement to temporarily disable the check constraint for the Salary column in the employees table? [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] [blank_start]Employees[blank_end] [blank_start]NOCHECK[blank_end] [blank_start]CONSTRAINT[blank_end] [blank_start]CK_Salary[blank_end]

When creating a database using SQL statements, type the line for the option to specify a data file in this location: C:\DATA\Acme.mdf. Ensure you put a space before and after the equal sign. [blank_start]FILENAME = 'C:\DATA\Acme.mdf'[blank_end] Only write out the single line for this option.

When creating a database using a SQL command, what is the line that would specify the option to auto grow the data file by 50%? [blank_start]FILEGROWTH = 50%[blank_end]

Create a table named tblStaff with the following columns: • ID_Staff INT PRIMARY KEY • FirstName varchar(50) Do not allow null values • LastName varchar(50) Do not allow null values • Extension varchar(4) Allow null values [blank_start]CREATE[blank_end] [blank_start]TABLE[blank_end] tblStaff ( [blank_start]ID_Staff[blank_end] [blank_start]INT[blank_end] [blank_start]PRIMARY[blank_end] [blank_start]KEY[blank_end], [blank_start]FirstName[blank_end] [blank_start]varchar(50)[blank_end] [blank_start]NOT NULL[blank_end], [blank_start]LastName[blank_end] [blank_start]varchar(50)[blank_end] [blank_start]NOT NULL[blank_end], [blank_start]Extension[blank_end] [blank_start]varchar(4)[blank_end] )

Below is a snippet of a Create Table SQL statement. ID_Staff int Re-write this line so it has an identity specification with a seed of 1 and an increment of 1. [blank_start]ID_Staff int IDENTITY (1, 1)[blank_end]

What is the SQL statement to alter the table tblStaff to add a column named Departments with a datatype of varchar(50)? [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] tblStaff [blank_start]ADD[blank_end] [blank_start]Departments[blank_end] [blank_start]varchar(50)[blank_end]

What is the SQL Statement to remove the column named Departments from tblStaff? [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] tblStaff [blank_start]DROP[blank_end] [blank_start]COLUMN[blank_end] [blank_start]Departments[blank_end]

What is the SQL Statement to change the data type of the column firstname from varchar(50) to varchar(25) in tblStaff? [blank_start]ALTER[blank_end] [blank_start]TABLE[blank_end] [blank_start]tblStaff[blank_end] [blank_start]ALTER[blank_end] [blank_start]COLUMN[blank_end] [blank_start]firstname[blank_end] [blank_start]varchar(25)[blank_end]

What is the SQL Statement to delete the table named tblStaff from Acme database? [blank_start]DROP TABLE tblStaff[blank_end]