bigsql1

1.       You have been asked to store time values that are accurate to the second. Which of the following data types do you use?

a.        Datetime

b.       uniqueidentifier

c.        smalldatetime

d.       smallint

e.       bit

Answer: datetime

2.       Which one of the following T-SQL statements do you use to create an HTTP endpoint?

a.        SET ENDPOINT

b.       CREATE ENDPOINT

c.        NEW END_POINT

d.       CREATE HTTPENDPOINT

e.       SET HTTP_ENDPOINT

Ans: CREATE ENDPOINT

3.       Which one of the following table names is a temporary table that is visible only on the connection on which it was created?

a.        ##myTable

b.       _myTable

c.        #myTable

d.       @@myTable

e.       @myTable

Answer: #myTable

4.       Which one of the following is a control-of-flow T-SQL statement?

a.        WHILE

b.       IN

c.        WHERE

d.       ORBER BY

e.       EXECUTE

Answer: WHILE

5.       Which one of the following keyword is NOT used with T-SQL cursors?

a.        DEALLOCATE

b.       FETCH

c.        MOVENEXT

d.       DECLARE

e.       SET

Answer: MOVENEXT

6.       What is the difference between a PRIMARY KEY and UNIQUE index?

a.        Both PRIMARY KEY and UNIQUE index do not allow duplicate values; UNIQUE does not allow Null.

b.       You can have more than one PRIMARY KEY and only one UNIQUE index.

c.        PRIMARY KEY allows duplicates; UNIQUE index does not.

d.       You can have more than one UNIQUE index and only one PRIMARY KEY.

e.       Both PRIMARY KEY and UNIQUE index do not allow duplicate values; PRIMARY allows Null.

Answer: You can have more than one UNIQUE index and only one PRIMARY KEY.

7.       DECLARE @I INT

SET @I=1

IF @I=1

                SELECT @I=2

                SET @I=@I+1

END

What is WRONG with the T-SQL code above?

a.        It is missing an ELSE

b.       The SELECT needs to be changed to SET

c.        It is missing a BEGIN

d.       The IF needs to be changed to WHILE

e.       It is missing a THEN

Answer: It is missing a BEGIN

8.       You manage a decision support system that contains numerous datetime columns. You are creating views that return the data as a string value in the format yyyy-mm-dd.

Referring to the scenario above, which one of the following contains the T-SQL function or functions that you need to use in your views in order to change datetime value to a char or varchar data type?

a.        CHAR

b.       CAST

c.        COALESCE

d.       COMPUTE

e.       COLLATE

Answer: CAST

9.       UPDATE TOP(1) Customers SET FirstName=’Ted’ WHERE LastName=’Anderson’

What does the above T-SQL Code do?

a.        It updates all rows with the LastName of Anderson to a FirstName of Ted

b.       It updates a row with the LastName of Anderson to a FirstName of Ted

c.        It updates no rows due to an error

d.       It updates all rows to a  FirstName of Ted if the LastName of Anderson is not found

e.       It updates a row with the LastName of Ted to a FirstName of Anderson

Answer: It updates a row with the LastName of Anderson to a FirstName of Ted

10.    You need to display all rows of data from TABLE1 and TABLE2, including duplicates.

Referring to the scenario above, which one of the following T-SQL statements do you use?

a.        SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2

b.       SELECT * FROM TABLE1 UNION ALL SELECT * FROM TABLE2

c.        SELECT * FROM TABLE1 UNION * SELECT * FROM TABLE2

d.       SELECT * FROM TABLE1 MERGE SELECT * FROM TABLE2

e.       SELECT * FROM TABLE1 JOIN SELECT * FROM TABLE2

Answer: SELECT * FROM TABLE1 UNION ALL SELECT * FROM TABLE2

2.       Which one of the following methods do you use to create custom error messages in SQL Server?

a.        Use the xp_addmessage extended stored procedure

b.       Insert a row in the syserrors table

c.        Use  the CREATE MESSAGE DDL statement

d.       Use sp_addmessage system stored procedure

e.       Install the Custom Error Messages add-in from the SQL Server  Resource kit

Answer: Use sp_addmessage system stored procedure

3.       CREATE INDEX T1index ON T1(F1 DESC)

Given the T-SQL code above, what type of index is created on table T1?

a.        UNIQUE

b.       NONCLUSTERED

c.        XML

d.       CLUSTERED

e.       PRIMARY KEY

Answer: NONCLUSTERED

