SQL Advanced in MS SQL

SQL Advanced in MS SQL Training Materials

IMPORTANT NOTE for trainers only: 
Please DO NOT change these materials, especially the EXERCISES, without direct permission from LUKASZ SOKOLOWSKI.

Copyright Notice

Copyright © 2004-2023 by NobleProg Limited All rights reserved.

This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.

Recap of Structured Query Language

• DDL
• DML

DDL

Data Definition Language

• A collection of imperative verbs
• Modify the schema of the database
• Adding, changing, or deleting definitions of tables or other objects

DDL Statements

Examples

• CREATE ...
  • FUNCTION, DATABASE, PROCEDURE, TYPE, USER, VIEW, SCHEMA
• ALTER ...
  • INDEX, TABLE, TRIGGER
• DROP ...
  • RULE, STATISTICS, SYNONYM
• Other DDL statements
  • ENABLE TRIGGER - DISABLE TRIGGER - TRUNCATE TABLE - UPDATE STATISTICS
• More here - learn.microsoft.com/en-us/sql/t-sql/statements/statements

DML

Data Manipulation Language

• Used to insert, query, delete and update data in a database
• Comprises the 'SQL-data change' statements
  • Modify stored data but not the schema or database objects

DML Statements

• SELECT
• INSERT
• DELETE
• UPDATE
• MERGE

Exercises

• Escape in LIKE, more special characters
• CASE expression
• Grouping sets
• Hierarchical queries
• Analytic functions
• Insert
• Delete
• Update
• Stored procedures
• Transactions
• Cursors
• Triggers
• Procedural programming, handling errors

Escape in LIKE, more special characters

--- DB preps
USE mike
GO

CREATE TABLE [dbo].[dept2](
	[DEPTNO] [numeric](2, 0) NOT NULL,
	[DNAME] [char](14) NULL,
	[LOC] [char](13) NULL,
)

INSERT INTO dept2(DEPTNO, DNAME, LOC)
VALUES (10, 'ACCOUNTING', 'NEW_YORK');

INSERT INTO dept2(DEPTNO, DNAME, LOC)
VALUES (20, 'RESEARCH', 'OLD_TOWN');

INSERT INTO dept2(DEPTNO, DNAME, LOC)
VALUES (30, 'SALES', 'PARIS');

INSERT INTO dept2(DEPTNO, DNAME, LOC)
VALUES (40, 'OPERATIONS', 'BERLIN');

--- Escape string in LIKE clause
SELECT
    dname, loc
FROM
    dept2
WHERE
    loc LIKE '%\_YORK' ESCAPE '\'
;

More specials in LIKE

• [e-k] range
• [bs] set
• [^] negation (not) of range or set
• [_], [%] simpler escaping
• Examples

SELECT dname, loc FROM dept2
WHERE
    loc LIKE '%[_]%'
    -- dname LIKE '[a-o]%'
    -- dname LIKE '[an]%'
    -- dname LIKE '[^or]%'
;

• Exercises
  • Find all employee names containing letters L or N or W
  • Find all employee names containing letters L and N and W
  • Search for people hired not in months: Jan and Dec

CASE expression

• Example

 
---- Case examples
-- Simple vs Searched 
SELECT DISTINCT job, sal,
  ---- Simple - same condition
  (CASE grade 
    WHEN 5 THEN 'Bossy'
    WHEN 4 THEN 'CEO monies'
    ELSE 'Peanuts' END) AS How_Much,
  ---- Searched - multiple conditions
  (CASE
    WHEN sal BETWEEN 500 AND 1200 THEN 'Rice'
    WHEN ((sal BETWEEN 1250 AND 1800) AND comm IS NULL ) THEN 'Beans'
    ELSE 'Coconuts' END) AS "Who's rich?"
  FROM emp JOIN salgrade
    ON sal BETWEEN losal AND hisal
  ORDER BY 2;
---------------------------------
---- https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

• Exercise - Calculate report per job, like below
  • summarize salary bigger than 2000, otherwise put 0
  • average of commissions lower than 1000, otherwise 0
  • how many people

-- 5 rows
----------------------------------------------------------------------
Job        Summarized Salary    Average Commission    Number of people
----------------------------------------------------------------------
ANALYST	   6000                 0                     2
CLERK	   0                    0                     4
SALESMAN   0	                200                   4
MANAGER	   8275                 0                     3
PRESIDENT  5000                 0                     1
----------------------------------------------------------------------

Grouping sets

------ EXAMPLE - GROUPING SETS
---- Exercises
-- 1. Compare it with simple grouping (undo the comment)
-- 2. Improve it - group also by 'grade' and count it
----
select deptno, job, sum(sal)
  from emp
  where deptno in (20, 30)
  -- group by deptno, job
  group by GROUPING SETS( 
      (deptno, job), 
      (deptno), 
      (job) ) 
  order by deptno, job
