Question

Using the following schema, write a SQL query to satisfy the question: What are the names of the suppliers of 'Pith_helmet' sold in a department managed by 'Andrew'?

Schema:

Sale (saleno, saleqty, itemno, dname)

Supplier (splno, splname)

Item (itemno, itemname, itemtype, itemcolor)

Department (deptname, deptfloor, deptphone, empno)

Delivery (delno, delqty, itemnum, dptname, splno)

Employee (empno, empfname, empsalary, departname, bossno)

Answer 1

Coding

/* Drop existing table*/
drop table Sale;
drop table Supplier;
drop table Item;
drop table Department;
drop table Delivery;
drop table Employee;
/*Create new table */
create table Sale(saleno number(3), saleqty number(8), itemno number(5), dname varchar(20));
desc Sale;
create table Supplier (splno number(5), splname varchar(20));
desc Supplier;
create table Item (itemno number(3), itemname varchar(10), itemtype varchar(10), itemcolor varchar(10));
desc Item;
create table Department (deptname varchar(20), deptfloor number(2), deptphone number(10), empno number(5));
desc Department;
create table Delivery (delno number(3), delqty number(3), itemnum number(3), dptname varchar(20), splno number(5));
desc Delivery;
create table Employee (empno number(5), empfname varchar(20), empsalary number(10), departname varchar(10), bossno number(3));
desc Employee;

/*What are the names of the suppliers of 'Pith_helmet' sold in a department managed by 'Andrew'?*/

/*In this query we need to join Four table toghtehr Supplier ,Delivery,and Department,and iteam and it will return only splier name with approriate condition as we set */
Select Supplier.splname from Supplier inner join Delivery on Supplier.splno=Delivery.splno inner join Department on Department.deptname=Delivery.dptname inner join Item on Item.itemno=Delivery.itemnum where itemname='Pith_helmet' and Department.empno in(Select empno from Employee where empfname='Andrew');

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........