SQL Advanced in MS SQL
Jump to navigation
Jump to search
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
- 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
-- TODO
hierarchyid Exercise
-- TODO
Analytic functions
-- 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
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'
Exercise 5, 'Transactions'
- Create simple named transaction which rises all the commissioned guys with 100 quid.
Example 6, 'Cursors'
Exercise 6, 'Fetch'
- Fetch people with names ending with N or R.
Example 7, 'Triggers'
Exercise 7, 'Trigger' and 'DDL'
- Run the above example and test it (=
Example 8, 'Procedural Programming'
Exercise 8, 'Procedural Programming'
- Delete person only if he/she has no subordinates (use procedure and transaction with the save point)
