CS 2258 DBMS LAB MANUAL

VALLIAMMAI ENGINEERING COLLEGE SRM NAGAR KACHEEPURAM DISTRICT, TAMIL NADU .

LAB MANUAL FOR CSE II YR

SUB NAME: DATABASE MANAGEMENT SYSTEMS LAB SUB CODE: CS2258

VALLIAMMAI ENGINEERING COLLEGE CHENNAI 603 203

(as per anna university syllabus under 2008 regulations)

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL

LIST OF EXPERIMENTS
1. To implement Data Definition language 1.1. Create, alter, drop, truncate 1.2. To implement Constraints. 1.2.1. (a). Primary key, (b).Foreign Key, (c). Check, (d). Unique, (e). Null, (f). Not null , (g) . Default, (h). Enable Constraints, (i). Disable Constraints (j). Drop Constraints 2. To implementation on DML, TCL and DRL 2.1. (a).Insert, (b).Select, (c).Update, (d).Delete, (e).commit, (f).rollback, (g).save point, (i). Like'%', (j).Relational Operator. 3. To implement Nested Queries & Join Queries 3.1.(a). To implementation of Nested Queries 3.2.(b). (a) Inner join, (b).Left join, (c).Right join (d).Full join 4. To implement Views 4.1. (a). View, (b).joint view, (c).force view, (d). View with check option 5(a). Control Structure 5.1. To write a PL/SQL block for Addition of Two Numbers 5.2. To write a PL/SQL block for IF Condition 5.3. To write a PL/SQL block for IF and else condition 5.4. To write a PL/SQL block for greatest of three numbers using IF AND ELSEIF 5.5. To write a PL/SQL block for summation of odd numbers using for LOOP 5. (b).Procedures VALLIAMMAI ENGINNERING COLLEGE 2

CS 2258 DBMS LAB MANUAL 5.6. To write a PL/SQL Procedure using Positional Parameters 5.7. To write a PL/SQL Procedure using notational parameters 5.8. To write a PL/SQL Procedure for GCD Numbers 5.9. To write a PL/SQL Procedure for cursor implementation 5.10. To write a PL/SQL Procedure for explicit cursors implementation 5.11. To write a PL/SQL Procedure for implicit cursors implementation 5. (c). Functions: 5.13. To write a PL/SQL block to implementation of factorial using function 5.12. To write a PL/SQL function to search an address from the given database 6. Front End Tools 6.1. To design a form using different tools in Visual Basic 7. Form 7.1. To design a Single Document Interface and Multiple Document Interface forms using Visual Basic. 8. Trigger: 8.1. To write a Trigger to pop-up the DML operations 8.2. To write a Trigger to check the age valid or not Using Message Alert. 8.3. Create a Trigger for Raise appropriate error code and error message. 8.4. Create a Trigger for a table it will update another table while inserting values 9. Menu Design 9.1. To design a Note Pad Application menu using Visual Basic. 10. Report design 10.1. To design a report using Visual Basic. 11. To design the Database and Implement it by using VB (Mini Project). 11.1. PASSPORT AUTOMATION SYSTEM VALLIAMMAI ENGINNERING COLLEGE 3

CS 2258 DBMS LAB MANUAL

DBMS MANUAL EX: NO: 1 DATA DEFINITION LANGUAGE (DDL) COMMANDS IN RDBMS AIM: To execute and verify the Data Definition Language commands and constraints DDL (DATA DEFINITION LANGUAGE) CREATE ALTER DROP TRUNCATE COMMENT RENAME

PROCEDURE STEP 1: Start STEP 2: Create the table with its essential attributes. STEP 3: Execute different Commands and extract information from the table. STEP 4: Stop SQL COMMANDS 1. COMMAND NAME: CREATE COMMAND DESCRIPTION: CREATE command is used to create objects in the database. 2. COMMAND NAME: DROP COMMAND DESCRIPTION: DROP command is used to delete the object from the database.

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL 3. COMMAND NAME: TRUNCATE COMMAND DESCRIPTION: TRUNCATE command is used to remove all the records from the table

