Weebly



ORACLE 11g 15

Advantages of ORACLE 15

History of Oracle 16

DATA BASE 16

Database Management System(DBMS) 16

Eg: Dbase, FoxPro, Paradox etc.. 16

RDBMS 16

Eg: MS-Access, Oracle, Ms-SQL Server, Sybase etc. 17

Advantages of Rdbms 17

Difference between DBMS AND RDBMS 17

DBMS 17

RDBMS 17

Types Of Management Systems 17

The Basic Objects Of Rdbms: 18

Attribute 18

Relation 18

Oracle9i - The eBusiness Platform 19

Oracle 11g – Key Infrastructure Areas 19

Oracle 11g - Key application areas 19

Scalability and Performance 19

Oracle 11g - Providing an End-to-End Security Infrastructure 20

Oracle 11g - Development Platform for eBusiness Applications 20

Oracle 11g - Manageability 20

Oracle 11g - Windows Integration 20

Product of ORACLE 21

BACKEND TOOLS FRONTEND TOOLS 21

DBMS (SQL & PLSQL) DBA 21

E.F.CODD RULES: 22

Oracle Data types 22

The following are different data types supported by ORACLE 23

Rules for define a table name. 24

SQL * PLUS 25

Commands 25

DDL 25

Syntax 25

Example : 26

ALTER COMMAND 26

1. Add 26

2. Modify 26

3. Drop 26

Syntax: 26

Alter Table Drop column 26

Examples on Alter Command 26

Create a table with the following structure 26

Rename Command 27

Syntax: Rename to 27

DML 27

Insert command 27

Syntax: 27

Examples on Insert Command 27

First Create a table 27

To insert values into all Columns 27

Similarly insert some more values as follows 28

To insert values into specified Columns 28

Update Command 28

Syntax: 28

Examples: 28

To update only one column value 28

Delete Command 28

Syntax : Delete From where 29

Ex : To Delete A Single Row 29

Ex : To Delete Multiple Rows (More Than One Row) 29

TCL 30

Syntax : commit; 30

Syntax : Rollback To [Savepoint] Savepoint_Name 31

Syntax : 31

DCL 31

Examples : 33

Between 33

Syntax : [ Not ] Between 33

Examples : 33

Examples : 34

Built In Function in Oracle 35

Character Functions or Text Functions or String Functions 37

Date and Time Functions 40

Add_Months : 40

Last_day : 40

Next_Day 41

months_between 41

Count 41

Sum(sal) 42

Syntax : Sum(distinct ) 42

Examples : 42

Max(sal) : 42

Syntax : Max(numerical column) 42

Example : 42

Min(sal) : 42

Syntax : Min(numerical column) 42

Example: 42

Write a query to find minimum sal earning by an employee in the emp table 42

Avg(sal) : 42

Syntax : Avg(distinct ) 42

Example : 42

Group By Clause 42

Having Clause 43

Examples : 43

Order By Clause 43

To_char : 45

NVL FUNCTION 46

Constraints 48

Note : Constraints can be imposed in two ways 48

Table Level : 48

Column Level : 48

NOT NULL 49

Syntax: 49

Not Null in Column Level 49

Adding Not Null Using Alter Command for Existing Table 49

Note : 49

Example : 49

Create table sample1(sno number(3), sname varchar2(20) not null); 49

Error Comes 49

Unique Constraint 49

Syntax: (For Column Level) 50

Syntax: (For Table Level) 50

Example : Imposing unique constraint Table Level 50

Example : Imposing unique constraint Column Level 50

Default Constraint : 50

Note : Only Column level definition can be applied to “Default constraint” 50

Syntax : for column level 50

