Introduction to Databases: Databases and Database Users

Introduction

● What is a Database?

o A collection of related data.

o Designed, built, and populated with data for a specific purpose.

o Has an inherent meaning.

o Logically coherent collection of data with some inherent meaning.

● What is Data?

o Raw facts, figures, text, images, sound, etc.

o Meaningful only when interpreted within a context.

● Why Databases?

o Overcome the limitations of traditional file processing systems (data

redundancy, inconsistency, difficulty in accessing data, isolation of
data, integrity problems, atomicity problems, concurrent access
anomalies, security problems).
Example

● University Database Example:

o Entities: STUDENTS, COURSES, INSTRUCTORS, DEPARTMENTS.

o Relationships:

▪ Students ENROLL in Courses.

▪ Instructors TEACH Courses.

▪ Students are MAJORED in Departments.

▪ Courses are OFFERED by Departments.

o Data: Student names, IDs, addresses; Course titles, numbers,

credits; Instructor names, ranks; Department names, heads.
o Operations: Add new students, register students for courses, check
course prerequisites, generate transcripts, etc.
Characteristics of the Database Approach

● Self-describing Nature:
 o A DBMS catalog (metadata) stores the description of the database
itself.
o This allows the DBMS software to work with different databases.

o Separation of data and data description.

● Insulation between Programs and Data (Data Abstraction):

o Program-data independence: Changes to data structure or storage

don't require changes to application programs.
o Users interact with a conceptual view, not the physical storage
details.

● Support for Multiple Views of Data:

o Different users/applications may need to see and access different

parts of the database in different ways.
o Each view presents a customized, simplified representation of the
database.

● Sharing of Data and Multi-user Transaction Processing:

o Allows multiple users and programs to access and modify the

database concurrently.
o Concurrency control mechanisms ensure data integrity and
consistency during simultaneous access.
o Transaction management ensures atomic and isolated operations.

Actors on the Scene

● Database Administrators (DBA):

o Responsible for authorizing access, coordinating and monitoring

use, acquiring software/hardware resources, and overall database
maintenance.
o Database design, security enforcement, performance tuning,
backup and recovery.

● Database Designers:

o Responsible for identifying the data to be stored and choosing

appropriate structures to represent and store this data.
o Interacts with users to understand data requirements.

● End Users:

o Casual End Users: Occasionally access the database, often with

sophisticated queries (e.g., managers, analysts).
 o Naïve/Parametric End Users: Constitute a majority, constantly
query and update the database using pre-programmed transactions
(e.g., bank tellers, airline reservation agents).
o Sophisticated End Users: Engineers, scientists, business analysts
who thoroughly familiarize themselves with the DBMS.
o Stand-alone End Users: Use personal databases (e.g., tax
programs, photo organizers).

● System Analysts and Application Programmers (Software

Engineers):
o System Analysts: Determine requirements of end users and
develop specifications for transactions.
o Application Programmers: Implement these specifications as
application programs, writing and testing queries and updates.
Workers behind the Scene

● DBMS Designers and Implementers:

o Design and implement the DBMS modules and interfaces (e.g.,

query parser, query optimizer, storage manager).

● Tool Developers:

o Design and implement software packages that facilitate database

design, performance monitoring, data recovery, and security.

● Operators and Maintenance Personnel:

o Responsible for the actual running and maintenance of the

hardware and software environment for the database system.
Advantages of Using the DBMS Approach

● Controlling Redundancy:

o Reduces data duplication and storage space.

o Leads to better data consistency.

● Restricting Unauthorized Access:

o Security and authorization subsystem controls who can access what

data.

● Providing Multiple User Interfaces:

o Different types of users can interact with the database using various
interfaces (SQL, forms, graphical tools).
 ● Representing Complex Relationships Among Data:

o Allows for modelling complex connections between data entities.

● Enforcing Integrity Constraints:

o Ensures data validity and correctness (e.g., data types, primary

keys, referential integrity).

● Providing Backup and Recovery:

o Mechanisms for restoring the database to a consistent state after

failures.

● Permitting Inferencing and Actions through Rules:

o Some advanced DBMSs support rule processing and active

databases.

● Potential for Enforcing Standards:

o Facilitates adherence to data formats and naming conventions.

● Reducing Application Development Time:

o DBMS provides built-in functionalities, reducing the need to write

custom code for data management.

● Flexibility and Extensibility:

o Ability to adapt to changing requirements and grow over time.

● Economy of Scale:

o Consolidating data management can be more cost-effective.

A Brief History of Database Applications

● Early Days (1960s):

