WELCOME to All In 1 Tutorial Blog

Data:

Collection of raw facts and figures is called data.

Base:

A place where we place, write OR put the data is called base.

Management:

Arrangement of data is called management. Like

Insertion.
Deletion.
Update.

System:

Where we make database means Software & Hardware.

DBMS:

Database Management System is a collection of facts and figures in which we inserts, delete and update data in the system. OR manage the data is known as management system. Collection of different tables or files is known as database.

DBMS is a structure format for any data.

Database management system is used to store modify and extract data. Collection of program that enables you to enter OR organizes and selects the data from database management system.

Cell:

A cell is an intersection of Rows and Columns.

Record:

Collection of attributes OR fields is called record. Topple is the second name of record.

Column:

Column specifies the single attribute.

Entity:

An object which we want to get about specific data.

Field:

It contains a single attribute about an entity.

File:

Collection of records is called file.

Types of files:

Directory File:

Directory file holds the information of whole tables.

Process of database:

To enter data.
To manage data.
To extract data.

SQL: (Structured Query Language)

It is a set of rules OR set of queries OR set of processing which is used to store modify and extract the data is called SQL.

Database Models:

Database models are the techniques use to arrange the different data types OR tables in specific OR required format.

Hierarchical Model:

This model is like a par-amid type model. Different child nodes have only one parent.

Advantages of Hierarchical Model:

It is a simple model.
Easy to manage the security of database and strong for security.
Efficiency data flow from top to bottom.
Time will shortly when its speed is increase.

Disadvantages of Hierarchical Model:

Implementation is difficult.
Lack of standard.
Data management and hardware, software difficult to manage OR complex.
Its implementation is limited can not use in large organizations.

Network Model:

It is a standard form of hierarchical model. Child node is used to store more than one parent node.

In 1969 CODYSAL (Conference on data system language) gives a description.

We can access child node through pointers and pointers stores the address of that node. Each and every child node has multiple parent nodes. It is also known as CODYSAL model.

Advantages of Network Model:

It supports to more relationship types as compare to hierarchical model.
Data accessibility is fast.
Data integration is easy as compare to hierarchical model.
Data is dependent.

Disadvantages of Network Model:

Implementation is difficult as compare to hierarchical model OR others.
Lack of structure independence.

Relational Model:

Relation is a second name of table. Relation is used to link between two or more than two tables. We use two keys to create relation between tables.

Advantages of Relational Mode:

Its structure is dependent.
It improves conceptual OR simplicity.
It is very easy to design and implementation OR management.
Relational model is more powerful then other models.
Consistency, integrity is better.
DB security is easily too maintained.

Disadvantages of Relational Model:

It is very costly due to high requirement of hardware, software and management.

Primary Key:

Primary key always occur in Master Table.

Properties of primary key:

Not Null (means not empty).
Unique (means different to each other).
Auto Index (means automatic initialize).

Foreign Key:

Foreign key is used to create the relation between tables. Foreign key also has the same properties as primary key.

Degree of relationship:

There are two types of degree.

Cardinality.
Optionality.

Cardinality:

Cardinality means maximum relationship. Like

One---------------One

One---------------Many

Many------------One

Many-----------Many

Symbol to show relationship:

Optionally:

It wills always the zero (0). 0 is the symbolic representation of optionally.

Database Model:

In database models we have two basic types

Conception-al Model:

In conception-al model we have 1-M, M-1, M-M, 1-1, and 0.

Implementation Model:

In implementation model we have N/M, H/M, and R/M.

E-R Model (Entity relational model):

Entity relational model is a conception-al and logical model.

Object relational model:

Object is a key where we access the data and functions of the program. Relationship between two entities is called object relational model.

ORD (Object relational database).

ORDMS (Object relational database management system).

Object Oriented Database (OOD):

Where database is linked to OOP then it is called object oriented database.

It also supports the audio-video interaction.

Advantages of Object Oriented database:

Database integrity.
Data structure and independence.

Disadvantages of Object Oriented database:

Lack of object oriented database management system.
Lack of qualified programmer for the management of object oriented database.

Model:

Physical and logical description of table is known as model.

Metadata:

Data about data is known as metadata.

Data that describes the properties of another data is called metadata.

RNO	Name	F-Name	Address
1	ABC	XYZ	fffffffffff
2
3

The information that collects about the roll no that is which kind of data is present in that column is known as Metadata.

Diagram of DBMS:

Data base is working like operating system, which is use to link between applications.

Database functions:

Data dictionary management. (Data dictionary contains about the data, about the table and about the inserts etc).
Data security.
System analysis. (Means how software is created).
Multiple user access control.
Back-up and Recovery:

There are two kind of back-up.

Remote Back-up.
Store Back-up.

Remote Back-up:

It creates back-up at same system.

Store Back-up:

Store back-up at another place at another server.

Recovery (To activate the Back-up data mean access that data).
Data integrity management (integration means to join).
Database language.
Database communication interface (The techniques which are used to transfer the data from one system to another system).

Types of DBMS:

There are three types of DBMS.

1) Centralized DBMS.

2) Parallel DBMS.

3) Distributed DBMS.

Centralized DBMS:

The database which is used at specific place like school. Data will place at central position at one system. The system send request to server for access of any data. It is easy to understand, easy to install.

Parallel DBMS:

It is used to load, sharing. In this we connect two or more than two servers to share the load. Time management is easy.

Distributed DBMS:

In this data accessibility is easy. Its processing is fast and square maintenance.

Draw backs:

Troubleshooting is difficult, management is difficult.

Distributed DBMS:

In this data is available at different places. Better reliability and data availability. Increases performances. It reduces time response. Its communication cost is low.

Types of DDBMS:

It has two main types.

Communication is difficult.

The problem of language because each city has different software and different OS, so we use conversion protocol.

Conversion Protocol:

Conversion protocol is a set of rules, which converts the language.

Advantages of Heterogeneous DBMS:

It is easy to install.
Easy to manage.
Error detection is easy.

Disadvantages of Heterogeneous DBMS:

Processing level is low because of conversion protocol.
Data availability and reliability is low.

Homogeneous DBMS:

Similar hardware and similar software. In this no need to use conversion protocol.

Advantages of HDBMS:

High performance.
Time constant decrease.
Reliability and availability is fast.

Disadvantages of Homogeneous DBMS:

Difficult to manage.
Installation is difficult.

Data dictionary:

Data dictionary means the information about data.

Level of DBMS:

Real world data:

It is a raw form data that is collect to create database. All information about bank OR employ.

Metadata:

Data about data is known as metadata. For example account is an entity and its number is metadata.

Data occurrence:

The implementation of data is known as data occurrence in the form of table.

Data dictionary:

It holds all the information about data, about tables, deletion, manipulation all information is stored in data dictionary.

Advantages:

It stores information about the data at central position.
Database will be at server OR central position.
It identifies the redundancy. It means duplication and data consistency (If we update one table other table will also update). This is consistency.
Data asset (data asset is an important property about data).

Component of Distributed DBMS:

Data communication:

Flow of data from one node to another is called communication. It contains the whole information about protocol which is responsible for the transfer of data it checks the dead or Alive system.

Local DBMS:

It is a part of DDBMS. The station which works locally and also connect with other. It works individually OR locally.

Global data dictionary:

It contains the whole information about globally. Every person cannot access. Only authorized person can access. It contains the information about total number of branches OR node, which is present in a DDBMS. Total number of synonyms, total number of users. Integrity constrains (mean check or limitation) it means connectivity of two or more tables and which limit or check are applied on it.

Entity Relationship Model:

Entity:

A single table which contains the data is called entity.

Relationship:

To create a link between two or more then two table is called relationship.

Model:

A way to represent the data is called model.

Entity Relationship Diagram:

ERD is used to show the relationship between two or more than entity.

Entity Type:

It contains multiple records. Entity means single record, entity type mean table.

Symbolic representation:

Weak entity:

The entity which can break easily is known as weak entity.

Strong entity:

The entity which cannot break easily is known as strong entity.

Attribute:

A property of any object is called attribute.

Ellipse Symbol:

Ellipse symbol is used to show the properties OR attribute.

Example:

Super Key:

It is the part of entity relationship diagram. Properties of super key.

Super key will be unique.
Supper key will not null.

Properties of Primary Key:

Unique.
Not null.
Auto index.

Foreign key:

It is used to link between two or more than two entity. It is also known as reference key.

Degree of relationship:

Unary Relationship.
Binary Relationship.
Ternary Relationship.

Relationship

Candidate Key:

It contains all/ Whole properties of super key. Candidate key is a super key with out any sub super key. It does not repeat attribute.

Types of primary key:

Composite key.
Compound key.

Composite key:

Taken two column as a primary key from the same table OR one table is called composite key.

Compound key:

Taken two primary key from column of one table OR more than one OR from different table is called composite key.

Important:

That:

P.K=S.K (Primary Key = Secondary Key)