Create Table (Column1 default , 50

Example : 50

Example : 2 51

SNO SNAME 51

Check Constraint 51

Syntax : (column Level) 51

Example: 51

Syntax : (Table Level) 52

Example : 52

Primary Key Constraint 52

Syntax: Table Level 52

Ex: 52

Error Comes 52

Syntax : Column Level 52

Example : 52

Error Comes 52

Example 2 52

Error Comes 53

Foreign Key Constraint 53

There are five major restrictions related to foreign key 53

Syntax : (Column Level) 53

Syntax : (Table Level) 53

Making relationships between two tables 53

2. Next Create the Child Table 53

3. Next insert the values in to mother table first, 54

101 Mahesh 6500 54

ERROR : (since there is no such account number present in the mother table) 54

SQL> select * from banktrans; 54

Dropping Relationship Tables 54

Note : 54

Joins 58

Table and column alias names 58

Types of Joins 58

General Syntax : 58

Equi Join 58

Example : 58

List the employee numbers, names, department numbers and dept names 58

Using Table Aliases 59

Non Equi Join 59

Example : 59

Outer Join 59

Left Outer Join 59

Example : 59

Right Outer Join 60

Example : 60

Rules to place ( + ) Operator 60

Full outer joins. 60

Here it will show both the extra field from two table. 60

P.OUTER JOIN 60

NATURAL JOIN 60

Self Join 61

select e1.ename,e1.deptno from emp e1,emp e2 61

where e2.ename='FORD' and e1.deptno=e2.deptno; 61

Cross Join (oracle 9i concept) 61

Syntax 62

Example : 62

SUB QUERY 62

Sub Queries Or Nested Queries 62

Using Sub Queries, 63

Using sub Query, 63

ERROR at line 1: 64

ORA-01427: single-row subquery returns more than one row*******DELETE & UPDATE 64

SET OPEREATOR 67

Union, Intersect and Minus Operators 67

Syntax : 67

Syntax : 68

Syntax : 69

Special Operators used in Sub Queries 69

All, Any and Some Operators 69

ANY Operator 69

Example : 70

ALL Operator 70

Example : 70

SOME Operator 71

Example : 71

Syntax : 71

Create View 71

Advantages of Views 71

Types of View 71

Simple View 72

Syntax : 72

Examples : 72

We can change column names in a view at the time of creation 72

¬ Create a view, which shows all employees details who belong to dept 20 72

We can Create a view, using another view 72

To Create Read Only Views 72

Example : 72

ERROR at line 1: 73

ORA-01732: data manipulation operation not legal on this view 73

ERROR at line 1: 73

ORA-01732: data manipulation operation not legal on this view 73

Composite View 73

Syntax : 73

Create view as select from 73

ERROR at line 1: 73

ORA-01776: cannot modify more than one base table through a join view 73

Dropping a view 74

Drop view 74

Note : 74

SEQUENCE 75

Syntax : 75

Start with : it is a keyword to specifying the starting value in the sequence creation 75

Maxvalue : specifies the maximum value or limit that the sequence can generate 75

Order : generates the sequences in the order of request 75

Attatching Sequence 76

Example : 76

Using Insert Command 76

Syntax : 76

Insert into (columns list) values(sequencename.nextval,……); 76

ERROR at line 1: 76

Using Update Command 76

Syntax : 76

Update set =, ------; 76

Updating only one value 76

Updating Multiple Values 77

Altering a Sequence 77

Syntax : 77

Example 77

Again Altering the above sequence 77

Drop sequence : 77

Syntax : drop sequence 77

Note : 78

Synonym 78

Clusters 79

Syntax : 79

To create any cluster 79

Note : 79

First create the cluster, 79

Next create the index, 80

Attaching the cluster to the table, 80

Example : 80

SNO SNAME 80

SNO SNAME 80

SNO SNAME 80

SNO SNAME 80

ERROR at line 1: 81

ORA-00942: table or view does not exist 81

Drop cluster 81

Syntax : drop Cluster 81

INDEX 81

Types of index. 81

Simple Index 82

ERROR at line 1: 82

ORA-00942: table or view does not exist 82

Creating Unique indexes on a Single column 82

Syntax : 82

Example : 82

Error : 83

Ora-00001: unique constraint (scott.idx_temp) violated 83

Note : 83

ERROR at line 1: 83

ORA-01408: such column list already indexed 83

Composite index 83

Syntax : 83

Composite unique index 83

Syntax : 83

Bitmap index 83

Syntax : 83

Drop index 84

Syntax : Drop index 84

To View Index 84

Clusters 84

Syntax : 85

To create any cluster 85

Note : 85

First create the cluster, 85

Next create the index, 85

Attaching the cluster to the table, 85

MERGE 86

RANKING 86

PRIVILEGES 86

Table Lock 89

SPOOL 'C:\ravi.txt'; ENTER 90

NEXT SELECT * FROM EMP ENTER 90

SPOOL OFF ENTER 90

THEN OPEN C 90

PIVOT 90

NORMALIZATION 91

PL/SQL PROGRAMMING 93

PLSQL DATA TYPES 93

IF condion 98

LOOP Condition 101

Types Of Loops 101

DECODE 108

CASE 108

Exp of Boolean Datatypes. 109

CURSOR 110

Types Of Cursor 110

IMPLICIT 111

STRONG CORSOR 111

WEEK CURSOR 112

CORSOR WITH FOR LOOP 113

WITH WHILE LOOP 114

WITH GROUP BY CLAUSE 114

WITH JOINS 114

WITH CASE FUNCTION 115

EXCEPTION 116

PL/SQL FUNCTION 123

PROCEDURE 128

PACKAGE 137

TRIGGER 139

VARRAY 144

BULK BINDING 145

NESTED TABLE 145

PARTITION TABLE 146

ORACLE 11g

Introduction

It is the most existing software for computers available in the market. Over more than 10 years the ORACLE RDBMS developed and improved a full featured. The ORACLE family of database predicted includes several powerful development and generation tools. These tools provides complete specialty for the system design and developing for the design and testing of software product.

Advantages of ORACLE

1. Back up and recovery

2. Portability

3. Performance

4. Multiple data base support

5. Security

6. Convertibility

7. Compatibility

8. Data Integration

9. Centralized Administration

10. Multi User Data Access

History of Oracle

Oracle first time released in 1979 with RDBMS concepts.

➢ Oracle 7.0 is released with RDBMS Concepts

➢ Oracle 7.3 is released with RDBMS Concepts

➢ Oracle 8.0 is released with ORDBMS

➢ Oracle 8i is released with Internet features

➢ Oracle 9i is released with Extended Internet features

➢ Oracle10g is released with Gride Features

➢ Oracle 11g is released with Extended Gride Features

DATA BASE

It is a collection of data items stored together in one or more data files with minimum redundancy in order to serve data to one ore more applications simultaneously

DBMS Environment (

Database Management System(DBMS)

It is a software used to create, destroy and Maintain database or in another words It is a Tool or Software that is integrated with a set of programs it will perform the following tasks

1. Adding Records

2. Updating Records

3. Modifying Records

4. Deleting Records

5. Arranging Records in to an Order

Eg: Dbase, FoxPro, Paradox etc..

RDBMS

Relational data mode is currently the most popular one of database management systems because it is conceptually simple and understandable by information systems, professionals and many end users.

This Model represents data in terms of relations using the concepts set theory, relational algebra and relational calculus

Eg: MS-Access, Oracle, Ms-SQL Server, Sybase etc.

A Typical Example of a Relation (

Advantages of Rdbms

1. It provides minimum data redundancy and minimum data in consistency.

2. It eliminates the difficulties in accessing and manipulating data.

3. It offers uniform security and privacy control.

4. Data sharing can be accomplished easily.

5. It provides data integration.

Difference between DBMS AND RDBMS

|DBMS |RDBMS |

|The concept of relation ship is missing in DBMS if it exists it is |Based on the concept of relation ships. |

|very less | |

|Speed of operation is very slow |Speed of operation is very fast |

|Hardware and software requirements are less |Hardware and software requirements are high. |

|Platform used is normally DOS and Windows |Platform used can be many UNIX, WINDOWS, DOS, VAX etc. |

|Uses the concept of a file |Uses the concept of Table |

|DBMS normally called 3rd generation language |RDBMS normally called 4th generation language. |

E.F Codd develops the relational data base model in 1970. He is a statistician; he provided 12 rules for an RDBMS. If a software an RDBMS, then it most satisfies these 12 rules.

Types Of Management Systems

File management system

*************************

1. There is no security of data.

2. It is not supporting to multiple users.

3. To select insert update delete record it is very difficult.

Hierarchic database

********************

Use to store the information with reverse tree structure.

Network database

******************

Use to store the information with physical link.

Relational database (RDBMS)

**************************

Use to maintain the relation between two-dimensional tables.

ONE - ONE

ONE -MANY

MANY - MANY

If any database supports more than six rules of Mr.E.f codds rule

That database is known as complete relational database management system.

The Basic Objects Of Rdbms:

1. Entity

2. Attribute

3. Relation

Entity THE RELATIONAL

A true statement regarding an object is called entity. I.e. the object may be person on place or any thing. Entities in the data base are implemented by Table.

For Eg: roll no 25 in a class is an entity, since it uniquely identified a person in the class.

Attribute

An additional quality of entity is called attribute. Attributes are implementing using columns.

Eg: eno, ename, sal etc.

Relation

The relation object specifies relation between two or more entities for staring a valid data into the database.

These relations are implemented using constraints. These constraints are called mapping cardinalities. These are useful in describing the relationship sets.

there are different ways to create relationship between 2 or more tables. that is called e-r modeling.

Oracle9i - The eBusiness Platform

Oracle9i continues Oracle8i’s focus on the Internet by providing a series of specific capabilities and product bundles targeted at eBusiness environments. In addition, Oracle9i continues to add features and capabilities that extends existing investment in mission-critical infrastructure.

Oracle 11g – Key Infrastructure Areas

➢ Availability

➢ Scalability and Performance

➢ Security

➢ Development Platform

➢ Manageability

➢ Windows2000 Integration

Oracle 11g - Key application areas

➢ Internet Content Management

➢ eBusiness Integration

➢ Packaged Applications

➢ Business Intelligence Platform

➢ Achieving Continuous Data Availability

Oracle9i dramatically extends Oracle’s leadership in Internet database availability, critical for any eBusiness application. Key focus areas in Oracle9i include:

➢ Providing an industry leading zero data loss data protection environment

➢ Reducing offline maintenance requirements with support for more online operations

➢ Providing fast and precise repair of damaged databases

➢ Enabling end-users to identify and correct their own mistakes.

Scalability and Performance

Oracle9i allows eBusiness to scale to tens of millions of users performing millions of transactions per hour. Key focus areas include:

➢ Transparent cluster scalability and performance

➢ Scalable session state management

➢ Optimized features critical for eBusiness

Oracle 11g - Providing an End-to-End Security Infrastructure

Oracle9i continues to provide the most secure application development and deployment platform in the industry. Key focus areas include:

➢ Strong, three-tier security

➢ Standards-based Public Key Infrastructure (PKI)

➢ Deep Data Protection

➢ Improved user and security policy management

➢ Data Encryption

➢ Oracle Label Security

➢ Oracle Internet Directory

Oracle 11g - Development Platform for eBusiness Applications

Oracle11g continues to offer the best development platform for eBusiness and traditional application development. Key focus areas include:

➢ Enterprise Java Engine

➢ XML type and XDK

➢ SQL and PL/SQL improvements

Oracle 11g - Manageability

Management is one of the key areas of improvement for Oracle9i. There are five aspects to our approach taken with management in Oracle9i:

➢ Make the database self managing in certain key areas

➢ Streamline and improve the operational management of an Oracle9i database

➢ Provide tools and techniques that significantly simplify and reduce the task time required to administer Oracle9i

➢ Enable fine-grained, automatic resource management

➢ Provide an end-to-end system management solution that manages the entire Oracle stack, not just the database.

Oracle 11g - Windows Integration

Oracle9i continues Oracle’s lead as the platform of choice for organizations deploying on Windows 2000. Key focus areas are:

➢ Close integration with Windows 2000

➢ Facilitated development and deployment on MS Windows platforms

Product of ORACLE

➢ SQL * PLUS

➢ SQL *DBA

➢ SQL*NET

➢ SQL*FORMS

➢ SQL*REPORTS

➢ SQL*NEBYS

➢ SQL*GRAPHS

➢ SQL*LOADER

➢ ORACLE*FINANCIAL

➢ PRO*C

➢ PRO*COBAL

➢ PRO*FORTRAN

BACKEND TOOLS FRONTEND TOOLS

SQL/PLSQL (ORACLE) JAVA (ORACLE)

SQLSERVER (MS) .NET (MS)

MYSQL (OSDB) ORACLEFORMS(D2K) (ORACLE)

DB2 (IBM)

MS ACCESS (MS)

DBMS (SQL & PLSQL) DBA

Creating Tables Store The Data Creating Data Base

And Retrive The Data Increase and Decrease The DB Size

Creating New User ID

Lock,Unlock and Delete The User ID

Recovaring The Data

E.F.CODD RULES:

1. Information Rule : information is to be represented as data stored in cells.

2. Representing NULL values: NULL must be used in a constant manner, if NULL is treated as zero for missing numeric value and as blank for missing character values, then this violates this rule. NULL should be simply the missing data and have no values.

3. Language: Since RDBMS is a package we may not define programs using RDBMS. All most all-supporting language is SQL on IBM corporation.

4. Insert, Update, Delete : This feature specifies that the user must be capable of applying insert, update, delete operations at any time on any table.

5. BackEnd: An RDBMS must act as the backend software and with must be support provider for establishing connection from any client software to server software.

6. Client server architecture : It specifies accessing data of the server from client, manipulating in the client and once again sending back data to the server.

7. Supporting constraints or rules : Main purpose of constraints is inserting valid data into the database. The constraints does not allow insertion of NULL values, duplicate values into the columns and also allows establishing relation ships between tables for inserting common data or similar values.

8. Logical Data independency: this feature specifies that the data must be independent of the logical operations like imposing constraints and executing different programs using the data existing in the table.

9. Non-Subversion Rule: It specifies the system must allow the user for executing the programs defined in the earlier versions. For Eg: the programs defined in oracle 7.3 must be executed in oracle 8.0 with out any errors.

10. Physical data independency: This feature specifies that even through the position on physical location of the table changes there will must not be any effect in the table data.

11. Normalizing the data : It specifies that they should not be any repetition on the data exists in the data base.

12. Data Transformation: This feature specifies data transferring between the RDBMS and also data transferring with in the RDBMS i.e. between the databases and users using import and export features.

Oracle Data types

The information in a database is maintained in the form of table, each table consists of rows and columns to store the data. A particular column in a table must contain similar data, which is of a particular type.

The following are different data types supported by ORACLE

1. CHAR This data type is used to store fixed length character of the specified length. Where the maximum size is 2000 bytes for columns/rows.

Syntax: char (size)

Example : Result char(4)

VARCHAR2 This data type is used to store variable length characters.

Maximum it can take is 4000 bytes for columns/row.

Syntax: varchar2 (size)

Example : sname varchar2(15)

2. NUMBER this data type is used to store both numbers and numbers with decimal pointes. It can take maximum precision up to 38 digits after decimal.

Syntax: Number(value, precisions)

Example : Empno number(5) ( Pure Integers

Sal number(6,2) ( Numbers With Decimals

3. DATE This data type is used to store date and time in a table. The date data types stores year (including the century) . the month, the days, hours, minutes, seconds. The maximum size is 7 bytes for each row in a table.

Syntax: Date

Example : Doj Date

4. LONG This data type is used to store variable length character containing up to 2 GB of information.

Syntax: Long

Example : Remarks Long

Restriction of Long

There are some restrictions of long data type.

1) Only one column is defined as long for table.

2) Long columns con not be indexed.

3) Long columns can’t appear in integrity constraints.

4) Long columns can’t be used in SQL expressions.

5) Long columns cannot be referenced by the SQL function

