26812648 Practical File SQL Queries DBMS

(1)

Submitted by:

Name

Name:

: Prabhat

Prabhat Kumar

Kumar

Class

Class:

: BCA

BCA IInd

IInd

Class Roll no

Class Roll no.

. 06

06 Submitted to

Submitted to

::

Kanika Mahajan

Concerned Lect. in DBMS

Ashoka college of Computer

DBMS & SQL

Queries

Session - 2009

-10

(2)

Certificate

Th

This

is is

is ce

certi

rtifie

fied t

d tha

hat th

t the

e

practica

practical file

l file belong

belongs to

s to

Prabhat Kumar, Class roll

no. 06 and Examination

Roll no…………of

Roll no…………of session

session

2009-10 and has

completed all the practical

in the college computer lab

under our guidance and his

behavior

behavior in

in the

the college

college is

is

good.

(3)

Teacher Incharge

Principal

Index

(4)

S

.

n

o

.

N

a

m

e

o

f

p

r

a

c

t

i

c

a

l

R

e

m

a

r

k

s

1 1.. DDaattaabbaassee, , DDBBMMS S ddeeffiinniittiioonn 2 2.. IInnttrroodduuccttiioon n tto o SSQQLL 3 3.. CCoommppoonneenntts s oof f SSQQLL, , DDaatta a ttyyppeess 4 4.. SSQQL L QQuueerriiees s ccrreeaattee, , iinnsseerrttiioonn 5 5.. CCrreeaatte e a a ttaabbllee 6 6.. DDeessccrriibbe e tthhe e ttaabbllee 7 7.. IInnsseerrt t tthhe e vvaalluueess 8 8.. TTo o vviieew w tthhe e ttaabbllee 9 9.. SSoorrttiinng g tthhe e ddaattaa 1 100.. TTo o rreennaamme e tthhe e ttaabbllee 1 111.. DDeelleette e a a ppaarrttiiccuullaar r rrooww 1 122.. LLoowweer r ccaassee 1 133.. UUppppeer r ccaassee 1 144.. DDrroop p tthhe e ttaabbllee 1 155.. MMaaxxiimmuumm 1 166.. MMiinniimmuumm 1 177.. AAvveerraaggee 1 188.. LLiikkee 1 199.. BBeettwweeeenn 2 200.. PPrriimmaarry y kkeeyy 2 211.. UUnniiqquue e kkeeyy 2 222.. CCoouunntt 2 233.. SSuumm 2 244.. AAlltteerr 25. 25. UpdateUpdate

(5)

Database

A

A database is an organized collection of facts. In other words, we

database is an organized collection of facts. In other words, we

can say that it is a collection of information arranged and

presented to serve an assigned purpose.

An example of a database is a dictionary

Database Management System

Database management system, or DBMS, is a computer software

program that is designed as the means of managing all databases

that are currently installed on a system hard drive or network.

Different types of database management systems exist, with

some of them designed for the oversight and proper control of

databases that are configured for specific purposes. Here are

(6)

some examples of the various incarnations of DBMS technology

that are currently in use, and some of the

that are currently in use, and some of the basic element

basic elements that

s that are

are

part of DBMS software applications.

As the tool that is employed in the broad practice of managing

databases, the DBMS is marketed in many forms. Some of the

Access, FileMa

Access, FileMaker,

ker, DB2, and Oracle. All

DB2, and Oracle. All these products provide for

these products provide for

the creation of a series of rights or privileges that can be

associated with a specific user. This means that it is possible to

designate one or more database administrators who may control

each function, as well as provide other users with various levels of

admin

administration rights. This flexibility makes the

istration rights. This flexibility makes the task of using

task of using

DBMS methods to oversee a

DBMS methods to oversee a system something that can be

system something that can be

centrally controlled, or allocated to several different people.

Introduction to Structured Query

Language (SQL)

Structured query language is a language that provides an

interface to relational database systems. SQL was developed by

IBM in the 1970s for use in system R, and is a de facto standard,

as well as an ISO and ANSI standard. SQL is often pronounced

SEQUEL.

SQL has been a command language for communication with the

oracle 9i server from any tool or application. Oracle SQL contains

many extensions. When an SQL statement is entered, it is stored

in a part of memory called the SQL buffer and remains there until

a new SQL statement is entered.

(7)

Features of Structured query language (SQL)

SQL can be used by a

SQL can be used by a range of users, including those with little or

range of users, including those with little or

no programming experience.

•

It is a nonprocedural language.

•

It reduces the amount of time required

It reduces the amount of time required for creating and

for creating and

maintaining systems.

•

It is English like language.

Components of SQL

1)

DDL

DDL (Data

(Data Defi

Definiti

nition

on Langu

Language)

age):-

:-

It is a set of SQL commands

used to create, modify and delete database structures but not data.

They

They are no

are normall

rmally used

y used by th

by the DBA

e DBA not by

not by user t

user to a lim

o a limited e

ited exten

xtent, a

t, a

database designer or application developer. These statements are

immediate i.e. they are not susceptible to ROLLBACK commands. It

