Pages

Wednesday, 6 March 2013

sql 2008 exam practice Q/A.



1.     DISTINCT keyword is used to retrieve column values with no repetition.                           
            T          F
2.     There is no difference between unique key constraint and primary key constraint.  
            T          F
3.     Unique key constraint restricts for null values as Primary key does.                                                           
T          F
4.     Foreign key is used to identify records uniquely in a table.                                                            
T          F
5.     Information is defined as the processed and meaningful form of the raw data.                    
            T          F
6.     The size of data type ‘smallint’ is 1 byte.                                                                         
            T          F
7.     Drop Command and Delete command for a table does the same job.                                             
            T          F
8.     Bit datatype is used to store yes/no (true/false) values.                                                    
            T          F
9.      DISTINCT keyword is used to retrieve column values with no repetition.                                       T            F
10.  There is no difference between unique key constraint and primary key constraint.                           T            F
11.  Unique key constraint restricts for null values as Primary key does.                                                  T            F
12.  Foreign key is used to identify records uniquely in a table.                                                               T            F
13.  Information is defined as the processed and meaningful form of the raw data.                                T            F
14.  The size of data type ‘smallint’ is 1 byte.                                                                                           T            F
15.  Drop Command and Delete command for a table does the same job.                                                           T          F
16.  Bit datatype is used to store yes/no (true/false) values.                                                                      T            F
17.  Syntax for the command to create table is
a.     Create table <tablename>
b.     Create <tablename>

18.  “Delete From <tablename>” and “Truncate table <tablename>” provides
a.     same functionality i.e. used to delete specified table from the database
b.     same functionality i.e. used to delete all rows (records) in specified table
c.     different functionality


19.  Left Outer Join operation results in
a.     Records all from left table but matched ones from right table
b.     Records from both the tables whether they match or not.
c.     Records from left table only

20.  Referential Integrity states that
a.     a database must not contain any unmatched foreign key values
b.     a database must contain unmatched foreign key values

21.  DML stands for
a.     Data Markup Language
b.     Data Manipulation Language

22.  SELECT name FROM users WHERE job=’Software Developer’ OR city=’Karachi’
a.     It will return the names when both scenarios match
b.     It will return the names when at least a single match is found.

23.  Create table Reservation (SeatNovarchar(3), PassengerNo Varchar(5))
a.     This command will return a syntax error
b.     This command will create a table named as “Reservation”

24.  Domain Integrity enforces
a.     valid values to be entered in a column.
b.     A column to be unique

25.  Identify the query used to view the index created on the table?
a.       sp_index<table_name>
b.    sp_helpindex <table_name>
c.       index <table_name>
d.      index_help<table_name>

26.  In SQL Server 2005, the ownership of a user database can be changes. ... procedure to change the ownership of a database. *
a.       sp_changedbowner.
b.      sp_changeowner
c.       change_db_owner.

27.   The Process of processing a set of commands one at a time from a batch of command called
a.        Command Processing.
b.       Batch Processing.
c.        Set Processing.
d.       Singular Processing

28.   __________ Statements are used for securing the database.
a.       DDL   
b.      DML  
c.       Batch  
d.      DCL

29.  The System stored procedure ____ is used to change ownership of database.
a.       DBCC SHRINKDATABASE.         
b.      sp_changedbowner    
c.       sp_dboption   
d.      sp_renamedb.

30.  The _____ data model allows a child node to have more than one parent.
a.       Flat-File          
b.      Hierarchical    
c.       Network

31.  Which trigger is used to enforce referential integrity?
a.       Cascading trigger
b.      Update trigger
c.       Nested trigger

32.  We can use the Alter table command to drop the constraint from a table
a.       True
b.      False

33.  Syntax for the command to create table is
a.       Create table <tablename>
b.      Create <tablename>

34.  “Delete From <tablename>” and “Truncate table <tablename>” provides
a.       same functionality i.e. used to delete specified table from the database
b.      same functionality i.e. used to delete all rows (records) in specified table
c.       different functionality

35.  Left Outer Join operation results in
a.       Records all from left table but matched ones from right table
b.      Records from both the tables whether they match or not.
c.       Records from left table only

36.  Referential Integrity states that
a.       a database must not contain any unmatched foreign key values
b.      a database must contain unmatched foreign key values

37.  DML stands for
a.       Data Markup Language
b.      Data Manipulation Language

38.  SELECT name FROM users WHERE job=’Software Developer’ OR city=’Karachi’
a.       It will return the names when both scenarios match
b.      It will return the names when at least a single match is found.

39.  Create table Reservation (SeatNovarchar(3), PassengerNo Varchar(5))
a.       This command will return a syntax error
b.      This command will create a table named as “Reservation”

40.  Domain Integrity enforces
a.       valid values to be entered in a column.
b.      A column to be unique

41.  The _____ data model allows a child node to have more than one parent.
a.       Flat-File          
b.      Hierarchical    
c.       Network

