SQL Source code for different DML, DDL and PLSQL Queries

Ex.no: 1

CREATION OF TABLE

Creates a table and define its column and other properties. Also contains pending changes to the database.

Syntax:

CREATE TABLE (COLUMN SPEC[NULL|NOT NULL],..);

Here

Spec - Specifies the column’s type and size.

Null - specifies at the fields in this column may be null.

Not Null- Specifies that may not be null.

Examples:

SQL> CREATE TABLE EMP(EMP_NO NUMBER(3) NOT NULL,

ENAME VARCHAR2(20),JOB VARCAHR2(10), HIRE_DATE DATE,

SALARY NUMBER(6,2);

Table created

DESCRIBE A TABLE

Display a brief description of the specified table owned by the specified uses if user is omitted, SQL assumes you own the table.

Syntax:

DESC

Example:

Desc EMP

INSERTION OF TABLE

The insert command inserts one or more rows into a table

Syntax:

INSERT INTO

(COLUMN NAME,…………..) VALUES(VALUE1, VALUE2……..);

OR

INSERT INTO

VALUES(&COULMN NAME, &COULMN NAME…);

Examples:

INSERT INTO EMP VALUES(122,’XYZABC’,’CLERK’,’11-JUL - 2006’,5000.00);

ALTERATION OF TABLES

This command is specifies the alteration of the tables.

Syntax:

ALTER TABLE

[ADD/MODIFY](COLUMN DATA TYPE(SIZE)….);

Examples:

ALTER TABLE EMP ADD(ADDRESS VARCHAR2(30));

ALTER TABLE EMP MODIFY(ENAME VARCHAR2(10),CITY VARCHAR(10));

UPDATION OF TABLES

The UPDATE command of an UPDATE clause followed by a set clause and an WHERE clause.

Syntax:

UPDATE

SET COLUMN-NAME=EXPR [WHERE CONDITION];

Examples:

UPDATE EMP SET SALARY=3000 WHERE JOB=’CLERAK’;

DELETION OF A TABLE

The delete command is used to delete the contents of the fields in a given table.

Syntax:

DELETE FROM

DELETE FROM

WHERE CONDITION

Example:

DELETE FROM EMP;

DELETE FROM EMP WHERE EMP_NO=11;

DROPING A TABLE

The drop command is used to drop the entire function or table

Syntax:

DROP TABLE

Examples:

DROP TABLE EMP;

DATA CONTROL

Ex.no: 2

GRANT :

Provides various types of access to database objects, such as tables,views, sequences.

Syntax:

GRANT {OBJECT-PRIVILEGE}\{ALL}ON[USER] OBJECT TO{USER\PUBLIC} [WITH GRANT OPTION]

Example:

GRANT ALL ON EMP TO SMITH WITH GRANT OPTION;

REVOKE

Revoke privileges from one or more users for tables, views and sequences.

Syntax:

REVOKE{ OBJECT-PRIVILAGE }\ALL ON USER OBJECT FROM [USER\PUBLIC]

CONTROL STRUCTURE

In addition to SQL commands, PL/SQL can also process data using flow of control statements. The flow of control statements can be classified under the following categories:

· Conditional Control

· Iterative Control

· Sequential control

CONDITIONAL CONTROL

Syntax:

IF CONDITION THEN

SEQUENCES OF STATEMENTS;

END IF;

ITREATIVE CONTROL

Syntax:

· Simple loop

· While loop

· For loop

SIMPLE LOOP

Syntax:

LOOP

SEQUENCE OF STATEMENTS’

END LOOP;

Program:

DECLARE

A NUMBER:=100;

BEGIN

LOOP

A:=A+25;

EXIT WHEN A=250;

END LOOP;

WHILE LOOP

Syntax:

WHILE

LOOP

SEQUENCE OF STATEMENTS;

END LOOP;

Program:

DECLARE

I NUMBER:=0;

J NUMBER=0;

BEGIN

WHILE I<=100;

LOOP

J:=J+1;

I:=I+5;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘The value of J=’||J);

END

FOR LOOP

Syntax:

FOR COUNTER IN [REVERSE]LOWER BOUND…..UPPER BOUND

LOOP

SEQUENCES OF STATEMENTS;

END LOOP;

Program:

BEGIN

FOR I IN 1..2

LOOP

UPDATE EMP SET SALARY=SALARY+100 WHERE SALARY>3000.

END LOOP

END;

PL/SQL

Ex.No.6

SQL> desc std_details

Name Null? Type

------------------------------- -------- ----

STD_ID NOT NULL NUMBER(10)

STD_NAME NOT NULL VARCHAR2(20)

STD_MARK NOT NULL NUMBER(5)

STD_REMARK NOT NULL VARCHAR2(10)

SQL>ED std_inf;

SET SERVEROUTPUT ON

prompt Enter id of the Student

accept s

declare

ID STD_DETAILS.STD_ID%TYPE;

NAME STD_DETAILS.STD_NAME%TYPE;

MARK STD_DETAILS.STD_MARK%TYPE;

REM STD_DETAILS.STD_REMARK%TYPE;

BEGIN

SELECT STD_ID,STD_NAME,STD_MARK,STD_REMARK INTO ID,NAME,MARK,REM FROM STD_DETAILS WHERE STD_ID=&S;

DBMS_OUTPUT.PUT_LINE('iD :'||ID);

DBMS_OUTPUT.PUT_LINE('NAME :'||NAME);

DBMS_OUTPUT.PUT_LINE('MARK :'||MARK);

DBMS_OUTPUT.PUT_LINE('REMARK :'||REM);

END;

/

SET SERVEROUTPUT OFF

OUTPUT:

SQL> @ std_inf;

Enter id of the Student

iD :1

NAME : Rahul Dravid

MARK :100

REMARK :PASS