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.
4. The faculty table contains faculty information and has a faculty_id as the primary key. The faculty_department table has the department assignments for faculty members and has the faculty_id from the faculty table as an exported key or foreign key. The department table has information about departments and has a department_id column as its primary key. The faculty_department table also has the exported key or foreign key for the department table.

Write a SQL statement to display faculty_id, first name, last name, department_id, department name (need to joins the faculty table with the faculty_department table with the department table. )Use an inner join for this query.

Answer 1

HI,

Below are SQL queries for you given statements:

SQL1:

SELECT s.supplier_name, p.part_name
FROM supplier s
INNER JOIN parts p ON s.supplier_id = p.supplier_id;

SQL2: I am using SQlite for the query and SQLite does not support outer join so i write the query like below for outer join..i write both type of queries one for SQlite and another other DB's

For other DB's:

SELECT  s.supplier_name, p.part_name from supplier s OUTER JOIN parts p WHERE
s.supplier_id = p.supplier_id

For SQlite:

SELECT s.supplier_name,
         p.part_name
         
FROM supplier s
LEFT JOIN parts p USING(supplier_id)
UNION ALL
SELECT s.supplier_name,
         p.part_name
FROM parts p
LEFT JOIN supplier s USING(supplier_id)
WHERE s.supplier_id IS NULL;

SQL3:

SELECT s.supplier_name, p.part_no, p.part_name
FROM supplier s
INNER JOIN parts p ON s.supplier_id = p.supplier_id WHERE s.supplier_name="Fred Smith"

SQL4:

SELECT fac.faculty_id, fac.faculty_fname, fac.faculty_lname, d.department_id, d.department_name
from faculty fac
INNER JOIN faculty_department fd ON fac.faculty_id = fd.faculty_id 
INNER JOIN department d ON d.department_id = fd.department_id

Thanks