42.  A relational database is a database structure on the _____ model.
a.       Network         
b.      Relational       
c.       Hierarchical
d.      Flat-File

43.  _____ is used to administer permission for the database and database objects.
a.       Data Definition Language (DDL)     
b.      Data Manipulation Language (DML)
c.       Sub-scheme    
d.      Data Control Language (DCL)

44.  In the relational model terminology, a row is called a ____, a Column an______ and the table is called a _____.
a.       Tuple, Attribute, Relation      
b.      Attribute, Tuple, Relation
c.       Row, Column, Tuple.

45.  A ____ can be defined as a collection of related records and a set of programs that access and manipulate these records.
a.       Database Management System          
b.      Relational Database Management System.
c.       Data Management      
d.      Network Model

46.   ____ describe container for storing data, process of storing and retrieving data from that container.
a.       Network Model         
b.      Database Model/Data Model 
c.       Flat-File Model          
d.      Relational model.

47.  During logical data modeling, the data modeler develops a ___ description of the data and the various entities involved with it.
a.       Detailed         
b.      Superficial

48.  One or more attributes that can uniquely define an entity from an entity set is called a ____
a.       Primary           
b.      Candidate      
c.       Alternate        
d.      Super.

49.  An attribute that contains two or more attribute values in it is called a ___ attribute.
a.       Derived          
b.      Composite
c.       Multivalve.

50.  Transitive dependence is eliminated in the ________ normal Form.
a.       First    
b.      Second           
c.       Third  
d.      Fourth.

51.  The _______ operation is further enhanced in the _______ operation.
a.       Product          
b.      Intersection    
c.       Difference      
d.      Join.

52.  ______ defines rules regarding the values allowed in columns and is the standard mechanism for enforcing data integrity.
a.       Table
b.      Index  
c.       Constraints     
d.      Rules.

53.  ______ And _____ are the two types of file groups in SQL Server 2005.
a.       Primary           
b.      Secondary      
c.       User Defined
d.      Log Files.

54.  ______ Clause returns a limited number of rows from a table in from clause to a sample number or percent of rows.
a.       Output
b.      TOP    
c.       Table Sample
d.      With.

55.  ______ Statements are used for securing the database.
a.       DDL   
b.      DML  
c.       Batch  
d.      DCL

56.  ______ Operator operate only on one operand to perform arithmetic operations.
a.       Bitwise           
b.      Assignment    
c.       Relational       
d.      Unary.

57.  ______ is a collection of one or more Transact-SQL statements sent at one time from an application to SQL Server for execution.
a.       Batch  
b.      Group 
c.       Order  
d.      Collection.

58.  ______ Function is used to assist with the summarization of large volumes of data.
a.       Aggregate      
b.      System
c.       Conversion.

59.  _____ is used as an alias for a table name.
a.       AS Clause      
b.      From
c.       INTO
d.      Where

60.  _____ Keyword displays only the top few rows of the result set.
a.       View  
b.      Percent           
c.       TOP    
d.      Distinct.

61.  _____ displays a string of any length.
a.       [ ]        
b.      %        
c.       -          
d.      [^]

62.  _____ Clause with _____ keyword restricts the number of rows to be grouped.
a.       Where and Group by             
b.      Having and Group by.                       
c.       All and Group by.

63.  _____ Clause sorts the result set in a particular order.
a.       Group by        
b.      Order by         
c.       Where 
d.      ASC.

64.  Which of the following data types does not belong to exact numeric Category?
a.       Tiny Int          
b.      Big Int
c.       Float   
d.      Money

65.  Which of these commands is used to remove a table when no longer required?
a.       Remove Table
b.      Alter table Remove
c.       Alter Table Drop
d.      Drop table tblName.

66.  _______ Operator is used to display only the rows that are common to both the tables.
a.       Intersect         
b.      Union 
c.       Except
d.      Union All

67.  _______ is formed when records from two tables are combined only if the rows from both the tables are matched based on a common column.
a.       Inner Join
b.      Left Outer Join
c.       Self Join
d.      Right Outer Join.

68.  Which of the following can be used with sub queries that return one column and many rows?
a.       Any    
b.      All      
c.       IN       
d.      =

69.  Collection of Data is called information  _____T___ .
70.  Collection of information is called Records ____T____ .
71.  Collection of Records is called Tables ________ .T
72.  Collection of Tables is called Database _____T___ .
73.  DBMS stands for _______________________dont know   ___________.
74.  Any Database consist on three major languages/Operations:
a.       DDL
b.      DML
c.       DCL
75.  Write commands used in DML, DDL, DCL.
76.  Write operation/Command used by Database Administrator.
77.  What is Stored Procedure? Write types of Stored Procedure and their Names.
78.  What is Trigger? Why it is used? Write types of Triggers?
79.  Write the command to create a Database “APTECHDB”
80.  Write all aggregate functions and their use.