But

S.K≠ P.K

Difference between file processing & DBMS:

In file processing every thing is in hard format.
DBMS is DBMS

Schema:

Logical or physical or properties or attribute about the data which is stored in place is known as schema.

Properties:

· Properties & attribute OR description about any data is schema.

· Properties are defined in schema.

· Meta data is a property of schema.

Applications of database:

Clustering:

· Used to load sharing.

Used to decrease time period.

Database Administrator (DBA):

DBA has complete control of DBMS. Tuning performance data entry, maintenance is a responsibility of DBA.

Responsibility of DBA:

Description of schema.
Grant access of different users.
Routine maintenance.
Back-up.
Recovery routine.
Memory management & monitor disk space.
Monitor job running.
DBA should have ability OR capability for limited troubleshooting.
DBA should have capability to run of DBMS engine.
DBA designing job is that, row of attribute, column of attribute.
Identify the user requirements.
End- user training, services & coordination.
DBA is responsible for DB security.
DBA is responsible for developing & monitoring the data dictionary.
DBA is enable to create & loading DB.
DBA is enabling to hide some data from users.
DBA is enabling to develop & enforce data standard.
Training users.
Keep track of changes.

Types of organization:

Sequential (store the data on tape).
Index Sequential (store on disk).
Direct (store on disk).

Sequential:

Sequential is also batch processing.

Index Sequential:

Index is also using batch processing.

Direct:

Direct used in a line processing. Prime NO technique for direct file organization.

Batch Processing:

To make a batch for two OR more than two similar job.

Design Tools:

Design tool is used to check the flow of data. It has two parts.

DFD (Data Flow Diagram).
Data dictionary.

DFD:

DFD is a design tool to show the flow of data with in any organization we can generate the DFD by using CASE tools.

CASE (Computer Aided Software Engineering) tool is used to create DFD.

CAD (Computer Aided Design) it is used to create DFD. We just define our analysis to CAD it automatically creates DFD. It is a part of CASE.

Pictorial representation:

Context DFD:

It contains main information.

0-DFD:

It contains the information more then minor.

1-DFD:

It contains the complete information. Level 1 is also known as detailed DFD.

Context DFD:

Data Architecture:

It is a tool OR technique to present the data base to show user, programmer.

It is a separator way for use to represent the DB physically & conceptual.

Database Architecture

Internal View / Physical View:

In the internal view the system store the data operating system responsible for internal view. Data store into track SQL also manage also physical view.

Data may be stored on hard-disk floppy, CDs etc.

Conceptual View/ Logical View:

Conceptual view is the form of rows and columns OR ERD creates the relationship. Properties of tables all these are in conceptual view.

To represent conceptual view.

Table: data is placed in tables.
In the form of ERD and ERD is a per-factional view to create the view on table.

External View:

View on table, means whole table is not viewable for user only some part of table is displaying for user.

Relation:

When we link two or more than two tables is called relation.

Algebra:

Calculation based on some relations is called Algebra.

Relational Algebra:

When we apply calculation on some relations is called relational Algebra.

Oracle based on relational Algebra. Relational Algebra based on entity relationship diagram (ERD). Relational Algebra is basically theoretically Model. Language with operators is called Relational Algebra. SQL is used for Relational Algebra. In relational algebra we use different kinds of operators. There are 8 basic operations in Relational Algebra. Which are categorized as following?

Advantages of Relational Algebra:

Relational Algebra works like pseudo code.
Relational Algebra work as symbolic representation. Which are used to work on single, two or multiple relations or tables?

Select:

Select operator is used to get data or retrieve the data from relation OR table.

Symbolic representation:

Examples:

(Student)

Project Operator:

It is a vertical subset of table is known as projection. Used to extract data from column. Used to remove the repeating attribute. In projection we select column. Projection will also apply on single table.

Selects vertically values.
Removes duplication.
Place data on second table.

Symbolic representation:

Join operator:

To combine to or more than two entities is known as join operator.

Join operator always apply on two tables.

Horizontally joining of table:

1	2	3

4	5	6

1	2	3	4	5	6

Vertically joining of table:

1	2	3

4	5	6

For vertically joining in both numbers of columns must be same.

1	2	3


4	5	6

For merging we use union, intersection and minus operator.

Types of joining:

Product.
Theta Join.
Equi join.
Natural Join.
Semi Join.
Left Outer Join.
Right Outer Join.

Product:

Cartition product will also apply on two entities.

Example:

A= {a, b, c}, B= {1, 2}

A x B= {(a, 1), (a, 2), (b, 1), (b, 2), (c, 1), (c, 2)}

Example:

(Student) (Class)

Roll-No	Name
1	A
2	B
3	C

Roll-No	Class
2	BS
3	MS
1	ICS

Student x Class

A x B

In both tables one join must same in both tables Roll-No column is same.

Roll-No	Name	Roll-No	Class
1	A	2	BS
1	A	3	MS
1	A	1	ICS
2	B	2	BS
2	B	3	MS
2	B	1	ICS
3	C	2	BS
3	C	3	MS
3	C	1	ICS

Theta Join:

Theta is a reserved word and

Greece

word used to check any kind of relationship between two tables.

We use relational operators. >, <, <=, >=.

Theta will always apply on the product tables. E.g. (Student x Class).

Example:

1	A	2	BS
2	B	2	BS
3	C	2	BS

(Student)

Class= BS

Equi operator:

Equi operator is used to join the common column, and is used to check the common values between two similar columns in a cartition product, will always apply on two identical columns.

Example:

1	A	2	BS
2	B	2	BS
3	C	2	BS

(Student)

Student, R.No= class, R.No

Natural Join:

The projection on Equi join is known as Natural Join.

RNO	Name	Class
1	A	ICS
2	B	BS
3	C	MS

Select RNO, Name, Class from student x class where Student, RNO= class, RNO

This query shows Natural Join.

Semi Join:

It will apply on two tables, both tables’ coordinates. If A and B are table then semi join of A by B can be found by

1. Taking natural join of A and B.

2. Projecting results on to the attributes.

Semi join = Natural join

Example:

Union Intersection & Minus:

Union:

Merging of two tables is known as union.

A B

RNO	Name
1	A
2	B
3	C

RNO	Name
2	B
4	D
1	A

(A U B)

RNO	Name
1	A
2	B
3	C
4	D
5	E

Intersection:

A B

RNO	Name
1	A
2	B
3	C

RNO	Name
2	B
4	D
1	A

(A ∩ B)

RNO	Name
1	A
2	B

Minus:

A-B B-A

RNO	Name
4	D
5	E

RNO	Name
3	C

Normalization:

To remove the complexity from the table is known as normalization.

To remove the complexity of the table in logical views is known as normalization.

Method of improvement of designs is called normalization.

Features of Normalization:

v It converts the table into simple format.

v Compressing tables.

v Error detection.

v Simplicity form.

v Is used to reduce the redundancy from the table.

v Is used to remove anomslies from the table.

v Normalization is used to get data into simple form that truly reflects.

v In normalization initial grouping of data occur.

v Normalization rule will be applied to remove anomalies.

v Actual data in re-arranged form.

v Separate entity type.

v Their attributes.

v Relationship between them.

Normalized:

Process of normalization is called normalization.

Normal forms are used for normalization.

Normal Forms:

1) 1NF

2) 2NF

3) 3NF

4) 4NF

5) 5NF

Normal Forms:

1) 1NF

2) 2NF

3) 3NF

4) 4NF

5) 5NF

1NF:

A relation is in 1NF if and only if it contains number of repeating attribute OR group of attribute.

Example:

(Report Format)

Roll_NO	NAME	ADDRESS	CLASS	MONTH	FEE
1	Faizan	QTA	CS2	JAN	2000
-	-	-	-	FEB	3000
-	-	-	-	MARCH	3000
2	Umer	KHI	CS3	JAN	3000
-	-	-	-	FEB	2000
3	Shoaib	LHR	CS4	JAN	2000

1^St Procedure of 1NF:

Roll_NO	NAME	ADDRESS	CLASS	MONTH	FEE
1	Faizan	QTA	CS2	JAN	2000
1	Faizan	QTA	CS2	FEB	3000
1	Faizan	QTA	CS2	MARCH	3000
2	Umer	KHI	CS3	JAN	3000
2	Umer	KHI	CS3	FEB	2000
3	Shoaib	LHR	CS4	JAN	2000

2^nd Procedure of 1NF:

In this table will split in two tables.

(Personal) (FEE)

Roll_NO	MONTH	FEE
1	JAN	2000
1	FEB	3000
1	MARCH	3000
2	JAN	3000
2	FEB	2000
3	JAN	2000

Roll_NO	NAME	ADD	CLASS
1	Faizan	QTA	CS2
2	Umer	KHI	CS3
3	Shoaib	LHR	CS4

2NF:

A relation is in 2NF if and only if it is in 1NF. Every non-key attribute is fully functionally dependent on primary key.

Example:

(Report format)

Ord-no	Ord-date	Acc-no	Name	Add	Ord-list	Pro-no	Desc	Price	Qty	Pro-total	Ord-total
1	3 Jan 08	10	Faizan	QTA	3	100	Apple	5	1	5	22
-	-	-	-	-	-	200	Cup	3	2	6	-
-	-	-	-	-	-	300	pen	1	1	1	-
2	3 Jan 08	8	Umer	KHI	2	100	apple	5	2	10	20
-	-	-	-	-	-	400	watch	10	1	10	-
3	3 Jan 08	10	Faizan	QTA	1	400	watch	10	2	20	20

(1NF)

1^st procedure:

Ord-no	Ord-date	Acc-no	Name	Add	Ord-list	Pro-no	Desc	Price	Qty	Pro-total	Ord-total
1	3 Jan 08	10	Faizan	QTA	3	100	Apple	5	1	5	22
1	3 Jan 08	10	Faizan	QTA	3	200	Cup	3	2	6	22
1	3 Jan 08	10	Faizan	QTA	3	300	pen	1	1	1	22
2	3 Jan 08	8	Umer	KHI	2	100	apple	5	2	10	20
2	3 Jan 08	8	Umer	KHI	2	400	watch	10	1	10	20
3	3 Jan 08	10	Faizan	QTA	1	400	watch	10	2	20	20

2^nd procedure:

(Customer order record)

Ord-no	Ord-date	Acc-no	Name	Add	Ord-list	Ord-total
1	3 Jan 08	10	Faizan	QTA	3	22
2	3 Jan 08	8	Umer	KHI	2	20
3	3 Jan 08	10	Faizan	QTA	1	20

P.K

(Product description & price)

Ord-no	Pro-no	Desc	Price	Qty	Pro-total
1	100	Apple	5	1	15
1	200	Cup	3	2	6
1	300	pen	1	1	1
2	100	apple	5	2	10
2	400	watch	10	1	10
3	400	watch	10	2	20

F.K

(2NF)

(Product) (Order Record)

Pro-no	Desc	Price
100	Apple	5
200	Cup	3
300	pen	1
400	watch	10

Ord-no	Pro-no	Desc	Qty	Pro-total
1	100	Apple	1	15
1	200	Cup	2	6
1	300	pen	1	1
2	100	apple	2	10
2	400	watch	1	10
3	400	watch	2	20

3NF:

A relation is in a 3^rd normal form if and only if it is in 2^nd normal form every non key attribute non transitively independent. Third normal form mutually independent and fully dependent on P.K.

(3NF)

Ord-no	Ord-date	Acc-no	Name	Add	Ord-list	Ord-total
1	3jan2008	10	Faizan	QTA	3	22
2	3jan2008	8	Umer	KHI	2	20
3	5jan2008	10	Faizan	QTA	1	20

Order List

Ord-no	Pro-no	Qty	Row-tot
1	100	3	15
1	200	2	6
1	300	1	1
2	100	2	10
2	400	1	10
3	400	2	20

Acc-no	Name	Add
10	Faizan	QTA
8	Umer	KHI

BCNF:

Boyce code normal form and it is a direct normalization method. A relation is in BCNF if and only if every determinant is a candidate key. BCNF is equal to 3^rd normal form.

PROD

Pro-no	Des	Price
100	Apple	5
200	Cup	3
300	Pen	1
400	watch	10

SQL:

SQL is standardized by ANSI (American national standard institute). SQL is used to access the data base.

v It executes the quires against database.

v It inserts delete and modify the data.

v It is used for manipulation of data in database

v Semicolon; is used for termination of the query.

Two basic operations:

1. Data manipulation language (DML)

2. Data definition language (DDL)

DDL:

v It creates table.

v It drops table (delete table).

v It creates index (it tells where the table exist for direct access).

v It drops index.

v Alter table (include single or multiple column & also delete).

v Alter index.

DML:

v It selects (Extraction of the data).

v It updates (Update the record).

v Delete (when we delete one row or record).

v Insert (insert the data into table).

Index table:

Information about the whole database is called index table.

Data Manipulation Language:

v Select

v Update

v Delete

Creation of table:

Naming rules for creating a table:

Ø Table name should be unique.

Ø No special characters are allowed in table name.

Ø Only underscore can be used for separating the name.

Ø Table name should not be reserved word.

Ø Table name should be of 32 characters.

Example:

Query:

Create table Employ