should also be noted that if several DML statements for example

UPDATES are executed then issuing a

UPDATES are executed then issuing any DDL command would COMMIT

ny DDL command would COMMIT

all the updates as every DDL command implicitly issues a COMMIT

command to the database. Anybody using DDL must have the CREATE

object privilege and a table space area in which to create objects.

For example: -

For example: - CREATE, ALTER, DROP, TRUNCATE, COMMENT etc.

CREATE, ALTER, DROP, TRUNCATE, COMMENT etc.

2)

DML

DML (Dat

(Data

a Manip

Manipulat

ulation

ion Langu

Language)

age):-

:-

It is the area of SQL

that allows changing data within the database.

Examples:-INSERT, UPDATE, DELETE etc.

(8)

3)

DCL

DCL (Dat

(Data

a Contr

Control

ol Langua

Language):

ge):--

It is the component of SQL

statement that control access to data and to the database. Occasionally

DCL statements are grouped with

DCL statements are grouped with DML statements.

DML statements.

Examples: - COMMIT, S

Examples: - COMMIT, SAVEPOINT, ROLLBAC

AVEPOINT, ROLLBACK etc.

K etc.

4)

DQL

DQL (Dat

(Data

a Quer

Query

y Langua

Language):

ge):--

It is the component of SQL

statement that allows getting data from the database and imposing

ordering upon it. It includes the SELECT statement. This command is the

heart of SQL. It allows getting the data out of the database perform

operations with it. When a SELECT is fired against a table or tables the

results is compiled into a further temporary table, which is displayed or

perhaps received by the program i.e. a front-end.

Examples: - SELECT retrieve data from the database.

Oracle data types

Data types come in several forms and sizes, allowing the programmer to

create tables suited to the scope of the project. The decisions made in

choosing proper data types greatly influence the performance of a database.

The in

The informa

formation in

tion in the da

the database

tabase is mai

is maintaine

ntained in th

d in the form

e form of tabl

of tables and

es and each

each

table consists of rows and columns, which store data and therefore this data

must have some data type i.e. the type of data, which is stored in the table.

The di

The differen

fferent type

t types of data

s of data type

types in O

s in Oracle

racle are:-

are:-•

•

CHAR

•

VARCHAR (size) or VARChAR2 (size)

• •

NUMBER

• •

DATE

• •

LONG.



CHAR

: -: -

This da

This data typ

ta types is u

es is used to

sed to store

store charac

character st

ter strings

rings

values of fixed length. The size in brackets determines the

number of characters the cell

number of characters the cell can hold. T

can hold. The maximum number

he maximum number

of characters (i.e. the size) this data type can hold is 255

(9)

characters. The data held is right- padded with spaces to

whatever length specified.



VARCHAR or VARCHAR2

:-:-

This d

This data ty

ata type is u

pe is used to

sed to store

store

variable length alphanumeric data. It is a more flexible form of

the CHAR data type. The maximum this data type can hold up

to 4000 characters. One difference between this data type and

char data type is oracle compares varchar values using non

padded comparison semantics i.e. the inserted values will not

be padded with spaces. VARCHAR can hold 1 to

be padded with spaces. VARCHAR can hold 1 to 255

255 characters. Varchar is usually a wiser choice than char due to

characters. Varchar is usually a wiser choice than char due to

its variable length format characteristics but keep in mind that

char is much faster than varchar sometimes up to 50%.



NUMBER

: -: -

The num

The number d

ber data ty

ata type is u

pe is used to

sed to store

store numb

numbers

ers

(fixed or floating point).The precision (P) determines the length

of the data while(s), the scale, determines the number of

places after the decimal. The NUMBER data type that is used

to store number data can be

to store number data can be specified either to store integers

specified either to store integers

or decimals with the addition of

or decimals with the addition of a parenthetical precision

a parenthetical precision

indicator. If we do not use then the default value is 0 and if we

don’t use precision then by default value stored can be of 38

digits.



DATE

:- The

DATEDATE

data type stores date and time information.

Although date and time information can be represented in both

character and number data types, the

DATEDATE

data type has

special associated properties. For each

DATEDATE

value, Oracle

stores the following information: century, year, month, date,

hour, minute, and second.



LONG

:-

LONGLONG

columns store variable-length character strings

containing up to 2 gigabytes, or 2

3131

_{-1 bytes.}

_LONG_LONG

_columns

have many of the characteristics of

VARCHAR2VARCHAR2

columns. You can

use

LONGLONG

columns to store long text strings. The length of

LONGLONG

values may be limited by the memory available on your

computer

(10)

The u

The use of se of LONLONG vG values alues is suis subject bject to soto some rme restricestrictionstions::

•

• A table can contain only one LONG column.A table can contain only one LONG column. •

• You c You cannot annot create create an oban object tyject type wipe with a th a LONLONG attrG attributibute.e. •

• LONG columns cannot appear in WHERE clauses or in LONG columns cannot appear in WHERE clauses or in integrity constraintsintegrity constraints

(except that they can appear in NULL and NOT NULL constraints). (except that they can appear in NULL and NOT NULL constraints).

