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