WELCOME to All In 1 Tutorial Blog

DBMS Tutorial

2017-07-19T12:23:00.002+05:00

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

PHP Tutorial Step By Step

2013-04-17T03:06:00.001+05:00

PHP Tutorial Step By Step

Hello Fellows after learning about HTML now we are going to learn about PHP first of all we should know what is PHP? and how it works.

PHP (PHP Hypertext Preprocessor).

PHP is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML.

PHP is Server-side embedded scripting language. It provides web developers with a full suite of tools for building dynamic websites.

As i told you that PHP is a server side Language so you cannot run directly on your computer or laptop, but HTML is client side that can easily run on client Computer/Laptop, to run the PHP on your computer you need to install a local host server like

WampServer (<--- click to download ) select your O/S and download it and install it

what is wamp server ??

WampServer is a Windows web development environment. It allows you to create web applications with Apache2, PHP and a MySQL database. Alongside, PhpMyAdmin allows you to manage easily your databases.

Steps for Installing WampServer

Step 1

Download the WampServer (<--- click to download ) from the given link and then open the location where you save that wamp exe file double click on it

Step 2

This will appear on your screen when you double click on the wampserver.exe file click NEXT and

Step 3

This screen will ask you about the License agreement just Click I Accept the agreement and click Next

Step 4

This step is asking where you want to save/install your Wamp server mostly we do not change the path but if u want to change the path or different folder click the BROWSE button and select the new distention or folder where you want to install the wamp and click NEXT

Step 5

This step is asking you that if you want to create the ICON of Wapm server on Desktop and Quick Launch if they are uncheck then check them and click NEXT

Step 6

This step shows some information about the wapm that where you are installing and about the icons so just click INSTALL

Step 7

Now the installation is start and it will take little time depends on your computer speed when Extracting files is complete

Step 8

Now it will ask you to select your browser where that wampserver will run so just click open

Step 9

In this step it is asking permission for the access from the fire wall just check Private if you are using it at your home network other

Step 10

In this its asking about the mail parameter we will not change it cause we are using local host but if you want to change it you can change it but this will make some problem in feature so try not to change it and press NEXT

Step 11

This step will ask that if you want to run/launch the Wamp now or later so just do as you like if you want to launch it now just check the Launch WampServer now and press FINISH

Step 12

If you launch the wamp server Now then it will ask you a permission press yes and then it will run the wamp when it will start wamp small icon will appear on your System Tray like this

That GREEN color Icon shows that wamp server is on if it’s RED that means not yet ready or if it is in ORANGE color that means starting up

When you will start the wamp it always like

1st RED 2nd ORANGE 3rd GREEN

Microsoft Access Tutorial

2011-07-25T18:10:00.112+05:00

Any one who want to learn or work on the MS Access so this tutorial is really going to help them all and don't worry about the version cause when you know the basic so you can do work on any version of Ms Access (2000, 2003, 2007 or 2010) just clear the concept and go on no problem so lets start with little introduction of Ms Access

Little Intro to Ms Access
MS Access is basically a Relational Database Management System (RDBMS), designed primarily for home or small business usage.

How To Install Ms Access

I am going to tell you how to install Ms Access 2010
when you will insert the CD/DVD the setup will automatically start if not then just click on the CD/DVD drive and select the SETUP.EXE file and it will start now it will take some time to load some file then it will ask you license term condition so just check it and press continue

after pressing continue it will look like following if you want to completely install the Ms Office just press Install other wise click on customize option

On clicking Customize you will get a window from here you can un-select your not required option by clicking on the drop down menu

and select the option Not available "Be careful do not select Ms Access as Not Available"

After doing selection press Install Now option this will start installing the Ms Office /Ms Access

Opening Ms Access

To Open the Ms Access click on START MENU --> All Programs --> Ms Office --> Ms Access now your Ms Access will start and it will look like

Now their are many option and menus

Blank Database (Simple Database )
Blank Web Database (Database link with website)
Recent Templates (Sample/Templates you used)
Sample Templates (Some Sample of Database)
My Templates (Your Own created Database)

BLANK DATABASE

After selecting the Blank Database and pressing the create Button you will get the following Screen

Well basically we use Blank Database cause its totally blank and there is no predefined Field Name or Data Type "Field Name are the use to define what type of information we will enter "Name, CNIC, TEL#, AGE etc" and Data Type tells that what type of the data is going to enter in the field "Text, Number, Data/Time, etc " this very important to define the type of the field
e.g:- if you want the records of persons name so Field Name should like "NAME" and the Data Type must be "TEXT" Don't place space in defining the Field Name Cause some time it create problem in creating relation ship. use underscore "_" instead of space.

Now Click on the View option as shown in following fig and select the Design View. In Design View you can enter the field name and data type

On clicking the Design View a Message box will appear it will ask to save the table type the table name by which you want to save and press OK table name must define the stored records/information
For Example:-
If you are going to store the records of Students Fee so table name like "FEE or FEE RECORDS or STUDENT FEE"

On Pressing ok you will get the following screen

There are few option in this view like