•

• LONG columns cannot be indexed.LONG columns cannot be indexed. •

• A stored function cannot return a LONG value.A stored function cannot return a LONG value. •

• You c You can dan declare eclare a vara variable iable or arguor argument ment of a of a PL/SQPL/SQL proL program gram unit unit usinusing theg the

LONG datatype. However, you cannot then call

LONG datatype. However, you cannot then call the program unit from SQL.the program unit from SQL.

•

• Within a single SQL statement, all LONG columns, updated tables, and lockedWithin a single SQL statement, all LONG columns, updated tables, and locked

tables must be located on the

tables must be located on the same database.same database.

QUERY

A query is a concise memo submitted to an editor by a writer seeking

publication. It is basically an in query to see whether the writer’s work is of

interest to a particular publication. A query briefly details a writer’s experience

and knowledge of the subject matter, and gives a summary or synopsis of the

article the writer hopes to have published. An approximate word count for the

proposed article or feature is also generally included.

1)

1) THE THE CREACREATE TATE TABLE BLE COMCOMMANMANDD

:- The CREATE TABLE command

defines each

column of the table uniquely. Each

column has a minimum of three attributes, a name, data type and

size (i.e. column width).

Syntax

: - CREATE TABLE<table name>(<c

: -

CREATE TABLE<table name>(<column Name 1> <data

olumn Name 1> <data

type>(<size>

type>(<size>),

), <columnname

<columnname2>

2> <data

<data type>(<

type>(<size>));

size>));

Example:

SQL> create

SQL> create table student(name varchar(23),roll_no number(12),clatable student(name varchar(23),roll_no number(12),classss varchar2(12

varchar2(12),address ),address varchar(23))varchar(23));; Table created.

Table created. 2)

2) THE INTHE INSERTSERTION OION OF DATA F DATA INTO TINTO TABLABLE:E: - O- O

nce a table is created, the

m

mo

os

st

t

n

na

attu

urra

al

l

tth

hiin

ng

g

tto

o

d

do

o

iis

s

load this with data to be manipulated later i.e. to insert the rows in

a table. The data in a table can be inserted in three ways.

Syntax

:-INSERT INTO <table name >(<columnname1>,<columnname2>):-INSERT INTO <table name >(<columnname1>,<columnname2>) VALUES(<expression1>,<expression 2>);

(11)

OR OR

INSERT INTO <tablename>VALUES(<expression1 >,<expression2> ); INSERT INTO <tablename>VALUES(<expression1 >,<expression2> );

OR OR INSERT

INSERT INTO INTO <tablename> VALUES(‘<&colum<tablename> VALUES(‘<&columnname1>’ ,’<&columnname2>’);nname1>’ ,’<&columnname2>’); Example

