SQL Statements:DDL: create,alter,rename,drop and truncate.DML: insert,update and delete.DCL: grant,revoke.TCL: commit,rollback,savepoint. DQL: select
How to insert multiple rows into a table:
insert into emp values(&empid,&ename,&salary);
update emp set salary = 10000 where ename = 'john';
delete from emp where ename = 'john';
create table emp ( eid number(4), ename varchar2(10). salary number(10));
alter table emp add dept number(4);
To rename a column: alter table emp rename column salary to newsalary;
drop table emp;
DDL commands are auto saved.
select * from dual; - shows dummy table.
truncate table emp; - deletes the data from table permanently. truncate is a DDL command as it is a autosave command. Also, it is DML because it deals with data.
commit; - saves the user action till that point.
rollback; - it will undo the user action to previous successfully saved status.
savepoint; - it is an instant save for the user action for that particular section.
example: insert 2 rows into emp; - savepoint sp1
update 1 row into emp; - savepoint sp2
insert 1 row into emp; - savepoint sp3
rollback to sp2; takes to last saved state until savepoint sp2.
grant select,insert,update on emp to scott;
revoke select,insert,update on emp from scott;
Constraints: are conditions or limitations on the table.
Types: Not Null, unique, primary key, foreign key, check.
Not Null: does not allow null values.
example: create table emp( empid number(4) unique);
Primary Key: It is used for maintaining the relation between two tables.
Also, it is a condition or limitation on the table.
In a single table, we can apply one pk.
create table emp(empid number(2), salary number(5), ename varchar2(10) ,deptid number(2),
constraint emp_pk primary key(empid), constraint dept_key foreign key(deptid) references dept(deptid));
Foriegn Key: It is a referential integrity key between two tables.
A table can contain one or more foreign keys. Foreign key will allow null values and duplicate values.
Check constraint: It is a constraint or condition or limitation on a table. It will enforce the users to validate the data before storing in the database.
example: create table emp(empid number(2), salary number(5), ename varchar2(10), constraint emp_chk check(salary > 500));
Logical,relational,special operators like 'like', in , between and is.
Set Operators:
Union: It returns all the unique values from both the tables.
Union all: It returns all the values from both the tables.
Intersect: It returns all the values common in both the tables.
Minus: It returns values which are there in one table but not there in another table.
NVL: It is a function used for handling null values while performing arithmetic operations.
select sal,comm, sal + NVL(comm,0) as "Total Sal" from emp;
- To do - functions
select statement syntax:
- select * from table where groupy having orderby
- To do - views
- Indexes are used for quick retrieval of data.
create index <indexname> on table name (colname) ;
drop index <index name>;
- To do - Normalization.
- To do - subqueries.
- Joins: are used for fetching the data from more than one table. To write a join, two tables must have a common column.
Types of Joins:
a. Inner Join
b. Outer Join
c. Self Join
- Inner Join is used in order to fetch matched records from both the tables.
example:
select * from table1 inner join table2 on
table1.commoncolumn = table2.commoncolumn;
- Outer Join
a.Left Outer Join
b.Right Outer Join
c.Full Outer Join
Left Outer Join:
select * from table1 left outer join table2 on
table1.commoncolumn = table2.commoncolumn;
fetches matched records from both the tables and non matched records from left side table.
b. Right Outer Join:
select * from table1 right outer join table2 on
table1.commoncolumn = table2.commoncolumn;
fetches matched records from both the tables and non matched records from right side table.
c.Full Outer Join: used for fetching matched and non-matched records from both the tables.
select * from table1 full outer join table2 on
table1.commoncolumn = table2.commoncolumn;
- Self Join: is used to apply the join condition on the same table.
example: Display the employees who are earning same salaries.
select A.* from emp A, emp B where
A.sal = B.sal and A.empno <> B.empno;
- Distinct: is the function used to fetch unique values.
What is the differences between distinct and unique?
- unique is used for storing the unique values.
Correlated subqueries: Its a combination of subqueries and joins.
Pseudo Columns:
a.rowid - a unique address for each and every row in the database. rowid reference always starts with zero.It is static value.
b.rownum - It is a sequential number generated by the database as and when user fetches the data from database.rownumber reference always starts with one.It is dynamic value.
