By: Arjun Rokaya

arjunrokaya@[Link]
 What is Data
In simple words data can be defined as a
facts or records related to any object in
consideration.

For example
your name, age, height, weight, etc are some
data related to you.

A picture , image , file , pdf, audio, videos etc.

2
arjunrokaya@[Link]
DATABASE SYSTEM VS. FILE
SYSTEM

Database Systems: Design, Implementation, & Management: Rob & Coronel

3
arjunrokaya@[Link]
DATABASE SYSTEM VS. FILE
SYSTEM
Drawbacks of Filesystem:

•Data Isolation: Because data are scattered in various files, and

files may be in different formats, writing new application
programs to retrieve the appropriate data is difficult.

•Duplication of data – Redundant data

•Dependency on application programs – Changing files

would lead to change in application programs.

4
arjunrokaya@[Link]
DATABASE SYSTEM VS. FILE
SYSTEM
Advantage of DBMS over file system:

There are several advantages of Database management system over file

system. Few of them are as follows:

No redundant data – Redundancy removed by data normalization

Data Consistency and Integrity – data normalization takes care of it too
Secure – Each user has a different set of access
Privacy – Limited access
Easy access to data
Easy recovery
Flexible

5
arjunrokaya@[Link]
 DATABASE SYSTEM VS. FILE
SYSTEM
Disadvantages of DBMS:

DBMS implementation cost is high compared to the file system

Complexity: Database systems are complex to understand

Performance: Database systems are generic, making them

suitable for various applications. However this feature affect their
performance for some applications

6
arjunrokaya@[Link]
 EVOLUTION OF DATA BASE
 Timeline

1960s 1970s 1980s 1990s 2000+

File-based

Hierarchical
Object-oriented
Network

Relational Web-based
Entity-Relationship

7
arjunrokaya@[Link]
 DBMS
 DBMS stands for Database Management System.

 DBMS = Database + Management System.

 Database is a collection of data and Management System

is a set of programs to store and retrieve those data.

 Based on this we can define DBMS like this: DBMS is a

collection of inter-related data and set of programs to
store & access those data in an easy and effective manner.

e.g. MySQL,DB2,PostgreSql, Oracle,MongoDB,Microsoft

Access,FileMaker,FoxPro etc.
8
arjunrokaya@[Link]
 DATABASE
 Database is a collection of related data and data is a collection of
facts and figures that can be processed to produce information.

 Mostly data represents recordable facts. Data aids in producing

information, which is based on facts. For example, if we have data
about marks obtained by all students, we can then conclude
about toppers and average marks.

e.g. 1. record of students stored in files

2. information stored over internet

9
arjunrokaya@[Link]
 What is the need of DBMS?
 Database systems are basically developed for
large amount of data. When dealing with huge
amount of data.

 There are two things that require optimization:

Storage of data and retrieval of data.

10
arjunrokaya@[Link]
 Contd..
 Storage:
According to the principles of database systems, the data is
stored in such a way that it acquires lot less space as the
redundant data (duplicate data) has been removed before
storage.

Let’s take a layman example to understand this:

In a banking system, suppose a customer is having two accounts, one is
saving account and another is salary account. Let’s say bank stores saving
account data at one place (these places are called tables we will learn
them later) and salary account data at another place, in that case if the
customer information such as customer name, address etc. are stored at
both places then this is just a wastage of storage (redundancy/ duplication
of data), to organize the data in a better way the information should be
stored at one place and both the accounts should be linked to that 11
information somehow. The same thing we achieve in DBMS.
arjunrokaya@[Link]
 Contd..
 Fast Retrieval of data:

Along with storing the data in an optimized and

systematic manner, it is also important that we
retrieve the data quickly when needed. Database
systems ensure that the data is retrieved as quickly as
possible.

12
arjunrokaya@[Link]
 Database Applications
 Banking: all transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Manufacturing: production, inventory, orders, supply
chain
 Human resources: employee records, salaries, tax
deductions
 Data Mining
 AI

Databases touch all aspects of our lives

13
arjunrokaya@[Link]
 Types of DBMS
There are 4 major types of DBMS.

Following diagram shows the evolution of

DBMS categories.

14
arjunrokaya@[Link]
 Hierarchical DBMS
Hierarchical - this type of DBMS employs the "parent-child"
relationship of storing data. This type of DBMS is rarely used
nowadays. Its structure is like a tree with nodes representing
records and branches representing fields. The windows
registry used in Windows XP is an example of a hierarchical
database. Configuration settings are stored as tree structures
with nodes.

