SQL (MSSQL)
Telerik Academy Alpha

 

Databases

 Table of contents

SQL Overview

 What is SQL?

  • SQL is a standard language for accessing and manipulating databases.

  • SQL stands for Structured Query Language

  • SQL lets you access and manipulate databases

  • SQL is an ANSI (American National Standards Institute) standard

 What is SQL?

  • SQL consists of:

    • Data Manipulation Language (DML)

      • SELECT, INSERT, UPDATE, DELETE

    • Data Definition Language (DDL)

      • CREATE, DROP, ALTER, GRANT, REVOKE

 Relational Databases and SQL

  • A relational database can be accessed and modified by executing SQL statements

  • SQL allows

    • Defining / modifying the database schema

    • Searching / modifying table data

  • A set of SQL commands are available for extracting subset of the table data

  • Most SQL commands return a single value or record set

 Execution

  • SQL commands are executed through a database connection

    • DB connection is a channel between the client and the SQL server

    • DB connections take resources and should be closed when no longer used

    • Multiple clients can be connected to the SQL server at the same time

    • SQL commands can be executed in parallel
      Transactions and isolation deal with concurrency

 Some examples

SELECT first_name, last_name, job_title
FROM employees

SELECT * FROM projects 
WHERE start_date= '1/1/2018'

INSERT INTO projects(name, start_date)
VALUES('Introduction to SQL Course', '1/10/2018')

UPDATE projects
SET end_date = '8/31/2018'
WHERE start_date= '1/1/2018'

DELETE FROM projects
WHERE start_date= '1/1/2020'

SQL

 Capabilities

  • Selection

    • A select operation reduces the length of a table by filtering out unwanted rows

  • Projection

    • Just as the select operation reduces the number of rows, the project operation reduces the number of columns

  • Join

    • Used to relate two or more independent tables that share a common column

 Database Schema

SELECT

 SELECT

  • SELECT identifies what columns

  • FROM identifies which table
     

  • Examples

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM	table
SELECT * FROM Departments

SELECT LastName, Salary, Salary + 300 FROM Employees

 SELECT

  • Aliases rename a column heading

  • Useful with calculations

  • Immediately follows the column name

    • There is an optional AS keyword

  • Double quotation marks or [ ] if contains spaces

SELECT FirstName, LastName, Salary, Salary * 0.2 AS [Salary Bonus]
FROM Employees

SELECT FirstName, LastName, Salary, Salary * 0.2 AS "Salary Bonus"
FROM Employees

 SELECT

  • A literal is a character, a number, or a date included in the SELECT list

    • Date and character literal values must be enclosed within single quotation marks

    • Each character string is output once for each row returned