4. COMMAND NAME: ALTER COMMAND DESCRIPTION: ALTER command is used to alter the database 5. COMMAND NAME: RENAME COMMAND DESCRIPTION: RENAME command is used to rename the objects. QUERY: 01 Q1. Write a query to create a table employee with empno, ename, designation, and salary. Syntax for creating a table: SQL: CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), COLUMN NAME.1 <DATATYPE> (SIZE) ); Output: Table created QUERY: 02 Q2. Write a query to display the column name and datatype of the table employee. Syntax for describe the table: SQL: DESC <TABLE NAME>; SQL> DESC EMP; Output: QUERY: 03 Q3. Write a query for create a from an existing table with all the fields Syntax For Create A from An Existing Table With All Fields structure of

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL SQL> CREATE TABLE <TRAGET TABLE NAME> SELECT * FROM <SOURCE TABLE NAME>; QUERY: 03 SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP; Table created. QUERY: 04 Q4. Write a query for create a from an existing table with selected fields Syntax For Create A from An Existing Table With Selected Fields SQL> CREATE TABLE <TRAGET TABLE NAME> SELECT EMPNO, ENAME FROM <SOURCE TABLE NAME>; QUERY: 05 Q5. Write a query for create a new table from an existing table without any record: Syntax for create a new table from an existing table without any record: SQL> CREATE TABLE <TRAGET TABLE NAME> AS SELECT * FROM <SOURCE TABLE NAME> WHERE <FALSE CONDITION>; ALTER & MODIFICATION ON TABLE QUERY: 06 Q6. Write a Query to Alter the column EMPNO NUMBER (4) TO EMPNO NUMBER (6). Syntax for Alter & Modify on a Single Column: SQL > ALTER <TABLE NAME> MODIFY <COLUMN NAME> <DATATYPE> (SIZE); QUERY: 07 Q7. Write a Query to Alter the table employee with multiple columns (EMPNO, ENAME.) Syntax for alter table with multiple column:

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL SQL > ALTER <TABLE NAME> MODIFY <COLUMN NAME1> <DATATYPE> (SIZE), MODIFY <COLUMN NAME2> <DATATYPE> (SIZE) .; QUERY: 08 Q8. Write a query to add a new column in to employee Syntax for add a new column: SQL> ALTER TABLE <TABLE NAME> ADD (<COLUMN NAME> <DATA TYPE> <SIZE>);

QUERY: 09 Q9. Write a query to add multiple columns in to employee Syntax for add a new column: SQL> ALTER TABLE <TABLE NAME> ADD (<COLUMN NAME1> <DATA TYPE> <SIZE>,(<COLUMN NAME2> <DATA TYPE> <SIZE>, ); REMOVE / DROP QUERY: 10 Q10. Write a query to drop a column from an existing table employee Syntax for add a new column: SQL> ALTER TABLE <TABLE NAME> DROP COLUMN <COLUMN NAME>; QUERY: 11 Q10. Write a query to drop multiple columns from employee Syntax for add a new column: SQL> ALTER TABLE <TABLE NAME> DROP <COLUMN NAME1>,<COLUMN NAME2>,.. ; REMOVE

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL QUERY: 12 Q10. Write a query to rename table emp to employee Syntax for add a new column: SQL> ALTER TABLE RENAME <OLD NAME> TO <NEW NAME>

CONSTRAINTS
Constraints are part of the table definition that limits and restriction on the value entered into its columns. TYPES OF CONSTRAINTS: 1) 2) 3) 4) 5) 6) 7) Primary key Foreign key/references Check Unique Not null Null Default

CONSTRAINTS CAN BE CREATED IN THREE WAYS: 1) Column level constraints 2) Table level constraints 3) Using DDL statements-alter table command OPERATION ON CONSTRAINT: i) ENABLE ii) DISABLE iii) DROP Column level constraints Using Primary key Q13. Write a query to create primary constraints with column level

Primary key
Syntax for Column level constraints Using Primary key:

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE)<TYPE OF CONSTRAINTS> , COLUMN NAME.1 <DATATYPE> (SIZE) ); Column level constraints Using Primary key with naming convention Q14. Write a query to create primary constraints with column level with naming convention Syntax for Column level constraints Using Primary key: SQL: >CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE)CONSTRAINTS <NAME OF THE CONSTRAINTS> <TYPE OF THE CONSTRAINTS> , COLUMN NAME.1 <DATATYPE> (SIZE) ); Table Level Primary Key Constraints Q15. Write a query to create primary constraints with table level with naming convention Syntax for Table level constraints Using Primary key: SQL: >CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE) , COLUMN NAME.1 <DATATYPE> (SIZE), CONSTRAINTS <NAME OF THE CONSTRAINTS> <TYPE OF THE CONSTRAINTS>); Table level constraint with alter command (primary key): Q16. Write a query to create primary constraints with alter command Syntax for Column level constraints Using Primary key: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), COLUMN NAME.1 <DATATYPE> (SIZE) ); SQL> ALTER TABLE <TABLE NAME> ADD CONSTRAINTS <NAME OF THE CONSTRAINTS> <TYPE OF THE CONSTRAINTS> <COLUMN NAME>);

Reference /foreign key constraint

Column level foreign key constraint:

VALLIAMMAI ENGINNERING COLLEGE

CS 2258 DBMS LAB MANUAL Q.17. Write a query to create foreign key constraints with column level Parent Table: Syntax for Column level constraints Using Primary key: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE)<TYPE OF CONSTRAINTS> , COLUMN NAME.1 <DATATYPE> (SIZE) ); Child Table: Syntax for Column level constraints Using foreign key: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), COLUMN NAME2 <DATATYPE> (SIZE) REFERENCES <TABLE NAME> (COLUMN NAME> );

Column level foreign key constraint with naming conversions: Parent Table: Syntax for Column level constraints Using Primary key: Q.18. Write a query to create foreign key constraints with column level SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE)<TYPE OF CONSTRAINTS> , COLUMN NAME.1 <DATATYPE> (SIZE) ); Child Table: Syntax for Column level constraints using foreign key: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE) , COLUMN NAME2 <DATATYPE> (SIZE) CONSTRAINT <CONST. NAME> REFERENCES <TABLE NAME> (COLUMN NAME> ); Table Level Foreign Key Constraints Q.19. Write a query to create foreign key constraints with Table level Parent Table: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE)<TYPE OF CONSTRAINTS> , COLUMN NAME.1 <DATATYPE> (SIZE) );

