SQL Server T-SQL Quiz-I

10 Questions | Total Attempts: 1135

SettingsSettingsSettings
Please wait...
T SQL Quizzes & Trivia

If you are a computer nerd that is really into programming then this quiz is specifically for you. It aims to find out how much knowledge you have on the SQL server T-SQL. All the best.


Related Topics
Questions and Answers
  • 1. 
    What does the select statement return? Create Table #T (        N1 Real      , N2 Float);   Insert Into #T Values (99.99999999991       , 99.99999999997);   Select * From #T;   Drop Table #T;
    • A. 

      The select statement returns < 100 for N1 and 100 for N2

    • B. 

      The select statement returns 100 for N1 and < 100 for N2

    • C. 

      The select statement returns < 100 for both columns

    • D. 

      The select statement returns 100 for both columns

  • 2. 
    What will be the output of below query when it is executed? (In the answer options, the rows are comma separated) SELECT TOP(6) WITH TIES COL FROM ( SELECT 1 COL UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 4 ) A  ORDER BY COL
    • A. 

      1,2,3,3,4,4

    • B. 

      1,2,3,4,5

    • C. 

      1,2,3,3,4,4,4

    • D. 

      1,2,3,3,4,4,5

  • 3. 
    Create table #temptable1 (name1 char(10),  name2 char(10),  zip int ) GO   insert into #temptable1 values (NULL,NULL,11), ('1','2a',NULL), (NULL,'a2',111), (NULL,NULL,NULL) GO select name1,name2,zip ,COALESCE(name1,name2,zip) AS name3  from #temptable1 While executing the select statement, an error for a data type conversion is returned. Which row (numbered in insert order) gives the error?
    • A. 

      1

    • B. 

      2

    • C. 

      3

    • D. 

      4

  • 4. 
    What is the number of locks held by any T-SQL statement on a single reference of a table that triggers Lock Escalation (i.e. Row lock to Page level lock)?
    • A. 

      200

    • B. 

      1000

    • C. 

      50

    • D. 

      5000

  • 5. 
    True or False: Stored procedures are optimized at creation – and their optimized/compiled plan is saved to disk.
    • A. 

      True

    • B. 

      False

  • 6. 
    What is returned? create procedure dbo.testProc @testVar varchar(4) as select @testVar GO exec testProc 'This is a test string' GO
    • A. 

      This is a test string

    • B. 

      This

    • C. 

      Error: string or binary data would be truncated.

    • D. 

      NULL

  • 7. 
    I have a table contains a large number of rows (the relevant table structure is abbreviated here) for this CREATE TABLE FirstTable (ID INT, Col VARCHAR(100)) GO CREATE INDEX IX_Col ON FirstTable (Col);   I have this stored procedure CREATE PROC Test_1    @Get NVARCHAR(20) AS  SELECT ID,Col   FROM FirstTable   WHERE Col = @Get; The execution of Test_1 results in a table scan. I can NOT alter the table in any way. I have permission to alter the procedure. The question is: Can I alter the procedure to do an index seek?
    • A. 

      True

    • B. 

      False

  • 8. 
    I have three select statements with different cast options : -- a. select CAST('aaaaaaaaaaaaaaaaaaaaabb' as varchar) -- b. select CAST('aaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbcccccccc' as varchar) -- c. select CAST('aaaaaaaaaaaaaaaaaaaaabbddddddddddddFF' as varchar(100)) Among these three which gives correct output, meaning the complete string inside quotes is returned?
    • A. 

      A and b

    • B. 

      A and c

    • C. 

      B and c

    • D. 

      Only c

  • 9. 
    I execute the following query on SQL Server Management Studio: Query1: Create Table #Table1(Col1 int, Col2 Varchar(50), Col3 DateTime) Then I open another query window and execute the following query: Query2: Create Table #Table1(Col1 int, Col2 Varchar(50)) A column is different in query 1 and query2, but the table name is same. Will query 2 execute successfully?
    • A. 

      Yes

    • B. 

      No

  • 10. 
    Create table address_staging   (clientid int primary key,addressdetails varchar(250));   insert into address_staging  select 100,'hyderbad,india'  union all  select 101,'banglore,india'  union all  select 102,'banglore,india' ; create table address_oltp  (client_id int primary key,address_details varchar(250));   insert into address_oltp  select 104,'newyork,usa'  union all  select 105,'chicago,usa'  union all  select 106,'washington,usa' ; select *  from address_oltp  where client_id in (select client_id from address_staging) How many rows are returned from the last SELECT?
    • A. 

      No rows and no errors.

    • B. 

      3 rows from address_oltp

    • C. 

      3 rows from address_staging

    • D. 

      No rows, but a syntax error is returned.