To

SQL Course
Mr. Festus Peter
Having completed this course, you should be able to:
✓ Explain the basic concepts of SQL and databases.
✓ Recognize how to write basic SQL queries to
retrieve data from one or more tables in a
database.
✓ Recall how to use the WHERE keyword to filter
data based on specific criteria.
✓ Describe how to use the ORDER BY keyword to
sort data in ascending or descending order.
✓ Summarize how to write queries involving
aggregate functions like COUNT, SUM, AVG, MAX,
and MIN to perform calculations on data in a
database.
✓ Discuss how to use string functions to manipulate
text data in SQL.
✓ Analyze and use date and time functions like
DATEADD, DATEDIFF, and GETDATE to manipulate
date and time data in SQL.
✓ Recall how to create and modify tables in a
database.
✓ Recognize how to write complex SQL queries that
involve multiple tables, subqueries, and joins to
retrieve and transform data from a database
COURSES / MODULES
1. Introduction To SQL
➢ Downloading and Installing SQL Server and SSMS
➢ Database concept
➢ SQL Connections
➢ Data Type in SQL
➢ Create Database objects
2. Data Types
➢ Drop table Statements
➢ Various Constraints
➢ Creating Views
3. Table Commands
➢ insert Statement
➢ Insert as Select Statement
➢ Update Statement
➢ Delete Statement
➢ Select Statement
➢ Select Statement: Alias
➢ Select statement : Distinct Values
➢ Sorting/ Filtering
4. Comparison Operator
➢ Where Clause
➢ Group by
➢ Having Clause
➢ Not
➢ In
➢ Symbols
➢ Like

5. Joins
➢ Self-Join
➢ Inner Join
➢ Left Outer Join
➢ Right Outer Join
➢ Full Outer Join
➢ Cross Join
6. Functions
➢ A function accepts input parameters, perform actions, and then return the
result either as a single value or table.
➢ Conversation Functions
➢ Logical Functions
➢ Math Functions
➢ Aggregate Functions
➢ String Functions
➢ Date and Time Functions
➢ Custom Functions
➢ Stored Procedure
➢ User Defined Functions
 Module 1

Introduction to SQL
MODULE 1 OBJECTIVES
➢Introduction To SQL
➢Downloading and Installing SQL
Server and SSMS
➢Database concept
➢SQL Connections
➢Restoring Sample DB
➢Accessing Sample DB
 SQL

STRUCTURED QUERY LANGUAGE

What is Structured Query Language (SQL)?
SQL is a programming language used by nearly
all relational databases to query, manipulate,
define data, and to provide access control.
SQL was first developed at IBM in 1970s with
Oracle as a major contributor, which led to
implementation of the SQL ANSI standard,
SQL has spurred many extensions from
companies such as IBM, Oracle, and Microsoft.
 Definition of Database
A database is an organized collection of
structured information, or data, typically stored
electronically in a computer system.
A database is usually controlled by a
database management system (DBMS)
 SQL Server
Oracle

Database Postgre SQL

Daily business activities

 SQL Server
Data Warehouse

Records

Historical repository of data for reporting

needs
 Data Lake AWS
AZURE
BIG QUERY

All Records

Stores all sorts of data (Structure & Semi-

structured)
Database Concept:
Relational Database Management System:
(RDBMS) is a group of software that are
used to create and manage databases.
✓ Oracle
✓ Sql Server
✓ SQL Workbench
✓ PostgreSQL
✓ Ms Access
✓ Maria DB etc.
Database Concept:

Relational Database: A relational database

is a collection of relations or two-
dimensional tables.
Advantages of Database
1. Manages large amounts of data
A database stores and manages a large amount of
data on a daily basis. This would not be possible
using any other tool such as a spreadsheet as they
would simply not work.

2. Accurate
A database is pretty accurate as it has all sorts of
build in constraints, checks etc. This means that
the information available in a database is
guaranteed to be correct in most cases.
3. Easy to update data
In a database, it is easy to update data using
various Data Manipulation languages (DML)
available.

4. Security of data
Databases have various methods to ensure
security of data. There are user logins required
before accessing a database and various access
specifiers. These allow only authorized users to
access the database.
5. Data Integrity
This is ensured in databases by using various
constraints for data. Data integrity in databases
makes sure that the data is accurate and consistent
in a database.
Disadvantages of Database
1. Increased Cost of Hardware and Software
To store a huge amount of data, one needs a
huge amount of space. Additionally, it will
require more memory and fast processing
power to run the DBMS. So, expensive
hardware and software will be needed that can
provide all these facilities.
2. Complexity
A DBMS fulfills lots of requirements and it
solves many problems related to databases. But
all these functionalities make DBMS an
extremely complex software. Developers,
designers, DBA, and End-users of database must
have complete skills of DBMS if they want to
use it properly.
3. Technical staff requirement
A dedicated team of technical staff is required
who can handle DBMS and as a result, the
company have to pay handsome salary to
them too.

