Введение в Oracle SQL - ответы

1 SELECT * 2 FROM students s, classes c 3 WHERE s.id = c.student_id 4 AND c.category IN 5 (SELECT type FROM courses WHERE level > 3) 6 ORDER BY s.last_name, c.number;

INSERT INTO inventory (invid, invname) VALUES (12345670, 'Milk powder'); INSERT INTO inventory (invid, invname) VALUES (12345671, 'Milk cream'); SAVEPOINT   a;INSERT INTO inventory (invid, invname) VALUES (12345672, 'Buttermilk');SAVEPOINT   b;UPDATE inventory SET invid = 12345679 WHERE invid = 12345670; UPDATE inventory SET invid = 12345670 WHERE invid = 12345671; UPDATE inventory SET invid = 12345677 WHERE invid = 12345670; ROLLBACK TO SAVEPOINT b;UPDATE  inventory SET invid = 12345676 WHERE invid = 12345672; COMMIT;

CREATE TYPE children_list IS VARRAY(10) OF relative_type;

SELECT UID FROM DUAL;

SELECT id, last_name, title,NVL2(commission_pct,20,5) "Комисионный процент"    FROM s_emp;

SELECT last_name, high_grade, RANK() OVER (ORDER BY high_grade DESC) AS "Analytic function"FROM math_101;

SELECT * FROM vtable FOR UPDATE WAIT(10);

SELECT NVL(MAX(sal),0) FROM emp WHERE ename = 'Blake' AND deptno = 10;

REVOKE updateON employeeFROM Sonya; 

ALTER USER Colin IDENTIFIED BY Wvu88Stud; 

RENAME streets TO city;

1 CREATE TABLE alpha_30002 (3000_id NUMBER(9),3  CONSTRAINT alpha_3000_id_pk PRIMARY KEY,4  name VARCHAR2(25),5  title VARCHAR2(25),6  idname VARCHAR2(25) NOT NULL);

SELECT last_name, (SELECT name FROM s_dept WHERE s_dept.id=s_emp.dept_id) dept_nameFROM s_emp;

1 SELECT bus_name, profits 2 FROM business 3 WHERE city = 4 	      (SELECT city FROM locations 5 	       WHERE city LIKE 'Alban%' 6 	       AND state = 'NY') 7 ORDER BY profits;

SELECT emp id, name FROM employees WHERE emp_num >= 1 AND emp_num <= 100;

SELECT name FROM employee WHERE name LIKE '_a%';

SELECT COUNT(DISTINCT name) FROM animals WHERE family = ANY (SELECT family 		    FROM blood_types 		    WHERE blood_type LIKE ('warm%'));

SELECT MAX(a), MIN(b), MAX(C) FROM COMBOS;

SELECT REGR_AVGX(high_grade, low_grade) AS "Analytic function", REGR_AVGY(high_grade, low_grade) AS "Analytic function", REGR_SLOPE(high_grade, low_grade) AS "Analytic function"FROM math_101;

SELECT t.type, SUM(t.price) FROM tools t GROUP BY t.type HAVING SUM(t.price) <= (SELECT AVG(price) 			FROM tools 			WHERE type IN (SELECT type 				       FROM tool_types 				       WHERE usage = 'Often')); 

SELECT type, AVG(price), MIN(price) FROM product GROUP BY category; 

1 SELECT city, state, COUNT(*) 2 FROM customers 3 GROUP BY city, state 4 UNION ALL 5 SELECT city, state, COUNT(*) 6 FROM employees 7 GROUP BY city, state;

SELECT dept_no, AVG(MONTHS_BETWEEN(SYSDATE, hiredate)) FROM empWHERE AVG(MONTHS_BETWEEN(SYSDATE, hiredate)) > 60GROUP BY dept_no ORDER BY AVG(MONTHS_BETWEEN(SYSDATE, hiredate));

SELECT flavor, SUM(ice_cream_sales) FROM sales_detail GROUP BY flavor ORDER BY 2 DESC;

SELECT RPAD(last_name, LEVEL,'_') "Level of subordination"FROM s_empCONNECT BY PRIOR id = manager_idSTART WITH manager_id IS NULL;

SELECT id, name FROM s_empUNIONSELECT id, last_name FROM s_dept;

SELECT a, b, SUM(c) FROM vtableGROUP BY CUBE(a,b));

