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;

SQL - Left Join and Left Outer Join

SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country

  FROM Customer C LEFT JOIN [Order] O

    ON O.CustomerId = C.Id

ORDER BY TotalAmount

SQL - Cross Join Or Full Join Or Full Outer Join

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, performs a full join. A full join is also called a cross join because each row of each table is crossed with each row in every other table to produce all possible combinations. This is also known as the Cartesian Product.

Joining tables this way has the potential to produce a very large number of rows because the possible row count is the product of the number of rows in each table. A full join between three tables that contain 10, 20, and 30 rows, respectively, could return 10x20x30 = 6 thousand rows.

The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SELECT DISTINCT JobTitle

          FROM   HumanResources.Employee

SELECT DISTINCT Gender

          FROM   HumanResources.Employee

SELECT JobTitle,

       Gender,

       COUNT(1) AS NumberEmployees

          FROM HumanResources.Employee

          GROUP BY JobTitle, Gender

WITH cteJobTitle (JobTitle)

AS  (SELECT DISTINCT JobTitle

     FROM   HumanResources.Employee),

cteGender (Gender)

AS (SELECT DISTINCT Gender

    FROM   HumanResources.Employee)

SELECT   J.JobTitle,

         G.Gender

FROM     cteJobTitle AS J

         CROSS JOIN cteGender AS G

ORDER BY J.JobTitle

WITH     cteJobTitle (JobTitle)

AS       (SELECT DISTINCT JobTitle

          FROM   HumanResources.Employee),

         cteGender (Gender)

AS       (SELECT DISTINCT Gender

          FROM   HumanResources.Employee),

         cteCounts (JobTitle, Gender, NumberEmployees)

AS       (SELECT   JobTitle,

                   Gender,

                   COUNT(1) AS NumberEmployees

          FROM     HumanResources.Employee

          GROUP BY JobTitle, Gender)

SELECT   J.JobTitle,

         G.Gender,

         COALESCE (C.NumberEmployees, 0) as NumberEmployees

FROM     cteJobTitle AS J

         CROSS JOIN cteGender AS G

         LEFT OUTER JOIN cteCounts AS C

         ON C.JobTitle = J.JobTitle

            AND C.Gender = G.Gender

ORDER BY J.JobTitle, G.Gender;

Query Employee With Birthdate in the Next 7 days

DECLARE @TODAY INT = DATEPART(DAYOFYEAR, GETDATE())

DECLARE @DAYSINYEAR INT = DATEPART(DAYOFYEAR, DATEADD(DAY, -1 , DATEFROMPARTS(DATEPART(YEAR, DATEADD(YEAR, 1, GETDATE())), 1, 1)))

SELECT [DATE_OF_BIRTH]

FROM EMPLOYEE

WHERE (CASE WHEN DATEPART(DAYOFYEAR, [DATE_OF_BIRTH]) < @TODAY

            THEN DATEPART(DAYOFYEAR, [DATE_OF_BIRTH]) + @DAYSINYEAR

            ELSE DATEPART(DAYOFYEAR, [DATE_OF_BIRTH]) END)

      BETWEEN @TODAY and @TODAY + 6