SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE korxona ( korxona_id NUMBER PRIMARY KEY, korxona_name VARCHAR2(100) NOT NULL, location VARCHAR2(100) NOT NULL ); -- Xodimlar jadvalini yaratish CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, phone_number VARCHAR2(20), address VARCHAR2(255), salary NUMBER(10, 2) NOT NULL, hire_date DATE NOT NULL, korxona_id NUMBER, CONSTRAINT fk_korxona FOREIGN KEY (korxona_id) REFERENCES korxona(korxona_id) ); -- Korxona ID uchun Sequence yaratish CREATE SEQUENCE korxona_seq START WITH 1 INCREMENT BY 1 NOCACHE; -- Xodimlar ID uchun Sequence yaratish CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1 NOCACHE; -- Korxona ID uchun Trigger yaratish CREATE OR REPLACE TRIGGER korxona_trigger BEFORE INSERT ON korxona FOR EACH ROW BEGIN SELECT korxona_seq.NEXTVAL INTO :NEW.korxona_id FROM dual; END; / -- Xodimlar ID uchun Trigger yaratish CREATE OR REPLACE TRIGGER employee_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual; END; / -- Korxona ma'lumotlarini kiritish INSERT INTO korxona (korxona_name, location) VALUES ('Xizmat Korxonasi', 'Toshkent'); INSERT INTO korxona (korxona_name, location) VALUES ('Texnika Korxonasi', 'Samarqand'); INSERT INTO korxona (korxona_name, location) VALUES ('Qurilish Korxonasi', 'Buxoro'); -- Xodimlar ma'lumotlarini kiritish INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Ali', 'Tursunov', '998901234567', 'Toshkent, Yunusobod', 3500000, TO_DATE('2022-05-01', 'YYYY-MM-DD'), 1); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Layla', 'Xamidova', '998907654321', 'Samarqand, Bo\'zoroq', 4000000, TO_DATE('2021-03-15', 'YYYY-MM-DD'), 2); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Farhod', 'Yusufov', '998936543210', 'Buxoro, Sho\'rchi', 3000000, TO_DATE('2023-06-10', 'YYYY-MM-DD'), 3); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Zarina', 'Mahmudova', '998904567890', 'Toshkent, Chilonzor', 3200000, TO_DATE('2021-08-20', 'YYYY-MM-DD'), 1); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Javlon', 'Murodov', '998901234567', 'Samarqand, Navoi', 2800000, TO_DATE('2022-11-25', 'YYYY-MM-DD'), 2); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Nodira', 'Ergasheva', '998923456789', 'Buxoro, G\'ijduvon', 3100000, TO_DATE('2022-02-17', 'YYYY-MM-DD'), 3); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Anvar', 'Shamsiev', '998939876543', 'Toshkent, Sergeli', 3900000, TO_DATE('2020-07-30', 'YYYY-MM-DD'), 1); INSERT INTO employees (first_name, last_name, phone_number, address, salary, hire_date, korxona_id) VALUES ('Omina', 'Rustamova', '998905678901', 'Samarqand, Jarqishloq', 3600000, TO_DATE('2022-09-01', 'YYYY-MM-DD'), 2); -- Xodimlar jadvalini ko'rish SELECT * FROM employees; -- Xizmat Korxonasi xodimlarini tanlash (Ismi, Familiya, Maoshi) SELECT e.first_name, e.last_name, e.salary FROM employees e JOIN korxona k ON e.korxona_id = k.korxona_id WHERE k.korxona_name = 'Xizmat Korxonasi'; -- Maosh bo'yicha xodimlarni saralash SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC; -- Tranzaksiyani sinash BEGIN; -- Xodimning maoshini yangilash UPDATE employees SET salary = 5000000 WHERE employee_id = 1; -- SAVEPOINT qo'yish SAVEPOINT update_salary; -- Xodimning maoshini yana yangilash UPDATE employees SET salary = 4500000 WHERE employee_id = 2; -- Agar xatolik bo'lsa, rollback qilish ROLLBACK TO update_salary; -- Tranzaksiyani tasdiqlash COMMIT;