Querying Data with T-SQL - Pluralsight

Is the SQL between statement inclusive or exclusive?

Inclusive

What are the main subclasses of the OVER clause?

PARTITION BY, ORDER BY, And ROW or RANGE

How would you correctly use the function UsersByName? CREATE FUNCTION usersByName (@name varchar(15)) Returns table AS RETURN ( SELECT userId, UserName FROM Users WHERE UserName LIKE '%' + @name + '%')

SELECT * FROM usersByName('ja')

What must follow the statement before the THROW statement

;

How do you drop a foreign key?

ALTER TABLE currTable DROP CONSTRAINT FK_SomeAlias;

You have an outermost transaction and one transaction nested inside it. Which transaction(s) is/are rolled back when you use a ROLLBACK TRANSACTION without any arguments?

Both the transactions are rolled back.

What is the difference between CAST and CONVERT?

CONVERT allows you to specify a style for the conversion; CAST does not.

Given the following table, how would you create a clustered index on its column named TestCol1? CREATE TABLE dbo.TestTable (TestCol1 int NOT NULL, TestCol2 nchar(10) NULL, TestCol3 nvarchar(50) NULL);

CREATE CLUSTERED INDEX IX_TestTable_TestCol1 ON dbo.TestTable (TestCol1);

Which code will specify an output parameter in a stored procedure?

CREATE PROCEDURE procName @parameter INT OUTPUT AS SET @parameter=1;

Which exemplified a stored procedure that returns a set of data?

CREATE PROCEDURE userSales @userId INT AS SET NOCOUNT ON SELECT salesDate, SUM(SalesAmount) FROM Sales WHERE userId = @userId GROUP BY salesDate RETURN

You must enable the application database user to TRUNCATE a table used for logging upon request. What approach should you use?

Create a stored procedure with the TRUNCATE TABLE statement and then grant execute permission on the stored procedure to the application user.

What statement correctly calculated age?

DECARE @bday DATETIME; SET @bday = '1990-01-01'; SELECT DATEDIFF(hour, @bday, GETDATE())

How would you execute the following stored procedure to make use of the output parameter lastSalesDate? CREATE PROCEDURE ProductLastSalesDate @productID INT, @lastSalesDate DATETIME OUTPUT AS SET NOCOUNT ON; SELECT @lastSalesDate = MAX(SalesDate) FROM SalesWHERE ID = @productID;

DECLARE @lastDate DATETIME; EXECUTE ProductLastSalesDate @productID = 123, @lastSalesDate = @lastDate OUTPUT; PRINT CONVERT (VARCHAR, @lastDate, 120);

You have a table with 55,000 records in it. How will you delete the records where shipment arrived in the last quarter of any year?

DELETE FROM myTable WHERE DATEPART(QUARTER, arrivalDate) = 4

In sql server 2016 and higher what command would you use to drop a database that will not cause an error if the database has not been created?

DROP DATABASE IF EXISTS database_name

Why is there a separate language to manage data structures?

Data structures are metadata and managing them is a different task than handling them

What is the first argument using the THROW statement?

Error_Number

What will be the output of the following operation? SELECT REPLACE('one MILLION ONE THOUSAND ONE HUNDRED ONE', 'one', 'FIVE');

FIVE MILLION FIVE THOUSAND FIVE HUNDRED FIVE

Which piece of code would you insert after RefId int to define a foreign key constraint to the Ref table given the following table definitions? CREATE TABLE Ref(RefId int PRIMARY KEY, RefData varchar(200)) CREATE TABLE Referencing(ReferID int, RefId int

FOREIGN KEY REFERENCES Ref(RefID)

The following result set is returned OrderId. TotalQuantity TotalPrice ————————————————————— 1. 30. 25 2. 57. 1817.25 3. 5. 0.5 What could you add to the query to eliminate orderId 3 as well as any orders greater than 1000 in price?

HAVING SUM(Quantity * unitPrice) BETWEEN 10 AND 1000

You use the following query to return a list of all orders with an order total greater than 1,000 and sort them in defending order total SELECT o.OrderNumber as [order number], oil.OrderTotal AS [order total] FROM dbo.Order o JOIN (SELECT oil.OrderId, SUM(p.cost * (1+ (p.MarkupPercentage / 100))) AS OrderTotal FROM dbo.OrderLineItem oli JOIN dbo.Product p ON p.ID = oli.ProductId GROUP BY oli.OrderId HAVING [order total] > 1000 oli. ON oli.[OrderId] ORDER BY OrderTotal DESC The query returns an exception what must you change for the query to return the correct results

HAVING SUM(p.Cost * (1 + (p.MarkupPercentage /100))) > 1000) oli ON oli.[orderId] = [orderId] ORDER BY OrderTotal DESC

Given the following dataset, salesUsers, and procedure, how would you use the returned dataset in a query to insert into another table Users? DataSet: UserID userCode ------ -------- 1 AVFHJ 2 DUHFK 3 SIKFH 4 AJSNJ 5 AIIOJ 6 RMSNJ 7 JOPJS Procedure: CREATE PROCEDURE UserCodesWith @userCodeInitial VARCHAR(1) AS SELECT userID, userCode FROM salesUsers WHERE userCode LIKE @userCodeInitial + '%'

INSERT INTO Users EXEC UserCodesWith 'A'

What are the four JSON built-in functions in Transact-SQL?

ISJSON, JSON_VALUE, JSON_QUERY, and JSON_MODIFY

Which is true of OPENQUERY?

It allows you to connect to a linked server.

Which function can you use to extract the number of sales from a JSON string?

JSON_VALUE()

Given the following tables and the MERGE statement: CREATE TABLE #targetTable(id1 int, id2 int, name varchar(50)) CREATE TABLE #sourceTable(id1 int, id2 int, name varchar(50)) INSERT INTO #sourceTable values(2, 1,'User1'); INSERT INTO #sourceTable values(1, 2,'User3'); INSERT INTO #sourceTable values(1, 1,'Usr3'); INSERT INTO #sourceTable values(3, 2,'User2'); INSERT INTO #targetTable values(1, 1,'User33'); MERGE #targetTable AS [target]USING #sourceTable AS [source] ON [target].id1 = [source].id1 WHEN MATCHED THEN UPDATE SET [target].name = [source].name WHEN NOT MATCHED THEN INSERT (id1, id2, Name) VALUES (source.id1, source.id2, source.Name); select * from #targetTable as T drop table #targetTable drop table #sourceTable You receive an error message that states, "The MERGE statement attempted to UPDATE or DELETE the same row more than once." How would you adjust the MERGE statement to resolve the error?

MERGE #targetTable AS [target] USING #sourceTable AS [source]ON [target].id1 = [source].id1 and [target].id2 = [source].id2 WHEN MATCHED THEN UPDATE SET [target].name = [source].name WHEN NOT MATCHED THENINSERT (id1, id2, Name) VALUES (source.id1, source.id2, source.Name);

Consider the following two tables: Table 1: BAZ itemID arrival price sales ---- ---- ---- ---- I45 2021-08-08 40 500 I46 2021-05-08 60 500 I47 2021-03-08 35 500 I48 2021-09-08 70 500 I49 2021-12-08 55 500 Table 2: QUE itemID price tagValue ---- ---- ---- P46 40 900 I46 60 1000 R78 35 800 I47 70 2000 I48 55 3000 How will you update the BAZ table records using the QUE table records based on the following conditions? When itemID and price are equal -> update sales based on tagValue When itemID is not equal -> add a new record to the BAZ table keeping the value of arrival as null

MERGE BAZ AS TARGET USING QUE AS SOURCE ON (TARGET.itemID = SOURCE.itemID) WHEN MATCHED AND TARGET.price = SOURCE.price THEN UPDATE SET TARGET.sales = SOURCE.tagValue WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (SOURCE.itemID, null, SOURCE.price, SOURCE.tagValue);

What result does the following code produce?DECLARE @testNumber int; SET @testNumber = 5; IF @testNumber > 0 BEGIN RETURN; PRINT 'the number is greater than than zero' END ELSE BEGIN IF @testNumber < 10 PRINT 'the number is less than 10'; ELSE PRINT 'the number is greater than 10'; END ;

None there is no output

In the following query, what command must you include in the placeholders to force SQL Server to optimize its query on each run? SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderIdAND h.OrderDate >= @StartOrderDate.........

OPTION(RECOMPILE)

You want to retrieve each users most recent order from the following dataset. Which COALESCE function will provide the desired result?

SELECT COALESCE(ThirdOrder, SecondOrder, FirstOrder) FROM orders

Given the following dataset, which statement provides the time passed in minutes for the process from the previous step to obtain the given output? caseID processOrder processTime ------ ------------ ------------------- 1 1 2018-01-01 10:00:00 1 2 2018-01-01 12:30:00 1 3 2018-01-01 13:00:00 2 1 2018-01-01 11:00:00 3 1 2018-01-02 10:30:00 3 2 2018-01-02 11:30:00 Output: caseID processOrder processTime timePassed ------ ------------ ------------------- ---------- 1 1 2018-01-01 10:00:00 1 2 2018-01-01 12:30:00 150 1 3 2018-01-01 13:00:00 30 2 1 2018-01-01 11:00:00 3 1 2018-01-02 10:30:00 3 2 2018-01-02 11:30:00 60

SELECT caseID, processOrder, processTime, DATEDIFF(minute, LAG(processTime) OVER (PARTITION BY caseID ORDER BY processOrder), processTime) AS timePassed FROM processTable

What statement would you use to specify whether SQL server automatically rolls back the current transaction when a T-SQL statement raises a run time error

SET XACT_ABORT ON

You wrote a CASE statement with following conditions: Value < 0; display negativeValue > 0; display positiveValue = 0; display zero What would you add in the following code to achieve the desired output? SELECT CASE ______(profit) WHEN -1 THEN 'negative' WHEN 1 THEN 'positive' WHEN 0 THEN 'zero' END FROM Sales

SIGN

Given the tabs definition CREATE TABLE Sales ( OrderId int, OrderItemId int, Quantity int, Price numeric(6,2)); What would you do to complete the following query to save the results into a global temporary table?

Select * INTO ##GT_$100 FROM SALES WHERE Quantity*Price > 100.00

What is a typical use case for common table expressions

Separating sub queries from the main query for increased readability and repetition elimination

What effect does the TRUNCATE statement have on a table's statistics?

Statistics on a table will not update automatically after the TRUNCATE operation unless AUTO UPDATE STATISTICS is ON.

Given the following table definition CREATE TABLE Employees ( EmpId int, MgrId int, EmpName varchar(50), EmpTitle varchar(50)); The given CTE fails to compile. How would you change the query so it will compile? WITH emp AS ( SELECT EmpId, EmpName, EmpTitle FROM Employees WHERE MgrId is NULL UNION ALL SELECT e. MgrId, E.EmpId, e.EmpName, e.EmpTitle FROM Employees e JOIN emp ON e.MgrId = emp.EmpId ) SELECT MgrId, EmpId, EmpName, EmpTitle From emp;

The first select in the CTE called emp must have the column MgrId added to the column list

What conditions must two result sets meet for them to be combined into one using the UNION operator so only similar features are united

The number and the order of columns must be the same and the data types must be compatible

Which statement is a valid difference between clustered and non clustered Indexes

There can be only one clustered index per table but more than one non clustered index per table

You can store data in different types. Why is it important to choose the correct type?

To minimize the storage used, prepare for future growth, and defined data integrity.

Which command can access values from a temporary table and update another table when both the tables have the same value in their ID columns?

UPDATE YourTable SET Col1 = OtherTable.Col1, Col2 = OtherTable.Col2 FROM (SELECT ID, Col1, Col2 FROM other_table) AS OtherTableWHERE OtherTable.ID = YourTable.ID

What option would you add to a CREATE VIEW statement to guard against changes in the underlying tables?

WITH SCHEMABINDING;

DECLARE @json nvarchar(4000 =N'{name: John Doe, rank: GI, Serial: 0034583}'; SELECT * FROM OPENJSON(@JSON) How would you complete the select statement so that the result is three columns? Name nvarchar(100) Rank varchar(50) Serial bigint

WITH( Name nvarchar(100), Rank varchar(50), Serial bigint)

What is the difference between unique and non unique indexes

With a unique index INSERT operations are rolled back for duplicate values whereas a non unique allows the INSERT operations with no error