Understanding SQL Subqueries: A Comprehensive Guide to Nested Queries

1. Subquery

2. Subquery • Subquery or Inner query or Nested query • A subquery is a query within another query. • The outer query is called as main query and • The inner query is called as subquery. • A subquery is usually added in the WHERE Clause of the sql statement.

3. Subquery • Subquery or Inner query or Nested query • Syntax • The subquery (inner query) executes once before the main query (outer query) executes.

4. Subquery • Subquery or Inner query or Nested query • Two tables ‘STUDENT' and ‘MARKS' with common field ‘ID'. • To write a query to identify all students who get better marks than that of the student who's ID is ‘2‘. • If we know the mark of ID ’2’ then • SELECT A.ID,A.NAME, B.MARK FROM STUDENT A, MARK B WHERE A.ID=B.ID AND B.MARK >70;

5. Subquery • Subquery or Inner query or Nested query • STUDENT MARK S • But we do not know the marks of ‘2'. • we require two queries (Nested query) • One query returns the marks of '2' and • Second query identifies the students who get better marks than the result of the first query • SELECT A.ID,A.NAME, B.MARK FROM STUDENT A, MARKS B WHERE A.ID=B.ID AND B.MARK > (SELECT MARK FROM MARKS WHERE ID=2);

6. Subquery • Rules for subqueries: • Subqueriesmust be enclosed within parentheses. • Inner query can have only one column in the SELECT clause, unless multiple columns are in the main query. • Inner Query cannot use An ORDER BY clause, although the main query can use an ORDER BY. • The BETWEEN operator cannot be used with a inner query; however, the BETWEEN operator can be used within the main query.

7. Subquery • Subqueries can be used with • SELECT • INSERT • UPDAT E, and • DELET E statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

8. Subquery • Subquerie in select statementTab1 Tab2 • List all details from tab2, where rollno =4 in tab1 • select * from tab2 where rollno in (select rollno from tab1 where rollno=4)

9. Subquery • Subquerie in insert statement Tab1 Tab4 • Consider table tab1 & tab4 with similar structure. • To copy the records from tab1 to tab4 where rollno=3 • insert into tab4 select * from tab1 where rollno in (select rollno from tab1 where rollno=3)

10. Subquery • Subquerie in update statement Tab1 Tab2 • Increment the marks with 10 in tab2, whose rollno = 4 in tab1 • update tab2 set mark=mark+10 where rollno in (select rollno from tab1 where rollno=4)

11. Subquery • Subquerie in delete statement Tab1 Tab2 • Delete all details from tab2, where rollno is 4 in tab1 • delete from tab2 where rollno in (select rollno from tab1 where rollno=4)

12. Subquery • NULL Functions • Consider the table N_TEST • 3rd and 2nd row contains NULL values • SELECT * FROM N_TEST WHERE COL1=NULL; • Then result will • no data found

13. Subquery • NULL Functions • Consider the table N_TEST • 3rd and 2nd row contains NULL values • Instead we must use the IS NULL or IS NOT NULL operators • SELECT * FROM N_TEST WHERE COL1 IS NULL; • Then • SELECT * FROM N_TEST WHERE COL1 IS NOT NULL;

14. Subquery • NULL Functions • NVL • The NVL function allows to replace null values with a default value. • If the value in the first parameter is null, the function returns the value in the second parameter. • If the first parameter is any value other than null, it is returned unchanged.

15. Subquery • NULL Functions • NVL • COL1 in the N_TEST table contains null in all rows except the first & second • Using the NVL function we replace the null values with ‘AJI'. • SELECT COL1,NVL(COL2,'AJI') AS OUTPUT FROM N_TEST;

16. Subquery • NULL Functions • DECODE • The DECODE function have 3 parameters. • If second parameter occurs will replace to third parameter • Other wise it return NULL • SELECT COL1,DECODE(COL2,NULL,'AJI') AS OUTPUT FROM N_TEST; • Here all NULL, converted to ‘AJI’ other wise it converted to NULL

17. Subquery • NULL Functions • NVL2 • The NVL2 function accepts three parameters. • If the first parameter value is not null it returns the value in the second parameter. • If the first parameter value is null, it returns the third parameter. • SELECT COL1,NVL2(COL2,'SAJI','AJI') AS OUTPUT FROM N_TEST;

18. Subquery • Numeric Functions • ABS(number) • Returns the absolute positive value of an expression. • SELECT ABS(COL1) AS OUTPUT FROM N_TEST;

19. Subquery • Numeric Functions • MOD(number, divisor) • Returns the remainder of the division from 2 integer values. • SELECT COL1,MOD(COL1,2) AS OUTPUT FROM N_TEST

20. Subquery • Numeric Functions • POWER(number, power) • Returns the exponential value for the numeric expression. • SELECT COL1,POWER(COL1,2) AS OUTPUT FROM N_TEST

21. Subquery • String Functions • CONCAT(string1, string2) • Join 2 strings • SELECT COL1,CONCAT(COL1,'AJI') AS OUTPUT FROM N_TEST

22. Subquery • String Functions • INITCAP(string) • Returns a string with each word's first character in uppercase and the rest in lowercase. • SELECT COL1,INITCAP(COL2) AS OUTPUT FROM N_TEST

23. Subquery • String Functions • REPLACE(string,searchString,replacement) • Used to replace the string1, having string2 with string3. • SELECT COL1,REPLACE(COL2,'one','TWO') AS OUTPUT FROM N_TEST

24. Subquery • String Functions • SUBSTR(string,start,length) • Displays the string Starting with the character in string and select the next characters. • SELECT COL1,SUBSTR(COL2,2,2) AS SUBSTR FROM N_TEST

25. Subquery • Date Functions • SYSDATE • Returns the current date • Tab1 • SELECT SYSDATE FROM TAB1;

26. Subquery • Date Functions DD • Addition of date • SELECT DOB,DOB+2,DOB+30 FROM DD;

27. Subquery • Date Functions • MONTHS_BETWEEN  • returns number of months between dates date1 and date2. • If date1 is later than date2, then the result is positive. • Syntax MONTHS_BETWEEN(date1,date2); • Other wise negative • SELECT DOB, MONTHS_BETWEEN('10-FEB-1998',DOB) FROM DD

28. Subquery • Date Functions • ADD_MONTHS • Used to add month • Syntax ADD_MONTHS(date,int); • Add DOB month with 2 • SELECT DOB, ADD_MONTHS(DOB,2) FROM DD;

29. Subquery • Date Functions • LAST_DAY(date1) Find the date of the last day of the month that contains date1. • SELECT DOB, LAST_DAY(DOB) FROM DD;

30. Subquery • Date Functions • TRUNC(date1,’char’) Find the date of first day of the month containing in date1 when char=‘MONTH’. If char=‘YEAR’, it finds first day of year containing date1. • SELECT DOB, TRUNC(DOB,'MONTH'),TRUNC(DOB,'YEAR') FROM DD;

31. Subquery • Date Functions • TO_CHAR() • SELECT DOB, TO_CHAR(DOB,'DAY-DDTH-MONTH-YEAR') FROM DD;

32. Subquery • Date Functions • TO_DATE • converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. • SELECT DOB, TO_DATE('FEB 17 2014','MONTH DD YYYY') FROM DD;

33. Subquery • Date Functions

34. Subquery • Date Functions