Untitled

08/02/-2024
=============================================================================
Joins:
A join clause is used for combine row from two or more table ,based on arelated column between them.

Different types of join 
1.inner join
2. left join = left outer join
3. right join =right outer join 
4. full join = full outer join 


1 inner join:-  return records that have matching values from both tables

syntax :-
select columns from table1 
inner join table2
on table1.matching_column= table2.matching_column

select * from customers as c
inner join orders as o
on c.customer_id=o.customer_id;

select * from customers as c
inner join orders as o
on c.customer_id=o.order_id;

select c.customer_id ,c.first_name,o.order_id,o.item
from customers c
inner join orders o
on c.customer_id=o.customer_id;


2.left join:- return all records from left table and the matching records from right tables.

syntax :-
select columns from table1 
left join table2
on table1.matching_column= table2.matching_column

exa:
select * from customers as c
left join orders as o
on c.customer_id=o.customer_id;


select c.customer_id ,c.first_name,o.order_id,o.item
from customers c
left join orders o
on c.customer_id=o.customer_id
order by c.customer_id;

select c.customer_id ,c.first_name,o.order_id,o.item
from orders o
left join customers c
on c.customer_id=o.customer_id
order by c.customer_id;



3.right join:- return all records from right table and the matching records from left tables.

syntax:
select columns from table1 
left join table2
on table1.matching_column= table2.matching_column


exa:
select * from customers c
right join orders o
on c.customer_id = o.customer_id
order by o.customer_id;

select o.customer_id, c.first_name ,o.item,o.amount
from customers c
right join orders o
on c.customer_id = o.customer_id
order by o.customer_id;

4. full join:- return all records from both tables there  is match in  either left or right table.
syntax:-
select columns from table1 
full join table2
on table1.matching_column= table2.matching_column

exa:
select o.customer_id, c.first_name ,o.item,o.amount
from customers c
full join orders o
on c.customer_id = o.customer_id
order by o.customer_id; 


_______________________________________________________________________________________________________________________________________________________
assignment:

Q1.From the following tables write a SQL query to find the salesperson(s) and the customer(s) he represents.
 Return Customer Name, city, Salesman, commission.

Q2.From the following tables write a SQL query to locate those salespeople who do 
not live in the same city where their customers live and have received a commission 
of more than 12% from the company. Return Customer Name, customer city, Salesman, salesman city, commission.

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005
Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12