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.
https://fahmidasclassroom.com/how-to-use-sql-developer-and-connect-with-a-database/
CONTENTS:
03. Using AS keyword
09. Using ROWNUM
10. Using Logical AND, OR and NOT
11. Using IN cluase
Start the following steps to create table and running SELECT query from SQL developer.
Steps:
1. After connecting with the database, create two tables named vendors and invoices with the following fields.
VENDORS
VENDOR_ID (Number, Primary key)
VENDOR_FIRST_NAME (Varchar2(50))
VENDOR_FIRST_NAME (Varchar2(50))
VENDOR_ADDRESS (Varchar2(100))
VENDOR_CITY (Varchar2(50))
VENDOR_STATE (Varchar2(50))
INVOICES
INVOICE_ID (Number, Primary key)
VENDOR_ID (Number, Foreign key)
INVOICE_NUMBER (Varchar2)
INVOICE_DATE (Date)
INVOICE_TOTAL (Number)
PAYMENT_TOTAL (Number)
CREDIT_TOTAL (Number)
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.
VENDORS
INVOICES
2. Insert Some data in both tables. Click on Data tab and click “+” icon to insert records in the table. After adding some records, the tables will be looked like below.
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.
Magic Chef 27-Lb. Portable Countertop Ice Maker with Authentic Realtree Xtra Camouflage Pattern
5. Run the following SELECT query to select specific columns of invoices table.
SELECT invoice_number , invoice_date , invoice_total
FROM invoices
ORDER BY invoice_total
6. Run the SELECT query using AS keyword.
SELECT invoice_total , payment_total , credit_total ,
invoice_total – payment_total – credit_total AS balance_due
FROM invoices
- Run the SELECT query using WHERE clause to retrieve particular records..
SELECT invoice_id , invoice_total, (credit_total + payment_total ) AS total_credits
FROM invoices
WHERE invoice_id = 2
SELECT invoice_number , invoice_date , invoice_total
FROM invoices
WHERE invoice_total > 50000
7. Using SUBSTR function to retrieve partial part of a field.
SELECT vendor_id,SUBSTR (vendor_name , 1 , 2) AS initials
FROM vendors
8. Using TO_CHAR function to retrieve partial part of a field.
SELECT ‘Invoice :#’ || invoice_number || ‘ , dated ‘ || TO_CHAR(invoice_date , ‘ MM/DD/YYYY ‘ ) ||
‘ for $’ || TO_CHAR(payment_total) AS ” Invoice Text “
FROM invoices
9. Using SYSDATE and ROUND to find out date difference.
SELECT invoice_date,SYSDATE AS today,
ROUND(SYSDATE – invoice_date)
FROM invoices
10. Using DISTINCT keyword to filter duplicate values
SELECT DISTINCT vendor_city , vendor_state
FROM vendors
ORDER BY vendor_city
11. Using ROWNUM pseudo column to limit the number of rows.
SELECT vendor_id, invoice_total
FROM invoices
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.
SELECT vendor_name, vendor_city, vendor_state
FROM vendors
ORDER BY vendor_city || vendor_state
SELECT vendor_name ,vendor_address || ‘ , ‘ || vendor_city || ‘ , ‘ || vendor_state AS address
FROM vendors
ORDER BY 2 , 1
17. Using FETCH clause in SELECT query.
SELECT invoice_id, vendor_id , invoice_total
FROM invoices
ORDER BY invoice_total DESC
FETCH FIRST 3 ROW ONLY