FIELD NAME
DATA TYPE
DESCRIPTION
FIELD PROPERTIES

You are now familiar with the FIELD NAME & DATA TYPE now their are new option DESCRIPTION in this area we describe some information related to the field name and data type

For Example:-

this option is not necessary to fill you can leave it empty

Now just fill the FIELD NAME & DATA TYPE

HTML Tutorial

2011-07-22T18:17:00.236+05:00

Well now a days every thing is going online no matter buying, selling, personal portfolio, personal business or multinational companies even online jobs are available

In this tutorial I am going to teach you some basic of HTML (Hyper Text Markup Language)

But first let me tell you what is HTML. It is a language which we use to create the webpage

It contain (tags) these tags are in pair cause their is open tag and close tag. These tags are enclosed by the angle brackets like "<HTML> it is an opening tag "</HTML>" this is a closing tag. Some tags contain the attributes the attributes are the use to customize the tags function.

All the HTML file can be create in normal Note Pad or you can download the advance version of note pad from here "notepad++" .
HTML is not a case sensitive language so no worries if you write <HTML> or <html> lets have an example

You can open a note pad by clicking on start menu --> All Programs or Programs --> Accessories --> NotePad

Basic Syntax of HTML (Web Page)

Well this is the basic syntax to create a web page

As you can see there are 4 tags and each tags are in pair 1 opening and other closing tag

The <HTML> tag shows it is a webpage document
The <HEAD> tag tells it is a container. Elements inside <HEAD> can include scripts, instruct the browser where to find style sheets, provide meta information, and more.
The <TITLE> tag
- defines a title in the browser toolbar
- provides a title for the page when it is added to favorites
- displays a title for the page in search-engine results
The <BODY> tag contains all the contents of an HTML document, such as text, hyperlinks, images, tables, lists, etc.

Tags

Some common tags use in creating webpage

Tag	Description
<!--...-->	Defines a comment
<!DOCTYPE>	Defines the document type
<a>	Defines an anchor
<b>	Defines bold text
<basefont />	Defines a default font, color, or size for the text in a page
<big>	Defines big text
<body>	Defines the document's body
<br />	Defines a single line break
<button>	Defines a push button
<caption>	Defines a table caption
<center>	Defines centered text
<col />	Defines attribute values for one or more columns in a table
<font>	Defines font, color, and size for text
<form>	Defines an HTML form for user input
<frame />	Defines a window (a frame) in a frameset
<frameset>	Defines a set of frames
<h1> to <h6>	Defines HTML headings
<head>	Defines information about the document
<hr />	Defines a horizontal line
<html>	Defines an HTML document
<i>	Defines italic text
<img />	Defines an image
<input />	Defines an input control
<label>	Defines a label for an input element
<li>	Defines a list item
<link />	Defines the relationship between a document and an external resource
<menu>	Defines a menu list
<meta />	Defines metadata about an HTML document
<object>	Defines an embedded object
<ol>	Defines an ordered list
<optgroup>	Defines a group of related options in a select list
<option>	Defines an option in a select list
<p>	Defines a paragraph
<s>	Defines strikethrough text
<script>	Defines a client-side script
<select>	Defines a select list (drop-down list)
<small>	Defines small text
<span>	Defines a section in a document
<strike>	Defines strikethrough text
<strong>	Defines strong text
<style>	Defines style information for a document
<sub>	Defines subscripted text
<sup>	Defines superscripted text
<table>	Defines a table
<td>	Defines a cell in a table
<textarea>	Defines a multi-line text input control
<th>	Defines a header cell in a table
<thead>	Groups the header content in a table
<title>	Defines the title of a document
<tr>	Defines a row in a table
<u>	Defines underlined text
<ul>	Defines an unordered list

Save The HTML File

After doing the above task now we are going to save our work for this go to File menu --> save the following option will appear

now type the File Name you want to save your page then place .html after the file name this will save the file as a webpage which will open in web browser. The .html is the file extension

Remember must save all the web page in one folder for example if you are making a website for some organization so you must save all the webpage of that organization in one folder every time you create the website for different organization do make the folder of that organization separately and keep all the related picture, document, files etc in that folder with sub folder like folder for images etc

Browser Support

to open the source code of the webpage you need to move the cursor on the webpage icon and do right click --> open with --> notepad

How do we work in Body and Title tag

As we already discusses that in body tag we do all work which will be display in the browser

Code

OutPut

Now i would like to tell you how to customize your font according to the requirement for this we are going to use the <FONT> tag its also a pair tag lets have a look

The font tag contain some attributes which is use for the customize the font style

Attributes of Font

Attributes	Description
Face	Defines font-family
Size	Defines size of font "size(2-6)"
Color	Defines color of font (BLUE,GREEN etc)

Code

OutPut

Underline Bold Italic

some time we want to make some important words or sentence visible Separately so we some time underline it Bold it or Italic it for this we use the following tags

<u> Underline </u>
<b> Bold </b>
<i> Italic </i>
lets have a look

Code

Output

See the above example its bit messy to make it more clear an understandable we are going to use the <br> tag it is a single tag there is no closing tag for this. The <br> tag is use to Break the Line

