https://plsqlquery.blogspot.com/2018/05/the-commit-statement.html
The COMMIT Statement is used to save data permanently into database. All DDL and DCL statements execute automatic commit. Commit is also executed automatically in some other cases. After DML statements you have to use COMMIT statement for permanently saving.
SQL> commit;
commit complete
Monday, May 7, 2018
The DELETE Clause
https://plsqlquery.blogspot.com/2018/05/the-delete-clause.html
The DELETE Statement is used to remove row from table.
How to remove ASLAM'S record from EMP table? Aslam's EMPNO is 1.
SQL> DELETE EMP where EMPNO=1;
row deleted
The DELETE Statement is used to remove row from table.
How to remove ASLAM'S record from EMP table? Aslam's EMPNO is 1.
SQL> DELETE EMP where EMPNO=1;
row deleted
Example of DML,DDL, DCL and TCL Commands
DML
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.
SELECT – Retrieves data from a table
INSERT – Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table
INSERT – Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table
DDL
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.
DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command
REVOKE – Withdraws user’s access privileges to database given with the GRANT command
TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a save point within a transaction
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a save point within a transaction
Using TRIM in PLSQL Query
https://plsqlquery.blogspot.com/2018/05/using-trim-in-plsql-query.html
The Oracle/PLSQL TRIM function removes all specified characters either from the beginning or the end of a string.
The syntax for the TRIM function in Oracle/PLSQL is:
Returns
select (' PAKISTAN ') from dual
PAKISTAN
select trim(' PAKISTAN ') from dual
PAKISTAN
select length((' PAKISTAN ')) from dual
30
select length(trim(' PAKISTAN ')) from dual
8
select length('PAKISTAN ') from dual
25
select length(trim('PAKISTAN ')) from dual
8
select TRIM(' ' FROM ' tech ') from dual
tech
-----------------------------------------------------------------------------------------------------------------
LEADING
2300000
select TRIM(LEADING '0' FROM '000012300000')from dual
12300000
------------------------------------------------------------------------------------------------------------------
TRAILING
select TRIM(TRAILING '2' FROM 'Tech12') from dual
Tech1
select TRIM(TRAILING '1' FROM 'Tech1') from dual
Tech
--------------------------------------------------------------------------------------------------------------------
BOTH
select TRIM(BOTH '1' FROM '123Tech111') from dual
23Tech
The Oracle/PLSQL TRIM function removes all specified characters either from the beginning or the end of a string.
The syntax for the TRIM function in Oracle/PLSQL is:
TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )
- LEADING
- The function will remove trim_character from the front of string1.
- TRAILING
- The function will remove trim_character from the end of string1.
- BOTH
- The function will remove trim_character from the front and end of string1.
- trim_character
- The character that will be removed from string1. If this parameter is omitted, the TRIM function will remove space characters from string1.
- string1
- The string to trim.
The TRIM function returns a string value.
Note
- If you do not choose a value for the first parameter (LEADING, TRAILING, BOTH), the TRIM function will remove trim_character from both the front and end of string1.
select (' PAKISTAN ') from dual
PAKISTAN
select trim(' PAKISTAN ') from dual
PAKISTAN
select length((' PAKISTAN ')) from dual
30
select length(trim(' PAKISTAN ')) from dual
8
select length('PAKISTAN ') from dual
25
select length(trim('PAKISTAN ')) from dual
8
select TRIM(' ' FROM ' tech ') from dual
tech
-----------------------------------------------------------------------------------------------------------------
2300000
select TRIM(LEADING '0' FROM '000012300000')from dual
12300000
------------------------------------------------------------------------------------------------------------------
select TRIM(TRAILING '2' FROM 'Tech12') from dual
Tech1
select TRIM(TRAILING '1' FROM 'Tech1') from dual
Tech
--------------------------------------------------------------------------------------------------------------------
select TRIM(BOTH '1' FROM '123Tech111') from dual
23Tech
Sunday, May 6, 2018
Using Outer Joins in PLSQL Query
https://plsqlquery.blogspot.com/2018/05/using-outer-joins-in-plsql-query.html
We use Outer Join when mismatched Values to be displayed or
Using Outer Joins to Return Records with No Direct Match
The missing rows can be returned if an outer join operator is used in the join condition. The Operator is a Plus sign enclosed in parenthesis (+) and it is placed on the "side" of the join that is deficient in information. The Operator has the effect of creating one or more null rows, to which one or more rows from the non-deficient table can be joined.
In the syntax:
table1.column = is the condition that joins (or relates) the tables together
table2.column(+) = is the outer join symbol, which can be placed either side of the WHERE clause condition,but not on both sides.
(Place the outer join symbol following the name of the column in the table without the matching rows.)
select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO = G.DEPTNO(+)
Placed (+) on the "side" of the join that is deficient (missing) in information. Aslam's record exists in EMP table, but NULL DEPTNO does not exists in DEPT table, so put (+) on DEPT side because DEPT side is missing information
Placed (+) on the "side" of the join that is deficient (missing) in information. Operations department DEPTNO 40 exist in DEPT table, but DEPTNO 40 is not assigned to any employee, so put (+) on EMP side because EMP side is missing information
We use Outer Join when mismatched Values to be displayed or
Using Outer Joins to Return Records with No Direct Match
The missing rows can be returned if an outer join operator is used in the join condition. The Operator is a Plus sign enclosed in parenthesis (+) and it is placed on the "side" of the join that is deficient in information. The Operator has the effect of creating one or more null rows, to which one or more rows from the non-deficient table can be joined.
In the syntax:
table1.column = is the condition that joins (or relates) the tables together
table2.column(+) = is the outer join symbol, which can be placed either side of the WHERE clause condition,but not on both sides.
(Place the outer join symbol following the name of the column in the table without the matching rows.)
select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO = G.DEPTNO(+)
EMPNO
|
ENAME
|
SAL
|
COMM
|
DEPTNO
|
DNAME
|
7934
|
MILLER
|
1300
|
(null)
|
10
|
ACCOUNTING
|
7782
|
CLARK
|
2450
|
(null)
|
10
|
ACCOUNTING
|
7839
|
KING
|
5000
|
(null)
|
10
|
ACCOUNTING
|
7876
|
ADAMS
|
1100
|
(null)
|
20
|
RESEARCH
|
7369
|
SMITH
|
800
|
(null)
|
20
|
RESEARCH
|
7902
|
FORD
|
3000
|
(null)
|
20
|
RESEARCH
|
7788
|
SCOTT
|
3000
|
(null)
|
20
|
RESEARCH
|
7566
|
JONES
|
2975
|
(null)
|
20
|
RESEARCH
|
7900
|
JAMES
|
950
|
(null)
|
30
|
SALES
|
7844
|
TURNER
|
1500
|
0
|
30
|
SALES
|
7654
|
MARTIN
|
1250
|
1400
|
30
|
SALES
|
7521
|
WARD
|
1250
|
500
|
30
|
SALES
|
7499
|
ALLEN
|
1600
|
300
|
30
|
SALES
|
7698
|
BLAKE
|
2850
|
(null)
|
30
|
SALES
|
1
|
ASLAM
|
1200
|
(null)
|
(null)
|
(null)
|
Placed (+) on the "side" of the join that is deficient (missing) in information. Aslam's record exists in EMP table, but NULL DEPTNO does not exists in DEPT table, so put (+) on DEPT side because DEPT side is missing information
----------------------------------------------------------------------------------------------------------
select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO(+) = G.DEPTNO
where E.DEPTNO(+) = G.DEPTNO
EMPNO
|
ENAME
|
SAL
|
COMM
|
DEPTNO
|
DNAME
|
7782
|
CLARK
|
2450
|
(null)
|
10
|
ACCOUNTING
|
7934
|
MILLER
|
1300
|
(null)
|
10
|
ACCOUNTING
|
7839
|
KING
|
5000
|
(null)
|
10
|
ACCOUNTING
|
7788
|
SCOTT
|
3000
|
(null)
|
20
|
RESEARCH
|
7876
|
ADAMS
|
1100
|
(null)
|
20
|
RESEARCH
|
7566
|
JONES
|
2975
|
(null)
|
20
|
RESEARCH
|
7902
|
FORD
|
3000
|
(null)
|
20
|
RESEARCH
|
7369
|
SMITH
|
800
|
(null)
|
20
|
RESEARCH
|
7900
|
JAMES
|
950
|
(null)
|
30
|
SALES
|
7844
|
TURNER
|
1500
|
0
|
30
|
SALES
|
7654
|
MARTIN
|
1250
|
1400
|
30
|
SALES
|
7521
|
WARD
|
1250
|
500
|
30
|
SALES
|
7499
|
ALLEN
|
1600
|
300
|
30
|
SALES
|
7698
|
BLAKE
|
2850
|
(null)
|
30
|
SALES
|
(null)
|
(null)
|
(null)
|
(null)
|
40
|
OPERATIONS
|
Placed (+) on the "side" of the join that is deficient (missing) in information. Operations department DEPTNO 40 exist in DEPT table, but DEPTNO 40 is not assigned to any employee, so put (+) on EMP side because EMP side is missing information
Subscribe to:
Comments (Atom)