70-761 Querying Data with Transact-SQL Ερωτήσεις & Απαντήσεις

70-761 Querying Data with Transact-SQL Ερωτήσεις & Απαντήσεις

Το 70-761 Querying Data with Transact-SQL Ερωτήσεις & Απαντήσεις, είναι ερωτήσεις σε μορφή Quiz μέσα σε μια Δωρεάν Αντρόιντ εφαρμογή που την λένε SQL 70-761 Preparation Exam Demo δημοσιευμένη στο Google Play. Περισσότερες ερωτήσεις και απαντήσεις, μπορείτε να βρείτε στην πληρωτέα έκδοση της ίδιας εφαρμογής που την λένε SQL 70-761 Preparation Exam επίσης στο Google Play. Δείτε περισσότερα για την εφαρμογή ή άλλα SQL ερωτηματολόγια εδώ στο UncoverThe.com.

1. You have a table: Culture

CREATE TABLE [Production].[Culture](
[CultureID] [nchar](6) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Culture_CultureID] PRIMARY KEY CLUSTERED ([CultureID] ASC))

You insert 3 rows in one statement, but the second row will give an error (NULL value in a NOT NULL column). How many rows will be inserted at the end of the statement?

INSERT INTO [Production].[Culture]([CultureID], [Name], [ModifiedDate])
VALUES (‘gr’, ‘Greek’, GETDATE()), (‘dk’,NULL,GETDATE()), (‘fr’,’French’,GETDATE())

Possible Answers:

1. Only the first value (Greek)
2. The first value and the last value (Greek, French)
3. No values at all

3. You have a table SalesPerson. You want to find out who has the biggest bonus per TerritoryID. Which script will return the right results?

CREATE TABLE [Sales].[SalesPerson](
[BusinessEntityID] [int] NOT NULL,
[TerritoryID] [int] NULL,
[SalesQuota] [money] NULL,
[Bonus] [money] NOT NULL,
CONSTRAINT [PK_SalesPerson_BusinessEntityID] PRIMARY KEY CLUSTERED ([BusinessEntityID] ASC))

A. SELECT *
FROM [Sales].[SalesPerson] SP
WHERE SP.[Bonus] > ANY (SELECT SP2.[Bonus] FROM [Sales].[SalesPerson] SP2
WHERE SP2.[TerritoryID] = SP.[TerritoryID] AND SP.[BusinessEntityID] <> SP2.[BusinessEntityID])
AND SP. [TerritoryID] IS NOT NULL
B. SELECT *
FROM [Sales].[SalesPerson] SP
WHERE SP.[Bonus] > ALL (SELECT SP2.[Bonus] FROM [Sales].[SalesPerson] SP2
WHERE SP2.[TerritoryID] = SP.[TerritoryID] AND SP.[BusinessEntityID] <> SP2.[BusinessEntityID])
AND SP. [TerritoryID] IS NOT NULL
C. SELECT *
FROM [Sales].[SalesPerson] SP
WHERE SP.[Bonus] > SOME(SELECT SP2.[Bonus] FROM [Sales].[SalesPerson] SP2
WHERE SP2.[TerritoryID] = SP.[TerritoryID] AND SP.[BusinessEntityID] <> SP2.[BusinessEntityID])

Possible Answers:

1. A
2. B
3. C

7. You create an Audit table for your Human Resource Department. You want to retrieve all records from you Temporal and History table. Which command to use?

CREATE TABLE [HumanResources].[Employee_Temporal](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[JobTitle] [nvarchar](50) NOT NULL,
[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Employee_History_BusinessEntityID] PRIMARY KEY CLUSTERED ([BusinessEntityID] ASC ) ,
PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]) ) ON [PRIMARY] WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [HumanResources].[Employee_Temporal_History] ))

Possible Answers:

1. SELECT * FROM [HumanResources].[Employee_Temporal] FOR SYSTEM_TIME ALL
2. SELECT * FROM [HumanResources].[Employee_Temporal] 3. SELECT * FROM [HumanResources].[Employee_Temporal] FOR SYSTEM_TIME AS OF ‘9999-12-31 23:59:59.9999999’

9. In your database you have two tables: [Sales].[Customer] and [Sales].[SalesOrderHeader]. You want to find the Customers which have sales of more than 100000 and at least 2 sales. You want to display those individual sales and their sum. Is the following query ok?

SELECT CUST.[CustomerID], CUST.[AccountNumber],ORDERS.[SubTotal], ORDERS.[SALESCOUNT], ORDERS.[SUM_SALES] FROM [Sales].[Customer] CUST
CROSS APPLY
(SELECT SOH.[CustomerID], SOH.[SubTotal], COUNT(SOH.[SalesOrderID]) OVER(PARTITION BY SOH.[CustomerID]) AS SALESCOUNT, SUM(SOH.[SubTotal]) OVER() AS SUM_SALES
FROM [Sales].[SalesOrderHeader] SOH
WHERE [SubTotal] > 100000 AND SOH.[CustomerID] = CUST.[CustomerID]) ORDERS
WHERE ORDERS.[SALESCOUNT] >= 2
ORDER BY CUST.[CustomerID] ASC

Possible Answers:

1. Yes
2. No

11. You have 2 tables: [Sales].[SalesOrderHeader] and [Sales].[Customer]. You want to find all the Customers (CustomerID) which have not placed any orders. Which query to choose?

Possible Answers:

1. SELECT [CustomerID] FROM [Sales].[SalesOrderHeader] EXCEPT
SELECT [CustomerID] FROM [Sales].[Customer]

2. SELECT CUST.[CustomerID] FROM [Sales].[SalesOrderHeader] SOH
RIGHT JOIN [Sales].[Customer] CUST
ON SOH.[CustomerID] = CUST.[CustomerID] WHERE SOH.[CustomerID] IS NULL

3. SELECT [CustomerID] FROM [Sales].[Customer] INTERSECT
SELECT [CustomerID] FROM [Sales].[SalesOrderHeader]

13. You have a table with all your sales. You would like to return the Creditcard numbers which were used in the years 2014 and 2013. You want to find out which same card ids have been used in those 2 years.

CREATE TABLE [Sales].[SalesOrderHeader](
[SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CreditCardID] [int] NULL
CONSTRAINT [PK_SalesOrderHeader2_SalesOrderID] PRIMARY KEY CLUSTERED ([SalesOrderID] ASC) )

Possible Answers:

1. SELECT [CreditCardID] FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2014
INTERSECT
SELECT [CreditCardID] FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2013
2. SELECT [CreditCardID] FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2014
UNION ALL
SELECT [CreditCardID] FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2013
3. SELECT [CreditCardID] FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2014
EXCEPT
SELECT [CreditCardID] FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2013

15. On the table [Sales].[Store] you create an index on the [Name] column which has data type NVARCHAR(50) in order to have faster queries. What is the result of this query concerning index usage?

CREATE INDEX IX_NAMES ON [Sales].[Store]([Name])

When you run the following query, what will be the result?

DECLARE @NAMES VARCHAR(50) = ‘Nationwide Supply’
SELECT [BusinessEntityID] FROM [Sales].[Store] WHERE [Name] = @NAMES

Possible Answers:

1. You will have an INDEX SEEK because the predicate is on an indexed column. The VARCHAR data type is lower in the hierarchy than NVARCHAR.
2. You will have an INDEX SCAN, because the parameter data type is of another type than the predicate column. (NVARCHAR vs VARCHAR).

17. You create a stored procedure which inserts data into a table: Addressline. In case of a null value for a parameter you want to show it as an error message together with the message that your application will be stopped. will this result be obtained?

CREATE PROCEDURE InsertAddress1 @AddressLine1 nvarchar(50) AS
BEGIN TRY
IF @AddressLine1 IS NULL THROW 52000, ‘AddressLine is NULL‘,1
Insert into dbo.Addressline(AddressLine) VALUES (@AddressLine1)
END TRY
BEGIN CATCH
DECLARE @ERRORMESSAGE NVARCHAR(255);
SELECT @ERRORMESSAGE = ERROR_MESSAGE();
THROW;
RAISERROR(‘WILL STOP APPLICATION’,16,1);
END CATCH

EXEC InsertAddress1 NULL

Possible Answers:

1. Yes, both messages will be shown.
2. No, only the first message will be shown. THROW command will stop further execution of the batch and the error message will not be visible.

19. You run a statement which generates an error (NULL inserted in a NOT NULL column). Will the following statements generate an error message, abort the batch and roll back the transaction?

BEGIN TRY
BEGIN TRAN
INSERT INTO [HumanResources].[Department]([Name],[GroupName])
VALUES (‘DEP1’,NULL)
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() = 1
BEGIN
;THROW 51000,’ERROR DURING INSERT’,1
ROLLBACK TRAN
END
END CATCH

Possible Answers:

1. Yes. Error message will be shown, batch will be aborted and transaction will be rolled back.
2. No. Error message will be shown, batch will be aborted, but transaction will not be rolled back and will remain open. Throw statement stops all executions so ROLLBACK statement is never reached.

21. You run a statement which generates an error (NULL inserted in a NOT NULL column). Will the following statements generate a desired error message, abort the batch and roll back the transaction?

SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO [HumanResources].[Department]([Name],[GroupName])
VALUES (‘DEP1’,NULL)
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() = 1 AND @@TRANCOUNT > 0
BEGIN
RAISERROR(‘WRONG DATA INSERTED TRAN WILL BE ROLLED BACK’,16,1)
ROLLBACK TRAN
END
END CATCH

Possible Answers:

1. Yes. Open Transaction will be rolled back in the CATCH block. Error message will be shown.
2. No. XACT_STATE will be -1 because XACT_ABORT is ON. Transaction is rolled back due to XACT_ABORT, but no message will be shown.

23. You create a Stored Procedure which inserts values into the Culture table. The first insert is with an INSERT VALUES statement, and the second is an INSERT SELECT from a non-existing global temp table. What will be the status of your transaction after you run this procedure?

