Oracle SQL accomodates simple, classic, robust architecture for accessing, defining and maintaining data.
What is Oracle SQL?
Oracle SQL (Structured Query Language) is the combination of statements with all programs and users access data in an Oracle database.
Application specific programs and Oracle tools allow users access to the respective database without querying SQL directly, but these applications must use SQL internally when executing the user’s request.
Oracle SQL Key Features:
- Oracle SQL offer ANSI SQL Compliance.
- Offering extensions for PL/SQL and Java.
- Multiversion read consistency.
- High performance by utilizing indexes, partitioning, in-memory, optimization.
- Text and Spatial.
- Analytic SQL for powerful queries.
Manage Database Users in Oracle:
In order to create an database user in oracle, first we’ll need to login as sys or system.
Once you login, the create user command is
create user identified by "password";
create user data_editor identified by "John";
Since we created a user, next step is connect to the created user.
conn data_editor/John! ORA-01045: user DATA_OWNER lacks CREATE SESSION privilege; logon denied.
Oops ! Why it was not connecting? The issue is you haven’t provided the user with permissions.
By default a database user has no privileges. Switch to next section about granting user privileges.
Granting & Revoking User Privileges:
User permissions can be provided by using grant command.
grant privilege to user
In order to allow user to login, we need to give “create session” privilege to user.
grant create session to data_editor;
Now coming to role permission, lets give a “CREATE TABLE” role which allows to user to create a table.
grant create table to data_editor;
However there are powerful ORACLE roles available, which can be granted on the basis of role and operations expected from user.
In order to remove role of user, the following syntax has to be applied.
revoke privilege to user
revoke create table from data_editor;
Similarly in order to remove user, the following syntax has to be applied
drop user username;
drop user data_editor;
Storing/Modifying Data in Database
In order to store the data in tables, first we need to create an table in database.
‘CREATE TABLE’ statement will be used to create a new table in the database.
Syntax for table creation:
CREATE TABLE TABLE_NAME( COLUMN1_NAME DATA_TYPE, COLUMN2_NAME DATA_TYPE, COLUMN3_NAME DATA_TYPE, ... );
CREATE TABLE EMPLOYEE ( EMPLOYEE_NAME VARCHAR2(10), ADDRESS VARCHAR2(20), );
If we need to alter a table like adding adding extra columns, then we will be using ALTER TABLE statement
Oracle ALTER TABLE statement will be used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement can also be used to rename a table.
ALTER TABLE TABLE_NAME ADD(COLUMN_NAME DATA_TYPE);
ALTER TABLE EMPLOYEE ADD(STATE VARCHAR2(10));
Similarly we can drop the column using ALTER TABLE statement.
ALTER TABLE TABLE_NAME DROP(COLUMN_NAME);
ALTER TABLE EMPLOYEE DROP (ADDRESS);
Accessing Data in Database – SELECT
In order to access data in data base, it all starts with SELECT.
The purpose of SELECT statement is to pull/retrieve data from one or multiple tables, views, object views, object tables, analytic views, materialized views.
SELECT * FROM "TABLE_NAME";
SELECT * FROM EMPLOYEE;
If we need to use SELECT statement using WHERE condition then
SELECT * from "TABLE_NAME" where "COLUMN_NAME" = "Value";
SELECT * from EMPLOYEE where EMPLOYEE_NAME = "John";