/* 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));
Constraints
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;
UNCONDITIONAL INSERT ALL
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