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; -- 2 DECLARE CURSOR cur_routers IS SELECT D.maker, D.model, D.type_, R.port FROM Devices D JOIN Routers R ON D.model = R.model WHERE D.type_ = 'Router'; v_router cur_routers%ROWTYPE; BEGIN OPEN cur_routers; LOOP FETCH cur_routers INTO v_router; EXIT WHEN cur_routers%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Maker: ' || v_router.maker || ', Model: ' || v_router.model || ', Type: ' || v_router.type_ || ', Port Speed: ' || v_router.port); END LOOP; CLOSE cur_routers; END; --3 DECLARE CURSOR cur_expensive_switches IS SELECT D.model, D.price, S.num FROM Devices D JOIN Switches S ON D.model = S.model WHERE D.type_ = 'Switch' AND D.price > 4000; BEGIN FOR switch_record IN cur_expensive_switches LOOP DBMS_OUTPUT.PUT_LINE('Model: ' || switch_record.model || ', Price: ' || switch_record.price || ', Ports: ' || switch_record.num); END LOOP; END; --4 DECLARE CURSOR cur_routers_with_price(p_price NUMBER) IS SELECT D.model, D.type_, R.num, R.port FROM Devices D JOIN Routers R ON D.model = R.model WHERE D.type_= 'Router' AND D.price > p_price; v_router cur_routers_with_price%ROWTYPE; BEGIN OPEN cur_routers_with_price(8000); -- Укажите нужный параметр: 8000, 1000, 2000 LOOP FETCH cur_routers_with_price INTO v_router; EXIT WHEN cur_routers_with_price%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Model: ' || v_router.model || ', Type: ' || v_router.type_ || ', Ports: ' || v_router.num || ', Port Speed: ' || v_router.port); END LOOP; CLOSE cur_routers_with_price; END; --5 DECLARE CURSOR switch_maker_cursor IS SELECT DISTINCT maker FROM Devices WHERE type_= 'Switch'; BEGIN FOR maker_row IN switch_maker_cursor LOOP DBMS_OUTPUT.PUT_LINE('Maker: ' || maker_row.maker); END LOOP; END;