Example:- :-SQL> insert SQL> insert intostudent(n

intostudent(name,roll_no,clasame,roll_no,class,address)values(s,address)values('Prabhat',06,'BCA'Prabhat',06,'BCA',Hat',Hat limore'); limore'); 1 row created. 1 row created. Or Or SQL> insert

SQL> insert into student into student values('kishovalues('kishore',01,'BCA','Nare',01,'BCA','Nagri');gri'); 1 row created.

1 row created.

Or Or SQL> insert into student

SQL> insert into student values('&name

values('&name','&roll_no','&c','&roll_no','&class','&address')lass','&address');; Enter value for name: Amarjeet

Enter value for name: Amarjeet Enter value for roll_no: 30 Enter value for roll_no: 30 Enter value for class: BCA Enter value for class: BCA

Enter value for address: airwan Enter value for address: airwan old

old 1: 1: insert insert into into studentstudent values('&name

values('&name','&roll_no','&c','&roll_no','&class','&address')lass','&address') new

new 1: 1: insert insert into into studen studen values('Atinvalues('Atinder','04','BCA',der','04','BCA','Sawan'Sawan chack')

chack')

1 row created. 1 row created.

FOR

FOR insertininserting g more values more values we we use use ‘/’ slash after ‘/’ slash after SQL> SQL> as as below below butbut after above syntax used:

after above syntax used: SQL> /

SQL> /

Enter value for name: Vinay Enter value for name: Vinay

(12)

Enter value for roll_no: 08 Enter value for roll_no: 08 Enter value for class: BCA Enter value for class: BCA Enter value for

Enter value for address: Barnotiaddress: Barnoti old

old 1: insert 1: insert into into studentstudent values('&nam

values('&name','&roll_no','&ce','&roll_no','&class','&address'lass','&address')) new

new 1: insert 1: insert into into studen studen values('Vinvalues('Vinay','08','BCA','ay','08','BCA','Barnoti')Barnoti') 1 row created.

1 row created. 3)

3) FOR VFOR VIEWINIEWING DATG DATA IN THA IN THE TABE TABLELE

: - Once data has been inserted

iin

ntto

o

a

tta

ab

blle

e,

,

tth

he

e

ne

n

ex

xt

t

m

mo

os

st

t

llo

og

giic

ca

all

operation would be to view what has been inserted. The SELECT

SQL verb is used to achieve this.

SQL verb is used to achieve this. The SELECT command is used to

The SELECT command is used to

retrieve rows selected from one or

retrieve rows selected from one or more tables.

more tables.

Syntax

Syntax: - SELECT * FROM <table name>;: - SELECT * FROM <table name>;

If we want to see all the tables that are already exist in the database .we use If we want to see all the tables that are already exist in the database .we use SELECT * FROM TAB;

SELECT * FROM TAB;

Example:-SQL> select * from student; SQL> select * from student;

NAME

NAME ROLL_NO ROLL_NO CLASS CLASS ADDRESSADDRESS

--- --- --- --- --- --- ---Prabhat

Prabhat 06 06 BCA BCA HatlimoreHatlimore Kishore

Kishore 01 01 BCA BCA NagriNagri Amarjeet

Amarjeet 30 30 BCA BCA airwanairwan Vinay

Vinay 08 08 BCA BCA barnotibarnoti

1 row created. 1 row created.

When we use the command

(13)

as:-SQL> select * from tab; SQL> select * from tab;

TNAME

TNAME TABTYPE TABTYPE CLUSTERIDCLUSTERID --- --- --- --- --- ---ABC TABLE ABC TABLE ANKU TABLE ANKU TABLE BONUS TABLE BONUS TABLE DEPARTMENTS TABLE DEPARTMENTS TABLE DEPT TABLE DEPT TABLE EMP TABLE EMP TABLE EMPLOYEE TABLE EMPLOYEE TABLE EMPLOYEES TABLE EMPLOYEES TABLE S STTUUDDEENNTT TTAABBLLEE 9 rows

9 rows selected.selected.

4)

4) ELIMIELIMINATINATION OON OF DUPF DUPLICATLICATE ROE ROWSWS :-:-

A table could hold duplicate

rows

rows in

in

such

such a

a case,

case, only

only

unique rows the distinct clause can be used.

Syntax

Syntax: - SELECT DISTINCT <column name 1>,<column name2> FROM: - SELECT DISTINCT <column name 1>,<column name2> FROM <table name> ;

<table name> ; This

This syntasyntax wx will giill give thve the une unique ique valuevalues of s of columcolumn 1 n 1 and cand columolumn 2.n 2. Example

Example:-

:-SQL> select distinct name,roll_no from

SQL> select distinct name,roll_no from student;student;

NAME ROLL_NO

--- ---

(14)

---Prabhat 06

Prabhat 06

Syntax:

Syntax:-- SELECT DISTINCT * from <tablename>;SELECT DISTINCT * from <tablename>; Example

Example:-

:-SQL> Select DISTINCT * from student; SQL> Select DISTINCT * from student;

NAME

--- - --- --- --- --- --- ---Prabhat

Prabhat 06 06 BCA BCA HatlimoreHatlimore 5)

5) SOSORTIRTING DNG DATA IATA IN A TABN A TABLELE

: - Oracle allows data from a table to be

viewed in

sorted order. The rows retrieve from

the table will be sorted either in ascending or descending order

depending on the condition specified in

depending on the condition specified in the select sentence.

the select sentence.

Syntax

Syntax: - SELECT * FROM <tablename>ORDER BY<column name1>,<column: - SELECT * FROM <tablename>ORDER BY<column name1>,<column name 2> <[sort order]>;

name 2> <[sort order]>; Example

Example:-

:-SQL> SELECT * FROM STUDENT ORDER

SQL> SELECT * FROM STUDENT ORDER BY name;BY name;

NAME

--- --- --- --- --- --- --- ---Amarjeet

Amarjeet 30 30 BCA BCA airwanairwan Atinder

Atinder 04 04 BCA BCA sawanchaksawanchak Dushyant

Dushyant 34 34 BCA BCA jagatpurjagatpur Kishore

Kishore 01 01 BCA BCA NagriNagri Prabhat

Prabhat 06 06 BCA BCA HatlimoreHatlimore Vinay

(15)

6 rows selected 6 rows selected

SQL> SELECT*FROM STUDENT ORDER BY name desc; SQL> SELECT*FROM STUDENT ORDER BY name desc;

NAME

--- --- --- --- --- ---

---Vinay

Vinay 08 08 BCA BCA barnotibarnoti Prabhat

Prabhat 06 06 BCA BCA HatlimoreHatlimore Kishore

Kishore 01 01 BCA BCA NagriNagri Dushyant

Dushyant 34 34 BCA BCA jagatpurjagatpur Atinder

Atinder 04 04 BCA BCA sawanchaksawanchak Amarjeet

Amarjeet 30 30 BCA BCA airwanairwan

6 rows

SQL> SELECT * FROM STUDEN

SQL> SELECT * FROM STUDEN ORDER BY roll_no desc;ORDER BY roll_no desc;

NAME

--- --- --- --- --- ---

---Kishore

Kishore 01 01 BCA BCA NagriNagri Atinder

Atinder 04 04 BCA BCA sawanchaksawanchak Prabhat

(16)

Vinay

Vinay 08 08 BCA BCA barnotibarnoti Amarjeet

Amarjeet 30 30 BCA BCA airwanairwan Dushyant

Dushyant 34 34 BCA BCA jagatpurjagatpur

6 rows

6)

6) MODMODIFYINIFYING THE STRG THE STRUCTUUCTURE OF TABRE OF TABLESLES: -: -

The st

The struct

ructure of

ure of a table

a table

can be

modified by using the ALTER

TABL

TABLE com

E command.

mand. ALTER

ALTER TABL

TABLE allo

E allows ch

ws changing t

anging the str

he structure

ucture of an

of an

existing table. With ALTER TABLE it is possible to add or delete columns,

create or destroy indexes, changes the data type of existing columns, or

rename columns or the table itself.

(a)

(a) ADDING NEW COLUMNSADDING NEW COLUMNS Syntax

Syntax: - ALTER TABLE <Table name> ADD(<New column Name> <data: - ALTER TABLE <Table name> ADD(<New column Name> <data type> (<size>),<new column

type> (<size>),<new column name><data type>(<size>)….);name><data type>(<size>)….); Example

Example:-

:-(b)

(b)DROPPING A COLUMN FROM A TABLEDROPPING A COLUMN FROM A TABLE Syntax

Syntax: - ALTER TABLE<TABLE NAME>DROP COLUMN<COLUMNNAME>;: - ALTER TABLE<TABLE NAME>DROP COLUMN<COLUMNNAME>; Example

Example:-:- alter table prabhu drop column name;alter table prabhu drop column name;

(c)

(c) MODIFYING EXISTING COLUMNSMODIFYING EXISTING COLUMNS Syntax:

-Syntax: - ALTER TABLE<Table name>MODIFY(<COLUMN NAME> <NEWALTER TABLE<Table name>MODIFY(<COLUMN NAME> <NEW DATATYPE>(<NEW SIZE>));

DATATYPE>(<NEW SIZE>)); Example

(17)

7)

7) RENRENAMAMING ING TABTABLESLES: -: -

Oracle allows renaming of tables. The rename

o

op

pe

erra

attiio

on

n

iis

s d

do

on

ne

e a

atto

om

miic

ca

alllly

y,

, w

wh

hiic

ch

h m

me

ea

an

ns

s

that no other thread can access any of the tables while the rename process

is running.

Syntax:

Syntax: - RENAME <Table name> to <New Tablename>- RENAME <Table name> to <New Tablename> Example

Example:-

:-SQL> rename student to

SQL> rename student to candidates;candidates;

Table renamed. Table renamed.

8)

8) DESDESTROTROYINYING TABG TABLESLES:-

:-•

• DROP COMMANDDROP COMMAND: - By using the DROP TABLE statement with the table name: - By using the DROP TABLE statement with the table name

w

we e ccaan n ddeessttrrooy y a a ssppeecciiffiic c ttaabblle e .. Syntax

Syntax: - DROP TABLE <table name>;: - DROP TABLE <table name>; Example

Example:--

:--SQL> Drop table

SQL> Drop table student;student; Table dropped.

Table dropped.

•

• TRUNCATE COMMANDTRUNCATE COMMAND:- The truncate :- The truncate command is much faster in comparisoncommand is much faster in comparison

to delete to delete

statement but similar to the drop command as to statement but similar to the drop command as to destroy a specific table.

destroy a specific table. Syntax:

Syntax:- - TRUNCATE TRUNCATE table table <tablename><tablename> Example

Example:-

:-SQL> truncate table employees; SQL> truncate table employees; Table truncated.

(18)

9)

9) DISPLDISPLAYINAYING THE G THE TABLTABLE STRUE STRUCTUCTURERE:- To display information about:- To display information about the

the columns columns defined defined in in aa table use the following syntax.

table use the following syntax. Syntax

Syntax: - DESCRIBE <table name>: - DESCRIBE <table name>

This command displays the columns names, the data types a

This command displays the columns names, the data types and thend the special attributes connected to the table.

special attributes connected to the table. Example

Example:: -

-SQL> describe employees; SQL> describe employees;

Name

Name Null? Null? TypeType

--- - --- --- --- ---EMP_ID NUMBER(5) EMP_ID NUMBER(5) EMP_NAME VARCHAR2(20) EMP_NAME VARCHAR2(20) D DEEPPTT__IIDD NNUUMMBBEERR((1100)) DEPT_NAME NAME(12) DEPT_NAME NAME(12) SALARY NUMBER(21) SALARY NUMBER(21) 10)

10) UPDAUPDATING TING THE CTHE CONTEONTENTS NTS OF A TAOF A TABLEBLE: - The update command is: - The update command is used

used to to change change or or modifymodify

data values in a table.

data values in a table. The verb UPDATE in SQL is used to eitThe verb UPDATE in SQL is used to either all theher all the rows from a table or a select set of rows from a table.

rows from a table or a select set of rows from a table.

•

• UPDATING ALL ROWSUPDATING ALL ROWS:- The update statement updates columns in the:- The update statement updates columns in the

existing table’s rows existing table’s rows