15
arjunrokaya@[Link]
 Network DBMS
This type of DBMS supports many-to many relations. This
usually results in complex database structures. RDM Server is
an example of a database management system that
implements the network model.

16
arjunrokaya@[Link]
 Relational DBMS
 This type of DBMS defines database relationships in form
of tables, also known as relations.

 Unlike network DBMS, RDBMS does not support many to

many relationships. Relational DBMS usually have pre-
defined data types that they can support.

 This is the most popular DBMS type in the market.

Examples of relational database management systems
include MySQL, Oracle, and Microsoft SQL Server
database.

17
arjunrokaya@[Link]
 Relational DBMS
Customer_ID Customer_Account Agent_ID
1224 4556 23
1225 4558 25

Agent_ID Last_Name First_Name Phone

23 Sturm David 334-5678
25 Long Kyle 556-3421

Customer_ID Last_Name First_Name Phone Account_Balance

1224 Vira Dyne 678-9987 1223.95
1225 Davies Tricia 556-3342 234.25
18
arjunrokaya@[Link]
Object Oriented Relation DBMS
 It supports storage of new data types.

 The data to be stored is in form of objects. The

objects to be stored in the database have attributes
(i.e. gender, ager) and methods that define what to
do with the data.

 PostgreSQL is an example of an object oriented

relational DBMS.

19
arjunrokaya@[Link]
Object Oriented Relation DBMS

20
arjunrokaya@[Link]
 DATA ABSTRACTION IN DBMS

Database systems are made-up of complex data

structures.

To ease the user interaction with database, the

developers hide internal irrelevant details from
users. This process of hiding irrelevant details
from user is called data abstraction.

21
arjunrokaya@[Link]
 DATA ABSTRACTION IN DBMS

22
arjunrokaya@[Link]
DATA ABSTRACTION IN DBMS
Physical level:
This is the lowest level of data abstraction. It describes how
data is actually stored in database. You can get the complex
data structure details at this level.

Logical level:
This is the middle level of 3-level data abstraction
architecture. It describes what data is stored in database.

View level:
Highest level of data abstraction. This level describes the user
interaction with database system.
23
arjunrokaya@[Link]
 DATA ABSTRACTION IN DBMS
Example:
Let’s say we are storing customer information in a customer table .
At physical level these records can be described as blocks of storage (bytes,
gigabytes, terabytes etc.) in memory. These details are often hidden from the
programmers.

At the logical level these records can be described as fields and attributes
along with their data types, their relationship among each other can be
logically implemented. The programmers generally work at this level
because they are aware of such things about database systems.

At view level, user just interact with system with the help of GUI and enter
the details at the screen, they are not aware of how the data is stored and
what data is stored; such details are hidden from them.
24
arjunrokaya@[Link]
 INSTANCE AND SCHEMA IN DBMS

Schema:
Design of a database is called the schema.

Schema is of three types:

Physical schema, logical schema and view schema.

25
arjunrokaya@[Link]
 INSTANCE AND SCHEMA IN DBMS

Physical schema:
The design of a database at physical level is
called physical schema

how the data stored in blocks of storage is

described at this level.

26
arjunrokaya@[Link]
 INSTANCE AND SCHEMA IN DBMS

Logical schema:
Design of database at logical level is called logical schema.

programmers and database administrators work at this level

In this level data can be described as certain types of data

records gets stored in data structures, however the internal
details such as implementation of data structure is hidden at
this level (available at physical level).

27
arjunrokaya@[Link]
 INSTANCE AND SCHEMA IN DBMS

View schema:
Design of database at view level is called view
schema.

This generally describes end user interaction with

database systems.

28
arjunrokaya@[Link]
 INSTANCE AND SCHEMA IN DBMS

Definition of instance:
The data stored in database at a particular moment of time is
called instance of database.

Database schema defines the variable declarations in tables

that belong to a particular database; the value of these variables
at a moment of time is called the instance of that database.

29
arjunrokaya@[Link]
 DATA
MODELS
30
arjunrokaya@[Link]
 DATA MODEL
A collection of tools for describing
• data
• data relationships
• data semantics
• data constraints
Entity-Relationship model
Relational model
Other models:
object-oriented model
semi-structured data models
Older models: network model and hierarchical model

31
arjunrokaya@[Link]
 E-R MODEL
E-R model of real world
 Entities (objects)
E.g. customers, accounts, bank branch

 Relationships between entities