4. Difficult Backup And Recovery

Database is processed and handled by several
users concurrently, so it becomes very difficult
to get the exact state of database at the time of
failure. It creates many problems and users get
confused about what to do next.
5. Database Failure
Data is the key for any organization, if data is
lost then the whole organization will collapse.
And as we know that in DBMS, all the files are
stored in a single database so the chances of
database failure become more.
Any accidental failure of components may
cause loss of valuable data.
6. Confidentiality, Privacy, and Security
In DBMS, information is made available to
users from remote locations, and because it is
centralized so chances of abuse are more
often than in a file-based system.
If information from the data center gets
corrupted then every user of the organization
will be in big trouble.
 Software
Microsoft SQL Server
Defining SQL Server:
This is a backend Relational database
management software created by Microsoft
in the 1980s that can mainly run on
Windows OS.

Versions of SQL Server:

2012, 2014, 2016, 2017, 2019, 2022
 PC Check
System Specifications
System Specifications

✓ Operating System: Windows 10, 11 etc

✓ RAM: 4gb & above
✓ Processor: 1.5ghz & above
✓ Dotnet Framework: 3.5
✓ HDD: 200gb & above free space
✓ System OS type: 64bit
Downloading
Softwares
 Downloading & Installing SQL Server 2022
(Express Edition)

➢ Microsoft Official website:

Download SQL Server Management Studio (SSMS)
SQL Server Management Studio (SSMS)
| Microsoft Learn
➢ Getintopc.com
➢ Google.com
 Downloading Sample Database 2022

➢ AdventureWorks2022
➢ AdventureWorksDW2022
➢ AdventureWorksLT2022

➢ SQL Server Management Studio 2022 (ssms)

➢ Winrar (Optional)
Installations
 Loading SQL Server 2022

&

Connecting to SQL Server 2022

AUTHENTICATION:
This is simply to validate the identity of user.

AUTHORISATION:
This is simply the privileges provided to user
as to what they can do within the database
 SQL SERVER APPLICATIONS (TOOLS)
✓ SQL Server Management Studio

✓ SQL Server Configuration Manager

✓ SQL Server Documentation

 ACCESSING THE SQL SERVER ENGINE

- DEFAULT CONNECTION
- CUSTOMIZED CONNECTION
RESTORING A SAMPLE DATABASE

ADVENTUREWORKS2022 Databases
 Module 2

Introduction to
Database Objects-Tables
MODULE 2 OBJECTIVES
➢Creating Database
➢Creating Tables
➢Data Types
➢Various Constraints
➢Creating Views
Understanding SQL Server 2022 Screen
&
Database Objects
 SQL Server Database Object

✓ TABLES
✓ VIEWS
✓ SYNONYMS
✓ DATABASE DIAGRAM. etc
 Creating a Database Table

✓ Graphical
✓ SQL
 Data Types

In SQL Server, each column, local variable,

expression, and parameter has a related data
type. A data type is an attribute that specifies
the type of data that the object can hold:
integer data, character data, monetary data,
date and time data, binary strings, and so
on.
Exact numerics

▪ Bigint
▪ numeric
▪ bit
▪ smallint
▪ decimal
▪ Smallmoney
▪ int
▪ tinyint
▪ money
Approximate numerics

▪ float

▪ real
Date and time

▪ date

▪ datetimeoffset

▪ datetime2

▪ smalldatetime

▪ datetime

▪ time
Character strings

▪ Char- Fixed Length

▪ Varchar- Variable Length

▪ text
Unicode character strings

▪ Nchar- Unicode – non English character *2bytes

▪ Nvarchar- Unicode – non English character *2bytes

▪ Ntext Unicode – non English character *2bytes

Binary strings

▪ binary

▪ varbinary

▪ image
Other data types

▪ cursor
▪ rowversion
▪ hierarchyid
▪ uniqueidentifier
▪ sql_variant
▪ xml
▪ Spatial Geometry Types
▪ Spatial Geography Types
▪ table
 Adding constraints to Database Table