with new values .The SET clause indicates which with new values .The SET clause indicates which column data should be modifying and the new values that they should hold. column data should be modifying and the new values that they should hold. The W

The WHERHERE CLE CLAUSE AUSE specifspecifies ies whicwhich rowh rows shs should ould be upbe updated. dated. OtherOtherwise wise allall table rows are updated.

table rows are updated. Syntax

Syntax: - UPDATE < Table name> SET <column name1>=<expression1> ,: - UPDATE < Table name> SET <column name1>=<expression1> , <column name2>=<expression2>;

(19)

•

• UPDATES RECORDS CONDITIONALLY UPDATES RECORDS CONDITIONALLY :-

:-Syntax

Syntax:- UPDATE <table name> SET <columnname1> = <expression1>,:- UPDATE <table name> SET <columnname1> = <expression1>, <columnname2> = <expression2> WHERE <condition>;

<columnname2> = <expression2> WHERE <condition>;

CONSTRAINTS CONSTRAINTS 1

111)) NNOOT T NNUULLLL:- The NOT NULL column constraint ensures that a:- The NOT NULL column constraint ensures that a table column cannot be left

table column cannot be left

empty. When a column is defined as not null, then t empty. When a column is defined as not null, then thathat column becomes a mandatory colum

column becomes a mandatory column. It implies that a n. It implies that a value must bevalue must be entered into the column if the record is to be accepted for storage in the entered into the column if the record is to be accepted for storage in the table.

table. Syntax

Syntax:- <Column :- <Column Name> Name> <data type>(<size><data type>(<size>) ) NOT NULL NOT NULL ;; Example

Example:-:-name varchar(22) not null;name varchar(22) not null;

THE PRIMARY KEY CONSTRAINT

THE PRIMARY KEY CONSTRAINT: - A primary is one or : - A primary is one or more columnmore column in a table

in a table

used to identify each row in a table. None of used to identify each row in a table. None of the fields that are part of the primary key can contain a null value. A table the fields that are part of the primary key can contain a null value. A table can have only one primary

can have only one primary Syntax

Syntax:- <Column name> <data type>(<size>) PRIMARY KEY:- <Column name> <data type>(<size>) PRIMARY KEY Example

Example:-

:-SQL> create table student

SQL> create table student name varchar2(12), roll_noname varchar2(12), roll_no number(12) primary key, class varchar2(21) NOT NULL, dob number(12) primary key, class varchar2(21) NOT NULL, dob date);

date);

or or

(20)

SQL> create table student(name varchar2(12), roll_no SQL> create table student(name varchar2(12), roll_no number(12)

number(12) constraint constraint pk_roll pk_roll primary primary key ,key ,class class varchar2(21varchar2(21)) not null, dob date);

not null, dob date);

1

122)) TTHHE FE FOORREEIGIGN N KKEEY (Y (SSEELLF RF REEFFEERREENNCCEE) C) COONNSSTTRRAAIINNTT:-Foreign:-Foreign key represent

key represent

relatio relatio nships between tables. A foreign key is a

nships between tables. A foreign key is a column (or a group of columns)column (or a group of columns) whose values are derived from the primary key or unique key of some other whose values are derived from the primary key or unique key of some other table. The table in which t

table. The table in which the foreign key is defined is cahe foreign key is defined is called a FOREIGNlled a FOREIGN TABLE

TABLE or DEor DETAIL TAIL TABLETABLE. The . The table table that dthat defines efines the prthe primarimary oy or unir unique kque keyey and is referenced by the foreign key is

and is referenced by the foreign key is called the PRIMARY KEY or MASTERcalled the PRIMARY KEY or MASTER KEY.

KEY. Syntax

Syntax: - Fore: - Foreign key (<column name>) REFERENCEign key (<column name>) REFERENCES <tableS <table name>(column name);

name>(column name); Example

Example:-

:-SQL> create table