o Hierarchical Data Model (e.g., IBM's IMS): Data organized in a

tree-like structure. Good for structured data with one-to-many
relationships.
o Network Data Model (CODASYL): More flexible than hierarchical,
allowing for many-to-many relationships through pointers.

● 1970s:

o Relational Data Model (E.F. Codd, IBM): Introduced the concept

of relations (tables) with rows and columns. Mathematical
foundation. Led to SQL development.
 ● 1980s:

o Object-Oriented Databases (OODBMS): Attempted to integrate

database capabilities with object-oriented programming. Limited
commercial success.
o Emergence of SQL as a standard.

● 1990s:

o Object-Relational Database Management Systems

(ORDBMS): Hybrid approach combining relational model with
object-oriented features.
o Growth of the Internet and web applications.

o Data Warehousing and OLAP (Online Analytical Processing).

● 2000s onwards:

o XML and Semi structured Data: Increased importance of

handling flexible data formats.
o Big Data and NoSQL Databases: Driven by massive datasets,
high velocity, and variety of data. Non-relational databases
optimized for specific use cases (key-value, document, columnar,
graph).
o Cloud Databases: Database services offered as part of cloud
computing platforms.
o New SQL (NewSQL): Attempts to combine the scalability of NoSQL
with the ACID properties of traditional relational databases.
o In-memory Databases: Data stored primarily in RAM for
extremely fast access.

UNIT - II:

Database System Concepts and Architecture : Data Models,

Schemas, and Instances - Three-Schema Architecture and Data
Independence - Database Languages and Interfaces - The Database System
Environment - Centralized and Client/Server Architectures for DBMSs -
Classification of Database Management Systems - The Relational Data Model and
SQL : The Relational Data Model and Relational - Database Constraints -
Relational Model Concepts - Relational Model Constraints and Relational
Database Schemas - Update Operations, Transactions, and Dealing with
Constraint Violations.

Database System Concepts and Architecture

Data Models, Schemas, and Instances

● Data Model:
○ A set of concepts used to describe the structure of a database.
○ Defines how data is represented, how relationships between data
are defined, and how data manipulation operations are specified.
○ Examples: Relational Model, Network Model, Hierarchical Model,
Object-Oriented Model.
● Schema:
○ The description of the database.
○ Defined during database design and is not expected to change
frequently.
○ Also known as the intension of the database.
○ Example: CREATE TABLE STUDENT (Name VARCHAR(255), ID INT
PRIMARY KEY);
● Instance (or State):
○ The actual data stored in the database at a particular moment in
time.
○ Changes frequently as data is updated, inserted, or deleted.
○ Also known as the extension of the database.
○ Example: A table STUDENT with rows like ('Alice', 101), ('Bob', 102).

Three-Schema Architecture and Data Independence

● Goal: To separate user applications from the physical database.

● Levels of Abstraction:
○ Internal Level (Physical Schema):
■ Describes the physical storage structure of the database.
■ How data is actually stored on disk (file organization,
indexing, access paths).
■ Concerned with efficiency of storage and retrieval.
○ Conceptual Level (Conceptual Schema):
■ Describes the structure of the whole database for a
community of users.
■ Hides the details of physical storage structure.
■ Focuses on entities, attributes, relationships, and constraints.
■ Is independent of any specific physical storage structure or
specific application's view.
○ External Level (External Schemas or Views):
■ Describes the part of the database relevant to a particular
user or application.
■ Provides a customized, simplified view of the database,
hiding irrelevant data and complex conceptual details.
■ Can be derived from the conceptual schema.
● Data Independence:
○ Logical Data Independence: The ability to change the conceptual
schema without having to change the external schemas or
application programs. (e.g., adding a new attribute to a table
doesn't affect existing applications that don't use it).
○ Physical Data Independence: The ability to change the internal
(physical) schema without having to change the conceptual
 schema. (e.g., changing file organization or indexing strategy
doesn't affect conceptual view).
○ Benefits: Reduces maintenance costs, allows for database
evolution, and simplifies application development.

Database Languages and Interfaces

● Database Languages:
○ Data Definition Language (DDL):
■ Used to define the conceptual and internal schemas.
■ Commands for creating, modifying, and dropping database
objects (tables, indexes, views).
■ Examples: CREATE TABLE, ALTER TABLE, DROP TABLE,
CREATE INDEX.
○ Data Manipulation Language (DML):
■ Used for retrieving, inserting, deleting, and updating data.
■ Procedural DML: Requires users to specify what data is
needed and how to get it (e.g., navigation commands in
network/hierarchical models).
■ Declarative (Non-procedural) DML: Users specify what
data is needed without specifying how to get it (e.g., SQL's
SELECT, INSERT, UPDATE, DELETE).
○ Data Control Language (DCL):
■ Used for managing permissions and controlling access to the
database.
■ Examples: GRANT, REVOKE.
● Database Interfaces:
○ Menu-based interfaces: For web clients or casual users.
○ Forms-based interfaces: For parametric users (e.g., data entry
screens).
○ Graphical User Interfaces (GUIs): Visual tools for querying and
managing databases.
○ Natural language interfaces: Attempt to understand free-form
text.
○ Speech input and output: Emerging interfaces.
○ Web browser interfaces: Most common for web applications.
○ Programmatic interfaces (APIs): For application programmers
(e.g., JDBC for Java, ODBC for C/C++).

The Database System Environment

● Components:
○ DBMS Software: Core software that manages data (DDL compiler,
DML compiler, query optimizer, transaction manager, buffer
manager, file manager).
○ Hardware: Computers, storage devices, network.
○ Data: The actual database and its schema.
○ Users: Various types of users (DBA, designers, end-users,
programmers).
○ Software Tools: Utilities for design, performance monitoring,
security, backup.
Centralized and Client/Server Architectures for DBMSs

● Centralized Architecture:
○ All DBMS components, data, and processing are located on a single
computer (mainframe or powerful server).
○ Terminals (dumb terminals) are connected to the central computer
for input/output.
○ Pros: Simplicity, easier management.
○ Cons: Single point of failure, scalability limitations, performance
bottleneck.
● Client/Server Architecture:
○ Client: User workstation or application server that requests
services (e.g., query, update).
○ Server: Central computer running the DBMS software, which
provides services (e.g., data storage, query processing, transaction
management).
○ Two-Tier Architecture:
■ Client (application) directly communicates with the database
server.
■ Pros: Simpler to implement than three-tier.
■ Cons: Business logic often resides on the client, harder to
manage, poor scalability for many clients.
○ Three-Tier Architecture:
■ Client (Presentation Layer): User interface.
■ Application Server (Business Logic Layer): Contains
application logic, acts as an intermediary between client and
database server.
■ Database Server (Data Layer): Stores and manages the
database.
■ Pros: Improved scalability, better security, easier
maintenance, business logic centralized.
■ Cons: Increased complexity.

Classification of Database Management Systems

● Based on Data Model:

○ Relational DBMS (RDBMS)
○ Hierarchical DBMS
○ Network DBMS
○ Object-Oriented DBMS (OODBMS)
○ Object-Relational DBMS (ORDBMS)
○ NoSQL DBMS (Key-value, Document, Column-family, Graph)
● Based on Number of Users:
○ Single-user systems (e.g., desktop databases like MS Access)
○ Multi-user systems (most enterprise DBMSs)
● Based on Distribution:
○ Centralized DBMS
○ Distributed DBMS (DDBMS): Data spread across multiple networked
sites.
■ Homogeneous DDBMS: All sites use the same DBMS software.
 ■ Heterogeneous DDBMS: Different sites use different DBMS
software.
○ Cloud Databases
● Based on Purpose/Application Domain:
○ Online Transaction Processing (OLTP) systems
○ Online Analytical Processing (OLAP) systems (Data Warehouses)
○ Real-time Database Systems
○ Spatial, Temporal, Multimedia Databases
● Based on Cost/Licensing:
○ Commercial DBMS (e.g., Oracle, SQL Server)
○ Open Source DBMS (e.g., MySQL, PostgreSQL)

The Relational Data Model and SQL

The Relational Data Model and Relational

● Foundation: Proposed by E.F. Codd in 1970. Based on mathematical

concepts of relations (sets of tuples) and relational algebra.
● Basic Idea: Represents data in tables (relations).
○ Each table has a name.
○ Each table consists of rows (tuples) and columns (attributes).
● Key Concepts:
○ Relation (Table): A set of tuples.
○ Tuple (Row): A single record in the table, representing a specific
entity.
○ Attribute (Column): A named property of a relation, representing
a characteristic of the entities.
○ Domain: The set of allowed values for an attribute.
○ Relational Schema: The name of the relation and the set of its
attributes (e.g., STUDENT(ID, Name, Major)).
○ Degree (Arity): The number of attributes in a relation.
○ Cardinality: The number of tuples in a relation at any given time.

Database Constraints

● Purpose: Rules that restrict the values of data in the database to

maintain data integrity and consistency.
● Types of Constraints:
○ Inherent Model-Based Constraints (Implicit Constraints):
Constraints that are part of the data model itself (e.g., no duplicate
rows in a relation, order of tuples/attributes doesn't matter).
○ Schema-Based Constraints (Explicit Constraints):
■ Domain Constraints: Restrict the values that an attribute
can take to a specified domain (e.g., AGE INT CHECK (AGE >
0)).
■ Key Constraints:
■ Super key: A set of attributes that uniquely identifies
a tuple in a relation.
 ■ Candidate Key: A minimal super key (no proper
subset of attributes is a super key).
■ Primary Key: One of the candidate keys chosen by
the database designer to uniquely identify tuples. Must
be non-NULL.
■ Unique Key: Similar to primary key, but allows NULL
values and there can be multiple unique keys.
■ Entity Integrity Constraint: No attribute participating in
the primary key can have a NULL value. (Ensures that every
entity has a unique identity).
■ Referential Integrity Constraint (Foreign Key
Constraint):
■ A foreign key in one relation (referencing relation)
refers to the primary key of another relation
(referenced relation).
■ Ensures that a tuple in the referencing relation refers
to an existing tuple in the referenced relation.
■ Example: DEPT_ID in EMPLOYEE table references
DEPT_ID in DEPARTMENT table.
■ NULL Values: Represents "unknown" or "not applicable".
■ Semantic Integrity Constraints
(User-Defined/Application-Defined Constraints):
■ Specific business rules that cannot be expressed by
the other types of constraints.
■ Often enforced by triggers or application logic.
■ Example: "An employee's salary cannot be greater
than their manager's salary."

Relational Model Concepts (revisited in context of constraints)

● Relation Schema: R=(A1,A2,…,An) where R is the relation name and Ai

are attributes.
● Relational Database Schema: A set of relation schemas and the
integrity constraints defined on them.
● Tuple: An ordered list of n values t=(v1,v2,…,vn), where vi is a value from
dom(Ai).
● Relation State (Instance): A set of tuples r(R), where r is the relation
name and R is its schema. It changes over time.

Relational Model Constraints and Relational Database Schemas

● The set of defined constraints dictates the valid states (instances) of the
database.
● A database state is valid if it satisfies all defined integrity constraints.
● DDL is used to specify these constraints when defining the database
schema.

Update Operations, Transactions, and Dealing with Constraint

Violations

● Update Operations:
 ○ Insert: Adds a new tuple to a relation.
○ Delete: Removes one or more tuples from a relation.
○ Update (Modify): Changes the values of one or more attributes in
one or more tuples.
● Constraint Violations:
○ Insert Violations:
■ Domain violation: Value not in attribute's domain.
■ Key violation: Primary/unique key value already exists.
■ Referential integrity violation: Foreign key references a non-
existent primary key.
■ Entity integrity violation: NULL value in primary key.
○ Delete Violations:
■ Referential integrity violation: If primary key of a deleted
tuple is referenced by foreign keys in other relations.
○ Update Violations: Can lead to any of the above violations,
depending on which attributes are updated.
● Dealing with Constraint Violations (Referential Integrity options in
SQL):
○ RESTRICT (or NO ACTION): Disallow the update/delete operation
if it violates the constraint. (Default in many systems).
○ CASCADE: Propagate the changes. If a primary key is
deleted/updated, all referencing foreign keys are also
deleted/updated.
○ SET NULL: If a primary key is deleted/updated, the referencing
foreign key values are set to NULL (if the foreign key attribute can
be NULL).
○ SET DEFAULT: If a primary key is deleted/updated, the referencing
foreign key values are set to their default value.
● Transactions:
○ A logical unit of work that contains one or more database operations
(inserts, deletes, updates, queries).
○ Must satisfy ACID properties to maintain database consistency:
■ Atomicity: All operations within a transaction are completed
successfully, or none are. (All or nothing).
■ Consistency: A transaction brings the database from one
consistent state to another consistent state.
■ Isolation: The execution of concurrent transactions does not
interfere with each other. (Each transaction appears to
execute alone).
■ Durability: Once a transaction is committed, its changes are
permanent and survive system failures.
○ COMMIT: Makes the changes permanent.
○ ROLLBACK: Undoes all changes made by the transaction.

UNIT - III:

Basic SQL : SQL Data Definition and Data Types - Specifying

Constraints in
SQL - Basic Retrieval Queries in SQL - INSERT, DELETE, and UPDATE
Statements in SQL - Additional Features of SQL - More SQL:
Complex
 Queries, Triggers, views, and Schema Modification - More Complex
SQL
Retrieval Queries - Specifying Constraints as Assertions and Actions
as Triggers
- Views (Virtual Tables) in SQL - Schema Change Statements in SQ.

BUILT IN FUNCTIONS

There are different types of SQL functions – Aggregate functions and Scalar
functions.

• Aggregate functions perform calculations on a group of values and return

a single value. Example SUM, AVG, COUNT.

• Scalar functions operate on a single value and return a single value.

Example UPPER, LOWER, SUBSTRING.

Students

Aggregate Functions Examples

AVG() Function Example

Computing average marks of students.

Query:
SELECT AVG(MARKS) AS AvgMarks FROM Students;
Output:
AvgMarks
80

COUNT() Function Example

Computing total number of students.
Query:
SELECT COUNT(*) AS NumStudents FROM Students;
Output:
NumStudents
5

FIRST() Function Example

Fetching marks of first student from the Students table.
Query:
SELECT FIRST(MARKS) AS MarksFirst FROM Students;

Output:
MarksFirst
90

LAST() Function Example

Fetching marks of last student from the Students table.
Query:
SELECT LAST(MARKS) AS MarksLast FROM Students;

Output:
MarksLast
85

MAX() Function Example

Fetching maximum marks among students from the Students table.
Query:
SELECT MAX(MARKS) AS MaxMarks FROM Students;

Output:

MaxMarks
95

MIN() Function Example

Fetching minimum marks among students from the Students table.

Query:

SELECT MIN(MARKS) AS MinMarks FROM Students;

Output:

MinMarks

50

SUM() Function Example

Fetching summation of total marks among students from the Students table.

Query:

SELECT SUM(MARKS) AS TotalMarks FROM Students;

Output:

TotalMarks

400

Scalar Functions Examples

Let’s look at some examples of each Scalar Function in SQL.

UCASE() Function Example

Converting names of students from the table Students to uppercase.

Query:

SELECT UCASE(NAME) FROM Students;

Output:

NAME

HARSH

SURESH

PRATIK

DHANRAJ

RAM

LCASE() Function Example

Converting names of students from the table Students to lowercase.

Query:

SELECT LCASE(NAME) FROM Students;

Output:

NAME

Harsh

Suresh

Pratik

Dhanraj

Ram

MID() Function Example

Fetching first four characters of names of students from the Students table.

Query:

SELECT MID(NAME,1,4) FROM Students;

Output:

NAME

HARS

SURE

PRAT
DHAN

RAM

LEN() Function Example

Fetching length of names of students from Students table.

Query:

SELECT LENGTH(NAME) FROM Students;

Output:

NAME

ROUND() Function Example

Fetching maximum marks among students from the Students table.

Query:
SELECT ROUND(MARKS,0) FROM Students;

Output:

MARKS

90

50

80

95

85

NOW() Function Example

Fetching current system time.

Query:

SELECT NAME, NOW() AS DateTime FROM Students;

Output:

NAME DateTime

HARSH 1/13/2017 1:30:11 PM

SURESH 1/13/2017 1:30:11 PM

PRATIK 1/13/2017 1:30:11 PM

DHANRAJ 1/13/2017 1:30:11 PM

RAM 1/13/2017 1:30:11 PM

Nested Queries in SQL

A nested query (or subquery) is a query inside another query. The inner query
runs first and passes its result to the outer query.

SELECT column_name FROM table_name

WHERE column_name OPERATOR (SELECT column_name FROM another_table

WHERE condition);

Table: students

Student_id name dept_id

1 Alice 101

2 Bob 102

3 Charlie 103
Table: departments

Dept_id dept_name

101 Computer Sci

102 Mathematics

104 Physics

Example 1: Find students whose department exists in the departments

table

SELECT name FROM students

WHERE dept_id IN (SELECT dept_id FROM departments);

Example 2: Find students who belong to the ‘Computer Sci’ department

SELECT name FROM students

WHERE dept_id = (SELECT dept_id FROM departments

WHERE dept_name = ‘Computer Sci’);

SELECT name FROM students

WHERE dept_id IN ( SELECT dept_id FROM departments WHERE dept_name LIKE

‘%Sci%’

);

2. Using =

SELECT name FROM students

WHERE dept_id = ( SELECT dept_id FROM departments WHERE dept_name =

‘Mathematics’);
3. Using ANY

SELECT name FROM students

WHERE dept_id = ANY ( SELECT dept_id FROM departments WHERE dept_name

IN (‘Mathematics’, ‘Physics’));

4. Using ALL

SELECT name FROM students

WHERE dept_id > ALL ( SELECT dept_id FROM departments WHERE dept_name
LIKE ‘C%’);

5. Using EXISTS

SELECT name FROM students

WHERE EXISTS ( SELECT FROM departments d

WHERE s.dept_id = d.dept_id AND d.dept_name = ‘Computer Sci’);

UNIT-3

Introduction to SQL

What is SQL?

SQL (Structured Query Language) is a language to operate databases; it includes

Database Creation, Database Deletion, Fetching Data Rows, Modifying &
Deleting Data rows, etc.

SQL stands for Structured Query Language which is a computer language for
storing, manipulating and retrieving data stored in a relational database. SQL
was developed in the 1970s by IBM Computer Scientists and became a standard
of the American National Standards Institute (ANSI) in 1986, and the
International Organization for Standardization (ISO) in 1987.

SQL is widely popular because it offers the following advantages

❖ Allows users to access data in the relational database management

systems.

❖ Allows users to describe the data.

❖ Allows users to define the data in a database and manipulate that data.

❖ Allows embedding within other languages using SQL modules, libraries &
pre-compilers.

❖ Allows users to create and drop databases and tables.

❖ Allows users to create views, stored procedure, functions in a database.

❖ Allows users to set permissions on tables, procedures and views.

A classic query engine handles all the non-SQL queries, but a SQL query engine
won’t handle logical files.

Following is a simple diagram showing the SQL Architecture –

Structured Query Language (SQL)

SQL is a database computer language designed for the retrieval and

management of data in a relational database. SQL stands for Structured Query
Language. 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. Originally, SQL was called SEQUEL (Structured
English QUEry Language).
SQL uses the terms table, row, and column for the formal relational model terms
relation, tuple, and attribute, respectively. SQL uses the concept of a catalog—a
named collection of schemas in an SQL environment. An SQL environment is
basically an installation of an SQL-compliant RDBMS on a computer system. A
catalog always contains a special schema called INFORMATION_SCHEMA , which
provides information on all the schemas in the catalog and all the element
descriptors in these schemas.

SQL Constraints

➢ NOT NULL – Ensures that a column cannot have a NULL value

➢ UNIQUE – Ensures that all values in a column are different

➢ PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely

identifies each row in a table

➢ FOREIGN KEY – Uniquely identifies a row/record in another table

➢ CHECK – Ensures that all values in a column satisfies a specific condition

➢ DEFAULT – Sets a default value for a column when no value is specified

SQL Commands

DDL: Data Definition Language

DML: Data Manipulation Language

DCL: Data Control Language

TCL:Transaction Control Language

DDL: The SQL DDL allows specification of not only a set of relations, but also
information about each relation, including:

➢ The schema for each relation.

➢ The types of values associated with each attribute.

➢ The integrity constraints.

➢ The set of indices to be maintained for each relation.

Create

➢ To create database, CREATE DATABASE database_name

➢ To create Table Statement is used to create tables to store data. Integrity

Constraints can also be defined for the columns while creating the table.

CREATE TABLE table name( Attribute1 datatype(No.),...An datatype(No)); CREATE

TABLE employee ( id number(5),name char(20),dept char(10));

➢ To create table constraint

CREATE TABLE table_name (A1 data type constraint, A2 data type constraint, A3
data type constraint,);

Primary key:

The primary key attributes are required to be nonnull and unique.

CREATE TABLE Persons (PID int(10) NOT NULL PRIMARY KEY, LastName

Varchar(25), FirstName varchar(25));

Or CREATE TABLE Persons (PID int (10) NOT NULL,

LastName varchar(25),FirstName varchar(25), primary key(id));

Foreign Key

CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int
NOT NULL, PersonID int, FOREIGN KEY (PID) REFERENCES Persons(PID));

ALTER:

➢ To add column ALTER TABLE table_name ADD column_name datatype;

➢ To delete column ALTER TABLE table_name Drop column column_name;

DROP:

DROP DATABASE database_name; DROP TABLE table_name; RENAME:

➢ To rename a table RENAME TABLE tbl_name TO new_tbl_name;

➢ To rename a column ALTER TABLE table_name Rename column old
column name to new name;

DML: The SQL commands that deals with the manipulation of data present in
database belong to DML or Data Manipulation Language and this includes most
of the SQL statements.

INSERT

INSERT INTO tablename (column1, column2, ..)VALUES (value1, value2,.. .); OR I

NSERT INTO table_name VALUES (value1, value2, value3, ...);

SELECT

➢ To select a entire table SELECT * FROM table_name;

➢ To select column SELECT column1, column2, ...FROM table_name;

➢ To select rows SELECT column1, column2, ...FROM table_name WHERE

condition;

UPDATE UPDATE table_name SET column1 = value1, column2 = value2,...

WHERE condition;
DELETE

➢ To delete all rows DELETE FROM table_name;

➢ To delete specific row DELETE FROM table_name WHERE condition;

GRANT

GRANT privilege_name ON Table_name TO user_name;

Eg. GRANT SELECT ON employee TO user1 REVOKE REVOKE privilege_name ON

Table_name FROM user_name;

Eg. REVOKE SELECT ON employee FROM user1;

The set operations are performed on two or more sets to obtain a combination of
elements as per the operation performed on them. In a set theory, there are
three major types of operations performed on sets, such as:

1. Union of sets (∪)

2. Intersection of sets (∩)

3. Difference of sets ( – )
Let us discuss these operations one by one.

Union of Sets

If two sets A and B are given, then the union of A and B is equal to the set that
contains all the elements present in set A and set B. This operation can be
represented as;

A ∪ B = {x: x ∈ A or x ∈ B}

Where x is the elements present in both sets A and B. Example: If set A =

{1,2,3,4} and B {6,7}

Then, Union of sets, A ∪ B = {1,2,3,4,6,7}

Venn Diagram of Union of sets

The set operations are performed on two or more sets to obtain a combination of
elements as per the operation performed on them. In a set theory, there are
three major types of operations performed on sets, such as:

1. Union of sets (∪)

2. Intersection of sets (∩)

 3. Difference of sets ( – )

Let us discuss these operations one by one.

Union of Sets

If two sets A and B are given, then the union of A and B is equal to the set that
contains all the elements present in set A and set B. This operation can be
represented as;

A ∪ B = {x: x ∈ A or x ∈ B}

Where x is the elements present in both sets A and B. Example: If set A =

{1,2,3,4} and B {6,7}

Then, Union of sets, A ∪ B = {1,2,3,4,6,7}

Venn Diagram of Union of sets

Intersection of Sets
If two sets A and B are given, then the intersection of A and B is the subset of
universal set U, which consist of elements common to both A and B. It is denoted
by the symbol ‘∩’. This operation is represented by:

A∩B = {x : x ∈ A and x ∈ B}

Where x is the common element of both sets A and B.

The intersection of sets A and B, can also be interpreted as:

A∩B = n(A) + n(B) – n(A∪B)

Where,

N(A) = cardinal number of set A, n(B) = cardinal number of set B,

N(A∪B) = cardinal number of union of set A and B.

Example: Let A = {1,2,3} and B = {3,4,5}

Then, A∩B = {3}; because 3 is common to both the sets.

Venn Diagram of Intersection of sets

Intersection of Two Sets

Difference of Sets

If there are two sets A and B, then the difference of two sets A and B is equal to
the set which consists of elements present in A but not in B. It is represented by
A-B.

Example: If A = {1,2,3,4,5,6,7} and B = {6,7} are two sets. Then, the difference
of set A and set B is given by;

A – B = {1,2,3,4,5}

We can also say, that the difference of set A and set B is equal to the intersection
of set A with the complement of set B. Hence,

A−B=A∩B’
Venn Diagram of Difference of sets

SQL Aggregate Functions

An aggregate function is a function that performs a calculation on a set of

values, and returns a single value.

Aggregate functions are often used with the GROUP BY clause of the SELECT
statement. The GROUP BY clause splits the result-set into groups of values and
the aggregate function can be used to return a single value for each group.

The most commonly used SQL aggregate functions are:

● MIN() – returns the smallest value within the selected column

● MAX() – returns the largest value within the selected column

● COUNT() – returns the number of rows in a set

● SUM() – returns the total sum of a numerical column

● AVG() – returns the average value of a numerical column Aggregate
functions ignore null values (except for COUNT()).

Nested Queries in SQL

Nested queries in SQL are a powerful tool for retrieving data from databases in a
structured and efficient manner. They allow us to execute a query within another
query, making it easier to handle complex data operations.

This article explores everything we need to know about SQL nested queries,
including types, syntax, examples, and outputs. By the end of this guide, we’ll be
able to use nested queries confidently for tasks like filtering, aggregation, and
data extraction

What Are Nested Queries in SQL?

A nested query (also called a subquery) is a query embedded within another SQL
query. The result of the inner query is used by the outer query to perform further
operations. Nested queries are commonly used for filtering data, performing
calculations, or joining datasets indirectly.

Key Characteristics:

The inner query runs before the outer query.

The result of the inner query can be used by the outer query for comparison or
as input data. Nested queries are particularly useful for breaking down complex
problems into smaller, manageable parts, making it easier to retrieve specific
results from large datasets.To better understand nested queries, we will use the
following sample tables: STUDENT, COURSE, and STUDENT_COURSE. These
tables simulate a real-world scenario of students, courses, and their enrollment
details, which will be used in the examples below.

A join is an operation that combines the rows of two or more tables based on
related columns. This operation is used for retrieving the data from multiple
tables simultaneously using common columns of tables. In this article, we are
going to discuss every point about joins.

JOIN

What is Join?

Join is an operation in DBMS(Database Management System) that combines the

row of two or more tables based on related columns between them. The main

used to perform multi-table queries. It is denoted by 𝔚.

purpose of Join is to retrieve the data from multiple tables in other words Join is

Syntax 1

R3 <- 𝔚(R1) <join_condition> (R2)

Where R1 and R2 are two relations to be joined and R3 is a relation that will hold
the result of the join operation.
Example

Temp <- 𝔚(student) S.roll==E.roll(Exam)

Where S and E are aliases of the student and exam respectively SQL JOIN
Example

Consider the two tables below as follows:

Both these tables are connected by one common key (column) i.e. ROLL_NO.

We can perform a JOIN operation using the given SQL query:

SELECT s.roll_no, s.name, s.address, s.phone, s.age, sc.course_id FROM Student
s

JOIN StudentCourse sc ON s.roll_no = sc.roll_no; Output:

Types of Join

There are many types of Joins in SQL. Depending on the use case, you can use
different types of SQL JOIN clauses. Here are the frequently used SQL JOIN types:

1. Inner Join

Inner Join is a join operation in DBMS that combines two or more tables based on
related columns and returns only rows that have matching values among tables.
Inner join of two types.

● Conditional join

● Equi Join

● Natural Join
 (a) Conditional Join

Conditional join or Theta join is a type of inner join in which tables are combined
based on the specified condition.

In conditional join, the join condition can include <, >, <=, >=, ≠ operators in
addition to the = operator.

SQL Query

SELECT R, S, T, U FROM TableA JOIN TableB ON S < T;

(b) Equi Join

Equi Join is a type of Inner join in which we use equivalence(‘=’) condition in the
join condition

(c) Natural Join

Natural join is a type of inner join in which we do not need any comparison
operators. In natural join, columns should have the same name and domain.
There should be at least one common attribute between the two tables.

2. Outer Join
Outer join is a type of join that retrieves matching as well as non-matching
records from related tables. These three types of outer join

Left outer join Right outer join Full outer join

(a) Left Outer Join

It is also called left join. This type of outer join retrieves all records from the left
table and retrieves matching records from the right table.

b) Right Outer Join

It is also called a right join. This type of outer join retrieves all records from the
right table and retrieves matching records from the left table. And for the record
which doesn’t lie in the Left table will be marked as NULL in the result Set.

(b) Full Outer Join

FULL JOIN

Creates the result set by combining the results of both LEFT JOIN and RIGHT
JOIN. The result set will contain all the rows from both tables. For the rows for
which there is no matching, the result set will contain NULL values.

JDBC

DBC stands for Java Database Connectivity. JDBC is a Java API to connect and
execute the query with the database, and processing the results. It is a part of
JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the
database. There are four types of JDBC drivers:

 JDBC-ODBC Bridge Driver


 Native Driver

 Network Protocol Driver

 Thin Driver


We have discussed the above four drivers in the next chapter.

We can use JDBC API to access tabular data stored in any relational database. By
the help of JDBC API, we can save, update, delete and fetch data from the
database. It is like Open Database Connectivity (ODBC) provided by Microsoft.
What is ODBC?

ODBC stands for Open Database Connectivity. It is an open standard Application

Programming Interface also known as API which is used for accessing a
database. The first ODBC driver was built in 1992 when Microsoft partnered with
Simba named SIMBA.DLL. With the help of an ODBC statement in a program. We
can access different files in a number of different or common databases.

History Of ODBC

The first ODBC standard was introduced in 1992 by Microsoft. This driver was a
standard model that was basically designed to unify access to different SQL
databases. Seeing the huge success of ODBC, Microsoft introduced another DB
(database) named OLE DB which was to be a broader data access standard than
ODBC. It was basically a data access standard that can be performed beyond just
SQL databases and that was extended to a different type of data source that
could represent data in form of rows and columns.

The basic plan of Microsoft was that OLE DB would take over ODBC as the most
common data access standard. Recently Microsoft introduced another data
access standard named ADO. ADO was supposed to work further than OLE DB
because ADO was more object- oriented. With so many advancements to reduce
use of ODBC doesn’t work out as ODBC has continued to be de facto data access
standard for SQL data sources. The main reason behind is its cross-platform data
access standard power. And Today also most common data access standards for
different SQL data sources continue to be ODBC and JDBC, not OLE DB or ADO.

Components of ODBC

There are 4 main components of ODBC these are as follows :

Application: This component basically calls the ODBC function and submits SQL
statements. Driver Manager: The role of this component is to load the driver for
each application. Driver: Role of this component to handle all function calls and
then submits each SQL requests to a data source. Data Source: Role of this
component to access data.

Features of ODBC

Following are some of features of ODBC

Error Codes: ODBC basically supports error code mechanism to show issues
which caused error to occur while processing SQL statements.

Attributes: Beside Error Code feature it also provides different types of functions
to get details of attributes and functions that are used in drivers.

Rich Metadata: ODBC also provides huge support to data about data also known
as metadata. ODBC also provides different functions to get data about both
functions and data types used. Interoperability: The most important feature of
ODBC is its interoperability. That means using the ODBC driver we can develop
different applications that can communicate with different DBs also known as
Database Management System and switching our application from one database
to another will not create any problem.

SQL Syntax: ODBC basically implements SQL syntax for easy understanding of
the user because SQL syntax is easily understood by the end-user. Whenever an
SQL statement is passed by the user to the ODBC driver it matches a given
statement to SQL 92 standard and converts it into a respective SQL statement
that is accepted by an underlying database.

Advantages of ODBC

Cross-Platform Compatibility: Another advantage of ODBC is its universality as

mentioned above ODBC operates equally well in any platform.

Wide Database Support: It supports large numbers of SQL databases to which

the developers can opt to use to fit their requirements.

Standardization: This is due to the fact that ODBC is an open standard that has
the effect of defining standard ways of data access across the systems.

Ease of Integration: Another advantage of ODBC is that it can be incorporated

into various development environments and RAD tools.

Flexibility: There is database flexibility meaning that developers can consider

different databases without altering the application code highly.

Disadvantages of ODBC

Despite having lots of advantages and features ODBC also possess some
disadvantages too these are as follows :
Slow with Large Databases: As the size of databases increases, the speed of
ODBC reduces.

Servers Not Standardized: In ODBC since most of the work is done by clients or
users, it is very difficult to scale and also these ODBC drivers are also not
standardized. Because of which clients maintain their own driver, naming tables
which creates a problem for management of large sites.

Complicated to Build: These ODBC drivers are complicated to build and also
complicated to maintain.

Depends on Framework: As ODBC specification specifies only application

protocol so it basically inherits features of the framework in which it is used on.
Thus we can say that reliability depends on implementation of request/response
protocol of the underlying framework that is being used.

Embedded SQL

Embedded SQL is a powerful method that allows the integration of high−level

programming languages with database management systems (DBMS). It acts as
a bridge between applications and databases which helps in data manipulation
and communication. Various database management systems offer embedded
SQL, giving developers the freedom to select the one that best serves their
requirements.

Popular DBMS that support Embedded SQL are Altibase, IBM Db2, Microsoft SQL
Server, Mimer SQL, Oracle Database, PostgreSQL, and SAP Sybase.

Need of Embedded SQL

The goal to make database interactions simpler for application developers and
end users determines the demand for embedded SQL. Users often enter values
or submit requests while interacting with an application, which requires
accessing and changing data contained in a database. Developers may conduct
these database tasks without burdening the user with SQL complexities by
integrating SQL queries directly into the application code.

Structure of Embedded SQL

Advantages of Embedded SQL

Optimized Performance: Since embedded SQL allows developers to have control

over the SQL statements, they can optimize the queries for better performance.
By analyzing the database schema and query execution plans, developers can
fine−tune the SQL statements to ensure efficient data retrieval and
manipulation.

Tight Integration: Embedded SQL enables seamless integration of SQL operations

within the application code. This integration allows developers to leverage the
full power of SQL while using the high−level programming language for other
application logic. It eliminates the need for separate SQL scripts or external files,
making the code more manageable and maintainable.
Data Consistency: By embedding SQL statements within the application code,
developers can ensure data consistency throughout the application. Changes
made to the database structure or table schema can be reflected in the
embedded SQL code, reducing the chances of data inconsistencies.

Database Security: Embedded SQL allows developers to incorporate

authentication and security measures directly into the application. This
integration enables the implementation of fine−grained access controls,
ensuring that only authorized users can perform database operationsTriggers

What are SQL Triggers?

SQL triggers are stored procedures that automatically execute in response to

certain events in a specific table or view in a database. They are used to
maintain the integrity of the data, enforce business rules, and automate tasks.
We can set triggers to fire before or after an INSERT, UPDATE, or DELETE
operation. Understanding and implementing SQL triggers can significantly
enhance your database management skills.

Syntax and structure of SQL triggers

The basic syntax of an SQL trigger includes the creation statement, the event
that activates the trigger, and the SQL statements that define the trigger’s
actions. Here’s a general template for creating a trigger. The following syntax
will work in many common databases, such as MySQL and Oracle.Operations
with SQL triggers

SQL triggers allow for various operations that help maintain data consistency and
automate processes, for which creating, modifying, deleting, and displaying
triggers are essential operations. Here’s how you can perform these tasks:

1. Creating triggers

Creating a trigger involves defining when it should be executed and what actions
it should perform. The example above shows how to make a trigger that logs
updates to an employees table.

2. Modifying and deleting triggers

To modify a trigger, you must drop the existing one and create a new one with
the desired changes. Here’s how you can do it:
Deleting a trigger is more straightforward. Use the following command to drop a
trigger:

3. Displaying existing triggers

You can view existing triggers in a database using specific queries based on your
SQL database management system (DBMS). For instance, in MySQL:

Types of SQL triggers

There are several main types of SQL triggers. These trigger types are grouped
according to the specific events they respond to and the operations they
perform.

● DML Triggers (Data Manipulation Language): DML Triggers include AFTER

Triggers, which execute after an operation, BEFORE Triggers, which execute
before an operation, and INSTEAD OF Triggers, which replace the operation with
the trigger’s code.

● DDL Triggers (Data Definition Language): DDL triggers are fired in

response to DDL events such as CREATE, ALTER, and DROP statements. They are
useful for controlling schema changes, auditing database modifications, and
enforcing security policies.

● Logon Triggers: Logon triggers are usually executed in response to a

LOGON event. They are typically used to control or monitor user sessions,
enforce logon policies, or log user activity. For example, a logon trigger can limit
access to certain hours or log each user’s login time and IP address.
SQL Triggers

A trigger is a stored procedure in adatabase that automatically invokes

whenever a special event in the database occurs. By using SQL triggers,
developers can automate tasks, ensure data consistency, and keep accurate
records of database activities. For example, a trigger can be invoked when a row
is inserted into a specified table or when specific table columns are updated.

In simple words, a trigger is a collection of SQL statements with particular names

that are stored in system memory. It belongs to a specific class of stored
procedures that are automatically invoked in response to database server
events.

Syntax

Create trigger [trigger_name]

[before | after]

{insert | update | delete}

On [table_name]
FOR EACH ROW

BEGIN

END;

Key Terms

• Trigger_name: The name of the trigger to be created

• BEFORE | AFTER: Specifies whether the trigger is fired before or after the
triggering event (INSERT, UPDATE, DELETE).

• {INSERT | UPDATE | DELETE}: Specifies the operation that will activate the
trigger.

• Table_name: The name of the table the trigger is associated with.

• FOR EACH ROW: Indicates that the trigger is row-level, meaning it

executes once for each affected row.

• Trigger_body: The SQL statements to be executed when the trigger is

fired.

Why Should You Use SQL Triggers?

Using SQL triggers brings several benefits to the table:

• Automation: Triggers handle repetitive tasks, saving our time and effort.

• Consistency & Data Integrity: Automatically enforcing rules ensures that

our data remains clean and accurate.

• Business Rules Enforcement: Triggers can help ensure that changes to our
database follow your business logic.

• Audit Trails: Track changes automatically, making it easier to monitor and

record data updates.

Now that you know why triggers are important, let’s look at how to create and
use them effectively.

Types of SQL Triggers

Triggers can be categorized into different types based on the action they are
associated with:

1. DDL Triggers

The Data Definition Language (DDL) command events such as Create_table,

Create_view, drop_table, Drop_view, and Alter_table cause the DDL triggers to be
activated. They allow us to track changes in the structure of the database. The
trigger will prevent any table creation, alteration, or deletion in the database.

Example: Prevent Table Deletions

CREATE TRIGGER prevent_table_creation

ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE

AS

BEGIN

PRINT ‘you can not create, drop and alter table in this database’;

ROLLBACK;

END;

2. DML Triggers

DML triggers fire when we manipulate data with commands like INSERT, UPDATE,
or DELETE. These triggers are perfect for scenarios where we need to validate
data before it is inserted, log changes to a table, or cascade updates across
related tables.

Example: Prevent Unauthorized Updates Let’s say you want to prevent users
from updating the data in a sensitive students table. We can set up a trigger to
handle that:

CREATE TRIGGER prevent_update

ON students

FOR UPDATE

AS

BEGIN
PRINT ‘You can not insert, update and delete this table i’;

ROLLBACK;

END;

3. Logon Triggers

These triggers are fired in response to logon events. Logon triggers are useful for
monitoring user sessions or restricting user access to the database. As a result,
the PRINT statement messages and any errors generated by the trigger will all
be visible in the SQL Server error log.

Authentication errors prevent logon triggers from being used. These triggers can
be used to track login activity or set a limit on the number of sessions that a
given login can have in order to audit and manage server sessions.

Example: Track User Logins

CREATE TRIGGER track_logon

ON LOGON

AS

BEGIN

PRINT ‘A new user has logged in.’;

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

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table.

INNER JOIN
The INNER JOIN keyword selects records that have matching values in both
tables.
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

LEFT JOIN:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the
matching records from the left table (table1). The result is 0 records from the left
side, if there is no match.

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName

FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in left
(table1) or right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

SQL Commands
oSQL commands are instructions. It is used to communicate with the database.
It is also used to perform specifc tassss functionss and queries of data.
oSQL can perform various tasss lise create a tables add data to tabless drop
the tables modify the tables set permission for users.
Types of SQL Commands
There are fve types of SQL commands: DDLs DMLs DCLs TCLs and DQL.
1. Data Definition Language (DDL)
oDDL changes the structure of the table lise creating a tables deleting a tables
altering a tables etc.

oAll the command of DDL are auto-committed that means it permanently save
all the changes in the database.
Here are some commands that come under DDL:
oCREATE
oALTER
oDROP
oTRUNCATE
a. CREATE It is used to create a new table in the database.
Syntax:
CREATE TABLE TABLE_NAME
(
COLUMN_NAME1 DATATYPES(size)s
COLUMN_NAME2 DATATYPES(size)s
--------------
COLUMN_NAMEN DATATYPES(size)s
);
Example:
CREATE TABLE EMP
(
EMPNo VARCHAR2(20)s
EName VARCHAR2(20)s
Job VARCHAR2(20)s
DOB DATE
);

b. DROP : This statement is used to drop an existing database. When you use
this statement,
Complete information present in the database will be lost.
Syntax
 DROP DATABASE DatabaseName;
Example
DROP DATABASE Employee;
The ‘DROP TABLE’ Statement
This statement is used to drop an existing table. When you use this statement,
complete
Information present in the table will be lost.
Syntax
DROP TABLE TableName;
Example
DROP Table Emp;
c. ALTER
This command is used to delete, modify or add constraints or columns in an
existing
Table.
The ‘ALTER TABLE’ Statement
This statement is used to add, delete, modify columns in an existing table.
The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN
You can use the ALTER TABLE statement with ADD/DROP Column
command
According to your need. If you wish to add a column, then you will use the ADD
Command, and if you wish to delete a column, then you will use the DROP
COLUMN
Command.
Syntax
ALTER TABLE TableName ADD ColumnName Datatype;
ALTER TABLE TableName DROP COLUMN ColumnName;
Example
--ADD Column MobNo:
ALTER TABLE Emp ADD MobNo Number(10);
--DROP Column MobNo:
ALTER TABLE Emp DROP COLUMN MobNo ;

The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN

This statement is used to change the datatype of an existing column in a table.

Syntax

ALTER TABLE TableName ADD COLUMN ColumnName Datatype;

Example

--Add a column DOB and change the data type to Date.

ALTER TABLE Emp ADD DOB date;

d. TRUNCATE

This command is used to delete the information present in the table but does not
delete

The table. So, once you use this command, your information will be lost, but not
the

Table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

 2. Data Manipulation Language

O DML commands are used to modify the database. It is responsible for all form

Of changes in the database.

O The command of DML is not auto-committed that means it can’t permanently

Save all the changes in the database. They can be rollbacs.

Here are some commands that come under DML:

O INSERT

O UPDATE

O DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data

Into the row of a table.

Syntax:

INSERT INTO TABLE_NAME

(col1s col2s col3s.... col N)

VALUES (value1s value2s value3s .... valueN);

Or

INSERT INTO TABLE_NAME

VALUES (value1s value2s value3s .... valueN);

For example:

INSERT INTO EMP(ENamesJob) VALUES (“SCOTT”s “MANAGER”);

b. UPDATE: This command is used to update or modify the value of a

Column in the table.

Syntax:

UPDATE table_name SET column1= values column2= values

columnN = value WHERE CONDITION;

For example:

UPDATE Emp SET Ename = ‘SMITH’ WHERE EmpNo = ‘1003’;

c. DELETE: It is used to remove one or more row from a table.

Syntax1:

DELETE FROM table_name;

Syntax1
DELETE FROM table_name WHERE condition;

Example1: Delete all rows from emp table

DELETE FROM Emp;

Example2: Delete all rows from emp table whose Ename is SCOTT

DELETE FROM EName WHERE EName=”SCOTT”;

3. Data Control Language

DCL commands are used to grant and tase bacs authority from any database

User.

Here are some commands that come under DCL:

O Grant

O Revose

a. Grant: It is used to give user access privileges to a database.

Example

GRANT SELECTs UPDATE ON MY_TABLE TO SOME_USERs ANOTHER_USER;

b. Revoke: It is used to tase bacs permissions from the user.

Example

REVOKE SELECTs UPDATE ON MY_TABLE FROM USER1s USER2;

4. Transaction Control Language

TCL commands can only use with DML commands lise INSERTs DELETE and

UPDATE only.

These operations are automatically committed in the database that’s why

They cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

O COMMIT

O ROLLBACK

O SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the

Database.

Syntax:

COMMIT;
Example:

DELETE FROM CUSTOMERS WHERE AGE = 25;

COMMIT;

b. Rollback: Rollbacs command is used to undo transactions that have not

Already been saved to the database.

Syntax:

ROLLBACK;

Example:

DELETE FROM CUSTOMERS WHERE AGE = 25;

ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction bacs to a certain point

Without rolling bacs the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

SELECT

This statement is used to select data from a database and the data returned is
stored in

A result table, called the result-set.

Syntax

SELECT Column1, Column2, ...ColumN FROM TableName;

--(*) is used to select all from the table

SELECT * FROM table_name;

To select the number of records to return use:

SELECT TOP 3 * FROM TableName;

Apart from just using the SELECT keyword individually, you can use the following

Keywords with the SELECT statement:

O DISTINCT

O ORDER BY
O GROUP BY

O HAVING Clause

O INTO

The ‘SELECT DISTINCT’ Statement

This statement is used to return only different values.

Syntax

SELECT DISTINCT Column1, Column2, ...ColumnN FROM TableName;

SELECT DISTINCT MobNo FROM Emp;

Example

The ‘ORDER BY’ Statement

The ‘ORDER BY’ statement is used to sort the required results in ascending or

Descending order. The results are sorted in ascending order by default. Yet, if
you wish

To get the required results in descending order, you have to use the DESC
keyword.

Syntax

SELECT Column1, Column2, ...ColumnN FROM TableName

ORDER BY Column1, Column2, ... ASC|DESC;

Example

Select all employees from the ‘Emp’ table sorted by EmpNo:

SELECT * FROM Emp ORDER BY EmpNo;

 Select all employees from the ‘Emp table sorted by EmpNo in Descending
order:

SELECT * FROM Employee_Info ORDER BY EmpNo DESC;

Select all employees from the ‘Empl’ table sorted by EmpNo and EName:

SELECT * FROM Emp ORDER BY EmpNo, EName;

/* Select all employees from the ‘Emp’ table sorted bsoEmpNo in Descending
order and Ename in

Ascending order: */

SELECT * FROM Emp ORDER BY EmpNo ASC, Ename DESC

The ‘GROUP BY’ Statement

This ‘GROUP BY’ statement is used with the aggregate functions to group the
result-set

By one or more columns.

Syntax
SELECT Column1, Column2,..., ColumnN FROM TableName

WHERE Condition GROUP BY ColumnName(s) ORDER BY ColumnName(s);

Example

To list the number of employees from each city.

SELECT COUNT(EmpNo), City FROM Emp GROUP BY City

The ‘HAVING’ Clause

The ‘HAVING’ clause is used in SQL because the WHERE keyword cannot be used

Everywhere.

Syntax

SELECT ColumnName(s) FROM TableName WHERE Condition GROUP BY

ColumnName(s) HAVING Condition ORDER BY ColumnName(s);

Example

To list the number of employees in each city. The employees should be sorted
high to low and only

Those cites must be included who have more than 5 employees:*/

SELECT COUNT(EmpNo), City FROM Emp GROUP BY City HAVING

COUNT(EmpNo) > 2 ORDER BY

COUNT(EmpNo) DESC;
The ‘SELECT INTO’ Statement

The ‘SELECT INTO’ statement is used to copy data from one table to another.

Syntax

SELECT * INTO NewTable IN ExternalDB FROM OldTable

WHERE Condition;

EExample
To create a backup of database ‘Employee’
SELECT * INTO EmpNo FROM Emp;

Views in DBMS

17

A view in SQL is a virtual table that is based upon the result-set of an SQL
statement

A view will also have rows and columns just like a real table in a database

Simply a view is nothing but a stored SQL Query

A view can contain all the rows of a table or specific rows based on some
condition
SQL functions conditions and join statements to a view and present the data just
like the data is produced from a single table
Views in DBMS

Views
 Simple View
 Complex View

Creating a view

A view is created by selecting fields from one or more tables present in a

database

Syntax

CREATE VIEW view_name AA

SELECT column1, column2,
FROM table_name
WHERE condition;

Note:
Whenever a user creates a view, database engine recreates the data using the
views SQL statement i.e. view always shows upto date data
Consider the tables Student Details and StudentMarks

StudentDetails
S_ID NAME ADDRESS
1 Harini Kolkata
2 Preity Hyderabad
3 Divya Chennai
4 Kushi Mumbai
5 Amitha Bangalore
StudentMarks

ID NAME MARKS AGE

1 Harini 96 20
2 Manisha 90 19
3 Divya 94 21
4 Kushi 92 19
5 Amitha 95 21

StudentDetails
S_ID NAME ADDRESS
1 Harini Kolkata
2 Preity Hyderabad
3 Divya Chennai
4 Kushi Mumbai
5 Amitha Bangalore

StudentMarks
ID NAME MARKS AGE
1 Harini 96 20
2 Manisha 90 19
3 Divya 94 21
4 Kushi 92 19
5 Amitha 95 21

Simple Views in DBMS: Creating a view from a single table

In this example, we will create a view named as Details View from a single table

StudentDetails
Re

CREATE VIEW DetailsView AS SELECT NAME, ADDRESS FROM StudentDetails

WHERE S ID < 5;

The data present in a view can be seen just like a normal table select query

SELECT * FROM DetailsView;

Output:

NAME ADDRESS
Harini Kolkata
Preity Hyderabad
Divya Chennai
Kushi Mumbai

Complex view: Creating a view from multiple tables

In this example will create a view named MarksView by taking data from both the
table’s student details and student marks

To create a View from multiple tables just simply include multiple tables in the
SELECT statement.

CREATE VIEW MarksView AA

SELECT StudentDetails.NAME, Student Details. AD
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME

To display data of View Marks:

SELECT * FROM MarksView;

Output:

NAME MARKS ADDRESS

Harini 96 Kolkata
Divya 94 Chennai
Kushi 92 Mumbai
Amitha 95 Bangalore

Deleting views in DBMS

You can simply delete a view by using the Drop statement
Not That view is not used anymore

Syntax:

DROP VIEW view_name;

Example
DROP VIEW MarksView;

Updating views
Views are updated only if certain conditions are met otherwise if any one of the conditions
are not met views will not be updated

Criteria for View Updating

The select statement used in the create view statement should not include group by cla or
order by clause

The select statement must not contain distinct keyword

A view should not be created from nested or Complex queries
A view should be created from a single table but if the view is created from more
than one table then it is not allowed for updating

CREATE OR REPLACE VIEW

Create or replace view statement is used to add or remove fields from existing
views

Syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT column1,coulmn2,..
FROM table_name
WHERE condition;

Update the view MarksView and add the field AGE to this View from
StudentMarks Table,

CREATE OR REPLACE VIEW MarksView AS

SELECT StudentDetails.NAME, Student Details. AD
FROM StudentDetails, StudentMarks

WHERE StudentDetails.NAME = StudentMarks.NAM

Fetch all the data from MarksView now as:

SELECT * FROM MarksView;

Output

NAME ADDRESS MARKS AGE

HARINI Kolkata 96 20
Divya Chennai 94 21
Kushi Mumbai 92 19
Amitha Bangalore 95 21

Inserting a row into a view

We can use insert into statement of SQL to insert a row in a view just like
inserting a row in an ordinary table
Syntax:
INSERT view_name(column1, column2 columna VALUES(value1, value2,
value3..);

Example

INSERT INTO DetailsView (NAME, ADDRESS) VALUES(“Preity”, “Hyderabad”);

Fetch all the data from Details View now as,

SELECT * FROM DetailsView;

Output

‫ره‬

NAME ADDRESS
Harini Kolkotta
Divya Chennai
Kushi Mumbai
Amitha Bangalore
Preity Hyderabad

Deleting a row from a view

A row in a view can be deleted just like simr deleting rows from a Table using delete
statement
But remember a row in a view can be deleted
Only if the row is actually deleted in the original table from which it is created

Syntax:

DELETE FROM view_name

WHERE condition;

Example

DELETE FROM DetailsView WHERE NAME=”Preity”;

Fetch all the data from Details View now as,

SELECT * FROM DetailsView;

Output:

NAME ADDRESS
Harini Kolkotta
Divya Chennai
Kushi Mumbai
Amitha Bangalore
Preity Hyderabad

Advantages and disadvantages of views

Advantages

Enforce Business Rules: By placing complicated or misunderstood business logic

into the view, you can be sure to present a unified portrayal of the data which
increases use and quality.
Consistency: Once defined their calculations are referenced from the view rather
than being restated in separate queries. This makes for less mistakes and easier
maintenance of code.

Security: For example, you can restrict access to the employee table, that
contains social security numbers, but allow access to a view containing name
and phone number.
Simplicity: Databases with many tables possess complex relationships, which
can difficult to navigate if you aren’t comforta using Joins.

Space: Views take up very little space, as the data is stored once in the source
table.
LLimitation
Modifications: Not all views support INSERT, UPDATE, or DELETE operations.
Complex multi-table views are generally read-only.
Performance: Hugely complex job for the database engine. That is because each
time a view is referenced, the query used to define it, is rerun.