CREATE PROCEDURE [Production].[InsertCulture] @cultid varchar(20), @Name varchar(50)
AS BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO [Production].[Culture](CultureID,Name) VALUES(@cultid, @Name);
INSERT INTO [Production].[Culture](CultureID,Name) SELECT CultureID,Name FROM ##NONEXISTINGTABLE;—GENERATES AN ERROR
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN
;THROW;
END CATCH
END

SET XACT_ABORT ON;
EXEC [Production].[InsertCulture] ‘Sp’, ‘Spain’

Possible Answers:

1. The procedure is aborted and the transaction is rolled back thanks to the XACT_ABORT option. No rows inserted.
2. The procedure is aborted, but the transaction remains open in a non-committable state due to the PARSE error. No rows have been inserted.
3. The procedure is aborted but the first row is inserted. The transaction is closed.

25. You have 2 tables and you want to find the latest OrderDate for each customer. Which join type to use? You want also display the Customers which have not placed any orders.

SELECT CUS.[AccountNumber] ,
MAX(SOH.[OrderDate]) AS MaxOrderDate
FROM [Sales].[Customer] CUS
<————————-WHICH JOIN TYPE TO CHOOSE
GROUP BY CUS.[AccountNumber]

Possible Answers:

1. LEFT JOIN [Sales].[SalesOrderHeader] SOH ON CUS.[CustomerID] = SOH.[CustomerID]

2. CROSS JOIN [Sales].[SalesOrderHeader] SOH ON CUS.[CustomerID] = SOH.[CustomerID]

3. INNER JOIN [Sales].[SalesOrderHeader] SOH ON CUS.[CustomerID] = SOH.[CustomerID]

27. In one transaction you have an inserted statement followed by a DIVISION BY 0 error. In your CATCH block you have a COMMIT TRAN statement. Will your insert statement be executed and committed despite the error in your transaction ?

SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRAN
INSERT INTO [Production].[Culture](CultureID,Name,modifieddate)
VALUES(2166,’SPANISH2′,’2018-01-30′)
SELECT 1/0 —generates an error
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() = 1 AND @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
SELECT ‘TRAN IS COMMITTED’;
END
ELSE
BEGIN
ROLLBACK TRAN
RAISERROR(‘TRAN IS ROLLED BACK’,16,1);
END
END CATCH

Possible Answers:

1. No your batch will be aborted and your entire transaction rolled back.
2. Yes. Your insert statement will be executed and committed as it is entered as committable in the CATCH block.

29. You have 2 tables: EmployeeData and SalesPersons with identical definitions. You want to find which employees are also defined as SalesPersons. The Code column can be NULL, but you can also check with FullName to get correct results. Which query to use?

CREATE TABLE EmployeeData Create Table SalesPersons
(Code NVARCHAR(10) NULL, (Code NVARCHAR(10) NULL,
FullName NVARCHAR(50) NOT NULL) FullName NVARCHAR(50) NOT NULL)

135 DAN ATKINS NULL PATRICK STEEL
2784 JAMES STRONG 245 RICO SANCHEZ
NULL PATRICK STEEL
245 RICO SANCHEZ

Possible Answers:

1. SELECT * FROM SalesPersons S INNER JOIN EmployeeData E
ON E.Code = S.Code AND S.FullName = E.FullName
2. SELECT * FROM SalesPersons
INTERSECT
SELECT * FROM EmployeeData
3. SELECT * FROM EmployeeData E LEFT JOIN SalesPersons S
ON E.Code = S.Code AND S.FullName = E.FullName
WHERE S.Code IS NOT NULL AND S.FullName IS NOT NULL

31. In your stored procedures you want to replace all your RAISERROR statements with THROW statement in order to have a more uniform description of your errors. Will this cause a problem for the following example what concerns the transactions?

CREATE TABLE [HumanResources].[Employee2](
[BusinessEntityID] [int] NOT NULL PRIMARY KEY,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL)

BEGIN TRY
BEGIN TRAN
INSERT INTO [HumanResources].[Employee2]([BusinessEntityID],[NationalIDNumber],[LoginID])
VALUES (1258,NULL,’JACK’); —-> STATEMENT GENERATES AN ERROR
COMMIT TRAN
END TRY
BEGIN CATCH
THROW ;—RAISERROR(‘ERROR DURING INSERTION OF LOGINS’,16,1) —IS REPLACED BY THROW;
ROLLBACK TRAN;
END CATCH

Possible Answers:

1. No. the current transaction will be rolled back in both cases. Only the description of the error will be different (more exact).
2. Yes. After the THROW statement, the execution of the batch will stop end the ROLLBACK statement will be never reached. The transaction will stay open.

33. You have a table Person which has potential NULL values for first, last and middle name. You want to write a query which will display to value ‘UNKNOWN’ in case that all the 3 columns have null values.

CREATE TABLE [Person].[Person2](
[BusinessEntityID] [int] NOT NULL,
[FirstName] [dbo].[Name] NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NULL
)
Which statement to use?

Possible Answers:

1. SELECT [BusinessEntityID], COALESCE([FirstName], [MiddleName], [LastName], ‘UNKNOWN’) FROM [Person].[Person2]

