1 / 63

Structured Query Language Recap

Learn the basics of Structured Query Language (SQL) for Oracle 11g database programming, including SELECT statements, WHERE clauses, wild cards, single row functions, and group functions.

pvilla
Download Presentation

Structured Query Language Recap

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. Database Programming Using Oracle 11g Structured Query Language Recap

  2. Structured Query Language Basics Select Statement Its only known way to interact with Database to retrieve Data. Declarative Language DRL: Data Retrieval Language

  3. Structured Query Language Basics Select distinct * | ColumnName from TableName; * mean all the column ColumnName is one or more column from table Distinct mean unique values from column

  4. Structured Query Language Basics Select Statement Assumption is tables are created and data is populated

  5. Structured Query Language Basics Implementing Select Statement

  6. Structured Query Language Basics Select depno from emp; Select distinct deptno from emp; Select * from emp; Select ename, job from emp;

  7. Structured Query Language Basics Select Statement Assumption is tables are created and data is populated

  8. Structured Query Language Basics SQL and where clause Where clause is used to restrict number of rows Required rows can be fetch using Where clause Applied at each row of table

  9. Structured Query Language Basics Select distinct * | ColumnName from TableName where condition 1 and / or condition 2 and / or condition 3 Conditions can include: >, <, =, <>, AND, OR, NOT

  10. Structured Query Language Basics SQL and where clause If there are multiple conditions in where clause then at a time only one condition will be evaluated

  11. Structured Query Language Basics Implementing Where Clause

  12. Structured Query Language Basics Table Name: Emp Question # 1: Write a query to find out list of all those employee name who are earning more than 2500 but less than 5000. Question # 2: Write a query to find out all those employees who are working in Dept # 20 with designation of Analyst but not earning more than 2000 and was hired at least 30 years ago.

  13. Structured Query Language Basics Table Name: Emp Question # 1: Write a query to find out list of all those employee name who are earning more than 2500 but less than 5000. Solution: Select ename from emp where sal>2500 and sal < 5000

  14. Structured Query Language Basics Table Name: Emp Question # 1: Write a query to find out all those employees who are working in Dept # 20 with designation of Analyst but not earning more than 2000 and was hired at least 30 years ago. Solution: Select * from emp where deptno=20 and Job=‘Analyst’ and sal <2000 and hiredate<sysdate -10000

  15. Structured Query Language Basics Implementing Where Clause In where clause we have recap logical operators and comparison operators

  16. Structured Query Language Basics Wild Cards Wild mean any character can be included Used for pattern matching to approximity

  17. Structured Query Language Basics Wild Cards Two Wild Cards %: Zero or more characters - : Exactly one character

  18. Structured Query Language Basics Implementing Wild Cards - I Wild Cards are implemented using LIKE Operator

  19. Structured Query Language Basics Write a query to display list of name of all those employees who are having either E in the name or the name should end with G with at least two characters but should be working in Dept#30 and salary at least 1500 Solution: select * from emp where ename like '%E%' or ename like '%-G' and deptno=30 and sal >=1500;

  20. Structured Query Language Basics Implementing Wild Cards - II

  21. Structured Query Language Basics Write a query to display all information about all those employees who are having ER in the job with at least three character in job and should be earning at least 2500 but at most 5000 and should be with company for at most 15 years Select * from emp where job like ‘%ER-%’ and sal > 2500 and sal < 5000 and hiredate <=sysdate – 5600;

  22. Structured Query Language Basics Single Row Functions Single row function operator in single row Return one result per row either Data or not Data

  23. Structured Query Language Basics Round and Trunc : select round (194.683,1), trunc(194.683,1) from dual; select ename, length(ename), instr (ename, 'A'), concat (ename,job) from emp where instr(ename,'A')=3; SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;

  24. Structured Query Language Basics Group Functions Group functions operate on multiple rows There is one row per group as output Group functions cannot be used in where clause

  25. Structured Query Language Basics Implementing Group Functions-I

  26. Structured Query Language Basics Write a query to display sum, minumum, maximum and average salaries which company is paying to its employees Solution: Select count(*), sum (sal), min(sal), max (sal), Avg(sal) from emp;

  27. Structured Query Language Basics Implementing Group Functions-II

  28. Structured Query Language Basics Write a query to display sum, minumum, maximum and average salaries which company is paying to its employees but employees from Dept# 20 should not be shown and average salaries should be less than 1500 Solution: Select count(*), sum (sal), min(sal), max (sal), Avg(sal) from emp where deptno !=20 and avg (sal) <=1500; - Error

  29. Structured Query Language Basics Group By Clause Group by clause group together similar row together to form group Groups functions are used with Group by clause

  30. Structured Query Language Basics Group By Clause After Select statement only those columns can be displayed which are written after Group-by clause

  31. Structured Query Language Basics Implementing Group By Clause

  32. Structured Query Language Basics What is the total salary paid by each department to its employees. Steps to Solution: i. Need to group together all rows of each department separately i-e creating groups ii. Need sum of salary each department - group

  33. Structured Query Language Basics

  34. Structured Query Language Basics What is the total salary paid by each department to its employees. Solution select sum(sal), deptno from emp group by deptno; Column which is coming after Group by clause can only after select clause

  35. Structured Query Language Basics Implementing Group By Clause-II

  36. Structured Query Language Basics What is average and maximum salary paid to each Job who are reporting to MGR 7839 and all the emlpoyees should have no occurrence of K in their ename Solution -1 Select Avg(sal), max(sal) from emp where mgr=7839 and ename not like ('%K%') group by job;

  37. Structured Query Language Basics What is average and maximum salary paid to each Job who are reporting to MGR 7839 and all the emlpoyees should have no occurrence of K in their ename Solution -2: Select Avg(sal), max(sal) , ename from emp where mgr=7839 and ename not like ('%K%') group by job; - Error : Only Job column can be shown after select

  38. Structured Query Language Basics Having Clause To restrict groups having clause is used. Equivalent to Where clause except having is applied to groups only

  39. Structured Query Language Basics Implementing Having Clause-I

  40. Structured Query Language Basics Write a query to display average salary of each department if there are at least 2 employees working in the department and minimum salary is more than average salary by 100 Solution: Select avg (sal) from emp Group by deptno Having count (*) > 3 and min(sal)>avg(sal)+100;

  41. Structured Query Language Basics Implementing Having Clause-II

  42. Structured Query Language Basics Write a query to display maximum and minimum salary by each department if average salary is more than 1500 of the department and less than 3000. The employee should not be included if there is any occurrence of ‘A’ in the ename or earning no commission and is hired at least six month before

  43. Structured Query Language Basics Select max(sal) , min(sal) from emp Where ename not like ‘%A%’ or comm is null and months_between(sysdate, hiredate)>6 Group by deptno Having max(sal) > 4500 and avg(sal)<1500;

  44. Structured Query Language Basics Order by Clause Use to sort data Can use independent of where or group by or having clause

  45. Structured Query Language Basics Select deptno, sal from emp order by sal;

  46. Structured Query Language Basics What are Joins Joins are required when data from Multiple tables are required. No of Joins = No. Tables – 1 Comparison of PK and FK are implementation

  47. Structured Query Language Basics Implementing Join-I

  48. Structured Query Language Basics Basic Join Statement: Select empno,ename, d.deptno, dname from emp e, dept d where d.deptno=e.deptno;

  49. Structured Query Language Basics Implementing Join-II

  50. Structured Query Language Basics Write a query to display list of employee name and name of department of all those employees who are hired at least 10 years before and are working as Analyst Select empno,ename, d.deptno, dname, round(months_between(sysdate, hiredate),0), hiredate from emp e, dept d where d.deptno=e.deptno and months_between(sysdate, hiredate) > 120 and job=‘Analyst’;

More Related