Latest revision as of 15:14, 22 August 2024

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

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

Get the level for each node
Get the path for each node
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

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)

SQL Advanced in MS SQL: Difference between revisions