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
- COMMAND NAME: CREATE Command is used to create objects in database.
- COMMAND NAME: ALTER Command is used to alter the structure of database
- COMMAND NAME: DROP Command is used to delete the object from database.
- 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
- 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.
- 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.
- COMMAND NAME: INTERSECT Command is used to display only similar entities in both rows.
- COMMAND NAME: MINUS Command is used to display only the rows that match in both queries.
- 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
- Domain Integrity Constraints
- Entity Integrity Constraints
- 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.
- 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
- 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.
- 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)