4.       You need to retrieve a list of last names from a table with no duplicates.

Referring to the scenario above, which one of the following statements do you use?

a.        SELECT DISTINCT LASTNAME FROM TABLE1

b.       SELECT TOP 1 LASTNAME FROM TABLE1

c.        SELECT LASTNAME FROM TABLE1 WHERE COUNT=1

d.       SELECT LASTNAME FROM TABLE1 WHERE UNIQUE

e.       SELECT INTO LASTNAME FROM TABLE1

Answer: SELECT DISTINCT LASTNAME FROM TABLE1

5.       Which one of the following statements regarding Clustered and Nonclustered indexes is true?

a.        There can only be one Nonclustered index per table; there can be multiple Clustered indexes.

b.       There can be multiple Clustered and Nonclustered indexes per table.

c.        Clustered and Nonclustered indexes are the same except Nonclustered indexes sort the data rows of a table.

d.       There can only be one Clustered index per table; there can be multiple Nonclustered indexes.

e.       A Clustered index always has unique values; a Nonclustered index can have any values.

Answer: There can only be one Clustered index per table; there can be multiple Nonclustered indexes.

6.       Which one of the following forms of referential integrity sets the value of a column when no other value is specified during an insert?

a.        Trigger

b.       Baseline

c.        Rule

d.       Index

e.       Default

Answer: Default

7.       SELECT * FROM TABLE1 WHERE ITEMID IN (10, 20)

What does the T-SQL statement above display?

a.        The first 10 to 20 rows of TABLE1.

b.       All rows of ITEMID only from TABLE1 that do not have an ITEMID of 10 and 20.

c.        All rows and columns from TABLE1 that do not have an ITEMID of 10 and 20.

d.       Only columns of TABLE1 that contain 10 or 20 in their name.

e.       All rows and columns from TABLE1 that contain an ITEMID of 10 or 20.

Answer:  All rows and columns from TABLE1 that contain an ITEMID of 10 or 20.

8.       Which one of the following is NOT a type of constraint?

a.        Cascade

b.       Foreign key

c.        Primary key

d.       Unique

e.       Check

Answer: Cascade

9.       Referring to the sample code above, what is the ending value of local variable @count?

a.        0

b.       1

c.        9

d.       10

e.       12

Answer: 10

10.    Referring to the sample code above, what is the ending value of local variable @count?

a.        Null

b.       0

c.        2

d.       8

e.       16

Answer: Null

11.    CREATE TABLE test_defaults (ID int IDENTITY(1, 1) DEFAULT 1)

What is wrong with the T-SQL statement above?

a.        You cannot create a table with only one column.

b.       You cannot use the int data type on identity fields.

c.        You cannot create a default on an identity field.

d.       You need to set the default to 0 for identity fields.

e.       You need to change the "IDENTITY(1, 1)" to "IDENTITY(int, 1, 1)".

Answer: You cannot create a default on an identity field.

1. DECLARE @isTrue char(1)
2. DECLARE @var char(10)

3. SET @var = 'alphabet'
4. If @var LIKE '%ABC%'
5.   SET @isTrue = 'Y'
6. ELSE
7.   SET @isTrue = 'N'

12.    Upon completion of the query batch above and assuming DEFAULT SQL Server installation, what is the value of @isTrue?

a.        NULL--an error occurred on line 1

b.       Y

c.        NULL--an error occurred on line 4

d.       N

e.       NULL--an error occurred on line 3

Answer: N

1.       What is the ending value of variable @Var1 in the sample code above?

a.        Null

b.       1

c.        4

d.       5

e.       6

Answer: 4

2.       Which one of the following is the default value that is assigned to a varchar data type immediately after it has been declared and before a value has been explicitly assigned to it?

a.        CHAR(0)

b.       The variable is filled with spaces.

c.        CHAR(NULL)

d.       NULL

e.       0

Answer: NULL

3.       SELECT * FROM T1 WHERE F1 = 2

Referring to the T-SQL code above, if an index does NOT exist on table T1, what must SQL Server do to find data in the table?

a.        Create a view of the table.

b.       Create a temporary index.

c.        Perform a table scan.

d.       Find the first match and stop searching.

e.       Use a stored procedure.

Answer: Perform a table scan

4.       If you are logged onto SQL Server as the System Administrator, and you execute the T-SQL code above, which one of the following results do you get?

a.        1 ...\Administrator 123

b.       1 sa 123

c.        0 ...\Administrator 987

d.       0 sa 987