E.g. Account A-101 is held by customer Johnson
Relationship set depositor associates customers with accounts

Widely used for database design

Database design in E-R model usually converted to design
in the relational model which is used for storage and
processing
32
arjunrokaya@[Link]
 E-R MODEL
Entity-Relationship Model or E-R Model was
developed by Peter Chen in 1976. E-R Model can be
expressed as the collection of entities, also called as
real word objects and relations between those entities.
No two entities should be identical. E-R Model shows
the conceptual view of the database.
E-R Model is formed by the entity set, relationship set
and the attributes. Where all the entities collectively
form entity set, all the relations between the entities
collectively form relationship set, and the attribute
describes the properties of entities. 33
arjunrokaya@[Link]
 E-R MODEL
It can be explained with an example. Let us take two real-world entities
Employ and Department it will form an Entity set. Now we can easily easy
derive a relation between these two entities that an Employ works-for a
Department. This is how we can retrieve relationship set from E-R Model.
On the other hand, attributes of these entities would be, for Employ-
(employ_name-, employ_id, emploly_add, employ_post, etc.) and for
Department- (department_name, department_no, location, etc.).

The content of ER-model must conform constraints like Mapping

cardinality, Participation ratio and Keys. Mapping cardinality describes
the number of entities associated to the another entity. Participation ratio
describes whether there is a total or partial participation of one entity to
another. Keys uniquely define an entity in an entity set.

34
arjunrokaya@[Link]
 E-R MODEL
Here are the geometric shapes and their meaning in an E-R
Diagram :

Rectangle: Represents Entity sets.

Ellipses: Attributes
Diamonds: Relationship Set
Lines: They link attributes to Entity Sets and Entity sets to Relationship Set
Double Ellipses: Multivalued Attributes
Dashed Ellipses: Derived Attributes
Double Rectangles: Weak Entity Sets
Double Lines: Total participation of an entity in a relationship set

35
arjunrokaya@[Link]
ENTITY-RELATIONSHIP MODEL
ENTITY-RELATIONSHIP MODEL
 ENTITY-RELATIONSHIP MODEL
 Multivalued Attributes:
An attribute that can hold multiple values is known as multivalued attribute.
We represent it with double ellipses in an E-R Diagram.

E.g. A person can have more than one phone numbers so the phone number
attribute is multivalued.

 Derived Attribute:
A derived attribute is one whose value is dynamic and derived from another
attribute. It is represented by dashed ellipses in an E-R Diagram.

E.g. Person age is a derived attribute as it changes over time and can be derived from
another attribute (Date of birth).
ENTITY-RELATIONSHIP MODEL
ENTITY-RELATIONSHIP MODEL
ENTITY-RELATIONSHIP MODEL
 RELATIONAL MODEL
 Relational Model was developed by Codd in 1970.
 In relational model, the data and relationships are
represented by collection of inter-related tables.

 Each table is a group of column and rows, where column

represents attribute of an entity and rows represents records.

The main highlights of this model are:

 Data is stored in tables called relations.
 Relations can be normalized.
 In normalized relations, values saved are atomic values.
 Each row in a relation contains a unique value.
 Each column in a relation contains values from a same
arjunrokaya@[Link]
domain.
 RELATIONAL MODEL

arjunrokaya@[Link]
 RELATIONAL MODEL

arjunrokaya@[Link]
RELATIONAL MODEL
 DATABASE LANGUAGES
 Database languages are used for read, update and store data
in a database.

 There are several such languages that can be used for this
purpose; one of them is SQL (Structured Query Language).

Types of DBMS languages:

 Data Definition Language (DDL)
 Data Manipulation Language (DML)
 Data Control language (DCL)

46
arjunrokaya@[Link]
 DATABASE LANGUAGES
Data Definition Language (DDL):

DDL is used for specifying the database schema.

Let’s take SQL for instance to categorize the statements that
comes under DDL.

•To create the database instance – CREATE

•To alter the structure of database – ALTER
•To drop database instances – DROP
•To delete tables in a database instance – TRUNCATE
•To rename database instances – RENAME

All these commands specify or update the database schema that’s why they
come under Data Definition language.
47
arjunrokaya@[Link]
 DATABASE LANGUAGES
Data Manipulation Language (DML):

DML is used for accessing and manipulating data in a

database.

To read records from table(s) – SELECT

To insert record(s) into the table(s) – INSERT
Update the data in table(s) – UPDATE
Delete all the records from the table – DELETE