5. CLOB This data type is used to store variable length character containing up to 4 GB of information.

6. BLOB This data type is used to store Photos,Video Clips up to 4 GB of information.

7. BFILE This data type is used to store Binary Data,Files Path up to 4 GB of information

8. RAW This data type is used to store Binary Data up to 2000Bytes of information.

9. LONG RAW This data type is used to store Binary Data up to 2GB of information.

10.TIMESTAMP

Date and time with fractional seconds.

11. INTERVAL YEAR TO MONTH

Stored as an interval of years and months.

12. INTERVAL DAY TO SECOND

Stored as an interval of days to hours minutes and seconds.

13. BINARY_INTEGER

The range of binary_integer is -2147483647 to 2147483647

14. BOOLEAN

Return true or false

Rules for define a table name.

1. The table name must not exceed 30 charactor.

2. The table name must begin with a alphabet between a and z.

3. The table name must not keep any spaces.

4. The table name cannot be the same as a oracle reserved key word.

5. The table name cannot be the same as an existing database object

with the same schema.

SQL * PLUS

This is a basic shell for quarries and reports and data base manipulating apart from the basic ability to issue SQL and pl/sql commands, it has a number of extensions to permit programming and report formatting.

What is SQL

SQL is an acronym of Structure Query Language. It is built to work around related data bases that are sets of related information stored in tables. ‘IBM’ as the language to inter face with its prototype relational database management system introduced SQL. The first commercially available SQL relation data base management was introduced in 1979 by ORACLE Corporation.

Through SQL a user can do

➢ Enter, edit, store, retrieve and run SQL commands.

➢ Format, perform calculate, store and print query result in the form of reports.

➢ List column definition of any table.

➢ Access and copy data between SQL databases.

SHOW USER....SET LINES...SYSDATE..TIMESTAMP..DICTIONARY...TAB..SQL PROMPT....DESC EMP,create new id

1. uid : this function is used to show the user id of the currently active user

syntax : uid

Example : select uid from dual;

Commands

DDL

➢ Create : It is use to create a table

Syntax

Create table

( [Size],

[Size],

[Size]);

Example :

➢ Create Table Sample ( sno number(3), sname varchar2(10));

ALTER COMMAND

It is used to change the structure of the Table i.e. adding new column, changing the data type and size. The alter command can have 3 types of sub commands. They are:

Add

Modify

Drop

4.Rename(It’s a 10g command)

1. ADD: By using this command we can add new columns to the existing table.

Syntax:

Alter Table add (

Column1 [],

Column2 [],

- - - - -,

Column(n) [])

2. Modify: It is used to change the Data Type and size of the existing columns. If you can change the Data Type and Size you must satisfy the following rules.

