Utility Queries for day to day use
SUBSTR Examples
To extract the last n characters from a string ie - diaplay the first m characters
Sample String - ABCDEFGHIJ
In below example extract last 2 characters of the string.
SELECT SUBSTR('ABCDEFGHIJ',1,LENGTH('ABCDEFGHIJ')-2) FROM DUAL;
ABCDEFGH
To display only the last n characters of a string
Sample String - ABCDEFGHIJ
In below example display only last 2 characters of the string.
SELECT SUBSTR('ABCDEFGHIJ', -2) FROM DUAL;
IJ
SUBSTR and INSTR Examples
I have a 10 digit phone number. 123-456-7890.
I want to extract the middle portion of this number.
Below is an example of doing the same using oracle SUBSTR and INSTR functions.
Here I need to get the characters between the first hyphen and the second hyphen '-'.
INSTR(string,'-') + 1, gives me the position (m) of the digit 4 after the first hyphen.
INSTR(string,'-',2) - 1, gives me the position (n) of the digit 6 before the second hyphen.
These I give as input to the SUBSTR function. SUBSTR(string, m,n).
SELECT SUBSTR('123-456-7890',INSTR('123-456-7890','-') + 1,INSTR('123-456-7890','-',2)-1 ) FROM DUAL;
456
Query to fetch the top n (n=5) employees having the highest salary
select employee_id, first_name, last_name, salary
from employees
order by salary desc
fetch first 5 rows only;
101 Neena Kochhar 17000
102 Lex De Haan 17000
145 John Russell 14000
146 Karen Partners 13500
The same can also be achieved using analytical function ROW_NUMBER() as below. The output will be the same.
select employee_id,first_name,last_name,salary
from(
select employee_id,first_name,last_name,
row_number() over(order by salary desc) rn,
salary
from employees)
where rn <= 5;select employee_id,first_name,last_name,department_id, salary, rn
from(
select employee_id,first_name,last_name,
row_number() over(partition by department_id order by salary desc) rn,
salary, department_id
from employees where department_id is not null
and department_id in (30,60,80,90,100))
where rn <= 5
order by department_id; EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY RN
114 Den Raphaely 30 11000 1
115 Alexander Khoo 30 3100 2
116 Shelli Baida 30 2900 3
117 Sigal Tobias 30 2800 4
118 Guy Himuro 30 2600 5
103 Alexander Hunold 60 9000 1
104 Bruce Ernst 60 6000 2
105 David Austin 60 4800 3
106 Valli Pataballa 60 4800 4
107 Diana Lorentz 60 4200 5
145 John Russell 80 14000 1
146 Karen Partners 80 13500 2
147 Alberto Errazuriz 80 12000 3
168 Lisa Ozer 80 11500 4
148 Gerald Cambrault 80 11000 5
100 Steven King 90 24000 1
101 Neena Kochhar 90 17000 2
102 Lex De Haan 90 17000 3
108 Nancy Greenberg 100 12008 1
109 Daniel Faviet 100 9000 2
110 John Chen 100 8200 3
112 Jose Manuel U 100 7800 4
111 Ismael Sciarra 100 7700 5
Listagg: Usage
Listagg query to get a comma separated list of employees, department wise
select
department_id,
listagg(first_name || ' ' || last_name,',') as dept_wise_employees
from employees
where department_id in (10,20,30,40)
group by department_id
order by department_id;
DEPARTMENT_ID DEPT_WISE_EMPLOYEES
10 Jennifer Whalen
20 Michael Hartstein;Pat Fay
30 Den Raphaely;Alexander Khoo;Sigal Tobias;Shelli Baida;Guy Himuro;Karen Colmenares
40 Susan Mavris
In the above query if within the comma separated list I want to order the employee list in the order of hire date and employee id, I can write the query as below.
select department_id,
listagg(all first_name || ' ' || last_name,';')
within group (order by hire_date, employee_id) as dept_wise_employees
from employees
where department_id in (10,20,30,40)
group by department_id;
DEPARTMENT_ID DEPT_WISE_EMPLOYEES
10 Jennifer Whalen
20 Michael Hartstein;Pat Fay
30 Den Raphaely;Alexander Khoo;Sigal Tobias;Shelli Baida;Guy Himuro;Karen Colmenares
40 Susan Mavris
Self Join
Query to get the employee names for manager Steven King(employee id 100) in Employees table
SELECT e.EMPLOYEE_ID,e.FIRST_NAME, e.LAST_NAME, m.EMPLOYEE_ID, m.FIRST_NAME, m.LAST_NAME
FROM EMPLOYEES e, EMPLOYEES m
WHERE m.EMPLOYEE_ID = e.MANAGER_ID
AND m.EMPLOYEE_ID = 100;EMPLOYEE_ID FIRST_NAME LAST_NAME EMPLOYEE_ID_1 FIRST_NAME_1 LAST_NAME_1
101 Neena Kochhar 100 Steven King
120 Matthew Weiss 100 Steven King
121 Adam Fripp 100 Steven King
123 Shanta Vollman 100 Steven King
124 Kevin Mourgos 100 Steven King
145 John Russell 100 Steven King
146 Karen Partners 100 Steven King
147 Alberto Errazuriz 100 Steven King
148 Gerald Cambrault 100 Steven King
149 Eleni Zlotkey 100 Steven King
201 Michael Hartstein 100 Steven King
102 Lex De Haan 100 Steven King
114 Den Raphaely 100 Steven King
122 Payam Kaufling 100 Steven King
Pivot Query
Query to get day wise sale of electronic devices in the TRONIC gadgets store.
CREATE TABLE TRONIC_SALE_STATS
(
SALE_DATE DATE,
DEVICE VARCHAR2(100),
DEVICE_MODEL VARCHAR2(100),
PRICE NUMBER,
QTY NUMBER
);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'LAPTOP', 'ULTRA',185000,12);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'LAPTOP', 'PREMIUM',95000,24);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'MOBILE', 'ULTRA',125000,72);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'MOBILE', 'PREMIUM',75000,120);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'MOBILE', 'STANDARD',45000,95);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'TV', 'ULTRA',225000,10);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'TV', 'PREMIUM',120000,35);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'TV', 'STANDARD',75000,65);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'LAPTOP', 'ULTRA',185000,10);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'LAPTOP', 'PREMIUM',95000,21);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'MOBILE', 'ULTRA',125000,68);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'MOBILE', 'PREMIUM',75000,115);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'MOBILE', 'STANDARD',45000,89);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'TV', 'ULTRA',225000,7);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'TV', 'PREMIUM',120000,28);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'TV', 'STANDARD',75000,61);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'LAPTOP', 'ULTRA',185000,7);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'LAPTOP', 'PREMIUM',95000,18);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'LAPTOP', 'STANDARD',65000,78);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'MOBILE', 'ULTRA',125000,55);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'MOBILE', 'PREMIUM',75000,84);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'MOBILE', 'STANDARD',45000,62);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'TV', 'ULTRA',225000,4);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'TV', 'PREMIUM',120000,21);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'TV', 'STANDARD',75000,45);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'LAPTOP', 'ULTRA',185000,2);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'LAPTOP', 'PREMIUM',95000,10);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'LAPTOP', 'STANDARD',65000,45);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'MOBILE', 'ULTRA',125000,35);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'MOBILE', 'PREMIUM',75000,42);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'MOBILE', 'STANDARD',45000,47);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'TV', 'ULTRA',225000,1);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'TV', 'PREMIUM',120000,12);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'TV', 'STANDARD',75000,25);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'LAPTOP', 'PREMIUM',95000,3);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'LAPTOP', 'STANDARD',65000,30);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'MOBILE', 'ULTRA',125000,12);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'MOBILE', 'PREMIUM',75000,32);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'MOBILE', 'STANDARD',45000,26);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'TV', 'PREMIUM',120000,4);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'TV', 'STANDARD',75000,19);
SELECT * FROM
(
SELECT SALE_DATE,DEVICE || ' ' || DEVICE_MODEL DEVICE,QTY
FROM TRONIC_SALE_STATS
)
PIVOT
(
sum(QTY)
FOR SALE_DATE IN ('01-May-23', '02-May-23','03-May-23', '04-May-23','05-May-23')
)
ORDER BY DEVICE;DEVICE 01-May-23 02-May-23 03-May-23 04-May-23 05-May-23'
LAPTOP STANDARD NULL NULL 78 45 30
LAPTOP PREMIUM 24 21 18 10 3
LAPTOP ULTRA 12 10 7 2 NULL
MOBILE PREMIUM 120 115 84 42 32
MOBILE STANDARD 95 89 62 47 26
MOBILE ULTRA 72 68 55 35 12
TV PREMIUM 35 28 21 12 4
TV STANDARD 65 61 45 25 19
TV ULTRA 10 7 4 1 NULL
REGEXP_LIKE to select employees having a valid email id
SELECT employee_id, Email
FROM
Employees
where REGEXP_LIKE(Email, '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}', 'i');
EMPLOYEE_ID EMAIL
201 MHARTSTE@gmail.com
202 PFAY@gmail.com
To delete duplicate records from a table
Delete FROM employees a WHERE ROWID <>(Select max (rowid) from employees b where a.employee_id=b.employee_id);
--To get the middle portion of the US telephone number
--PHONE NUMBER - 123-456-7890
select SUBSTR('123-456-7890', INSTR('123-456-7890','-',1,1)+1 ,INSTR('123-456-7890','-',1,2) - INSTR('123-456-7890','-',1,1)-1) phoneno FROM DUAL;
| PHONENO |
|---|
| 456 |
--To get the count code from a telephone number
--PHONE NUMBER - +91-8765498765
select REPLACE(SUBSTR('+91-8765498765',1, INSTR('12-345678-90','-',1,1)),'+',NULL) country_code FROM DUAL;
| COUNTRY_CODE |
|---|
| 91 |
Thought for the day
Give every day the chance to be the most beautiful day of your life.
Mark Twain
No comments:
Post a Comment