2 SELECT [BusinessEntityID], ISNULL([FirstName] + [MiddleName] + [LastName], ‘UNKNOWN’) FROM [Person].[Person2]

3. Both statements will give the desired result

4. Both statements will not return the desired result

35. Your company sells products over the internet or via resellers (tables: FactResellerSales, FactInternetSales). You want to find the products which are sold by by only one channel. Which query to use?

Possible Answers:

1. SELECT DISTINCT ISNULL(ISL.[ProductKey], RSL.[ProductKey]) AS SINGLEPRODUCT
FROM [dbo].[FactInternetSales] ISL
FULL JOIN [dbo].[FactResellerSales] RSL
ON EXISTS( SELECT ISL.[ProductKey] INTERSECT
SELECT RSL.[ProductKey])
WHERE (ISL.[ProductKey] IS NULL OR RSL.[ProductKey] IS NULL)
2. SELECT DISTINCT ISNULL(ISL.[ProductKey], RSL.[ProductKey]) AS SINGLEPRODUCT
FROM [dbo].[FactInternetSales] ISL
FULL JOIN [dbo].[FactResellerSales] RSL
ON ISL.[ProductKey] = RSL.[ProductKey] WHERE NOT (ISL.[ProductKey] IS NOT NULL OR RSL.[ProductKey] IS NOT NULL)
3. SELECT SINGLEPRODUCT.[ProductKey] FROM(
SELECT ISL.[ProductKey] FROM [dbo].[FactInternetSales] ISL
EXCEPT
SELECT RSL.[ProductKey] FROM [dbo].[FactResellerSales] RSL) SINGLEPRODUCT

36. Your company sells products over the internet or via resellers. You want to find the products which are sold by both channels. Which query to use?

Possible Answers:

1. SELECT ISL.[ProductKey] FROM [dbo].[FactInternetSales] ISL LEFT JOIN [dbo].[FactResellerSales] RSL
ON ISL.[ProductKey] = RSL.[ProductKey]

2. SELECT ISL.[ProductKey],RSL.[ProductKey] FROM [dbo].[FactInternetSales] ISL CROSS JOIN [dbo].[FactResellerSales] RSL

3. SELECT [ProductKey] FROM [dbo].[FactInternetSales] INTERSECT
SELECT [ProductKey] FROM [dbo].[FactResellerSales]

38. You need to return from a table dbo.Sales the totals sales per country/region and country together with the total sales for everyone.

Example Data

Results of the query

SELECT[Country], [Region], SUM(SALES)
FROM [dbo].[Sales] AS TOTALSALES
?????????? —–> WHICH STATEMENT TO INSERT?

Possible Answers:

1. GROUP BY GROUPING SETS(([Country], [Region]),(country),())
2. GROUP BY CUBE ([Country], [Region])
3. GROUP BY ROLLUP([Country], [Region])

40. Due to a bug in your application you have records in the [SalesOrderHeaders] table without records in the [SalesOrderDetails] table. Which clause to place in order to find the SalesOrderHeaders with missing Details?

SELECT soh.*
FROM [Sales].[SalesOrderHeader] soh
??????
JOIN [Sales].[SalesOrderDetail] sod
ON soh.[SalesOrderID] = sod.[SalesOrderID] WHERE sod.[SalesOrderID] IS NULL

Possible Answers:

1. INNER JOIN
2. CROSS JOIN
3. CROSS APPLY
4. LEFT JOIN

42. You develop websites for companies and you have applications to track the web visits on your clients’ sites. You want to have a list of monthly visits on those sites and you want to use the EOMONTH() function. How many rows will the following query return?

CREATE TABLE #WebVisits
(WebID int,
VisitDate datetime)
INSERT INTO #WebVisits VALUES (35, ‘2018-10-01 01:48:40.200’),
(135, ‘2018-10-07 11:50:40.783’),
(140, ‘2018-10-31 23:15:40.783’),
(165, ‘2018-10-31 00:00:40.200’)
SELECT * FROM #WebVisits WHERE VisitDate BETWEEN ‘2018-10-01’ AND EOMONTH(‘2018-10-01’)

Possible Answers:

1. 4 Rows. All records from the first of October until the 31 of the month will be returned
2. 2 Rows. The EOMONTH function takes as a last day midnight 30 of October, not midnight of 31

44. You have to combine 3 variables to retrieve the fullname of an employee. You can use CONCAT or the ‘+’ clause. What difference will they give?

DECLARE @FIRSTNAME VARCHAR(50) = ‘JOHN’
DECLARE @MIDDLENAME VARCHAR(50) = NULL
DECLARE @LASTNAME VARCHAR(50) = ‘ANDERSON’

SELECT @FIRSTNAME + ‘ ‘ + @MIDDLENAME + ‘ ‘ + @LASTNAME
SELECT CONCAT(@FIRSTNAME , ‘ ‘ , @MIDDLENAME , ‘ ‘ , @LASTNAME)

Possible Answers:

1. No difference. Both statements will return ‘JOHN ANDERSON’
2. The first statement will return NULL, the second ‘JOHN ANDERSON’
3. Both statements will return NULL because you concatenate with a NULL value

