50%(4)2 out of 4 people found this document helpful
This preview shows page 11 - 14 out of 107 pages.
DELETETRUNCATEWe can Roll Back the data. * We cannot Roll Back the data.Rows are deleting temporally. * Rows are deleting permanently.Where clause can be used. * Where clause cannot be used.Delete is sub language DML. * Delete is sub language DDL. 5) Rename: This command is used to change the table name.Syntax: RENAME <OLD_TABLE_NAME> TO <NEW_TABLE_NAME>;Ex: Rename student To student1;Rename SALGRADE To GRADE;NOTE: When we use a Truncate command the table gets dropped and re-created. As the structure is effected is called as a DDL command.Page 11of 107
All DDL command are permanent.All DML command are Temporary.*To see list of all the tables of a user: Select * from TAB;*Creating duplicate tables or backup tables: By using the combination of create and select. We can create copy of a table.Ex:Create table emp1 AS select * from emp; //total table copyCreate table emp2 AS select * from emp where deptno = 30; // only deptno = 30Create table emp3 AS select * from emp where 10; // only deptno =10 is copyCreate table emp4 AS select empno, ename, wages, deptno from emp where deptno = 20; //empno,ename,wages,deptno is coped by emp4 tableCreate table emp5 AS select *from emp where 1=2; //This mean total table copySelect * from emp where 1 = 1;Select * from ‘malli’ from emp;***Right clickpropertiesoptions and select quick edit modifier and ok./Run the same queryEDOpen the Buffer commandSET NUM 5SCOTT is a new user***Creating a new user: Connect in the database AS DBA.*user_name: /AS SYSDBA*create user: create user malli Identified by malli123; //user and password created*giving permissions to the user;*GRANT CONNECT, RESOURCE TO malli; //Grant Succeeded*SHOW userTo connect the current user.**FUNCTIONS: Functions will manuplate the data items and gives the result. They are two types of functions. 1.Group functions or multiple row functions2.Scalar functions or single row functionPage 12of 107
1)Group functions or multiple row functions: This functions act on group of rows.i.AVG: select AVG(sal) from emp; ii.SUM: select SUM(sal) from emp;iii.MAX: select MAX(sal) from emp;iv.MIN: select MIN(sal) from emp;v.COUNT(*): select COUNT(*) from emp; //Return total no.of rows in the tablevi.COUNT(EXPR): Return no.of values present in the column.Ex: Select COUNT(sal) from emp;Select COUNT(empno) from emp;Select COUNT(comm) from emp; Dual table: It is a dummy table which is generally used to perform some calculation is seeing to the system date and etc. Dual table is collection of one row and one column with ‘X’ in it.Ex: Select SYSDATE from dual;Select 10+20 from dual;Select 20+40, 50+60 from dual;2)Scalar functions or single row functions: Scalar function are decided into four types. They are given that.i.Character functionsii.Number functionsiii.Data functionsiv.Conversion functionsi.Character functions: a.Upper: converts into lower case to upper case.