| No | SQL | DESCRIPTION |
|---|---|---|
| 1 | SELECT | DATA RETRIEVAL |
| 2 | INSERT UPDATE DELETE |
DATA MANIPULATION LANGUAGE (DML) |
| 3 | CREATE ALTER DROP RENAME TRANCATE |
DATA DEFINATION LANGUAGE (DDL) |
| 4 | COMMIT ROLLBACK SAVEPOINT RENAME TRANCATE |
TRANSICTION CONTROL LANGUAGE (TCL) |
| 5 | GRAND REVOKE |
DATA CONTROL LANGUAGE (DCL) |
| No | LABs | PRACTICALS |
|---|---|---|
| 1 | LAB-1 | CREATE TABLE |
|
Syntax: CREATE TABLE schema_name.table_name ( column_1 data_type column_constraint, column_2 data_type column_constraint, ... table_constraint ); Example: create table DEPARTMENT ( deptno number, deptname varchar2(50) not null, location varchar2(50), constraint pk_department primary key (deptno) ); create table EMPLOYEE ( empno number, name varchar2(50) not null, jobtitle varchar2(50), manager number, hiredate date, salary number(7,2), commission number(7,2), deptno number, constraint pk_employee primary key (empno), constraint fk_employee_deptno foreign key (deptno) references DEPARTMENT (deptno) ); |
||
| 2 | LAB-2 | ALTER TABLE |
|
It is used to add, MODIFY, or drop columns. Syntax for ADD: ALTER TABLE table_name ADD (column datatype,column datatype....);Example: Suppose we want to ADD new column (location) in existing table ALTER TABLE department ADD location varchar2(50); Syntax for MODIFY: ALTER TABLE table_name MODIFY (column datatype,column datatype....);Example: Suppose we wants to change/modify the size of column location from 50 to 20 ALTER TABLE deptartment MODIFY location varchar(20); Syntax for DROP: ALTER TABLE table_name DROP COLUMN column_name ;Example: Suppose we wants to remove the column location from department table. ALTER TABLE dept DROP COLUMN location Syntax for RENAME COLUMN NAME: ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name ;Example: Suppose we wants to rename/ change the name of column. ALTER TABLE dept RENAME COLUMN location to dept_loc Syntax for RENAME table NAME: RENAME table_name TO new_table_name;Example: Suppose we wants to rename/ change the name of TABLE. RENAME dept TO department; |
||
| 3 | LAB-3 | DROP TABLE |
|
Dorp command will drop/remove the structure of table Syntax: DROP TABLE table_name; Example: DROP TABLE department; |
||
| 4 | LAB-4 | TRUNCATE |
|
We can not ROLLBACK trancated table.It is parmanent deleted from storage. Syntax: TRUNCATE table table_name; Example: TRUNCATE table emp; |
||
| 5 | LAB-5 | Working with Constraints |
|
Following Constraints are used in SQL Type of COnstraints:
1. CREATE TABLE EMPLOYEE( EID NUMBER(6) PRIMARY KEY, ENAME VARCHAR2(50) NOT NULL, SAL NUMBER(9) CHECK(SAL<>>0), AGE NUMBER(2) CHECK(AGE>18) ) 2. CREATE TABLE EMPLOYEE( EID NUMBER(6) CONSTRAINT EMP_EID PRIMARY KEY, ENAME VARCHAR2(50) CONSTRAINT EMP_ENAME NOT NULL, SAL NUMBER(9) CONSTRAINT EMP_SAL CHECK(SAL<>0), AGE NUMBER(2) CONSTRAINT EMP_AGE CHECK(AGE>18) ) 3. CREATE TABLE EMPLOYEE( EID NUMBER(6), ENAME VARCHAR2(50) NOT NULL, SAL NUMBER(9), AGE NUMBER(2), CONSTRAINT EMP_EID_PK PRIMARY KEY (EID), CONSTRAINT EMP_SAL_CK CHECK(SAL<>0), CONSTRAINT EMP_AGE_CK CHECK(AGE>18) ) 4. DEFINE FOREIGN KEY CONTRAINT CREATE TABLE DEPARTMENT( DID NUMBER(6) PRIMARY KEY, DNAME VARCHAR2(50) NOT NULL ) CREATE TABLE EMPLOYEE( EID NUMBER(6) PRIMARY KEY, ENAME VARCHAR2(50) NOT NULL, DID NUMBER(6) REFERENCES DEPARTMRNT(DID) ) OR CREATE TABLE DEPARTMENT( DID NUMBER(6) PRIMARY KEY, DNAME VARCHAR2(50) NOT NULL ) CREATE TABLE EMPLOYEE( EID NUMBER(6) PRIMARY KEY, ENAME VARCHAR2(50) NOT NULL, DID NUMBER(6) CONSTRAINT DID_FK REFERENCES DEPARTMRNT(DID) ) OR CREATE TABLE DEPARTMENT( DID NUMBER(6) PRIMARY KEY, DNAME VARCHAR2(50) NOT NULL ) CREATE TABLE EMPLOYEE( EID NUMBER(6) PRIMARY KEY, ENAME VARCHAR2(50) NOT NULL, DID NUMBER(6), CONSTRAINT EMP_DID_FK FOREIGN KEY (DID) REFERENCES DEPARTMRNT(DID) ) |
||
| 4 | LAB-4 | RENAME |
|
INFO Syntax: Example: |
||