SELECT FirstName + '''s last name is ' + LastName AS [Our Employees]
FROM Employees

 SELECT

  • The default display of queries is all rows, including duplicate rows

    • Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause

SELECT DISTINCT DepartmentID FROM Employees

 SELECT

  • UNION combines the results from several SELECT statements

    • The columns count and types should match

    • By default UNION selects only distinct values

  • INTERSECT/EXCEPT perform logical intersection/difference between given two sets of records

SELECT FirstName AS Name FROM Employees UNION SELECT LastName AS Name
FROM Employees

 SELECT

  • Restrict the rows returned by using the WHERE clause

  • The WHERE clause is used to extract only those records that fulfill a specified condition

SELECT FirstName, LastName, DepartmentID 
FROM Employees 
WHERE LastName = 'Sullivan'

SELECT LastName, Salary 
FROM Employees
WHERE Salary <= 20000

 SELECT

  • Using BETWEEN operator to specify a range

  • Using IN/NOT IN to specify a set of values

  • Using LIKE operator to specify a pattern

    • ​ % means 0 or more chars;

    • _ means one char

SELECT LastName, Salary FROM Employees
WHERE Salary BETWEEN 20000 AND 22000

SELECT FirstName, LastName, ManagerID FROM Employees 
WHERE ManagerID IN (109, 3, 16)

SELECT FirstName FROM Employees WHERE FirstName LIKE 'S%'

 SELECT

  • Using BETWEEN operator to specify a range

  • Using IN/NOT IN to specify a set of values

  • Using LIKE operator to specify a pattern

    • ​ % means 0 or more chars;

    • _ means one char

SELECT LastName, Salary FROM Employees
WHERE Salary BETWEEN 20000 AND 22000

SELECT FirstName, LastName, ManagerID FROM Employees 
WHERE ManagerID IN (109, 3, 16)

SELECT FirstName FROM Employees WHERE FirstName LIKE 'S%'

 SELECT

  • Checking for NULL value:

    • IS NULL

    • IS NOT NULL

  • ​Attention: COLUMN = NULL is always false!

SELECT LastName, ManagerId FROM Employees
WHERE ManagerId IS NULL

SELECT LastName, ManagerId FROM Employees
WHERE ManagerId IS NULL

-- Always FALSE
SELECT LAST_NAME, MANAGER_ID FROM EMPLOYEES
WHERE MANAGER_ID = NULL

 SELECT

  • Logical operators

    • NOT

    • OR

    • AND

SELECT FirstName, LastName FROM Employees
WHERE Salary >= 20000 AND LastName LIKE 'C%'

SELECT LastName FROM Employees
WHERE ManagerID IS NOT NULL OR LastName LIKE '%so_'

SELECT LastName FROM Employees
WHERE NOT (ManagerID = 3 OR ManagerID = 4)

 SELECT

  • Sort rows with the ORDER BY clause

    • ASC: ascending order, default

    • DESC: descending order

SELECT e.EmployeeID, e.LastName, e.DepartmentID, d.DepartmentID
FROM Employees e 
INNER JOIN Departments d 
ON e.DepartmentID = d.DepartmentID

JOIN

 JOINS

  • A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

  • (INNER) JOIN: Returns records that have matching values in both tables

  • LEFT (OUTER) JOIN: Return all records from the left table, and the unmatched records from the right table

  • RIGHT (OUTER) JOIN: Return all records from the right table, and the unmatched records from the left table

  • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

 JOINS

 Cartesian product

  • A Cartesian product is formed when:

    • A join condition is omitted

    • A join condition is invalid

    • All rows in the first table are joined to all rows in the second table

  • To avoid a Cartesian product, always include a valid join condition

 INNER JOIN

  • To specify arbitrary conditions or specify columns to join, the ON clause is used

    • Such JOIN is called also INNER JOIN

SELECT e.EmployeeID, e.LastName, e.DepartmentID, 
       d.DepartmentID, d.Name AS DepartmentName
FROM Employees e 
INNER JOIN Departments d 
ON e.DepartmentID = d.DepartmentID

 Equijoins

  • Inner joins with join conditions pushed down to the WHERE clause

SELECT e.EmployeeID, e.LastName, e.DepartmentID, 
       d.DepartmentID, d.Name AS DepartmentName
FROM Employees e, Departments d 
WHERE e.DepartmentID = d.DepartmentID

 Self-Join

  • Self-join means to join a table to itself
    Always used with table aliases

SELECT e.FirstName + ' ' + e.LastName +
 ' is managed by ' + m.LastName as Message
FROM Employees e JOIN Employees m
ON (e.ManagerId = m.EmployeeId)

 Self-Join

  • Self-join means to join a table to itself
    Always used with table aliases

SELECT e.FirstName + ' ' + e.LastName +
 ' is managed by ' + m.LastName as Message
FROM Employees e JOIN Employees m
ON (e.ManagerId = m.EmployeeId)

INSERT, UPDATE, DELETE

 INSERT

  • INSERT command

    • INSERT INTO <table> VALUES (<values>)

    • INSERT INTO <table>(<columns>) VALUES (<values>)

    • INSERT INTO <table> SELECT <values>

INSERT INTO EmployeesProjects VALUES (229, 25)

INSERT INTO Projects(Name, StartDate)
VALUES ('New project', GETDATE())

INSERT INTO Projects(Name, StartDate)
SELECT Name + ' Restructuring', GETDATE()
FROM Departments

 UPDATE

  • UPDATE command

  • UPDATE <table> SET <column=expression> WHERE <condition>

    • ​Note: Don't forget the WHERE clause!

UPDATE Employees
SET LastName = "Brown"
WHERE EmployeeID = 1;

UPDATE Employees
SET Salary = Salary * 1.10, JobTitle = 'Senior ' + JobTitle
WHERE DepartmentID = 3

 DELETE

  • Deleting rows from a table

    • DELETE FROM <table> WHERE <condition>

      • Note: Don’t forget the WHERE clause!

DELETE FROM Employees
WHERE EmployeeID = 1

DELETE FROM Employees 
WHERE LastName LIKE 'S%'

 

  • Delete all rows from a table at once

TRUNCATE TABLE Users

Advanced SQL

Nested SELECT

 Nested SELECT

  • SELECT statements can be nested in the where clause

    • Note: always prefer joins to nested SELECT statements for better performance

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary = 
  (SELECT MAX(Salary) FROM Employees)

SELECT FirstName, LastName, DepartmentID, Salary
FROM Employees
WHERE DepartmentID IN 
  (SELECT DepartmentID FROM Departments
   WHERE Name='Sales')

 Nested SELECT

  • Tables from the main SELECT can be referred in the nested SELECT by aliases

  • Example:

    • Find the maximal salary for each department and the name of the employee that gets it

SELECT FirstName, LastName, DepartmentID, Salary
FROM Employees e
WHERE Salary = 
  (SELECT MAX(Salary) FROM Employees 
   WHERE DepartmentID = e.DepartmentID)
ORDER BY DepartmentID

Group Functions

 Group Functions

  • COUNT(*) – count of the selected rows

  • SUM(column) – sum of the values in given column from the selected rows

  • AVG(column) – average of the values in given column

  • MAX(column) – the maximal value in given column

  • MIN(column) – the minimal value in given column

  • Group functions ignore NULL values in the target column

 Group Functions

  • Group functions operate over sets of rows to give one single result (per group)

SELECT
  AVG(Salary) [Average Salary],
  MAX(Salary) [Max Salary],
  MIN(Salary) [Min Salary],
  SUM(Salary) [Salary Sum]
FROM Employees
WHERE JobTitle = 'Design Engineer'

 Group Functions

  • Group functions operate over sets of rows to give one single result (per group)

SELECT MIN(HireDate) MinHD, MAX(HireDate) MaxHD
FROM Employees

SELECT COUNT(*) Cnt FROM Employees 
WHERE DepartmentID = 3

  • Group functions ignore NULL values in the target column

SELECT AVG(ManagerID) Avg,
  SUM(ManagerID) / COUNT(*) AvgAll
FROM Employees

Groups of data

 GroupBy

  • The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns

SELECT DepartmentID, JobTitle, 
  SUM(Salary) as Salaries, COUNT(*) as Count
FROM Employees
GROUP BY DepartmentID, JobTitle

 GroupBy Illeal

  • Can not combine columns with groups functions unless when using GROUP BY

  • Can not use `WHERE` for group functions

SELECT DepartmentID, COUNT(LastName)
FROM Employees

SELECT DepartmentID, AVG(Salary)
FROM Employees
WHERE AVG(Salary) > 30
GROUP BY DepartmentID

 GroupBy Restrictions

  • When using groups we can select only columns listed in the GROUP BY and grouping functions over the other columns

SELECT DepartmentID, JobTitle, 
  SUM(Salary) AS cost, MIN(HireDate) as StartDate
FROM Employees
GROUP BY DepartmentID, JobTitle

  • Can not select columns not listed in the GROUP BY clause

  • It is allowed to apply group functions over the columns in the GROUP BY clause, but has no sense

 GroupBy Having

  • Grouping function can be applied on columns from joined tables

SELECT COUNT(*) AS EmpCount, d.Name AS DeptName
FROM Employees e JOIN Departments d
    ON e.DepartmentID = d.DepartmentID
WHERE e.HireDate BETWEEN '1999-2-1' AND '2002-12-31'
GROUP BY d.Name
HAVING COUNT(*) > 5
ORDER BY EmpCount DESC

Functions in SQL Server

 Functions

  • Single-row functions

    • String functions

    • Mathematical functions

    • Date functions

    • Conversion functions

  • Multiple-row functions

    • Aggregate functions

 Functions

  • ISNULL(<value>,<default_value>) – converts NULL values to given default value

  • COALESCE(<value>,<value>,<value>) – returns the first that is not NULL

SELECT Name AS [Projects Name], 
  ISNULL(EndDate, GETDATE()) AS [End Date]
FROM Projects

 Functions

  • Changing the casing

    • LOWER, UPPER

  • Manipulating characters

    • SUBSTRING, LEN, LEFT, RIGHT, TRIM, REPLACE

SELECT LastName, LEN(LastName) AS LastNameLen,
  UPPER(LastName) AS UpperLastName
FROM Employees
WHERE RIGHT(LastName, 3) = 'son'

 Functions

  • Mathematical Functions

    • ROUND, FLOOR, POWER, ABS, SQRT, ...

  • Date Functions

    • GETDATE, DATEADD, DAY, MONTH, YEAR, …

  • Conversion Functions

    • CONVERT, CAST 

SELECT FLOOR(3.14) --> 3
SELECT ROUND(5.86, 0) --> 6.00

SELECT CONVERT(DATETIME, '20051231', 112)
-->`2005-12-31 00:00:00.000`
--`112` is the ISO formatting style `YYYYMMDD`

DDL (Data Definition Language)

 DDL

  • DDL commands for defining/editing objects

    • CREATE

    • ALTER

    • DROP

  • Data Control Language (DCL) for managing access permissions

    • GRANT

    • REVOKE

    • DENY

 Creating

  • CREATE command

    • CREATE TABLE <name> (<field_definitions>)

    • CREATE VIEW <name> AS <select>

    • CREATE <object> <definition>

CREATE TABLE Persons (
  PersonID int IDENTITY,
  Name nvarchar(100) NOT NULL,
  CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
)
GO

 Modifying

  • ALTER command

    • ALTER TABLE <name> <command>

    • ALTER <object> <command>

-- Add a foreign key constraint Cities --> Country
ALTER TABLE Cities
ADD CONSTRAINT FK_Cities_Countries
  FOREIGN KEY (CountryID)
  REFERENCES Countries(CountryID)
-- Add column Population to the table Country
ALTER TABLE Countries ADD Population int
-- Remove column Population from the table Country
ALTER TABLE Countries DROP COLUMN Population

 Deleting

  • DROP command

    • DROP TABLE <name>

    • DROP TRIGGER <name>

    • DROP INDEX &<name>

    • DROP <object

DROP TABLE Persons
ALTER TABLE Cities
DROP CONSTRAINT FK_Cities_Countries

T-SQL

 What is T-SQL?

  • Transact-SQL (T-SQL) is database manipulation language, an extension to SQL

    • Supported by Microsoft SQL Server and Sybase

    • Used for stored procedures, functions, triggers

  • Transact-SQL extends SQL with few additional features:

    • Local variables

    • Control flow constructs (ifs, loops, etc.)

    • Functions for strings, dates, math, etc.

 What is T-SQL?

  • There are 3 types of statements in the Transact-SQL (T-SQL) language:

    • Data Definition Language (DDL) Statements

    • Data Control Language (DCL) Statements

    • Data Manipulation Language (DML) Statements

 Batch Directives

  • USE <database>

    • Switch the active database

  • GO

    • Separates batches (sequences of commands)

  • EXEC(<command>)

    • Executes a user-defined or system function stored procedure, or an extended stored procedure

    • Can supply parameters to be passed as input

    • Can execute SQL command given as string

 Stored Procedures

  • Stored procedures are named sequences of T-SQL statements

    • Encapsulate repetitive program logic

    • Can accept input parameters

    • Can return output results

  • Benefits of stored procedures

    • Share application logic

    • Improved performance 

    • Reduced network traffic

 Stored Procedures

USE TelerikAcademy
GO

-- Create
CREATE PROC dbo.usp_SelectSeniorEmployees
AS
  SELECT * 
  FROM Employees
  WHERE DATEDIFF(Year, HireDate, GETDATE()) > 5
GO

-- Execute
EXEC usp_SelectSeniorEmployees

INSERT INTO Customers
EXEC usp_SelectSpecialCustomers

Questions?

[Databases] SQL

By telerikacademy

Made with Slides.com

[Databases] SQL

  • 1,421

More from telerikacademy