Based on table below, using oracle: used subqueries,display the supplier that supplied the most computers. CREATE TABLE supplier (SuppID NUMBER(3), SName VARCHAR2(15), SAddress VARCHAR2(20), SPhone VARCHAR2(12), CONSTRAINT supplier_suppID_PK PRIMARY KEY (suppID)); CREATE TABLE location (LocID NUMBER(3), Bldg_code CHAR(4), RoomNo NUMBER(3), CONSTRAINT location_locid_PK PRIMARY KEY (LocID)); CREATE TABLE computer (CompID NUMBER(5), SNumber VARCHAR2(15), Make VARCHAR2(20), SuppID NUMBER(3), LocID NUMBER(3), Value NUMBER(6,2), Date_purchased DATE, CONSTRAINT computer_compID_PK PRIMARY KEY (compID), CONSTRAINT computer_suppID_FK FOREIGN KEY (suppID) REFERENCES supplier, CONSTRAINT computer_locID_FK FOREIGN KEY (locID) REFERENCES location); INSERT INTO supplier VALUES (122, 'Compaq Sdn.Bhd', '10 Jalan Duta', '03-989898'); INSERT INTO supplier VALUES (123, 'ASUS Sdn.Bhd', '12 Jalan Duta', '03-232323'); INSERT INTO supplier VALUES (124, 'IBM Sdn.Bhd', '15 Jalan Duta', '03-565656'); INSERT INTO supplier VALUES (125, 'Apple Sdn.Bhd', '18 Jalan Duta', '03-785676'); INSERT INTO location VALUES (222, 'LAW', 343); INSERT INTO location VALUES (333, 'IRK', 343); INSERT INTO location VALUES (444, 'ICT', 343); INSERT INTO computer VALUES (13321, 'XDX12345', 'Compaq', 122, 222, 2300.50, TO_DATE('12-JAN-2008', 'DD-MON-YYYY')); INSERT INTO computer VALUES (13322, 'XDX12344', 'ASUS PC', 123, 222, 3000.80, TO_DATE('17-JAN-2008', 'DD-MON-YYYY')); INSERT INTO computer VALUES (13323, 'XDX12343', 'IBM', 124, 333, 2800.50, TO_DATE('12-DEC-2008', 'DD-MON-YYYY')); INSERT INTO computer VALUES (13324, 'XDX12342', 'Apple', 125, 444, 5300.50, TO_DATE('12-JUN-2009', 'DD-MON-YYYY')); INSERT INTO computer VALUES (13325, 'XDX12341', 'Apple', 125, 444, 6300.50, TO_DATE('12-JUN-2009', 'DD-MON-YYYY')); INSERT INTO computer VALUES (13326, 'XDX12346', 'Apple', 125, 444, 6000.85, TO_DATE('12-JUN-2009', 'DD-MON-YYYY')); COMMIT;
Based on table below, using oracle:
used subqueries,display the supplier that supplied the most computers.
CREATE TABLE supplier
(SuppID NUMBER(3),
SName VARCHAR2(15),
SAddress VARCHAR2(20),
SPhone VARCHAR2(12),
CONSTRAINT supplier_suppID_PK PRIMARY KEY (suppID));
CREATE TABLE location
(LocID NUMBER(3),
Bldg_code CHAR(4),
RoomNo NUMBER(3),
CONSTRAINT location_locid_PK PRIMARY KEY (LocID));
CREATE TABLE computer
(CompID NUMBER(5),
SNumber VARCHAR2(15),
Make VARCHAR2(20),
SuppID NUMBER(3),
LocID NUMBER(3),
Value NUMBER(6,2),
Date_purchased DATE,
CONSTRAINT computer_compID_PK PRIMARY KEY (compID),
CONSTRAINT computer_suppID_FK FOREIGN KEY (suppID) REFERENCES supplier,
CONSTRAINT computer_locID_FK FOREIGN KEY (locID) REFERENCES location);
INSERT INTO supplier VALUES (122, 'Compaq Sdn.Bhd', '10 Jalan Duta', '03-989898');
INSERT INTO supplier VALUES (123, 'ASUS Sdn.Bhd', '12 Jalan Duta', '03-232323');
INSERT INTO supplier VALUES (124, 'IBM Sdn.Bhd', '15 Jalan Duta', '03-565656');
INSERT INTO supplier VALUES (125, 'Apple Sdn.Bhd', '18 Jalan Duta', '03-785676');
INSERT INTO location VALUES (222, 'LAW', 343);
INSERT INTO location VALUES (333, 'IRK', 343);
INSERT INTO location VALUES (444, 'ICT', 343);
INSERT INTO computer VALUES (13321, 'XDX12345', 'Compaq', 122, 222, 2300.50, TO_DATE('12-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO computer VALUES (13322, 'XDX12344', 'ASUS PC', 123, 222, 3000.80, TO_DATE('17-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO computer VALUES (13323, 'XDX12343', 'IBM', 124, 333, 2800.50, TO_DATE('12-DEC-2008', 'DD-MON-YYYY'));
INSERT INTO computer VALUES (13324, 'XDX12342', 'Apple', 125, 444, 5300.50, TO_DATE('12-JUN-2009', 'DD-MON-YYYY'));
INSERT INTO computer VALUES (13325, 'XDX12341', 'Apple', 125, 444, 6300.50, TO_DATE('12-JUN-2009', 'DD-MON-YYYY'));
INSERT INTO computer VALUES (13326, 'XDX12346', 'Apple', 125, 444, 6000.85, TO_DATE('12-JUN-2009', 'DD-MON-YYYY'));
COMMIT;
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 2 images