Database Final Exam

1

Microsoft SQL Server is a relational database system, which includes a relationship as well.

1

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

1

The file type is the log file that holds transactions.

1

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

1

There are two types of indexes within SQL Server, one is , the other is .

1

By default, the is a clustered index.

1

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

1

Only one clustered index can exist on a single ( table, column, row ).

1

You can have as many non-clustered indexes in a table as you have ( columns, rows, tables ).

1

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

1

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

1

Indexes are sorted using a .

1

Label the three levels in the image:

1

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

1

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

1

What is a "Commit"?

1

What is a "Checkpoint"?

1

A Simple Recovery model does not permanently keep transaction logs.

1

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

1

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

1

Which SQL statement will you use to add a table called tblFuck within your database?

1

Match each Constraint with the appropriate Data Integrity Type:

Default Constraint: ( Domain, Referential, Entity )
Check Constraint: ( Domain, Referential, Entity )
Foreign Key Constraint: ( Referential, Domain, Entity )
Unique Constraint: ( Entity, Domain, Referential )
Primary Key Constraint: ( Entity, Domain, Referential )

1

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.

Name tblProducts Price Price

1

Add a column called Departments with varchar of 50 to the table tblStaff:

tblStaff Departments

1

Remove a column called Departments from the tblStaff table.

tblStaff Departments

1

What is the proper naming convention for constraints?

( CK_ConstraintName, PK_ConstraintName, FK_ConstraintName, DF_ConstraintName, U_ConstraintName ) - For CHECK Constraint
( CK_ConstraintName, PK_ConstraintName, FK_ConstraintName, DF_ConstraintName, U_ConstraintName ) - For PRIMARY KEY Constraint
( CK_ConstraintName, PK_ConstraintName, FK_ConstraintName, DF_ConstraintName, U_ConstraintName ) - For FOREIGN KEY Constraint
( CK_ConstraintName, PK_ConstraintName, FK_ConstraintName, DF_ConstraintName, U_ConstraintName ) - For DEFAULT Constraint
( CK_ConstraintName, PK_ConstraintName, FK_ConstraintName, DF_ConstraintName, U_ConstraintName ) - For UNIQUE Constraint

1

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

1

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

1

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

1

Fill in the blank so that only records where the Country is Canada are displayed.

SELECT *
FROM tblWhatever
Country =

1

Fill in the blanks for the statement so that it displays results from Detroit, USA.

City, Country
Locations
Country = City =
Country, City

1

Fill in the statements so that it will return records where the country is either USA or Canada.

City, Country
Locations
Country = Country =
ORDER BY Country, City

1

Fill in the blanks so that tblStaff and tblDept are joined by the Name column.

,

1

Fill in the blank to complete the SQL statement; so that it will return the average unit price.

()

1

Write a SQL statement that will return the number of clients who live in London.

()

1

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'.

1

Will this query run successfully?

SELECT FirstName, LastName
FROM tblEmployees
GROUP BY FirstName

1

Will this query run successfully?

SELECT ProductName, SUM(UnitPrice), SUM(Cost), UnitPrice * Cost
FROM Orders
GROUP BY ProductName

1

What is true about views? Select all that apply.

1

, ,

1

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

1

Fill in the blanks that will complete the SQL statement used to create a stored procedure named USP_ViewEmp.

SELECT FirstName, LastName FROM tblEmployees
SELECT DeptName FROM tblDepartments

1

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

1

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.

Employee

( )

1

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?

1

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.

Only write out the single line for this option.

1

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%?

1

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

tblStaff
(
,
,
,

)

1

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.

1

What is the SQL statement to alter the table tblStaff to add a column named Departments with a datatype of varchar(50)?

tblStaff

1

What is the SQL Statement to remove the column named Departments from tblStaff?

tblStaff

1

What is the SQL Statement to change the data type of the column firstname from varchar(50) to varchar(25) in tblStaff?

1

Database Final Exam

Database Final Exam

Microsoft SQL Server is a relational database system, which includes a relationship as well.

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

The 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 , the other is .

By default, the 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 table column row( table, column, row ).

You can have as many non-clustered indexes in a table as you have columns rows tables( columns, rows, tables ).

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 .

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).

Only one clustered index can exist on a single ( table, column, row ).

You can have as many non-clustered indexes in a table as you have ( columns, rows, tables ).

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.

Name tblProducts Price Price

Add a column called Departments with varchar of 50 to the table tblStaff:

tblStaff Departments

Remove a column called Departments from the tblStaff table.

tblStaff Departments

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
Country =

Fill in the blanks for the statement so that it displays results from Detroit, USA.

City, Country
Locations
Country = City =
Country, City

Fill in the statements so that it will return records where the country is either USA or Canada.

City, Country
Locations
Country = Country =
ORDER BY Country, City

Fill in the blanks so that tblStaff and tblDept are joined by the Name column.

,

Fill in the blank to complete the SQL statement; so that it will return the average unit price.

()

Write a SQL statement that will return the number of clients who live in London.

()

Will this query run successfully?

SELECT FirstName, LastName
FROM tblEmployees
GROUP BY FirstName