Global Test

Question No.1 (1M)

Name the process of intentional introduction of redundancy in a table that improves
performance.
 Normalization
 Denormalization
 Functional Dependency
 Duplication
Question No.2 (2M)
A database can be accessed multiple users .But the entire data available inside a
database is not relevant for all the users .For security reasons also specific data
should available to the users based on their role or permissions .John as a database
developer,needs to ensure this restricted access of data to the users .Which level of
database organization is applicable in the preceding context?
 External Level
 Internal Level
 Physical Level
 Conceptual level
Question No.3 (1M)
Which one of the following operators builds a relation from two specified relations
based on an attribute that is common in both the relations?
 INTERSECT
 PROJECT
 JOIN
 PRODUCT
Question No.4 (1M)
Identify the application architecture that is arranged in the following layers.
_Client tier
_UI –centric processing components
_Data-centric processing objects
_Database server
 N-Tier architecture
 Single-Tier architecture
 Two-Tier architecture
 Three-Tier architecture
Question No.5 (2M)
Steve has been assigned the task of creating Web based reports based on the data
that is stored in the data warehouse .Also ,these reports should get published in
different formats .Which one of the following SQL server components will enable
Steve implement the desired task?
 Microsoft SQL Server Reporting Services
 Integration Services
 Analysis Services
 Replication Services
Question No.6 (1M)
Which one of the following services of the SQL Server database engine allows you to
search seconds in the basis of certain words and phrases?
 Notification services
 Service Broker
 Replication
 Full-text search
Question No.7 (2M)
Which features of Common Language Runtime enables an application to interact
with another application written in a different programming language of the .NET
suite ,therefore maximizing code reuse?
 Platform independence
 Automatic memory management
 Standard type system
 Language interoperability
Question No.8 (1M)
Which one of the following string functions is used to remove leading blanks from
the character expression?
 ltrim
 left
 lower
 difference
Question No.9 (2M)
John as a database developer wants to determine the total number of employees
who are working in the Sales department .Which one of the following aggregate
functions should John use?
 count()
 max()
  avg()
 min()
Question No.10 (3M)
You are a database developer .You plan to design a database solution by using SQL
server .The database contains two tables named Supplier and Product .There is a
foreign key constraint between the Supplier and Product tables on the SupplierID
column .The Supplier table is the parent table and contains a row that has the
SupplierID value as 0 .This record is required ,else the foreign key constraint will
not allow value 0 for the SupplierID attribute in the Product table .Certain
transactions might delete the records of certain suppliers from the Supplier
table .You need to ensure that if a supplier is deleted ,the corresponding SupplierID
value in the Product table set to 0 .How can you implement this functionality?
 Create a FOR UPDATE trigger on the Supplier table that updates the
SupplierID value to 0 in the Product table for the deleted supplier.
 Create a default constraint on the SupplierID column in the Product table that
sets the value to 0 .Set the ON DELETE property of the foreign key constraint
to NULL.
 Create a FOR DELETE trigger on the Product table that updates the
SupplierID value to 0 in the Product table for the deleted supplier.
 Create a default constraint on the SupplierID column in the Product table that
sets the value to 0 .Set ON DELETE property of the foreign key constraint to
Default.
Question No.11 (4M)
You are a database developer on an instance of SQL Server .You execute the
following statements to create the Health_Insurance_Holders and Insurance_Terms
tables:
CREATE TABLE Health_Insurance_Holders(
Insurance_Holder_ID int IDENTITY(1,1) PRIMARY KEY,
LAST_NAME varchar(50) NOT NULL,
FIRST_NAME varchar(50) NOT NULL);

CREATE TABLE Insurance_Terms(

Insurance_Number varchar(10) PRIMARY KEY,
Insurance_Holder_ID int FOREIGN KEY REFERENCES
Health_Insurance_Holders(Insurance_Holder_ID),
Insurance_Classification_ID int,
Insurance_Effective_Date datetime,
Insurance_Review_Date AS DATEADD(month,4,Insurance_Effective_Date),
Insurance_Termination_Date datetime NOT NULL,
Insurance_Premium_Amount money NOT NULL)

You need to ensure that the following conditions are met when an Insurance is
added or updated in the Insurance_Terms table:
-Each Insurance must have an effective date.
-Each Insurance must have a premium amount that is numeric and greater than
zero.
-Each Insurance must have a review date that is before the termination date.

Which Transact-SQL should you execute?

 ALTER TABLE dbo.Insurance_Terms

ADD CONATRAINT nn_Insurance_Effective_Date NOT NULL,
CONSTRAINT ck_PremiumAmt CHECK (Insurance_Premium_Amount>0),
CONSTRAINT ck_ReviewDate CHECK
(Insurance_Review_Date<Insurance_Termination_Date);

 ALTER TABLE dbo.Insurance_Terms

DROP COLUMN Insurance_Review_Date,
GO
ALTER TABLE dbo.Insurance_Terms
ALTER COLUMN Insurance_Effective_Date datetime NOT NULL,
GO
ALTER TABLE dvo.Insurance_Terms
ADD Insurance_Review_Date AS DATEADD(month,3,
Insurance_Effective_Date)
PERSISTED,
GO
ALTER TABLE dbo.Insurance_Terms
ADD CONSTRAINT ck_PremiumAmt CHECK
(Insurance_Premium_Amount>0),
CONSTRAINT ck_ReviewDate CHECK
(Insurance_Review_Date<Insurance_Termination_Date),
GO
  ALTER TABLE dbo.Insurance_Terms
ALTER COLUMN Insurance_Effective_Date datetime NOT NULL,
GO
ALTER TABLE dbo.Insurance_Terms
ADD CONSTRAINT ck_PremiumAmt CHECK
(Insurance_Premium_Amount>0),
CONSTRAINT ck_ReviewDate CHECK
(Insurance_Review_Date<Insurance_Termination_Date),
GO

Question No.12 (3M)

You are a database developer on an instance of SQL Server .You have a table in your
production database created with the following T-SQL Statement.
CREATE TABLE emp
(
Empid int,
Emp_sal money,
Emp_sales int,
)
The emp table contains the data ,as shown in the following table,
EmpID Emp_Sal Emp_Sales
1 10 50
2 10 50
12 10 60
3 20 60
4 20 60
5 30 50
6 30 70
7 40 70
8 40 80
9 40 80
10 50 60
11 50 90

Your client wants you to fetch data from the emp table in such a way the Salary of
every employee is ranked in desending order without any gaps in the ranks .In
addition , if some employee have the same salary that they should be given in same
rank .Which query should you write to get the desired result?
  SELECT empid,emp_sal,
Emp_sales,ROW_NUMBER()OVER (PARTITION BY emp_sales ORDER BY
emp_sal desc)
FROM emp
 SELECT empid,emp_sal,
Emp_sales,DENSE_RANK() OVER (PARTITION BY emp_sales ORDER BY
emp_sal desc)
FROM emp
 SELECT empid,emp_sal,
Emp_sales,DENSE_RANK() OVER ( ORDER BY emp_sal desc)
FROM emp
 SELECT empid,emp_sal,
Emp_sales RANK() OVER (PARTITION BY emp_sales ORDER BY emp_sal desc)
FROM emp

Question No.13 (1M)

Which one of the following options is a clustered B-tree representation of the nodes
in the XML data?
 Property index
 Primary XML index
 Value index
 Secondary XML index

Question No.14 (2M)

Working as a database developer ,you want to create a view of a table in such a way
that is a row is modified through a view ,the data remains visible through the
view .Which one of the following arguments should be used
 WITH ENCRYPTION
 FILLFACTOR
 WITH CHECK OPTION
 SCHEMABINDING

Question No.15 (3M)

You are a database developer .You plan to design a database solution by using SQL
Server .You have a website supported by a database that has the full-text-search
component installed .You plan to create a table named Products that will have the
structure as shown in the following table .

Column_Name Data_Type
ProductID INT
ProductTitle VARCHAR (500)
ProductDescription VARCHAR (4000)
Product_Owner_ID INT

Users of the website should be able to search for the products based on ProductTitle
field .You need to construct a full-text query that ensures the following compliances
when a user launches for search for a product .
-Rows are returned when the exact search phrase is found
-Rows are returned in the order of how well they match the searched phrase
What should you specify in the full-text query?
 A CONTAINS predicate
 A CONTAINSTABLE function
 A FREETEXT predicate
 A FREETEXTTABLE function

