SQL Server T-SQL Quiz-I
(162).jpg "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,4Explanation
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. 3Explanation
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. FalseExplanation
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. ThisExplanation
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. TrueExplanation
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 cExplanation
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. YesExplanation
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
D. No rows, but a syntax error is returned.Explanation
The last SELECT statement is trying to retrieve rows from the address_oltp table where the client_id exists in the address_staging table. However, there is a mismatch in column names between the two tables. In the address_staging table, the primary key is named clientid, while in the address_oltp table, it is named client_id.
The subquery select client_id from address_staging will result in a syntax error because there is no column named client_id in the address_staging table. The correct column name should be clientid.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Feb 05, 2024Quiz Edited by
ProProfs Editorial Team -
Jul 19, 2013Quiz Created by
Guna24x7
Back to top