SQL> create table department(ddepartment(dept_no number(10) primaryept_no number(10) primary key,dept_na

key,dept_name me varchar2(25varchar2(25),dept_loc ),dept_loc char(5,e_no number(11),char(5,e_no number(11), foreign key(e_no) references employee

foreign key(e_no) references employee (e_no);(e_no);

Table created Table created

SQL> describe department; SQL> describe department;

Name

--- -- --- -- --- -DEPT_NO

DEPT_NO NOT NOT NULL NULL NUMBER(10)NUMBER(10)

DEPT_NAME VARCHAR2(25) DEPT_NAME VARCHAR2(25) DEPT_LOC CHAR(5) DEPT_LOC CHAR(5) E_NO NUMBER(11). E_NO NUMBER(11).

(21)

1

133)) TTHHE E UUNNIIQQUUE E KKEEY Y CCOONNSSTTRRAAIINNTT:- The unique key constraint:- The unique key constraint permits multiple

permits multiple

entries of NULL into the column. These NULL entries of NULL into the column. These NULL values are clubbed at the top of

values are clubbed at the top of the column in the order in which tthe column in the order in which they werehey were entered into the table. This

entered into the table. This is the essential difference between the primaryis the essential difference between the primary key and the unique constraints when applied to table column(s). Key point key and the unique constraints when applied to table column(s). Key point about UNIQUE constraint:

about UNIQUE constraint:

•

• Unique key will not allow duplicate values.Unique key will not allow duplicate values. •

• Unique index is created Unique index is created automatically.automatically. •

• A table can have more than one A table can have more than one unique key which is not possible inunique key which is not possible in

primary key. primary key.

Syntax

Syntax:- CREATE :- CREATE TABLE TABLE Table name Table name (<columnNa(<columnName1>me1> <datatype>(<size>), <columnName2> <data

<datatype>(<size>), <columnName2> <data

type>(<size>),UNIQUE(<columnName1>, <columnName2>)); type>(<size>),UNIQUE(<columnName1>, <columnName2>)); Example

Example:-

:-SQL> create table

SQL> create table student1(rolstudent1(roll_no number(12)primary key,dobl_no number(12)primary key,dob date,name varchar2(20),class varchar2(2),e_mail

date,name varchar2(20),class varchar2(2),e_mail varchar2(20)varchar2(20) constraint un_st unique);

constraint un_st unique);

Table created. Table created.

To see the description of the table. To see the description of the table.

SQL> Describe student1; SQL> Describe student1;

Name

(22)

---ROLL_NO

ROLL_NO NOT NULL NOT NULL NUMBER(12)NUMBER(12)

DOB DATE DOB DATE NAME VARCHAR2(20) NAME VARCHAR2(20) CLASS VARCHAR2(20) CLASS VARCHAR2(20) E_MAIL VARCHAR2(20) E_MAIL VARCHAR2(20)

ORACLE FUNCTIONS

Oracle functions serve the purpose of

Oracle functions serve the purpose of manipulating data items andmanipulating data items and returning a result. Functions are the programs that take zero or

returning a result. Functions are the programs that take zero or moremore arguments and return a single value. Oracle has built a no. of

arguments and return a single value. Oracle has built a no. of functions intofunctions into SQL. These functions can be

SQL. These functions can be called from SQL statements.called from SQL statements.

14)

14) COCOUNUNT (eT (exprxpr) fu) functnctionion: - Returns the number of rows where: - Returns the number of rows where expression is not null.

expression is not null. Syntax:

-Syntax: - COUNT ([<distinct>[<all>] <expr>)COUNT ([<distinct>[<all>] <expr>) Example

Example:-

:-EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---1 1 sourabh sourabh 21 21 5500055000 2 2 sonu sonu 22 22 5500055000 3 3 anku anku 4 4 5500055000 5 5 anku anku 21 21 5500055000 3 3 panku panku 22 22 7500075000

SQL> select count(distinct name) from

(23)

COUNT(DISTINCTNAME) COUNT(DISTINCTNAME) ---4 4

SQL> select count(salary) from employees; SQL> select count(salary) from employees;

COUNT(SALARY) COUNT(SALARY) ---5 5 15)

15) COUCOUNT (*) fNT (*) functiounction n : -: - Returns the number of rows in the table,Returns the number of rows in the table, iinncclluuddiinng g dduupplliiccaattees s aannd d tthhoosse e wwiitth h nnuullllss.. Syntax

Syntax: : - - COUNT(*)COUNT(*) Example

Example:-

:-SQL> select count(*) from

SQL> select count(*) from employees;employees;

COUNT(*) COUNT(*) ---5 5 SQL>

SQL> select select count(*)"salcount(*)"salary" ary" from from employees;employees;

salary salary ---5 5

(24)

16)

16) THE THE SUSUM M FUFUNCNCTIOTIONN: - Returns the sum of the values of ‘n’.: - Returns the sum of the values of ‘n’. Syntax

Syntax: - : - SUM ([<distinct>][<all>] <expr>)SUM ([<distinct>][<all>] <expr>) Example

Example:-

:-SQL> select sum

SQL> select sum (salary) from employees;(salary) from employees;

SUM(SALARY) SUM(SALARY) ---295000 295000 17

17)) THTHE ME MAX FAX FUNUNCTCTIOIONN: - Returns the maximum value of expression.: - Returns the maximum value of expression. Syntax

Syntax: - : - MAX([<distinct>][<MAX([<distinct>][<all>] <expr>)all>] <expr>) Example

Example:-

:-SQL> select max(salary) from employees; SQL> select max(salary) from employees;

MAX(SALARY) MAX(SALARY) ---75000 75000 18

18)) THTHE ME MIN FIN FUNUNCTCTIOIONN: - : - Returns the minimum value of expression.Returns the minimum value of expression. Syntax

Syntax: - : - MIN ([<distinct>][<all>] <expressionMIN ([<distinct>][<all>] <expression>)>)

Example Example:-

:-SQL> select min

(25)

MIN(SALARY) MIN(SALARY) ---55000 55000 19

19)) THTHE AVE AVG FUG FUNCNCTIOTIONN: - Returns an average value of : - Returns an average value of ‘n’, ignoring null‘n’, ignoring null v