Question No.16 (4M)

You are a database developer on an instance of SQL Server .The Product_Description
column of the Product_AssemblyProcess table is full-text enabled .You issue the
following SELECT statement .

Select Product_ID , Product_Description

From Product_AssemblyProcess
Where Product_Description CONTAINS(*,”HEIGHT” AND “STRENGTH”)

What will be returned in the result set of the preceding query?

 SELECT Product_ID and Product_Description values for all those rows that
contains the phrase , “HIGH and STRENGTH” are returned.
 The Product_ID and Product_Description values for all those rows that
contain either HIGH word or the STRENGTH word in Product_Name.
  The ProductID and Product _Description values for all rows containing the
words,HIGH and STRENGTH in the full-text enabled columns along with their
rankings.
 The Product_ID and Product_Description values for all those contain both the
words ,HIGH and STRENGTH are returned.
Question No.17 (3M)
You are a database developer on an instance of SQL Server .Your Employee table
was defined using the following Transact SQL statement .
CREATE TABLE Employee(
Employee_ID INT PRIMARY KEY,
Employee_Name NVARCHAR(50),
Manager_ID INT
)
The Employee table contains the data as shown in the following table.
Employee_ID Employee_Name Manager_ID
1 Mike 3
2 David 3
3 Roger NULL
4 Marry 2
5 Joseph 2
6 Ben 2

The management of the organization wants to see the list of employees and their
respective managers .For this you entered the following query.
CREATE VIEW Employee_Manager_Details
WITH SCHEMABINDING
AS
SELECT e1.Employee_Name AS EmployeeName ,e2.Employee_Name AS
ManagerName
FROM dbo.employee e1
INNER JOIN dbo.Employee e2
ON e1.Manager_ID = e2.Employee_ID
But ,the query execution on view tables a long time and you decide to create unique
clustered index on view using the following query.
CREATE UNIQUE CLUSTERED INDEX IX_Empplyee_Manager_Details ON
dbo.Employee_Manager_Details
(
EmployeeName ASC
)
What would be the outcome of the preceding query?
 Index gets successfully created and query performance is improved.
 You need to create index on Employee_ID rather the on EmployeeName.
 You should create unique clustered index on Employee_ID and include
EmployeeName
 Index creation will fail because of join condition.
Question No.18 (1M)
Which one of the following system stored procedure is used to see the details of the
database?
 sp_helpdb
 sp_lock
 sp_configure
 sp_renamedb

Question No.19 (2M)

Working as a database developer ,you want to create the number of local
transactions creating on the constant examination .Which one of the following
function should be used?
 @@TRANCOUNT
 UPDATE
 VALUE()
 EVENTDATA()
Question No.20 (3M)
You are a database developer on an instance of SQL Server .Your Production
database contains the Employee_Work_Detail table that store that stores
information about the work being processed by employees .As rows are inserted
into the Employee_Work_Detail ,you want to gather and record information in the
Employee_Work_Status table ,which is defined as shown in the following statement.
CREATE TABLE Employee_Work_Status(
Emp_ID INT,
WIP_Count INT,
Error_Description varchar(100),
Error_DateTime datetime)
If a row cannot be successfully added to the Employee_Work_Detail table ,the
following requirements must be met.
-A description of the error that occurred should be recorded in the
Error_Description column.
-The total number of work order in progress should be recorded in the WIP_Count
column.
-The date and time that an error occurred should be stored in the Error_DateTime
column.
Which action should you take?
 Create a table_level CHECK constraint.
 Create an AFTER INSERT ,UPDATE trigger on the Employee_Work_Detail
table.
 Create AFTER DELETE trigger on the Employee_Work_Detail table.
 Create an INSTEAD OF INSERT trigger on the Employee_Work_Detail table.

Question No.21 (4M)

You are a database developer .You maintain the Production database their
includes the Employee_Details table defined as shown in the following
statement