48
arjunrokaya@[Link]
 DATABASE LANGUAGES
Data Control language (DCL):

DCL is used for granting and revoking user access on a

database

To grant access to user – GRANT

To revoke access from user – REVOKE

In practical data definition language, data manipulation

language and data control languages are not separate language;
rather they are the parts of a single database language such as
SQL.
49
arjunrokaya@[Link]
 KEYS IN DBMS
Key plays an important role in relational database.
It is used for identifying unique rows from table.
It also establishes relationship among tables.

Types of keys in DBMS:

Primary Key
Foreign Key
Super Key
Candidate Key
Alternate Key
Composite Key 50
arjunrokaya@[Link]
 KEYS IN DBMS
Primary key :
A primary key is a column or set of columns in a table
that uniquely identifies tuples (rows) in that table.

51
arjunrokaya@[Link]
 KEYS IN DBMS
How to choose a primary key?
There are two ways:

Either to create a column and let database automatically have numbers in

increasing order for each row .

or choose a column yourself making sure that it does not contain
duplicates and nulls.

For e.g. in the above Student table, The Stu_Name column cannot be a
primary key as more than one people can have same name, similarly the
Stu_Age column cannot play a primary key role as more than one persons
can have same age.

52
arjunrokaya@[Link]
 KEYS IN DBMS

Foreign key :

Foreign keys are the columns of a table that points to

the primary key of another table.

They act as a cross-reference between tables.

53
arjunrokaya@[Link]
 KEYS IN DBMS

54
arjunrokaya@[Link]
 KEYS IN DBMS
Super key :
A super key is a set of one of more columns (attributes) to
uniquely identify rows in a table.

55
arjunrokaya@[Link]
 KEYS IN DBMS

56
arjunrokaya@[Link]
 KEYS IN DBMS

57
arjunrokaya@[Link]
 KEYS IN DBMS
Candidate key :
A super key with no redundant attribute is known as
candidate key

Candidate keys are selected from the set of super keys,

the only thing we take care while selecting candidate key
is: It should not have any redundant attributes.

That’s the reason they are also termed as minimal super

key.
58
arjunrokaya@[Link]
 KEYS IN DBMS

59
arjunrokaya@[Link]
 KEYS IN DBMS

Alternate key :
– Out of all candidate keys, only one gets
selected as primary key, remaining keys are
known as alternate or secondary keys.

60
arjunrokaya@[Link]
 KEYS IN DBMS

61
arjunrokaya@[Link]
 KEYS IN DBMS

Composite key :
A key that consists of more than one attribute
to uniquely identify rows (also known as
records & tuples) in a table is called composite
key. It is also known as compound key.

62
arjunrokaya@[Link]
 KEYS IN DBMS

63
arjunrokaya@[Link]
 SQL
64
arjunrokaya@[Link]
 SQL
 SQL is Structured Query Language, which is a
computer language for storing, manipulating and
retrieving data stored in a relational database.

 SQL is the standard language for Relational

Database System.

 All the Relational Database Management Systems

(RDMS) like MySQL, MS Access, Oracle, Sybase,
Informix, Postgres and SQL Server use SQL as their
standard database language.
65
arjunrokaya@[Link]
 SQL
 SQL – Syntax
SQL is followed by a unique set of rules and guidelines called
Syntax.

All the SQL statements start with any of the keywords like
SELECT, INSERT, UPDATE, DELETE, ALTER, DROP,
CREATE, USE, SHOW and all the statements end with a
semicolon (;).

The most important point to be noted here is that SQL is case

insensitive, which means SELECT and select have same
meaning in SQL statements. Whereas, MySQL makes
difference in table names. So, if you are working with 66
MySQL, then you need to give table names as they exist in
arjunrokaya@[Link]

the database.
 SQL
 CREATE DATABASE :
Syntax:

CREATE DATABASE DatabaseName;

 SHOW DATABASES:

show database;

 DROP DATABASE :

Syntax:
DROP DATABASE DatabaseName;

 SQL USE DATABASE

syntax:
USE database_name;

Always the database name should be unique within the RDBMS 67

arjunrokaya@[Link]
 SQL
CREATE TABLE :
The SQL CREATE TABLE statement is used to create a new
table.

Syntax:

CREATE TABLE table_name(

column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
); 68
arjunrokaya@[Link]
 SQL

69
arjunrokaya@[Link]
 SQL
 Creating a Table from an Existing Table :

Syntax:

CREATE TABLE NEW_TABLE_NAME AS