SQL constraints are used to specify rules for data in a table
❑ Data Type
❑ Not Null
❑ Primary key
❑ Unique
❑ Check
❑ Default Value
❑ Computed Column
❑ Identity Specification
❑ Foreign key
Not Null Ensures that a column cannot have a
NULL value
That is, the column cannot be empty.
Primary Key is a unique record identifier.
When you make a column in the table as primary key
then this column will always have unique or distinct
values. Duplicate values and NULL value will not be
allowed in a primary key column. A table can only
have one primary key.
Unique Key is also a unique record identifier
When you make a column in the table as unique key
then this column will always have unique or distinct
values. Duplicate values will not be allowed.
However, NULL value is allowed in a column which
has unique key constraint. This is the major difference
between primary and unique key.
Check is used to ensure a column in a table adhere
to a certain condition/rule.
When a check constraint is used in a column, users
must obey the rules set for the column.
Default Value is used to set a binding value in a
column of a table if no value is specified.
It normally allows only one value to be in the
column of a table
Computed column is used to generate or compute
values from the manipulation of other columns in
the table.
Identity Specification is used to generate unique
numeric values to be used as primary key or unique
values in the table
Foreign key is used to create a master child kind of
relationship between two tables. When we make a
column in a table as foreign key, this column will
then have to be referenced from another column
from some other table
QUIZ
What is the difference between primary key,
unique key and foreign key?
 Importing Data
Into
Microsoft SQL Server
1. Using Import and Export Wizard
2. Using Copy and Paste
 SQL

STRUCTURED QUERY LANGUAGE

WHAT IS SQL (STRUCTURED QUERY LANGUAGE)?
is a standardized programming language
that is used to manage relational
databases and perform various operations
on the data in them. Initially created in
the 1970s, SQL is regularly used not only
by database administrators, but also by
developers writing data integration scripts
and data analysts looking to set up and
run analytical queries.
SQL is used for the following:
•modifying database table and index structures;
•adding, updating and deleting rows of data;
•retrieving subsets of information from within
relational database management systems
(RDBMSes) -- this information can be used for
transaction processing, analytics applications
and other applications that require
communicating with a relational database.
 Writing SQL Statements.

➢ SQL statements are not case-sensitive.

➢ SQL statements can be entered on one or more lines
➢ Keywords can’t be abbreviated or split across lines
➢ Clauses are usually placed on separate lines
➢ You are required to end each SQL statement with a
semi colon (;)
 SQL
STATEMENT

DDL- DATA DML- DATA DCL- DATA DTL-DATA DQL- DATA

DEFINITION MANIPULATION CONTROL TRANSACTION QUERY
LANGUAGE LANGUAGE LANGUAGE LANGUAGE LANGUAGE

CREATE INSERT GRANT COMMIT SELECT

ALTER UPDATE REVOKE ROLLBACK SHOW

DROP DELETE DENY SAVEPOINT HELP

TRUNCATE MERGE
 DDL
Data Definition Language.
DDL stands for Data Definition Language. They include
CREATE, DROP, ALTER and TRUNCATE statements.
DDL statements are used to create, remove or modify
database objects like table. You do not need to commit
the changes after running DDL commands.
CREATE statement can be used to create any database
objects like tables, views, functions, procedures, triggers
etc.
DROP statement can be used to remove any database
objects like tables, views, functions, procedures, triggers
etc.
ALTER statement can be used to modify the structure
of a database objects.
TRUNCATE statement can be used to remove all the
data from a table at once.
DDL: DATA DEFINITION LANGUAGE

CREATE: create table exams (COURSES NVARCHAR(20),

COURSE_ID NVARCHAR(5) PRIMARY KEY,
EXAMS_SCORES INT);

TRUNCATE:TRUNCATE TABLE EXAMS;

DROP: DROP TABLE EXAMS;

SP_HELP EXAMS;
 Alter Table
ALTER TABLE EXAMS ADD SCORE INT;

ALTER TABLE EXAMS ALTER COLUMN SCORE

NVARCHAR(50);

ALTER TABLE EXAMS DROP COLUMN SCORE;

EXEC SP_RENAME 'EXAMS.COURSES', 'COSS',

'COLUMN';
 Alter Table
To add a primary key to an existing table.

ALTER TABLE EXAMS ADD USERKEY IDENTITY

(1,1) NOT NULL;

You may need to manually set the primary key

on the design window.
 DQL
Data Query Language.
DQL stands for Data Query Language. It includes
only the SELECT statement.
SELECT statement is used to fetch and view data
from the database.

SELECT: SELECT * FROM EXAMS;

 DML