CREATE TABLE Employee_Details(

Employee_ID INT,
Employee_Name varchar(100),
Employee_Gender varchar(6),
Bonus_Point int,
)
You want to create a user-defined scalar function to return the Bonus_Point
value for a specific employee . If the EmployeeID value passed to the function
is between 100 and 500, the function should return the actual Bonus_Point
value . For other employees,a Bonus_Point of zero should be returned . Which
statement should you use to create functions?

 CREATE FUNCTION dob ndf_get_bonus_point (@v_empid)

AS
BEGIN
DECLARE @v_bonuspt int,
 SELECT @v_bonuspt = Bonus_Point From Employee_Details Where
Employee_ID = @v_empid
IF (@v_empid BETWEEN 100 AND 500)
RETURN @v_bonuspt
ELSE
RETURN 0
END

 CREATE FUNCTION dob ndf_get_bonus_point (@v_empid)

RETURNS int
AS
BEGIN
DECLARE @v_bonuspt int,
IF (@v_empid BETWEEN 100 AND 500)
SELECT @v_bonuspt = Bonus_Point From Employee_Details Where
Employee_ID = @v_empid
ELSESET @v_bonuspt = 0
END
 CREATE FUNCTION dob ndf_get_bonus_point (@v_empid)
RETURNS int
AS
BEGIN
DECLARE @v_bonuspt int,
SELECT @v_bonuspt = Bonus_Point From Employee_Details Where
Employee_ID = @v_empid
IF (@v_empid BETWEEN 100 AND 500)
RETURN @v_bonus
RETURN 0
END
 CREATE FUNCTION dob ndf_get_bonus_point (@v_empid)
RETURNS int
AS
BEGIN
DECLARE @v_bonuspt int,
 IF (@v_empid BETWEEN 100 AND 500)
BEGIN
SELECT @v_bonuspt = Bonus_Point From Employee_Details Where
Employee_ID = @v_empid
RETURN @v_bonus
END
ELSE
RETURN 0
END

Question No.22 (3M)

You are a database developer on an instance of SQL server. The Producttable
in your database was created using the following statement.
CREATE TABLE Products(
Product_ID int PRIMARY KEY,
Product_Category_ID int,
Product_Amount money CHECK(Product_Amount>0),
Product_Launch_Date date DEFAULT GETDATE());

The Product table currently contains no data.

You execute the following Transact-SQL statements;

SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO Products VALUES(2,1,500,’11-01-2011’);
INSERT INTO Products VALUES(1,3,0,’01-15-2010’);
INSERT INTO Products VALUES(3,7,250,DEFAULT);
COMMIT TRANSACTION
What is the result of the preceding statements?
 The Transact-SQL executes successfully and inserts two rows into the
Products table.
 The Transact-SQL generates an error message, but inserts two into the
Products table.
 The Transact-SQL generates am error message and rolls back all inserts.
  The Transact-SQL executes successfully and inserts three rows into the
Products table.

Question No.23 (1M)

Which one of the following DMV’s is used to view the authenticated SQL
Server sessions?
 Sys.dm_exec_sessions
 Sys.dm_resource_governor_workload_groups
 Sys.dm_exec_connections
 Sys.dm_exec_requests
Question No.24 (2M)
You are working as a database administrator. You want to change tracking on
a database. Which one of the following statements should be used to perform
this task?
 ALTER DATABASE
 ALTER TABLE
 SET STATISTICS
 UPDATE STATISTICS
Question No.25 (3M)
You work in a company named Raisy_Crop. The company uses SQL Server.
You are the administrator of the company database named Prod_Sales. You
need to enable Change Data Capture(CDC) on the product table that under the
schema named Prod.What are the steps you need to perform to achieve the
same?

 USE Prod_Salees
EXEC sp_cdc_enable_db
GO
SET cdc_enable_table ‘Products’’Prod_Sales’ ON
GO

 USE Prod_Salees
 EXEC sys.sp_cdc_enable_db
GO
EXEC sys.sp_cdc_enable_db
@source_schema=’Prod’
@source_name=’Products’
@role_name=’NULL’
GO
 USE Prod_Salees
SET cdc_enable_db ON
GO
SET cdc_enable_table ‘Products’’Prod_Sales’ ON
GO

 USE Prod_Salee
SET cdc_enable_db ON
GO
EXEC sys.sp_cdc_enable_table
@source_schema=’Products’
@source_name=’Prod_Sales’
@role_name=’NULL’
GO