SQL statements in Oracle-Exercise

-- 1
Create table my_test (
Id NUMBER (3 ),
Name VARCHAR2 (20)
);

Insert into my_test VALUES (1, 'this is line 1 ');
Insert into my_test VALUES (1, 'this is line 1 ');
Insert into my_test VALUES (1, 'this is line 1 ');
Insert into my_test VALUES (1, 'this is line 1 ');
Insert into my_test VALUES (1, 'this is line 1 ');
COMMIT;

SELECT * FROM my_test;

Select rowid, id, name FROM my_test;

-- The specific ROWID value depends on the query result.
Delete from my_test where rowid = 'aaahosaajaaaah0aac ';

Select rownum, id, name FROM my_test;

SELECT * FROM my_test where rownum <3;

-- 2
Create table employee_copy as select * FROM employee
WHERE 1 = 2;

Insert into employee_copy
SELECT * FROM employee WHERE deptcode = 'dp02 ';

SELECT empno "employee ID", ename "employee name ",
Designation "position", dateofjoining "employment date"
FROM employee;

-- 3
SELECT e. empno, ename, e. deptcode, s. empsal
FROM employee e inner join salary_records s
ON e. empno = s. empno
WHERE s. empsal> = 15000 AND s. empsal <= 20000;

SELECT e. empno, ename, e. deptcode, s. empsal
FROM employee e inner join salary_records s
ON e. empno = s. empno
WHERE s. empsal BETWEEN 15000 AND 20000;

-- 4
SELECT (orderno | 'order date is '| odate
| ', Shipping date is' | del_date) AS "order status"
FROM order_master WHERE vencode = 'v001 ';

-- 5
SELECT vencode FROM vendor_master
UNION
SELECT vencode FROM order_master WHERE ostatus = 'C ';

-- 6
SELECT EmpNo, Ename, designation, deptcode,
ROUND (dateofjoining, 'Year ')
FROM Employee WHERE dateofjoining <'01-January 1, July-2003 ';

-- 7
SELECT ProdId, ProdPrice, RANK () OVER
(Order by ProdPrice DESC) rank
FROM ProductDetails;