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

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

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

SQL (MSSQL) Telerik Academy Alpha

Databases

Table of contents

SQL Overview

What is SQL?

What is SQL?

Relational Databases and SQL

Execution

Some examples

SQL

Capabilities

Database Schema

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

SELECT

JOIN

JOINS

JOINS

Cartesian product

INNER JOIN

Equijoins

Self-Join

Self-Join

INSERT, UPDATE, DELETE

INSERT

UPDATE

DELETE

Advanced SQL

Nested SELECT

Nested SELECT

Nested SELECT

Group Functions

Group Functions

Group Functions

Group Functions

Groups of data

GroupBy

GroupBy Illeal

GroupBy Restrictions

GroupBy Having

Functions in SQL Server

Functions

Functions

Functions

Functions

DDL (Data Definition Language)

DDL

Creating

Modifying

Deleting

T-SQL

What is T-SQL?

What is T-SQL?

Batch Directives

Stored Procedures

Stored Procedures

Questions?

SQL (MSSQL)
Telerik Academy Alpha