oracle.basic.sql;

User in oracle database:

1.       sys

         a.   sysdba

         b.sysopera

      2.system

      3.hr

Select *from dba_users;

Schema: is a collection of database objects

Sub-division of structured query language(sql):

1.DDL(create,drop,alter,truncate,rename):data definition language

2.DML(insert,update,delete,merge):data manipulation language

3.DCL(grant,revoke):data control  language

4.DQL(select):data query language

5.DTL/TCL(rollback,commit,savepoint):data transation language/transaction control language

CREATING AN USER:

CREATE USER    user_name IDENTIFIED BY password;

GRANTING TO USER:

GRANT CONNECT TO user_name;

GRANT CREATE TABLE TO user_name;

GRANT RESOURCE TO user_name;

ANOTHER WAY OF GRANTING:

CREATE ROLE role_name;

GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW,SELECT ANY TABLE TO role_name;

GRANT role_name TO user_name;

Grant dba to user_name;

Desc dba_sys_privs;

Select grantee,privilege from dba_sys_privs  where grantee=’user_name’;

Create table employee(emp_id number constraint pk_empid primary key,emp_name varchar not null,salary number(7,2) check(salary>1000),dob date,gender char(1){check(gender=’m’ or gender=’f’)});

Create table employee(emp_id number constraint pk_empid primary key,emp_name varchar not null,salary number(7,2) check(salary>1000),dob date,gender char(1){check(gender in (‘m’,’M’,’f’,’F’))});

Change name:

Alter table employee rename column emp_id to eid

Change data type:

Alter table employee modify emp_name varchar2(15);

Add/Drop:

Alter table employee add dept_id number;

Alter table employee drop column dept_id;

Spooling:

Spool my_file_name;

Spool my_file_name append;

Spool off;

Inserting:

Insert into employee(gender,dob,eid,salary,emp_name) values(,,,,,);

Insert into employee(gender,dob,eid,salary,emp_name) values(&eid,&ename,,,,);

Insert into employee values(,,,,,);

Deleting:

Delete employee;

Rollbacking:

Rollback;

Show autocommit;

Truncate table employee;

Rollnum is not readable

System can access any user’s data

Alter user hr account unlock;

Insert into employee(eid,emp_name) select employee_id,first_name from hr.employee where rownum<=10;

Select rownum,rowid,eid,empname from employee;

Alias:

Select sal*12 “Annual Salary” from employee;

Distinict:

Select  distinict(*) from emp;

Select distinict ename from emp;

Wild cards:-----

Rpad(),lpad(),trim(),rtrim(),ltrim(),reverse(),replace(),length(),translate()

On delete cashcade

On delete set null

On update null

Col table_name format a6 word;

Alter session set nls_date_format=’dd/mm/yyyy’;

Select *from employee where gender is null;

Create sequence eid_seq min value 3 max value 55 start with 10 increment by 10;

Insert into emp values(eid_seq.nextval,’a’);

Create sequence eid_seq2 min value 1 max value 50 start with 1 nocache cycle;

Insert all into abc values(,,,,) into abc values(,,,,,) into abc(,,,,,,);

Create view sal_1000 as select *from emp where salary=1000;

Select*from sal_1000;

Create or replace  view sal_1000 as select ename from emp;

Create synonym syn_emp for emp;

Select *from syn_emp;

Update syn_emp set manager=20 where emp_id=12;

Set pagesize 15;

Set linesize 178;

Show all sql plus command by:

Show all;

ttitle Tables_Detail;

ttitle Tables_Detail skip5;

btitle My_Info;

btitle My_Info skip5;

timing;

set time on;

column sal format $9999.99;

col bank_name format a6 word;

column bank_address “Address”;

column bank_address heading off;

column bank_name noprint;

column bank_name print;

break on bank_name;

break on bank_name skip 10;

clrear break;

compute sum label ‘sum of sal’ of salary of debtid;

set  sql prompt ‘The Krrish’;

define

select to_char(to_date(1234,'j'),'jsp') from dual

/