Using SQL Developer you can easily create table and insert data without writing any query. After inserting data, SELECT query is used to retrieve data from any table based on different criteria. How these types of tasks can be done using SQL developer of Oracle 12c is shown in this tutorial. Before starting this tutorial you have to make connection with a oracle database. This task is shown in the following tutorial link.
03. Using AS keyword
09. Using ROWNUM
11. Using IN cluase
Start the following steps to create table and running SELECT query from SQL developer.
1. After connecting with the database, create two tables named vendors and invoices with the following fields.
VENDOR_ID (Number, Primary key)
INVOICE_ID (Number, Primary key)
VENDOR_ID (Number, Foreign key)
Expand the database connection in which you want to create the table. Right click on table name and click New Table… to open create table dialog box.
Fill up the dialog box based on the table requirements for vendors table. In the similar way create invoices table.
3. Click on SQL button to write any query for the database.
4. Write a simple SELECT query to display all data from INVOICES table and click on run button to
execute the query.
5. Run the following SELECT query to select specific columns of invoices table.
6. Run the SELECT query using AS keyword.
- Run the SELECT query using WHERE clause to retrieve particular records..
WHERE invoice_id = 2
SELECT invoice_number , invoice_date , invoice_total
WHERE invoice_total > 50000
7. Using SUBSTR function to retrieve partial part of a field.
8. Using TO_CHAR function to retrieve partial part of a field.
‘ for $’ || TO_CHAR(payment_total) AS ” Invoice Text “
9. Using SYSDATE and ROUND to find out date difference.
SELECT invoice_date,SYSDATE AS today,
ROUND(SYSDATE – invoice_date)
10. Using DISTINCT keyword to filter duplicate values
11. Using ROWNUM pseudo column to limit the number of rows.
WHERE ROWNUM <= 2
12. Using logical AND, OR and NOT in SELECT query.
WHERE vendor_city = ‘Dhaka’ OR vendor_state = ‘Khulna’
SELECT * FROM invoices
WHERE NOT invoice_total <= 700000 AND invoice_date > ’01-JAN-2018′
13. Using IN clause in SELECT query.
WHERE vendor_id IN
(SELECT vendor_id FROM invoices
WHERE invoice_date > ’26-JAN- 2019′)
14. Using BETWEEN clause in SELECT query.
WHERE invoice_total – payment_total – credit_total BETWEEN 200000 AND 700000
15. Using LIKE clause in SELECT query.
WHERE vendor_city LIKE ‘Khu%’
16. Using ORDER clause in SELECT query.
ORDER BY vendor_city || vendor_state
SELECT vendor_name ,vendor_address || ‘ , ‘ || vendor_city || ‘ , ‘ || vendor_state AS address
ORDER BY 2 , 1
17. Using FETCH clause in SELECT query.
SELECT invoice_id, vendor_id , invoice_total
ORDER BY invoice_total DESC
FETCH FIRST 3 ROW ONLY