Data Manipulation Language.
DML stands for Data Manipulation Language. DML
includes INSERT, UPDATE, DELETE and MERGE
statements.
DML statements are used to add, remove or modify
data from database tables. It is mandatory to run the
COMMIT command after running a DML statement
so as to save the changes to the database (some tools
may have auto commit on so you don’t have to
manually run the commit command).
INSERT statement will add rows or records to a
table.
DELETE statement will remove one or multiple rows
from a table.
UPDATE statement will replace values in a table of
the database.
MERGE statement will either do an update or insert
to a table based on the available data. If the data is
present then it does an update. If data not present
then merge will do an insert.
Merge is part of the DML commands in SQL which
can be used either perform INSERT or UPDATE
based on the data in the respective table.
If the desired data is present then merge will update
the records. If desired data is not present then merge
will insert the records.
MERGE INTO managers m
USING directors d ON (m.id = d.id)
WHEN MATCHED THEN
UPDATE SET name = 'TEST'
WHEN NOT MATCHED THEN
INSERT VALUES (d.id, d.name, 0);
 SQL STATEMENT
DML: DATA MANIPULATION LANGUAGE
By default they are auto-commit operation by the DB,
(BEGIN TRANSACTION)

INSERT: INSERT INTO EXAMS VALUES (‘JESSE’ 10);

UPDATE: UPDATE EXAMS SET NAME = ‘PETER’ WHERE

ID = 10;

DELETE: DELETE FROM EXAMS WHERE ID = 10;

 SQL STATEMENT
SETTING IDENTITY COLUMN

SET IDENTITY_INSERT TABLE ON

SET IDENTITY_INSERT TABLE OFF

NOTE: YOU NEED TO USE THE EXPLICIT INSERT

METHOD.
TO RESET THE IDENTITY, YOU SIMPLY TRUNCATE
 TCL
Transaction Control Language.
TCL stands for Transaction Control Language. TCL
includes COMMIT, ROLLBACK and SAVEPOINT.
COMMIT statement will permanently save any open
transactions in the current session to the database. By
transaction, I mean any changes done to any
database table using any of the DML statements like
INSERT, UPDATE, DELETE and MERGE.
ROLLBACK statement will remove (unsave) any
open transactions in the current session to the
database. So all uncommitted transactions in the
current session will be lost.
SAVEPOINT (Save Tran) statement can be used to
create a specific pointer in your session and provide
a name to this pointer. You can then either rollback
or commit transactions only until this point
(savepoint name) rather than committing or
rollbacking all the transaction in the session.
 SQL STATEMENT
TCL: TRANSACTION CONTROL LANGUAGE
o COMMIT
o ROLLBACK
o SAVEPOINT

(BEGIN TRANSACTION)
(SAVE TRANSACTION S1)
 ACID
Atomic- must work as a unit
Consistent- must take the DB to another
valid state
Isolated- must be locked as at when the
changes is made
Durable- must be true
VIEW
View is a database object which is created based
on SQL Query. It’s like giving a name to the
results returned from SQL Query and storing it in
the database as a view.
If the query result changes then the data in view
also changes. View is directly linked to the SQL
Query over which it was created.
CREATE VIEW C_VIEW AS
SELECT * FROM REGISTRATION WHERE
SALARY > 50000;
SYNONYM
Synonym on the other hand is just an alias or an
alternate name that you can provide to any
database objects such as tables, views, sequences,
procedures etc.
Synonym is created for a single database object
whereas view can be created on a query where
the query may have been formed by multiple
tables.
CREATE SYNONYM S1 FOR STUDENT;
SELECT * FROM S1;
RETRIEVING DATA
Retrieving Data Using the SQL Statement

SELECT identifies the columns to be displayed

FROM identifies the table containing those columns

 DEFINING A COLUMN ALIAS

A column alias:
➢ Renames a column heading
➢ Is useful with calculations
➢ Immediately follows the column name
➢ There is an optional AS keyword between the
column name and alias
➢ Requires double quotation marks if it contains
spaces or special characters or case sensitive
CONCATENATION
 CONCATENATION OPERATOR

➢ Links columns or character string to other

columns
➢ Is represented by plus (+)
➢ Is represented using the concat function
➢ Creates a resultant column that is a character
expression
 Arithmetic Expressions

Create expressions with number and date

data by using arithmetic operators
OPERATOR DESCRIPTION
+ ADD
- SUBTRACTION
* MULTIPLY
/ DIVIDE
 OPERATOR PRECEDENCE

Select firstname, salary, 12*salary+200 from employees;

Select firstname, salary, 12*(salary+200) from employees;

BODMAS
 DEFINING A NULL VALUE

➢ NULL is a value that is unavailable,

unassigned, unknown or inapplicable.

