Untitled

15/02/2024
=======================

foreign key
-------------------
use ddd;
select * from customer;
select * from order1;

-- insert into order1 values(1,"dress",1000,10);
create table Order3(id int primary key ,ord_name varchar(222),price int,cust_id int,
foreign key(cust_id) references customer(cust_id)
);

insert into Order3 values(1,"dress",1000,101);


-----------------------------------------------------------------------------------------------------------------------

Join in sql:-
=================
join is used to combine data or rows from two or more table based common field between them.
there are differet 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 thet have matching values from both tables.
syntax:
select table1.column1,table1.column1.....,table2.column1,........
from table1
inner join table2
on table1.matching_column=table2.matching_column

ex:
select * from customers as a
inner join orders as o
on a.customer_id = o.customer_id;

select a.customer_id,a.first_name,o.item from customers as a
inner join orders as o
on a.customer_id = o.customer_id

select a.customer_id,a.first_name,o.item from customers as a
join orders as o
on a.customer_id = o.customer_id

------------------------------------------------------------------------------------------------------

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

syntax:
select table1.column1,table1.column1.....,table2.column1,........
from table1
inner join table2
on table1.matching_column=table2.matching_column

ex:
select * from customers 
left join orders 
on customers.customer_id= orders.customer_id;

select customers.customer_id,customers.first_name,orders.item from customers 
left join  orders 
on customers.customer_id = orders.customer_id;

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

select a.customer_id,a.first_name,o.item from customers as a
inner join orders as o
on a.customer_id = o.customer_id;

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

syntax:
select table1.column1,table1.column1.....,table2.column1,........
from table1
inner join table2
on table1.matching_column=table2.matching_column;

exa:

select * from customers as a
right join orders as o
on a.customer_id = o.customer_id
order by o.customer_id;

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

4. Full join :- return all records from both table either matching or not.
notes:-full join not work on mysql database

syntax:
select table1.column1,table1.column1.....,table2.column1,........
from table1
inner join table2
on table1.matching_column=table2.matching_column.

---------------------------------------------------------------------------------------------------------------------------

toaday 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.

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