Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

DBMS Lab Programs for Anna University, Exercises of Database Management Systems (DBMS)

here i have attached DBMS lab manual for your reference

Typology: Exercises

2020/2021

Uploaded on 04/20/2021

vimal-raja
vimal-raja 🇮🇳

2 documents

1 / 87

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
DDL COMMANDS IN DBMS
TYPES OF COMMAND
DDL(DATA DEFINITION LANGUAGE)
DDL (datadefinitionlanguage) statements are used to create ,delete ,or change the objects
of a database.
Typically a database administrator is responsible for using ddl statements or production
databases in a large database system.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME
SQL COMMANDS
1. COMMAND NAME: CREATE
Command is used to create objects in database.
2. COMMAND NAME: ALTER
Command is used to alter the structure of database
3. COMMAND NAME: DROP
Command is used to delete the object from database.
4. COMMAND NAME: TRUNCATE
Command is used to remove all the records in the table including the space allocated for the
table
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29
pf2a
pf2b
pf2c
pf2d
pf2e
pf2f
pf30
pf31
pf32
pf33
pf34
pf35
pf36
pf37
pf38
pf39
pf3a
pf3b
pf3c
pf3d
pf3e
pf3f
pf40
pf41
pf42
pf43
pf44
pf45
pf46
pf47
pf48
pf49
pf4a
pf4b
pf4c
pf4d
pf4e
pf4f
pf50
pf51
pf52
pf53
pf54
pf55
pf56
pf57

Partial preview of the text

Download DBMS Lab Programs for Anna University and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!

DDL COMMANDS IN DBMS

TYPES OF COMMAND

DDL(DATA DEFINITION LANGUAGE)

 DDL (datadefinitionlanguage) statements are used to create ,delete ,or change the objects of a database.  Typically a database administrator is responsible for using ddl statements or production databases in a large database system.  CREATE  ALTER  DROP  TRUNCATE  COMMENT  RENAME SQL COMMANDS

  1. COMMAND NAME: CREATE Command is used to create objects in database.
  2. COMMAND NAME: ALTER Command is used to alter the structure of database
  3. COMMAND NAME: DROP Command is used to delete the object from database.
  4. COMMAND NAME: TRUNCATE Command is used to remove all the records in the table including the space allocated for the table

EXECUTION:

TABLE CREATION:

SQL> create table smp(emp_name varchar2(10),emp_no number(8),dept_name varchar2(10),dept_no number(5),date_of_join date); Table created. SQL> create table emp1(emp_name varchar2(10),emp_no number(8) primary key,dept_name varchar2(10),dept_no number(5),date_of_join date); Table created. TABLE DESCRIPTION: SQL>desc smp; Name Null? Type


EMP_NAME VARCHAR2(10) EMP_NO NUMBER(8) DEPT_NAME VARCHAR2(10) DEPT_NO NUMBER(5) DATE_OF_JOIN DATE SQL>desc emp1; Name Null? Type


EMP_NAME VARCHAR2(10) EMP_NO NOT NULL NUMBER(8) DEPT_NAME VARCHAR2(10) DEPT_NO NUMBER(5) DATE_OF_JOIN DATE CREATING PRIMARY KEY USING ALTER COMMAND: SQL> alter table emp1 add primary key(emp_no); Table altered.

DEPT_NO NUMBER(5)

DATE_OF_JOIN DATE

SALARY NUMBER

MODIFYING THE TABLE:

SQL>alter table emp1 modify (salary number(5)); SQL>desc emp1; Name Null? Type


EMP_NAME VARCHAR2(10) EMP_NO NUMBER(8) DEPT_NAME VARCHAR2(10) DEPT_NO NUMBER(5) DATE_OF_JOIN DATE SALARY NUMBER(5) TRUNCATING OF TABLE: SQL> truncate table emp1; Table truncated. DROPPING OF TABLE: SQL> drop table emp1; Table dropped.

DML COMMANDS IN DBMS

DML(DATA MANIPULATION LANGUAGE)

 DML commands are the most frequently used SQL commands and is used to query and manipulate the existing database objects. Some of the commands are,  SELECT  INSERT  UPDATE  DELETE

  1. COMMAND NAME: SELECT Command is used to display the table & table values 2.COMMAND NAME: INSERT Command is used to insert the values to the table.
  2. COMMAND NAME: DELETE Command is used to delete the constraint from the table 4.COMMAND NAME: UPDATE Command is used to update the values

1 row created. SQL> insert into emp1 values('smith',305,'ece',21,'2-oct-2013'); 1 row created. SQL> select * from emp1; EMP_NAME EMP_NO DEPT_NAME DEPT_NO DATE_OF_JOIN


raju123cse21 21-JUN- john 456 it22 30-SEP- smith 305 ece 21 02-OCT- UPDATING TABLE VALUES: SQL> update emp1 set emp_no=456 where dept_no=22; 1 row updated. SQL> select * from emp1; EMP_NAME EMP_NO DEPT_NAME DEPT_NO DATE_OF_J


raju123cse 21 21-JUN- john 456 it 22 30-SEP- smith 305 ece 21 02-OCT- DELETING TABLE VALUES: SQL> delete from table emp1 where emp_no= ; 1 row deleted. SQL> select * from emp1; EMP_NAME EMP_NO DEPT_NAME DEPT_NO DATE_OF_J SALARY


raju 123 cse 21 21-JUN-06 4500 john 456 it 22 30-SEP-07 3000

TRANSACTION CONTROL COMMANDS

TYPES OF COMMANDS

TCL (TRANSACTION CONTROL COMMANDS)

 Transaction control language is used for statements are used to manage the changes made by DML statements.  It allows statements to be grouped together into logical transactions. There are lots of TCL commands which are used in SQL in which some are namely defined as follows:  COMMIT  SAVEPOINT  ROLLBACK SET OPERATORS SQL set operators allows combine results from two or more SELECT statements.  UNION  UNIONALL  INTERSECT  MINUS ARITHMETIC OPERATORS Arithmetic operators can perform arithmetical operations on numeric operands involved.  MAX  MIN

9. COMMAND NAME: UNIONALL

Command is used to return all entities in both rows.

  1. COMMAND NAME: INTERSECT Command is used to display only similar entities in both rows.
  2. COMMAND NAME: MINUS Command is used to display only the rows that match in both queries.
  3. COMMAND NAME: AVG Command is used to find average of entity in particular attribute.

EXECUTION

TABLE CREATION:

SQL> create table emp1(emp_name varchar2(10),emp_no number primary key,dept_no number,dept_name varchar2(10)); Table created. SQL> create table em1(emp_name varchar2(10),emp_no number primary key,dept_no number,dept_name varchar2(10)); Table created. INSERTING VALUES: SQL> insert into emp1 values('vijay',345,54,'cse'); 1 row created. SQL> insert into emp1 values('ajay',35,6,'cse'); 1 row created. SQL> insert into em1 values('ajay',35,6,'cse'); 1 row created. SQL> insert into em1 values('vinoth',45,504,'cse'); 1 row created. DISPLAY THE TABLE: SQL> select * from emp1; EMP_NAME EMP_NO DEPT_NO DEPT_NAME


vijay 345 54 cse ajay 35 6 cse SQL> select * from em1; EMP_NAME EMP_NO DEPT_NO DEPT_NAME


ajay 35 6 cse vinoth 45 504 cse COMMIT & ROLLBACK COMMAND: SQL> commit; Commit completed. SQL> delete from em1 where emp_no=45;

ARITHMETIC OPERATORS

SQL> select sum(dept_no) from em1; SUM(DEPT_NO)


600 SQL> select max(dept_no) from em1; MAX(DEPT_NO)


504 SQL> select avg(dept_no) from em1; AVG(DEPT_NO)


300 SQL> select min(dept_no) from em1; MIN(DEPT_NO)


6 SQL> select count(dept_no) from em1; COUNT(DEPT_NO)


2

SET OPERATORS

SQL> insert into em1 values('arjun',87,978,'mech'); 1 row created. SQL> insert into em1 values('akshai',83,98,'it'); 1 row created. SQL> insert into emp1 values('akash',38,56,'eee'); 1 row created. SQL> insert into emp1 values('arjun',87,978,'mech'); 1 row created. SQL> select * from em1; EMP_NAME EMP_NO DEPT_NO DEPT_NAME