(E_NO number (4),

E_NAME char (20),

ADDRESS varchar (30),

P_NO number (10),

SALARY number (15),

COM number (10));

It will create table like this

E_NO	E_NAME	ADDRESS	P_NO	SALARY	COM

Example:

Query:

Create table Student

(R_NO number (4),

S_NAME char (20),

ADDRESS varchar (30),

MARKS number (10),

FEE number (10));

It will create table like this

R_NO	S_NAME	ADDRESS	MARKS	FEE

Inserting data into table:

Insert is a reserved word which is used to insert data into the table.

Example:

Query:

Insert into Employ values

(1, ‘FAIZAN’, ‘Quetta’, 031-123456, 500 );

Example:

Query:

Insert into Student values

(1, ‘FAIZAN’, ‘Quetta’, 500, 1000 );

For viewing all records from table we write this query.

Select * from Employ

Syntax:

Select column (s)

From table name;

Example:

Select R_NO, FEE

From Student

Statements:

Select
Create
Insert

Clause:

Clause is used to show the condition within a statement.

If we want to apply the condition in the statement that is known as clause.

(Where) is a clause.

Syntax:

Select column(s)

From table name

Where

Column operator values;

Example:

Select Emp_No, Sal

From Emp

Where

Emp_No = 2;

Operators:

Operators are used to perform some specific operations on operands.

Logical operators:

(AND, OR, NOT, NOT IN)

Some additional operators are

(LIKE, BETWEEN, IN)

QUERIES SECTION

Examples:

1. Query:

Select sal, job

From EMP

Where

Sal>5000 OR job=’Manages’;

2. Query

Select sal, job

From EMP

Where

sal>5000 NOT job=’Manager’;

3. Query

Select sal, job

From EMP

Where

NOT job=’Manager’ AND sal>5000;

4. Query

Select fee, class

From STUDENT

Where

NOT class=’CS2’ AND fee>5000;

5. Query

Select sal, com

From EMP

Where

Sal>4000 OR sal<=3000 AND com>500;

6. Query

Select sal

From EMP

Where

Sal>=3000 AND sal<=6000;

7. Query:

Select sal

From EMP

Where

Sal>4000 AND sal<7000;

8. Query

Select sal

From EMP

Where

Sal BETWEEN 4000 AND 7000;

9. Query

Select sal

From EMP

Where

Sal NOT BETWEEN 4000 AND 7000;

IN operator:

Syntax:

Select Column(s)

From table name

Where

Column IN (values);

Query:

Select *

From EMP

Where

First_name IN (‘Faizan’, ‘XYZ’);

Insertion:

This query can insert many records at a time from this single query.

Query:

Insert into EMP

Values

(&RNO, &NAME, &FNAME, &CLASS, &MARKS);

NOT IN operator:

Query:

Select E_NAME

From EMP

Where

Desg NOT IN(‘Clerk’, ’MANAGER’);

Predicates:

Combination of two type of operator, which are logical & Relational operator is known as predicates.

LIKE operator:

LIKE operator is used for pattern matching.

Query for prefix:

Select *from EMP

Where

Name LIKE ‘S%’;

Query for postfix:

Select *from EMP

Where

Name LIKE ‘%S’;

Query for infix:

Select *from EMP

Where

Name LIKE ‘%S%’;

Deletion:

Delete is a reserved word use to delete the row from table.

For deleting the row we use delete. For deleting the column we use order. For deleting the table we use drop.

Syntax:

Delete from table name

Where

Column name= value;

Query for delete:

Delete from EMP

Where

RNO=5;

Delete EMP

Where

RNO=5;

For deleting the Whole data the query will be like this:

Delete *from EMP;

Delete From EMP;

UPDATE:
Syntax:

Update table name

Set

Column name = new values

Where

Column name = old values;

Query:

Update EMP

Set

Sal=9000

Where

ENO= 2;

Query:

Update EMP

Set

Name =’ABC’

Where

ENO = 4;

Query:

Update EMP

Set

City=’Sibi’, NIC=5678

Where

ENO=3;

Query:

Update EMP

Set

Des =’clerk’

Where

ENO=3;

Query:

Update EMP

Set

Sal =NULL

Where

ENO=3;

Query:

Update EMP

Set

Sal =sal + sal * 20 /100

Where

ENO=3;

Alias:

Alias is a short form of the original words. It is use to print the data on console. Alias is used to present the data in short form it applies on column as well as table name.

Syntax:

Select column as column Alias

From table;

Example:

Select RNO, Firstname AS Name,

Lastname AS FName,