VALLIAMMAI ENGINNERING COLLEGE

10

CS 2258 DBMS LAB MANUAL Child Table: Syntax for Table level constraints using foreign key: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), COLUMN NAME2 <DATATYPE> (SIZE), CONSTRAINT <CONST. NAME> REFERENCES <TABLE NAME> (COLUMN NAME> ); Table Level Foreign Key Constraints with Alter command Q.20. Write a query to create foreign key constraints with Table level with alter command. Parent Table: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE)<TYPE OF CONSTRAINTS> , COLUMN NAME.1 <DATATYPE> (SIZE) );

Child Table: Syntax for Table level constraints using foreign key: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE) , COLUMN NAME2 <DATATYPE> (SIZE)); SQL> ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONST. NAME> REFERENCES <TABLE NAME> (COLUMN NAME>);

Check constraint
Column Level Check Constraint Q.21. Write a query to create Check constraints with column level Syntax for clumn level constraints using Check: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE) CONSTRAINT <CONSTRAINTS NAME> <TYPE OF CONSTRAINTS> (CONSTRAITNS CRITERIA) , COLUMN NAME2 <DATATYPE> (SIZE)); Table Level Check Constraint: Q.22. Write a query to create Check constraints with table level VALLIAMMAI ENGINNERING COLLEGE 11

CS 2258 DBMS LAB MANUAL Syntax for Table level constraints using Check: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), (COLUMN NAME2 <DATATYPE> (SIZE), CONSTRAINT <CONSTRAINTS NAME> <TYPE OF CONSTRAINTS> (CONSTRAITNS CRITERIA)) ; Check Constraint with Alter Command Q.23. Write a query to create Check constraints with table level using alter command. Syntax for Table level constraints using Check: SQL:>CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), (COLUMN NAME2 <DATATYPE> (SIZE), CONSTRAINT <CONSTRAINTS NAME> <TYPE OF CONSTRAINTS> (CONSTRAITNS CRITERIA)) ;

