















































































Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
here i have attached DBMS lab manual for your reference
Typology: Exercises
1 / 87
This page cannot be seen from the preview
Don't miss anything!
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
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.
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 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 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 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
Command is used to return all entities in both rows.
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;
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
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
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)