CREATE TABLE salgrade_test ( grade NUMBER (2),lowsal NUMBER (7,2),hisal NUMBER (7,2) );
DESC salgrade;
SELECT * FROM salgrade_test;
INSERT INTO salgrade_test VALUES (1,10001,99999);
INSERT INTO salgrade_test VALUES (2,8001,10000);
INSERT INTO salgrade_test VALUES (3,6001,8000);
INSERT INTO salgrade_test VALUES (4,4001,6000);
INSERT INTO salgrade_test VALUES (5,1,4000);
SELECT NAME,salary,grade FROM emp_test,salgrade_test WHERE emp_test.salary BETWEEN salgrade_test.lowsal AND salgrade_test.hisal;
SELECT NAME,grade FROM emp_test FULL JOIN salgrade_test ON emp_test.salary BETWEEN salgrade_test.lowsal AND salgrade_test.hisal;
CREATE TABLE salgrade_copy AS ( SELECT * FROM salgrade_test WHERE 1 <> 1 );
SELECT * FROM salgrade_copy;
CREATE TABLE emp_test_copy AS ( SELECT emp_id,NAME,salary * 12 year_sal FROM emp_test WHERE dept_test_id = 10 );
SELECT * FROM emp_test_copy;
CREATE TABLE emp_count (did,emp_num) AS ( SELECT dept_test_id,COUNT (*) FROM emp_test GROUP BY dept_test_id );
SELECT * FROM emp_count;
CREATE TABLE emp_copy AS ( SELECT * FROM emp_test WHERE 1 <> 1 );
INSERT INTO emp_copy ( SELECT * FROM emp_test WHERE dept_test_id = 10 );
SELECT * FROM emp_copy;
DELETE FROM emp_copy;
INSERT INTO emp_copy ( SELECT * FROM emp_test WHERE dept_test_id IN (20,30) );
SELECT * FROM emp_copy;
DELETE FROM emp_copy;
INSERT INTO emp_copy ( SELECT * FROM emp_test WHERE ROWNUM <= 8 );
SELECT * FROM emp_copy;
UPDATE emp_test SET salary = 3500,JOB = 'Programmer' WHERE emp_id = 1012;
SELECT * FROM emp_test;
UPDATE emp_test SET salary = salary + 3500 WHERE dept_test_id = 10;
SELECT * FROM emp_test;
CREATE TABLE emp_copy2 AS ( SELECT * FROM emp_test WHERE 1 <> 1 );
INSERT INTO emp_copy2 (emp_id,salary) VALUES (1015,'amy',4000);
INSERT INTO emp_copy2 (emp_id,salary) VALUES (1016,'rory',5000);
INSERT INTO emp_copy2 (emp_id,salary) VALUES (1017,'river',6000);
SELECT * FROM emp_copy2;
CREATE TABLE emp_copy3 AS SELECT DISTINCT * FROM emp_copy2;
SELECT * FROM emp_copy3;
RENAME emp_copy3 TO emp_bak4;
RENAME emp_bak4 TO emp_copy3;
DELETE FROM emp_copy2 WHERE ROWID NOT IN ( SELECT MAX (ROWID) FROM emp_copy2 GROUP BY emp_id,salary );
SELECT * FROM emp_copy3;
UPDATE emp_copy3 SET emp_id = 1015 WHERE emp_id = 1014;
CREATE TABLE mytemp_test (ID NUMBER(4));
INSERT INTO mytemp_test VALUES (3);
SAVEPOINT A;
INSERT INTO mytemp_test VALUES (4);
SAVEPOINT B;
INSERT INTO mytemp_test VALUES (5);
ROLLBACK TO A;
SELECT * FROM mytemp_test;
TRUNCATE TABLE mytemp_test;
ALTER TABLE mytemp_test ADD(name VARCHAR(10));
ALTER TABLE mytemp_test ADD(password VARCHAR(10));
ALTER TABLE mytemp_test RENAME COLUMN password TO pwd;
ALTER TABLE mytemp_test MODIFY(pwd VARCHAR(8)) ;
ALTER TABLE mytemp_test DROP COLUMN pwd ;
DESC mytemp_test;