Unique Constraint
Column Level Constraint Q.24. Write a query to create unique constraints with column level Syntax for Column level constraints with Unique: SQL :> CREATE <OBJ.TYPE> <OBJ.NAME> (<COLUMN NAME.1> <DATATYPE> (SIZE) CONSTRAINT <NAME OF CONSTRAINTS> <CONSTRAINT TYPE>, (COLUMN NAME2 <DATATYPE> (SIZE)) ; Table Level Constraint Q.25. Write a query to create unique constraints with table level Syntax for Table level constraints with Unique: SQL :> CREATE <OBJ.TYPE> <OBJ.NAME> (<COLUMN NAME.1> <DATATYPE> (SIZE), (COLUMN NAME2 <DATATYPE> (SIZE), CONSTRAINT <NAME OF CONSTRAINTS> <CONSTRAINT TYPE>(COLUMN NAME);) ; Table Level Constraint Alter Command Q.26. Write a query to create unique constraints with table level Syntax for Table level constraints with Check Using Alter SQL :> CREATE <OBJ.TYPE> <OBJ.NAME> (<COLUMN NAME.1> <DATATYPE> (SIZE), (COLUMN NAME2 <DATATYPE> (SIZE)) ;

VALLIAMMAI ENGINNERING COLLEGE

12

CS 2258 DBMS LAB MANUAL SQL> ALTER TABLE ADD <CONSTRAINTS> <CONSTRAINTS NAME> <CONSTRAINTS TYPE>(COLUMN NAME);

Not Null
Column Level Constraint Q.27. Write a query to create Not Null constraints with column level Syntax for Column level constraints with Not Null: SQL :> CREATE <OBJ.TYPE> <OBJ.NAME> (<COLUMN NAME.1> <DATATYPE> (SIZE) CONSTRAINT <NAME OF CONSTRAINTS> <CONSTRAINT TYPE>, (COLUMN NAME2 <DATATYPE> (SIZE)) ;

Null Column Level Constraint Q.28. Write a query to create Null constraints with column level Syntax for Column level constraints with Null: SQL :> CREATE <OBJ.TYPE> <OBJ.NAME> (<COLUMN NAME.1> <DATATYPE> (SIZE) CONSTRAINT <NAME OF CONSTRAINTS> <CONSTRAINT TYPE>, (COLUMN NAME2 <DATATYPE> (SIZE)) ;

Constraint Disable \ Enable

Constraint Disable Q.29. Write a query to disable the constraints Syntax for disabling a single constraint in a table: SQL>ALTER TABLE <TABLE-NAME> DISABLE CONSTRAINT <CONSTRAINTNAME> Constraint Enable Q.30. Write a query to enable the constraints Syntax for disabling a single constraint in a table: SQL>ALTER TABLE <TABLE-NAME> DISABLE CONSTRAINT <CONSTRAINTNAME>

VALLIAMMAI ENGINNERING COLLEGE

13

CS 2258 DBMS LAB MANUAL

Result:-Thus the given DDL commands in RDMS is executed and its output is verified.

EX: NO: 2 To implementation on DML and DCL Commands in RDBMS

AIM: To execute and verify the DML and TCL Language commands DML (DATA MANIPULATION LANGUAGE) SELECT INSERT DELETE UPDATE

TCL (TRANSACTION CONTROL LANGUAGE)

COMMIT ROLL BACK SAVE POINT

PROCEDURE STEP 1: Start STEP 2: Create the table with its essential attributes.

VALLIAMMAI ENGINNERING COLLEGE

14

CS 2258 DBMS LAB MANUAL STEP 3: Insert the record into table STEP 4: Update the existing records into the table STEP 5: Delete the records in to the table STEP 6: use save point if any changes occur in any portion of the record to undo its original state. STEP 7: use rollback for completely undo the records STEP 6: use commit for permanently save the records. SQL COMMANDS 1. COMMAND NAME: INSERT COMMAND DESCRIPTION: INSERT command is used to Insert objects in the database. 2. COMMAND NAME: SELECT COMMAND DESCRIPTION: SELECT command is used to SELECT the object from the database. 3. COMMAND NAME: UPDATE COMMAND DESCRIPTION: UPDATE command is used to UPDATE the records from the table 4. COMMAND NAME: DELETE COMMAND DESCRIPTION: DELETE command is used to DELETE the Records form the table 5. COMMAND NAME: COMMIT COMMAND DESCRIPTION: COMMIT command is used to save the Records. 6. COMMAND NAME: ROLLBACK COMMAND DESCRIPTION: ROLL BACK command is used to undo the Records. 6. COMMAND NAME: SAVE POINT COMMAND DESCRIPTION: SAVE POINT command is used to undo the Records in a particular transaction. VALLIAMMAI ENGINNERING COLLEGE 15

CS 2258 DBMS LAB MANUAL INSERT QUERY: 01 Q1. Write a query to insert the records in to employee. Syntax for Insert Records in to a table: SQL :> INSERT INTO <TABLE NAME> VALUES< VAL1, VAL2,..);

SELECT QUERY: 02 Q2. Write a query to display the records from employee. Syntax for select Records from the table: SQL> SELECT * FROM <TABLE NAME>;

INSERT A RECORD USING SUBSITUTION METHOD QUERY: 03 Q3. Write a query to insert the records in to employee using substitution method. Syntax for Insert Records into the table: SQL :> INSERT INTO <TABLE NAME> VALUES< &column name, &column name 2,..);

UPDATE QUERY: 04 Q4. Write a query to update the records from employee.

VALLIAMMAI ENGINNERING COLLEGE

16

CS 2258 DBMS LAB MANUAL Syntax for update Records from the table: SQL> UPDATE <<TABLE NAME> SET <COLUMNANE>=<VALUE> WHERE <COLUMN NAME=<VALUE>; UPDATE MULTIPLE COLUMNS QUERY: 05 Q5. Write a query to update multiple records from employee. Syntax for update multiple Records from the table: SQL> UPDATE <<TABLE NAME> SET <COLUMNANE>=<VALUE> WHERE <COLUMN NAME=<VALUE>;

DELETE
QUERY: 06 Q6. Write a query to delete records from employee. Syntax for delete Records from the table: SQL> DELETE <TABLE NAME> WHERE <COLUMN NAME>=<VALUE>;

TCL(TRANSACTION CONTROL LANGUAGE)

SAVEPOINT: QUERY: 07 Q7. Write a query to implement the save point. Syntax for save point: SQL> SAVEPOINT <SAVE POINT NAME>;

ROLL BACK

VALLIAMMAI ENGINNERING COLLEGE

17

CS 2258 DBMS LAB MANUAL QUERY: 08 Q8. Write a query to implement the Rollback. Syntax for save point: SQL> ROLL BACK <SAVE POINT NAME>;

COMMIT
QUERY: 09 Q9. Write a query to implement the Rollback. Syntax for commit: SQL> COMMIT;

DCL (DATA CONTROL LANGUAGE)

CREATING A USER SQL>CONNECT SYSTEM/MANAGER; SQL>CREATE USER "USERNAME" IDENTIFIED BY "PASSWORD" SQL>GRANT DBA TO "USERNAME" SQL>CONNECT "USERNAME"/"PASSWORD"; EXAMPLE CREATING A USER SQL>CONNECT SYSTEM/MANAGER; SQL>CREATE USER CSE2 IDENTIFIED BY CSECSE; SQL>GRANT DBA TO CSE2; SQL>CONNECT CSE2/CSECSE; SQL>REVOKE DBA FROM CSE2;

VALLIAMMAI ENGINNERING COLLEGE

18

CS 2258 DBMS LAB MANUAL

Result:-Thus the given program to implement DML & DCL in RDBMS is executed & its output is verified.

EX: NO: 3
EX: NO: 3 A
AIM

NESTED QUERIES AND JOIN QUERIES Nested Queries

To execute and verify the SQL commands for Nested Queries. OBJECTIVE: Nested Query can have more than one level of nesting in one single query. A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query.
PROCEDURE

STEP 1: Start STEP 2: Create two different tables with its essential attributes. STEP 3: Insert attribute values into the table. STEP 4: Create the Nested query from the above created table.

VALLIAMMAI ENGINNERING COLLEGE

19

CS 2258 DBMS LAB MANUAL STEP 5: Execute Command and extract information from the tables. STEP 6: Stop SQL COMMANDS 1. COMMAND NAME: SELECT COMMAND DESCRIPTION: SELECT command is used to select records from the table. 2. COMMAND NAME: WHERE COMMAND DESCRIPTION: WHERE command is used to identify particular elements. 3. COMMAND NAME: HAVING COMMAND DESCRIPTION: HAVING command is used to identify particular elements. 4. COMMAND NAME: MIN (SAL) COMMAND DESCRIPTION: MIN (SAL) command is used to find minimum salary.
Table -1 SYNTAX FOR CREATING A TABLE: SQL: CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), COLUMN NAME.1 <DATATYPE> (SIZE) );

SYNTAX FOR INSERTING RECORDS IN TO A TABLE: SQL :> INSERT INTO <TABLE NAME> VALUES< VAL1, VAL2,..);