e.       1 dbo 987

Answer: 1 dbo 987

5.       If you need to create a table with a column that contains no duplicate values but allows for a NULL value, which one of the following index types do you use?

a.        XML

b.       Primary Key

c.        Clustered

d.       Non Clustered

e.       Unique

Answer: Unique

 

1.       SELECT * FROM PRODUCTS WHERE PRODUCTID IN (SELECT PRODUCTID FROM ONSALEPRODUCTS)

What does the T-SQL statement above select?

a.        All rows from PRODUCTID that have PRODUCTS in the ONSALEPRODUCTS table

b.       All rows from PRODUCTS that have a PRODUCTID in the PRODUCTID table

c.        All rows from ONSALEPRODUCTS that have a PRODUCTID in the PRODUCTID table

d.       All rows from ONSALEPRODUCTS that have a PRODUCTID in the PRODUCTS table

e.       All rows from PRODUCTS that have a PRODUCTID in the ONSALEPRODUCTS table

Answer: All rows from PRODUCTS that have a PRODUCTID in the ONSALEPRODUCTS table

2.       SELECT o.Price, o.Cost, (SELECT MAX(i.Quantity) FROM Items as i
WHERE i.OrderNum = o.OrderNum) as MaxQty FROM Orders as o

What does the T-SQL statement above display?

a.        The columns named Price, Cost from Items, and MaxQty from Orders

b.       All columns from Items and from Orders

c.        The columns named Price, Cost from Orders, and Quantity from Items

d.       The columns named Price, Cost from Items, and Quantity from Orders

e.       The columns named Price, Cost from Orders, and MaxQty from Items

Answer: The columns named Price, Cost from orders and MaxQty from Items

3.       RETURN @avar.query('Product/Prices')

The above T-SQL code is an example of using which one of the following data types?

a.        Nvarchar

b.       Varchar

c.        Text

d.       Ntext

e.       Xml

Answer:Xml

The stored representation of xml data type instances cannot exceed 2 GB.

4.       Which one of the following types of database objects is bound to a table and executes a batch of code whenever a specific data modification action occurs?

a.        Trigger

b.       Stored Procedure

c.        Rule

d.       Default

e.       Constraint

Answer: Trigger

5.       Which one of the following commands and or keywords CANNOT be included inside a trigger?

a.        Truncate table

b.       Drop index

c.        Create procedure

d.       Create index

e.       Update statistics

Answer: Truncate table
1.    Ansi_warnings is not honored wen error occurs in stored procedure or functions ..? true or false..T
2.    Create table emp (id int, name varchar(20), sal int) which of the following is wrong in inserting data
 Insert into emp values (1,’a’,’null’)
 Insert into emp values (1,’a’,null)
 Insert into emp(id,name)values(1,’a’)
 Insert into emp (name,id) values (‘a’,2)
3.    Which one of the following T-SQL statements is directly permitted in a Stored Procedure?
ALTER PROCEDURE
CREATE VIEW
CREATE TRIGGER
ALTER FUNCTION
CREATE TABLE
4.    When are constraints checked on a table with INSTEAD OF and AFTER triggers?
    After the INSTEAD OF triggers and before the AFTER trigger
    Before an INSTEAD OF trigger set to execute last by sp_settriggerorder
    Before the INSTEAD OF and AFTER triggers
    Before an INSTEAD OF UPDATE or INSTEAD OF INSERT trigger but after an INSTEAD OF DELETE trigger
    After the INSTEAD OF and AFTER triggers
5.Which is not a DDL?
    Alter
    Truncate
     Use
      Identity
6.which out of the foll Stored proc types comes a part of SQL server and begin with xp_?
a)system b)user defined c)extended d)clr

7.which one ensure that in a relation all attributes must be atomic and every non key attribute is fully functionally dependent on key attribute for its existence?
a)1NF b)2NF c)3NF d)4NF e)BCNF

8.which stored procedure is used to get the structure of table created???
a) sp_help
b)sp_helpconstraint
c)sp_source code
d)sp_helptext

9.not data type in procedure
a)timestamp
b)blob
c)bigint
d) XML

10.BCNF p(a,b) as a primary r(a,b,c,d,e)…….
a)it may not happen c identifies b
b) it may not happen a identifies e
c) it may happen a identifies c
d) it may happen d identifies b

11.select order.colname,customer,col.order,colname from customers,order
a)inner join
b)natural join
c)eqi join
d)Cartesian

