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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.