SYNTAX FOR SELECT RECORDS FROM THE TABLE: SQL> SELECT * FROM <TABLE NAME>;

TABLE- 2 SYNTAX FOR CREATING A TABLE: SQL: CREATE <OBJ.TYPE> <OBJ.NAME> (COLUMN NAME.1 <DATATYPE> (SIZE), COLUMN NAME.1 <DATATYPE> (SIZE) );

VALLIAMMAI ENGINNERING COLLEGE

20

CS 2258 DBMS LAB MANUAL

SYNTAX FOR INSERT RECORDS IN TO A TABLE: SQL :> INSERT INTO <TABLE NAME> VALUES< VAL1, VAL2,..);

SYNTAX FOR SELECT RECORDS FROM THE TABLE: SQL> SELECT * FROM <TABLE NAME>;

GENERAL SYNTAX FOR NESTED QUERY:

SELECT "COLUMN_NAME1" FROM "TABLE_NAME1" WHERE "COLUMN_NAME2" [COMPARISON OPERATOR] (SELECT "COLUMN_NAME3" FROM "TABLE_NAME2" WHERE [CONDITION])

SYNTAX NESTED QUERY STATEMENT: SQL> SELECT <COLUMN_NAME> FROM FRORM <TABLE _1> WHERE <COLUMN_NAME> <RELATIONAL _OPERATION> VALUE (SELECT (AGGRECATE FUNCTION) FROM <TABLE_1> WHERE <COLUMN NAME> = VALUE (SELECT <COLUMN_NAME> FROM <TABLE_2> WHERE <COLUMN_NAME= VALUE)); NESTED QUERY STATEMENT:

VALLIAMMAI ENGINNERING COLLEGE

21

CS 2258 DBMS LAB MANUAL

SQL> SELECT ENAME FROM EMP2 WHERE SAL> (SELECT MIN(SAL) FROM EMP2 WHERE DPTNO= (SELECT DEPTNO FROM DEPT2 WHERE LOCATION='UK'));

RESULT: Thus the SQL commands for nested and join has been verified and executed successfully.

EX: NO: 3 B
AIM

JOINS

To execute and verify the SQL commands using Join queries. OBJECTIVE: SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. PROCEDURE STEP 1: Start STEP 2: Create the table with its essential attributes. STEP 3: Insert attribute values into the table STEP 4: Execute different Commands and extract information from the table. STEP 5: Stop SQL COMMANDS VALLIAMMAI ENGINNERING COLLEGE 22

CS 2258 DBMS LAB MANUAL 1. COMMAND NAME: INNER JOIN COMMAND DESCRIPTION: The INNER JOIN keyword return rows when there is at least one match in both tables. 2. COMMAND NAME: LEFT JOIN COMMAND DESCRIPTION: The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). 3. COMMAND NAME: RIGHT JOIN COMMAND DESCRIPTION: The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1). 4. COMMAND NAME : FULL JOIN COMMAND DESCRIPTION: The FULL JOIN keyword return rows when there is a match in one of the tables.

VALLIAMMAI ENGINNERING COLLEGE

23

CS 2258 DBMS LAB MANUAL

INNER JOIN SYNTAX

INNTER JOIN SYNTAX SQL>SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

LEFT JOIN SYNTAX

SQL> SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

RIGHT JOIN
SQL>SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

FULL JOIN SYNTAX

SQL>SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

Result:- Thus the given program for joins is executed and its output is verified.

VALLIAMMAI ENGINNERING COLLEGE

24

CS 2258 DBMS LAB MANUAL

EX: NO: 4
AIM

VIEWS

To execute and verify the SQL commands for Views. OBJECTIVE: Views Helps to encapsulate complex query and make it reusable. Provides user security on each view - it depends on your data policy security. Using view to convert units - if you have a financial data in US currency, you can create view to convert them into Euro for viewing in Euro currency.

