SQL

for Beginners
(Telugu)
FULL COURSE CSE & IT Tutorial 4u
What’s in this video for
you? SQL Topics
1. Introduction to Database Theory
2. Introduction to SQL
3. Creating database & Tables in SQl
4. Basic Operations in SQL
5. SQL Built-in Functions
6. Aggregate & String Functions
7. Window Functions
8. Types of Joins
9. Triggers & Cursors
10. Procedures & Functions
11. Temporary Tables, SubQueries
12. Views CSE & IT Tutorial 4u
01 Introduction To Database
Theory

What is Database?

What is Data?

What is DBMS?

What is RDBMS?

CSE & IT Tutorial 4u

 What is Data?

Computer data is
information stored by a
computer. This information
may be in the form of text
documents, images, audio
clips, software programs, or
other types of data.

CSE & IT Tutorial 4u

 What is
Database?

A database is an
organized collection
of structured
information (data),
typically stored
electronically in a
computer system.
CSE & IT Tutorial 4u
Popular
Database

CSE & IT Tutorial 4u

What is Database Management System(DBMS)?

Database Management
System (DBMS) is a
software for storing
and retrieving users’
data

CSE & IT Tutorial 4u

What is RDBMS?

RDBMS is the software that

executes queries on the
related data, including adding,
updating, and searching for
values.

CSE & IT Tutorial 4u

Introductio
n to
SQL
 SQL- Structured Query Language

Structured Query Language is a

standard Database language
which is used to create, maintain
and retrieve the relational
database.

CSE & IT Tutorial 4u

 SQL- Structured Query Language

DDL (Data Definition Language):

The data definition language deals with the schema creation and
modification Ex: CREATE TABLE and ALTER TABLE

DML (Data Manipulation Language):

The data manipulation language provides the constructs to query data
Ex : SELECT statement and INSERT, UPDATE, and DELETE statements.

DCL (Data Control Language):

The data control language consists of the statements that deal with the
user authorization and security.
Ex: GRANT and REVOKE statements.
CSE & IT Tutorial 4u
 SQL- Structured Query Language

SQL Dialects:

Most popular dialects of SQL:

1. PL/SQL stands for procedural language/SQL developed by Oracle.

2. Transact-SQL or T-SQL is developed by Microsoft for Microsoft SQL
Server.
3. PL/pgSQL stands for Procedural Language/PostgreSQL that consists
of SQL dialect and extensions implemented in PostgreSQL
4. MySQL has its own procedural language since version 5. Note that MySQL
was acquired by Oracle.

CSE & IT Tutorial 4u

Creating
Database &
Table in SQL
 Creating Database & Table

Creating Database:

Syntax:

CREATE DATABASE
database_name;

Example:

CREATE DATABASE Test;

CSE & IT Tutorial 4u
 Deleting Database

Delete or Drop Database:

Syntax:

DROP DATABASE

database_name; Example:

DROP DATABASE Test;

CSE & IT Tutorial 4u

 Backup
Database
Backup Database:

Syntax:

BACKUP DATABASE databasename

TO DISK = 'file path';

Example:

BACKUP DATABASE testDB

TO DISK = 'D:\backups\testDB.bak';
CSE & IT Tutorial 4u
 Creating table
