1 / 28

SQL Equivalent for JOIN

SQL Equivalent for JOIN. EQUI JOIN: An equal sign (=) is used as comparison operator in the where clause to refer equality. Syntax: SELECT column_list (s) FROM table1, table2.... WHERE table1.column_name = table2.column_name ;.

libba
Download Presentation

SQL Equivalent for JOIN

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. SQL Equivalent for JOIN EQUI JOIN: An equal sign (=) is used as comparison operator in the where clause to refer equality. Syntax: SELECT column_list(s) FROM table1, table2.... WHERE table1.column_name = table2.column_name; NON EQUI JOIN uses comparison operator instead of the equal sign like >, <, >=, <= along with conditions. Syntax: SELECT * FROM table_name1, table_name2 WHERE table_name1.column [> | < | >= | <= ] table_name2.column; • INNER JOIN: Selects all rows from both tables as long as there is a match between the columns in both tables. • Syntax: • SELECT column_name(s)FROM table1INNER JOIN table2ON table1.column_name=table2.column_name;

  2. SQL Equivalent for JOIN CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. Syntax (Cartesian Product): Select * FROM table1 CROSS JOIN table2; NATURAL JOIN: Creates an implicit join clause based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. Syntax: Select * FROM table1 NATURAL JOIN table2;

  3. SQL Equivalent for JOIN • OUTER JOIN returns all rows from both the participating tables which do/do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only. Subtypes: • LEFT OUTER JOIN or LEFT JOIN • RIGHT OUTER JOIN or RIGHT JOIN • FULL OUTER JOIN • LEFT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table before the JOIN clause (i.e. table1). • Syntax: • Select * • FROM table1 • LEFT OUTER JOIN table2 • ON table1.column_name=table2.column_name;

  4. SQL Equivalent for JOIN RIGHT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table written after the JOIN clause. Syntax: Select * FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name; FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Syntax: Select * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

  5. DIVISION Attributes of Rbe denoted as Z Attributes of S be denoted as X, such that X is subset of Z. R divided by S is denoted by: T(Y) = R(Z) ÷ S(X) where the attributes in the resultant relation T are the attributes of R that are not attributes of S. i.e. Y (Attributes in Result) = Z (Attributes in R) - X (Attributes in S) AND the tuples of the resultant relation T are such that each tupleof T in combination with every tuple in S must appear in R.

  6. DIVISION Retrieve the names of employees who work on all the projects that John Smith works on .

  7. DIVISION Solution : Step 1 : Retrieve the list project numbers that john smith works on and store those project numbers in SMITH_PNOS temporary relation. SMITH  FNAME = ‘John’ AND LNAME = ‘Smith’(EMPLOYEE) SMITH_PNOS  πPNO( WORKS_ON ESSN=SSN SMITH)

  8. DIVISION Step 2 : Select ESSN and PNO tuples from WORKS_ON relation and store in SSN_PNOS relation. SSN_PNOS πESSN , PNO( WORKS_ON)

  9. DIVISION Step 3 : Finally, apply DIVISION operation to the two relation, which gives the desired employees (SSN) working on all the projects that john smith works on. SSNS(SSN )  SSN_PNOS  SMITH_PNOS

  10. DIVISION Step 4 & 5: We can then select Names of the employees with these SSN by using natural JOIN of SSNS and EMPLOYESS and selecting the desired columns. RESULT  πFNAME , LNAME (SSNS * EMPLOYEE) So following is the sequence of relation expressions for the query SMITH  FNAME = ‘John’ AND LNAME = ‘Smith’ (EMPLOYEE) SMITH_PNOS  πPNO ( WORKS_ON ESSN=SSN SMITH) SSN_PNOS πESSN , PNO ( WORKS_ON) SSNS(SSN )  SSN_PNOS  SMITH_PNOS RESULT  πFNAME , LNAME (SSNS * EMPLOYEE)

  11. Examples of Queries on Company database Q1: Retrieve the name and address of all employees who work for the ‘Research’ department. RESEARCH_DEPT  DNAME=’Research’ (DEPARTMENT) RESEARCH_EMPS  (RESEARCH_DEPT DNUMBER= DNO EMPLOYEE) RESULT FNAME, LNAME, ADDRESS(RESEARCH_EMPS)

  12. Examples of Queries on Company database

  13. Examples of Queries on Company database Q2: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthrate STAFFORD_PROJS = PLOCATION=’Stafford’ (PROJECT) CONTR_DEPT = (STAFFORD_PROJS DNUM=DNUMBER DEPARTMENT) PROJ_DEPT_MGR = (CONTR_DEPT MGRSSN=SSN EMPLOYEE) RESULT = PNUMBER, DNUM, LNAME, ADDRESS, BDATE(PROJ_DEPT_MGR)

  14. Examples of Queries on Company database

  15. Examples of Queries on Company database Q3: Find the names of employees who work on all the projects controlled by department number 5. DEPT5_PROJS(PNO)= PNUMBER(DNUM= 5(PROJECT)) EMP_PRJO(SSN, PNO) = ESSN, PNO(WORKS_ON) RESULT_EMP_SSNS = EMP_PRJO ÷ DEPT5_PROJS RESULT = LNAME, FNAME(RESULT_EMP_SSNS * EMPLOYEE)

  16. Examples of Queries on Company database

  17. Examples of Queries on Company database Q4: Make a list of project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project. SMITHS(ESSN) = SSN (NAME=’Smith’ (EMPLOYEE)) SMITH_WORKER_PROJ = PNO(WORKS_ON * SMITHS) MGRS = LNAME, DNUMBER(EMPLOYEE SSN=MGRSSN DEPARTMENT) SMITH_MANAGED_DEPTS(DNUM) = DNUMBER(LNAME= ’Smith’(MGRS)) SMITH_MGR_PROJS(PNO)=PNUMBER (SMITH_MANAGED_DEPTS * PROJECT) • RESULT = (SMITH_WORKER_PROJS U SMITH_MGR_PROJS)

  18. Examples of Queries on Company database

  19. Examples of Queries on Company database Q5: List the names of all employees with two or more dependents. T1(SSN, NO_OF_DEPTS) = ESSNℱ COUNT DEPENDENT_NAME(DEPENDENT) T2 = NO_OF_DEPS ≥2 (T1) RESULT = LNAME, FNAME (T2 * EMPLOYEE)

  20. Examples of Queries on Company database

  21. Examples of Queries on Company database Q6: Retrieve the names of employees who have no dependents. ALL_EMPS SSN(EMPLOYEE) EMPS_WITH_DEPS(SSN) ESSN(DEPENDENT) EMPS_WITHOUT_DEPS  (ALL_EMPS - EMPS_WITH_DEPS) • RESULT LNAME, FNAME(EMPS_WITHOUT_DEPS * EMPLOYEE)

  22. Examples of Queries on Company database

  23. Examples of Queries on Company database Q7: List the names of managers who have at least one dependent. • MGRS(SSN) = MGRSSN (DEPARTMENT) EMPS_WITH_DEPS(SSN) = ESSN(DEPENDENT) • MGRS_WITH_DEPS = (MGRS ∩ EMPS_WITH_DEPS) • RESULT = LNAME, FNAME(MGRS_WITH_DEPS * EMPLOYEE)

  24. Assignment – II • Specify the following queries on the company database schema, using the relational operators. • Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project. • b. List the names of all employees who have a dependent with the same first name as themselves. • c. Find the names of all employees who are directly supervised by ‘Franklin Wong’. • d. For each project, list the project name and the total hours per week (by all employees) spent on that project. • e. Retrieve the names of all employees who work on every project.

  25. Assignment - II f . Retrieve the names of all employees who do not work on any project. g. For each department, retrieve the department name and the average salary of all employees working in that department. h. Retrieve the average salary of all female employees. • Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston. j. List the last names of all department managers who have no dependents.

More Related