PROCEDURE STEP 1: Start STEP 2: Create the table with its essential attributes. STEP 3: Insert attribute values into the table. STEP 4: Create the view from the above created table. STEP 5: Execute different Commands and extract information from the View. STEP 6: Stop SQL COMMANDS 1. COMMAND NAME: CREATE VIEW COMMAND DESCRIPTION: CREATE VIEW command is used to define a view. 2. COMMAND NAME: INSERT IN VIEW COMMAND DESCRIPTION: INSERT command is used to insert a new row into the view. 3. COMMAND NAME: DELETE IN VIEW COMMAND DESCRIPTION: DELETE command is used to delete a row from the view. 4. COMMAND NAME: UPDATE OF VIEW COMMAND DESCRIPTION: UPDATE command is used to change a value in a tuple without changing all values in the tuple. 5. COMMAND NAME: DROP OF VIEW COMMAND DESCRIPTION: DROP command is used to drop the view table

VALLIAMMAI ENGINNERING COLLEGE

25

CS 2258 DBMS LAB MANUAL

COMMANDS EXECUTION SYNTAX FOR CREATION OF TABLE SQL> CREATE TABLE EMPLOYEE ( EMPLOYEE_NAMEVARCHAR2(10), EMPLOYEE_NONUMBER(8), DEPT_NAME VARCHAR2(10), DEPT_NO NUMBER (5),DATE_OF_JOIN DATE); Table created.

SYNTAX FOR CREATION OF VIEW SQL> CREATE <VIEW> <VIEW NAME> AS SELECT <COLUMN_NAME_1>, <COLUMN_NAME_2> FROM <TABLE NAME>;

INSERTION INTO VIEW INSERT STATEMENT: SYNTAX: SQL> INSERT INTO <VIEW_NAME> (COLUMN NAME1,) VALUES(VALUE1,.); DELETION OF VIEW: DELETE STATEMENT: SYNTAX: SQL> DELETE <VIEW_NMAE>WHERE <COLUMN NMAE> =VALUE;

UPDATE STATEMENT: SYNTAX: AQL>UPDATE <VIEW_NAME> SET< COLUMN NAME> = <COLUMN NAME> +<VIEW> WHERE <COLUMNNAME>=VALUE; DROP A VIEW: SYNTAX: SQL> DROP VIEW <VIEW_NAME>

VALLIAMMAI ENGINNERING COLLEGE

26

CS 2258 DBMS LAB MANUAL

CREATE A VIEW WITH SELECTED FIELDS: SYNTAX: SQL>CREATE [OR REPLACE] VIEW <VIEW NAME>AS SELECT <COLUMN NAME1>..FROM <TABLE ANME>; Note: Replace is the keyboard to avoid the error ora_0095:name is already used by an existing abject. JOIN VIEW: SQL> CREATE OR REPLACE VIEW DEPT_EMP AS SELECT A.EMPNO "EID",A.ENAME "EMPNAME",A.DEPTNO "DNO",B.DNAM E "D_NAME",B.LOC "D_LOC" FROM EMPL A,DEPMT B WHERE A.DEPTNO=B.DEPTNO; VIEW READ ONLY AND CHECK OPTION: READ ONLY CLAUSE: You can create a view with read only option which enable other to only query .no dml operation can be performed to this type of a view.

FORCE VIEW
SYNTAX SQL> CREATE OR REPLACE FORCE VIEW MYVIEW AS SELECT * FROM XYZ;

COMPILING A VIEW
SYNTAX: ALTER VIEW <VIEW_NAME> COMPILE;

RESULT: Thus the SQL commands for View has been verified and executed successfully.

VALLIAMMAI ENGINNERING COLLEGE

27

CS 2258 DBMS LAB MANUAL

EX: NO: 5 A
AIM

CONTROL STRCTURE

To write a PL/SQL block using different control (if, if else, for loop, while loop,) statements. OBJECTIVE: PL/SQL Control Structure provides conditional tests, loops, flow control and branches that let to produce well-structured programs. Addition of Two Numbers: 1. Write a PL/SQL Program for Addition of Two Numbers PROCEDURE STEP 1: Start STEP 2: Initialize the necessary variables. STEP 3: Develop the set of statements with the essential operational parameters. STEP 4: Specify the Individual operation to be carried out. STEP 5: Execute the statements. STEP 6: Stop.

PL/ SQL General Syntax SQL> DECLARE <VARIABLE DECLARATION>; BEGIN <EXECUTABLE STATEMENT >; END;

VALLIAMMAI ENGINNERING COLLEGE

28

CS 2258 DBMS LAB MANUAL

PL/ SQL GENERAL SYNTAX FOR IF CONDITION: SQL> DECLARE <VARIABLE DECLARATION>; BEGIN IF(CONDITION)THEN <EXECUTABLE STATEMENT >; END;

PL/ SQL GENERAL SYNTAX FOR IF AND ELSECONDITION: SQL> DECLARE <VARIABLE DECLARATION>; BEGIN IF (TEST CONDITION) THEN <STATEMENTS>; ELSE <STATEMENTS>; ENDIF; END;

PL/ SQL GENERAL SYNTAX FOR NESTED IF: SQL> DECLARE <VARIABLE DECLARATION>; BEGIN IF (TEST CONDITION) THEN <STATEMENTS>; ELSEIF (TEST CONDITION) THEN <STATEMENTS>; ELSE <STATEMENTS>; VALLIAMMAI ENGINNERING COLLEGE 29

CS 2258 DBMS LAB MANUAL ENDIF; END;

PL/ SQL GENERAL SYNTAX FOR LOOPING STATEMENT: SQL> DECLARE <VARIABLE DECLARATION>; BEGIN LOOP <STATEMENT>; END LOOP; <EXECUTAVLE STATEMENT>; END; PL/ SQL GENERAL SYNTAX FOR LOOPING STATEMENT: SQL> DECLARE <VARIABLE DECLARATION>; BEGIN WHILE <condition> LOOP <STATEMENT>; END LOOP; <EXECUTABLE STATEMENT>; END;

Result:- Thus, the given PL/SQL block using different control structures is executed and its output is verified.

VALLIAMMAI ENGINNERING COLLEGE

30

CS 2258 DBMS LAB MANUAL

EX: NO:5B
AIM

PROCEDURES

To write a PL/SQL block to display the student name, marks whose average mark is above 60%. ALGORITHM STEP1:Start STEP2:Create a table with table name stud_exam STEP3:Insert the values into the table and Calculate total and average of each student STEP4: Execute the procedure function the student who get above 60%. STEP5: Display the total and average of student STEP6: End EXECUTION SETTING SERVEROUTPUT ON: SQL> SET SERVEROUTPUT ON PROCEDURE USING POSITIONAL PARAMETERS: SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE PROC1 AS 2 BEGIN 3 DBMS_OUTPUT.PUT_LINE('Hello from procedure...'); 4 END; 5 /

Result:-Thus the given program to execute procedures is executed and its output is verified.

VALLIAMMAI ENGINNERING COLLEGE

31

CS 2258 DBMS LAB MANUAL

EX: NO: 5C
AIM

FUNCTIONS

To write a Functional procedure to search an address from the given database. PROCEDURE STEP 1: Start STEP 2: Create the table with essential attributes. STEP 3: Initialize the Function to carryout the searching procedure.. STEP 4: Frame the searching procedure for both positive and negative searching. STEP 5: Execute the Function for both positive and negative result . STEP 6: Stop

RESULT:Thus the Function for searching process has been executed successfully.

VALLIAMMAI ENGINNERING COLLEGE

32

CS 2258 DBMS LAB MANUAL

EX:NO:6
AIM

FRONT END TOOLS

To design a form using different tools in Visual Basic. PROCEDURE STEP 1: Start STEP 2: Create the form with essential controls in tool box. STEP 3: Write the code for doing the appropriate functions. STEP 4: Save the forms and project. STEP 5: Execute the form . STEP 6: Stop

RESULT: Thus the program has been loaded and executed successfully.

VALLIAMMAI ENGINNERING COLLEGE

33

CS 2258 DBMS LAB MANUAL

EX: NO:7
AIM

FORM DESIGN

To design a Single Document Interface and Multiple Document Interface forms using Visual Basic. PROCEDURE STEP 1: Start STEP 2: Create the form with essential controls in tool box. STEP 3: Write the code for doing the appropriate functions. STEP 4: Save the forms and project. STEP 5: Execute the form. STEP 6: Stop

RESULT: Thus the program has been loaded and executed successfully.

VALLIAMMAI ENGINNERING COLLEGE

34

CS 2258 DBMS LAB MANUAL

EX: NO: 8
AIM

TRIGGER

To develop and execute a Trigger for Before and After update, Delete, Insert operations on a table. PROCEDURE STEP 1: Start STEP 2: Initialize the trigger with specific table id. STEP 3:Specify the operations (update, delete, insert) for which the trigger has to be executed. STEP 4: Execute the Trigger procedure for both Before and After sequences STEP 5: Carryout the operation on the table to check for Trigger execution. STEP 6: Stop

RESULT: Thus the Trigger procedure has been executed successfully for both before and after sequences.

VALLIAMMAI ENGINNERING COLLEGE

35

CS 2258 DBMS LAB MANUAL

EX:NO:9
AIM

MENU DESIGN

To design a Note Pad Application menu using Visual Basic. PROCEDURE STEP 1: Start STEP 2: Create the form with essential controls and insert the menu using menu editor. STEP 3: Write the code for doing the appropriate functions. STEP 4: Save the forms and project. STEP 5: Execute the form. STEP 6: Stop

RESULT: Thus the VB program for Menu design is executed successfully and its output is verified.

VALLIAMMAI ENGINNERING COLLEGE

36

CS 2258 DBMS LAB MANUAL

EX: NO: 10
AIM

REPORT DESIGN

To design a report design using Visual Basic. PROCEDURE STEP 1: Start STEP 2: Create the form with essential controls and insert the menu using menu editor. STEP 3: Write the code for doing the appropriate functions. STEP 4: Save the forms and project. STEP 5: Execute the form and generate report STEP 6: Stop

RESULT: Thus the VB program for report design has been loaded and executed successfully.

VALLIAMMAI ENGINNERING COLLEGE

37

CS 2258 DBMS LAB MANUAL

Ex. No. 11.

MINI PROJECT

PASSPORT AUTOMATION SYSTEM

A PROJECT MINI REPORT Submitted by NAME: REG.NO: COMPUTER SCIENCE AND ENGINEERING VALLIAMMAI ENGINEERING COLLEGE, KATTANKULATHUR

ANNA UNIVERSITY: CHENNAI 600 025 MAY 2011

VALLIAMMAI ENGINEERING COLLEGE, KATTANKULATHUR ANNA UNIVERSITY: CHENNAI 600 025