45. You want in a statement to display the firstname, lastname and salary of employees. What is the difference if you use the ‘+’ clause or concat function?

DECLARE @FIRSTNAME VARCHAR(50) = ‘JOHN’, @LASTNAME VARCHAR(50) = ‘ANDERSON’
DECLARE @SALARY DECIMAL(18,2) = 1524.37

SELECT @FIRSTNAME + ‘ ‘ + ‘ ‘ + @LASTNAME + ‘ ‘ + @SALARY
SELECT CONCAT(@FIRSTNAME , ‘ ‘ , ‘ ‘ , @LASTNAME, ‘ HAS SALARY ‘ , @SALARY )

Possible Answers:

1. They will all return the same value: ‘ANDERSON JOHN 1524.37’
2. The statement @FIRSTNAME + ‘ ‘ + ‘ ‘ + @LASTNAME + ‘ ‘ + @SALARY will return an ERROR because you add a number to a string. The other will return ‘ANDERSON JOHN 1524.37’

47. You have a table [Person].[Address] with data of your employees. Whenever you have changes you want to track those changes in a history table. What is the correct statement to log the changes for one update?

CREATE TABLE [Person].[AddressHistory] (AddressID int,
AddressLineOLD nvarchar(60),
AddressLineNEW nvarchar(60),
ModifiedDate datetime DEFAULT GETDATE())

Possible Answers:

1. UPDATE [Person].[Address] SET AddressLine1 = ‘2145 Washington Drive’
WHERE AddressID = 1
INSERT INTO [Person].[AddressHistory](AddressID,AddressLineOLD,AddressLineNEW)
SELECT inserted.AddressID, deleted.AddressLine1, inserted.AddressLine1
FROM [Person].[Address]

2. UPDATE [Person].[Address] SET AddressLine1 = ‘2145 Washington Drive’
OUTPUT inserted.AddressID, deleted.AddressLine1, inserted.AddressLine1
INTO [Person].[AddressHistory](AddressID,AddressLineOLD,AddressLineNEW)
WHERE AddressID = 1

3. UPDATE [Person].[Address] SET AddressLine1 = ‘2145 Washington Drive’
OUTPUT inserted.AddressID, inserted.AddressLine1,deleted.AddressLine1
INTO [Person].[AddressHistory](AddressID,AddressLineOLD,AddressLineNEW)
WHERE AddressID = 1

49. You want to execute a SELECT statement in a heavily used transactional table to check the values of some old records. However you do not want to cause extra overhead for users which are inserting and updating data. How to achieve this? (generating minimal SHARED locks)

Possible Answers:

1. Execute the query with the NOLOCK hint
2. Execute the query with the READPAST hint
3. Start your statement with the SET IZOLATION LEVEL READ COMMITTED

51. Your company is active in 2 countries: France and the US. The hiredates in your employees table are of type varchar and you want to filter out the dates which are in French format.
Which statement to use to identify French dates?

Possible Answers:

1. SELECT TRY_PARSE(‘janvier 21 2018’ AS DATETIME USING ‘fr-FR’)
2. SELECT TRY_CONVERT(DATETIME,’janvier 21 2018′)
3. SELECT TRY_CAST(‘janvier 21 2018’ AS DATETIME)

53. You want to calculate the running sales (sum current sales and previous’ periods sales) per employee. Which clause to use in your query?
ModifiedDate EMPID CURRENTTOAL RUNNINGTOTALS

2011-12-23 250 1984,6192 1984,6192
2011-12-24 250 7721,4638 9706,083
2012-01-25 250 31160,2541 40866,3371
2012-03-03 250 284,6209 41150,958
2012-03-17 250 157,3647 41308,3227
2012-03-18 250 38281,8686 79590,1913

SELECT [ModifiedDate],
[EmployeeID] AS EMPID,
[TotalDue] AS CURRENTTOTAL,
????????? —->CODE TO PLACE
FROM [Purchasing].[PurchaseOrderHeader]

Possible Answers:

1. SUM(TOTALDUE) OVER( PARTITION BY [EmployeeID] ORDER BY [ModifiedDate] ROWS UNBOUNDED PRECEDING ) AS RUNNINGTOTALS
2. SUM(TOTALDUE) OVER( PARTITION BY [EmployeeID] ORDER BY [ModifiedDate] DESC ROWS UNBOUNDED PRECEDING ) AS RUNNINGTOTALS
3. SUM(TOTALDUE) OVER( PARTITION BY [EmployeeID] ORDER BY [ModifiedDate] VALUES UNBOUNDED PRECEDING ) AS RUNNINGTOTALS

55. What will be the result of the following query?

CREATE TABLE COLORS (COLOR VARCHAR(20))
INSERT INTO COLORS VALUES(‘RED’), (‘GREEN’), (‘BLUE’), (NULL)

SELECT ‘TRUE’
WHERE ‘BLACK’ NOT IN (SELECT COLOR FROM COLORS)

Possible Answers:

1. 1 Row: True.
2. 0 rows due to the NULL value in one row.
3. 1 Row:NULL

57. You have a transaction which generates an error.

BEGIN TRAN
INSERT INTO dbo.Countries2(ID,Code, Name) VALUES(235,’BB’,’COUNTRY1′)
SELECT CAST(‘ABC’ AS INT) — CONVERSION ERROR
INSERT INTO dbo.Countries2(ID,Code, Name) VALUES(100,’AA’,’COUNTRY3′)
COMMIT TRAN
Which rows will be returned when you run the following statement?
SELECT * FROM DBO.Countries2

Possible Answers:

1. No rows as the error generated by the CAST statement is batch terminating and will roll back the entire transaction.
2. Only COUNTRY1. The insert has stopped after the error as it is a batch terminating error.
3. Only COUNTRY1 and COUNTRY3. The insert continued after the error as the error is a statement terminating error.

59. You want to create a view for your employee info which will use a scalar UDF to return the employee’s resume. This view is very important and will be schemabound in order to index it. Will this code work without errors?

CREATE FUNCTION [HumanResources].[fn_GetResume](@BusinessEntityID int)
RETURNS XML
AS
BEGIN
DECLARE @resume XML
SELECT @resume = [Resume] FROM [HumanResources].[JobCandidate] WHERE [BusinessEntityID] = @BusinessEntityID
RETURN @resume
END
CREATE VIEW [HumanResources].[vEmployeeInfo] AS SELECT e.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[LastName] ,[HumanResources].[fn_GetResume](e.[BusinessEntityID]) AS [Resumes] FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID];

Possible Answers:

1. Yes
2. No. In order to use a function in an schemabound view, the function must also be schema bound

61. You create a non unique nonclustered index on the [CarrierTrackingNumber] column of the [Sales].[SalesOrderDetail] column which has a nvarchar(25) data type and is nullable.

CREATE NONCLUSTERED INDEX IX1 ON [Sales].[SalesOrderDetail]([CarrierTrackingNumber])
The column [SalesOrderID] is the CLUSTERED PRIMARY KEY of the table. What is the difference of the 2 queries concerning IO and execution plan?

SET STATISTICS IO ON GO
SELECT [SalesOrderID] FROM [Sales].[SalesOrderDetail] WHERE ISNULL([CarrierTrackingNumber],’3D8D-4908-A6′) = ‘3D8D-4908-A6’;

SELECT [SalesOrderID] FROM
[Sales].[SalesOrderDetail] WHERE [CarrierTrackingNumber] = ‘3D8D-4908-A6’ OR [CarrierTrackingNumber] IS NULL;

Possible Answers:

1. Both the queries will have the same execution plan and the same IO.
2. The second query will have an index seek, the first an index scan because of the function on the predicate. The second query will have less IO
3. The first query will be faster because it has less values after the predicate in order to compare

63. You want to join 2 tables and return the result in the following XML format. Which clause to use in your query?

<Cust CustomerID=’29825′ PersonID=’1045′ AccountNumber=’AW00029825′>
<OrderHeader CustomerID=’29825′ SalesOrderID=’43659′ SubTotal=’20565.6206′ />
</Cust>
<Cust CustomerID=’29672′ PersonID=’721′ AccountNumber=’AW00029672>
<OrderHeader CustomerID=’29672′ SalesOrderID=’43660′ SubTotal=’1294.2529′ />
</Cust>

SELECT Cust.CustomerID,
Cust.PersonID,
Cust.AccountNumber,
OrderHeader.SalesOrderID,
OrderHeader.SubTotal
FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID;

Possible Answers:

1. FOR XML AUTO
2. FOR XML RAW
3. FOR XML RAW, ELEMENTS

65. In a table you insert 4 rows. The third row will generate an error. You will insert a NULL value in a non NULLABLE column.

SET XACT_ABORT ON
INSERT INTO [Person].[ContactType]([Name]) VALUES(‘JOHN’);
INSERT INTO [Person].[ContactType]([Name]) VALUES(‘JAMES’);
INSERT INTO [Person].[ContactType]([Name]) VALUES(NULL)———— NULL VALUE GENERATES AN ERROR
INSERT INTO [Person].[ContactType]([Name]) VALUES(‘REGINALD’);

What rows will be returned after the statement:
SELECT * FROM [Person].[ContactType]

Possible Answers:

1. No rows. The XACT_ABORT statement will rollback the entire batch
2. Only JOHN and JAMES. The statement stops after the error
3. JOHN, JAMES and REGINALD. The statements will continue after the error.

67. In a table you insert 4 rows. The third insert statement will generate an error because it will insert a NULL value in a NOT NULL column.

INSERT INTO [Person].[ContactType]([Name]) VALUES(‘JOHN’);
INSERT INTO [Person].[ContactType]([Name]) VALUES(‘JAMES’);
INSERT INTO [Person].[ContactType]([Name]) VALUES(NULL);———— NULL VALUE GENERATES AN ERROR
INSERT INTO [Person].[ContactType]([Name]) VALUES(‘REGINALD’);

What rows will be returned after the statement: SELECT * FROM [Person].[ContactType]