Lets Have a Look

Code

Output

Listing

Some time we need to make a list of some thing it could be the list of the name, places, items etc there are the 2 common types of list in HTML

Order List <ol>

Unordered List <ul>

Ordered List Types

Lower-Case Letters	Upper-Case Letters	Lower-Case Numerals	Upper-Case Numerals
a. Find a Job b. Get Money c. Move Out	A. Find a Job B. Get Money C. Move Out	i. Find a Job ii. Get Money iii. Move Out	I. Find a Job II. Get Money III. Move Out

Unordered List Types

Square	Disc	Circle
§ Find a Job § Get Money § Move Out	· Find a Job · Get Money · Move Out	o Find a Job o Get Money o Move Out

Lets have a look

Code

Output

Table
Tables are seem difficult to create, but after working you will not feel like that again. The <table> tag is also a pair tag <table> used to begin a table. Within a table tag we have <tr> (table rows) and <td> (table columns) tags. The attribute of the table are

Table border
Table header
Colspan
Rowspan
Cell padding and spacing

Lets Have a Look

Code

Output

Adding Images Pictures to your Site
In every website we see pictures images etc so how they do that.Don't worry its too easy you can also do that just one tag <img src > tag its not a pair tag there are some attributes of image

Image Attributes

Height
Width
Border
alt (Alternate value "Right, Left, Center")
align (Position of image in horizontal "Top, Bottom, Center")

Lets Do some Example

Code

As you can see i did not write the complete path of the image just the folder name and the image name with extension this is because I save the image in a folder of Image folder which i created with in the folder where my webpage is save this make no need of full path of the image but if we place image in different place and the webpage in different so we need the complete path of the image other wise the image will not display

Output

You can also apply picture or image as a backgroung of the webpage but be careful in choosing the background. Choose background which i light in color so it will not effect on the text of webpage on my concern always choose the light color and not very textured background to avoid facing problem in reading the text for reader

To Apply the image on background use attribute of <BODY background = " path of image"> or you can use <Body bgcolor ="Any Color Name"> this will give a solid color

Example

Code

Output

Work With Hyper-links

The hyper link is use to link the other page of website or any other URL or website link this is use to call any other page or links from your website

Example if you want to give a link of yahoo on your web site so you are going to use the <a href> the hyperlink tag there is a attribute "target = blank" which allow you to open the linked page in new blank browser lets have an example

Code

The last link will open in new tab but the remaining three will open on the same page you are working on
Output

Creating The Form

Many time you saw or fill the form page to get ID, to get Registered for some thing etc today you are going to create your own form page.

The <form> tag is used to create an HTML form for user input. A form can contain input elements like text fields, checkboxes, radio-buttons, submit buttons and more. A form can also contain select menus, textarea etc

Example

Code

Output

This look bit messy and difficult to understand the good idea to make beautiful and clear form is creating in table

here is an example

Code

Output

Basic Info For Windows 7

2011-07-22T00:56:00.002+05:00

Windows 7 Editions

Windows 7 is available in six different editions, but only Home Premium, Professional, and Ultimate are widely available at retail

View your computer information

You can view a summary of important information about your computer by opening System in Control Panel. You can see basic hardware information, such as your computer's name, and which edition of Windows your computer is running. You can change important system settings by clicking the links in the left pane of System.

Open System by clicking the Start button , clicking Control Panel, clicking System and Maintenance, and then clicking System.

View basic information about your computer

System presents a summary view of basic details about your computer, including:

Windows edition. Lists information about the version and edition of Windowsrunning on your computer.

System. Displays your computer's Windows Experience Index base score, which is a number that describes the overall capability of your computer. Your computer's processor type, speed, and quantity are listed, if your computer uses multiple processors. For example, if your computer has two processors, you will see (2 processors) displayed. Also displayed is how much random access memory (RAM) is installed. For more information about your computer's base score and what it means, see what is the windows Experience Index?

Computer name, domain, and workgroup settings. Displays your computer's name and workgroup or domain information. You can change this information and add user accounts by clicking Change settings.

Windows activation. Activation verifies that your copy of Windows is genuine, which helps prevent software piracy. For more information, see Activating Windows: frequently asked questions.

Change Windows system settings

The links in the left pane provide access to additional system settings. If you're looking for the information that you used to find in System in Control Panel when using earlier versions of Windows, you will find that same information by clicking the links in the left pane.

Device Manager. Use Device Manager to change settings and update drivers.

Remote settings. Change settings for Remote Desktop, which enables you to connect to a remote computer, and Remote Assistance, which enables you to invite someone to connect to your computer to help with a computer problem.
System Protection. Manage settings that automatically create the restore points that System Restore uses to restore your computer's system settings. You can turn System Protection on or off for the disks on your computer, and you can create restore points manually. For more information, search Windows Help and Support for "System Restore."
Advanced System Settings. Access advanced performance, user profile, and system startup settings, including Data Execution Prevention, which monitors programs and reports possible security attacks. You can also change your computer's virtual memory settings.