SELECT LEVEL, RPAD(last_name, LEVEL,'_') "Level of subordination"FROM s_empCONNECT BY PRIOR id = manager_idSTART WITH manager_id IS NULLORDER SIBLINGS BY last_name;

SELECT a.family, a.nameFROM animals a INNER JOIN blood_types t ON (a.family = t.family)WHERE a.family NOT IN ('fish', 'bird');

SELECT p.name, e.emp_id, j.description

SELECT name, salary, dept_no FROM employeeWHERE salary > (SELECT AVG(salary)		FROM employee;		WHERE dept_no = (SELECT dept_no				 FROM employee				 WHERE last_name = (SELECT last_name						    FROM employee				    		    WHERE salary > 50000)));

WITH subordination (id, job) AS (SELECT id, title FROM s_emp WHERE title = 'President'UNION ALLSELECT s.job||' -> '||e.titleFROM s_emp e INNER JOIN subordination s             ON s.id = e.manager_id)SELECT job FROM subordination;

SELECT a.family, a.nameFROM animals a, blood_types t WHERE a.family = t.family AND a.family NOT IN ('fish', 'bird');

VARIABLE sale_id NUMBER(9)

INSERT INTO inventory (invid, invname) VALUES (12345670, 'Milk powder'); INSERT INTO inventory (invid, invname) VALUES (12345671, 'Milk cream'); INSERT INTO inventory (invid, invname) VALUES (12345672, 'Buttermilk');UPDATE inventory SET invid = 12345679 WHERE invid = 12345670; UPDATE inventory SET invid = 12345670 WHERE invid = 12345671; UPDATE inventory SET invid = 12345677 WHERE invid = 12345670; COMMIT;UPDATE  inventory SET invid = 12345676 WHERE invid = 12345672; ROLLBACK;

MERGE INTO doctors d, physicians p WHEN MATCHED THENDELETE WHERE house_calls = 'N' AND          d.doc_id = p.phys_id;

FLASHBACK TABLE s_empTO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);

INSERT INTO sale (id, sale_date)SELECT ...;

CREATE VIEW CustomerItemSummary AS SELECT i.year, i.month, c.custno, c.name, d.item, SUM(d.extended) YTDPurchased FROM customer c, invoice i, invoice_detail d WHERE c.custno = i.custno AND i.invno = d.invno GROUP BY i.year, i.month, c.custno, c.name, d.item;

SELECT constraint_name, constraint_typeFROM USER_CONSTRAINTSWHERE table_name = 'S_EMP';

CREATE OR REPLACE VIEW emp_info AS SELECT last_name, first_name, title, start_date, dept_id FROM employeeWHERE dept_id = 30;

CREATE TABLE descriptions (relation REF relative_type SCOPE IS relatives,description VARCHAR2(100));

CREATE TYPE relative_type AS OBJECT (first_name VARCHAR2(30),last_name VARCHAR2(30),birth_date DATE-- дата рождения);

CREATE TABLE relatives OF relative_type;

SELECT EMP_ID_SEQ.CURRVAL FROM dual;

CREATE INDEX myindex ON vtable (a,b); 

CREATE GLOBAL TEMPORARY TABLE temp_empAS SELECT * FROM emp;

GRANT CREATE ANY EDITION, DROP ANY EDITION TO u01;

CREATE TABLE vtable (a NUMBER(3), b VARCHAR2(10));

INSERT INTO my_table (id, name) VALUES (1, 'Mike'); SAVEPOINT   a;INSERT INTO my_table (id, name) VALUES (2, 'Karen'); SAVEPOINT   b;INSERT INTO my_table (id, name) VALUES (3, 'Tracy');SAVEPOINT   c;INSERT INTO my_table (id, name) VALUES (4, 'Sam');INSERT INTO my_table (id, name) VALUES (5, 'Elrod');ALTER TABLE my_table ADD last_name(VARCHAR2(15));ROLLBACK;

UPDATE Scott.vtable SET Column2 = 'c' WHERE Column1 = 1;

SELECT * FROM s_empWHERE manager_id IS NULL AND      title = 'President' AND      salary > 3000 ORDER BY last_name;

SELECT * FROM s_region, s_dept, s_emp, s_customer;

SELECT SUM(...) OVER () FROM ...;

INTERVAL '12' MONTH