;

Hierarchy

• Using outer joins only
• Recursively with CTE (Common Table Expression)
• Built-in support - hierarchyid column type

Outer joins example

-- Show all workers and their bosses, within 3 levels of hierarchy.
SELECT W.ename AS Level0, B.ename AS Level1, B2.ename AS Level2, B3.ename AS Level3
FROM emp W LEFT JOIN emp B	ON W.mgr = B.id
		   LEFT JOIN emp B2 ON B.mgr = B2.id
		   LEFT JOIN emp B3 ON B2.mgr = B3.id
ORDER BY 4, 3, 2
;

CTE Example

-- Steps
---- 1. CTE, used in recursion
---- 2. UNION ALL
---- 3. INNER JOIN

CTE Ex Skeleton

-- Rewrite above 'join' solution into 'recursive CTE'
WITH ...
AS (SELECT ...
	UNION ALL
	SELECT ...
	)
SELECT *
FROM ...
;

CTE Exercises

1. Get the level for each node
2. Get the path for each node
3. Get the sorted hierarchy tree result
   • parent followed by all of its children, then all the children of each one of those and so on
   • use functions row_number() and power()

hierarchyid Example & Exercises

hierarchyid

Analytic functions

• https://learn.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-ver16

-- Example 1
---- Find the 2 highest paid employees in each department
SELECT deptno, ename, sal
FROM
(
  SELECT
    deptno, ename, sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rn
  FROM emp
) AS context
WHERE rn <= 2
ORDER BY deptno, sal DESC;

-- Exercise 1
---- Count cumulative distribution of salaries by manager
------ only for people from departments 10 and 30
------ sort it by hierarchy and id

-- Exercise 2
---- Extend above with also percent rank
------ show department name
------ sort it within window by hierarchy

LAG, LEAD

------ LAG vs LEAD example - no need to use 'self-join'
--- For each Clerk find the salary of the employee hired just before
SELECT hiredate, ename, sal,
  LAG(sal, 1, 0) OVER (ORDER BY hiredate) AS prev_sal
  FROM emp
  WHERE job = 'CLERK'
  ORDER BY hiredate;
----------------------
-- Exercise
--- For each employee in Department 30 find the hire date of the employee hired just after

FIRST, LAST

-- Return the employee with the fewest monies compared to other employees with the same job title
SELECT job,
    ename,
    sal,
    dname,
    FIRST_VALUE(ename) OVER (
        PARTITION BY job ORDER BY sal ASC ROWS UNBOUNDED PRECEDING
    ) AS FewestMonies
FROM emp AS e
INNER JOIN dept AS d
    ON e.deptno = d.deptno
ORDER BY job;
-------------------------
-- Exercise
---- Return the hire date of the last employee in each department for the given salary
------ show department name and monies grade

Example 1, 'WITH'

WITH functions_names as (
    SELECT 'GetAllTheBookingForms' f union all
    SELECT 'PrepareDelegatesDetails' f union all
    SELECT 'HelperFunction' f
)
SELECT
    f "Function name",
    lower( f )
    as "FN after"
FROM functions_names;

Exercise 1, 'INSERT' and 'WITH'

• Create a table (FinancialReport): Ename, Dname, Grade
• Insert into it all the related data - only from Sales and Accounting
• Do it via WITH

Exercise 2, 'DELETE' and 'OUTPUT'

• Remove grades 3 and 1 from FinancialReport
• Save the deleted rows in a variable (temporary table)

Exercise 3, 'UPDATE'

• Actualize grade 4: boost it up to 4k, make sure the rest of the data is consistent
• Compare the old and new values in temporary variable (table)

Example 4, 'Stored Procedures', with 'INPUT' and 'OUTPUT' accordingly

• https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#d-create-a-procedure-with-input-parameters
• https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#f-use-output-parameters

Exercise 4a, 'Stored Procedures' with 'INPUT'

• Prepare the procedure which will search for 'job' and 'sal'

Exercise 4b, 'Stored Procedures' with 'OUTPUT'

• Invent the procedure which will return name and all the possible monies the person can earn in next 5 years.

Example 5, 'Transactions'

• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

Exercise 5, 'Transactions'

• Create simple named transaction which rises all the commissioned guys with 100 quid.

Example 6, 'Cursors'

• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/fetch-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#a-using-fetch-in-a-simple-cursor

Exercise 6, 'Fetch'

• Fetch people with names ending with N or R.

Example 7, 'Triggers'

• https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#e-using-a-server-scoped-ddl-trigger

Exercise 7, 'Trigger' and 'DDL'

• Run the above example and test it (=

Example 8, 'Procedural Programming'

• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#examples

Exercise 8, 'Procedural Programming'

• Delete person only if he/she has no subordinates (use procedure and transaction with the save point)