If we want to execute any SQL command, we need a user/schema and appropriate privileges from the Database Administrator(DBA) to connect to the user/schema.
User:- is someone to connect the Database with appropriate privileges.
Schema:- is a logical structures of database objects and owned by database user, has the same name as user.
Table:- is a basic unit of data storage and data is stored in rows and columns.
Let’s create a user and give basic privileges to the user. To create an user, you must be a user with DBA privilege.
SQL> connect system/*******
Here, system is oracle user with DBA privileges. once you connected to system, you can create another user with required privileges.
Syntax: create user username identified by password;
SQL>create user prasanth identified by pr@s@nth;
SQL>grant connect, resource to prasanth;
Here, connect is a privilege allows us to connect to the user. but you cannot execute any SQL command. just to stay connected.
Resource is another privilege allows us to execute SQL commands.
These are the basic privileges require to a new user.
To connect the user ‘prasanth’:
SQL> connect prasanth/pr@s@nth
DDL Commands:
CREATE:- is used to create database objects like Tables, Views, Sequences, Procedures, Triggers etc.;
Syntax: CREATE TABLE table_name (column1 datatype(size), column2 datatype(size));
Now, we will create two tables EMP and DEPT
CREATE TABLE emp(empno NUMBER(6), ename VARCHAR2(30), job VARCHAR2(13), mgr NUMBER(5), doj DATE, sal NUMBER(10,2), deptno NUMBER(4));
CREATE TABLE dept(deptno NUMBER(4), dname VARCHAR2(30), loc VARCHAR2(12));
To display table structure, DESCRIBE table_name (OR) DESC table_name
Note: These example tables are taken from Oracle default user ‘scott’
ALTER:- used to modify the existing definition of table like add column, rename column, drop column, increase or decrease datatype size, change the datatype etc;
Adding a column: to add one or more columns to existing table
Syntax: ALTER TABLE table_name ADD column_name datatype(size);
ALTER TABLE emp ADD address VARCHAR(100);
ALTER TABLE emp ADD (address VARCHAR(100), pincode number(6));
Rename a column: to change the name of existing column.
Synytax: ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE emp RENAME COLUMN addres to address;
Modify a column: to modify the existing definition of the table/column.
Syntax: ALTER TABLE table_name MODIFY column_name datatype(size);
ALTER TABLE emp MODIFY address VARCHAR2(120);
we modified datatype and its length using modify clause
Drop a column: to drop existing column in a table.
Syntax: ALTER TABLE table_name DROP column_name;
ALTER TABLE emp drop address;
ALTER TABLE emp drop (address, pincode);
RENAME:- to change the existing name of a table.
Syntax: RENAME old_name to new_name;
RENAME emp to emp1;
We can rename the table using ALTER command.
Syntax: ALTER TABLE old_table_name RENAME to new_table_name;
Example 1: ALTER TABLE emp RENAME to emp1;
Example 2: ALTER TABLE emp1 RENAME to emp;
DROP:- to drop existing table.
Syntax: DROP TABLE table_name; — we can restore the table after dropping the table
Example 1: DROP TABLE table_name;
Syntax: DROP TABLE table_name PURGE; — we cannot restore the table after dropping the table
Example 2: DROP TABLE table_name PURGE;
Note: PURGE is a clause, to remove the table permanently.(introduced in Oracle 10g)
Truncate:- to remove the records/data permanently from a table i.e; empty table definition remains
Syntax: TRUNCATE TABLE table_name;
Example: TRUNCATE TABLE emp;
I suggest you all, use SQL*PLUS command line utility tool for practicing SQL commands. If you are familiar with SQL commands then use Integrated Development Environment(IDE) like SQL Developer, TOAD or DB Solo.
Next, We’re going to have a “Hands-on session on Data Manipulation Language (DML)”