SELECT last_name, title, start_date,AVG(salary) OVER (ORDER BY start_date RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW) avg_sal FROM s_emp;

SELECT manager_id, MIN(salary) min_sal, COUNT(id) emp_countFROM s_empWHERE manager_id IS NOT NULLGROUP BY manager_idHAVING min_sal > 1000;

SELECT id, last_name, title,CASE WHEN title='President' THEN '20% BONUS', WHEN title='Vice President' THEN '10% BONUS' WHEN title='Sales Representative' THEN '30% BONUS'ELSE '5% BONUS' END "Премии (в %)"    FROM s_emp;

INSERT INTO inventory (invid, invname) VALUES (12345670, 'Milk powder'); INSERT INTO inventory (invid, invname) VALUES (12345671, 'Milk cream'); INSERT INTO inventory (invid, invname) VALUES (12345672, 'Buttermilk');UPDATE inventory SET invid = 12345679 WHERE invid = 12345670; UPDATE inventory SET invid = 12345670 WHERE invid = 12345671; UPDATE inventory SET invid = 12345677 WHERE invid = 12345670; ROLLBACK;UPDATE  inventory SET invid = 12345676 WHERE invid = 12345672; COMMIT;

SELECT last_name, title, dept_id,SUM(salary) OVER (PARTITION BY title, dept_id) sum_sal FROM s_emp;

DELETE FROM sale WHERE id = (SELECT ...);

DELETE FROM doctors d, physicians p WHERE d.doc_id = p.phys_id AND   d.house_calls = 'N' AND   p.fee > (SELECT MAX(gnp) FROM countries WHERE world = 3);

SELECT name, salary, dept_no FROM employeeWHERE salary > (SELECT AVG(salary)		FROM employee;		WHERE last_name = (SELECT last_name				   FROM employee				   WHERE dept_no IN (SELECT dept_no						     FROM employee				    		     WHERE salary > 50000)));

SELECT * FROM (SELECT title, dept_id FROM emp)PIVOT (COUNT(*) FOR dept_id IN (11, 21, 31, 41));

SELECT a.family, a.nameFROM animals a, blood_types t WHERE a.family(+) = t.family AND a.family NOT IN ('fish', 'bird');

SELECT id, last_name, title,DECODE (title, 'President', 30,                'Vice President', 20, 	       'Sales Representative', 10, 5) "Премии (в %)"    FROM s_emp;

INSERT INTO inventory (invid, invname) VALUES (12345670, 'Milk powder'); INSERT INTO inventory (invid, invname) VALUES (12345671, 'Milk cream'); SAVEPOINT   a;INSERT INTO inventory (invid, invname) VALUES (12345672, 'Buttermilk');SAVEPOINT   b;UPDATE inventory SET invid = 12345679 WHERE invid = 12345670; UPDATE inventory SET invid = 12345670 WHERE invid = 12345671; UPDATE inventory SET invid = 12345677 WHERE invid = 12345670; COMMIT;UPDATE  inventory SET invid = 12345676 WHERE invid = 12345672; ROLLBACK;

SELECT department, COUNT(*) FROM customers WHERE gender LIKE '%male' AND purchases > 30.00 GROUP BY department ORDER BY 1;

ALTER USER Sonya IDENTIFIED BY Wvu88; 

1 CREATE TABLE sale2 (purchase_no NUMBER(9),3  customer_no NUMBER(9)4 	CONSTRAINT sale_customer_id_fk REFERENCES customer(id),5  CONSTRAINT sale_purchase_no_pk PRIMARY KEY (purchase_no),6  CASCADE CONSTRAINTS);

SELECT id, last_name, NVL(TO_CHAR(manager_id),'No manager') FROM s_emp;

SELECT ename, empno, sal FROM empWHERE deptno = (SELECT deptno 		FROM dept		WHERE UPPER(loc) = UPPER(:loc));

SELECT name FROM empWHERE REGEXP_LIKE (name,'^A|B','i');

FROM emp e, dept d

SELECT COUNT(title) FROM books WHERE genre = 'Horror' AND title IS NULL;

SELECT MAX(a), VARIANCE(b), MIN(C) FROM COMBOS;

SELECT t.type, SUM(t.price) FROM tools t GROUP BY t.type HAVING SUM(t.price) >= (SELECT AVG(price) 			FROM tools 			WHERE type IN (SELECT type 				       FROM tool_types 				       WHERE usage = 'Often')); 

