SQL Queries:-
1. JOIN Queries (used to view records from more than one file)
(a). Join or inner join [both are same]
Select * from epf a join cpf b on a.name = b.name
Select * from epf a inner join apf b on a.name = b.name
it will show common records of both file matched on the basis of joined field(name here)
(b). Left Join [it will show all records of first file and only matching records of second file)
Select * from epf a left join cpf b on a.name = b.name
(c). Right Join [it will show all records of second file and only matching records of 1st file)
Select * from epf a right join cpf b on a.name = b.name
(d). Full join (Shows all records of both file in exact matching order)
Select * from epf a full join cpf b on a.name = b.name
(e). Exception join (shows all uncommon records of 1st file only)
Select * from cpf a exception join epf b on a.name = b.name
(f). Select * from cpf a right exception join epf b on a.name = b.name
It will show uncommon records of second file.
Note: - Exception join and left exception join are same, right exception file will show all uncommon records
of second file. In Above examples EPF and CPF are physical files.
2. Substring Query
Select substr(name,1,5) from emp
It will show only initial five characters of name field.
SELECT empname, length(rtrim(EMPNAME)) FROM LCOTTK/TEST1 – length is used to find length of field
3. Showing field record in Lower and upper case:-
Select lcase(name) from emp
SELECT statement run complete.
Select ucase(name) from emp
SELECT statement run complete.
4. Concatenated results:
SELECT name, skill1 || ' ' || skill2 || '' || skill3 FROM emp
SELECT name, trim(skill1) || ',' || trim(skill2) || ',' || trim(skill3) FROM emp
5. Creating a table like another table:
create table ROHAN like emp
Table ROHAN in QGPL created
Sensitivity: Internal & Restricted
6. How to see only distinct records and not duplicate one:
Select distinct * from afl
7. How to see maximum or minimum value record:
Select max(age) from emp
Select min(age) from emp
8. How to see only 4 or 5 or 6 rows from a file – by using this type of query we can find second or third
highest record similarly second or third or fourth lowest recod
Select * from afl fetch first 4 rows only
9. How to fetch records in between a particular window
SELECT * FROM AFL WHERE AGE between 20 and 26
10. To show all records in descending orders
SELECT * FROM afl ORDER BY name desc
11. For Selecting only few records basis of any field
Select * from afl where age in (23, 25, 26)
12. To find third maximum record in any file
Select * from afl where age in(
Select min(age) from afl where age in (Select age from afl group by
age order by age desc fetch first 3 rows only))
13. To find and delete duplicate records by single query
SELECT * FROM AFL F1 WHERE RRN(F1) > (SELECT MIN(RRN(F2)) FROM AFL
F2 WHERE F1. NAME = F2.NAME)
DELETE FROM AFL F1 WHERE RRN(F1) > (SELECT MIN(RRN(F2)) FROM AFL
F2 WHERE F1. NAME = F2.NAME)
13a. Creating alias for any field
SELECT CustomerName AS Customer, ContactName AS Contact_Person
FROM Customers;
Sensitivity: Internal & Restricted
14. Finding specific records using %like
a. Select * from csi21232/apf where name like '%Abhi%'
b. Second position of field is ‘o’
SELECT * FROM Customers where City like "_o%"
c. Starts with a and ends with o
SELECT * FROM Customers WHERE ContactName LIKE 'a%o';
d. Starting with a
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
e. Does not start with a
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';
f. ‘or’ at any position
SELECT * FROM Customers WHERE CustomerName LIKE '%or%'
g. ending with a
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
15. View records by using RRN no
Select rrn(alias), alias.* from pf2 alias or
Select rrn(a),a.* from Library/pf2 a
here pf2 is file name, a is alias only
16. Insert query
insert into bpf values ('1521241','Aamer','Punjab', 25 , 'Male')
Note: all character values will come under quote
17. To find out sum of records with reference to a specific field:-
(a) SELECT NAME, SUM(AGE) FROM PF5 GROUP BY NAME
Note:- it worked only with numeric data
More precise result:-
(b) SELECT NAME, SUM(AGE) FROM PF5 GROUP BY NAME HAVING count(*)>1
18. Restricting any field on first place at the time of records fetching: -
Select name, a.* from cpf a
It will show a specific field name here at first place and then shows all the rest of fields.
19. Alter a table deleting or inserting any field without losing any data
alter table epf add column color char(5) before age
alter table epf drop column color
Note:- we can choose char or binary for data type at the time of field addition
Sensitivity: Internal & Restricted
20. Query using group by and Sum simultaneously
SELECT NAME , SUM(AGE), COUNT(NAME) FROM PF1 GROUP BY NAME
On the basis of name it grouped the records and show desired value for example.
NAME SUM ( AGE ) COUNT ( NAME )
SURYA 49 1
SUKHMEET 27 1
ABsgjshki 25 1
ABHI 96 4
21. Query to change some part of any field
update apf set name = (substr(name,1,4)||'ZZZ'||substr(name,8,8)) where substr(name,5,3) = 'SHE'
22. Fetch records having name with last character is “I”.
SELECT * FROM EPF WHERE SUBSTR (NAME, LENGTH (TRIM (NAME)), 1) = 'I'
This query will show all the names on file EPF ending with letter ‘I’.
23. Creating a table with data referring any other table
Create table csi21232/CPF as (select * from csi21232/EPF) with data
24. Existing/non existing...
Query to find uncommon fields records in both files
SELECT * FROM Library/File1 WHERE
ERR_LOCATION||ERR_MASTER_CO||ERR_SYMBOL||ERR_POLICY_NO||ERR_MODULE
Not in
(SELECT STS_LOCATION||STS_MASTER_CO||STS_SYMBOL||STS_POLICY_NO||STS_MODULE
FROM Library/File2)
25. To find out current date and time of system
SELECT CURDATE() CURTIME() FROM SYSIBM/SYSDUMMY1
Sensitivity: Internal & Restricted
