MS SQL Server developer interview

By admin | April 30, 2006
  1. Which of the following has the highest order of precedence?
    • Functions and Parenthesis
    • Multiplication, Division and Exponents
    • Addition and Subtraction
    • Logical Operations
  2. When designing a database table, how do you avoid missing column values for non-primary key columns?
    • Use UNIQUE constraints
    • Use PRIMARY KEY constraints
    • Use DEFAULT and NOT NULL constraints
    • Use FOREIGN KEY constraints
    • Use SET constraints

  3. Which of the following is the syntax for creating an Index?
    • CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)
    • CREATE [UNIQUE] INDEX OF tbl_name (index_columns)
    • CREATE [UNIQUE] INDEX ON tbl_name (index_columns)
    • CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
  4. Which of the following is not a valid character datatype in SQL Server?
    • BLOB
    • CHAR
    • VARCHAR
    • TEXT
    • VARTEXT
  5. Which of the following statements about SQL Server comments is false?
    • /* … */ are used for multiline comments
    • // is used for single line comments
    • – is used for single line comments
    • Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
    • ‘ is used for single line comments
  6. Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = "Ramesh" where employee_name = "Mahesh"
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = "Engineer"
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?

    • “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be

      • updated

    • Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
    • “Ramesh” will be updated to “Mahesh” and salary of engineers will also be

      • updated.

  7. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
    • DEFAULT and NOT NULL constraints
    • FOREIGN KEY constraints
    • PRIMARY KEY and UNIQUE constraints
    • IDENTITY columns
    • CHECK constraints
  8. Which of the following are not date parts?
    • quarter
    • dayofweek
    • dayofyear
    • weekday
  9. The IF UPDATE (column_name) parameter in a trigger definition will return
    TRUE in case of an INSERT statement being executed on the triggered table:

    • Yes
    • No
    • It returns TRUE only if an UPDATE query is executed
    • Both b and c
  10. Which one of the following must be specified in every DELETE statement?
    • Table Name
    • Database name
    • LIMIT clause
    • WHERE clause
    • Column Names
  11. Which one of the following correctly selects rows from the table myTable that have null in column column1?
    • SELECT * FROM myTable WHERE column1 is null
    • SELECT * FROM myTable WHERE column1 = null
    • SELECT * FROM myTable WHERE column1 EQUALS null
    • SELECT * FROM myTable WHERE column1 NOT null
    • SELECT * FROM myTable WHERE column1 CONTAINS null
  12. Is this statement true or false:
    A cursor is a pointer that identifies a specific working row within a set

    • True
    • False
  13. Which of the following commands is used to change the structure of table?
    • CHANGE TABLE
    • MODIFY TABLE
    • ALTER TABLE
    • UPDATE TABLE
  14. Consider the following statements and pick the correct answer:

    1. ceiling() - returns the smallest integer greater than or equal to the specified value
    2. floor() - returns the largest integer less than or equal to the specified value

    • 1 is true and 2 is false
    • 1 is false and 2 is true
    • Both 1 and 2 are true
    • Both 1 and 2 are false
  15. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?
    • SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
    • SELECT * FROM Persons SORT REVERSE ‘FirstName’

      • c . SELECT * FROM Persons ORDER BY ‘FirstName’

    • SELECT * FROM Persons ORDER BY FirstName DESC

      • SELECT * FROM Persons ORDER BY DESC FirstName

  16. What is the maximum value that can be stored for a datetime field?
    • Dec 31, 9999
    • Jun 6, 2079
    • Jan 1, 2753
    • Jan 1, 2100
  17. Consider the following queries:
    1. select * from employee where department LIKE "[^F-M]%”;
    2. select * from employee where department = “[^F-M]%”;
    Select the correct option:

    • Query 2 will return an error
    • Both the queries will return the same set of records
    • Query 2 is perfectly correct
    • Query 2 would return one record less than Query 1
  18. How can you view the structure of a table named “myTable” in SQL Server?
    • desc myTable
    • desc table myTable
    • sp_columns myTable
    • None of the above
    • Using either option a or c
  19. What does referential integrity (also called relational integrity) prevent?
    • Loss of data from employee sabotage
    • Loss of data from any one corrupted table
    • Recursive joins
    • One-to-many or many-to-many relationships between columns in a table
    • Data redundancy
  20. Which of the following is not a global variable?
    • @@colcount
    • @@error
    • @@rowcount
    • @@version
    • All are valid global variables
  21. Consider the following two tables:

    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch

    • It will return the fields customer_id, customer_name, branch_name
    • It will return the fields customer_id, customer_name, branch_id, branch_name
    • It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
    • It will return an empty set since the two tables do not have any common field name
    • It will return an error since * is used alone for one table only
  22. Which of the following is not a control statement?
    • if…else
    • if exists
    • do…while
    • while
    • begin…end
  23. Which of the following is not a valid Numeric datatypes in SQL Server?
    • INT
    • SMALLINT
    • TINYINT
    • BIGINT
    • MONEY
  24. Which of the following datatypes is not supported by SQL-Server?
    • Character
    • Binary
    • Logical
    • Date
    • Numeric
    • All are supported
  25. What will the output be if you try to perform arithmetic on NULL values?
    • 0
    • NULL
    • It will generate an error message
    • Can’t be determined
  26. Which of the following options is not correct about the DATEDIFF() function?
    • It returns the difference between parts of two specified dates
    • It takes three arguments
    • It returns a signed integer value equal to second date part minus first date part
    • It returns a signed integer value equal to first date part minus second date part
  27. Sample Code

    CREATE TABLE table1(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?

    • ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;
    • MODIFY TABLE table1 ADD column2a AFTER column2;
    • INSERT INTO table1 column2a AS varchar(50) AFTER column2;
    • ALTER TABLE table1 INSERT column2a varchar(50) AFTER column2;
    • CHANGE TABLE table1 INSERT column2a BEFORE column3;
  28. State which of the following are true
    • Views are a logical way of looking at the logical data located in the tables
    • Views are a logical way of looking at the physical data located in the tables
    • Tables are physical constructs used for storage and manipulation of data in databases
    • Tables are logical constructs used for storage and manipulation of data in databases
  29. Which of the following is not a valid binary datatype in SQL Server?
    • BINARY
    • VARBINARY
    • BIT
    • IMAGE
    • TESTAMP
  30. Which of the following is false with regards to sp_help?
    • When a procedure name is passed to sp_help, it shows the parameters
    • When a table name is passed to sp_help, it shows the structure of the table
    • When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
    • All of the above are true
    • Which of the following are false for batches (batch commands)?
      • Statements in a batch are parsed, compiled and executed as a group
      • None of the statements in the batch is executed if there are any syntax errors in the batch
      • None of the statements in the batch is executed if there are any parsing errors in the batch
      • None of the statements in the batch is executed if there are any fatal errors in the batch
    • Select the correct option:
      • Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
      • Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer
This entry was posted in Database, VB. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

23 Comments on MS SQL Server developer interview

  1. Baljeet Chawla
    Posted 5/5/2006 at 4:03 am | Permalink

    2. When designing a database table, how do you avoid missing column values for non-primary key columns?

    Ans. Use DEFAULT and NOT NULL constraints

    3. Which of the following is the syntax for creating an Index?

    Ans. CREATE [UNIQUE] INDEX ON tbl_name (index_columns)

    4. Which of the following is not a valid character datatype in SQL Server?

    Ans. VARTEXT

    6. Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?

    Ans. “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be
    updated.

    7. Which of the following constraints can be used to enforce the uniqueness of rows in a table?

    Ans. PRIMARY KEY and UNIQUE constraints

    8. Which of the following are not date parts?

    Ans. dayofweek

    10. Which one of the following must be specified in every DELETE statement?

    Ans. Table Name

    11. Which one of the following correctly selects rows from the table myTable that have null in column column1?

    Ans. SELECT * FROM myTable WHERE column1 is null

    12. Is this statement true or false:
    A cursor is a pointer that identifies a specific working row within a set

    Ans. True

    13. Which of the following commands is used to change the structure of table?

    Ans. ALTER Table

    14. Consider the following statements and pick the correct answer:

    1. ceiling() - returns the smallest integer greater than or equal to the specified value
    2. floor() - returns the largest integer less than or equal to the specified value

    Ans. Both 1 and 2 are true

    15. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

    Ans. SELECT * FROM Persons ORDER BY FirstName DESC

    16. What is the maximum value that can be stored for a datetime field?

    Ans. Dec 31, 9999

    18. How can you view the structure of a table named “myTable” in SQL Server?

    Ans. desc myTable

    19.What does referential integrity (also called relational integrity) prevent?

    Ans. One-to-many or many-to-many relationships between columns in a table

    20.Which of the following is not a global variable?

    Ans.@@colcount

    21. Consider the following two tables:

    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch

    Ans. none of all

    22. Which of the following is not a control statement?

    Ans. if exists

    23. Which of the following is not a valid Numeric datatypes in SQL Server?

    Ans.

    24.Which of the following datatypes is not supported by SQL-Server?

    Ans. Logical

    25. What will the output be if you try to perform arithmetic on NULL values?

    Ans.NULL

    26. What will the output be if you try to perform arithmetic on NULL values?

    Ans. It returns a signed integer value equal to first date part minus second date part

    27. Sample Code

    CREATE TABLE table1(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?

    Ans. none of all

    29. Which of the following is not a valid binary datatype in SQL Server?

    Ans. TESTAMP

  2. Rajesh S. Chandan
    Posted 5/20/2006 at 7:29 am | Permalink

    Correct Answer of Question 3 is :
    CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
    bz it required the name of the index.

    Thanks.

  3. Rajesh S. Chandan
    Posted 5/20/2006 at 7:59 am | Permalink

    When designing a database table, how do you avoid missing column values for non-primary key columns?
    Use DEFAULT and NOT NULL constraints

    Which of the following is the syntax for creating an Index?
    CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)

    Which of the following is not a valid character datatype in SQL Server?
    BLOB
    VARTEXT

    Which of the following statements about SQL Server comments is false?
    – is used for single line comments
    ‘ is used for single line comments

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?
    Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.

    Which of the following constraints can be used to enforce the uniqueness of rows in a table?
    PRIMARY KEY and UNIQUE constraints

    Which of the following are not date parts?
    dayofweek

    The IF UPDATE (column_name) parameter in a trigger definition will return
    TRUE in case of an INSERT statement being executed on the triggered table:
    No

    Which one of the following must be specified in every DELETE statement?
    Table Name

    Which one of the following correctly selects rows from the table myTable that have null in column column1?
    SELECT * FROM myTable WHERE column1 is null

    Is this statement true or false:
    A cursor is a pointer that identifies a specific working row within a set
    True

    Which of the following commands is used to change the structure of table?
    ALTER TABLE

    Consider the following statements and pick the correct answer:
    1. ceiling() - returns the smallest integer greater than or equal to the specified value
    2. floor() - returns the largest integer less than or equal to the specified value
    Both 1 and 2 are true

    What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?
    SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC

    What is the maximum value that can be stored for a datetime field?
    Dec 31, 9999

    Consider the following queries:
    1. select * from employee where department LIKE “[^F-M]%”;
    2. select * from employee where department = “[^F-M]%”;
    Select the correct option:
    Query 2 will return an error

    How can you view the structure of a table named “myTable” in SQL Server?
    None of the above

    What does referential integrity (also called relational integrity) prevent?
    Data redundancy
    Which of the following is not a global variable?
    @@colcount

    Consider the following two tables:

    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch

    It will return an error since * is used alone for one table only

    Which of the following is not a control statement?
    begin…end

    Which of the following is not a valid Numeric datatypes in SQL Server?
    TINYINT

    Which of the following datatypes is not supported by SQL-Server?
    Logical

    What will the output be if you try to perform arithmetic on NULL values?
    NULL

    Which of the following options is not correct about the DATEDIFF() function?
    It returns the difference between parts of two specified dates
    It takes three arguments
    It returns a signed integer value equal to second date part minus first date part

    CREATE TABLE table1(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?
    Nothing not possible through Query Analyzer.

    State which of the following are true
    Views are a logical way of looking at the logical data located in the tables

    Which of the following is not a valid binary datatype in SQL Server?
    IMAGE

    Which of the following is false with regards to sp_help?
    When a procedure name is passed to sp_help, it shows the parameters
    When a table name is passed to sp_help, it shows the structure of the table
    When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database

    All of the above are true

    Which of the following are false for batches (batch commands)?
    Statements in a batch are parsed, compiled and executed as a group
    None of the statements in the batch is executed if there are any syntax errors in the batch
    None of the statements in the batch is executed if there are any parsing errors in the batch
    None of the statements in the batch is executed if there are any fatal errors in the batch
    Select the correct option:
    Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer

    Please send me the comments by mail if you are not agree with me.
    Thank you very much for your passion.

    Rajesh

  4. Sudhir Malik
    Posted 5/23/2006 at 12:46 am | Permalink

    18. How can you view the structure of a table named “myTable” in SQL Server?
    a.desc myTable
    b.desc table myTable
    c.sp_columns myTable
    d.None of the above
    e.Using either option a or c

    Ans. c. sp_columns myTable
    (Verified and Tested Ok.) by Sudhir Malik (IIT Roorkee, Graduate)

  5. sumith
    Posted 7/6/2006 at 3:20 am | Permalink

    what is the difference between primarykey and foreignKey in mssql

  6. mdate
    Posted 7/17/2006 at 4:02 pm | Permalink

    Which of the following has the highest order of precedence?
    Functions and Parenthesis
    Multiplication, Division and Exponents
    Addition and Subtraction
    Logical Operations

    Functions and Parenthesis

  7. Joe Gakenheimer
    Posted 7/20/2006 at 3:22 pm | Permalink

    Which of the following commands is used to change the structure of table?
    * ALTER TABLE

  8. Prateek Arora
    Posted 8/3/2006 at 12:54 pm | Permalink

    18. How can you view the structure of a table named “myTable” in SQL Server?
    a.desc myTable
    b.desc table myTable
    c.sp_columns myTable
    d.None of the above
    e.Using either option a or c

    Ans. c. sp_columns myTable

    or else you can also use sp_help mytable

  9. srinivasa rao
    Posted 8/4/2006 at 7:38 am | Permalink

    How duplicate record will be delete in sql2000?

  10. Sudeep
    Posted 8/14/2006 at 6:40 am | Permalink

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?

    Question #6 If you try to execute this statement it will give you error saying “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.”
    Please correct the question.
    this should be as
    ….
    ….
    Rollback transaction SAVE_POINT
    Commit transaction

    Then the answer will be “o“Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be updated”
    Becasue the SAVE_POINT will save the transaction and rollback will effect only till the save point. so the second part of the query will be rolledback but the statement above the Save Point is saved and will be commited.

    Please correct me if i am wrong.

    Thanks,
    Sudeep Srivastava

  11. helper
    Posted 8/18/2006 at 7:04 pm | Permalink

    What will the output be if you try to perform arithmetic on NULL values?
    Ans:0
    Which of the following is not a valid binary datatype in SQL Server?
    TESTAMP
    Which one of the following correctly selects rows from the table myTable that have null in column column1?
    None

  12. helper
    Posted 8/18/2006 at 7:05 pm | Permalink

    Which of the following statements about SQL Server comments is false?
    Ans:
    // is used for single line comments
    – is used for single line comments
    ‘ is used for single line comments

  13. LM
    Posted 1/10/2007 at 2:28 pm | Permalink

    23.Which of the following is not a valid Numeric datatypes in SQL Server?

    * INT
    * SMALLINT
    * TINYINT
    * BIGINT
    * MONEY
    A: All are valid

  14. LM
    Posted 1/10/2007 at 2:29 pm | Permalink

    MONEY is not a numeric

  15. Rajesh.M
    Posted 1/23/2007 at 8:49 am | Permalink

    1.Which of the following has the highest order of precedence?
    Functions and Parenthesis

    2.When designing a database table, how do you avoid missing column values for non-primary key columns?

    Use DEFAULT and NOT NULL constraints

    3.Which of the following is the syntax for creating an Index?

    CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)

    4.Which of the following is not a valid character datatype in SQL Server?

    VARTEXT

    5.Which of the following statements about SQL Server comments is false?

    ‘

    6.Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?

    “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be

    7.PRIMARY KEY and UNIQUE constraints

    8.quarter
    9.nnn
    10.table
    11.SELECT * FROM myTable WHERE column1 = null
    12.true
    13.ALTER TABLE
    14true
    15.SELECT * FROM Persons ORDER BY FirstName DESC
    16.Dec 31, 9999
    17.Query 2 will return an error
    18.sp_columns myTable
    19.One-to-many or many-to-many relationships between columns in a table
    20.All are valid global variables
    21.It will return the fields customer_id, customer_name, branch_id, branch_name
    22.begin…end
    23.MONEY
    24.All are supported
    25.NULL
    26.It returns a signed integer value equal to second date part minus first date part
    27.ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;
    28.Views are a logical way of looking at the logical data located in the tables
    29.TESTAMP

  16. bsrd
    Posted 3/13/2007 at 6:57 am | Permalink

    23.Which of the following is not a valid Numeric datatypes in SQL Server?

    * INT
    * SMALLINT
    * TINYINT
    * BIGINT
    * MONEY
    A: MONEY (see data types overview in SQL Book)

  17. vigneshwaran.balu
    Posted 6/30/2007 at 2:30 am | Permalink

    .ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;

    The above showned code is not correct code to add the new column inbetween the existing column

    if u know pls forward to my id

  18. Bhavin Parikh
    Posted 1/8/2008 at 4:01 am | Permalink

    17. Consider the following queries:
    1. select * from employee where department LIKE “[^F-M]%”;
    2. select * from employee where department = “[^F-M]%”;
    Select the correct option:
    * Query 2 will return an error
    * Both the queries will return the same set of records
    * Query 2 is perfectly correct
    * Query 2 would return one record less than Query 1
    Ans. Both the queries will return the same set of records

  19. Prabhat Jana
    Posted 1/8/2008 at 10:59 am | Permalink

    21. Consider the following two tables:

    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch

    Ans. 3. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name

  20. RaiS
    Posted 1/16/2008 at 5:07 am | Permalink

    1. Which of the following has the highest order of precedence?
    Functions and Parenthesis

    2. When designing a database table, how do you avoid missing column values for non-primary key columns?
    Use DEFAULT and NOT NULL constraints

    3. Which of the following is the syntax for creating an Index?
    CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)

    4. Which of the following is not a valid character datatype in SQL Server?
    BLOB
    VARTEXT

    5.Which of the following statements about SQL Server comments is false?
    – is used for single line comments
    ‘ is used for single line comments

    6.Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction

    What will be the result produced by this transaction?

    Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
    “Ramesh” will be updated to “Mahesh” and salary of engineers will also be
    updated.

    7. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
    PRIMARY KEY and UNIQUE constraints

    8. Which of the following are not date parts?
    quarter
    dayofweek

    9. The IF UPDATE (column_name) parameter in a trigger definition will return
    TRUE in case of an INSERT statement being executed on the triggered table:

    Yes

    10. Which one of the following must be specified in every DELETE statement?
    Table Name

    11.Which one of the following correctly selects rows from the table myTable that have null in column column1?
    SELECT * FROM myTable WHERE column1 is null

    12. Is this statement true or false:
    A cursor is a pointer that identifies a specific working row within a set

    True

    13. Which of the following commands is used to change the structure of table?
    ALTER TABLE

    14.Consider the following statements and pick the correct answer:

    1. ceiling() - returns the smallest integer greater than or equal to the specified value
    2. floor() - returns the largest integer less than or equal to the specified value

    1 is true and 2 is false

    15. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

    SELECT * FROM Persons ORDER BY FirstName DESC

    16.What is the maximum value that can be stored for a datetime field?
    Dec 31, 9999

    17. Consider the following queries:
    1. select * from employee where department LIKE “[^F-M]%”;
    2. select * from employee where department = “[^F-M]%”;
    Select the correct option:

    Query 2 will return an error

    18. How can you view the structure of a table named “myTable” in SQL Server?
    sp_columns myTable

    19.What does referential integrity (also called relational integrity) prevent?
    One-to-many or many-to-many relationships between columns in a table

    20.Which of the following is not a global variable?
    @@colcount

    21. Consider the following two tables:
    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch

    It will return an error since * is used alone for one table only

    22. Which of the following is not a control statement?
    if exists

    23. Which of the following is not a valid Numeric datatypes in SQL Server?
    MONEY _ It is money type.

    24. Which of the following datatypes is not supported by SQL-Server?
    Logical

    25. What will the output be if you try to perform arithmetic on NULL values?
    NULL

    26. Which of the following options is not correct about the DATEDIFF() function?
    It returns a signed integer value equal to first date part minus second date part

    27.Sample Code

    CREATE TABLE table1(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?
    None of the above

    28.State which of the following are true
    Views are a logical way of looking at the physical data located in the tables
    Tables are physical constructs used for storage and manipulation of data in databases

    29.Which of the following is not a valid binary datatype in SQL Server?
    BIT
    TESTAMP

    30. Which of the following is false with regards to sp_help?
    All of the above are true
    31. Which of the following are false for batches (batch commands)?
    Statements in a batch are parsed, compiled and executed as a group
    None of the statements in the batch is executed if there are any syntax errors in the batch

    32.Select the correct option:
    Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer

  21. Anne Xu
    Posted 4/10/2008 at 11:40 am | Permalink

    1.Which of the following has the highest order of precedence?
    Functions and Parenthesis

    2.When designing a database table, how do you avoid missing column values for non-primary key columns?
    Use DEFAULT and NOT NULL constraints

    3.Which of the following is the syntax for creating an Index?
    CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
    4.Which of the following is not a valid character datatype in SQL Server?
    VARTEXT
    5.Which of the following statements about SQL Server comments is false?
    // is used for single line comments
    – is used for single line comments
    Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
    ‘ is used for single line comments
    6.Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?

    “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be
    updated

    7.Which of the following constraints can be used to enforce the uniqueness of rows in a table?
    PRIMARY KEY and UNIQUE constraints
    8.Which of the following are not date parts?
    dayofweek
    9.The IF UPDATE (column_name) parameter in a trigger definition will return
    TRUE in case of an INSERT statement being executed on the triggered table:
    No
    It returns TRUE only if an UPDATE query is executed
    Both b and c
    10.Which one of the following must be specified in every DELETE statement?
    Table Name
    11.Which one of the following correctly selects rows from the table myTable that have null in column column1?
    SELECT * FROM myTable WHERE column1 is null
    12.Is this statement true or false:
    A cursor is a pointer that identifies a specific working row within a set

    True
    13.Which of the following commands is used to change the structure of table?
    ALTER TABLE
    14.Consider the following statements and pick the correct answer:

    1. ceiling() - returns the smallest integer greater than or equal to the specified value
    2. floor() - returns the largest integer less than or equal to the specified value

    Both 1 and 2 are true
    15.What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

    SELECT * FROM Persons ORDER BY FirstName DESC

    16.What is the maximum value that can be stored for a datetime field?
    Dec 31, 9999
    17.Consider the following queries:
    1. select * from employee where department LIKE “[^F-M]%”;
    2. select * from employee where department = “[^F-M]%”;
    Select the correct option:

    Query 2 is perfectly correct

    18.How can you view the structure of a table named “myTable” in SQL Server?
    sp_columns myTable
    19.What does referential integrity (also called relational integrity) prevent?
    ?One-to-many or many-to-many relationships between columns in a table
    ?Data redundancy
    20.Which of the following is not a global variable?
    All are valid global variables
    21.Consider the following two tables:

    1. customers( customer_id, customer_name)
    2. branch ( branch_id, branch_name )
    What will be the output if the following query is executed:
    Select * branch_name from customers,branch

    It will return an error since * is used alone for one table only
    22.Which of the following is not a control statement?
    if exists
    23.Which of the following is not a valid Numeric datatypes in SQL Server?
    MONEY
    24.Which of the following datatypes is not supported by SQL-Server?
    Logical
    25.What will the output be if you try to perform arithmetic on NULL values?
    NULL
    26.Which of the following options is not correct about the DATEDIFF() function?
    It returns a signed integer value equal to first date part minus second date part
    27.Sample Code

    CREATE TABLE table1(
    column1 varchar(50),
    column2 varchar(50),
    column3 varchar(50),
    column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?
    None of them
    28.State which of the following are true
    Views are a logical way of looking at the physical data located in the tables
    Tables are physical constructs used for storage and manipulation of data in databases
    29.Which of the following is not a valid binary datatype in SQL Server?
    TESTAMP
    30.Which of the following is false with regards to sp_help?
    When a procedure name is passed to sp_help, it shows the parameters
    All of the above are true
    31.Which of the following are false for batches (batch commands)?

    None of the statements in the batch is executed if there are any parsing errors in the batch
    32.Select the correct option:
    Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer

  22. hades
    Posted 4/22/2008 at 12:39 pm | Permalink

    What about problems like:
    Select the max values without using the max funtion, etc.

  23. thunder6
    Posted 1/24/2009 at 4:00 pm | Permalink

    6.Consider the following transaction code:

    Begin Transaction
    Update names_table set employee_name = “Ramesh” where employee_name = “Mahesh”
    Save Transaction SAVE_POINT
    Update salaries set salary=salary + 900 where employee_job = “Engineer”
    Rollback transaction
    Commit transaction
    What will be the result produced by this transaction?

    o Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.

    bcos the ROLLBACK TRANSACTION statement will undo everything as the save point is not mentioned. ‘ROLLBACK TRANSACTION save_point’ will only undo transaction made after the save point
    ref:- ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/b953c3f1-f96d-42f1-95a2-30e314292b35.htm

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*