Thursday, August 9, 2012


/*   to view the available tables in the current user    */
Sql> select * from user_objects where object_type = ‘TABLE’;   

/*        to voew the available tables    */
Select * from tab;   (or)  select * from cat;     (or)    select tname from user_tables;
Sql> select * from user_tables;   /*          it will display all the tables  */

/*display the tables which are all having constraints  */
Sql> select constraint_name,table_name from user_constraints;  

To findout what view we have created
Select view_name from user_views;

To findout what constraints are defined in our database
Select constraint_name from user_constraints;

To make each transaction to be automatically committed right after it is executed
Sql> set autocommit on;     in reverse   sql> set autocommit off;

To display the files which are available in the database
Sql> select file_name from dba_data_files;

To modify the constraint null to notnulll in a table
Sql> alter table employee modify empno not null;

Alter Table Commands
Alter table employee add constraint constraint_name unique(employee_id);
Alter table table_name disable constraint constraint_name;
Alter table employee add emp_mail_id varchar2(20);
Alter table employee rename column empno ro employeeno;
Alter table employee modify(empid varchar2(4));
Alter table emp rename to employee;
Alter table employee drop constraint constraint_name;

Drop table command
Drop table table_name;
Drop table table_name purge;
Drop table table_name cascade constraints;
Flashback table emp to before drop;

Create Table Commands
Create table employee (empno number(3) not null, empid number(4));
Insert into employee values(1,null);
Insert into employee values(null,3);

/*  the second insert statement will throw error   */
Alter table employee modify empno null;
Create table employee ( empno number unique, empname varchar2(10));

To drop a constraint:
Alter table employee drop constraint constraint_name;

To add a unique constraint:
Alter table employee add constraint uq_emp unique(empid);
Create table employee ( empno number, ename varchar2, unique(empno,ename));
Create table employee( empno number, empname varchar2(10), constraint uq_emp unique(empno,empname);

Primary key constraint:
Create table employee ( empno number primary key, empname varchar2(15));

Naming primary key:
Create table employee( empno number, empid number, constraint pk_emp primary key(empno));

Check constraint:
Create table employee( empno number check(empno between 100 and 150), empname varchar2(10));

This allows only numbers that are between 100 and 150 in the column empno.
Check constraint can be added after a table had been created.
Alter table employee add constraint check_emp check(empno>50);
Create table employee( empno number, empid number, empname varchar2(5), check(empno<empid));

To disabling constraint:
Alter table employee disable unique(empno,ename);
Alter table employee  disable constraint uq_emp;

Foreign key
Alter table department add constraint fk_dept_empno foreign key(empno) references employees(empno);
Create table emp as select * from employee;
Select tablespace_name from dba_data_files;
Select username,default_tablespace from dba_users;
Select * from tab where name like’HR’;
Select object_type from user_objects;
Select * from user_catalog;

Insert all
            Into sal_history values( empno,hiredate,sal)
            Into mgr_history values( empno,mgr,sal)
            Select emp_no  empno, here_data  hiredate, salary  sal,  mgr)
From employees where emp_no > 100;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect