1 / 35

Lecture 2

Lecture 2. Joins and sub-queries. Topics. Joins Simple; Outer Sub-queries aliases IN Operator NULL values Saving and running files. Joins. Joins are how to connect together the tables in a relational database

peggy
Télécharger la présentation

Lecture 2

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. Lecture 2 Joins and sub-queries

  2. Topics • Joins • Simple; Outer • Sub-queries • aliases • IN Operator • NULL values • Saving and running files

  3. Joins • Joins are how to connect together the tables in a relational database • To join tables we must have a column in each table that contains the same information • Two main types: simple, outer.

  4. Joins • In our example tables (emp and dept), each contains a column: deptno (the name does not have to be the same). • This column contains the dept number for the employee in the emp table and the dept table has departmental information

  5. Joins • To join the two tables to select, say, the employee name and the name of their department we use a join-condition in the WHERE clause:SELECT ename, dname FROM emp, deptWHERE emp.deptno = dept.deptno;

  6. Joins (example2) To find ALLEN’s location enter: SELECT ename, locFROM emp, deptWHERE ename = ‘ALLEN’AND emp.deptno = dept.deptno;

  7. Joins • In general:SELECT columnsFROM table1, table2, …WHERE join-condition; • The join-condition must join all the tables: for two tables we need a single condition, for three we require two conditions etc

  8. Multiple Table Joins • SELECT columnsFROM tab1, tab2, tab3WHERE join-condition1 AND join-condition2;

  9. Simple Joins • If a column appearing in SELECT has the same name in both tables, we MUST specify which one we requireSELECT ename, dname, dept.deptno FROM emp, deptWHERE emp.deptno = dept.deptno;

  10. Outer joins • Return rows from table which have no match in other tableSELECT columnsFROM table1, table2WHERE join-condition1 = join-condition2 (+);

  11. Outer joins • SELECT dept.deptno, dname, sum(sal)FROM emp, deptWHERE emp.deptno (+) = dept.deptnoGROUP BY dept.deptno, dnameORDER BY dept.deptno • Append outer join symbol to table without matching rows

  12. Subqueries • This is when one of the parts of WHERE clause is a query itself. • Consider the following question: list the name and salary of all employees who earn greater than the average salary? • We need to determine what the average salary is before we can ask who earns more than it.

  13. Sub-queries • To determine the average salary:SELECT AVG(sal) FROM emp;now who earns more than thisSELECT ename, sal FROM empWHERE sal > (SELECT AVG(sal) FROM emp);

  14. Sub-queries • The inner query is executed just once, i.e. the average salary is found and the outer query determines who earns more than the average salary. • Queries can be composed where the sub-query is executed once for each row in the outer query

  15. Sub-queries • List employees, department number and salary for those employees who earn more than their departmental average salary.SELECT ename, deptno, sal FROM empWHERE sal > (average salary of candidate employee’s department); • You also need a subquery that calculates the average salary of each candidate employee’s department

  16. SELECT AVG(SAL) FROM emp WHERE deptno = (candidate row’s value of DEPTNO) • As the main query considers each candidate row, it must invoke the subquery and ‘tell’ it the employee’s dept number. • The subquery must then compute the average salary for that employee’s dept. • The main query must then compare the employee’s salary to the department’s average salary.

  17. Subqueries • How do we tie the department no in the inner query to the department no in the outer to get that individuals department’s average salary? • The trick is to alias the name of the table emp in the outer query

  18. Sub-queries • SELECT ename, deptno, salFROM emp aliasempWHERE sal > (SELECT AVG(sal) FROM emp WHERE aliasemp.deptno = deptno);

  19. More aliasing • Example: for each manager list their staffSELECT manager.ename, worker.enameFROM emp manager, emp workerWHERE worker.mgr = manager.empno; • Manager here does not mean job = ‘MANAGER’

  20. IN Operator • Matches any one in list. • Example: list average salary of only CLERKs and ANALYSTsSELECT AVG(sal), job FROM empWHERE job IN (‘CLERK’, ‘ANALYST’)GROUP BY jobORDER BY job

  21. NULL values • Care must exercise when performing calculations that involve NULL (empty) values. A useful function is NVL(column, 0)which converts NULL values to 0 for the calculation.

  22. NULL values • We can use NULL in SQL commands:SELECT * FROM empWHERE comm IS NOT NULL;INSERT INTO empVALUES (7256, ‘GILES’, ‘CLERK’, 7788, ‘15-AUG-80’, 1100, NULL, 20);

  23. SQLplus • Command filesInstead of typing commands directly in Oracle, they can be placed in a file (using Notepad).

  24. SQLplus • The sequence of commands in a file can be run with the command:START <filename> • This means that complicated sequences of commands (like reports) can be written outside of SQL*Plus and then run

  25. SQLplus • We can save the current SQL command to a file with:SAVE <filename> • To load a file but not run it:GET <filename>

  26. Summary • Joins • Simple; Outer • Sub-queries • aliases • IN Operator • NULL values • Saving and running files

More Related