VALLIAMMAI ENGINNERING COLLEGE

38

CS 2258 DBMS LAB MANUAL

ABSTRACT
Earlier the issue of passport was done manually which eventually became hectic as the number of applications saw an exponential rise. Also it took several months for applicants to obtain the passport. Only after this, the concept of computerization in processing of passport applications came into consideration. This software Passport Automation System is used in the effective dispatch of passport. It adopts a comprehensive approach to minimize the manual work and schedule resources and time in a cogent manner. It adopts different strategies to tackle the rise in the number of passport applications. The main objective of the system is to ensure speedy dispatch of passport to the applicants and to minimize the manual work. Technical and practical problems encountered during the development are discussed in this paper, and a thorough performance evaluation of the developed prototype is also presented.

VALLIAMMAI ENGINNERING COLLEGE

39

CS 2258 DBMS LAB MANUAL

TABLE OF CONTENTS
CHAPTER NO. ABSTRACT ACKNOWLEDGEMENT LIST OF TABLES LIST OF FIGURES INTRODUCTION 1.1 About Passport Automation System 1.2 Problem Definition 1.3 Existing System 1.4 Proposed System 1.5 Environment Specification 1.5.1 Hardware Specification 1.5.2 Software Specification 1.6 Technologies used 1.7 Software Engineering Paradigm 1.8 System Planning SYSTEM DESIGN 2.1 Input Design 2.1.1 Input Description 2.2 Database Design 2.3 ER Diagram 2.4 Use case diagram 2.5 Class Diagram 2.6 Activity Diagram 2.7 Sequence Diagram 2.8 Collaboration Diagram 2.9 Component Diagram 2.10 Deployment Diagram SYSTEM DEVELOPMENT 3.1 System Architecture IMPLEMENTATION OF PAS 4.1 Screenshots 4.2 Coding 4.2.1 Code for Opening Screen 4.2.2 Code for Main Page 4.2.3 Code for New Registration 4.2.4 Code for checking status
VALLIAMMAI ENGINNERING COLLEGE

TITLE

PAGE iii iv vii viii

1.

2.

3. 4.

40

CS 2258 DBMS LAB MANUAL

4.2.5 Code for authentication to Admin Panel 4.2.6 Code for Admin Panel 1.2.7 Code for processing application 5. SYSTEM TESTING 5.1 Unit Testing 5.1.1 New registration test case 5.1.2 Check Status test case 5.1.3 Authentication test case 5.1.4 Admin Panel test case 5.2 Integration Testing CONCLUSION REFERENCES

6. 7.

VALLIAMMAI ENGINNERING COLLEGE

41

CS 2258 DBMS LAB MANUAL

LIST OF TABLES
TABLE NO. 2.1 5.1 5.2 5.3 5.4 5.5 TITLE Registrations New Registrations test case Check Status test case Authentication test case Admin Panel test case Integration Testing PAGE

VALLIAMMAI ENGINNERING COLLEGE

42

CS 2258 DBMS LAB MANUAL

LIST OF FIGURES
FIG NO. 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 2.13 2.14 3.1 4.1 4.2 4.3 4.4 4.5 4.6 4.7 TITLE ER Diagram Use case Diagram Class Diagram Activity Diagram for New Registration Activity Diagram for Checking Status Activity Diagram for Admin Panel Sequence Diagram for New Registration Sequence Diagram for Checking Status Sequence Diagram for Admin Panel Collaboration Diagram for New Registration Collaboration Diagram for Checking Status Collaboration Diagram for Admin Panel Component Diagram for PAS Deployment Diagram for PAS Passport Automation System Architecture Startup Screen Main menu New Registration Check Status Authentication Screen Admin Panel Process Application PAGE

VALLIAMMAI ENGINNERING COLLEGE

43

CS 2258 DBMS LAB MANUAL

LIST OF ABBREVIATIONS
PAS VB SDLC ER Passport Automation System Visual Basic Software Development Life Cycle Entity - Relationship

CONCLUSION

VALLIAMMAI ENGINNERING COLLEGE

44

CS 2258 DBMS LAB MANUAL PAS simplifies the manual work load and is helpful in the effective dispatch of passport. The applicant of passport is benefited by the fact that he need not go all the way to the passport office to submit application but can do this from the comfort of his own. The job of the administrator is simplified because of this system. The applicant can check his passport status anytime, anywhere and he need not stand in the enquiry queue. Furthermore, the time taken for the passport to reach the applicant is considerably reduced.

REFERENCES

VALLIAMMAI ENGINNERING COLLEGE

45

CS 2258 DBMS LAB MANUAL

[1] Ali Bahrami, Object oriented Systems development, using the unified modeling language, Tata McGraw Hill edition, pp.400-420, 2008 [2] Gary Cornell, Visual basic 6: from the GROUND UP Build windows and web applications step by step, pp. 9501000 [3] Kevin Loney, Oracle 10g: The complete reference, Master the revolutionary features of oracle, pp. 940-950, 2004 [4] Steven Holzner, Visual Basic black book, Comprehensive problem solver, pp. 1050-1100, 1998.

VALLIAMMAI ENGINNERING COLLEGE

46