a. By using Modify command in alter we cannot change the column name.

b. We can not Change the Positions of the existing or new columns

c. We cannot decrease the length (Size) of an existing column, if that column is having values. But we can increase the size of the existing column even if the data is present

Syntax:

Alter Table Modify (

Column1 [],

Column2 [],

- - - - -,

Column(n) [])

3. Drop This command is introduced in Oracle 8i. It is used to remove the column of a table.

Syntax:

Alter Table Drop column

Examples on Alter Command

Create a table with the following structure

➢ Create table students(roll number(3), sname varchar2(20), sub1 number(3), sub2 number(3), sub3 number(3));

➢ Insert some values into that table

➢ GET BACK @DD AND ALSO DELETE COMND

4.Rename This command is used to rename column name.

Syntax

ALTER T TNAME RENAME OLDCNAME TO NNAME;

Drop This command is used to Drop a table

Syntax

Drop table t_name;

(GET BACK FLASHBACK T TNAME TO BEFORE DROP)

Rename Command

It is used to change the name of the Object like table.

Syntax: Rename to

Example: Rename sample to samples;

Example: Rename samples to sample;

DML

Insert command

The insert command is used to append the new records /rows / values at the end of the object (Table or View).

Syntax:

Insert into (columns list) values (value1, value2,….,value N)

Examples on Insert Command

First Create a table

➢ Create Table Sample ( sno number(3), sname varchar2(10), class varchar2(10));

To insert values into all Columns

➢ Insert into sample values (100, “Nithya”, ”First”);

After entering the above query on the SQL prompt, press the enter key, if you entered the query correctly, it displays a message “ 1 Record Inserted “ otherwise it displays an error message.

Similarly insert some more values as follows

➢ Insert into sample values (101, “Saloni”, ”Second”);

➢ Insert into sample values (102, “Aruna”, ”Third”);

To insert values into specified Columns

➢ Insert into sample(sno,class) values (103, “Fourth”);

Update Command

This command is used to update or modify all or specified column values with new values.

Syntax:

Update < Table Name > set = , =,

-------= where

Examples:

To update only one column value

➢ Update sample set sno=500 where sno=100;

1.Q: CHANGE THE DEPTNO OF JAMES TO 20 AND INC SAL 20%

UPDATE EMP SET DEPTNO=20 ,MGR=7788,

SAL=SAL+(SAL*20/100)

WHERE ENAME='JAMES';

2.Q: REPLACE COMM OF ALL SALESMAN OF SALES DEPT WITH 30% OF THEIR OWN SAL AND RECEIVING COMM ,COMM IS LESS THAN SAL BUT NOT ZERO....

UPDATE EMP SET COMM=SAL*30/100

WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') AND

JOB='SALESMAN' AND

COMM(SELECT SAL FROM EMP WHERE ENAME='SMITH') AND

SAL(SELECT AVG(SAL) FROM EMP WHERE EMPNO

IN(SELECT MGR FROM EMP));

****************************************************

Q: DELETE ALL THE EMPS WHO ARE SENIOR TO THE SENIORMOST EMP OF GRADE 4,DONT DELETE GRADE 5 EMPS

A: DELETE FROM EMP WHERE HIREDATE>(SELECT E.HIREDATE FROM EMP E,SALGRADE

S WHERE S.GRADE=4 AND E.SAL BETWEEN S.LOSAL AND S.HISAL) AND

EMPNO NOT IN(SELECT E.EMPNO FROM EMP E,SALGRADE S WHERE S.GRADE=5 AND

E.SAL BETWEEN S.LOSAL AND S.HISAL);

If emp table having any duplicate record delete that.

delete from emp where rowid in

(select rowid from emp e where rowid !=

(select max(rowid) from emp where e.empno=empno))

write a query to display the duplicate salary.

select * from emp where sal in(

select sal from emp where rowid not in(

select max(rowid) from emp group by sal))

DELETE DUPLICATE RECORD.

DELETE FROM EMP WHERE ROWID IN

(SELECT ROWID FROM EMP E WHERE ROWID !=

(SELECT MAX(ROWID) FROM EMP WHERE E.EMPNO=EMPNO))

DELETE FROM EMP WHERE ROWID IN

(SELECT ROWID FROM EMP E WHERE ROWID NOT IN

(SELECT MAX(ROWID) FROM EMP WHERE E.SAL=SAL))

/

TCL

Commit

The changes made by the user are not physically written to the table, giving only the user a view of his/her work. While the other users, having access to these tables, continue to get the old information. The “Commit” command is used to make the changes permanent to the database.

A commit statement guarantees all of the transactions, modifications are made permanent part of the data base. By default, all your transactions are temporarily stored in the database.

Syntax : commit;

SQL * Plus has the facility to automatically commit all the work, without explicitly issuing the Commit command

Set AutoCommit ON : Enable Autocommit feature

Set AutoCommit OFF : Disables Autocommit feature (default)

Rollback

This Rollback command is used to undo work done in the current transaction. Ie the user can continue with any number of inserts, updates and / or deletetion, and still undo the work, issuing the Rollback Command.

Syntax : Rollback To [Savepoint] Savepoint_Name

optional

SavePoint

Savepoint identifies a point in a transaction to which one can later rollback with the Rollback command. It is helpful when a transaction contains a large number of SQL statements and the user wants to commit only once when all are done. If required, one can rollback to a particular transaction. It works in a Last In First Out (LIFO) manner

Savepoint command Sets a save point within a transaction or to identify a point in a transaction to which you can later roll back.

Syntax :

Savepoint savepoint_name;

Note : Save point works as LIFO manner (Last In First Out). We can not rollback only middle part of the transaction. If you rollback to middle part, then last part also affects with the rollback command.

DCL

GRANT

This command is used to give the permission.

REVOKE

This command is used to stop the permission

DRL

Select

This command is used to REtrive the data from the db

Operators

1. Arithmetic operator.

+ , * , - , / .

parison operator.

= ,>, =, 1000 ;

3. List The Names Of The Clerks Working In The Department 20

➢ Select ename, job from emp where job = ‘Clerk’ and deptno = 20 ;

4. List The Names Of Analysts And Salesman

➢ Select ename, job from emp where job = ‘ANALYST’ or job=‘SALESMAN’ ;

5. List the details of the employees who have joined before the end of september 1981.

➢ Select * from emp where hiredate = 1000 and job like '%man%';

6. Select * from emp where job='clerk' or deptno=20;

7. Select * from emp where mgr is null;

8. Select * from emp where mgr is not null;

9. Select * from emp where job='clerk' or job='salesman'

and sal>1500;

10. Select * from emp where (job='clerk' or job='salesman')

and sal>1500;

11. Select * from emp where comm is null;

12. Select * from emp where comm is not null;

13. select * from emp where deptno=30 and job='salesman';

14. Select * from emp where deptno=10 or job='analyst';

15. Select ename||' '||'is a'||' '||job||' '||'working under deptno'||' '||deptno from emp;

16. Select * from emp where (deptno=20 or job='manager') and sal operating

11.Lpad :

This function is used to append the given text to the left side of any

column or String or lpad function allows you to “pad” the left side of a column

with any set of Characters.

Syntax : Lpad (,,)

Example : select lpad(sal,7,’Rs. ‘) from emp;

output

Rs. 800

Rs.1200

Example : select lpad(sal,10,’Rs. ‘) from emp;

output

Rs. Rs. 800

Rs. Rs.1200

12.Rpad :