Possible Answers:

1. Only JOHN and JAMES. The statement stops after the error
2. JOHN, JAMES and REGINALD. The statements will continue after the error
3. No records will be inserted at all

69. You have confidential info info in a table and you create views so that developers can only see the needed columns.

CREATE VIEW vw_Persons
AS SELECT [BusinessEntityID], [FirstName], [LastName] FROM [Person].[Person]

In this view you want to perform the following update:

UPDATE vw_Persons
SET [FirstName] = ‘JAMES’
WHERE [BusinessEntityID] = 285

Is this possible?

Possible Answers:

1. No. You can only select columns and rows from a view, never update
2. Yes, in this cases it is possible (1 column from 1 table is updated)
3. Yes. You can always update a view, independent of the statements from which it is created

71. When you run a query and ask for a non-existing column from a non-existing table. Which error message will be shown?

BEGIN TRY
SELECT NONEXISTING_COLUMN FROM dbo.Countries2 –> GENERATES AN ERROR.
END TRY
BEGIN CATCH
SELECT ‘CATCH BLOCK REACHED’
END CATCH

Possible Answers:

1. The message: Invalid object name ‘dbo.Countries2’. The CATCH block will not be reached as it is a COMPILE error
2. The error ‘CATCH BLOCK REACHED’. After the error in the TRY block, the CATCH block is called

72. You have 2 tables: TAB1 and TAB2. TAB1 has an AFTER Trigger. When you insert values in TAB1 the trigger inserts values in TAB2.

CREATE TABLE TAB1 (C1 INT IDENTITY(1,1), NAMES VARCHAR(50))
CREATE TABLE TAB2 (C1 INT IDENTITY(2,2), NAMES VARCHAR(50))
CREATE TRIGGER TR1 ON TAB1 AFTER INSERT AS
INSERT INTO TAB2(NAMES) VALUES(‘TEST’)

When you run the statement for the first time: INSERT INTO TAB1(NAMES) VALUES(‘TEST1’) SELECT @@IDENTITY, SCOPE_IDENTITY(), what will be the result for the functions SCOPE_IDENTITY and IDENTITY?

Possible Answers:

1. @IDENTITY = 2, SCOPE_IDENTITY() = 2
2. @IDENTITY = 1, SCOPE_IDENTITY() = 1
3. @IDENTITY = 2, SCOPE_IDENTITY() = 1

74. You run the following query and you want to have your result in the following format in order to exchange with a web application and minimize the length of the generated string. Which statement to put at the end?

CREATE TABLE [Countries2] (
[ID] [tinyint] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL
)
SELECT * FROM [dbo].[Countries2] ??????? <— WHICH STATEMENT TO USE?
—-> OUTPUT OF THE STATEMENT
[
{‘ID’:1,
‘Code’:’AC ‘,
‘Name’:’Ascension Island’}’
]

Possible Answers:

1. FOR JSON AUTO
2. FOR XML AUTO

76. You want to run a query and display the results in a hierarchical JSON format with the title COUNTRIES in the first line

CREATE TABLE [dbo].[Countries2](
[ID] [tinyint] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL)
RESULT
{‘COUNTRIES’:
[
{‘ID’:1,
‘Code’:’AC ‘,
‘Name’:’Ascension Island ‘},
{‘ID’:2,
‘Code’:’AD ‘,
‘Name’:’Andorra ‘}
]}
what statement to put after this select?
SELECT TOP (2) * FROM dbo.Countries2
?????? <——

Possible Answers:

1. FOR JSON AUTO, ROOT(‘COUNTRIES’)
2. FOR JSON PATH
3. FOR JSON AUTO

78. You have a 3 insert statements in one batch. One generates an error.

SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO dbo.Countries2(ID,Code, Name) VALUES(100,’AA’,’COUNTRY1′)
INSERT INTO dbo.Countries2(ID,Code, Name) VALUES(200,’BB’,’COUNTRY3′)
INSERT INTO dbo.Countries2(ID,Code, Name) VALUES(‘300′,’CC’,’COUNTRY2′)—>generates an error
COMMIT TRAN
Which rows will be returned when you run the following statement?
SELECT * FROM DBO.Countries2

Possible Answers:

1. No rows as the XACT_ABORT statement transformed the statement terminating errors to batch terminating errors
2. Only COUNTRY1. The insert has stopped after the error as it is a batch terminating error
3. Only COUNTRY1 and COUNTRY3. The insert continued after the error as the error is a statement terminating error

80. In your Human Resources Database you want your programmers to query easily some very important tables. However you do not want them to update the tables directly.
What is the EASIEST way to achieve this (least effort, best performance) ?

Possible Answers:

1. Create Stored Procedures which read the data and return results. For every new kind of query create a new procedure
2. Create views which cover the tables to be queried. Give end-users SELECT permissions on those views
3. Create INDEXED views which cover the tables to be queried. Give end users SELECT permissions on those views

82. In the table Person.Address you have your clients’ addresses together with their Geometric location (SpatialLocation). You have to visit a client in Redmond with clientID 206. You need to write a query that returns the 10 nearest clients near 206.