Salary AS sal,

Commission AS Com

from emp;

Changing the table name (imaginary):

Select RNO,Name

From

Emp AS emp1;

Modification:

The expressions or cooperation of table is called modification.

1- Addition of column.

2- Increase/ decrease the width of column.

Syntax:

Alter table EMP

ADD

(PH_NO number (8),

Address varchar (25));

Increase query:

Alter table EMP

Modify

(Name char (25));

Condition for caudation:

1- Deletion of column.

2- Decreasing the width of column.

3- Conversion the data type of columns.

ENO	Name	Sal	Com
1	A	5000	100
2	X	9000	900
3	B	2000	600
2	X	9000	500

Fore data:

DOB date

For insertion of date

Value (‘15-03-96’ ‘DD-MM-YYYY’);

DD date number

MM month number

YYYY year with century

Order By:

Syntax:

Select column(s)

From table name

Order By

Column(s); (for Ascending)

ENO	Name	Sal	Com
3	B	2000	600
1	A	5000	100
2	X	9000	900
2	X	9000	500

Query
Select *

From EMP

Order By

Sal;

Query for descending:

Select *

From EMP

Order By

Sal desc;

Query:

Select name, sal

From EMP

Order By

Name, sal;

Query:

Select job, sal

From EMP

Where

job =’clerk’

Order By

Sal desc;

Query:

Select *

From EMP

Order By

Name, sal desc;

Query:

Select First_name, Last_name

From EMP

Where

First_name = ‘Faizan’ OR First_name = ‘Umer’

AND

Last_name = ‘XYZ’;

Table constraints:

Table constraints are the second name of condition. When we apply any check or condition on any value or on any column that is known as table constraints.

There are different types of constraints.

Primary Key
Not Null
Unique
Reference Key
Check constraints

Query:

Create table EMP

(EM_ID Number (4) primary key, Unique,

E_NAME char (20) Not Null,

Sal Number (5) Not Null, check (sal<=3000),

Add varchar (25));

Query:

Create table student

(S_ID number (3) primary key,

S_NAME char (20) Not Null,

Class char (5),

Add varchar (25) Not Null );

Reference Key;

Query:

Create table SP

(RNO number (3) primary key, unique,

Name char (20) Not Null,

F_Name char (20) Not Null,

P_NO number (15) check (P_NO<=11),

Add varchar (25) Not Null);

Another table

Create table SF

(RNO number (3) reference SP (RNO),

Fee_Paid number (3) Not Null,

Dues number (4) Not Null);

Another table

Create table SR

(RNO number (3) reference SP (RNO),

Tot_Marks number (5) Check (Tot_Marks<=900),

Perc number (4) Not Null);

For getting data query will be:

Select RNO, Name, Fee_Paid, Dues, Tot_Marks

From SP, SF, SR

Where

SP.RNO = SF.RNO AND

SP.RNO = SR.RNO AND

SP.RNO = 4;

DOJ Date:

To char:

It is responsible to enter the date into character format.

To date:

It is a function which is responsible to enter the date in a specific format in the table.

For inserting:

Query:

to-date (’15-02-1999’ , ‘DD-MM-YYYY’)

For showing:

Select enmae, tochar (DOJ ‘DD-MM-YYYY’)

Group Functions:

sum()
Avg()
Min()
Max()
Count()

Count ():

Query:

Select count (sal)

From EMP;

This query will calculate the total number of salary.

Min ():

Query;

Select Min (sal)

From EMP;

This query will calculate the minimum salary from the table.

Max ():

Query:

Select Max (sal)

From EMP;

This query will calculate the maximum salary from the table.

Avg ():

Query:

Select Avg (sal)

From EMP;

This query will calculate the average of salary from the table.

Sum ():

Query:

Select sum (sal)

From EMP;

Select ename, sum (sal)

From EMP

Group by job;

Abstract function:

Query:

Select ENO, Abs (sal)

From EMP;

Query:

Select Abs (-10)

From dual;

Dual:

Dual table is a dummy table which is use to perform different kinds of mathematically operations on the constant.

Query:

Select 40*800

From dual;

Power:

Query:

Select power (2, 2)

From dual;

Round off:

Query:

Select round (15.6729)

From dual;

Truncate:

Use to cut off the values.

Query:

Select trunk (1563.5529, 3)

From dual;

Ceiling & floor:

Query:

Select ceil (63.59)

From dual;

Upper:

Query:

Select upper (‘Faizan’)

From dual;

Query:

Select upper (ename)

From EMP;

