Test
Database Final Exam
Este test tiene tipos de pregunta que se abren en el reproductor de siempre.
Preguntas del test (53)
1Microsoft 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.
- Aone to many
- Bone to one
2The MDF file type holds the raw data for the database.
- AVerdadero
- BFalso
3The [blank_start]LDF[blank_end] file type is the log file that holds transactions.
- ALDF
4It is best practice for the MDF and LDF files to be stored on separate disk volumes if possible.
- AVerdadero
- BFalso
5There are two types of indexes within SQL Server, one is [blank_start]clustered[blank_end], the other is [blank_start]non clustered[blank_end].
- Aclustered
- Bnon clustered
6By default, the [blank_start]primary key[blank_end] is a clustered index.
- Aprimary key
7Check off what is TRUE about a non clustered index within SQL Server:
- ACreates an index of the column OUTSIDE of the table.
- BA RID (RecordID) is stored in the index and is used to point back to a specific record within the table.
- CA non clustered index can be sorted without resorting the rest of the columns within the table.
- DA non clustered index is created by default.
8Only one clustered index can exist on a single [blank_start]table[blank_end].
- Atable
- Bcolumn
- Crow
9You can have as many non-clustered indexes in a table as you have [blank_start]columns[blank_end].
- Acolumns
- Brows
- Ctables
10What type of SERVER-LEVEL roles will Microsoft SQL Server support? Check all that apply.
- Asysadmin
- Bserveradmin
- Csecurityadmin
- Ddbcreator
- Epublic
- Fdb_owner
- Gdb_datareader
11What type of DATABASE-LEVEL roles will Microsoft SQL Server support? Check all that apply.
- Adb_owner
- Bdb_securityadmin
- Cdb_datareader
- Ddb_backupoperator
- Edb_datawriter
12Indexes are sorted using a [blank_start]Balanced Tree[blank_end].
- ABalanced Tree
13Label the three levels in the image:
- ARoot
- BIntermediate
- CLeaf
14Assume we want to add a user called James into the B-Tree. What would have to be done?
- APerform a page split then add James
- BAdd James after Hart with no page split
- CAdd James at the intermediate level
- DStart a new B-Tree
15When is the most ideal situation to use a B-Tree?
- AIf you have a table with lots of updates occurring.
- BIf you have a table with many reads but very few writes.
- CIf you have a table column containing multiple records with the same data.
16What is a "Commit"?
- AThe data cached on the SQL server is saved to the transaction log file (the LDF file).
- BData is cached onto the SQL server when changes in the database are made.
- CAny change that is made to a table within your database.
17What is a "Checkpoint"?
- AWrites committed transactions to the database.
- BA read-only buffer cache.
- CModifies records within tables.
18A Simple Recovery model does not permanently keep transaction logs.
- AVerdadero
- BFalso
19A Full Recovery model keeps the transaction logs, meaning they can be used in a restore.
- AVerdadero
- BFalso
20The drawbacks of a Full Recovery model are: (check all that apply).
- AThey take up more disk space than a Simple Recovery
- BWhen the transaction log file becomes full the SQL server will stop working
- CYou need RAID5 to use the Full Recovery model.
21Which SQL statement will you use to add a table called tblFuck within your database?
[blank_start]CREATE TABLE tblFuck[blank_end]- ACREATE TABLE tblFuck
22Match 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]- ADomain
- BReferential
- CEntity
- DDomain
- EReferential
- FEntity
- GReferential
- HDomain
- IEntity
- JEntity
- KDomain
- LReferential
- MEntity
- NDomain
- OReferential
23Fill 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]- ASELECT
- Bfrom
- CWHERE
- D>= 100
- EAND
- F<= 200
24Add 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]- AALTER
- BADD
- Cvarchar(50)
- DTABLE
25Remove 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- AALTER
- BTABLE
- CDROP
- DCOLUMN
26What 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- ACK_ConstraintName
- BPK_ConstraintName
- CFK_ConstraintName
- DDF_ConstraintName
- EU_ConstraintName
- FCK_ConstraintName
- GPK_ConstraintName
- HFK_ConstraintName
- IDF_ConstraintName
- JU_ConstraintName
- KCK_ConstraintName
- LPK_ConstraintName
- MFK_ConstraintName
- NDF_ConstraintName
- OU_ConstraintName
- PCK_ConstraintName
- QPK_ConstraintName
- RFK_ConstraintName
- SDF_ConstraintName
- TU_ConstraintName
- UCK_ConstraintName
- VPK_ConstraintName
- WFK_ConstraintName
- XDF_ConstraintName
- YU_ConstraintName
27A transaction started before and committed after a checkpoint.
Then a system failure occurred.
When the system comes back online, what would occur?- AA roll forward
- BA roll back
- CA system failure
- DNo action would be required
28A transaction started and committed before a checkpoint.
Then a system failure occurred.
What is required?- ANo action is required.
- BA roll back.
- CA roll forward.
- DThe data is corrupt.
29A transaction started before the checkpoint.
It never fully committed at the time of the crash.
What action is required?- AA roll back.
- BA roll forward
- CA system failure occurred so there's nothing that can be done
30Fill 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]- AWHERE
- B'Canada'
31Fill 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- ASELECT
- BFROM
- CWHERE
- D'USA'
- EAND
- F'Detroit'
- GORDER
- HBY
32Fill 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- ASELECT
- BFROM
- CWHERE
- D'USA'
- EOR
- F'Canada'
33Fill 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]
- ASELECT
- BtblStaff.Name
- CtblDept.Name
- DFROM
- EtblStaff
- FJOIN
- GtblDept
- HON
- ItblDept.ID_Dept
- JtblStaff.ID_Dept
- K=
34Fill 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]
- ASELECT
- Bavg
- Cunitprice
- DFROM
- EtblOrders
35Write 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]
- ASELECT
- BCOUNT
- C*
- DFROM
- EWHERE
- F'London'
- GCity
- H=
- ItblLocations
36Complete 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]
- ASELECT
- BCost
- C*
- DQuantity
- E*
- F1.13
- G'Total'
- HFROM
- ItblOrders
37Will this query run successfully?
SELECT FirstName, LastName
FROM tblEmployees
GROUP BY FirstName- AThis will fail, as LastName is neither an aggregate function or included in the group by.
- BThere is no problem with this query.
- CThis will fail as the Group By clause is incorrect.
- DThis will fail as FirstName is neither an aggregate function or included in the group by.
38Will this query run successfully?
SELECT ProductName, SUM(UnitPrice), SUM(Cost), UnitPrice * Cost
FROM Orders
GROUP BY ProductName- AThis will fail as SUM(UnitPrice) and SUM(Cost) are aggregate functions and cannot be used with a Group By Clause.
- BThis command will run successfully.
- CThis will fail as ProductName cannot be used with the Group By Clause.
- DThis will fail as UnitPrice * Cost is not an aggregate function and cannot be used with a Group By Clause.
39What is true about views? Select all that apply.
- AYou can insert data into a view.
- BYou cannot join a table and a view together.
- CYou can use a view to denormalize data.
- DYou can define permissions for a view.
40[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]
- AALTER
- BVIEW
- CvEmployees
- DAS
- ESelect
- FID_EMP
- GFirstName
- HLastName
- IFROM
- JEmployees
41What is true about stored procedures? Check all that apply.
- AYou cannot alter (modify) a stored procedure.
- BA Stored Procedure can contain multiple SQL Statements.
- CYou must use a BEGIN and END around your SQL statements within a stored procedure if you have multiple SQL statements.
- DApplications can use stored procedures to prevent against SQL injection
- EYou must use a GO between each SQL statement.
42Fill 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]- ACREATE
- BPROCEDURE
- CUSP_ViewEmp
- DAS
- EBEGIN
- FEND
- GGO
43Which of the following help ensure data integrity?
Select all that apply.- AData Type
- BIdentity Specification
- CAllowing/Not Allowing Null values
- DConstraints
44What 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])- AALTER
- BTABLE
- CADD
- DCONSTRAINT
- ECK_Salary
- FCHECK
- GSalary
- H<=
- I100000
45Assuming 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]- AALTER
- BTABLE
- CEmployees
- DNOCHECK
- ECONSTRAINT
- FCK_Salary
46When 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.- AFILENAME = 'C:\DATA\Acme.mdf'
47When 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]- AFILEGROWTH = 50%
48Create 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]
)- ACREATE
- BTABLE
- CID_Staff
- DINT
- EPRIMARY
- FKEY
- GFirstName
- Hvarchar(50)
- INOT NULL
- JLastName
- Kvarchar(50)
- LNOT NULL
- Mvarchar(4)
- NExtension
49Below 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]- AID_Staff int IDENTITY (1, 1)
50What 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]- AALTER
- BTABLE
- CADD
- DDepartments
- Evarchar(50)
51What 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]- AALTER
- BTABLE
- CDROP
- DCOLUMN
- EDepartments
52What 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]- AALTER
- BTABLE
- CALTER
- DCOLUMN
- EtblStaff
- Ffirstname
- Gvarchar(25)
53What is the SQL Statement to delete the table named tblStaff from Acme database?
[blank_start]DROP TABLE tblStaff[blank_end]- ADROP TABLE tblStaff
Información
- Descripción
- Database Final Exam
- Autor
- dbm
- Idioma
- English
- Visitas
- 526
- Etiquetas
- Valoración
- Aún sin valoraciones Valora este test