SELECT a, b, c, SUM(d), GROUP_ID FROM vtableGROUP BY GROUPING SETS (a, ROLLUP(b,c));

SELECT a, b, SUM(c) FROM vtableGROUP BY (a,b);

SELECT a.ln, b.zip, c.city FROM a, b, c WHERE a.ln = b.ln;

SELECT a1.animal, a2.animal, a1.color FROM animals a1, animals a2 WHERE a1.color = a2.color AND a1.animal <> a2.animal;

VARIABLE sale_id NUMBER(9)

INSERT INTO inventory (invid, invname) VALUES (12345670, 'Milk powder'); INSERT INTO inventory (invid, invname) VALUES (12345671, 'Milk cream'); INSERT INTO inventory (invid, invname) VALUES (12345672, 'Buttermilk');UPDATE inventory SET invid = 12345679 WHERE invid = 12345670; UPDATE inventory SET invid = 12345670 WHERE invid = 12345671; UPDATE inventory SET invid = 12345677 WHERE invid = 12345670; COMMIT;UPDATE  inventory SET invid = 12345676 WHERE invid = 12345672; ROLLBACK;

INSERT INTO s_empSELECT * FROM s_empAS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);

CREATE TYPE relative_type AS OBJECT (first_name VARCHAR2(30),last_name VARCHAR2(30),birth_date DATE,MEMBER FUNCTION age RETURN NUMBER);

CREATE TABLE relatives (relation relative_type,description VARCHAR2(100));

CREATE TABLE AIR$_#Table (Plane_ID VARCHAR2(8) CONSTRAINT Pk_Plane_ID PRIMARY KEY, Name VARCHAR2(15)); 

CREATE GLOBAL TEMPORARY TABLE temp_empAS SELECT * FROM emp;

SELECT object_name FROM all_objectsWHERE object_type = 'TABLE';

CREATE TABLE s_emp (id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY ...);

INSERT INTO my_table (id, name) VALUES (1, 'Mike'); SAVEPOINT   a;INSERT INTO my_table (id, name) VALUES (2, 'Karen'); SAVEPOINT   b;INSERT INTO my_table (id, name) VALUES (3, 'Tracy');SAVEPOINT   c;ROLLBACK;INSERT INTO my_table (id, name) VALUES (4, 'Sam');INSERT INTO my_table (id, name) VALUES (5, 'Elrod');COMMIT;

SELECT * FROM s_deptWHERE id NOT IN (SELECT dept_id FROM s_emp);

SELECT * FROM s_dept, s_emp, s_customer;

SELECT SUM(...) OVER () FROM ...;

SELECT last_name, title, start_date,AVG(salary) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_sal FROM s_emp;

SELECT * FROM (SELECT a.*, ROWNUM rn     FROM (SELECT * FROM s_emp ORDER BY TRUNC(SYSDATE - start_date) DESC) a)WHERE rn = 5;

SELECT LEVEL, RPAD(last_name, LEVEL,'_') "Level of subordination" FROM s_empCONNECT BY PRIOR id = manager_idSTART WITH manager_id IS NULLORDER SIBLINGS BY last_name;

SELECT last_name, first_name FROM employee WHERE UPPER(last_name) IN ('BROWN', 'CHAN', 'LINDSEY')AND UPPER(job) IN ('MANAGER', 'TECHNICIAN', 'CLERK')AND salary <=30000;

CREATE VIEW employees AS SELECT j.id, j.description, p.id, p.last_name, p.first_name FROM jobs j, people p WHERE j.id = p.job_id AND p.active = 'Y'; 

SELECT * FROM relationships WHERE predator >= prey;

SELECT month, monthname FROM vtable WHERE month > 6 OR month < 12;

INSERT INTO my_table (id, name) VALUES (1, 'Mike'); SAVEPOINT   a;INSERT INTO my_table (id, name) VALUES (2, 'Karen'); SAVEPOINT   b;INSERT INTO my_table (id, name) VALUES (3, 'Tracy');SAVEPOINT   c;ROLLBACK TO b;INSERT INTO my_table (id, name) VALUES (4, 'Sam');INSERT INTO my_table (id, name) VALUES (5, 'Elrod');COMMIT;

 ALTER SESSION SET NLS_SORT = RUSSIAN_CI;