Lower:

Query:

Select lower (ename)

From EMP;

Init cap:

Query:

Select init cap (ename)

From EMP;

Trim:

Ltrim (for left)

Rtrim (for right)

Query:

Select Ltrim (ename, ‘M’)

From EMP;

PADING:

Lpad (for left)

Rpad (for right)

Query:

Select Lpad (ename, 15, ‘Mr.’)

From EMP;

Query:

Select Lpad (ename, 15, ‘Mr.’)

From EMP

Where

Gender = ‘male’;
Functions

(Aggregate functions) (Scalar functions)

Avg ( ) Upper ( )

Count ( ) Lower ( )

Max ( ) Initcap ( )

Min ( ) Round ( )

Sum ( ) Mod ( )

First ( ) Remainder ( )

Last ( ) Power ( )

Truncate ( )

Ceil ( )

Floor ( )

First ( ):

Query:

Select first (ename), first (fee)

From EMP;

Last ( ):

Query:

Select last (ename), last (fee)

From EMP;

Mod ( ):

Query:

Select mod (5, 4)

From dual;

Count ( ):

Query:

Select count (Distinct ename)

From EMP;

Query:

Select count (*)

From EMP;

Union:

Query:

Select ename

From QTA

Union

Select ename from LHR;

Joining:

Query:

Select RNO, NAME, CGPA, DUES

From SP, SF, SR

Where

SP.RNO = SR.RNO AND

SP.RNO = SF.RNO AND

SP.RNO = 6;

Right join:

Query:

Select emp-name, order

From EMP

Right join order

Emp.id = order.id;

Left join:

Query:

Select emp-name, order

From EMP

Left join order

Emp.id = order.id;

Indexing:

Indexing is used to get the fast query result.

Query:

Create index RNO_index

EMP (RNO);

Query for multiple columns:

Create index RNO_index, Name

EMP (RNO, Name);

Query for descending order:

Create unique index Name

EMP (Name desc);

Dropping:

Query:

Drop table EMP;

This query can use as in indexing:

Drop index RNO_index;

Creation:

Query:

Create database Uni;

For dropping:

Drop database Uni;

Table truncation:

Query:

Truncate table EMP;

Column deletion:

Query:

Alter table EMP

Drop column (RNO);

To create Backup:

Query:

Select * std_backup

Into

From STD;

For columns:

Query:

Select RNO, NAME, FEE

Into

STD-Backup

From STD;

Views:

View is responsible to create view on specific columns;

Query;

Create view eview

(Select RNO, NAME, FNAME

From STD);

We can use view for inserting.

Query:

Insert into eview

Values

(3. ‘ABC’, ‘XYZ’);

Catalog:

Query:

Select *

From tab;

Sub query:

Mixture of two or more than two queries is known as sub query.

Query within query is called sub query.

Query:

Select ename from EMP

Where

Sal = (select max (sal) from EMP);

Query:

Select ename from EMP

Where

Deptno = (select Deptno from EMP

Where

Name = ‘Faizan’);

Users:

Query:

Create user CS

Identified by BUITMS;

To enter in new user:

Query:

SQL> CS / BUITMS;

Rules, privileges, authorities, rights:

There are 3 basic rules:

1. Connect

2. Resource

3. Admin

When we give rights to other user than we use GRANT, and for retrieving the rights we use REVOKE.

For example:

Grant connect to CS;

Grant connect, resource

To CS;

For finishing:

Revoke resource

From CS;

For changing password of user:

Query:

Alter user CS

Identified by BUITEMS;

For deleting user:

Query:

Drop user CS;

Rights on table:

Selection
Insertion
Deletion
Alternation
Updating
All

Query:

Grant select, insert, update

EMP

CS;

Query for viewing other user table;

CS / BUITMS

Select * from scott.EMP;

Query for update in other table:

Update scott.EMP

Set

Sal = 5000

Where

Sal = 9000;

For deletion Query:

Delete scott.EMP

Where

Deptno = 10;

For finishing the rights;

Revoke update

On EMP

From CS;

Query for all rights:

Grant All

On EMP

To CS;

Synonyms:

Query:

Create synonyms ST

For scott.EMP;

Commit is used for saving.

Roll back is used for to start from beginning from where we saved.

For edition:

We use Ed then press enter.

For saving query:

Select * from EMP

Save DC;

For viewing: @ Dc;

Earn free bitcoin

WELCOME to All In 1 Tutorial Blog

Pages

Wednesday, 19 July 2017

DBMS Tutorial

Blog Archive