SQL Advanced in MS SQL

From Training Material
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.

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
  • 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
;

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'

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)
Retrieved from ‘https://training-course-material.com/index.php?title=SQL_Advanced_in_MS_SQL&oldid=87380’