Wednesday, 6 March 2013

sql 2008 exam practice questions.

1.       ___________________ is a collection of related data stored in the form of data.
a)      Database
b)      Database management system

2.       A________________________________ can be define as a collection of related records and a set of programs that access and manipulate these records.
a)      Database
b)      Database management system
c)       Relational database management System.

3.  A_____________________ ________ is a rule of software programs for creating, maintaining, modifying, and manipulating a relational database.
a)   Database
b)   Database management system
c)   Relational database management System.

4. The_________________ data model allows a child node to have more than one parent.
a) Falt-file
b) Hierarchical
c)  Network

5. ___________________is used to administer permission on the database and database objects.
a)      Data Definition Language (DDL)
b)      Data manipulation Language (DML)
c)       Sub-Schema
d)      Data control Language (DCL)

6. An attribute that contain two or more attribute valus in it is called a________________ attribute.
a)      Derived
b)      Composite
c)       Multivalued

7. The_______________ operation is further enhanced in the __________________ operation.
a)      Product
b)      Intersection
c)       Difference
d)      Join

8. _____________provides an implementation of SOA to build a message queuing system for reliable and secure database.
a)      Service Oriented architecture
b)      Enterprise architecture
c)       Service broker

9. ______________________ defines rules and processes to allow many clients for accessing tha data servers via business services.
a)      Abstraction layer
b)      Data presentation layer
c)       Business service layer
d)      Data service layer.

10. The_______________ clause returns a limited number of rows from a table in the FROM clause to a sample number or percent of rows.
a)      OUTPUT
b)      TOP
d)      WITH

11. ____________operator takes rows and puts them into columns.
a)      UNPIVOT
b)      ANY
c)       PIVOT
d)      SOME

12. A_____________ is a collection of one or more Transaction statements sent at one time.
a)      Batch
b)      Group
c)       Order
d)      Collection

13. ________________  function is used to assist with the summarization of large volumes of data.
a)      Aggregate
b)      System
c)       Conversion

14. The system stored procedure _____________ is used to change ownership of databases.
b)      Sp_changedbowner
c)       Sp_dboption
d)      Sp_renamedb

16. ALTER DATABASE Employee MODIFY NAME  =  Emp_DB changes the_______________
a)      Database name
b)      Database primary file name
c)       Database logical file name

17. Every database has a _________________filegroup by default.
a)      Primary
b)      Secondary
c)       User-defined
d)      System

18. ____________ displays a string of any length.
a)      []
b)      %
c)       _
d)      [^]

19. The ______________clause with_________ keyword restricts the number of rows to be grouped.
a)      WHERE and GROUPBY
b)      HAVING and GROUPBY
c)       ALL and GROUPBY

20. What is the function that can be used to determine if a table has a globally unique identifier ?
b)      Uniqueidentifier
d)      NEWID()

21. Which of the following xml data type method can be used to retrieve entire or specific part of an XML instance ?
a)      Query()
b)      Retrieve()
c)       Get()
d)      Value()

     22. Which of these is the correct statement to create an alias type based on system defined type nvarchar of length 30 and which prevents of null values ?
            a) CREATE TYPE newtype BASED ON nvarchar(30) NOT NULL
            b) CREATE TYPE newtype FROM nvarchar(30) NULL NOT ALLOWED
            c) CREATE TYPE usertype FROM nvarchar(30) NULL
            d) CREATE TYPE usertype FROM nvarchar(30) NOT NULL

  23. The _________________ operator is used to display only the rows that are common to both the table.
   b) UNION
  c) correlated subquery
  d)  parent query

24. _____________________ is formed when records from two table are combined only if the rows from both the table are matched based on a common column.
   a) INNER join
  b) Left Outer Join
  c) Self join
  d) Right outer join

25. Outer join can be either right outré join or left outer join.
    a) TRUE
    b) FALSE

26. Which of the following can be used with subqueries that return one column and many rows.
    a) ANY
    b) ALL
    c) IN
    d)  =

27. Using the CONVERT () function a value of one data type can be transformed to another. Which of the following syntax will help you to achieve this?
a: CONVERT(datatype1,datatype2).
b: CONVERT(datatype[length],expression[style]).
c: CONVERT(datatype,expression).
d: CONVERT(expression1,expression2)

28. Which of the following codes creates a variable of XML data type?
1.      DECLARE @xmlvar xml
     SELECT @xmlvar=’<Employee name=”joan”/>’

2.      DECLARE @xmlvar xml
       SELECT @xmlvar=<Employee name=joan>

3.      DECLARE @xmlvar xml
      SELECT @xmlvar=’<Employee name=”joan”’

4.      DECLARE @xmlvar xml
        SELECT @xmlvar=<Employee name=”joan”/>
29. Every table have only one primary key constrain. You can add a primary key by using which of the following syntax?
1.     CREATE TABLE <table_name>(Column_name datatype PRIMARY KEY {,column_list})
2.     CREATE TABLE <table_name>(Column_name datatype PRIMARY KEY {column_list})
3.     CREATE TABLE <table_name>(Column_name datatype PRIMARY KEY {column_list});

30. The _______________ indexing features allows complex queries to be formed on character data.
         a) Partitioned Index
          b) Unique index
          c) Full-text Index

31. Which of the following are the features of Trigger.
      a) A trigger can be created only in the current databse.
      b) A table can have only single insert, update and delete triggers.
       c) A trigger can be applied to only one table.