1 / 34

Subquery

Subquery. 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. Subquery.

rhian
Download Presentation

Subquery

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related