CREATE TABLE vtable (a NUMBER(3), b VARCHAR2(10));

SELECT r.* FROM relatives rWHERE TO_CHAR(r.birth_date,'MONTH') = 'JANUARY';

INSERT INTO s_empSELECT * FROM empAS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);

GRANT updateON employeeTO Sonya; 

SELECT SYSDATE FROM DUAL;

SELECT ' " '||SUBSTR('happybirthday', 4, 6)||' " ' FROM dual; 

SELECT last_name, first_name, salary FROM employeesWHERE salary = ANY (SELECT avg_salary 		    FROM markets                    WHERE location IN ('N','S'));

SELECT name FROM empWHERE REGEXP_LIKE (name,'^A|B');

SELECT COUNT(title) FROM books WHERE genre = 'Horror';

SELECT COUNT(DISTINCT name) FROM animals WHERE family = ANY (SELECT family 		    FROM blood_types 		    WHERE blood_type NOT LIKE ('warm%'));

SELECT MAX(a), COUNT(b), MIN(C) FROM COMBOS;

 SELECT DISTINCT a FROM vtable;

SELECT RPAD(last_name, LEVEL,'_') FROM s_empCONNECT BY PRIOR id = manager_idSTART WITH manager_id IS NULL;

 SELECT last_name FROM s_empORDER BY NLSSORT(last_name, 'NLS_SORT = BINARY');

WITH subordination (id, job) AS (SELECT id, title FROM s_emp WHERE title = 'President'UNION ALLSELECT s.job||' -> '||e.titleFROM s_emp e INNER JOIN subordination s             ON s.id = e.manager_id)SELECT job FROM subordination;

MERGE INTO doctors d USING physicians p ON (d.doc_id = p.phys_id)WHEN MATCHED THENDELETE WHERE house_calls = 'N'WHEN NOT MATCHED THENUPDATE SET house_calls = 'Y';

CREATE OR REPLACE VIEW emp_info AS SELECT last_name, first_name, title, start_date, dept_id FROM employeeWHERE dept_id = 30;

CREATE TYPE relative_type AS OBJECT (first_name VARCHAR2(30),last_name VARCHAR2(30),birth_date DATE);

GRANT USE ON EDITION emp_vu_edition_1 TO PUBLIC;

INSERT INTO inventory (invid, invname) VALUES (12345670, 'Milk powder'); INSERT INTO inventory (invid, invname) VALUES (12345671, 'Milk cream'); SAVEPOINT   a;INSERT INTO inventory (invid, invname) VALUES (12345672, 'Buttermilk');SAVEPOINT   b;UPDATE inventory SET invid = 12345679 WHERE invid = 12345670; UPDATE inventory SET invid = 12345670 WHERE invid = 12345671; UPDATE inventory SET invid = 12345677 WHERE invid = 12345670; COMMIT;UPDATE  inventory SET invid = 12345676 WHERE invid = 12345672; ROLLBACK;

UPDATE s_emp SET dept_id = 31 WHERE id = 12;

SELECT SUM(...) FROM ...;

SELECT last_name, title, dept_id,SUM(salary) OVER (PARTITION BY title) sum_sal FROM s_emp;

SELECT LEVEL, RPAD(last_name, LEVEL,'_') "Level of subordination"FROM s_empCONNECT BY PRIOR id = manager_idSTART WITH manager_id IS NULLORDER SIBLINGS BY last_name;

INSERT INTO descriptions (description, relation)VALUES ('My grandmother', 	SELECT REF(r)	FROM relatives	WHERE first_name = 'Ekaterina' AND last_name = 'Ivanova');

SELECT SUM(...) OVER (PARTITION BY ...) FROM ...;

SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(TO_DATE('08-NOV-1999','DD-MON-YYYY')),3),'DD-MONTH-YYYY') FROM dual;  

ALTER USER Alice IDENTIFIED BY Stud91Gr08; 

SELECT last_name, (SELECT name FROM s_dept WHERE s_dept.id=s_emp.dept_id) dept_nameFROM s_emp;

SELECT a, b FROM vtable WHERE c LIKE '%e%';

SELECT DISTINCT a.nameFROM animals a, blood_types tWHERE a.family = t.family;