Will the following query give the desired result?
SELECT TOP 10 B.[AddressLine1],B.[City],
A.[SpatialLocation].ShortestLineTo(B.[SpatialLocation]).STLength() as [LineLenght] FROM [Person].[Address] A CROSS JOIN [Person].[Address] B
WHERE A.[AddressID] = 206 AND A.[AddressID] <> B.[AddressID] ORDER BY [LineLenght] ASC

Possible Answers:

1. Yes
2. No

84. You have 2 salesdatabases on 2 servers in 2 locations: Paris and London. The SalesDate is of a datetime data type. You want to show the date depending on the needs of the location (French or UK). Which statement to use?

Possible Answers:

1. SELECT Id,Product, TODATETIMEOFFSET(SalesDate, -2) FROM Sales
2. SELECT Id,Product, SWITCHOFFSET(SalesDate, -2) FROM Sales
3. SELECT Id,Product, CAST(SalesDate AS DATETIMEOFFSET) FROM Sales

86. You have a carrenting TEMPORAL table. You want to know which cars where rented for a specific date. Which rows will be returned when you run the following query?

SELECT * FROM dbo.CarSales FOR SYSTEM_TIME AS OF ‘2018-10-08 13:57:35.5485274’

Possible Answers:

1. 3 ROWS

2. 5 ROWS

88. You have the following piece of code in an application where a variable @AMOUNT receives a string value from a query. In case the value is an integer, you want to show the value and continue the program. In case of string values you want to show the string and stop execution:

DECLARE @AMOUNT varchar(20) = ‘EUR 25000’
SELECT ISNULL(TRY_CAST(@AMOUNT as int), @AMOUNT) as SALEASMOUNT

Will this code work?

Possible Answers:

1. Yes, if TRY_CAST is NULL (non integer) then with ISNULL the String value will be shown
2. No. You will have a Conversion ERROR

90. You run the following query:

SELECT [ProductID] ,[StandardCost] ,DENSE_RANK() OVER (ORDER BY [StandardCost] DESC) AS DENSE_RANK
FROM [Production].[ProductCostHistory]

What result is correct?

Possible Answers:

1.

2.

91. In a Stored Procedure you have a temp table with TotalDues per ShipDate. You want in a query to show those 2 columns, together with the TotalDue value of the last SalesOrderID.

Will the following query do the job?
SELECT [SalesOrderID], CAST(ShipDate AS DATE) AS SHIPDATE, TotalDue ,
LAST_VALUE([TotalDue]) OVER (ORDER BY [SalesOrderID] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LAST_TOTALDUE
FROM [Sales].[SalesOrderHeader] ORDER BY [SalesOrderID] ASC

Possible Answers:

1. No
2. Yes

92. You have 2 tables Customer and SalesOrderHeader with a common Column CustomerID. You want to find which customers have more than 10 orders. Will this query give the right result?

SELECT Cust.[CustomerID], COUNT(*)
FROM [Sales].[Customer] Cust
INNER
JOIN [Sales].[SalesOrderHeader] Soh
ON EXISTS (SELECT Cust.[CustomerID] INTERSECT
SELECT Soh.[CustomerID] )
GROUP BY Cust.[CustomerID] HAVING COUNT(*) > 10

Possible Answers:

1. Yes
2. No

94. You have a table [Sales].[SalesOrderHeader]. You want to retrieve the first order for every customer. Which statement to use?

Possible Answers:

1. ;WITH CTE_ORDERS AS(SELECT [CustomerID], OrderDate,ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY [OrderDate] ASC) AS FIRSTORDER
FROM [Sales].[SalesOrderHeader])
SELECT * FROM CTE_ORDERS WHERE FIRSTORDER = 1
2. SELECT [CustomerID], OrderDate,ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY [OrderDate] DESC) AS FIRSTORDER
FROM [Sales].[SalesOrderHeader] WHERE FIRSTORDER = 1

97. You have the following TEMPORAL TABLES’ history rows (Carsales)

You want to retrieve the rows which where active at a certain point in time. Which rows will be returned?
SELECT * FROM dbo.CarSales
FOR SYSTEM_TIME FROM ‘2018-10-08 13:56:59.1813706’ TO ‘2018-10-08 13:57:35.5485274’

Possible Answers:

1. 3 ROWS

2. 5 ROWS

99. You have the following TEMPORAL TABLES’ history rows (Carsales)

You want to retrieve the rows which where active at a certain point in time. Which rows will be returned?
SELECT * FROM dbo.CarSales
FOR SYSTEM_TIME BETWEEN ‘2018-10-08 13:57:35.5485274’ AND ‘2018-10-08 13:57:35.5485274’

Possible Answers:

1. 3 ROWS

2. 5 ROWS

Dimitri Stathatos

Dimitri is a fanatic MVP DBA who sleeps and wakes up with SQL commands in his mind. Since 2017, also makes questionnaires about Microsoft’s certification exams and in co-operation with Nikolaos Katsogridakis, published many preparation apps on Google_Play. He would like to UncoverThe all his experience within these pages.