Syntax:
CREATE TABLE table_name
( column1 datatype,
column2 datatype,
column3 datatype,
);
Example:
CREATE TABLE Persons
( PersonID int,
LastName
varchar(255),
FirstName
varchar(255), Address CSE & IT Tutorial 4u
 Deleting Table

Delete or Drop Table:

Syntax:

DROP TABLE

table_name; Example:

DROP TABLE Test;

CSE & IT Tutorial 4u

 TRUNCATE Table

Truncate Table:

Syntax:

TRUNCATE TABLE

table_name; Example:

TRUNCATE TABLE Test;

CSE & IT Tutorial 4u

 ALTER
Table
ALTER TABLE - ADD Column
Syntax:
ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Customers

ADD Email varchar(255);

CSE & IT Tutorial 4u

 ALTER
Table
ALTER TABLE - DROP Column
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE Customers

DROP COLUMN Email;

CSE & IT Tutorial 4u

 INSERT INTO QUERY

The INSERT INTO statement is used to insert new records in a table.

Insert into Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
INSERT INTO table_name VALUES (value1, value2, value3, ...);

Insert into Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
Basic
Operation
s in SQL
 Basic Operations in SQL

1. Like
1. Select 2. In
2. Update 3. Between
3. Delete 4. Alias
4. Where 5. Wildcards
5. Null Values 6. Union
6. AND , OR, NOT 7. Exists
7. ORDER BY 8. Any,All
8. GROUP BY 9. Select into
9. Insert Into 10. Insert into select
10. HAVING
CSE & IT Tutorial 4u
 DATA BASE
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. Berlin 12209 Germany

57

2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico

Emparedados y D.F.
helados Constitució
n 2222
3 Antonio Moreno Antonio Moreno Mataderos México 05023 Mexico
Taquería 2312 D.F.

4 Around the Horn Thomas Hardy 120 London WA1 1DP UK

Hanover
Sq.

5 Berglunds snabbköp Christina Berglund Berguvsväg Luleå S-958 22 Sweden

en 8
 SELECT
QUERY
The SELECT statement is used to select data from a database.

Two Types of SELECT’s

1. SELECT: The SELECT statement is used to select data from

a database.

2. SELECT DISTINCT : The SELECT DISTINCT statement is used to

return only distinct (different) values.

CSE & IT Tutorial 4u

 SELECT
QUERY
SELECT Syntax

i. SELECT column1, column2, ... FROM table_name;

ii. SELECT * FROM table_name;

SELECT EXAMPLE:
1. SELECT CustomerName, City FROM Customers;
2. SELECT * FROM Customers;

CSE & IT Tutorial 4u

 SELECT DISTINCT QUERY

SELECT Syntax

i. SELECT DISTINCT column1, column2, ...

FROM table_name;
ii. SELECT COUNT(DISTINCT Column)* FROM table_name;

SELECT EXAMPLE:
1. SELECT DISTINCT Country FROM Customers;
2. SELECT COUNT(DISTINCT Country)* FROM table_name;

CSE & IT Tutorial 4u

 UPDATE
QUERY
The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

UPDATE
EXAMPLE: SET ContactName='Juan' WHERE Country='Mexico';
UPDATE Customers
CSE & IT Tutorial 4u
 DELETE
QUERY
The DELETE statement is used to delete existing records in a
table.

DELETE Syntax
DELETE FROM table_name WHERE condition;
DELETE EXAMPLE:

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

CSE & IT Tutorial 4u

 WHERE CLAUSE QUERY

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified
condition.
WHERE Syntax

SELECT column1, column2, … FROM table_name WHERE condition;

DELETE EXAMPLE:

SELECT * FROM Customers WHERE Country='Mexico';

CSE & IT Tutorial 4u
 NULL VALUES QUERY
A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert
a new record or update a record without adding a value to this field. Then, the field will be saved
with a NULL value.

IS NULL
SELECT column_names FROM table_name WHERE column_name IS NULL;

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;

IS NOT NULL

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;
 AND QUERY
AND Syntax

SELECT column1, column2, … FROM table_name WHERE condition1 AND

condition2 AND condition3 ...;

AND Example:

SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';

 OR
QUERY

OR Syntax
SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3
...;

OR Example:

SELECT * FROM Customers WHERE City='Berlin' OR City='Mü nchen';

 NOT QUERY
NOT Syntax

SELECT column1, column2, … FROM table_name WHERE NOT condition;

NOT Example:

SELECT * FROM Customers WHERE NOT Country='Germany';

 ORDER BY QUERY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the
records in descending order, use the DESC keyword.

ORDER BY Syntax

SELECT column1, column2, … FROM table_name ORDER BY column1, column2, ... ASC|DESC;

ORDER BY Example
SELECT * FROM Customers ORDER BY Country;
 GROUP BY
QUERY
The GROUP BY statement groups rows that have the same values into summary rows, like
"find the number of customers in each country".

GROUP BY Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)

ORDER BY column_name(s);

ORDER BY Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
 HAVING QUERY

The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.

HAVING Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING

condition ORDER BY column_name(s);

HAVING Example

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING

COUNT(CustomerID) > 5;
 LIKE QUERY

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

● The percent sign (%) represents zero, one, or multiple characters

● The underscore sign (_) represents one, single character

LIKE Syntax

SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;

Example

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers WHERE CustomerName LIKE '%a';

LIKE QUERY
 IN
QUERY
The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

IN Operator Examples
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
 BETWEEN QUERY