vaalluuees s iin n a a ccoolluummnn.. Syntax

Syntax: - : - AVG ([<distinct>][<all>] <n>);AVG ([<distinct>][<all>] <n>); Example

Example:-

:-SQL> select avg(salary) from employees; SQL> select avg(salary) from employees;

AVG(SALARY) AVG(SALARY) ---59000 59000 2

200)) LLIIKKE E OOPPRREEAATTOORR:- The LIKE predicate allows comparison of one:- The LIKE predicate allows comparison of one string value with

string value with

another string value, which is not identical. This is another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that achieved by using wildcard characters. Two wildcard characters that areare available are:

available are:

•

• % allows to match any string of any length(including zero length)% allows to match any string of any length(including zero length) •

• _allow _allows to s to match match on a on a singlsingle che charactearacter.r.

Example Example:-

:-SQL> select emp_id,name,dept_id,salary from employees where SQL> select emp_id,name,dept_id,salary from employees where name like 'a%';

(26)

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- ---3 3 anku anku 4 4 5500055000 5 5 anku anku 21 21 5500055000 •

• NOT LIKE OPERATORNOT LIKE OPERATOR:-

:-Example Example:-

:-SQL>select emp_id,name,dept_

SQL>select emp_id,name,dept_id,salary from id,salary from employees whereemployees where name

name not not like like 'a%';'a%';

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---1 1 sourabh sourabh 21 21 5500055000 2 2 sonu sonu 22 22 5500055000 3 3 panku panku 22 22 7500075000 SQL>select emp_id,name,dept_

SQL>select emp_id,name,dept_id,salary from id,salary from employees whereemployees where name like '_n_u';

name like '_n_u';

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---3 3 anku anku 4 4 5500055000 5 5 anku anku 21 21 5500055000

(27)

2

211)) IIN N OOPPEERRAATTOORR:- In case a value needs to be compared to a list:- In case a value needs to be compared to a list of values then the IN

of values then the IN

predicate is used. The IN predicates helps reduce the predicate is used. The IN predicates helps reduce the need to use multiple OR conditions.

need to use multiple OR conditions. Example

Example:-

:-SQL> select emp_id,name,dept_id,salary from employees where SQL> select emp_id,name,dept_id,salary from employees where dept_id in(20,22);

dept_id in(20,22);

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---2 2 sonu sonu 22 22 5500055000 3 3 panku panku 22 22 7500075000 •

• NOT IN OPERATORNOT IN OPERATOR:-

:-Example Example:-

:-SQL>select emp_id,name,dept_

SQL>select emp_id,name,dept_id,salary from id,salary from employees whereemployees where dept_id not in(20,22);

dept_id not in(20,22);

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---1 1 sourabh sourabh 21 21 5500055000 3 3 anku anku 4 4 5500055000 5 5 anku anku 21 21 5500055000 2 222)) BBEETTWWEEEEN N OOPPEERRAATTOORR:-

(28)

:-SQL> select emp_id,name,dept_id,salary from employees where SQL> select emp_id,name,dept_id,salary from employees where dept_id between 22 and 30;

dept_id between 22 and 30;

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---2 2 sonu sonu 22 22 5500055000 3 3 panku panku 22 22 7500075000 SQL>select emp_id,name,dept_

SQL>select emp_id,name,dept_id,salary from id,salary from employees whereemployees where dept_id between 11 and 21;

dept_id between 11 and 21;

EMP_ID

EMP_ID NAME NAME DEPT_ID DEPT_ID SALARYSALARY --- --- --- --- --- --- --- ---1 1 sourabh sourabh 21 21 5500055000 5 5 anku anku 21 21 5500055000

STRING FUNCTIONS

2

233)) UUPPPPEER R ffuunnccttiioonn :- :- Returns chReturns char, with ar, with all letters forced all letters forced toto uppercase.

(29)

Syntax

Syntax: - UPPER(char): - UPPER(char) Example

Example: -:

-SQL> select upper(name) from employees; SQL> select upper(name) from employees;

UPPER(NAME) UPPER(NAME) ---SOURABH SOURABH SONU SONU ANKU ANKU ANKU ANKU PANKU PANKU 2

244)) LLOOWWEER R ffuunnccttiioonn: - Returns char, with all : - Returns char, with all letters in lowercase.letters in lowercase. Syntax

Syntax: - LOWER(char): - LOWER(char) Example

Example:-

:-SQL> select lower(name) from employees; SQL> select lower(name) from employees;

LOWER(NAME) LOWER(NAME) ---sourabh sourabh sonu sonu anku anku anku anku

(30)

panku panku

2

255)) IINNIITTCCAAP P ffuunnccttiioonn: -: - Returns a string with the first letter of Returns a string with the first letter of each word in upper case.

each word in upper case. Syntax

Syntax:- INITCAP(char):- INITCAP(char) Example

Example:-

:-SQL> select initcap(name) from employees; SQL> select initcap(name) from employees;

INITCAP(NAME) INITCAP(NAME) ---Sourabh Sourabh Sonu Sonu Anku Anku Anku Anku Panku Panku