SELECT [ column1, column2...columnN ]

FROM EXISTING_TABLE_NAME

[ WHERE ]

70
arjunrokaya@[Link]
 SQL
 Example:

Following is an example which would create a table SALARY

using the CUSTOMERS table and having the fields –
customer ID and customer SALARY:

SQL:
CREATE TABLE SALARY AS

SELECT ID, SALARY

FROM CUSTOMERS;
71
arjunrokaya@[Link]
 SQL
 DROP or DELETE Table :

Syntax:

DROP TABLE table_name;

72
arjunrokaya@[Link]
 SQL
 INSERT INTO :
The SQL INSERT INTO Statement is used to add new
rows of data to a table in the database.

Syntax:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]

VALUES (value1, value2, value3,...valueN);

73
arjunrokaya@[Link]
 SQL
 Example:
 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, Ktm', 2000.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, 'kaushik', 23, ‘Pokhara', 2000.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, 'Hardik', 27, ‘Butwal', 8500.00 );

74
arjunrokaya@[Link]
 SQL
 SELECT Query :

The SQL SELECT statement is used to fetch the data from a

database table which returns this data in the form of a result
table. These result tables are called result-sets.

Syntax:
SELECT column1, column2, columnN FROM table_name;

To select all the Data From table:

SELECT * FROM table_name;

75
arjunrokaya@[Link]
 SQL
 Example:

SELECT ID, NAME, SALARY FROM CUSTOMERS;

SELECT * FROM CUSTOMERS;

76
arjunrokaya@[Link]
 SQL
 WHERE Clause :
 The SQL WHERE clause is used to specify a condition while fetching
the data from a single table or by joining with multiple tables.

 If the given condition is satisfied, then only it returns a specific value

from the table.

 You should use the WHERE clause to filter the records and fetching
only the necessary records.

 The WHERE clause is not only used in the SELECT statement, but it is
also used in the UPDATE, DELETE statement, etc., which we would
examine in the subsequent chapters.

77
arjunrokaya@[Link]
 SQL
 Syntax:

SELECT column1, column2, columnN

FROM table_name
WHERE [condition]

You can specify a condition using the comparison or logical operators like >,
<, =, LIKE, NOT, etc.

78
arjunrokaya@[Link]
 SQL
 Example:

1> SELECT ID, NAME, SALARY

FROM CUSTOMERS
WHERE SALARY > 2000;

2> SELECT ID, NAME, SALARY

FROM CUSTOMERS
WHERE NAME = 'Hardik';

3> SELECT * FROM CUSTOMERS WHERE NAME LIKE 'Ko%';

79
arjunrokaya@[Link]
 SQL
 AND & OR Conjunctive Operators :
The SQL AND & OR operators are used to combine multiple
conditions to narrow data in an SQL statement.

These two operators are called as the conjunctive operators.

These operators provide a means to make multiple
comparisons with different operators in the same SQL
statement.

80
arjunrokaya@[Link]
 SQL
 AND Operator:

 Syntax :

SELECT column1, column2, columnN

FROM table_name

WHERE [condition1] AND [condition2]...AND [conditionN];

81
arjunrokaya@[Link]
 SQL
 Example:

SELECT ID, NAME, SALARY

FROM CUSTOMERS

WHERE SALARY > 2000 AND age < 25;

82
arjunrokaya@[Link]
 SQL
 The OR Operator :
The OR operator is used to combine multiple
conditions in an SQL statement's WHERE clause.

Syntax :

SELECT column1, column2, columnN

FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

83
arjunrokaya@[Link]
 SQL
 Example:

SELECT ID, NAME, SALARY

FROM CUSTOMERS

WHERE SALARY > 2000 OR age < 25;

84
arjunrokaya@[Link]
 SQL
 Operators in The WHERE Clause:

85
arjunrokaya@[Link]
 SQL
 IN Operator :
The IN operator allows you to specify multiple values in a
WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Example :

SELECT * FROM Customers

WHERE Country IN ('Germany', 'France', 'UK'); 86

arjunrokaya@[Link]
 SQL
 BETWEEN Operator:
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.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND valu
e2; 87
arjunrokaya@[Link]
 SQL
 UPDATE Query :

The SQL UPDATE Query is used to modify the existing records

in a table.

You can use the WHERE clause with the UPDATE query to
update the selected rows, otherwise all the rows would be
affected

88
arjunrokaya@[Link]
 SQL
 UPDATE Query :

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

