Oracle SQL Commands: Part 5

24. DROP SCHEMA Command
 
A group of database objects owned by a database user that has the name of the database user is known as a Schema. If you decide to delete all objects of a certain schema then it is better to drop the schema because it will save time as well as it is easy to process. To delete the existing Schema we use the DROP SCHEMA Command.
 
Syntax

DROP SCHEMA Schema;

GO
 
Example

Assume the following Schema:
 

CREATE SCHEMA AUTHORIZATION Student

 

 CREATE TABLE BOOK

  (

   Book_id NUMBER PRIMARY KEY,

   Book_Price NUMBER,

   Author Varchar2(10),

   Seller Varchar2(10),

   Publications Varchar2(30)

  )

 

 CREATE VIEW New_Book AS

 SELECT Book_id, Author FROM Book WHERE Book_Price = 500

 

 GRANT SELECT ON New_Book TO Scott;
 
Now, drop the Schema as in the following:
 

DROP SCHEMA Student;

GO


25. DROP FUNCTION Command
 
To drop a standalone stored function from the database we use the DROP FUNCTION Command.
 
Syntax
 

DROP FUNCTION [function name]


Example

Assume that we have the following table with a function total_students.
 
STUDENT

Stu_ID Name Age Stream
1 Rajat 18 Science
2 Jaya 20 Commerce
3 joseph 19 Arts
4 Maria 18 Science

 

CREATE OR REPLACE FUNCTION total_students

RETURN number IS

       total_number(2) :=0;

BEGIN

SELECT count(*) into total

FROM Students;

RETURN total;

END;


Now, to drop the function execute the following statement:

DROP FUNCTION <total_students>;


26. DROP SEQUENCE Command
 
An Oracle sequence is an object that generates incrementing and decrementing numbers. They can be cast as a Primary Key of a table. To drop the existing sequence we use a DROP SEQUENCE Command.
 
Syntax

DROP SEQUENCE [sequence name]
 
Example

Assume that we have the following created Sequence:

CREATE SEQUENCE dba_seq

MINVALUE 1

MAXVALUE 40000

START WITH 1

INCREMENT BY 1

CACHE 100
 
To drop the dba_seq we write the statement as:

DROP SEQUENCE dba_seq;


27. DROP SENONYM Command
 
If you are going to change the definition of the Schema and want to recreate it, then you need to drop it first or if you are going to permanently delete the synonym, for both the cases use the DROP SENONYM Command.
 
Syntax

DROP [PUBLIC] SYNONYM [schema.]synonym


Example

Assume we have the following Synonym:
 

CREATE PUBLIC SYNONYM buyers

FOR app.buyers;


Now, drop the Synonym as in the following:
 
DROP SYNONYM buyers; 

28. CREATE JAVA Command
 
To create a schema object containing a Java source, class, or resource in your own schema, you must have CREATE PROCEDURE system privilege.
 
Syntax

Use the following to create a Java Class Object:
 

CREATE JAVA CLASS USING BFILE (java_dir, 'Agent.class')

/
Example

The following statement creates a Java resource schema object named "MyJava":
 

CREATE JAVA RESOURCE NAMED "MyJava"

USING BFILE (java_dir, 'JavaBundle.dat')

/


29. DROP JAVA Command
 
To create a Schema object containing a Java source, class, or resource in your own schema, you must have CREATE PROCEDURE system privilege. The Java source, class, or resource must be in your own schema or you must have the DROP ANY PROCEDURE system privilege. You also must have the EXECUTE object privilege on Java classes to use this command.
 
Syntax

Use the following to drop a Java Class Object:
 

DROP JAVA CLASS Class_name;


Example

The following statement drops a Java Class named "MyJava":
 

DROP JAVA CLASS "MyJava";

  
30. TRUNCATE TABLE Command
 
To delete all the rows from the table that are occupying more space and don't let the other rows to enter without dropping the entire table, we use the TRUNCATE TABLE Command.
 
Syntax
 

TRUNCATE TABLE table_name;


Example

Assume that we have a table named "MyTable".
 

TRUNCATE TABLE MyTable; 


DML Commands
 
1. DELETE Command
 
The DELETE Command is used to delete a single or multiple rows from the table that are in your schema or if you have DELETE privilege on them.
 
Syntax
 

DELETE FROM <table_name>

WHERE <where clause>


Example

Assume the following PRODUCTS table to understand the example:
 
PRODUCTS

Prod_id Prod_Name Quantity Price
11023 SONY 2 50k
45600 PHILIPS 5 20k
12109 SONY 3 80k
87600 SANSONG 8 90k
42421 SONY 1 25k


If you want to delete all the rows having a record of the product SONY from a table Products, then execute the following command:
 

DELETE FROM PRODUCTS

WHERE Prod_Name=SONY;


This query will delete all the records having a Product name as SONY, and the result will be:
 
PRODUCTS

Prod_id Prod_Name Quantity Price
45600 PHILIPS 5 20k
87600 SANSONG 8 90k


Note: If you don't specify the WHERE condition then all rows will be deleted.

  • Example of DELETE Command Using SubQuery

DELETE FROM (SELECT * FROM PRODUCTS)

WHERE Prod_Name = SONY;
 
And, last but not the least if you want to delete the entire table, then you can use the following Query:
 
DELETE FROM Products;
  
2. INSERT Command
 
After creating a table, the second step to do is to add the records in that table. To insert the data to the rows of that table we use the INSERT Command.
 
Syntax
 

INSERT INTO < table_name>

(col1, col2 . . . coln)

VALUES (value1, value2, . . . valuen)


Or

To fetch the data from some other table then:
 

INSERT INTO <table_name>

 SELECT <columns>

 FROM   <table>

 WHERE  <where-clause>


Example

Assume the following table of Employees.
 
EMPLOYEES

Emp_ID Emp_Name Designation Salary
      111 Anjali Tech Lead 20K
      222 Manuj Sr. Software Eng. 25K
      333 Pipul Asst. Project Manager 50k
      444 Nitin Software Eng. 20k
      555 Varun Sr. Software Eng. 25k


Use the following to add a new row to the existing table:
 
Insert into Employees (666, Nipun, Project Manager, 80K);
 
And the result is the following.
 
EMPLOYEES

Emp_ID Emp_Name Designation Salary
      111 Anjali Tech Lead 20K
      222 Manuj Sr. Software Eng. 25K
      333 Pipul Asst. Project Manager 50k
      444 Nitin Software Eng. 20k
      555 Varun Sr. Software Eng. 23k
      666 Nipun Project Manager 80k


Again, if you want to fetch the data from one table to another, then the following is the example.
 
EMPLOYEES

Emp_ID Emp_Name Designation Salary
      111 Anjali Tech Lead 20K
      222 Manuj Sr. Software Eng. 25K
      333 Pipul Asst. Project Manager 50k
      444 Nitin Software Eng. 20k
      555 Varun Sr. Software Eng. 23k
      666 Nipun Project Manager 80k


The following is a sample query to fetch the data from the Employees table to the New_Employees table.
 

SELECT Emp_Id, Emp_name, Salary

INTO New_Employees

FROM EMPLOYEES

WHERE Designation = "Sr.Software Engg."
 
And the result is:
 
New_Employees

Emp_ID Emp_Name Designation Salary
      111 Anjali Tech Lead 20K
      222 Manuj Sr. Software Eng. 25K
      555 Varun Sr. Software Eng. 23k

Previous article: Oracle SQL Commands : Part 4
Next article: Oracle SQL Commands: Part 6