The BETWEEN operator selects values within a given range. The values can be numbers, text, or
dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND

value2;

BETWEEN Example
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
 ALIAS
QUERY
SQL aliases are used to give a table, or a column in a table, a temporary
name.Aliases are often used to make column names more readable.

Alias Column Syntax

SELECT column_name AS alias_name FROM table_name;

Alias Table Syntax

SELECT column_name(s) FROM table_name AS alias_name;

Example:

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

 WILDCARD QUERY
A wildcard character is used to substitute one or more characters in a string. Wildcard characters
are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a
specified pattern in a column.
 WILDCARD QUERY

SELECT * FROM Customers WHERE City LIKE 'ber%';

 UNION
QUERY
The UNION operator is used to combine the result-set of two or more SELECT statements.

UNION Syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Example
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
 ExistsQUERY
The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax

SELECT column_name(s) FROM table_name WHERE EXISTS

(SELECT column_name FROM table_name WHERE condition);

Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products

WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

 Any QUERY
The ANY operator:

● returns a boolean value as a result

● returns TRUE if ANY of the subquery values meet the condition

ANY Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name
FROM table_name WHERE condition);

ANY Example

SELECT ProductName FROM Products WHERE ProductID = ANY

(SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

 ALL QUERY
The ALL operator:

● returns a boolean value as a result

● returns TRUE if ALL of the subquery values meet the condition
● is used with SELECT, WHERE and HAVING statements

ALL Syntax With SELECT

SELECT ALL column_name(s) FROM table_name WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE
condition);

ALL EXAMPLE:

SELECT ALL ProductName FROM Products WHERE TRUE;

● SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10)
 Numeric
Function
s
 Built-in functions in SQL
● ABS
● POWER ● SQRT
● ROUND ● SQUARE
● SIN ● EXP
● COS ● LN
● TRUNC ● LOG
● TAN ● CEILING
● ASIN ● FLOOR
● ACOS ● SIGN
● ATAN

CSE & IT Tutorial 4u

 ABS QUERY
The ABS() function returns the absolute value of a number.

Syntax
ABS(number)

Example

Find the absolute value of a number:

SELECT Abs(-243.5) AS AbsNum;

 POWER
QUERY
The POWER() function returns the value of a number raised to the power of
another number.

Syntax
POWER(a, b)
Example
Return 4 raised to the second
power: SELECT POWER(4, 2);
 ROUND
QUERY
The ROUND() function rounds a number to a specified number of decimal places.

Syntax
ROUND(number, decimals, operation)

Example
Round the number to 2 decimal places:
SELECT ROUND(235.415, 2) AS RoundValue;
 SIN QUERY
The SIN() function returns the sine of a number.

Syntax
SIN(number)

Example

Return the sine of a

number: SELECT SIN(2);

 COS QUERY

The COS() function returns the cosine of a number.

Syntax
COS(number)
Example
Return the cosine of a
number: SELECT COS(2);
 TRUNCATE QUERY

TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

The following SQL truncates the table "Categories":

Exampl
e
TRUNCATE TABLE Categories;
 TAN QUERY
The TAN() function returns the tangent of a number.

Syntax
TAN(number)
Example
Return the tangent of a
number: SELECT TAN(1.75);
 SQRT QUERY

The SQRT() function returns the square root of a number.

Syntax
SQRT(number)
Example
Return the square root of a
number: SELECT SQRT(64);
 SQUARE QUERY
The SQUARE() function returns the square of a number.

Syntax
SQUARE(number)

EXAMPLE
Return the square of a
number: SELECT
SQUARE(64);
 EXP QUERY
The EXP() function returns e raised to the power of a specified number.

The constant e (2.718281...), is the base of natural logarithms.

Syntax
EXP(number)

Example
Return e raised to the power of
1: SELECT EXP(1);
 LN
QUERY
LN() function

SQL LN() function returns the natural logarithm of n, where n is greater than 0 and its base
is number equal to approximately 2.71828183.

Syntax:

LN(expression)

Exampl
SELECT
e LN(65) "natural_log of 65"
 LOG QUERY
The LOG() function returns the natural logarithm of a specified number, or the logarithm of the
number to the specified base.

Syntax
LOG(number, base) -- Syntax for SQL Server

OR:

LOG(number) -- Syntax for Azure SQL Database, Azure SQL Data Warehouse, Parallel Data
Warehouse
Example

Return the natural logarithm of

