SQL Server T-SQL Quiz-I

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Guna24x7
G
Guna24x7
Community Contributor
Quizzes Created: 4 | Total Attempts: 3,017
Questions: 10 | Attempts: 2,563

SettingsSettingsSettings
SQL Server T-SQL Quiz-I - Quiz

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.


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

    Correct Answer
    B. The select statement returns 100 for N1 and < 100 for N2
  • 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

    Correct Answer
    A. 1,2,3,3,4,4
    Explanation
    The TOP(6) WITH TIES clause is used to retrieve the top 6 rows from the result set, including ties. In this case, the result set is created by the UNION ALL of selected values. The ORDER BY clause then sorts the result set based on the COL column in ascending order. The WITH TIES ensures that if there are ties (equal values), those additional tied rows are included in the output.

    Rate this question:

  • 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

    Correct Answer
    C. 3
    Explanation
    The error occurs in row number 3 because the COALESCE function is trying to convert the value 'a2' to an integer data type for the zip column. Since 'a2' cannot be converted to an integer, a data type conversion error is returned.

    Rate this question:

  • 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

    Correct Answer
    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

    Correct Answer
    B. False
    Explanation
    Stored procedures are not optimized at creation and their optimized/compiled plan is not saved to disk. The optimization process for stored procedures occurs at runtime when they are executed. The database management system analyzes the query and creates an execution plan based on the current state of the database and the available resources. This allows for flexibility and adaptability in the optimization process, as the execution plan can be adjusted based on the specific conditions at the time of execution.

    Rate this question:

  • 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

    Correct Answer
    B. This
    Explanation
    The given code snippet is creating a stored procedure called testProc that takes a parameter called @testVar of type varchar(4). Inside the procedure, the @testVar is selected, which means it will be returned as the result of the procedure.

    When the stored procedure is executed with the parameter 'This is a test string', the result returned will be 'This'. However, it is important to note that an error message is also displayed stating "string or binary data would be truncated". This error occurs because the length of the input string exceeds the maximum length defined for the @testVar parameter, which is 4 characters.

    Rate this question:

  • 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

    Correct Answer
    A. True
    Explanation
    The stored procedure Test_1 can be altered to do an index seek by modifying the WHERE clause of the SELECT statement. Instead of using the equality operator (=) to match the Col column with the @Get parameter, a range or inequality operator (such as >, =,

    Rate this question:

  • 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

    Correct Answer
    B. A and c
    Explanation
    The correct answer is "a and c". In statement a, the cast option is not specified, so the default cast is applied which returns the complete string inside quotes. In statement c, the cast option is specified as varchar(100), which also returns the complete string inside quotes. Statement b does not return the complete string inside quotes because the length of the string exceeds the default length for varchar.

    Rate this question:

  • 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

    Correct Answer
    A. Yes
    Explanation
    Yes, query 2 will execute successfully. In SQL Server Management Studio, when a table is created with a temporary table name (#Table1 in this case), it is only accessible within the session or connection where it was created. Therefore, even though query 2 has a different column compared to query 1, it will not cause any conflicts because it is being executed in a separate query window. Each query window has its own session and can create its own temporary tables with the same name as long as they are not accessed simultaneously.

    Rate this question:

  • 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.

    Correct Answer
    B. 3 rows from address_oltp
    Explanation
    The last SELECT statement returns 3 rows from the address_oltp table.

    Rate this question:

Related Topics

Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.