DELETE TRUNCATE We can Roll Back the data We cannot Roll Back the data Rows are

Delete truncate we can roll back the data we cannot

This preview shows page 11 - 14 out of 107 pages.

DELETE TRUNCATE We 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 11 of 107
Image of page 11
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 copy Create table emp2 AS select * from emp where deptno = 30; // only deptno = 30 Create table emp3 AS select * from emp where 10; // only deptno =10 is copy Create table emp4 AS select empno, ename, wages, deptno from emp where deptno = 20; //empno,ename,wages,deptno is coped by emp4 table Create table emp5 AS select *from emp where 1=2; //This mean total table copy Select * from emp where 1 = 1; Select * from ‘malli’ from emp; ***Right click properties options and select quick edit modifier and ok. / Run the same query ED Open the Buffer command SET NUM 5 SCOTT 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 user To 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 functions 2. Scalar functions or single row function Page 12 of 107
Image of page 12
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 table vi. 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 functions ii. Number functions iii. Data functions iv. Conversion functions i. Character functions : a. Upper : converts into lower case to upper case.
Image of page 13
Image of page 14

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture