SQL400

Database: file CAPG

NAME EMPID AGE LOC DE SKILL

Abhishek 101 27 Hyderabad SCON AS400
RAVI 102 26 ANATPUR SSE PMO
SOUMYA 103 22 ODDISA SSE PMO
IMRAN KHAN 104 29 KOLKATA SCON AS400
AMOL JADHAV 105 30 MUMBAI SCON JAVA
BHANU P 106 22 KURNOOL SSE MF
BIBHUDATTA 107 24 ODDISA SE JDE

SQL Queries
1. To fetch all records from file

select * from lib/file

2. To fetch records based on particular condition

select * from lib/file where name/age/loc/gender (field) = ‘XXXXX’

Note: - Character fields need to be given in single quotes like ‘ ‘ .

3. How to see only few or particular field

Select field1, feild2 from lib/file

4. NOT EQUAL TO (Condition) is used to retrieve records which satisfy the not equal condition

Select * from lib/file where field <> condition

5. The BETWEEN operator selects values within a range. The values can be numbers, text, or
dates.

Select * from lib/file where field BETWEEN value1 and value2

6. The DELETE statement is used to delete records in a table.

DELETE from lib/file - It will delete complete file

DELETE from lib/file where some column = some particular record only

7. The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in a descending order, you can use the DESC keyword.
Select * from lib/file ORDER BY asc/desc

Or

Select * from lib/file ORDER BY field1 desc

8. The INSERT INTO statement is used to insert new records in a table.

INSERT INTO lib/file VALUES (value1, value2…)

9. SELECT DISTINCT statement is used to return only distinct values.

Select DISTINCT * from lib/file

10. The MAX() function returns the largest value of the selected column.

Select MAX() from lib/file.

11. The MIN() function returns the smallest value of the selected column.

Select MIN() from lib/file

Note: - Sum and AVG can also be calculated by this function, Sum(age), Avg(age)

12. To find all records of any specific field like all records of min(age), max(age).

Select * from lib/file where age in (select min(age) from lib/file)

Select * from lib/file where age in (select max(age) from lib/file)

13. Select field from file ORDER BY field DESC fetch first 2/3/4 rows only

To find max 3 ages from file (fetching limited records)

14. SUBSTRING field Position, length

Select SUBSTRING (field, 1, 3) from lib/file.

15. The UPDATE statement is used to update existing records in a table.

UPDATE lib/file SET field1 = new value WHERE field1 = old value.

16. The LIKE predicates searches for strings that have a certain/similar pattern.

Select * from lib/file WHERE field LIKE ‘%value%’

17. IN keyword is used to search specific records.

Select * from lib/file WHERE field IN (value1, value2….)

18. The RRN function returns the relative record number of a row.

Select RRN(a),a. * From file/lib value

19. CONCATINATION (||) is used to merge or club fields of the record together.

Select field1 || field2 || field3 from lib/file

Ex: if there are 3 diff. field like month, date and year then we can club all three fields together
as a single field with help of concatenation.

20. The UCASE() function converts the value of a field to uppercase.

Select UCASE (field) from lib/lib.

21. The LCASE() function converts the value of a field to lowercase.

Select LCASE (field) from lib/lib

22. The AVG() function returns the average value of a numeric column.

Select AVG(filed) from lib/file

23. The SUM() function returns the total sum of a numeric column.

Select SUM(filed) from lib/file

24. The LENGTH () function returns the length of the value in a text field.

Select LENGTH(field) from lib/file

25. To trim all blanks or zeroes from any field we can use trim function.

Select field , LENGTH ((rtrim(field))) from lib/file

26. Order by column no. , rather than column name

Select * from lib/file where order by 1/2/3/4/5

Ex: select * from kumar/apf order by 2 (2 is age field , 3 is location field)

27. Having condition: It can only be used with group by function : It will give result which
matches - given or required conditions

Select * from lib/file group by age having count(*) > 1

Ex: SELECT age, count(age) FROM kumar/apf GROUP BY age HAVING count(*) > 1
28. SQL operators:

Select * from file where field = ‘ ‘

select * from file where field <> ‘ ‘
Select * from file where field > ‘ ‘
Select * from file where field > = ‘ ‘
Select * from file where field < ‘ ‘
Select * from file where field <= ‘ ‘
Select * from file where field = ‘ ‘ or field = ‘ ‘
Select * from file where field = ‘ ‘ and field = ‘ ‘

29. Query to change some part of any field

Step 1 : select (substr(name,1,4)||substr(name ,5,8))from apf

Step 2: - update apf set name = (substr(name,1,4)||'ZZZ'||substr(name,8,8)) where
substr(name,5,3) = 'SHE'

30. Existing/non existing - Query to find uncommon fields records in both files

Select * from apf where name not in (select name from bpf)

31. Fetch records having name with last character is “H”.

SELECT * FROM APF WHERE SUBSTR (NAME, LENGTH (TRIM (NAME)), 1) = 'H'

Step 1 : select length(trim(name)) from apf – find total length

Step 2: select * from apf where substr (name, length(trim(name)),1) = 'h'

select * from clocfilewe/ckempeff where

substr(EENAME,5,1) = 'E'

result =

Van Essen, Richard H

So, Edgard
Van Eperen, John
La, Eric
PUCKETT, RICHARD M
Lu, Emily

32. Create alias to enter record in any member

CREATE ALIAS XYZ FOR file (member)

insert into XYZ values ('152090 CQ3 4 CN 224 126674 605 I')
SQL JOINS

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field
between them.

The most common type of join is: SQL INNER JOIN (simple join).
An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Orders" table

File 1 – APF

NAME AGE LOC GENDER

HITESH KARNANI 27 RAJASTHAN MALE
DEEPAK 28 PUNE MALE
BHIBHU DATTA 22 ODDISA MALE
IMRAN KHAN 26 BANGALURU MALE
RAJAT 26 VARANASI MALE
SHASHI 27 BANGALURU MALE
ABCD 41 RANDOM MALE
SUKHMEET SINGH 25 LUDHIANA MALE
Nitin 27 Meruth male
Mragank 27 Ajmer Male
Pramod 30 Allhabad male
Yogesh 30 Vaishali Male
Nimish 25 Indirapuram Male

File 2- BPF

MONTH YEAR DATE NAME LOC

1 2,017 31 ABHI Hyd
1 2,017 30 Bhanu Hyd
1 2,017 29 Ravi Banglore
1 2,017 28 Rahul Banglore
12 2,016 7 DEEPAK Bhopal
3 1,990 14 HITESH KARNANI Ghaziabad
5 1,980 11 Yogesh Vaishali
33. The INNER JOIN keyword selects all rows from both tables as long as there is a match
between the columns in both tables.

Select * from lib/file1 a INNER JOIN lib/file2 b on a.field = b.field

34. The FULL JOIN keyword is use show all records in matching rows including null.

Select * from lib/file1 a FULL JOIN lib/file2 b on a.field , b.field

35. LEFT JOIN shows all records of left table with only matching records of the right table.

Select * from lib/file1 a LEFT JOIN lib/file2 b on a.field, b.field

36. RIGHT JOIN shows all records of rigth table with only matching records of the left table.

Select * from lib/file1 a RiGHT JOIN lib/file2 b on a.field, b.field

37. LEFT EXCEPTION JOIN returns only the rows from the first table that do not have a match
in the second table.

Select * from lib/file1 a LEFT EXCEPTION JOIN lib/file2 b on a.field =b.field

38. RIGTH EXCEPTION JOIN returns only the rows from the second table that do not have a
match in the first table.

Select * from lib/file1 a RIGHT EXCEPTION JOIN lib/file2 b on a.field =b.field