➢ NULL is not the same as zero or blank

space

➢ Arithmetic expressions containing a null

value evaluates to null.
SQL SERVER REPLACING NULL

ISNULL (COLNAME,‘NO MANAGER’)

SELECT SALARY, ISNULL(SALERY,5000) AS NEWSAL FROM EMPLOYEES;

COALESCE (COLNAME,‘NO MANAGER’)

SELECT SALARY, COALESCE(FIRSTNAME, LASTNAME, OTHERNAME) AS
NAME FROM EMPLOYEES;
 LITERAL CHARACTER STRINGS

➢ A literal is a character, a number, or a date that is

included in the SELECT statement.
➢ Date and character literal values must be enclosed
within single quotation marks.
➢ Each character string is output once for each row
returned.
Distinct Clause
DISTINCT clause will return unique column values.

Depending on the list of columns you provide In the

DISTINCT clause, it will fetch the unique combination
of values for all those combined columns.

If you provide just a single column in DISTINCT then

it fetches just the unique values in that specific column.
 Duplicate Rows

The default display of queries is all rows, including

duplicate rows.

SELECT department_id FROM employees;

SELECT distinct department_id

FROM employees;
Where Clause
WHERE clause is used to filter records from the table.
We can also specify join conditions between two tables
in the WHERE clause. If SQL query has both WHERE
and GROUP BY clause then the records will first get
filtered based on the conditions mentioned in WHERE
clause before the data gets grouped as per the
GROUP BY clause.
 Restricting and Sorting Data

• Limiting rows with:

– The WHERE clause
– The comparison conditions using =, <=, BETWEEN, IN,
LIKE, and NULL conditions
– Logical conditions using AND, OR, and NOT operators

• Restrict the rows that are returned by using the WHERE

clause:
• The WHERE clause follows the FROM clause.
 Comparison Operators

Operator Meaning
= Equal to

> Greater than

>= Greater than or equal to

< Less than

<= Less than or equal to

<> Not equal to

BETWEEN Between two values (inclusive)

...AND...

IN(set) Match any of a list of values

LIKE Match a character pattern

IS NULL Is a null value
 Pattern Matching Using the LIKE
Operator

• Use the LIKE operator to perform wildcard searches of

valid search string values.
• Search conditions can contain either literal characters or
numbers:
– % denotes zero or many characters.
– _ denotes one character.
 Combining Wildcard Characters

• You can combine the two wildcard characters (%, _)

with literal characters for pattern matching:
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%' ;
 Using the NULL Conditions

Test for nulls with the ISNULL operator.

SELECT last_name, manager_id

FROM employees
WHERE manager_id IS NULL ;
Defining Conditions Using the Logical
Operators

Operator Meaning
AND Returns TRUE if both component conditions
are true
OR Returns TRUE if either component condition
is true
NOT Returns TRUE if the condition is false
 Using the AND Operator

AND requires both the component conditions to be true:

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id LIKE '%MAN%' ;
 Using the OR Operator

OR requires either component condition to be true:

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%' ;
 Using the NOT Operator

SELECT last_name, job_id

FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Logical Operator - NOT

The action of a BETWEEN, IN and LIKE operator can

be negated by preceding it with NOT.

SELECT Branch, Accno, Balance FROM ACC WHERE

Branch NOT LIKE '_insley';

SELECT Branch, Accno, Balance FROM ACC WHERE

Branch NOT IN ('Tinsley', 'Brecks', 'Dore');

SELECT Accno, Balance FROM ACC WHERE Balance

NOT BETWEEN 1 AND 1000;
 Rules of Precedence
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_PRES' 1
AND salary > 15000;

SELECT last_name, job_id, salary

FROM employees 2
WHERE (job_id = 'SA_REP'
OR job_id = 'AD_PRES')
AND salary > 15000;
Order By Clause
 Using the ORDERBY Clause

• Sort retrieved rows with the ORDERBY clause:

– ASC: Ascending order, default
– DESC: Descending order
• The ORDERBY clause comes last in the SELECT
statement:
SELECT last_name, job_id, department_id,
hire_date
FROM employees
ORDER BY hire_date ;
CASE STATEMENT
CASE statement is similar to IF ELSE statement from any other
programming languages. We can use it to fetch or show a
particular value based on certain condition.
For example:
In above example query, we display the gender as ‘Male’
when the gender column in the employee table has value as
‘M’.
And if the gender column has value as ‘F’ then we display the
value as ‘Female’. If the gender column has values anything
other than M or F then the query would return ‘Other’
(which we have defined using the ELSE part in the CASE
statement.)
CASE:
Select FirstName, LastName, DeptID, Age
Case when Age > 20 then ‘Old’ when age>10 then ‘Young’
Else ‘Baby’
End as Remarks
from employees;
Functions
Functions In SQL Server

