SQL - Subquery

What is Subquery

A subquery is a SELECT statement within another SQL statement. The SQL statement can be SELECT, WHERE clause, FROM clause, JOIN, INSERT, UPDATE, DELETE, SET, DO, or another subquery. The query that contains the subquery is normally called outer query and the subquery itself is called inner query.

Using Subquery to Return A Single Value (Known as Single-Value Subquery or Scalar Subquery)

Practice #1-1: Use subquery in WHERE clause with an aggregate function.

Select Max(ShippedDate) From orders

Select OrderID, CustomerID

From Orders

Where ShippedDate = '1998-05-06 00:00:00'

--An aggregate may not appear in the WHERE clause unless

--it is in a subquery contained in a HAVING clause or a select list,

--and the column being aggregated is an outer reference.

Select OrderID, CustomerID

From Orders

Where ShippedDate = (Select max(ShippedDate) From Orders);

SQL Admin - Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

Saving Changes Is Not Permitted. The Changes That You Have Made Require The Following Tables To Be Dropped And Re-Created

Cause

If the Prevent saving changes that require the table re-creation option is enabled, one or more of the following changes are made to the table in database:

        I.            Change Allow Nulls setting for a column.

      II.            Reorder columns in the table.

    III.            Change column data type.

    IV.            Add a new column.

You are altering the metadata structure of the table when you changed a table in database, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table.

Solution

To change the Prevent saving changes that require the table re-creation option, follow these steps:

        I.            In SSMS

      II.            Go to Tools menu, click Options.

    III.            Click Designers.

    IV.            Uncheck (clear) the Prevent saving changes that require the table re-creation checkbox

      V.            Click OK.

SQL - CTE Bottom to Top Recursive

Create Table EmployeeTable ([EmployeeId] int, [Name] varchar(10), [ManagerId] int)

INSERT EmployeeTable VALUES (1,'Jane', NULL )       -- StaffHierarchy is as follows:

INSERT EmployeeTable VALUES (2,'Robert',1)          --                    1-Jane

INSERT EmployeeTable VALUES (3,'Tony',2)            --                   /      \

INSERT EmployeeTable VALUES (4,'Albert',3)          --            2-Robert       9-Felix

INSERT EmployeeTable VALUES (5,'David',3)           --            /       \           \

INSERT EmployeeTable VALUES (6,'Heskey',2)          --     3-Tony          6-Heskey    10-Kent

INSERT EmployeeTable VALUES (7,'Marco',6)           --    /      \          /    \

INSERT EmployeeTable VALUES (8,'Judy',6)            -- 4-Albert  5-David 7-Marco  8-Judy

INSERT EmployeeTable VALUES (9,'Felix',1)           --

INSERT EmployeeTable VALUES (10,'Kent',9)

DECLARE @employeeId int = 4;

WITH StaffHierarchy AS

(

    SELECT

            A.[EmployeeId], A.[Name], A.ManagerId, 0 AS [Level]

        FROM EmployeeTable A

        WHERE A.EmployeeId=@employeeId OR (@employeeId IS NULL AND A.ManagerId IS NULL)

    UNION ALL

        SELECT

            B.[EmployeeId], B.[Name], B.ManagerId, C.[Level]+1

        FROM StaffHierarchy C

        INNER JOIN EmployeeTable B ON C.ManagerId=B.[EmployeeId]

)

SELECT EmployeeId,

       [Name],

       ManagerId,

       [Level]

FROM StaffHierarchy

ORDER BY ManagerId asc;