SQLize | PHPize | SQLtest

-- create CREATE TABLE Devices ( maker VARCHAR2(5), model VARCHAR2(5), types VARCHAR2(10), price number ); -- insert INSERT INTO Devices VALUES ('A', 'S1200', 'Switch', 870); INSERT INTO Devices VALUES ('A', 'S1900', 'Switch', 1600); INSERT INTO Devices VALUES ('A', 'S2800', 'Switch', 4200); INSERT INTO Devices VALUES ('B', 'S2900', 'Switch', 2900); INSERT INTO Devices VALUES ('B', 'S2600', 'Switch', 15000); INSERT INTO Devices VALUES ('B', 'S2000', 'Switch', 2700); INSERT INTO Devices VALUES ('C', 'R700', 'Router', 1200); INSERT INTO Devices VALUES ('C', 'R1600', 'Router', 1700); INSERT INTO Devices VALUES ('C', 'R4000', 'Router', 27000); INSERT INTO Devices VALUES ('C', 'R2500', 'Router', 2800); INSERT INTO Devices VALUES ('D', 'R7000', 'Router', 85000); INSERT INTO Devices VALUES ('D', 'R7500', 'Router', 93000); INSERT INTO Devices VALUES ('E', 'W520', 'Router', 1100); INSERT INTO Devices VALUES ('E', 'W300', 'Router', 1500); INSERT INTO Devices VALUES ('E', 'W500', 'Router', 2000); INSERT INTO Devices VALUES ('E', 'W130', 'Router', 1800); -- create CREATE TABLE Switches ( model VARCHAR2(5), port VARCHAR2(10), num number ); -- insert INSERT INTO Switches VALUES ('S1200', '10BaseT', 8); INSERT INTO Switches VALUES ('S1200', 'FDDI', 1); INSERT INTO Switches VALUES ('S1200', '10BaseFL', 8); INSERT INTO Switches VALUES ('S2900', '10BaseT', 24); INSERT INTO Switches VALUES ('S2900', '100BaseTX', 24); INSERT INTO Switches VALUES ('S2800', '10BaseT', 24); INSERT INTO Switches VALUES ('S2800', '100BaseTX', 8); INSERT INTO Switches VALUES ('S2000', '10BaseT', 8); INSERT INTO Switches VALUES ('S2000', '100BaseTX', 8); INSERT INTO Switches VALUES ('S2600', '10BaseT', 24); INSERT INTO Switches VALUES ('S2600', '100BaseTX', 24); INSERT INTO Switches VALUES ('S2600', '100BaseFX', 2); -- create CREATE TABLE Routers ( model VARCHAR2(5), port VARCHAR2(16), num number, types VARCHAR2(5) ); -- insert INSERT INTO Routers VALUES ('R700', '10BaseT', 4, 'LAN'); INSERT INTO Routers VALUES ('R700', 'E1', 1, 'WAN'); INSERT INTO Routers VALUES ('R700', 'ISDN-BRI', 1, 'LAN'); INSERT INTO Routers VALUES ('R1600', '10BaseT', 1, 'LAN'); INSERT INTO Routers VALUES ('R1600', 'FrameRelay', 1, 'WAN'); INSERT INTO Routers VALUES ('R1600', 'ISDN-BRI', 1, 'WAN'); INSERT INTO Routers VALUES ('R4000', '100BaseFX', 2, 'LAN'); INSERT INTO Routers VALUES ('R4000', 'TokenRing', 2, 'LAN'); INSERT INTO Routers VALUES ('R4000', 'ISDN-BRI', 1, 'WAN'); INSERT INTO Routers VALUES ('R4000', 'X.25', 1, 'WAN'); INSERT INTO Routers VALUES ('R2500', 'TokenRing', 1, 'LAN'); INSERT INTO Routers VALUES ('R2500', 'FrameRelay', 2, 'WAN'); INSERT INTO Routers VALUES ('R7000', '100BaseFX', 2, 'LAN'); INSERT INTO Routers VALUES ('R7000', 'FDDI', 1, 'WAN'); INSERT INTO Routers VALUES ('R7000', 'ATM', 1, 'WAN'); INSERT INTO Routers VALUES ('R7500', '100BaseFX', 6, 'LAN'); INSERT INTO Routers VALUES ('R7500', 'FDDI', 1, 'LAN'); INSERT INTO Routers VALUES ('R7500', 'ATM', 1, 'WAN'); INSERT INTO Routers VALUES ('R7500', 'ISDN-BRI', 2, 'WAN'); INSERT INTO Routers VALUES ('W520', 'Antenna', 1, 'LAN'); INSERT INTO Routers VALUES ('W520', '100BaseTX', 4, 'LAN'); INSERT INTO Routers VALUES ('W520', 'FDDI', 1, 'WAN'); INSERT INTO Routers VALUES ('W300', 'Antenna', 1, 'LAN'); INSERT INTO Routers VALUES ('W300', '100BaseTX', 8, 'LAN'); INSERT INTO Routers VALUES ('W300', 'FrameRelay', 1, 'WAN'); INSERT INTO Routers VALUES ('W500', 'Antenna', 1, 'LAN'); INSERT INTO Routers VALUES ('W500', '100BaseTX', 4, 'LAN'); INSERT INTO Routers VALUES ('W500', 'FDDI', 1, 'WAN'); -- create CREATE TABLE WAN ( port VARCHAR2(16), standard VARCHAR2(20), bitRate number, cannels number ); -- insert INSERT INTO WAN VALUES ('ISDN-BRI', 'CCITT', 64, 2); INSERT INTO WAN VALUES ('X.25', 'CCITT', 64, 0); INSERT INTO WAN VALUES ('FrameRelay', 'Frame Relay Forum', 56, 27); INSERT INTO WAN VALUES ('ATM', 'ATM Forum', 155520, 4); INSERT INTO WAN VALUES ('E1', '', 64, 30); -- create CREATE TABLE LAN ( standard VARCHAR2(20), port VARCHAR2(20), bitRate number, busType VARCHAR2(10), length number ); -- insert INSERT INTO LAN VALUES ('IEEE802.3', '10BaseT', 10, 'UTP', 100); INSERT INTO LAN VALUES ('IEEE802.3', '100BaseTX', 100, 'UTP', 100); INSERT INTO LAN VALUES ('IEEE802.3', '100BaseFX', 100, 'Fiber', 2000); INSERT INTO LAN VALUES ('IEEE802.3', '10BaseFL', 10, 'Fiber', 2000); INSERT INTO LAN VALUES ('IEEE802.3', '100BaseFX', 100, 'Fiber', 2000); INSERT INTO LAN VALUES ('FDDI', 'FDDI', 10, 'Fiber', 2000); INSERT INTO LAN VALUES ('IEEE802.5', 'TokenRing', 100, 'UTP', 100); INSERT INTO LAN VALUES ('IEEE802.11b', 'Antenna', 11, 'wireless', 600); INSERT INTO LAN VALUES ('IEEE802.11g', 'Antenna', 54, 'wireless', 100); INSERT INTO LAN VALUES ('IEEE802.11g', 'Antenna', 54, 'wireless', 100); --1 DECLARE CURSOR cur_switches IS SELECT D.model, D.price, D.type_, S.num FROM Devices D JOIN Switches S ON D.model = S.model WHERE D.type_ = 'Switch'; v_model Devices.model%TYPE; v_price Devices.price%TYPE; v_device_type Devices.type_%TYPE; v_num Switches.num%TYPE; BEGIN OPEN cur_switches; LOOP FETCH cur_switches INTO v_model, v_price, v_device_type, v_num; EXIT WHEN cur_switches%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Model: ' || v_model || ', Price: ' || v_price || ', Type: ' || v_device_type || ', Ports: ' || v_num); END LOOP; CLOSE cur_switches; END;