DBMS Tutorial

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
  • TXT
  • DAT
  • DOC
  • Jpeg
  • Gif
  • Pdf
  • Ppt
  • Mp3
  • Avi
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
1.     Not Null (means not empty).
2.     Unique (means different to each other).
3.     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.
1.     Cardinality. 
2.     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.
OR
Data that describes the properties of another data is called metadata.


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.
1.     Remote Back-up.
2.     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.


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.
1.     Super key will be unique.
2.     Supper key will not null.
Properties of Primary Key
1.     Unique.
2.     Not null.
3.     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
1.     Unary Relationship.
2.     Binary Relationship.
3.     Ternary Relationship.

Relationship

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

Types of primary key
1.     Composite key.
2.     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
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
1.     Sequential (store the data on tape).
2.     Index Sequential (store on disk).
3.     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.
1.     DFD (Data Flow Diagram).
2.     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.
OR
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.
1.     Table: data is placed in tables.
2.     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

Symbol

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

Symbol

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
1.     Product.
2.     Theta Join.
3.     Equi join.
4.     Natural Join.
5.     Semi Join.
6.     Left Outer Join.
7.     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







Features of Normalization

·                    It converts the table into simple format.
·                    Compressing tables.
·                    Error detection.
·           Simplicity form.
·           Is used to reduce the redundancy from the table.
·           Is used to remove anomalies from the table.
·           Normalization is used to get data into simple form that truly reflects.
·           In normalization initial grouping of data occur.
·                  Normalization rule will be applied to remove anomalies.
·           Actual data in re-arranged form.
·           Separate entity type.
·           Their attributes.
·           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

1St 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

2nd 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)


(1NF)1st procedure


2nd 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)

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

 (Product)                                                                        




(Order Record)
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 3rd normal form if and only if it is in 2nd 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 3rd 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.

·           It executes the quires against database.
·           It inserts delete and modify the data.
·           It is used for manipulation of data in database
·           Semicolon; is used for termination of the query.
Two basic operations
1.      Data manipulation language (DML)
2.      Data definition language (DDL)
DDL

·           It creates table.
·           It drops table (delete table).
·          It creates index (it tells where the table exist for direct access).
·           It drops index.
·          Alter table (include single or multiple column & also delete).
·          Alter index.

DML

·           It selects (Extraction of the data).
·           It updates (Update the record).
·                    Delete (when we delete one row or record).
·           Insert (insert the data into table).
Index table
Information about the whole database is called index table.

Data Manipulation Language

·          Select
·          Update
·          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
1.     Select
2.     Create
3.     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;

OR
Delete EMP
Where
RNO=5;

For deleting the Whole data the query will be like this
Delete *from EMP;
OR
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
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)
Query:
ENO
Name
Sal
Com
3
B
2000
600
1
A
5000
100
2
X
9000
900
2
X
9000
500





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.
1.     Primary Key
2.     Not Null
3.     Unique
4.     Reference Key
5.     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:
1.     sum()
2.     Avg()
3.     Min()
4.     Max()
5.     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.

Subscribe to: Posts (Atom)