How To Create Table In Pl Sql Developer

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:

01. Select all records

02. Select particular fields

03. Using AS keyword

04. Using WHERE clause

05. Using SUBSTR function

06. Using TO_CHAR function

07. Using SYSDATE and ROUND

08. Using DISTINCT clause

09. Using ROWNUM

10. Using Logical AND, OR and NOT

11. Using IN cluase

12. Using BETWEEN clause

13. Using LIKE clause

14. Using ORDER clause

15. Using FETCH clause

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_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.

SELECT * FROM invoices

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.

SELECT * FROM vendors

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.

SELECT * FROM vendors

WHERE vendor_id IN
(SELECT vendor_id FROM invoices
WHERE invoice_date > '26-JAN- 2019′)

14. Using BETWEEN clause in SELECT query.

SELECT * FROM invoices

WHERE invoice_total – payment_total – credit_total BETWEEN 200000 AND 700000

15. Using LIKE clause in SELECT query.

SELECT * FROM vendors

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