12.reference integrity constraint tat establishes relation bet tables

13.Which of the Following stored procedurs displays the dependencies of view?
sp_help
sp_dependencies
sp_depends
sp_helpdepends

14.If values have to be inserted which constraint should be used?
not null
primary
unique
check

15.Which cursor type used for navigation?
fetch
forward
read

16.triggers can be nested to d maximum level
ans:32

17.Constraint can be applied to Table level only
 T or  F

18.Which of the Following is valid in Function?
table
text
timestamp
xml

19..which 1 of the following about creationof views in sql server  is true?
ans:view cannot have stored procedures
stored procedures  for parameter-@

2. table name is a temp table visible only on connection on which it was created #mytable

3. which among the following is a function  type
a)timestamp
b) Table
c) xml

4. what is used in cursor for navigation
a. fetch
b. read

5. should function have an open parenthesis or not when .

6. self-join keyword present or not
no

7.sme insert statement qs
Ans:insert into table values (1,’A’,null)

8.stored procedures type comes as part of sql server and begin with xp

Ans:exdended

9.sme questions in trigger

Ans:t1,t2,t3

10.if any error in transaction

Ans:rollback

11.syntax for select with subqueries

12.which is not a ddl command?

a)role
b)identity

13.wat iswrong in select statement???
a)  from is necessary thng
b) group by may be present
c) order by may
d) some long ans

14. subquery cannot start after
a)from
b) where
c)expression

15. Which one of the following keyword is NOT used with T-SQL cursors?
DEALLOCATE
FETCH
MOVENEXT
DECLARE
SET
16.which 1 of the following about creationof views in sql server  is true?

Ans:views cannot execute stored procedures

17. When are constraints checked on a table with INSTEAD OF and AFTER triggers? 
Choice 1  
 After the INSTEAD OF triggers and before the AFTER trigger 
Choice 2  
 Before an INSTEAD OF trigger set to execute last by sp_settriggerorder
Choice 3  
 Before the INSTEAD OF and AFTER triggers 
Choice 4  
 Before an INSTEAD OF UPDATE or INSTEAD OF INSERT trigger but after an INSTEAD OF DELETE trigger 
Choice 5  
 After the INSTEAD OF and AFTER triggers
18.which stored procedure is used to get the structure of table created???
a) sp_help
b)sp_helpconstraint
c)sp_source code
d)sp_helptext

19.triggers can be nested to d maximum level 32




20.not data type in procedure
a)timestamp
b)blob
c)bigint
d) XML

21. reference integrity constraint tat establishes relation bet tables

22.constraints check the validity for ddl operations:true

23.smething related with row ans:entity

24.SME THIMG WITH UNIQUE SET
a)BCNF
B)1NF
C)2NF
D)3NF
I thk it s b(not sure)
Learnt left outer join,trigger,stored procedure,normalization,triggers

Some qs on scalar datatype came

Which datatype has 2GB storage space?
 BLOB,NText,Nvarchar2, nvarchar(max)

 1nf is what??
 which normalization talks abt transitive relationship?   3NF

 which constraint makes sure a column has some value entered?  Not Null

 which keyword in cursors used to move from one row to the end
 a)scrollon
 forwardonly
 forward_only
 Scroll_only

‏‏
 features of stored procedures
 features of index on stored procedures
 a)only one index per stored procedure
 b) it is not saved in databsae
 2 more options


 2 coding questions on join came ‏‏
 like if common things from both tables come n from left table then which join is it?
Ansi-warnings are honored when making changes to table--------------T/F
Composite primary key can be applied when creating new table----------T/F
How select statement works with EXISTS?
Which is used to create and populate the table?
Select into
Select *
Which one of the following is not a DDl?
A. alter
B. truncate
C. use
D. identity
2. Which constraint makes sure that a column should be given a value?
    A. primary key
    B. foreign key
    C. not null
3. Normalization: In which form the components have highest atomicity?
    A. ----
    B. ----
    C. ----
    D. ----
4. Which type of normalization has referential properties amongst themselves?
    A. multi
5. Which of the following is directly allowed in a stored procedure?
    A. alter procedure
    B. create table
    C. creates function.
6. XP is which type of stored procedure??Extended
7. Which system stored procedure is used to display all the views? sp_tables
8. Cursor can be used up to how many levels?
    32
9. Which can be used to read values from the first value onwards using cursors?
    A. forward
    B. fast forward
10. Which of the following can be used in functions?
    A. table
    B. xml


‏‏