SQLize Online / PHPize Online  /  SQLtest Online

--создаем таблицы для тестового задания -- create CREATE TABLE JT$Calendar ( Cal_Day DATE NOT NULL --календ. дата ); CREATE TABLE JT$Emp( Empno NUMBER NOT NULL, EName VARCHAR2(80) NOT NULL, Job VARCHAR2(30) NOT NULL, Mgr NUMBER, Hiredate DATE NOT NULL, Sal NUMBER(10,2) NOT NULL, Comm NUMBER(10,2), Deptno NUMBER NOT NULL ); CREATE TABLE JT$Operations( Operation_id NUMBER NOT NULL, Account_id NUMBER NOT NULL, Operation_Type VARCHAR2(1) NOT NULL, Operation_Date DATE NOT NULL, Agreement_Num VARCHAR2(20), Amount NUMBER(20,2) NOT NULL, CONSTRAINT CH_OPER CHECK(Operation_Type IN ('D','C')) ); CREATE TABLE JT$Saldo( Customer_id NUMBER NOT NULL, Account_id NUMBER NOT NULL, Beg_Date DATE NOT NULL, End_Date DATE NOT NULL, Out_Saldo NUMBER(20,2) NOT NULL ); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('01.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('02.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('03.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('04.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('05.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('06.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('27.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('28.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('29.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('30.01.2009','DD.MM.YYYY')); INSERT INTO JT$Calendar (Cal_Day) VALUES(TO_DATE('31.01.2009','DD.MM.YYYY')); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7369, 'smith', 'clerk', 7902, TO_DATE('17.12.2007', 'DD.MM.YYYY'), 800.00, NULL, 20); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7499, 'allen', 'salesman', 7698, TO_DATE('20.02.2005', 'DD.MM.YYYY'), 1600.00, 300.00, 30); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7521, 'ward', 'salesman', 7698, TO_DATE('22.01.2009', 'DD.MM.YYYY'), 1250.00, 500.00, 30); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7566, 'jones', 'manager', 7839, TO_DATE('02.04.2006', 'DD.MM.YYYY'), 2975.00, NULL, 20); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7654, 'martin', 'salesman', 7698, TO_DATE('28.09.2008', 'DD.MM.YYYY'), 1250.00, 1400.00, 30); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7698, 'blake', 'manager', 7839, TO_DATE('15.01.2009', 'DD.MM.YYYY'), 2850.00, NULL, 30); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7782, 'clark', 'manager', 7839, TO_DATE('09.06.2000', 'DD.MM.YYYY'), 2450.00, NULL, 10); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7839, 'king', 'president', NULL, TO_DATE('17.11.2005', 'DD.MM.YYYY'), 5000.00, NULL, 10); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7844, 'turner', 'salesman', 7698, TO_DATE('11.01.2009', 'DD.MM.YYYY'), 1500.00, 0.00, 30); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7900, 'james', 'clerk', 7844, TO_DATE('03.12.2001', 'DD.MM.YYYY'), 950.00, NULL, 30); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7902, 'ford', 'analyst', 7566, TO_DATE('11.01.2009', 'DD.MM.YYYY'), 3000.00, NULL, 20); INSERT INTO JT$Emp (Empno, EName, Job, Mgr, Hiredate, Sal, Comm, Deptno) VALUES (7934, 'miller', 'clerk', 7782, TO_DATE('23.01.2004', 'DD.MM.YYYY'), 1300.00, NULL, 10); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (1, 1, 'c', TO_DATE('01.01.2009', 'DD.MM.YYYY'), NULL, 100.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (2, 1, 'c', TO_DATE('01.01.2009', 'DD.MM.YYYY'), '01-11a', 230.00); INSERT INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (3, 1, 'c', TO_DATE('01.01.2009', 'DD.MM.YYYY'), '01-11b', 350.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (4, 1, 'd', TO_DATE('01.01.2009', 'DD.MM.YYYY'), NULL, 100.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (5, 1, 'd', TO_DATE('01.01.2009', 'DD.MM.YYYY'), NULL, 100.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (6, 1, 'd', TO_DATE('01.01.2009', 'DD.MM.YYYY'), '01-11a', 150.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (7, 1, 'd', TO_DATE('01.01.2009', 'DD.MM.YYYY'), '01-11a', 150.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (8, 1, 'c', TO_DATE('01.01.2009', 'DD.MM.YYYY'), NULL, 500.00); INSERT INTO JT$Operations (operation_id, account_id, operation_type, operation_date, agreement_num, amount) VALUES (9, 1, 'c', TO_DATE('01.01.2009', 'DD.MM.YYYY'), NULL, 327.20); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 1, TO_DATE('01.12.2008', 'DD.MM.YYYY'), TO_DATE('19.12.2008', 'DD.MM.YYYY'), 100.00); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 1, TO_DATE('20.12.2008', 'DD.MM.YYYY'), TO_DATE('21.12.2008', 'DD.MM.YYYY'), 180.00); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 1, TO_DATE('22.12.2008', 'DD.MM.YYYY'), TO_DATE('31.12.2008', 'DD.MM.YYYY'), 83.50); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 1, TO_DATE('01.01.2009', 'DD.MM.YYYY'), TO_DATE('09.01.2009', 'DD.MM.YYYY'), -923.70); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 1, TO_DATE('10.01.2009', 'DD.MM.YYYY'), TO_DATE('31.12.9999', 'DD.MM.YYYY'), 0.32); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 2, TO_DATE('08.12.2008', 'DD.MM.YYYY'), TO_DATE('19.12.2008', 'DD.MM.YYYY'), 10.00); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 2, TO_DATE('20.12.2008', 'DD.MM.YYYY'), TO_DATE('20.12.2008', 'DD.MM.YYYY'), 120.00); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 2, TO_DATE('21.12.2008', 'DD.MM.YYYY'), TO_DATE('04.01.2009', 'DD.MM.YYYY'), 63.10); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 2, TO_DATE('05.01.2009', 'DD.MM.YYYY'), TO_DATE('24.01.2009', 'DD.MM.YYYY'), 1200.00); INSERT INTO JT$Saldo (customer_id, account_id, beg_date, end_date, out_saldo) VALUES (1, 2, TO_DATE('25.01.2009', 'DD.MM.YYYY'), TO_DATE('31.12.9999', 'DD.MM.YYYY'), 1003.31); SELECT * FROM DUAL; commit;