This function is used to append the given text to the right side of any column or string or lpad function allows you to “pad” the left side of a column with any set of characters.

Syntax : Rpad(,,)

Example : select Rpad(sal,7,’Rs. ‘) from emp;

output

800Rs.

1200Rs.

Example : select Rpad(sal,10,’Rs. ‘) from emp;

output

800Rs. Rs.

1200Rs. Rs

13.Initcap :

This function takes the initial letter of every word in a string or column and converts just those letters to upper case.

Syntax : initcap (String)

Example : select Initcap(ename) from emp;

select Initcap(‘WELCOME TO ALL’) from dual; ( Welcome To All

14.Translate :

This function is used to translate the source expression into target expression that is present in the main string

Syntax : Translate(Main String, Source Expression, Target Expression)

Example : select Translate (‘jack’,’j’,’b’) from dual; ( back

select Translate (‘back and bill’,’b’,’j’) from dual; ( jack and jill

it will translate only one char

Date and Time Functions

Sysdate :

This Function Returns The System Date And Time. By default it will show only the data but not the time. To show time also, we have to user To_Char conversion function

Syntax : Sysdate

Example : select sysdate from dual;

To add five minutes

select to_char(sysdate +05/1440,'hh:mi:ss am') from dual;

To subtract ten minutes

select to_char(sysdate -10/1440,'hh:mi:ss am') from dual;

To change date format.

alter session set nls_date_format='dd-month-yyyy'

select sysdate-1 from dual

to find last date...+1 to tomrow...

Add_Months :

This function is used to add the number of months to the months part of the accepted dates. ( we can give positive/negative values )

Syntax : Add_Months(Date, Number)

Example : select sysdate, add_months(sysdate,5) from dual;

Output

SYSDATE ADD_MONTH

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

14-FEB-05 14-JUL-05

Last_day :

This Function Is Used To Return The Last Day Of Accepted Date (0r) Last Day Of the Month

Syntax : Last_day ( date expression )

Example : select sysdate, Last_Day(sysdate) from dual;

Output

SYSDATE LAST_DAY(

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

14-FEB-05 28-FEB-05

Next_Day

This function is used to find the Next day of the given weekday name

Syntax : Next_Day(Date Expression, Week day name)

1. Example : select sysdate, next_day(sysdate, ‘Monday’) from dual;

Output

SYSDATE NEXT_DAY(

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

14-FEB-05 21-FEB-05

months_between

This function is used to find number of months between the given two dates

Syntax : months_between(date expression1, date expression2)

Example : 1

2. select months_between(sysdate,

to_date(‘20-oct-05’,’dd-mon-yy’)) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE(‘20-OCT-05’,’DD-MON-YY’))

2.432654

To eliminate decimal points, give the following form

3. select round(months_between(sysdate,

to_date(‘20-oct-05’,’dd-mon-yy’))) from dual

ROUND(MONTHS_BETWEEN(SYSDATE,TO_DA

2

4. select round(months_between(to_date(‘20-oct-05’,’dd-mon-yy’),sysdate))

from dual;

Count

The count function is used to find the number of records or non – null columns values in the given column or expression

Syntax : count(* | distinct )

Examples :

write a query to count total number of records in the given table

➢ select count(*) from emp;

write a query to count, how many types of jobs available in the emp table

➢ select count(job) from emp;

select count(*) from emp WHERE JOB='MANAGER';

select count(*) from emp WHERE DEPTNO=30;

Select deptno,count(*) from emp group by deptno;

Sum(sal)

This command is used to find the sum of all Values of given numerical columns.

Syntax : Sum(distinct )

Examples :

Write a query to find sum of sal for all employees in the emp table

select sum(sal) from emp;

Max(sal) :

This command is used to find the maximum value from the given numerical column.

Syntax : Max(numerical column)

Example :

Write a query to find maximum sal earning by an employee in the emp table

select max(sal) from emp;

Min(sal) :

This command is used to find the minimum value from the given numerical column.

Syntax : Min(numerical column)

Example:

Write a query to find minimum sal earning by an employee in the emp table

select min(sal) from emp;

Avg(sal) :

This command is used to find the average value from the given numerical column.

Syntax : Avg(distinct )

Example :

Write a query to find average salary of all employee in the emp table

select avg(sal) from emp;

Group By Clause

The group by clause is used with “select” to combine a group of rows based on the values of a particular column or expression. Aggregate functions are used to return summary information for each group. The aggregate functions are applied to the individual groups.

List The Department Numbers And Number Of Employees In Each Department

select deptno, count(*) from emp group by deptno ;

List the jobs and number of employees in each job

select job, count(*) from emp group by job;

List The Total Salary, Maximum And Minimum Salary And The Average Salary Of Employees Job Wise

select job, sum(sal), avg(sal), max(sal), min(sal) from emp group by job ;

Select deptno,job,max(sal) from emp group by cube(job,deptno);

Select deptno,job,sum(sal),count(*) from emp group by job,deptno;

Select deptno,job,sum(sal),count(*) from emp group by cube(job,deptno);

Having Clause

The having clause is used to specify which groups are to be displayed that means it restricts the groups which returns on the basis of aggregate functions

(Or)

This is used to define condition on the columns used after the group by clause. It is used to restrict the number of rows by specifying a condition with the grouped columns

Examples :

List The average salary of all the Departments employing more than 5 people

select deptno, avg(sal) from emp group by deptno having count(*)>5;

List the jobs of all the employees whose maximum salary is >=5000

select job, max(sal) from emp group by job having max(sal)>=5000;

Order By Clause

The order by clause is used to arrange the rows in Ascending or in descending order. By default the select statement displays in ascending order. If you want to display in descending order, specify the “desc” keyword after the column name.

Multiple columns are ordered one within another, and the user can specify whether to order them in ascending or in descending order.

1. List The Empno,Ename,Sal In Ascending Order By Salary

➢ select empno,ename,sal from emp order by sal;

2. List The Employee Name In Ascending Order And Their Salaries In Descending Order

➢ select ename ,sal from emp order by ename, sal desc;

0. Select deptno,sum(sal) from emp

group by deptno

having sum(sal)>8000

Select deptno,max(sal) from emp group by deptno;

Select min(hiredate) from emp;

0. Select job,min(hiredate) from emp

group by job;

0. Select deptno,count(*),sum(sal),avg(sal) from emp

group by deptno;

0. Select deptno,min(sal) from emp

Where deptno in(20,30,10)

Group by deptno;

0. Select deptno,count(*) from emp

Group by deptno

Having count(*) >2

0. Select deptno,sum(sal) from emp

Group by deptno

Having sum(sal) 2000

order by job

Select deptno,avg(sal) from emp group by deptno;

To_char :

This function is used to change the format of accepted date into any predefind format.

Syntax : To_char(, )

Predefined Format’s Are

mm/dd/yy

yy.mm.dd

dd/mm/yy

dd.mm.yy

dd-mm-yy

mon yy

mon dd, yy

hh:mm:ss

mon dd yyyy hh:mi:mm (Am or Pm)

mm-dd-yy

yy mm dd

dd mon yyyy hh:mi:ss:mm (24 hour format)

hh:mi:ss:mmm (24 hour)

dy (to find week day number)

day (week day name)

dd (number of days in month)

yyyy (year in four digits)

yy (year of Last two digits)

year (spelt in terms of words)

month (month name)

w (week number)

Example : 1

select sysdate,to_char(sysdate,’dd mm yy’) from dual;

SYSDATE TO_CHAR(S

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

15-FEB-05 15 02 05

select sysdate,to_char(sysdate,’month dd day w’) from dual;

select to_char(sysdate,'ddsp-month-yyyy')from dual;

select to_char(to_date('2-jan-90'),'day-ddspth-month-year')from dual;

select to_char(to_date(empno,'j'),'jsp')from emp;

select to_char(to_date(comm,'j'),'jsp')from emp;

error: julian date must be between 1 and 5373484

Select to_char(to_date(comm,'j'),'jsp')from emp

Where comm is not null and comm0

> select to_char(sysdate,'ddspth-month-year')from dual;

> select to_char(sysdate,'ddspth-month-year,hh:mm:ss')from dual;

> select to_char(sysdate,'hhpm:mi:ss') from dual;

> select to_char(to_date('28-mar-2007'),'ddspth-month-year,hh24:mm:ss')from dual;

(out put)

sTO_CHAR(TO_DATE('28-MAR-2007'),'DDSPTH-MONTH-YEAR,H

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

twenty-eighth-march -two thousand seven,00:03:00

1. To_Date : this function is used to convert any character expression into a date expression according to the format you specified

Syntax : To_Date(, )

Example :

insert into student(jdate) values(‘jan-10-05’)

ERROR at line 1:

ORA-01858: a non-numeric character was found where a numeric was expected

insert into student(jdate) values(to_date(‘jan-10-05’,’mon-dd-yy’));

select round (to_date('05-mar-2007'),'year') from dual;

NVL FUNCTION

NVL :- Use to place any value if column contains null value.

select nvl(comm,1)comm from emp

NVL2 :- Nvl2 returns second not null argument.

select nvl2(10,20,30) from dual;

select nvl2(null,20,30) from dual;

select empno,ename,job,sal,comm,nvl2(comm,11,22) from emp;

NULLIF :- Nullif compare two value if both are same function returns

null if both are not same function returns first arguments.

select nullif(10,10) from dual;

select empno,ename,job,sal,comm,nullif(comm,500) from emp;

COALESCE :- The COALESCE function returns the first not null

expression of the list.

select coalesce(10,20,30,40) from dual;

select empno,ename,job,comm,coalesce(comm,sal,55) from emp

PSUDO COLUMNS The psudo columns are will not create by users they will create by defaults…so they are pre –defined columns…..

ROWID,ROWNUM,CURRENT VALUE,NEXT VALUE

You can find rowid,rownum in tables and current value,next value in sequence.

Select rowed,rownum from t_name;

Select seq_name.nextval from dual;

Select seq_name.currval from dual;

Constraints

These are the conditions or rules that we impose on any column for entering valid data into the table. Constraints are a part of the table definition that are used to limit the values entered into its columns.

Note : Constraints can be imposed in two ways

1. Table Level and

2. Column Level

Table Level :

Imposing constraints on a table level by specifying the constraint type at the end of the columns is called Table Level Constraints. In Table Level, the Constraint key word followed by Constraint name must be defined otherwise error comes.

Column Level :

Imposing constraints on a column by specifying the constraint type beside the column name is called Column Level Constraints

Constraints can be defined in two ways

1. In the Table Definition itself

2. Using Alter Command

The following are a list of constraints

S

1. NOT NULL CONSTRAINT

2. UNIQUE CONSTRAINT

3. PRIMARY KEY CONSTRAINT

4. CHECK CONSTRAINT

5. DEFAULT

6. REFERENCES (FOREIGN KEY CONSTRAINT )

NOT NULL

The Not Null constraint specifies that a column can not contain Nulls. To satisfy this constraint every row in the table must contain a value for the column.

if you do not specify not null, the column can contain nulls by default

Syntax:

Not Null in Column Level

➢ Create table ( Column Not Null,

Column2 ,-------);

Adding Not Null Using Alter Command for Existing Table

➢ Alter Table

Modify ( (size) not null)

Note :

1. Column must be empty to impose the not Null constraint with alter table statement.

2. Table Level is not applicable for Not Null constraint

Example :

Create table sample1(sno number(3), sname varchar2(20) not null);

➢ insert into sample1 values(&sno,’&sna’);

Enter value for sno: 100

Enter value for sna: Mahesh

Enter value for sno: 101

Enter value for sna: (nothing is entered)

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert

Unique Constraint

This constraint does not allow duplicate values into any column.

Syntax: (For Column Level)

Create Table (Column1 Unique,

Column2 ,--------);

Syntax: (For Table Level)

Create Table (Column1 ,

Column2 ,--------,

Constraint Unique );

( Or )

Example : Imposing unique constraint Table Level

create table customer(cno number(3),cname varchar2(20),

constraint con_un unique(cno));

➢ insert into customer values(&cno,’&cname’);

Enter value for cno: 100

Enter value for cname: Mahesh

Enter value for cn: 100

Enter value for c: Nithya

Error Comes

unique constraint (SCOTT.CON_UN) violated

Example : Imposing unique constraint Column Level

create table customer1(cno number(3) unique ,cname varchar2(20));

➢ insert into customer1 values(&cno, ‘&cname’);

Enter value for cno: 100

Enter value for cname: Mahi

Enter value for cn: 100

Enter value for c: Prasad

Error Comes

unique constraint (SCOTT.SYS_C00385) violated

Default Constraint :

The default constraint is used to specify a default value for the column, when you want to given default values. You can give default value as NULL or some other value.

Note : Only Column level definition can be applied to “Default constraint”

Syntax : for column level

Create Table (Column1 default ,

Column2 ,--------);

Example :

➢ create table sample2(sno number(3) default 10,sname varchar2(20), phone

varchar2(10) default null)

➢ insert into sample2 values(1,’Mahesh’,’11111’)

➢ insert into sample2 values(2,’Prasad’,’22222’)

➢ insert into sample2(sno, sname) values (3,’Nithya’)

➢ insert into sample2(sno, sname) values (4,’Saloni’)

➢ select * from sample2;

SNO SNAME PHONE

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

1 Mahesh 11111

2 Prasad 22222

3 Nithya

4 Saloni

Example : 2

create table sample3(sno number(3) default 100, sname varchar2(10) not null);

➢ insert into sample3 values(200,’Nithya’)

➢ insert into sample3 values(201,’Aruna’)

➢ insert into sample3(sname) values(‘Mahesh’)

➢ insert into sample3(sname) values(‘Saloni’)

select * from sample3;

SNO SNAME

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

200 Nithya

201 Aruna

100 Mahesh

100 Saloni

Check Constraint

The check constraint explicitly defines a condition. It will put restrictions on the range of the column values and the values to be accepted into the column. The condition of a check constraint can refer to any column in the table. But it can not refer to any column in other tables.

Syntax : (column Level)

Create table ( [size] check(condition),

,-----);

Example:

Create table items(itemno number(3) check(itemno>=100),itemname varchar2(10));

➢ Insert into items values (100,’Rice’)

➢ insert into items values(101,’Paste’)

➢ insert into items values(99,’Chacolate’) ( Error comes

Syntax : (Table Level)

Create table

( [size],

,

constraint check( with condition));

Example :

create table item1(itno number(3), Itname varchar2(10),

Constraint con_check check (itno>=100));

➢ Insert into item1 values (100,’Rice’)

➢ insert into item1 values(101,’Paste’)

➢ insert into item1 values(99,’Chacolate’) ( Error comes

Primary Key Constraint

It avoids null values and also it does not allows duplicate values. Ie A Primary key is nothing but combination of not null and unique constraints

To Impose Primary Key On Any Column, You Can Use Either Table Level Or Column Level

Syntax: Table Level

create table (column1 ,

column2 ,--------,

constraint primary key );

Ex:

create table items1(itemno number(3),itemname varchar2(20),

constraint con_pk primary key(itemno));

➢ insert into items1 values(100,’Rice’);

➢ insert into items1 values(100,’paste’);

Error Comes

unique constraint (SCOTT.CON_PK) violated

Syntax : Column Level

create table (column1 primary key ,

column2 ,--------);

Example :

create table items2(itemno number(3) primary key, itemname varchar2(20));

➢ insert into items2 values(100 ,’Wheat’);

➢ insert into items2 values(100 ,’Rice’);

Error Comes

unique constraint (SCOTT.CON_PK) violated

Example 2

➢ insert into item values(null ,’Wheat’);

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert

Foreign Key Constraint

This is used to establish relationship between 2 or more tables for inserting common or similar values into the related columns of the table.

The table from which we are establishing relation is called child table. Because the related column in the child table depends on another table for its values.

The table on which other tables are depending on the table, to which we are establishing a relation is called Mother table.

If two tables are having this relationship then the system checks whether the new value that we are inserting is related column of child table exists in the mother table or not.

There are five major restrictions related to foreign key

1. The Mother table column related must have primary key

2. We can not drop the mother table with out child table provided.

3. The data type and the size of the related columns in both the tables must be same and name can be different

4. We can establish relation to any number of tables

5. We can not impose foreign key which are having values.

Syntax : (Column Level)

Create Table

(

References (),

,

);

Syntax : (Table Level)

Create Table

(

,

,

constraint

foreign key(child column)

References ());

Making relationships between two tables

1. First Create the Mother Table or Parent Table

create table bankmast

(accno number(3) primary key,

accna varchar2(10) not null,

balance number(8,2));

2. Next Create the Child Table

create table banktrans

(acno number(3) references bankmast(accno),

trmode char(1),trdate date,

amount number(8,2));

3. Next insert the values in to mother table first,

➢ insert into bankmast values(&accno,’&accna’,&bal);

select * from bankmast;

ACCNO ACCNA BALANCE

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

100 Priya 5000

101 Mahesh 6500

102 Saryu 4500

103 Nandhini 7600

4. Next insert values into child table

➢ insert into banktrans values(&acno,’&trmode’,’&trdate’,&amt);

➢ insert into banktrans values(100,’D’,’5-May-05’,5000);

➢ insert into banktrans values(102,’W’,’15-Apr-05’,2000);

➢ insert into banktrans values(104,’D’,’10-Feb-05’,10000);

ERROR : (since there is no such account number present in the mother table)

ORA-02291: integrity constraint (SCOTT.SYS_C00674) violated - parent key not found

SQL> select * from banktrans;

ACNO T TRDATE AMOUNT

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

100 d 05-MAY-05 5000

102 w 01-JUN-05 2000

Dropping Relationship Tables

Note :

1. if you try to drop the mother table when child table is present, it will give an error message

drop table bankmast;

ERROR :

ORA-02449: unique/primary keys in table referenced by foreign keys

2. To drop the mother table, First Drop the Child table and then drop the mother table

Drop table banktrans;

Drop table bankmast;

******************* OR ************************

CONSTRAINTS

*********************************

A set of pre define rules apply on table column at the time of creating table

or after creating table call constraints.

On two way you can create constraints.

Table level constraints := After define the column.

Column level constraints := At the time of define column.

Types of constraints.

*************************************

1. Domain level constraints > Not null

Check

2. Integrity constraints > Unique key

Primary key

3. References constraints > Foreign key

Rule of constraints.

**********************************

1. Not null - It will not accept null values.

On one table user can define more than one not null constraints.

2. Check - User can provide condition like sal between 500 and 5000

job in('president','manager','clerk','analyst',

salesman');

On one table user can define more than one check constraints.

3. Unique key- It will not accept duplicate values.

It will accept null values.

On one table user can define more than one unique constraints.

4. Primary key. It will not accept duplicate values.

It will not accept null values.

On one table one primary key can possible.

One unique index will automatically create.

This table is known as parent table.

5. Composit primary key -

Using more than one column user can create one primary key

that is composit primary key.

It will not accept duplicate values.

6. Foreign key - It will accept duplicate values which is available in primary key.

On one table user can define more than one foreign key.

It will accept null values.

This table is known as child table.

On delete cascade - If child record is exists user cannot delete parent record

using on delete cascade on foreign key constraint user can

delete parent record. Same time automatically it will delete

the child record.

On delete set null - If child record is exists user cannot delete parent record

using on delete set null on foreign key constraint user can

delete parent record. In case of on delete set null those child

record will be there but it will place null values in child column cell.

***************************************************************************************************************************

CREATE TABLE DEPT

(DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,

DNAME VARCHAR2(10) CONSTRAINT CHK_DNAME

CHECK(DNAME IN('RESEARCH','FINANCE','HR','ACCOUNTING')),

LOC VARCHAR2(10),

PHONE NUMBER(11))

/

CREATE TABLE EMP1

(EMPNO NUMBER(4) UNIQUE,

ENAME VARCHAR2(10) NOT NULL,

JOB VARCHAR2(10),

MGR NUMBER(4),

DOJ DATE,

SAL NUMBER(4) CONSTRAINT CHK_SAL CHECK(SAL BETWEEN 500 AND 5000),

COMM NUMBER(4),

DEPTNO NUMBER(4), CONSTRAINT FK_DEPT FOREIGN KEY(DEPTNO)

REFERENCES DEPT(DEPTNO))

/

ALTER TABLE EMP ADD CONSTRAINT CHK_SAL CHECK(SAL BETWEEN 500 AND 5000);

create table ck (name varchar2(4) check (length(name)=4))

> In this column you can not insert >4 and create table con (ename varchar2(4) check (ename

in('ddd','jkjk')),check(length(ename)=4))

> create table che (sno number(6) constraint p_k primary key,

sname varchar2(10) not null,

fee number(4) check(fee between 1000 and 5000),

pap1 number(3) unique,

loc varchar2(10) check (loc in('hyd','mum')))

Joins

One of the most important features of SQL is the ability to define relationships between multiple tables and draw information from them in terms of these relationships, all within a single command.

With joins we can combine columns from different tables. The connection between tables is established through the WHERE clause

Table and column alias names

The full name of a column of a table actually consists of the table name followed by a dot and then the column name e.g. emp.empno, emp.ename etc

User can omit the table names if one is querying only single table at a time. Even when querying on multiple tables one can still be able to omit the table names provided that all the column names are different.

Types of Joins

There are five types of joins, they are

1. Equi Join ( = )

2. Non Equi Join ( < , > , >=, from ,,-----,

Where = and ------

Equi Join

When two tables joined together using equality operator, then it is called Equi Join. Table prefixes are utilized to prevent ambiguity and the Where clause specifies the columns being joined

Example :

List the employee numbers, names, department numbers and dept names

Select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;

Here, the deptno column exists in both the tables. To avoid ambiguity, the column name should be qualified with the table name ( or with an alias of table name)

Both the table names need to be specified(emp and dept) the where clause defines the joining condition ie joining the deptno of emp table to the deptno of dept table. Here, it checks for the equality of values in these columns

Using Table Aliases

It can be very tedious to type table names repeatedly. Temporary labels ( or aliases) can be used in the FROM clause. These temporary names are valid only for the current select statement. Table aliases should also be specified in the select clause. Table aliases can be up to 30 characters in length, but the shorter they are the better.

Example : List Employee Numbers, Names, Department Numbers, And Department Names From Emp And Dept Tables Using Aliases

select e.empno, e.ename, e.deptno, d.dname from emp e, dept d where e.deptno=d.deptno;

Non Equi Join

Using this we can retrieve data from two or more tables by specifying a condition on the common column with any non-equi join operators (>, =, = salary of emp table employees

➢ select eno,ena,emp1.sal,emp.sal from emp1,emp where emp1.sal>=emp.sal

➢ select e.ename,e.job,e.sal,s.grade,d.dname from emp e,salgrade s,dept d

where e.sal between s.losal and s.hisal

and e.deptno=d.deptno and d.dname='sales' and s.grade=2;

➢ select e.empno,e.ename,e.job,e.sal from emp e,dept d

where e.deptno=d.deptno and d.dname='sales' and e.sal (select avg(sal) from emp where deptno=e.deptno)

group by e.ename,e.sal,e.deptno;

Example : list out the names of the manager with the employees in the emp table

➢ Select w.ename, m.ename from emp w, emp m where w.mgr=m.empno;

Retrieve those employees record whose salary same as

ford's salary.

select e1.ename,e1.sal from emp e1,emp e2

where e2.ename='ford' and e1.sal=e2.sal;

Retrive those employees record whose deptno

equal to ford's deptno.

select e1.ename,e1.deptno from emp e1,emp e2

where e2.ename='FORD' and e1.deptno=e2.deptno;

select w.ename||' is a '||w.job|| ' working under ' || m.job||' '||

m.ename from emp w,emp m

where w.mgr=m.empno;

Cross Join (oracle 9i concept)

A cross joins returns what’s known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. This type of join can be used in situations where it is desired, to select all possible combinations of rows and columns from both tables. This kind of join is usually not preferred as it may run for a long time and produce a huge result set that may not be useful

Syntax

Select from table1 alias cross join table2 alias

Example :

Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table and also display the department details even if no employee belongs to that department

➢ select empno,ename,sal,emp.deptno,dname,loc from emp Cross join dept

Join with using clause.

select e.ename,e.job,d.loc from emp e join dept d

using (deptno);

SUB QUERY

Sub Queries Or Nested Queries

A query within another query is called a sub query. We can define any number of sub queries with in a query. But the system executes the inner most query first. If we are using relational operators between the queries then the sub query must return a single value.

The Following Points should be kept in Mind While using SubQueries

1. The inner Query must be enclosed in parentheses

2. The Inner query must be on the right hand side of the condition

3. The sub query may not have an order by clause

4. The Order By clause appears at the end of the main select statement

5. Sub queries are always executed from the most deeply nested to the least deeply nested

Note : If we use operators “in” and “not in” then the sub query followed by the operators can return more than one value.

Example Queries

1. List the employee’s Details who belong to the department of “Ward”

In general, first we have to know; what the department of “Ward” and then we can display all the employee’s details belonging to “Ward”

select * from emp where ename=’WARD’;

➢ select * from emp where deptno=30;

Using Sub Queries,

➢ select * from emp where deptno=(select deptno from emp

where ename=’WARD’);

2. List the employee’s details whose salary is greater than the average salary of all the employees

In general, first we have to know; average sal of all employee’s and then we can display all the employee’s details whose salary is > average salary of all employees

➢ select avg(sal) from emp;

select * from emp where sal>=2161;

Using Sub Queries,

➢ select * from emp where sal>(select avg(sal) from emp);

3. Write a Query to find the details of the department whose manager’s empcode ‘7698’

In general, first we have to know; department number whose manager empcode is “7698” and then we can display all the department details of that employee

select * from dept;

select deptno from emp where mgr=’7698’

Using sub Query,

select * from dept where deptno=(select deptno from emp where mgr=’7698’)

ERROR at line 1:

ORA-01427: single-row subquery returns more than one row

➢ select * from dept where deptno in (select deptno from emp

where mgr=’7698’)

Or you can give, like this

select * from dept where deptno=(select distinct deptno from emp where

mgr=’7698’);

4. List the details of all the employee’s who earn lowest salary in each department

➢ select * from emp where sal in(select min(sal) from emp group by deptno)

SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC=(SELECT D.LOC FROM EMP E,DEPT D WHERE E.ENAME='WARD' AND E.DEPTNO=D.DEPTNO));

SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC=(SELECT LOC FROM

DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP

WHERE ENAME='SCOTT'))));

SELECT * FROM EMP WHERE MGR=(SELECT MGR FROM EMP WHERE HIREDATE=(SELECT MIN(HIREDATE) FROM EMP));

➢ In which year maximum employees are joining

select * from emp where to_char(hiredate,'yyyy')=(

select y from (select to_char(hiredate,'yyyy') y,count(empno) n

from emp emp group by to_char(hiredate,'yyyy')

order by n desc) where rownum (select sal from emp where ename='allen');

select * from emp where hiredate < (select hiredate from emp where ename='scott');

select * from emp where job (select job from emp where ename='smith');

select * from emp where deptno=(select deptno from dept where loc='chicago')

select * from emp where mgr (

select empno from emp where ename='king');

select * from emp where sal=(select min(sal) from emp);

select * from emp where hiredate=(select min(hiredate) from emp

where deptno=10 );

select * from emp where mgr not in(select empno from emp where ename in('blake','king'));

select * from emp where sal> (select sal from emp where empno=7788);

➢ Retrieve those employee record whos joining date first half of the month.

select * from emp where to_char(hiredate,'dd') =70) then

dbms_output.put_line('DISTICTION');

elsif (avg>=60 and avg=50 and avg=35 and avg1500 then

disc:=bill*10/100;

net_pay:=bill-disc;

else if (bill500) then

disc:=bill*5/100;

net_pay:=bill-disc;

else disc:=0;

end if;

end if;

dbms_output.put_line('pid '||pid);

dbms_output.put_line('bill '||bill);

dbms_output.put_line('discount '||disc);

dbms_output.put_line('Net '||net_pay);

end;

/

Home Work:

write a progm to accept 3 numbers and find the biggest from them.

LOOP Condition

Loop is execution of a sequence of statesments repeatedly.

Iterative Control

Iterative control statements enable you to execute a sequence of statements multiple times. The

simplest form of an iterative statement is the loop.

Loop Statements

The syntax for the loop statement is

loop

statement(s)

end loop;

Types Of Loops

1.simple loop

2.while loop

3.for loop

4.Revarse for loop

SIMPLE LOOP

To Print Numbers

Declare

A number:=1;

Begin

Loop

Exit when a>20;

Dbms_output.put_line(a);

A:=a+1;

End loop;

End;

/

REVERSE NO

Declare

A number:=100;

Begin

Loop

Exit when a < 1;

Dbms_output.put_line(a);

A:=a - 1;

End loop;

End;

To Print The No Is Prime Or Not

Declare

A number:=&no;

B number;

C number:=0;

I number:=1;

Begin

Loop

Exit when i > a;

B:=mod(a,i);

If b=0 then

C:=c+1;

End if;

I:=i+1;

End loop;

If c=2 then

Dbms_output.put_line('prime');

Else

Dbms_output.put_line('not prime');

End if;

End;

/

Syntax for while loop

The while-loop structure repeats a statement until a condition is no longer true. The syntax

for the while loop is as follows:

while condition loop

statement(s)

end loop

9.write a prog to print a series of numbers from that no to specified no.

declare

n number(3):=&n;

a number(3):=&a;

begin

while(a ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download