Tuesday, August 5, 2014

Rough guide to write Oracle / SQL queries


          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.');

row created.

SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.');

row created.

SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.');

row created.

SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.');

row created.

SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.');

row created.

SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.');

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  /

6 comments:

  1. Very good information about Oracle / SQL queries. It is very helpful most of the people. Thanks for sharing with us. Keep posting.

    ReplyDelete
  2. Show-Box is one of the best addons on KODI for TV Shows, It has a very nice and big selection of TV Shows, Click here to check the process for install.
    ShowBox on Kodi

    ReplyDelete
  3. Find the best drones for aerial videography and photography. Get the complete information about the drones
    Best Drones For Sale

    ReplyDelete
  4. We are giving on the online help 24*7 to the customers. Our authorities offer you the best response to your issues. You can pursue the connection and you are far away from one step to the solution to your problem.
    www.office.com/setup
    www.webroot.com/safe
    www.norton.com/setup

    ReplyDelete