SELECT a.lname, a.fname FROM a, b WHERE a.lname = b.lname AND b.sex = 'male';

SELECT t.city, t.name FROM teams t, payroll p WHERE t.id = p.team_id AND   p.total_amt > 5000000 AND   t.country = 'Canada';

SELECT name, salary, dept_no FROM employeeWHERE salary > (SELECT AVG(salary)		FROM employee;		WHERE dept_no = (SELECT dept_no				 FROM employee				 WHERE last_name = (SELECT last_name						    FROM employee				    		    WHERE salary > 50000)));

CREATE TABLE relatives (relation relative_type,description VARCHAR2(100));

CREATE OR REPLACE DIRECTORY emp_files AS 'c:\employees';

SELECT object_name FROM user_objectsWHERE object_type = 'TABLE';

ALTER USER Colin IDENTIFIED BY Wvu88Stud; 

SELECT name, NVL(sal,0) salaryFROM empWHERE sal IS NULL;

SELECT product_id, name FROM products WHERE EXISTS IN (SELECT * FROM types 		 WHERE types.type = products.type 		 AND descr <> 'Household') AND price >= 10.57;

SELECT COUNT(*) FROM locations GROUP BY climate, cost_rank;

SELECT a, b, SUM(c) FROM vtableGROUP BY ROLLUP(a,b);

SELECT a1.animal, a2.animal, a1.color FROM animals a1, animals a2 WHERE a1.color = a2.color AND a1.animal <> a2.animal; 

CREATE OR REPLACE VIEW last_first_vu AS SELECT last_name||' '||first_name "Employee names"FROM employee ORDER BY last_name, first_name;

CREATE GLOBAL TEMPORARY TABLE temp_empAS SELECT * FROM emp;

SELECT COUNT(name) FROM animals WHERE family <> ALL (SELECT family 		    FROM blood_types 		    WHERE blood_type LIKE ('warm%'));

WITH subordination (id, job) AS (SELECT id, title FROM s_emp WHERE title = 'President'UNION ALLSELECT s.job||' -> '||e.titleFROM s_emp e INNER JOIN subordination s             ON s.id = e.manager_id)SELECT job FROM subordination;

INSERT ALLWHEN TO_CHAR(sale_date,'Q' = '1') THEN INTO sal_q1 (id, sale_date)...WHEN TO_CHAR(sale_date,'Q' = '4') THEN INTO sal_q4 (id, sale_date)SELECT * FROM sale;

SELECT * FROM s_dept, s_emp, s_region;

SELECT last_name, title, start_date,AVG(salary) OVER (ORDER BY start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_sal FROM s_emp;

SELECT t.type, SUM(t.price) FROM tools t GROUP BY t.type HAVING SUM(t.price) <= (SELECT AVG(price) 			FROM tools 			WHERE type IN (SELECT type 				       FROM tool_types 				       WHERE usage = 'Sometimes')); 

VARIABLE sale_id NUMBER(9)

CREATE SEQUENCE emp_seq;

ALTER TABLE s_empRENAME CONSTRAINT sys_c0001411 TO s_emp_id_pk;

CREATE TYPE relative_type AS OBJECT (-- хранение имени, фамилии, даты рождения и вычисление возраста родственниковfirst_name VARCHAR2(30),last_name VARCHAR2(30),birth_date DATE,MEMBER FUNCTION age RETURN NUMBER);

CREATE TABLE relatives (relation relative_type,description VARCHAR2(100)-- описание степени родства);

SELECT last_name, SUM(salary) OVER() sum_sal FROM s_emp;

CONN system/********CREATE USER u1_02 PROFILE "DEFAULT" IDENTIFIED BY "u1_02p";GRANT CLASSUSER TO u1_02;GRANT CONNECT TO u1_02;

ALTER TABLE employee ADD (end_date DATE);

CREATE TABLE ItemPromotion (itemno char(10) NOT NULL, pricelvl char(3) NOT NULL,  promotionprice decimal(12,2) NOT NULL); 

SELECT id, last_name, ROWNUM FROM s_emp;

SELECT * FROM s_deptWHERE id NOT IN (SELECT dept_id FROM s_emp);

SELECT last_name, high_grade, high_grade-lag(high_grade,1) OVER (ORDER BY last_name) AS "Analytic function"FROM math_101;

SELECT USER FROM DUAL;