Copy and Edit

Database Final Exam

Description

Database Final Exam
dbm
Quiz by dbm, updated more than 1 year ago
dbm
Created by dbm almost 9 years ago
475
1
0

Resource summary

Question 1

Question
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.
Answer
  • one to many
  • one to one

Question 2

Question
The MDF file type holds the raw data for the database.
Answer
  • True
  • False

Question 3

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

Question 4

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

Question 5

Question
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].
Answer
  • clustered
  • non clustered

Question 6

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

Question 7

Question
Check off what is TRUE about a non clustered index within SQL Server:
Answer
  • Creates an index of the column OUTSIDE of the table.
  • A RID (RecordID) is stored in the index and is used to point back to a specific record within the table.
  • A non clustered index can be sorted without resorting the rest of the columns within the table.
  • A non clustered index is created by default.

Question 8

Question
Only one clustered index can exist on a single [blank_start]table[blank_end].
Answer
  • table
  • column
  • row

Question 9

Question
You can have as many non-clustered indexes in a table as you have [blank_start]columns[blank_end].
Answer
  • columns
  • rows
  • tables

Question 10

Question
What type of SERVER-LEVEL roles will Microsoft SQL Server support? Check all that apply.
Answer
  • sysadmin
  • serveradmin
  • securityadmin
  • dbcreator
  • public
  • db_owner
  • db_datareader

Question 11

Question
What type of DATABASE-LEVEL roles will Microsoft SQL Server support? Check all that apply.
Answer
  • db_owner
  • db_securityadmin
  • db_datareader
  • db_backupoperator
  • db_datawriter

Question 12

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

Question 13

Question
Label the three levels in the image:
Image:
e9224f07-b436-45e6-bc6a-0bc305725c85 (image/png)
Answer
  • Root
  • Intermediate
  • Leaf

Question 14

Question
Assume we want to add a user called James into the B-Tree. What would have to be done?
Image:
5169c1a7-e4f3-4665-b29b-9c4c187ff0b3 (image/png)
Answer
  • Perform a page split then add James
  • Add James after Hart with no page split
  • Add James at the intermediate level
  • Start a new B-Tree

Question 15

Question
When is the most ideal situation to use a B-Tree?
Answer
  • If you have a table with lots of updates occurring.
  • If you have a table with many reads but very few writes.
  • If you have a table column containing multiple records with the same data.

Question 16

Question
What is a "Commit"?
Answer
  • The data cached on the SQL server is saved to the transaction log file (the LDF file).
  • Data is cached onto the SQL server when changes in the database are made.
  • Any change that is made to a table within your database.

Question 17

Question
What is a "Checkpoint"?
Answer
  • Writes committed transactions to the database.
  • A read-only buffer cache.
  • Modifies records within tables.

Question 18

Question
A Simple Recovery model does not permanently keep transaction logs.
Answer
  • True
  • False

Question 19

Question
A Full Recovery model keeps the transaction logs, meaning they can be used in a restore.
Answer
  • True
  • False

Question 20

Question
The drawbacks of a Full Recovery model are: (check all that apply).
Answer
  • They take up more disk space than a Simple Recovery
  • When the transaction log file becomes full the SQL server will stop working
  • You need RAID5 to use the Full Recovery model.

Question 21

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

Question 22

Question
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]
Answer
  • Domain
  • Referential
  • Entity
  • Domain
  • Referential
  • Entity
  • Referential
  • Domain
  • Entity
  • Entity
  • Domain
  • Referential
  • Entity
  • Domain
  • Referential

Question 23

Question
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]
Answer
  • SELECT
  • from
  • WHERE
  • >= 100
  • AND
  • <= 200

Question 24

Question
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]
Answer
  • ALTER
  • ADD
  • varchar(50)
  • TABLE

Question 25

Question
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
Answer
  • ALTER
  • TABLE
  • DROP
  • COLUMN

Question 26

Question
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
Answer
  • CK_ConstraintName
  • PK_ConstraintName
  • FK_ConstraintName
  • DF_ConstraintName
  • U_ConstraintName
  • CK_ConstraintName
  • PK_ConstraintName
  • FK_ConstraintName
  • DF_ConstraintName
  • U_ConstraintName
  • CK_ConstraintName
  • PK_ConstraintName
  • FK_ConstraintName
  • DF_ConstraintName
  • U_ConstraintName
  • CK_ConstraintName
  • PK_ConstraintName
  • FK_ConstraintName
  • DF_ConstraintName
  • U_ConstraintName
  • CK_ConstraintName
  • PK_ConstraintName
  • FK_ConstraintName
  • DF_ConstraintName
  • U_ConstraintName

Question 27

Question
A transaction started before and committed after a checkpoint. Then a system failure occurred. When the system comes back online, what would occur?
Answer
  • A roll forward
  • A roll back
  • A system failure
  • No action would be required

Question 28

Question
A transaction started and committed before a checkpoint. Then a system failure occurred. What is required?
Answer
  • No action is required.
  • A roll back.
  • A roll forward.
  • The data is corrupt.

Question 29

Question
A transaction started before the checkpoint. It never fully committed at the time of the crash. What action is required?
Answer
  • A roll back.
  • A roll forward
  • A system failure occurred so there's nothing that can be done

Question 30

Question
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]
Answer
  • WHERE
  • 'Canada'

Question 31

Question
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
Answer
  • SELECT
  • FROM
  • WHERE
  • 'USA'
  • AND
  • 'Detroit'
  • ORDER
  • BY

Question 32

