WorksheetsDatabase
Total questions: 64
Worksheet time: 2hrs 35mins
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
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)
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
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
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)
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)
Constraints:
After a table has been built, you can not alter the table to add or remove a constraint
True
False
k
k
Insert Statement:
Create an SQL statement to insert the following information into the table described below
Customer (CustID, Name)
Multiple Inserts:
When inserting multiple rows of data with a single insert statement, you only need to use the Values statement once
True
False
k
k
Insert Shortcuts:
When using the Insert statement, you are allowed to skip the "column clause" under which of the following circumstances
Note: Multiple answers may apply
There are no Surrogate columns
When there are multiple column/value pairs
The number of values exactly matches the number of columns
The values are in the same order as the columns
Update Statement:
Describe what happens if you forget to use the Where clause. For example, with this statement (that does have a Where)
Update Employee set DeptID=10 Where EmpID=12
Deleting Rows:
What was the practical guidance for configuring a table so you won't have to delete any rows
Views:
Consider the scenario where you created a View called vEmp. You would typically use this view in an SQL statement where the view appears "as if" it was a table.
For example:
Select * from vEmp
True
False
k
k
View vs. Function:
According to lecture, what is the major feature difference between a View and a table Function
DML statements
Which of the following DML statements appear to be using the correct syntax
Note: Multiple answers may apply
Update EmployeeSet Dept='Engineering'Where EmpID=1234
Delete from Employeewhere EmpID=1234
Drop from Employeewhere EmpID=1234
Truncate from Employeewhere EmpID=1234
Index:
Explain why the performance of the Insert command is not influenced by the creation of an index
Database Redesign:
According to lecture, which of the following are the common reasons for performing a database redesign
Note: Multiple answers may apply
You merged with another company and you need to merge your data
You need to address some long-term database issues
You are migrating to a new database product
You want to gather some statistics on the performance of your database
Correlated Subquery:
Describe in your own words how a correlated subquery is different from a normal subquery.
Note: I'm not asking for a definition. Read the question!
Diagram vs. Logical Design:
What parts of a logical design are not in evidence when looking at an SQL Server Management Studio's "Database Diagram"
Note: Multiple answers may apply
The table names
The candidate keys
The column properties (data type, null vs not null)
Some of the cardinality items
Use of Dependency Graph:
Which of the following is the best reason for looking at the Dependency Graph of an existing database before populating the database with data
Note: I'm not talking about the Database Diagram
To be able to see the foreign key relationships
To be able to tell which tables need to be filled first
To be able to see the maximum cardinality
To be able to spot errors in cardinality
Rename Table:
When renaming a table via the Microsoft SQL Server Management Studio, the system will automatically find and update all of the views, functions, and stored procedures in the database to that table with the new name
True
False
l
l
Change Identity
You can use the "Alter Table/Alter Column" technique to add an "Identity" to an existing column
T
F
Defaults:
In Microsoft SQL Server, if you add a new column with a default constraint to an existing table (with existing data), all of the rows will be automatically populated with the default value for that column only if the column is also marked NOT NULL
True
False
Add a Not Null Column:
What are the steps to add a new column to an existing table (with existing data) that has a "not null" column constraint
Drop Column:
To drop a column that is part of a key you must first remove the constraints that apply to that key
True
False
Data Conversion:
Changing the data type of a column from an integer to a string will automatically perform a data type conversion (provided there are no errors)
True
False
Configuration Control
Describe the fundamental purpose for using a "configuration control board" before making change to a databaseNote: Purpose != What it does
The best practices for solving a concurrency issue is to temporarily put a lock on the entire database
True
False
Explain the significance of having an "atomic transaction" in a financial system (for example transferring money from one account to another)
Note: I'm not asking for a definition
Which of the following statements about locks are true
Note: Multiple answers may apply
Explicit locks use the "concurrent" keyword
Implicit locks require that you provide a "hint" as to your intentions
The granularity of the lock should be at the smallest point that provides the required protection
If there is an exclusive lock, no other user can gain access to that resource
If there is a shared lock, other users can read (but not write) to that resource
Pessimistic Concurrency does a better job of protecting the database than Optimistic Concurrency
True
False
Atomic:
What SQL keyword makes sure that a group of statements is executed together a single "atomic" statement
Lock
Lock
With
Transaction
Kumquat
Describe why you should always set permissions by role (or group) rather than to individual users
The issue of allowing a user to edit their own Employee record (but not be able to see other people's records) can be accomplished using just DCL statements
True
False
Describe the cartoon that was used in lecture to describe a SQL Injection attack
Backup vs. Recovery
Describe why nobody cares how long it takes to do a backup
SQL Server:
Which of the following are true of SQL Server 2019Note: Multiple answers may apply
There is a Linux version
It is 64 bit only
It is supported on Windows 7
It comes bundled with the SQL Server Management Studio (so you don't have to download it separately)
Microsoft Azure:
In a traditional Azure SQL Database, the administrator does not have control over the database instance
True
False
Server Instance:
A single windows server can have multiple SQL Server instances
True
False
Hierarchy:
Put the following items of the data hierarchy in the proper order (starting with the top of the pyramid)
Cluster, SQL Server Instance, Database, Table, Rows
SQL Server Instance, Cluster, Database, Tables, Rows,
SQL Server Instance, Cluster, Database, Rows, Tables
Cluster, SQL Server Instance, Database, Rows, Tables
Authentication:
Describe a scenario where you must use SQL Authentication (instead of Windows Authentication)
SSMS Current Database:
Using the SQL Server Management Studio, how can you tell which database that you're currently "talking to"
Logins, Users, and Roles:
Which of following statements are true about Logins, Users, and Roles
Note: Multiple answers may apply
Logins are stored in the service instance
Users are stored in the database
A single service instance can have many identical logins
A single database can have many identical users
A single database user can have many roles
Backup Types:
Which of the following are the correct types of backups for Microsoft SQL Server
Note: Multiple answers may apply
Full
Differential
Incremental
Log
Image
Full Backup:
A Full backup includes information from both the data "file" and the entire "log" file
True
False
Backup Location:
Explain why the default location for the backups is not ideal
What recovery model is best for a temporary database
Simple
Full
Bulk Logged
Complex
What recovery model is best for a read only database
Simple
Full
Bulk Logged
Complex
I can recover to the point of the last database backup
Simple
Full
Bulk Logged
Complex
I can recover to the point of the last log backup
Simple
Full
Bulk Logged
Complex
I can recover to any point in time covered by a log backup
Simple
Full
Bulk Logged
Complex
What action is required for the database log files to "roll over"
Log Backup
Full Backup
Partial Backup
Simple Backup
Maintenance Plans:
The scheduled maintenance plans in SQL Server can be viewed in the Windows Task Scheduler
True
False
SQL Server File Extensions:
What is the file extension of the Data file used by SQL Server
(a)
Copy Database Files:
Which of the following are true about your ability to copy the physical database files during normal operations
Note: Multiple answers may apply
When the service is running, the files are locked for all "attached" databases
You need administrator rights, since the folder is not accessible to ordinary users
The database administrator can copy the files without changing permissions
The SQL server administrator can copy the files without changing permissions
Clustered Index:
What is the "side effect" of using a Clustered index over an non-clustered index
Explain why it is discouraged that you manually "tune" the indexes
Index Location:
Which of the following is where you'd find the indexes for Cape_Codd's Inventory table?
Under Databases->Cape_Codd->Indexes
Under Databases->Cape_Codd->Tables->Indexes
Under Databases->Cape_Codd->Tables->dbo.Inventory->Indexes
Under Databases->Cape_Codd->Storage->Indexes
None of the above
Index Effects:
Which of the following DML statements will rarely (if ever) benefit from using an index
Select
Update
Delete
Insert
Current Configuration:
What is the command that was used in lecture to view the current database's configuration (like language or isolation level)
(a)