89
arjunrokaya@[Link]
 SQL
 Example:

1>
UPDATE CUSTOMERS

SET ADDRESS = ‘China‘

WHERE ID = 6;

2>
UPDATE CUSTOMERS

SET ADDRESS = 'Pune', SALARY = 1000.00; 90

arjunrokaya@[Link]
 SQL
 DELETE Query :
The SQL DELETE Query is used to delete the existing records from a table.

You can use the WHERE clause with a DELETE query to delete the selected
rows, otherwise all the records would be deleted .

Syntax :

DELETE FROM table_name

WHERE [condition];
91
arjunrokaya@[Link]
 SQL
 Example:

DELETE FROM CUSTOMERS

WHERE ID = 6;

92
arjunrokaya@[Link]
 SQL

ORDER BY Clause

93
arjunrokaya@[Link]
 SQL
 ORDER BY Clause :

The SQL ORDER BY clause is used to sort the data in ascending

or descending order, based on one or more columns.

Some databases sort the query results in an ascending order by

default.

94
arjunrokaya@[Link]
 SQL
 Syntax:

SELECT column-list

FROM table_name

[WHERE condition]

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Note:
You can use more than one column in the ORDER BY clause.

Make sure whatever column you are using to sort that column should
be in the column-list. 95
arjunrokaya@[Link]
 SQL
 Example:

1>
SELECT * FROM CUSTOMERS

ORDER BY NAME, SALARY;

2>

SELECT * FROM CUSTOMERS

ORDER BY NAME DESC;

96
arjunrokaya@[Link]
 SQL
 Group By :

The SQL GROUP BY clause is used in collaboration

with the SELECT statement to arrange identical
data into groups.

This GROUP BY clause follows the WHERE clause in a

SELECT statement and precedes the ORDER BY
clause.

97
arjunrokaya@[Link]
 SQL
 Example:

SELECT column1, column2

FROM table_name
WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

98
arjunrokaya@[Link]
 SQL
 Example:

1>
SELECT NAME, SUM(SALARY)
FROM CUSTOMERS
GROUP BY NAME;

99
arjunrokaya@[Link]
 SQL

100
arjunrokaya@[Link]
SQL - Useful Functions

101
arjunrokaya@[Link]
 SQL - FUNCTIONS
 COUNT():

It is the simplest function and very useful in counting

the number of records, which are expected to be
returned by a SELECT statement.

102
arjunrokaya@[Link]
 SQL - FUNCTIONS
 Example:

1>
SELECT COUNT(*) FROM employee_tbl ;

output: 7 (counts rows in a table)

2>
SELECT COUNT(*) FROM employee_tbl
WHERE name="Zara";

output: 2
103
arjunrokaya@[Link]
 SQL-FUNCTION
 MAX():
The MAX() function returns the largest value
of the selected column.

 Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;
104
arjunrokaya@[Link]
 SQL-FUNCTION
 MAX():

105
arjunrokaya@[Link]
 SQL-FUNCTION
 Example:

SELECT MAX(daily_typing_pages)

FROM employee_tbl;

Output: 350

106
arjunrokaya@[Link]
 SQL-FUNCTION
 MIN():
The MIN() function returns the smallest
value of the selected column.

 Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;
107
arjunrokaya@[Link]
 SQL-FUNCTION
 MIN():
The MIN() function returns the smallest
value of the selected column.

 Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;
108
arjunrokaya@[Link]
 SQL-FUNCTION
 MIN():

109
arjunrokaya@[Link]
 SQL-FUNCTION
 Example:

SELECT MIN(daily_typing_pages)

FROM employee_tbl;

Output: 100

110
arjunrokaya@[Link]
 SQL-FUNCTION
 AVG():
AVG function is used to find out the average
of a field in various records.

 Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;
111
arjunrokaya@[Link]
 SQL-FUNCTION
 Example:

SELECT AVG(daily_typing_pages)

FROM employee_tbl;

Output: 230.0000

2>
SELECT name, AVG(daily_typing_pages)
FROM employee_tbl
GROUP BY name;
112
arjunrokaya@[Link]
 SQL-FUNCTION
 SUM():
The SUM() function returns the total sum of
a numeric column.

 Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;
113
arjunrokaya@[Link]
 SQL-FUNCTION
 Example:
1>
SELECT SUM(daily_typing_pages)

FROM employee_tbl;

2>

SELECT name, SUM(daily_typing_pages)

FROM employee_tbl
GROUP BY name;
114
arjunrokaya@[Link]