Question

1. Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query.

2. Write a SQL statement which joins the parts table with the suppliers table and lists the part_name, supplier_name. You should return all rows from the parts table whether or not there are corresponding rows in the supplier table. You should use an outer join for this query.

3. Write a SQL statement which joins the parts table with the supplier table and lists the part_no, part_name, supplier_name from parts and suppliers table. Only return the parts supplied by ‘Fred Smith ..'. You should use an inner join for this query.

Answer 1

Fo this question we have following two tables:-

1> supplier table (parent table):-

2> parts tabble (Child table) :-

1>We have to fetch the part_name and supplier_name from part table and supplier table using inner join query for first SQL statement. Now we understand how to fetch the above data step by step using inner join.

STEP 1 : First select the part_name from the parts table.

Query :- SELECT part_name from parts;

STEP 2 :then select the supplier_name from the supplier table.

Query :- SELECT supplier_name from supplier.

STEP 3:- Now merge above two queries using inner join and fetch data according to the supplier_id.

Final Query:-

SELECT P.part_name, S.supplier_name from parts as P inner join supplier as S on S.supplier_id = P.supplier_id;

Output Of First SQL statement:-

From above output, we can see that we get the part_name and supplier_name from both the table where part.supplier_id = supplier.supplier_id.

2> For second SQL statement we need to fetch all the parts table whether or not there are corresponding rows in the supplier table. SO here we can used left join which is type of Outer join.

Now, we apply left join on supplier table so we can get the all data of parts table because parts table contain supplier_id as foreign key and the data that are not matched in pat table we get the NULL data int that rows.

STEP 1 : First select the part_name from the parts table.

Query :- SELECT part_name from parts;

STEP 2 :then select the supplier_name from the supplier table.

Query :- SELECT supplier_name from supplier.

STEP 3: Now merge STEP1 and STEP 2 query using left join which is type of outer join.

Final Query:-

SELECT P.part_name, S.supplier_name from supplier as S left join parts as P on S.supplier_id = P.supplier_id;

Output Of Second SQL statement:-

From the above output, we can see that we get all the part data using left join because we have foriegn key supplier_id and we get the null data in part_name column because that are does not match with supplier_id.

Because "Sagar Naik" supplier does not suuply any part so parts table does not have any record so we get the null data after left join.

3> For third SQL statement, we need to fetch the part_no, part_name and supplier_name from the parts and supplier table using ineer join that are used in first SQL statement and here we have to fetch data for the "Fred Smith" supplier name. So we need to use WHERE clause.

STEP 1 : First select the part_no,part_name from the parts table.

Query :- SELECT part_no,part_name from parts;

STEP 2 : then select the supplier_name "Fred Smith" from the supplier table using WHERE clause.

Query :- SELECT supplier_name from supplier where supplier_name='Fred Smith'.

STEP 3: Now merge STEP1 and STEP 2 query using left join which is type of outer join.

Final Query:-

SELECT P.part_no,P.part_name, S.supplier_name from parts as P inner join supplier as S on S.supplier_id = P.supplier_id WHERE S.supplier_name = 'Fred Smith';

Output Of Third SQL statement:-

From the above output, we can see that we get the all parts number and name that are supplied by "Fred Smith" supplier.

I hope you will understand above queries and the concept of inner join with WHERE clause as well as outer join.

Do you feel needful and useful then please upvote me.

Thank you.