Question
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
Answer
  • SELECT
  • FROM
  • WHERE
  • 'USA'
  • OR
  • 'Canada'

Question 33

Question
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]
Image:
5b390d0c-3f6c-47ae-a1fb-0f6ec8de8f7e (image/png)
Answer
  • SELECT
  • tblStaff.Name
  • tblDept.Name
  • FROM
  • tblStaff
  • JOIN
  • tblDept
  • ON
  • tblDept.ID_Dept
  • tblStaff.ID_Dept
  • =

Question 34

Question
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]
Image:
e2c64c4e-5986-4026-bf62-eab0b002966a (image/png)
Answer
  • SELECT
  • avg
  • unitprice
  • FROM
  • tblOrders

Question 35

Question
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]
Image:
7cc37161-7e28-4921-b35b-7e5c42a91bd6 (image/png)
Answer
  • SELECT
  • COUNT
  • *
  • FROM
  • WHERE
  • 'London'
  • City
  • =
  • tblLocations

Question 36

Question
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]
Image:
27b65c40-6906-40dd-9053-4cebafb2b4a6 (image/png)
Answer
  • SELECT
  • Cost
  • *
  • Quantity
  • *
  • 1.13
  • 'Total'
  • FROM
  • tblOrders

Question 37

Question
Will this query run successfully? SELECT FirstName, LastName FROM tblEmployees GROUP BY FirstName
Answer
  • This will fail, as LastName is neither an aggregate function or included in the group by.
  • There is no problem with this query.
  • This will fail as the Group By clause is incorrect.
  • This will fail as FirstName is neither an aggregate function or included in the group by.

Question 38

Question
Will this query run successfully? SELECT ProductName, SUM(UnitPrice), SUM(Cost), UnitPrice * Cost FROM Orders GROUP BY ProductName
Answer
  • This will fail as SUM(UnitPrice) and SUM(Cost) are aggregate functions and cannot be used with a Group By Clause.
  • This command will run successfully.
  • This will fail as ProductName cannot be used with the Group By Clause.
  • This will fail as UnitPrice * Cost is not an aggregate function and cannot be used with a Group By Clause.

Question 39

Question
What is true about views? Select all that apply.
Answer
  • You can insert data into a view.
  • You cannot join a table and a view together.
  • You can use a view to denormalize data.
  • You can define permissions for a view.

Question 40

Question
[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]
Image:
e811ef62-7147-4755-85ac-12bbd16b22a8 (image/png)
Answer
  • ALTER
  • VIEW
  • vEmployees
  • AS
  • Select
  • ID_EMP
  • FirstName
  • LastName
  • FROM
  • Employees

Question 41

Question
What is true about stored procedures? Check all that apply.
Answer
  • You cannot alter (modify) a stored procedure.
  • A Stored Procedure can contain multiple SQL Statements.
  • You must use a BEGIN and END around your SQL statements within a stored procedure if you have multiple SQL statements.
  • Applications can use stored procedures to prevent against SQL injection
  • You must use a GO between each SQL statement.

Question 42

Question
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]
Answer
  • CREATE
  • PROCEDURE
  • USP_ViewEmp
  • AS
  • BEGIN
  • END
  • GO

Question 43

Question
Which of the following help ensure data integrity? Select all that apply.
Answer
  • Data Type
  • Identity Specification
  • Allowing/Not Allowing Null values
  • Constraints

Question 44

Question
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])
Answer
  • ALTER
  • TABLE
  • ADD
  • CONSTRAINT
  • CK_Salary
  • CHECK
  • Salary
  • <=
  • 100000

Question 45

Question
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]
Answer
  • ALTER
  • TABLE
  • Employees
  • NOCHECK
  • CONSTRAINT
  • CK_Salary

Question 46

Question
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.
Answer
  • FILENAME = 'C:\DATA\Acme.mdf'

Question 47

Question
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]
Answer
  • FILEGROWTH = 50%

Question 48

Question
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] )
Answer
  • CREATE
  • TABLE
  • ID_Staff
  • INT
  • PRIMARY
  • KEY
  • FirstName
  • varchar(50)
  • NOT NULL
  • LastName
  • varchar(50)
  • NOT NULL
  • varchar(4)
  • Extension

Question 49

Question
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]
Answer
  • ID_Staff int IDENTITY (1, 1)

Question 50

Question
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]
Answer
  • ALTER
  • TABLE
  • ADD
  • Departments
  • varchar(50)

Question 51

Question
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]
Answer
  • ALTER
  • TABLE
  • DROP
  • COLUMN
  • Departments

Question 52

Question
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]
Answer
  • ALTER
  • TABLE
  • ALTER
  • COLUMN
  • tblStaff
  • firstname
  • varchar(25)

Question 53

Question
What is the SQL Statement to delete the table named tblStaff from Acme database? [blank_start]DROP TABLE tblStaff[blank_end]
Answer
  • DROP TABLE tblStaff
Show full summary Hide full summary

Want to create your own Quizzes for free with GoConqr? Learn more.

Similar

FLAT FILE VS RELATIONAL DATABASE
rosiejones
Midterm 2 (Chapter 5 - 13)
Yorria Raine
SQL Quiz
Chris Cronin
Databaser - Introduksjon
B K
OCR gcse computer science
Jodie Awthinre
GCSE AQA Computer Science - Definitions
James Jolliffe
Midterm 1
Yorria Raine
SQL Quiz
R M
SQL 1: Databases (Module 3)
aries cantos
Managing Digital Data Review
Shannon Anderson-Rush
GCSE AQA Computer Science - Definitions
moffat00
Browse Library