1. Consider the Invoices table below:
| INVOICE_ID | INVOICE_NUM |
|---|---|
| 101 | INV101 |
| 102 | INV102 |
| 103 | INV103 |
| 103 | INV103 |
| 102 | INV102 |
| 101 | INV101 |
Sol: DELETE FROM invoices WHERE rowid NOT IN
(SELECT MIN(rowid) FROM invoices GROUP BY invoice_id, invoice_num);
OR
DELETE FROM invoices WHERE rowid NOT IN
(SELECT MAX(rowid) FROM invoices GROUP BY invoice_id, invoice_num);
2. Consider below items table:
| ITEM_NUM | INV_ORG_ID |
|---|---|
| Pen | 87 |
| Pen | 86 |
| Book | 87 |
| Laptop | 86 |
| Computer | 87 |
| Mouse | 87 |
| Mouse | 86 |
| Keyboard | 87 |
| Keyboard | 86 |
Sol: Method 1: SELECT item_num FROM items
MINUS
(SELECT item_num FROM items WHERE inv_org_id = 87
INTERSECT
SELECT item_num FROM items WHERE inv_org_id = 86);
Method 2: SELECT item_num FROM items WHERE inv_org_id = 87 AND item_num NOT IN (SELECT item_num FROM items WHERE inv_org_id = 86)
UNION
SELECT item_num FROM items WHERE inv_org_id = 86 AND item_num NOT IN (SELECT item_num FROM items WHERE inv_org_id = 87);
Method 3: SELECT item_num FROM items
MINUS
(SELECT item_num FROM items WHERE inv_org_id = 86 AND item_num IN (SELECT item_num FROM items WHERE inv_org_id = 87));
Method 4: SELECT item_num FROM items WHERE item_num NOT IN
(SELECT item_num FROM items WHERE inv_org_id = 87
INTERSECT
SELECT item_num FROM items WHERE inv_org_id = 86);
Method 5: SELECT item_num FROM items WHERE item_num NOT IN
(SELECT item_num FROM items WHERE inv_org_id = 87 AND item_num IN
(SELECT item_num FROM items WHERE inv_org_id = 86));
3. Write a query to retrieve order numbers that have more than 10 lines.
Sol: SELECT order_number, count(*)
FROM oe_order_headers_all ooh, oe_order_lines_all ool
WHERE ooh.header_id = ool.header_id
GROUP BY order_number
HAVING count(*) > 10;
4. Write a query to get the hire date of least employee number.
Sol: Method 1: SELECT hire_date FROM emp WHERE empno = (SELECT MIN(empno) FROM emp);
Method 2: SELECT MIN(hire_date) KEEP (dense_rank FIRST ORDER BY empno) FROM emp;
5. Consider below ITEMS table:
| ITEM_NUM | INV_ORG_ID |
|---|---|
| Pen | 87 |
| Air Conditioner | 86 |
| Book | 87 |
| Laptop | 86 |
| Computer | 87 |
| Ebook | 87 |
| Mouse | 86 |
| Inkpot | 87 |
| Keyboard | 86 |
Write a query to display the items that don't start or end with a vowel.
Sol: SELECT item_num FROM items WHERE SUBSTR(item_num, '1', '1') NOT IN ('a','e','i','o','u') AND SUBSTR(item_num, '-1', '1') NOT IN (item_num, 'A','E','I','O','U');
6. Consider below table Emp.
| Empno | Empname |
|---|---|
| 10 | John |
| 11 | Jason |
| 12 | Joseph |
| 13 | Jones |
| 14 | Jane |
| 15 | Jackmel |
Write a query to to display the empname values in the following order:
Jones, Jason, John, Jackmel, Joseph, Jane.
Sol: SELECT empname FROM emp ORDER BY DECODE(empname, 'Jones', 1, 'Jason', 2, 'John', 3, 'Jackmel', 4, 'Joseph', 5, 'Jane', 6, 7);
7. Display the difference between given two dates in hours: 21-OCT-2019 and 19-AUG-2020
Sol: SELECT (TO_DATE('21-OCT-2019 00:00:00','DD-MON-YYYY')-('19-AUG-2020 00:00:00','DD-MON-YYYY'))*24 FROM dual;
8. Build a hierarchical query on emp table to display the records such that the MGR column equals their EMPNO, start with person(s) who have no manager.
Sol: SELECT * FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
9. How to fetch the first record from a table (Take emp as example)?
Sol: SELECT * FROM emp WHERE rownum = 1;
10. How to fetch the last record from a table (Take emp as example)?
Sol: SELECT * FROM emp WHERE rowid = (SELECT MAX(rowid FROM emp);