Functions are used to get computed values and

cannot be use to execute Updates, Insert, Delete
i.e cannot make permanent changes in sql
environment. It can be called from Select, Where,
other Stored Procedure
 SQL Functions
Input Output

Function

arg 1 Function performs

action
arg 2
Result
value

arg n
 Two Types of SQL Functions

Functions

Single-row Multiple-row
functions functions

Return one result Return one result

per row per set of rows
SINGLE ROW FUNCTION
 Single-Row Functions

Single-row functions:
• Manipulate data items
• Accept arguments and return one value
• Act on each row that is returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments that can be a column or an
expression
Single Row Function

UPPER()…….Converts all character to upper

case

LOWER()……Converts all character to lower

case

LTRIM()……Removes blanks on the left side

of the character expression.
RTRIM()……Removes blanks on the right side
of the character expression.

REVERSE()…..Reverse all the character in a

string

SUBSTRING()…..Give a sub string from the

original string
TRIM()……Removes blanks on the right and
left side of the character expression.

REPLACE()…..Replace a character in a string

with another

Format(col, ‘N0’ or ‘C0’)…Insert comma

separator/currency
Quiz:
slice out hiit plc. What numbers will you use?
select substring('i am a student
of hiit plc’,_,_) as Sentence;

a. 8, 9
b. 18, 6
c. 19, 8
d. 19, 7
STRING FUNCTION
SQL Server String Functions
❑ASCII Returns the ASCII value for the specific character
❑CHAR Returns the character based on the ASCII code
❑CHARINDEX Returns the position of a substring in a
string
❑CONCAT Adds two or more strings together
❑CONCAT_WS Adds two or more strings together with
a separator
❑DATALENGTH Returns the number of bytes used to
represent an expression
❑DIFFERENCE Compares two SOUNDEX values, and
returns an integer value
❑LEFT Extracts a number of characters from a string
(starting from left)
❑REPLACE Replaces all occurrences of a substring
within a string, with a new substring
❑LEN Returns the length of a string
❑NCHAR Returns the Unicode character based on the
number code
❑PATINDEX Returns the position of a pattern in a string
❑QUOTENAME Returns a Unicode string with
delimiters added to make the string a valid SQL Server
delimited identifier
❑REPLICATE Repeats a string a specified number of
times
❑RIGHT Extracts a number of characters from a string
(starting from right)
❑SOUNDEX Returns a four-character code to evaluate
the similarity of two strings
❑UNICODE Returns the Unicode value for the first
character of the input expression
❑SPACE Returns a string of the specified number of space
characters
❑STR Returns a number as string
❑STUFF Deletes a part of a string and then inserts
another part into the string, starting at a specified
position
MATHS FUNCTION
SQL Server Math/Numeric Functions
❑ABS Returns the absolute value of a number
❑ACOS Returns the arc cosine of a number
❑ASIN Returns the arc sine of a number
❑ATAN Returns the arc tangent of a number
❑ATN2 Returns the arc tangent of two numbers
❑AVG Returns the average value of an expression
❑CEILING Returns the smallest integer value that is >=
a number
❑COUNT Returns the number of records returned by a
select query
❑COS Returns the cosine of a number
❑COT Returns the cotangent of a number
❑DEGREES Converts a value in radians to degrees
❑EXP Returns e raised to the power of a specified
number
❑FLOOR Returns the largest integer value that is <= to a
number
❑LOG Returns the natural logarithm of a number, or the
logarithm of a number to a specified base
❑LOG10 Returns the natural logarithm of a number to
base 10
❑MAX Returns the maximum value in a set of values
❑MIN Returns the minimum value in a set of values
❑PI Returns the value of PI
❑POWER Returns the value of a number raised to the
power of another number
❑RADIANS Converts a degree value into radians
❑RAND Returns a random number
❑ROUND Rounds a number to a specified number of
decimal places
❑SIGN Returns the sign of a number
❑SIN Returns the sine of a number
❑SQRTReturns the square root of a number
❑SQUARE Returns the square of a number
❑SUM Calculates the sum of a set of values
DATE FUNCTION
Date Functions
❑CURRENT_TIMESTAMP Returns the current date and
time
❑DATEADD Adds a time/date interval to a date and then
returns the date
❑DATEDIFF Returns the difference between two dates
❑DATEFROMPARTS Returns a date from the specified
parts (year, month, and day values)
❑DATENAME Returns a specified part of a date (as string)
❑DATEPART Returns a specified part of a date (as
integer)
❑DAY Returns the day of the month for a specified date
❑GETDATE Returns the current database system date and
time
❑GETUTCDATE Returns the current database system
UTC date and time
❑SYSDATETIME Returns the date and time of the SQL
Server
❑YEAR Returns the year part for a specified date
ADVANCED FUNCTION
SQL Server Advanced Functions
❑CAST Converts a value (of any type) into a specified
datatype
❑COALESCE Returns the first non-null value in a list
❑CONVERT Converts a value (of any type) into a
specified datatype
❑CURRENT_USER Returns the name of the current user
in the SQL Server database
❑IIF Returns a value if a condition is TRUE, or another
value if a condition is FALSE
❑ISNULL Return a specified value if the expression is
NULL, otherwise return the expression
❑ISNUMERIC Tests whether an expression is numeric
❑NULLIF Returns NULL if two expressions are equal
❑SESSION_USER Returns the name of the current user
in the SQL Server database
❑SESSIONPROPERTY Returns the session settings for a
specified option
❑SYSTEM_USER Returns the login name for the current
user
❑USER_NAME Returns the database user name based
on the specified id
GROUP FUNCTION
 Multiple Row/Aggregate FUNCTIONS

