Certiport Comprehensive Exam with
Accurate Solutions
How many files does Microsoft SQL Server use to store database - ANSWER-3
What is the sublanguage that allows the user to insert, delete, and update data in a
database - ANSWER-DML
The Alter Table command can change the IDENTITY specification of an existing column
- ANSWER-false
Which Transact-SQL command removes a table from the database - ANSWER-Drop
Table
Which Transact-SQl command removes all rows from - ANSWER-TRUNCATE TABLE
A user needs the _____ permission to run a stored procedure. - ANSWER-Execute
Which is a major difference between a stored procedure and a function - ANSWER-
Function must be complied every time it is called
____ is a reserved keyword in SQL Server 2008 and therefore cannot be used as an
object name or as a column name in a table, unless delimited. - ANSWER-ADD
A clustered index improves the performance of which types of queries - ANSWER-
Queries that use large number sets
Three are fixed server roles - ANSWER-Setupadmin, diskadmin, securityadmin
____ is the best method to prevent the loss of data - ANSWER-Backup
three characteristics are the foundation of modern database - ANSWER-attributes, data,
relationships
Which three are are T-SQL commands to handle privileges - ANSWER-Grant, Deny,
Revoke
removes a SQL Server login account - ANSWER-Drop Login
What does SQL stand for - ANSWER-Structured Query Language
, What is the maximum data value that can be stored in a SMALLDATETIME datatype -
ANSWER-June 6, 2079
What is a view - ANSWER-A virtual table created by a query joining one or more tables
The where clause allows you to - ANSWER-Obtain a subset of rows
Consider a query of two tables. The result contains only the rows that have a match in
both tables. What kind of join was used - ANSWER-Inner Join
What statement is used to retrieve data from a database - ANSWER-Select
The result of the query:
SELECT COUNT (*) from employees - ANSWER-The number of rows in the table
employees
What is the result of the following query:
Select MAX(salary) from employee - ANSWER-The maximum salary of all employees.
what does the following statement do
SELECT DATEADD (DD, 0, DATEDIFF(DD, 0, GETDATE())); - ANSWER-Return just
the date part of the current system date removing the time.
How many levels of normal forms are there - ANSWER-5
what determines a database in the third normal form. - ANSWER-Every non-prime
attribute is non-transitively dependent on every candidate key in the table
Sql code injection - ANSWER-A technique that exploits security vulnerabilities in the db
layer of an application
Members of what fixed database role cannot add, modify, or delete any data in the
tables within a database, regardless of other roles. - ANSWER-db_denydatawriter
Which of the following fixed server roles can shut down the SQL server - ANSWER-
Serveradmin
Three types of databases - ANSWER-Relational, Hierarchical, Flat File
Allows you to use the statements INSERT, UPDATE, DELETE, and MERGE to
manipulate data in any SQL Server tables - ANSWER-DML
A subset of the Transact-SQL language. - ANSWER-DDL
Microsoft's proprietary extension to SQL - ANSWER-T-SQL
Accurate Solutions
How many files does Microsoft SQL Server use to store database - ANSWER-3
What is the sublanguage that allows the user to insert, delete, and update data in a
database - ANSWER-DML
The Alter Table command can change the IDENTITY specification of an existing column
- ANSWER-false
Which Transact-SQL command removes a table from the database - ANSWER-Drop
Table
Which Transact-SQl command removes all rows from - ANSWER-TRUNCATE TABLE
A user needs the _____ permission to run a stored procedure. - ANSWER-Execute
Which is a major difference between a stored procedure and a function - ANSWER-
Function must be complied every time it is called
____ is a reserved keyword in SQL Server 2008 and therefore cannot be used as an
object name or as a column name in a table, unless delimited. - ANSWER-ADD
A clustered index improves the performance of which types of queries - ANSWER-
Queries that use large number sets
Three are fixed server roles - ANSWER-Setupadmin, diskadmin, securityadmin
____ is the best method to prevent the loss of data - ANSWER-Backup
three characteristics are the foundation of modern database - ANSWER-attributes, data,
relationships
Which three are are T-SQL commands to handle privileges - ANSWER-Grant, Deny,
Revoke
removes a SQL Server login account - ANSWER-Drop Login
What does SQL stand for - ANSWER-Structured Query Language
, What is the maximum data value that can be stored in a SMALLDATETIME datatype -
ANSWER-June 6, 2079
What is a view - ANSWER-A virtual table created by a query joining one or more tables
The where clause allows you to - ANSWER-Obtain a subset of rows
Consider a query of two tables. The result contains only the rows that have a match in
both tables. What kind of join was used - ANSWER-Inner Join
What statement is used to retrieve data from a database - ANSWER-Select
The result of the query:
SELECT COUNT (*) from employees - ANSWER-The number of rows in the table
employees
What is the result of the following query:
Select MAX(salary) from employee - ANSWER-The maximum salary of all employees.
what does the following statement do
SELECT DATEADD (DD, 0, DATEDIFF(DD, 0, GETDATE())); - ANSWER-Return just
the date part of the current system date removing the time.
How many levels of normal forms are there - ANSWER-5
what determines a database in the third normal form. - ANSWER-Every non-prime
attribute is non-transitively dependent on every candidate key in the table
Sql code injection - ANSWER-A technique that exploits security vulnerabilities in the db
layer of an application
Members of what fixed database role cannot add, modify, or delete any data in the
tables within a database, regardless of other roles. - ANSWER-db_denydatawriter
Which of the following fixed server roles can shut down the SQL server - ANSWER-
Serveradmin
Three types of databases - ANSWER-Relational, Hierarchical, Flat File
Allows you to use the statements INSERT, UPDATE, DELETE, and MERGE to
manipulate data in any SQL Server tables - ANSWER-DML
A subset of the Transact-SQL language. - ANSWER-DDL
Microsoft's proprietary extension to SQL - ANSWER-T-SQL