2: SELECT LOG(2);
 CEILING QUERY
The CEILING() function returns the smallest integer value that is larger than or equal to a
number.

Syntax
CEILING(number)

Example
Return the smallest integer value that is greater than or equal to a
number: SELECT CEILING(25.75) AS CeilValue;
 FLOOR
QUERY
The FLOOR() function returns the largest integer value that is smaller than or equal to a
number.

Tip: Also look at the CEILING() and ROUND() functions.

Syntax
FLOOR(number)

Example

Return the largest integer value that is equal to or less than

25.75: SELECT FLOOR(25.75) AS FloorValue;

 SIGN QUERY
The SIGN() function returns the sign of a number.

This function will return one of the following:

● If number > 0, it returns 1

● If number = 0, it returns 0
● If number < 0, it returns -1

Syntax
SIGN(number)

Example
Return the sign of a

number: SELECT
 SQL
AGGREGATE
Function
s
 Aggregation functions in SQL

● Min
● Max
● Averag
e
● Count
● Sum

CSE & IT Tutorial 4u

 MIN QUERY
The MIN() function returns the smallest value of the selected column.
MIN() Syntax

SELECT MIN(column_name) FROM table_name WHERE condition;

MIN() Example

SELECT MIN(Price) AS SmallestPrice FROM Products;

 MAX QUERY

The MAX() function returns the largest value of the selected

column. MAX() Syntax

SELECT MAX(column_name) FROM table_name WHERE condition;

MAX() Example

SELECT MAX(Price) AS LargestPrice FROM Products;

 AVERAGE QUERY

The AVG() function returns the average value of a numeric column.

AVG() Syntax

SELECT AVG(column_name) FROM table_name WHERE condition;

AVG() Example

SELECT AVG(Price) FROM Products;

 COUNT
QUERY
The COUNT() function returns the number of rows that matches a specified criterion.

COUNT() Syntax

SELECT COUNT(column_name) FROM table_name WHERE condition;

COUNT() Example

SELECT COUNT(ProductID) FROM Products;

 SUM QUERY

The SUM() function returns the total sum of a numeric column.

SUM() Syntax

SELECT SUM(column_name) FROM table_name WHERE condition;

SUM() Example

SELECT SUM(Quantity) FROM OrderDetails;

 String
Function
s
 String functions in SQL

● INITCAP
● LOWER ● LENGTH
● UPPER ● INSTR
● CONCAT
● LPAD
● RPAD
● LTRIM
● RTRIM
● REPLACE
● SUBSTR

CSE & IT Tutorial 4u

 INITCAP
QUERY
SQL INITCAP() Function return capitalize string/char (capitalize first letter of each word).

Syntax

INITCAP(string)

Example
Consider following example return the capitalize string of given string
argument.
SQL> SELECT INITCAP('opal kole') "INITCAP" FROM DUAL;

INITCAP

Opal Kole
 LOWER
QUERY
The LOWER() function converts a string to lower-case.

Syntax
LOWER(text)

Convert the text to lower-case:

SELECT LOWER('SQL Tutorial is FUN!');

 UPPER
QUERY
The UPPER() function converts a string to upper-case.

Syntax
UPPER(text)

Example
Convert the text to upper-case:
SELECT UPPER('SQL Tutorial is FUN!');
 CONCAT QUERY

The CONCAT() function adds two or more strings together.

Syntax
CONCAT(string1, string2, ...., string_n)

Example
Add two strings together:

SELECT CONCAT('google', '.com');

Output : google.com
 LTRIM
QUERY
The Note LTRIM() function removes leading spaces from a string.

Syntax
LTRIM(string)

Example
Remove leading spaces from a string:
SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString;
 RTRIM
QUERY
The RTRIM() function removes trailing spaces from a string.

Syntax
RTRIM(string)

Example
Remove trailing spaces from a
string: SELECT RTRIM("SQL ") AS RightTrimmedString;
Tutorial
 REPLACE QUERY

The REPLACE() function replaces all occurrences of a substring within a string, with a
new substring.

Note: This function performs a case-sensitive replacement.

Syntax
REPLACE(string, from_string, new_string)
Example
Replace "SQL" with "HTML":
SELECT REPLACE("SQL Tutorial", "SQL", "HTML");
 SUBSTR
QUERY
The SUBSTR() function extracts a substring from a string (starting at any position).