▪ COUNT( )
▪ SUM( )
▪ AVG( )
▪ MAX( )
▪ MIN( )
▪ DISTINCT
 What Are Group Functions?

Group functions operate on sets of rows to give one result

per group.
NESTING/SUB FUNCTION
Nesting Function
This is simply a function inside another function, it
is solved from the deepest level to the least deep
level
 Nesting Functions

• Single-row functions can be nested to any level.

• Nested functions are evaluated from the deepest level to
the least deep level.

F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
 Nesting Functions

SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employees
WHERE department_id = 60;
GROUP BY CLAUSE
GROUP BY clause will group together the data based
on the columns specified in group by, Which will
then return just one record for each unique value in
the column specified in group by.
In other words GROUP BY can also be used to fetch
unique records from a table.
• Grouping rows:
– GROUPBY clause
– HAVING clause Creating Groups of Data

EMPLOYEES

4400
Average salary in
EMPLOYEES table for
9500
each department

3500

6400

10033

…
The main purpose of group by clause is to perform some
aggregation (using the aggregate functions like MIN, MAX,
COUNT, SUM, AVG) based on the grouped by column
values.
 Using the GROUPBY Clause

The GROUPBY column does not have to be in the SELECT

list.

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
 Using the GROUPBY Clause
on Multiple Columns
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id
ORDER BY department_id;
HAVING CLAUSE
HAVING clause is used to filter records returned from
the GROUP BY clause. So if SQL query has WHERE,
GROUP BY and HAVING clause then first the data gets
filtered based on WHERE condition, only after this
grouping of data takes place. Finally based on the
conditions in HAVING clause the grouped data
again gets filtered.
 Using the HAVING Clause

SELECT department_id, MAX(salary)

FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
 The HAVING clause

SELECT job, SELECT job,

COUNT(*) AS employees, COUNT(*) AS employees,
SUM(sal) SUM(sal)
FROM emp FROM emp
WHERE job != 'PRESIDENT' WHERE job != 'PRESIDENT'
GROUP BY job GROUP BY job
HAVING COUNT(*)<4

See the difference?

❑ HAVING keeps the results that satisfy the
criteria.
❑ There is a difference between having and
WHERE.
SUB-QUERY
 Using a Subquery to Solve a Problem

Who has a salary greater than Abel’s?

Main query:

Which employees have salaries greater than Abel’s

salary?

Subquery:

What is Abel’s salary?

 SQL SERVER SUB-QUERY

The subquery (inner query) executes

before the main query (outer query)

The result of the subquery is used by the

main query
 Guidelines for Using Subqueries

• Enclose subqueries in parentheses.

• Place subqueries on the right side of the comparison
condition for readability (However, the subquery can
appear on either side of the comparison operator.).
• Use single-row operators with single-row subqueries
and multiple-row operators with multiple-row subqueries.
 SUB-QUERY

Select select_list from table where expr

operator
(select select_list from table);
 SUB-QUERY

Select employeeid, jobtitle, salary from

employeesalary where employeeid in
(select employeeid from employeedemo
where age>40);
 Executing Single-Row Subqueries

SELECT last_name, job_id, salary

