×

Let's log you in.

or

Don't have a StudySoup account? Create one here!

×

or

Joins Multiple Tables

by: Ismail Yousuf

18

0

31

Joins Multiple Tables CSC 271

Ismail Yousuf
Comsats Institute of Information Technology
GPA 3.7

Get a free preview of these Notes, just enter your email below.

×
Unlock Preview

Joins concept in multiple tables of oracle database
COURSE
Database Management
PROF.
TYPE
Class Notes
PAGES
31
WORDS
CONCEPTS
Database, Oracle, SQL, Computer Science
KARMA
25 ?

Popular in ComputerScienence

This 31 page Class Notes was uploaded by Ismail Yousuf on Saturday January 9, 2016. The Class Notes belongs to CSC 271 at Comsats Institute of Information Technology taught by Amjad Usman in Winter 2016. Since its upload, it has received 18 views. For similar materials see Database Management in ComputerScienence at Comsats Institute of Information Technology.

×

Reviews for Joins Multiple Tables

×

×

What is Karma?

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 01/09/16
CSC271 Database Systems Lecture 20 Displaying Data from Multiple Tables [SQL SELECT - JOIN] Instructor : Amjad Usman amjadusman@ciitwah.edu.pk Thursday, December 10, 2015 Objectives – Today’s Lecture  Write multiple table SQL queries  Understanding Joins  Types of Joins 3 Introduction  Querying one table already done & practiced!  Real power of relational database  Storage of data in multiple tables  Necessitates creating queries to use multiple tables  Two Basic approaches for processing multiple tables  Sub-queries  Join 4 Processing Multiple Tables Using Joins  Join - Most frequently used operation - brings together data from multiple tables into one resultant table  Join can be achieved in two ways  Implicitlyby referring in aWHERE clause to the matching of common columns over which the tables are joined  Explicitlyby JOIN…..ON commands in FROM clause 5 What is the Join?  Use a join to query data from more than one table SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1=table2.column2  Write the join condition in theWHERE clause  Prefix the column name with the table name when the same column name appears in more than one table 6 Types of Joins Joins that are compliant with the SQL include the following:  Equijoin / Inner Join  Natural joins  Self join  Non-equijoin  Outer join  Cross Join 7 SQL Joins:Defining Join Types: INNER JOIN A B INTERSECTION 8 SQL Joins Defining Join Types: INNER JOIN  An INNER JOIN is also an equijoin,or equality join between equals.  An INNER JOIN matches on one or a set of columns values from one table:  When one table is involved,an INNER JOIN creates an intersection between two copies of a single table (typically done with two different column names).  When two or more tables are involved,an INNER JOIN creates an intersection between the tables based on designated column names. 9 Defining Join Types: INNER JOIN  Create an INNER JOIN by placing a position specific set of tables in the FROM clause followed by an ON or USING clause.  Equality statements are between one or more columns in two copies of one table or two tables:  When the columns share the same name and data type,  use the USING clause.  When the columns have different names but the same data type,  use the ON clause.  If only the word JOIN is used,an INNER JOIN is assumed by the SQL parser. 10 Defining Join Types: INNER JOIN  SELECT a.column1, b.column2 FROM table1 a, table2 b WHERE a.columnpk = b.columnfk;  SELECT a.column1, b.column2 FROM table1 a [INNER] JOIN table2 b ON a.columnpk = b.columnfk;  SELECT a.column1, b.column2 FROM table1 a [INNER] JOIN table2 b USING (same_column_name); 11 Cartesian Products  A Cartesian product is formed when:  A join condition is omitted  A join condition is invalid  All rows in the first table are joined to all rows in the second table  To avoid a Cartesian product,always include a valid join condition. 12 Generating a Cartesian Product EMPLOYEES (20 rows) DEPARTMENTS (8 rows) … Cartesian product: 20 x 8 = 160 rows … 13 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 ; … 14 Retrieving Record with Equijoin Employees ∞ Department DEPARTMENTS EMPLOYEES Foreign key Primary key 15 Using Equijoin Write SQL statement to do this: Employees ∞ Department Select * From employees ,departments Where employees.department_id=departments.department_id 16 Qualifying Ambiguous Column Names  Use table prefixes to qualify column names that are in multiple tables.  Use table prefixes to improve performance.  Use column aliases to distinguish columns that have identical names but reside in different tables. 17 Using Table Aliases  Use table aliases to simplify queries.  Use table aliases to improve performance. SELECT e.employee_id, e.last_name, d.location_id, department_id FROM employees e INNER JOIN departments d USING (department_id) ; 18 Retrieving Records with the ON Clause SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e INNER JOIN departments d ON (e.department_id = d.department_id); … 19 Retrieving Records with the USING Clause SELECT employees.employee_id, employees.last_name, departments.location_id, department_id FROM employees INNER JOIN departments USING (department_id) ; … 20 SELECT s.sid, s.name, r.bid FROM Sailors s INNER JOIN Reserves r ON s.sid = r.sid sid sname rating age sid bid day 22 Dustin 7 45.0 22 101 10/10/96 31 Lubber 8 55.5 95 103 11/12/96 95 Bob 3 63.5 s.sid s.name r.bid 22 Dustin 101 95 Bob 103 21 Joins Example  Show all customers and order date who have placed an order  SELECT CUSTOMER_NAME ,ORDER_DATE FROM CUSTOMER,ORDER WHERE CUSTOMER.CUSTOMER_ID = ORDER.CUSTOMER_ID  SELECT CUSTOMER_NAME ,ORDER_DATE FROM CUSTOMER INNER JOIN ORDER ON CUSTOMER.CUSTOMER_ID = ORDER.CUSTOMER_ID  SELECT CUSTOMER_NAME ,ORDER_DATE FROM CUSTOMER INNER JOIN ORDER USING CUSTOMER_ID 22 Applying Additional Conditions to a Join  Show employee id ,last name,dept id and location id who have a manager ID 149. SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e INNER JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ; 23 Joins Example  Show the students’ name and marks who failed in course CSC271  SELECT S.std_name, R.marks FROM Student S INNER JOIN Result R ON S.std_id = R.std_id AND R.marks<50 AND course_id = ‘CSC271’  SELECT S.std_name, R.marks FROM Student S INNER JOIN Result R USING std_id AND R.marks<50 AND course_id = ‘CSC271’ 24 Joining More than two table Employees Departments Locations 25 Joining More than two table select first_name,department_name,city from employees E,departments D,locations L where E.department_id=D.department_id and D.location_id=L.location_id select first_name,department_name,city from employees JOIN departments ON(employees.department_id=departments.department_id) JOIN locations ON(departments.location_id=locations.location_id) select first_name,department_name,city from employees JOIN departments using(department_id) JOIN locations using(location_id) 26 SQL Joins Defining Join Types: Non-equijoin  A non-equijoin is an indirect match:  Occurs when one column value is found in the range between two other column values  Uses the BETWEEN operator.  Also occurs when one column value is found by matching against a criterion using an inequality operator. 27 SQL Joins Defining Join Types: Non-equijoin  Example: SELECT a.column1, b.column2 FROM table1 a, table2 b WHERE a.columnpk >= b.columnfk; SELECT a.column1, b.column2 FROM table1 a, table2 b WHERE a.cola BETWEEN b.colx AND b.coly; 28 Non-Equijoins EMPLOYEES JOB_GRADES Salary in the EMPLOYEES table must be between lowest salary and highest … salary in the JOB_GRADES table. 29 Retrieving Records with Non-Equijoins SELECT e.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; … 30 THANK YOU SO MUCH 31 COMSATS Institute of Information Technology, Wah Campus

×

25 Karma

×

×

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

Jennifer McGill UCSF Med School

"Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over \$500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

Jim McGreen Ohio University

Forbes

"Their 'Elite Notetakers' are making over \$1,200/month in sales by creating high quality content that helps their classmates in a time of need."

Become an Elite Notetaker and start selling your notes online!
×

Refund Policy

STUDYSOUP CANCELLATION POLICY

All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email support@studysoup.com

STUDYSOUP REFUND POLICY

StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here: support@studysoup.com

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to support@studysoup.com