1 / 19

SQL/PL SQL

SQL/PL SQL. Quiz 2. Q1.Create a table called "Persons" that contains five columns: PersonID , LastName , FirstName , Address, and City with Person ID Primary Key  Q2. Insert a record in the "Persons Table" from another table PersonBackup same structure as Person Table. 

kiral
Download Presentation

SQL/PL SQL

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/PL SQL Oracle By Rana Umer

  2. Quiz 2 Q1.Create a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City with Person ID Primary Key  Q2. Insert a record in the "Persons Table" from another table PersonBackup same structure as Person Table.  Q3. Update the record City =Islamabad where City is Lahore. Q4. Show all record in the Person Table where City is Islamabad   Q5. Show Person ID, FirstName & LastName from Person where City is Islamabad. Q6. Create View to show Person Table. Q7. Show record from Person View   Q8. Add new column ContactNo number in Person Table Q9. Create User Ali assign role DBA. Q10. Create User Khalid assign Select, insert privileges Q11. Remove insert privileges from Khalid. Q12. Create Index on Person ID. Q13. Show ROWID of the Indexes. Oracle By Rana Umer

  3. Displaying Data from Multiple Tables Types of Joins • Joins that are compliant with the SQL:1999 standard include the following: • Natural joins: • NATURAL JOIN clause • USING clause • ON clause • Outer joins: • LEFT OUTER JOIN • RIGHT OUTER JOIN • FULL OUTER JOIN • Cross joins Oracle By Rana Umer

  4. Displaying Data from Multiple Tables An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL equijoins (INNER JOIN) (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. List the types the different SQL JOINs you can use: INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables Natural JOIN: Return all rows of common columns in both the tables Oracle By Rana Umer

  5. Displaying Data from Multiple Tables EMPLOYEES DEPARTMENTS … … Oracle By Rana Umer

  6. Displaying Data from Multiple Tables • Natural joins: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] Or [JOINtable2USING (column_name)] Or [JOINtable2ON (table1.column_name = table2.column_name)] Oracle By Rana Umer

  7. Displaying Data from Multiple Tables • Natural joins: SELECTemployee_id, last_name, location_id, department_id FROMemployees , departmentsWhere employees.department_id=Departments.department_id; SELECTemployee_id, last_name, location_id, department_id FROMdepartmentsJOIN employees ON employees.department_id=Departments.department_id; SELECTemployee_id, last_name, location_id, department_id FROM employees JOIN departments USING (department_id) ; ------------------------------------------------------------------------------------------ SELECTemployee_id, last_name, location_id, department_id FROM employees Natural JOIN departments; Oracle By Rana Umer

  8. Displaying Data from Multiple Tables Table Alias SELECTe.employee_id, e.last_name, e.location_id, d.department_id FROMemployees e, departments dWhere e.department_id=d.department_id; SELECTe.employee_id, e.last_name, e.location_id, department_id FROM employees eJOIN departments d USING (department_id) ; Oracle By Rana Umer

  9. Displaying Data from Multiple Tables Additional Conditions to a Join Use the AND clause or the WHERE clause to apply additional conditions: SELECTe.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) ANDe.manager_id = 149 ; SELECTe.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHEREe.manager_id = 149 ; Oracle By Rana Umer

  10. Displaying Data from Multiple Tables Joining a Table to Itself • Self joins: EMPLOYEES (WORKER) EMPLOYEES (MANAGER) EMPLOYEES (WORKER) EMPLOYEES (MANAGER) … … MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table. Oracle By Rana Umer

  11. Displaying Data from Multiple Tables Using the ON Clause • Self joins: SELECTworker.last_nameemp, manager.last_namemgr FROM employees worker JOIN employees manager ON (worker.manager_id = manager.employee_id); … Oracle By Rana Umer

  12. Displaying Data from Multiple Tables • NonEqui joins: JOB_GRADES EMPLOYEES JOB_GRADES table defines the LOWEST_SAL and HIGHEST_SAL range of values for each GRADE_LEVEL. Hence, the GRADE_LEVEL column can be used to assign grades to each employee. … On NonEqui Join Condition does with out ‘=‘ sign Oracle By Rana Umer

  13. Displaying Data from Multiple Tables • NonEqui joins: SELECTe.last_name, e.salary, j.grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal; … On NonEqui Join Condition does with out ‘=‘ sign Oracle By Rana Umer

  14. Displaying Data from Multiple Tables • OUTER join: • LEFT OUTER join • RIGHT OUTER join • FULL OUTER join Oracle By Rana Umer

  15. Displaying Data from Multiple Tables Outer Joins • An Inner Join of two tables returning only matched rows. • A join between two tables that returns the results of the inner join as well as the unmatched rows from the Left, Right or Full table is called a Left, Right or Full outer join. Oracle By Rana Umer

  16. Displaying Data from Multiple Tables LEFT OUTER JOIN Outer Joins SELECTe.last_name, e.department_id, d.department_name FROMemployees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ; … Oracle By Rana Umer

  17. Displaying Data from Multiple Tables RIGHT OUTER JOIN Outer Joins SELECTe.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ; … Oracle By Rana Umer

  18. Displaying Data from Multiple Tables FULL OUTER JOIN Outer Joins SELECTe.last_name, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; … Oracle By Rana Umer

  19. Displaying Data from Multiple Tables Creating Cross Joins • The CROSS JOIN clause produces the cross-product of two tables. • This is also called a Cartesian product between the two tables. SELECT last_name, department_name FROM employees CROSS JOIN departments ; … Oracle By Rana Umer

More Related