ajay 35 6 cse vijay 345 54 cse arjun 87 978 mech akshai 83 98 it SQL> select * from emp1; EMP_NAME EMP_NO DEPT_NO DEPT_NAME


akash 38 56 eee arjun 87 978 mech SQL> select emp_no from em1 union select emp_no from emp1; EMP_NO


35 38 83 87 345 SQL> select emp_no from em1 union all select emp_no from emp1; EMP_NO


CONSTRAINTS

TYPES OF CONSTRAINTS

  1. Domain Integrity Constraints
  2. Entity Integrity Constraints
  3. Referential Integrity Constraints SQL COMMANDS The three types of constraints are Domain Integrity Constraints, Entity Integrity Constraints, and Referential Integrity Constraints  Integrity Constraints are used to enforce rules that the columns in a table have to conform with.  It is a mechanism used by Oracle to prevent invalid data entry into the table.
    1. Domain Integrity Constraints a. Not Null Constraint – The enforcement of Not Null Constraints in a table ensures that the table contain values. b. Check Constraint – Allow only a particular range of values
      1. Entity Integrity Constraints a. Unique Constraints – The unique constraint designates a column or a group of columns as unique key. This allows only unique value to be stored in the column. Rejects duplication. b. Primary Key Constraints – Primary key similar to unique key avoids duplication , relation between two tables , does not allow not null values.
    2. Referential Integrity Constraints Enforces relationship between tables. It designates a column or group of columns as a foreign key

EXECUTION

NOT NULL CONSTRAINT:

SQL> create table empl (ename varchar2(30) not null, eid varchar2(20) not null); Table created. SQL> insert into empl values ('abcde',11); 1 row created. SQL> insert into empl values ('fghij',12); 1 row created. SQL> insert into empl values ('klmno',null); insert into empl values ('klmno',null)

ERROR at line 1: ORA-01400: cannot insert NULL into ("ITA"."EMPL"."EID") SQL> select * from empl; ENAME EID


Abcde 11 fghij 12 CHECK CONSTRAINT: SQL> create table depts ( dname varchar2(30) not null, did number(20) not null check (did<10000)); Table created. SQL> insert into depts values ('sales ',9876); 1 row created. SQL> insert into depts values ('marketing',5432); 1 row created. SQL> insert into depts values ('accounts',789645); insert into depts values ('accounts',789645)

ERROR at line 1: ORA-02290: check constraint (ITA.SYS_C003179) violated

1 row created. SQL> insert into book values ('comics',1001); insert into book values ('comics',1001)

ERROR at line 1: ORA-00001: unique constraint (ITA.SYS_C003130) violated SQL> select * from book; BNAME BID


Fairy tales 1000 bedtime stories 1001 UNIQUE AS A TABLE CONSTRAINT SQL> create table orders( oname varchar2(30) not null , oid number(20) not null, unique(oname,oid)); Table created. SQL> insert into orders values ('chair', 2005); 1 row created. SQL> insert into orders values ('table',2006); 1 row created. SQL> insert into orders values ('chair',2007); 1 row created. SQL> insert into orders values ('chair', 2005); insert into orders values ('chair', 2005)

ERROR at line 1: ORA-00001: unique constraint (ITA.SYS_C003152) violated SQL> select * from orders; ONAME OID


chair 2005 table 2006

chair 2007 PRIMARY KEY AS A COLUMN CONSTRAINT SQL> create table custo ( cname varchar2(30) not null , cid number(20) not null primary key); Table created. SQL> insert into custo values ( 'jones', 506); 1 row created. SQL> insert into custo values ('hayden',508); 1 row created. SQL> insert into custo values ('ricky',506); insert into custo values ('ricky',506)

ERROR at line 1: ORA-00001: unique constraint (ITA.SYS_C003165) violated SQL> select * from custo; CNAME CID


jones 506 hayden 508 PRIMARY KEY AS A TABLE CONSTRAINT SQL> create table branches( bname varchar2(30) not null , bid number(20) not null , primary key(bname,bid)); Table created. SQL> insert into branches values ('anna nagar', 1005); 1 row created. SQL> insert into branches values ('adyar',1006); 1 row created. SQL> insert into branches values ('anna nagar',1007); 1 row created. SQL> insert into branches values ('anna nagar', 1005); insert into branches values ('anna nagar', 1005)