FROM employees
WHERE job_id = SA_REP
(SELECT job_id
FROM employees
WHERE last_name = ‘Taylor’)
AND salary > 8600
(SELECT salary
FROM employees
WHERE last_name = ‘Taylor’);
 ENTITY
RELATIONSHIP
DIAGRAM
(ERD)
ERDs are diagrams of your database, which
visualize the structure of your tables and how
they are related.
Most importantly they provide a powerful
visualization of which tables are related to each
other and how
RELATIONSHIP
 RELATIONSHIP IN SQL SERVER

1. ONE-TO-ONE

2. ONE-TO-MANY

3. MANY-TO-MANY
 CREATE TABLE STATEMENT-- DDL
PRIMARY KEY CONSTRAINT

Create table student (regno int primary

key, name nvarchar(40), course
nvarchar(20), gender nvarchar(8));
 PRIMARY KEY CONSTRAINT

Create table parent (parent_id int primary

key, pname nvarchar(40), occupation
nvarchar(20), address nvarchar(60), gsm
varchar(15), gender nvarchar(8));
 FOREIGN KEY CONSTRAINT

Create table student (regno int primary key,

name nvarchar(40), course nvarchar(20),
gender nvarchar(8), parent_id int foreign
key references parent(parent_id));
ADDING FOREIGN KEY CONSTRAINT TO
EXISTING TABLES

ALTER TABLE EMPLOYEES ADD REGNO

INT CONSTRAINT FK_1 FOREIGN KEY
REFERENCES REGISTRATION(col);
JOINS
 JOINS

✓ Use a join to query data from more

than one table.
✓ Prefix the column name with the table
name when the same column name
appears in more than one table.
TYPES OF JOINS
 SQL SERVER INNER JOINS

Select * from school inner join parent on

school.reg_id = parent.reg_id;

This joins all records from the tables where

they have matching column.
 SQL SERVER LEFT OUTER JOINS

Select * from school left outer join parent

on school.reg_id = parent.reg_id;

This joins all records from the tables where

they have matching and un-matching
column, but ignores the un-matching
column from the right side table
 SQL SERVER RIGTH OUTER JOINS

Select * from school right outer join parent

on school.reg_id = parent.reg_id;

This joins all records from the tables where

they have matching and un-matching
column, but ignores the un-matching
column from the left side table
 SQL SERVER FULL OUTER JOINS

Select * from school full outer join parent

on school.reg_id = parent.reg_id;

This joins all records from the tables

where they have matching and un-
matching columns.
 SQL SERVER CROSS JOINS

Select * from school cross join parent;

This multiplies all records from the tables

where they have matching and un-matching
columns.
UNION
 UNION
This is used to combine tables together
without a joining column (PK & FK) with
an equal number of expression in the
target/select list.
 TYPES OF UNION
✓ UNION---Removes duplicates
✓ UNION ALL---Allows duplicates
✓ INTERSECT---Displays common records
✓ EXCEPT---Displays records not in both
tables
 UNION

Select * from employees

union
select * from department
 UNION ALL

Select * from employees

Union all
select * from department
 INTERSECT

Select * from employees

intersect
select * from department
 INTERSECT ALL

Select * from employees

Intersect All
select * from department
 EXCEPT

Select * from employees

except
select * from department
 EXCEPT ALL

Select * from employees

Except All
select * from department
How To Fix SQL Database
Restore Failed, Admin Issues
How To Fix SQL Database Restore Failed, Database Is in Use Error

As mentioned above, this error can occur if there are any active connections to the
database. So, you can close any existing connections to the database to fix the issue.
Here are the steps:
•Open SQL Server Management Studio (SSMS).
•In Object Explorer, right-click the Databases option and then click Restore Database.
•In the Restore Database window, go to the Options page and check the Close
existing connections to destination database option.
Un-Installing SQL Server

Uninstall the Mgt Studio, Azure Data Studio, Visual Studio, Ms MPI, ODBC, OLE

Access Registry Editor: Press Win + R to open the Run dialog, then type regedit and
press Enter to open Registry Editor

Navigate to Uninstall Keys: In Registry Editor, navigate to the following path:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\
sql server

Delete SQL Server Sub-keys: Under the Uninstall key, locate and delete all sub-keys
referencing SQL Server

Delete SQL Server Folders: Manually remove or rename all SQL Server folders on the
computer, typically located in C:\Program Files (x86)\Microsoft SQL Server

Perform Registry Cleanup: Use %temp%, temp, prefetch. To clean the registry

Reboot: Restart your computer to apply the changes and complete the uninstallation
process
End of Module
End Of Course
 Q&A
Mr. Festus Peter
(Instructor HKC)