Syntax
SUBSTR(string, start, length)

OR
SUBSTR(string FROM start FOR length)

Example
Extract a substring from a string (start at position 5, extract 3

characters): SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString;

 LENGTH
QUERY
The LENGTH() function returns the length of a string (in bytes).

Syntax
LENGTH(string)

Example
Return the length of the string, in bytes:
SELECT LENGTH("SQL Tutorial") AS LengthOfString;
 INSTR
QUERY
The INSTR() function returns the position of the first occurrence of a string in another
string.

This function performs a case-insensitive search.

Syntax
INSTR(string1, string2)

Example
Search for "3" in string "W3Schools.com", and return
position: SELECT INSTR(“google.com", "g") AS
Window
Function
s
 Window functions in SQL

● OVER
● COUNT
● SUM
● ROW-NUMBER
● RANK
● DENSE-RANK
● LEAD
● LAG

CSE & IT Tutorial 4u

 OVER QUERY

The OVER clause is used to determine which rows from the query are applied to the function,
what order they are evaluated in by that function

The syntax of the OVER clause is:

<function> OVER ( [PARTITION BY clause]

[ORDER BY clause]
[ROWS or RANGE clause])

Example:

select Object_id , = min(object_id) over() , [max] = max(object_id) over()

[min]
from
sys.objects
 COUNT
QUERY
The COUNT() function returns the number of records returned by a select query.

Note: NULL values are not counted.

Syntax
COUNT(expression)

Example
Return the number of products in the "Products" table:
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
 SUM QUERY

The SUM() function calculates the sum of a set of values.

Note: NULL values are ignored.

Syntax
SUM(expression)

Example
Return the sum of the "Quantity" field in the "OrderDetails" table:
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
 ROW NUMBER QUERY

SQL statement shows the equivalent example using ROWNUM ():

Example

SELECT * FROM Customers

WHERE ROWNUM <= 3;

 RANKQUERY

The RANK() function is a window function that assigns a rank to each row within a partition of a
result set.

Syntax
RANK() OVER ( [PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ... )

Example
SELECT v,
RANK ()
OVER ( ORDER BY v )
rank_no FROM sales.rank_demo;
 DENSE RANK
QUERY
The DENSE_RANK() is a window function that assigns a rank to each row within a partition of
a result set. Unlike the RANK() function, the DENSE_RANK() function returns consecutive rank
values. Rows in each partition receive the same ranks if they have the same values.

Syntax of the DENSE_RANK() function

DENSE_RANK() OVER ([PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ... )
Example:
CREATE TABLE sales.dense_rank_demo ( v VARCHAR(10) ); INSERT INTO
sales.dense_rank_demo(v) VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');
SELECT * FROM sales.dense_rank_demo;
 LEAD QUERY

LEAD() is a window function that provides access to a row at a specified physical offset
which follows the current row. By using the LEAD() function, from the current row, you can
access data of the next row, or the row after the next row, and so on.

Syntax:
LEAD(return_value ,offset [,default])

OVER ( [PARTITION BY partition_expression, ... ]

ORDER BY sort_expression [ASC | DESC], ... )

Example:
SELECT month, net_sales, LEAD(net_sales,1) OVER ( ORDER BY month ) next_month_sale
 LAG QUERY

LAG() is a window function that provides access to a row at a specified physical offset which
comes before the current row.

By using the LAG() function, from the current row, you can access data of the previous row, or the
row before the previous row, and so on.

Syntax:
LAG(return_value ,offset [,default])
OVER ( [PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ... )

Example:
SELECT month, brand_name, net_sales, LAG(net_sales,1) OVER ( PARTITION BY brand_name
ORDER BY month ) previous_sales FROM sales.vw_netsales_brands WHERE year = 2018
Types
of Joins
 Joins

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is
performed when two or more tables are combined in a SQL statement.

There are 4 different types of SQL joins:

● SQL INNER JOIN (sometimes called simple join)
● SQL LEFT OUTER JOIN (sometimes called LEFT JOIN)
● SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN)
● SQL FULL OUTER JOIN (sometimes called FULL JOIN)

CSE & IT Tutorial 4u

 Joins

SQL INNER JOIN (simple join)

It is the most common type of SQL join. SQL INNER JOINS return all rows from multiple
tables where the join condition is met.

Syntax
The syntax for the INNER JOIN in SQL is:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column =
table2.column;
CSE & IT Tutorial 4u
 Joins

SQL LEFT OUTER JOIN

Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows
from the LEFT-hand table specified in the ON condition and only those rows from the
other table where the joined fields are equal (join condition is met).

Syntax:
The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column =
CSE & IT Tutorial 4u
table2.column;
 Joins

SQL RIGHT OUTER JOIN

Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all
rows from the RIGHT-hand table specified in the ON condition and only those rows
from the other table where the joined fields are equal (join condition is met).

Syntax
The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column =
CSE & IT Tutorial 4u
table2.column;
 Joins

SQL FULL OUTER JOIN

Another type of join is called a SQL FULL OUTER JOIN. This type of join returns
all rows from the LEFT-hand table and RIGHT-hand table with NULL values in
place where the join condition is not met.
Syntax
The syntax for the SQL FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = CSE & IT Tutorial 4u
Temporary
Tables,
SubQueries
 Temporary
Tables
Temporary tables are stored in tempdb. They work like a regular table in that
you can perform the operations select, insert and delete as for a regular table.
They are specified with the prefix #, for example
#table_name There are 2 Types of Temporary Tables:
1 . Local Temporary Tables
2. Global Temporary Tables

CSE & IT Tutorial 4u

 Local Temporary
Table
Local Temporary tables :
Local temporary tables are the tables stored in tempdb. Local temporary tables
are temporary tables that are available only to the session that created them.
They are with the prefix
specified CREATE PROCEDURE sp_create_tempt

# Syntax AS

CREATE TABLE #temp1

(c1 INT);

create table
#table_name CREATE PROCEDURE sp_use_tempt
( AS

column_name BEGIN

varchar(20), EXEC sp_create_tempt

column_no int SELECT * FROM ##temp1

) END
CSE & IT Tutorial 4u
 Global Temporary Table

Global Temporary Table:

Global temporary tables are also stored in tempdb. Global temporary tables are
temporary tables that are available to all sessions and all users. They are
specified with the prefix #, for example ##table_name.
Syntax
create table ##table_name
CREATE TABLE ##temp1
( (c1 INT);
INSERT INTO ##temp1
VALUES (1);
column_name
varchar(20),
column_no int
)
CSE & IT Tutorial 4u
 Sub Queries

A subquery is a SQL query nested inside a larger query.

● A subquery may occur in : SELECT, FROM, WHERE Clause

● The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE

statement or inside another subquery.
● A subquery is usually added within the WHERE Clause of another SQL SELECT
statement.
● You can use the comparison operators, such as >, <, or =. The comparison
operator can also be a multiple-row operator, such as IN, ANY, or ALL.

CSE & IT Tutorial 4u

 Sub Queries

Type of Subqueries
● Single row subquery : Returns zero or one row.
● Multiple row subquery : Returns one or more rows.
● Multiple column subqueries : Returns one or more columns.
● Correlated subqueries : Reference one or more columns in the outer SQL
statement. The subquery is known as a correlated subquery because the subquery
is related to the outer SQL statement.
● Nested subqueries : Subqueries are placed within another subquery.

CSE & IT Tutorial 4u

View
s
 Creating a View

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in
a real table in the database. We can create a view by selecting fields from one or more
tables present in the database. A View can either have all the rows of a table or specific
rows based on certain condition.

Syntax:

CREATE VIEW view_name AS Example

SELECT column1, column2..... CREATE VIEW [Brazil Customers]
FROM table_name AS SELECT CustomerName,
WHERE condition; ContactName FROM Customers
WHERE Country = 'Brazil';
view_name: Name for the View
table_name: Name of the table
condition: Condition to select CSE & IT Tutorial 4u
 Deleting a View

We have learned about creating a View, but what if a created View is not needed any
more? Obviously we will want to delete it. SQL allows us to delete an existing View.
We can delete or drop a View using the DROP statement.

Syntax:
DROP VIEW view_name;

Example
DROP VIEW [Brazil Customers];

CSE & IT Tutorial 4u

 Update a View

There are certain conditions needed to be satisfied to update a view. If any one of
these conditions is not met, then we will not be allowed to update the view.

Syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, ..
FROM table_name
WHERE condition;
Example
CREATE OR REPLACE VIEW [Brazil Customers]
AS SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
CSE & IT Tutorial 4u
Trigger
s &
Cursors
 Triggers &
Cursors
Triggers :
A trigger is a block of code that is executed automatically from a database statement.
Triggers is generally executed for DML statements such as INSERT, UPDATE or DELETE.
SYNTAX :

CREATE OR REPLACE TRIGGER <trigger-name>

[BEFORE/AFTER] [INSERT/UPDATE/DELETE]
OF<column-name>
ON<table-name>
[REFERENCING OLD AS O NEW AS N]
[FOR EACH ROW]
WHEN <trigger-condition>
DECLARE
BEGIN
<sql-
END;
statement>
CSE & IT Tutorial 4u
 Triggers &
Cursors
Cursors :
A cursor in SQL is a temporary work area created in system memory when a SQL
statement is executed. A SQL cursor is a set of rows together with a pointer that identifies
a current row. It is a database object to retrieve data from a result set one row at a time.

Types of Cursors in SQL

There are the following two types of cursors in SQL:

1. Implicit Cursor
2. Explicit Cursor

CSE & IT Tutorial 4u

 Triggers &
Cursors
Main components of Cursors

Each cursor contains the followings 5 parts,

1. Declare Cursor: In this part, we declare variables and return a set of

values.
2. Open: This is the entering part of the cursor.
3. Fetch: Used to retrieve the data row by row from a cursor.
4. Close: This is an exit part of the cursor and used to close a cursor.
5. Deallocate: In this part, we delete the cursor definition and release all
the system resources associated with the cursor.
 Triggers &
Cursors
Cursor Scope
Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to
define the scope of the cursor name.

1. GLOBAL - specifies that the cursor name is global to the connection.

2. LOCAL - specifies that the cursor name is local to the Stored Procedure, trigger, or query that
holds the cursor.

Data Fetch Option in Cursors

Microsoft SQL Server supports the following two fetch options for data:

3. FORWARD_ONLY - Specifies that the cursor can only be scrolled from the first to the last row.
4. SCROLL - It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE, and
ABSOLUTE). CSE & IT Tutorial 4u
Procedure
s
 Procedure
s
What is a Procedure?
Procedures are the subprograms which can be created and saved in the database as
database objects.
A procedure in SQL (often referred to as stored procedure), is a reusable unit that
encapsulates the specific business logic of the application. A SQL procedure is a group of
SQL statements and logic, compiled and stored together to perform a specific task.

CSE & IT Tutorial 4u

 Procedure
s
Procedure Syntax:

CREATE [ OR REPLACE] PROCEDURE procedure_name

[ (parameter_name [IN | OUT | IN OUT] type [ ])]
{IS | AS }
BEGIN [declaration_section]
executable_section
//SQL statement used in the stored
procedure END
GO

CSE & IT Tutorial 4u

THANK
YOU
What’s in this video for
you? DBMS Topics
1. Introduction to Database
Theory
2. DBMS Architecture
3. Relational Data Model
4. ER Diagrams
5. Relational Algebra in DBMS
6. Transaction Management
7. Concurrency Control in DBMS
8. Keys in DBMS
9. Dependencies, Independencies
10. File Structure
CSE & IT Tutorial 4u
 01
Introductio
n
To
Databas
e
 Difference Between DBMS, RDBMS
DBMS RDBMS
Data stored is in the file format Data stored is in table format

No support for distributed database Support distributed database

Data stored is a small quantity Data is stored in a large amount

DBMS supports a single user RDBMS supports multiple users

Individual access of data elements Multiple data elements are

accessible together

No connection between data Data in the form of a table are

linked together

There is normalisation Normalisation is not achievable

XML, Microsoft Access. Oracle, SQL Server. CSE & IT Tutorial 4u

DBMS Advantages

1. Reducing Data Redundancy

2. Sharing of Data
3. Data Integrity
4. Data Security
5. Privacy
6. Backup and Recovery
7. Data Consistency

CSE & IT Tutorial 4u

DBMS Disadvantages

1. Increased Cost
2. Complexity
3. Currency Maintenance
4. Database Failure
5. Huge Size
6. Difficult Backup And
Recovery
7. Confidentiality, Privacy, and
Security CSE & IT Tutorial 4u
DBMS Applications

CSE & IT Tutorial 4u

 02
DBMS
Architectur
e
02 DBMS
Architecture DBMS Architecture

Types of DBMS Architecture

One Tier Architecture

Two Tier
Architecture

Three Tier
Architecture
Database Schema
CSE & IT Tutorial 4u