Guide to write Oracle / SQL queries:
*-*-* IS NOT operator *-*-*
SELECT CUSTOMER_ID, is_default from TABLEcustchannelacct
where is_default <> '0';
*-*-* Query update (DATE, TIME) &
Sysdate *-*-*
Login from root
>root
>Password
(Format: date MONTH
DD HH MM YYYY) that is
date Month Date Hour Minute Year
date 07 03 10
53 2014
Select sysdate from
dual;
-UPDATE TABLEcfgchannelstatus
SET code=03
WHERE code=02
-UPDATE TABLEaccount
SET TITLE='NASIB
GUL', BranchID = '9991', Last_Updated = '23-AUG-12'
WHERE account_id=
'3110575202275';
*-*-* Renew
Database User password *-*-*
alter user user
identified by password;
*-*-* (Creation
of table and insertion of data from other table) *-*-*
Create table ARBAB as
select *from TABLEACCOUNT;
Insert into ARBAB
select *from TABLEACCOUNT; table1.cnic != table2.cnic
Commit
CREATE TABLE Sort1
(
CNICIB varchar(255),
CNICUSER varchar(255)
)
*-*-* Aggregate functions {COUNT, AV} *-*-*
1- SELECT COUNT(*)FROM TABLECUSTOMER;
2- SELECT AVG(channelid) FROM TABLECFGCHANNEL;
*-*-* DISTINCT or UNIQUE*-*-*
Select DISTINCT
institutionid from TABLEguirole;
Select UNIQUE
institutionid from TABLEguirole;
*-*-* IN Operator *-*-*
SELECT * FROM TABLEcustchannelacct
WHERE channel_id IN
('0105','0107')
*-*-* WHERE *-*-*
1- Select *from TABLEguirole where
institutionid = '0002';
2- Delete from TABLECUSTOMER where CUSTOMERID
< '00012277';
3- Select *from TABLECUSTCHANNELACCT where
channel_id = '0120'; (To have the whole ROW of channel_id of 0120)
4- Select channel_id from TABLECUSTCHANNELACCT
where channel_id = '0120'; (To have only the column of channel_id 0120)
*-*-* AND/OR
*-*-*
The AND operator
displays a record if both the first condition and the second condition is true.
p q p AND q
T T T
T F F
F T F
F F F
The OR operator
displays a record if either the first condition or the second condition is
true.
p q p OR q
T T T
T F T
F T T
F F F
1-Select *from TABLEguirole
where institutionid = '0002' AND role_id = 'full access';
2-SELECT CUSTOMERID
FROM TABLEcustomer
WHERE customerid >
6110148910985
OR (customerid
< 8 AND customerid > 6)
*-*-* IN
*-*-*
SELECT *
FROM TABLECUSTCHANNELACCT
WHERE Channel_ID IN
('0007', '9999')
Multiple values in a WHERE clause; Used for equals to
0007 OR 9999
Gives exactly the value of the returned values we want to
see.
The ‘In’ has to
be inside the Where clause (it's not an order statement, is a select statement).
*-*-* BETWEEN *-*-*
SELECT *
FROM TABLECUSTOMER
WHERE DATEOFBIRTH
BETWEEN '19820419' AND '19650329';
SELECT *
FROM TABLEeodtracelog
WHERE
"DATE" between '20140108' and '20140108';
*-*-* LIKE (Wildcards)*-*-*
SELECT *
FROM TABLEguirolepermission
WHERE ROLE_ID LIKE '%institution2%'
*-*-* ORDER BY ASC/DSC *-*-*
SELECT
ROLE_ID,NAME,INSTITUTIONID
FROM TABLEGUIROLE
ORDER BY
INSTITUTIONID ASC
1- Select Error_Code,
ERROR_NAME,ERROR_DESCRIPTION,IMAGE_FILE,ERROR_CATEGORY
from TABLEcfgerrorcodes
ORDER BY Error_CODE ASC;
2- SELECT
* FROM TABLECUSTCHANNELACCT
WHERE Channel_ID IN
('0007', '9999','0001')
ORDER BY Channel_ID
ASC;
4- Select PAN, date_sett from TABLEtransactionlog
where date_sett is
not null
order by date_sett
asc
*-*-* FUNCTIONS (Aggregate, Scalar)*-*-*
SELECT AVG
(CUSTOMERID)
FROM TABLECUSTOMER
For Scalar Functions
1-SELECT
LENGTH(RELATIONSHIP_ID)
FROM TABLECUSTCHANNELACCT
2-SELECT LENGTH ('This
sentence''s length will be calculated') "Length of characters"
FROM dual;
Note: Here 1 extra
apostrophe shows that sentence did not end there it continues.
http://oreilly.com/catalog/sqlnut/chapter/ch04.html
Oracle/PLSQL:
Dealing with apostrophes/single quotes in strings
http://www.techonthenet.com/oracle/questions/quotes.php
*-*-* GROUP
BY *-*-*
SELECT Channel_ID,
SUM(Account_ID)
FROM TABLEcustchannelacct
GROUP BY Channel_ID
Note: 'Now this
way individually sums of Account_ID for all channel_ID will show.'
*-*-* HAVING, GROUP *-*-*
1-SELECT
SUM(ACCOUNT_TYPE)
FROM TABLECUSTCHANNELACCT
HAVING
SUM(ACCOUNT_TYPE) >10
2-SELECT IS_DEFAULT,
SUM(ACCOUNT_TYPE)
FROM TABLECUSTCHANNELACCT
GROUP BY IS_DEFAULT
HAVING
SUM(ACCOUNT_TYPE) > 10
3-SELECT
CUS_SHO_NAME, avg (cus_num)
FROM
customer_acct_type
GROUP BY CUS_SHO_NAME
HAVING AVG(CUS_NUM)
> 0
*HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions.
*-*-* Alias,
without As & JOINS *-*-*
(Table
alias)
1- SELECT CHANNEL_ID, IS_DEFAULT from TABLEcustchannelacct
ImTbAlias WHERE ImTbAlias.IS_DEFAULT = 1;
2- SELECT CHANNEL_ID, IS_DEFAULT from TABLEcustchannelacct
ImTbAlias WHERE IS_DEFAULT = 1;
(This
will also run even without table alias before the column as there is no other table’s
column which has the same name)
(Column
alias)
3- Select channel_id ImClAlias from TABLEcustchannelacct;
(Alias, with
As but not using JOINS)
1- Select Is_Default, channel_id AS ClAlias
from TABLEcustchannelacct TbAlias where IS_DEFAULT=1;
&
2- Select Is_Default, channel_id AS
"ClAlias" from TABLEcustchannelacct TbAlias where IS_DEFAULT=1; (Col.
Alias with inverted commas)
&
3- Select Is_Default, channel_id AS ClAlias
from TABLEcustchannelacct TbAlias where TbAlias.IS_DEFAULT = 1; (Col. after
where with table alias)
&
4- Select TbAlias.Is_Default,
TbAlias.channel_id AS ClAlias from TABLEcustchannelacct TbAlias where
TbAlias.IS_DEFAULT = 1; (Col. before and after with table alias)
5- Select * from TABLEaccount, TABLEcustchannelacct
where
TABLEaccount.account_id = TABLEcustchannelacct.account_id;
6- Select TABLEaccount.account_id, TABLEcustchannelacct.account_id
from TABLEaccount, TABLEcustchannelacct where TABLEaccount.account_id = TABLEcustchannelacct.account_id;
*-*-* AS,
GROUP BY *-*-*
1- Select count(*) as
Total, error_category from TABLEcfgerrorcodes group by error_category order by
total desc
2- Select * from TABLEguiuserrole
where user_id='arbab';
3-SELECT * from TABLEregioninstitution
where regionid='1900';
*-*-*-* Inner
Joins (Implicit) *-*-*-* Equi-join
1-Select
A1.Account_type,A1.Title, A2.Channel_id,A2.Account_type
from TABLEaccount A1,
TABLEcustchannelacct A2
where A1.Account_type
= A2.Account_type;
2-Select
a1.CUSTOMERID, b1.customer_id
from TABLEdebitcard
a1, TABLECUSTOMERBENEFICIARY b1
where a1.CUSTOMERID =
b1.customer_id
*-*-*-* Inner joins
(Explicit) of the same query above*-*-*-*
Select
A1.Account_type,A1.Title, A2.Channel_id,A2.Account_type
from TABLEaccount A1
INNER JOIN TABLEcustchannelacct
A2
on A1.Account_type =
A2.Account_type;
*-*-*-* Inner Joins (Implicit) with group by*-*-*-*
Select A1.TITLE (You may write Column alias here), SUM (A2.Account_ID) (You may write Column alias here)
from TABLEaccount A1,
TABLEcustchannelacct A2
where a1.account_type
= a2.account_type
group by a1.TITLE
*-*-*-* Outer Joins *-*-*-*
Outer join. With an
outer join, you get all the records from one table and only those records from
the other table that have matching values from the first table.
*-*-*-*-* Concatenation *-*-*-*-*
1-Select concat
(RELATIONSHIP_ID,TRAN_PERM)
from TABLEcustchannelacct
where account_type =
'20';
(Note: The Oracle CONCAT() function only allows two
arguments -- only two strings can be put together at a time using this
function. However, it is possible to concatenate more than two strings at a
time in Oracle using '||'.)
2-Select
RELATIONSHIP_ID || ' ' || ACCOUNT_TYPE || ' ' || TRAN_PERM
from TABLECUSTCHANNELACCT
where account_type =
'20';
*-*-*-*-* Subquery *-*-*-*-*
You know account_id but not the Title of the account,
Select Title from TABLEaccount
where ACCOUNT_ID in (select ACCOUNT_ID
from TABLEcustchannelacct
where ACCOUNT_ID like
'477900783901%')
Ans. Imran Ahmed
Select Title,account_id from TABLEaccount where ACCOUNT_ID in (select ACCOUNT_ID
from TABLEcustchannelacct
where customer_id='000000015432');
Ans. Irfan Bin Ilm 20100043149
Irfan Bin Ilm 20200030032
Irfan Bin Ilm 20300030032
Irfan Bin Ilm 20400030032
Irfan Bin Ilm 20500030032
Select * from TABLEhosttrail where trxlogid in (select trxlogid from TABLEtransactionlog
where product_id in ('4290','4291','4292'));
Select * from TABLEhosttrail where trxlogid in (select trxlogid from TABLEhosttrail
where trxlogid in (select trxlogid from TABLEtransactionlog where product_id in
('4290','4291','4292')));
Select customerid,firstname from TABLEcustomer where customerid in
(Select customer_id from TABLEcustchannelacct where account_id like
'%70000000234 %');
Ans.
Customerid Firstname
000000000212 SyedQaim
Select
relationship_id,tracking_id,expirydate from TABLEdebitcard where
ISCUSTOMERLINKED='1' and
cardnumber not in ( select cardnumber from TABLEnipushsms);
Select * from TABLErelationshipauth
where relationshipauth_id in (Select relationshipauth_id from TABLEcustchannelacct
where (substr(RELATIONSHIP_ID, 0, 16) in
('6369620030000415','6369620030000506')) and channel_id= 0001);
*-*-*-* Substring *-*-*-*
1-SUBSTR
(Columns, Starting position, length)
Select SUBSTR (RELATIONSHIP_ID ,3)
From TABLECUSTCHANNELACCT
where RELATIONSHIP_ID =
'1400001001000100=1602';
2-SELECT SUBSTR
(RELATIONSHIP_ID,1,10) --Start from 1 and end
the result on 10th
FROM TABLECUSTCHANNELACCT
WHERE RELATIONSHIP_ID =
'1400001001000100=1602';
3-SELECT SUBSTR('ABCDEFGhijklmno',3,6)
"Substring"
FROM DUAL;
Ans.: CDEFGh
4- SELECT
SUBSTR('ABCDEFGhijklmno',-5,6) "Substring" -- Oracle counts backward from the
end
FROM DUAL;
Ans.: klmno
5- Select * from TABLEcustchannelacct
where (substr(RELATIONSHIP_ID, 0, 16) in
('6369620030000415','6369620030000506')) and channel_id= 0001;
*-*-*-* Trim & Length *-*-*-*
1-SELECT
RTRIM(RELATIONSHIP_ID)from TABLEcustchannelacct;
2-Select
length (RELATIONSHIP_ID) from TABLEcustchannelacct;
select *
from TABLEaccount where trim (account_id) = '00630021156001'
Both RTRIM and Length
functions:
3-SELECT
RTRIM(Account_id) || ' -- '|| length (RTRIM(Account_id)), length
(Account_id) from TABLEACCOUNT;
4-select
log_feedback_id from TABLEimplogcustomer where Trim(cnic) like
'%33100288099991%'
5-where
trim(mgr_branch_migration.NEWACCOUNT) in (
select
trim(Account_id) from TABLEcustchannelacct Where substr(trim(Account_id),1,4) =
substr(trim(mgr_branch_migration.NEWACCOUNT),1,4)
*-*-*-* Replace *-*-*-*
Region_name = Column, replace
wherever it finds ‘ast’ with ‘astern’
1-SELECT REPLACE (region_name, 'ast', 'astern')
FROM Geography;
2-Select replace (TSN,'23',00)
FROM arch
*-*-*-* Creating table *-*-*-*
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
*-*-*-*Add
Column Syntax *-*-*-*
ALTER TABLE arbab ADD
age char(10);
*-*-*-* Change the data type of a column *-*-*-*
1-ALTER TABLE arbab
MODIFY age char(100);
2-ALTER TABLE arbab
MODIFY age varchar(100);
*-*-*-*
Rename Column *-*-*-*
ALTER TABLE arbab
RENAME COLUMN age TO Address;
*-*-*-* Drop Column *-*-*-*
ALTER TABLE arbab
drop column age;
*-*-*-* Add Constraint Syntax
*-*-*-*
ALTER TABLE arbab ADD
CONSTRAINT constraint_name UNIQUE (IDs);
*-*-*-* Drop Constraint
Syntax *-*-*-*
ALTER TABLE arbab
DROP CONSTRAINT constraint_name
*-*-*-* CHECK Constraint *-*-*-*
1-Alter table arbab
ADD CONSTRAINT Con_Name
CHECK(GENDER='Male' OR GENDER='Female')
2-(Better query): What
if you have to check the values in the column in a lsit of 10 or more values.
How many times you will write OR OR OR ....?
That is the reason the values are check with IN (.,.,...) operator.
Alter table arbab
add constraint ck_abc
check (gender in
('MALE', 'FEMALE'));
Insert into arbab
values ('abc','34343','99','');
Insert into arbab
values ('abc','34343','99','Male');
Note: Insert value only work for either Male or Female or it
enters a null.
*-*-*-* DROP COLUMN *-*-*-*
ALTER TABLE
table_name
DROP COLUMN
column_name;
*-*-*-* RENAME COLUMN *-*-*-*
ALTER TABLE
table_name
RENAME COLUMN old_name to new_name;
*-*-*-* To find
which table has ‘TRAILID’ column ( WAQAS )*-*-*-*
Select table_name from
user_tab_cols where column_name like '%TRAILID%';
*-*-*-* To find packages of tables (Yousuf
)*-*-*-*
select * from user_source
where upper(text) like '%TABLEREGION%'
*-*-*-* To find
which table has ‘TRIGGER’ column (AFTAB )*-*-*-*
CREATE OR
REPLACE TRIGGER TABLEdebitcard_after_insert
AFTER INSERT
ON TABLEdebitcard
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
INSERT INTO TABLEDEBITCARDTRAIL
(cARDNUMBER,
TIME
)
VALUES
( :new.CARDNUMBER,
TO_CHAR(sysdate,'YYYYMMDDHH24MISS')
);
END;
*-*-*-**-*-*-**-*-*-*
select * from
all_all_tables where owner like '%USER%' and table_name like 'MV%'
delete from
all_all_tables where owner like '%USER%' and table_name like 'MVTRANSACTIONLOG'
*-*-*-**-*-*-**-*-*-*
A trigger is an event within the DBMS that
can cause some code to execute automatically.
SQL>
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.');
1 row created.
SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.');
1 row created.
SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.');
1 row created.
SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.');
1 row created.
SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.');
1 row created.
SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.');
1 row created.
SQL>
SQL> create table product_audit(
2 product_id number(4) not null,
3 num_rows number(8) not null
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTrigger
2 AFTER INSERT ON company
3 FOR EACH ROW
4 BEGIN
5 UPDATE product_audit
6 SET num_rows =num_rows+1
7 WHERE product_id =:NEW.product_id;
8 IF (SQL%NOTFOUND) THEN
9 INSERT INTO product_audit VALUES (:NEW.product_id,1);
10 END IF;
11 END;
12 /
