Friday, 4 May 2018

DATA BASE MANAGEMENT SYSTEM PRACTICALS | practical

DATA BASE MANAGEMENT SYSTEM PRACTICALS
Experiment 1
Program 1:
Delete duplicate row from the table.
DELETE FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT         
GROUP BY DEPTNO HAVING COUNT(DEPTNO)>1);  
DELETE FROM emp A WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM  emp WHERE A.DEPTNO=B.DEPTNO);
OR
DELETE FROM DEPT A WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM  DEPT B WHERE A.DEPTNO=B.DEPTNO);
Ques1:-Delete the row containing name Ram?
Ques2:-Delete all the rows having same name more then once?
Ques3:- Delete the row of employee whose name start with M?
Experiment 2
Program 2:
Display the alternate row from table.
SELECT * FROM EMP WHERE ROWID IN(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM EMP);
OR
SELECT * FROM GDEPT WHERE ROWID IN(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM GDEPT);
Ques1:-Show the name of those employees who earn commission?
Ques2:-Show all employees who has no commission but have a10% hike in their salary?
Ques3:-Show the last name of all employees together with the number of years & the number of complete months that they have been employed?
Experiment 3
Program 3:
Delete alternate row from table.
DELETE  FROM GDEPT WHERE ROWID IN(DELETE  DECODE(MOD(ROWNUM,2),0,ROWID) FROM GDEPT);
Ques1:-Delete the row of employee who works in location Bombay?
Ques2:- Delete the row of employee whose name end with N?
Ques3:- Delete the row of employee whose salary is more then 25000?
Experiment 4
Program 4:
Update multiple rows in using single update statement
DISPLAY ALL THE DETAILS WHERE DEPT IS EITHER SALES OR RESEARCH  
Select * from emp where dname = any(select dname from emp where dname = ‘sales’ or dname = ‘research’);
Select * from emp where dname = any(select dname from emp where     
Dname like(‘sales’,’research’));
Ques1:-Find the name of those entire employee who work in Delhi and update there location to Bombay?
Ques2:-Find the name of those dept which are in same city?
Ques3:- Write a query to raise the salary by 50% of those employees who do not have a commission?
Experiment 5
Program 5:
Find the third highest paid and third lowest paid salary.
SOL: SELECT MAX(SAL) FROM EMP WHERE       
SAL<(SELECT MAX(SAL) FROM EMP WHERE      
SAL<(SELECT MAX(SAL) FROM EMP));
SOL: SELECT ENAME,SAL FROM EMP      
MINUS       
SELECT ENAME,SAL FROM EMP WHERE      
SAL>(SELECT MIN(SAL) FROM EMP WHERE       
SAL>(SELECT MIN(SAL) FROM EMP WHERE       
SAL>(SELECT MIN(SAL) FROM EMP WHERE       
SAL>(SELECT MIN(SAL) FROM EMP))));
Ques1:-Write a query to find all those employee who are in the dept which has the max salary of all dept?
Ques2:- Write a query to find those entire employees who earn maximum salary?
Ques3:- Write a query to find those employees who work in that dept in which the higher salary taker works?
Experiment 6
DISPLAY from NTH ROW
SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT   WHERE ROWNUM<=(SELECT COUNT(*)-&N FROM DEPT));
Program 6:
Display the 3rd, 4th, 9th rows from table.
SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT   WHERE ROWNUM<=(SELECT COUNT(*)-&3 FROM DEPT));
SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT   WHERE ROWNUM<=(SELECT COUNT(*)-&4 FROM DEPT));
SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT   WHERE ROWNUM<=(SELECT COUNT(*)-&9 FROM DEPT));
Ques1:-Show the dept name of the dept where no clerk works?
Ques2:-show the dept number and the lowest salary of the dept with the highest average salary?
Experiment 7
Program 7:
Display the ename, which is start with j, k, l or m.
select ename
from employees
where name like 'J%'
or name like 'K%'
or name like 'L%' or name like 'M%' ;
or
select ename
from
employees
where name like '[JKLM]%'
Ques1:-Write a query to find that how many employees are there whose name ends with N?
Ques2:- Write a query to find that how many employees are there whose name ends with M without using like operator?
Experiment 8
Program 8:
Show all employees who were hired the first half of the month.
SELECT last_name, hire_date
FROM employees
WHERE hire_date < trunc(sysdate,'MM')+15;
Ques1:-Write a query to find the data of that entire employee whose name ends with t?
Ques2:- Find the DOB of that employee who was born on the same date on which the maximum salary earner was born?
Experiment 9
Program 9:
Display the three record in the first row and two records in the second row and one record in the third row in a single sql statements.
INSERT INTO TEMP(EMPNO,ENAME,JOB)      
SELECT TOP 1 *
FROM
(SELECT TOP 2<some columns>
FROM<table>ORDER BY<something> ASC)ORDER BY <something> DESC;
Ques1:-Find the average salary of employee according to their dept?
Ques2:-Find the standard deviation according to employee salary?
Rollback/ Savepoint
All these statements fall in the category of Transaction Control Statements.
Rollback:
This is used for undoing the work done in the current transaction. This command also releases the locks if any hold by the current transaction. The command used in SQL for this is simply:
ROLLBACK;
Savepoint:
This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a savepoint defined in it. The command used in SQL for this is simply:
SAVEPOINT savepointname;
For example:
UPDATE…..  
DELETE….  
SAVEPOINT e1;  
INSERT….  
UPDATE….  
SAVEPOINT e2;  
…… It is also possible to define savepoint and rollback together so that programmer can achieve rollback of part o a transaction. Say for instance in the above
ROLLBACK TO SAVEPOINT e2;
This results in the rollback of all statements after savepoint e2
Commit
This is used to end the transaction and make the changes permanent. When commit is performed all save points are erased and transaction locks are released. In other words commit ends a transaction and marks the beginning of a new transaction. The command used in SQL for this is simply:
COMMIT;
Experiment 10
Program 10:
Write a sql statement for rollback commit and save points.
SQL> SELECT * FROM DEPT;
 DEPTNO DNAME        LOC
--------- -------------- -------------      
10 ACCOUNTING        NEW YORK      
20 RESEARCH              DALLAS       
30 SALES         CHICAGO      
40 OPERATIONS          BOSTON    
  50 CS             MYSORE
SQL> SAVEPOINT A
2  ;
Savepoint created.  
SQL> INSERT INTO DEPT VALUES(60,'IP','BANGALORE');
1 row created.  
SQL>  SAVEPOINT B;
Savepoint created.  
SQL>  INSERT INTO DEPT VALUES(70,'IT','GOA');
1 row created.  
SQL> SELECT * FROM DEPT;
 DEPTNO DNAME        LOC
--------- -------------- -------------      
10 ACCOUNTING        NEW YORK      
20 RESEARCH              DALLAS       
30 SALES                       CHICAGO       
40 OPERATIONS          BOSTON      
50 CS                            MYSORE      
60 IP                             BANGALORE  
70 IT                             GOA
7 rows selected.  
SQL> ROLLBACK TO SAVEPOINT B;
Rollback complete.
SQL> SELECT * FROM DEPT;
 DEPTNO DNAME        LOC
--------- -------------- -------------      
10 ACCOUNTING        NEW YORK      
20 RESEARCH              DALLAS      
30 SALES                       CHICAGO      
40 OPERATIONS          BOSTON      
50 CS                             MYSORE      
60 IP                              BANGALORE
6 rows selected.  
Temp
~~~~
prodname     comment          date1
create table temp( prodname varchar2(10), comm varchar2(16),              
date1 date);
declare
qty NUMBER(5);
pname VARCHAR2(10);
begin
select quantity,prodname into qty,pname from inv where
prodname='&productname';
if qty>5 then
DBMS_OUTPUT.PUT_LINE('THANK U FOR THE PURCHASES MADE VISIT AGAIN');
update inv set quantity=quantity-1 where prodname=pname;
commit;
else
DBMS_OUTPUT.PUT_LINE('STOCK LEVEL IS BELOW ORDER LEVEL');
insert into temp values(pname,'out of stock',sysdate);
commit;
end if;
end;
Ques1:-Draw a sequence diagram for roll back and save point activity in ATM transaction?
Ques2:-Write syntax for rollback SQL query using suitable example?
PL/SQL
PL/SQL stands for Procedural Language/SQL.PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block, All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in the program.
Block has the following structure:
DECLARE
/* Declarative section: variables, types, and local subprograms.
*/
BEGIN
/* Executable section: procedural and SQL statements go
here. */
/* This is the only section of the block that is required. */
EXCEPTION
/* Exception handling section: error handling statements go
here. */
END;
Let us see an example of the above
DECLARE
TEMP_COST NUMBER(10, 2);
BEGIN
SELECT COST FROM JD11.BOOK INTO TEMP_COST
WHERE ISBN = 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST =
(TEMP_COST*1.175) WHERE ISBN = 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE
ISBN = 21;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE)
VALUES(99, ‘ISBN 21 NOT FOUND’);
END;
Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. Data definition statements like CREATE, DROP, or ALTER are not allowed.The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.To execute a PL/SQL program, we must follow the program
text itself by· A line with a single dot (“.”), and then · A line with run;As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it in sqlplus or by putting the code in a file and invoking the file in the various ways we learned in.
Getting Started With Oracle.
What are the Variables?
Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be · One of the types used by SQL for database columns
· A generic type used in PL/SQL such as NUMBER
· Declared to be the same as the type of some database Column
The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:
DECLARE
price NUMBER;
myBeer VARCHAR(20);
You know that PL/SQL allows BOOLEAN variables, even
though Oracle does not support BOOLEAN as a type for
database columns.
Types in PL/SQL
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:
DECLARE gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.
Experiment 11
Program 11:
Write a pl/sql for select, insert, update and delete statements.
CREATE TABLE TEMP
( ENAME VARCHAR2(10),
DESIG VARCHAR2(10),
SAL NUMBER(7,2));
DECLARE
NAME VARCHAR2(10);
DESIG VARCHAR2(10);
SALARY NUMBER(7,2);
ENO NUMBER(4):=&EMPNO;
BEGIN
SELECT ENAME,JOB,SAL INTO NAME,DESIG,SALARY FROM EMP WHERE EMPNO=ENO;
DBMS_OUTPUT.PUT_LINE(ENO||' '||NAME||' '||SALARY||' '||DESIG);
IF DESIG='CLERK' THEN
DELETE FROM EMP WHERE EMPNO=ENO;
INSERT INTO TEMP VALUES(NAME,DESIG,SALARY);
DBMS_OUTPUT.PUT_LINE('DELETED FROM EMP AND INSERTED TO TEMP');
COMMIT;
ELSIF DESIG='MANAGER' THEN
UPDATE EMP SET SAL=SALARY+200 WHERE EMPNO=ENO;
DBMS_OUTPUT.PUT_LINE('INCREMENTED SALARY IS '||TO_CHAR(SALARY+200));
END IF;
END;
Ques1:- Write a pl/sql for merge statement using suitable example?
Ques2:-Write a query to create a view for DEPT table(Full view,View of fragmented table) ?
Experiment 12
Program 12:
Write a pl/sql block to delete a record. If delete operation is successful return 1 else return 0.
create or replace function fun3(n emp.empno%type) return number is
a number;
begin
delete from emp where empno=n;
if sql%found then
return 1;
else
return 0;
end if;
--exception
--when no_data_found then
--return 100;
end;
declare
n number;
begin
n:=fun3(&empno);
dbms_output.put_Line(n);
if n=0 then
dbms_output.put_line('deletion unsuccessfull');
elsif n=1 then
dbms_output.put_line('deletion successfull');
end if;
end;
Ques1:-Write a query to run grant and revoke command ?
Ques2:-What is sequence?
Cursors
What are Cursors?
A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position. The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose  tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.
DECLARE
/* Output variables to hold the result of the query: */
a T1.e%TYPE;
b T1.f%TYPE;
/* Cursor declaration: */
CURSOR T1Cursor IS
SELECT e, f
FROM T1
WHERE e < f
FOR UPDATE;
BEGIN
OPEN T1Cursor;
LOOP
/* Retrieve each row of the result of the above query
into PL/SQL variables: */
FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
EXIT WHEN T1Cursor%NOTFOUND;
/* Delete the current tuple: */
DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Insert the reverse tuple: */
INSERT INTO T1 VALUES(b, a);
END LOOP;
/* Free cursor used by the query. */
CLOSE T1Cursor;
END;
Experiment 13
Program 13:
Display name, hire date of all employees using cursors.
DECLARE       
cursor c1 is select ename,hiredate from emp;      
name varchar(20);      
hdate date;
begin      
open c1;       
loop      
fetch c1 into name,hdate;      
exit when c1%NOTFOUND;      
dbms_output.put_line(name||' '||hdate);      
end loop;      
close c1;
end;
Ques1:-Display maximum salary using cursor?
Ques2:-Display salary of all employee in descending order using cursor?
Experiment 14
Program 14:
Display details of first 5 highly paid employees using cursors
DECLARE      
cursor c1 is select * from emp order by sal desc;      
a c1%rowtype;
begin      
open c1;       
loop      
fetch c1 into a;      
exit when c1%rowcount>6;      
dbms_output.put_line(a.ename||' '||a.sal||' '||a.job||'        
'||C1%ROWCOUNT);      
end loop;      
close c1;
end;
Ques1:-Write a query to find the details of those employee who have same job using cursor?
Ques2:-Write a query to show dept where no sales representative works using cursor?
Triggers
A trigger (essentially, a stored SQL statement associated with a table) is a database object that defines events that happen when some other event, called a triggering event, occurs. Create a trigger by using the CREATE TRIGGER statement. Triggers execute when an INSERT, UPDATE, or DELETE modifies a specified column or columns in the subject table. Typically, the stored SQL statements perform an UPDATE, INSERT, or DELETE on a table different from the subject table. Sometimes a statement fires a trigger, which in turn, fires another trigger. Thus the outcome of one triggering event can itself become another trigger. The Teradata RDBMS processes and optimizes the triggered and triggering statements in parallel to maximize system performance.
Trigger Functions
Use triggers to perform various functions:
• Define a trigger on the parent table to ensure that UPDATEs and DELETEs performed to the parent table are propagated to the child table.
• Use triggers for auditing. For example, you can define a trigger which causes INSERTs in a log record when an employee receives a raise higher than 10%.
• Use a trigger to disallow massive UPDATEs, INSERTs, or DELETEs during
business hours. For example, you can use triggers to set thresholds for inventory of each item by store, to create a purchase order when the inventory drops below a threshold, or to change a price if the daily volume does not meet expectations.
Restrictions on Using Triggers
Teradata triggers do not support FastLoad and MultiLoad utilities and, and
you must disable triggers before you run load utilities. In addition, a positioned
(updatable cursor) UPDATE or DELETE is not allowed to fire a trigger and
generates an error.
Note: You cannot define a join index on a table with a trigger.
CREATE TRIGGER <triggername> AFTER UPDATE/INSERT/DELETE  OF   <COLUMN NAME> ON <TABLENAME> FOR EACH ROW           
BEGIN             
-----            
-----            
executable statements;            
-----            
-----
END;
Experiment 15
Program 15:
Write a database trigger which fires if you try to insert, update, or delete after 7’o’ clock
CREATE OR REPLACE TRIGGER GEETIME BEFORE INSERT OR UPDATE OR          
DELETE ON EMP for each row      
DECLARE          
A VARCHAR2(10);      
BEGIN         
SELECT TO_CHAR(SYSDATE,'HH:MI') INTO A FROM DUAL;         
IF A > '06:59' then             
RAISE_APPLICATION_ERROR(-20500,'YOU CANT DO THIS OPERATION                             
NOW');         
END IF;      
END;
Experiment 16
Program 16:
Write a data base trigger, which acts just like primary key and does not allow duplicate
CREATE OR REPLACE TRIGGER PRIKEY  BEFORE INSERT ON EMP      
FOR EACH ROW      
DECLARE        
A NUMBER;      
BEGIN          
SELECT COUNT(*) INTO A FROM EMP WHERE EMPNO=:NEW.EMPNO;         
IF A >=1 THEN      
RAISE_APPLICATION_eRROR(-20500,'THE PRI KEY RULE IS          
VOILATED');         
ELSIF A=0 THEN            
PRINT('RECORD IS INSERTED');      
END IF;    
END;
SQL> INSERT INTO EMP(EMPNO,DEPTNO) VALUES(7788,20);
INSERT INTO EMP(EMPNO,DEPTNO) VALUES(7788,20)
ERROR at line 1:
*ORA-20500: THE PRI KEY RULE IS VOILATED
ORA-06512: at "GEETHA.PRIKEY", line 6
ORA-04088: error during execution of trigger 'GEETHA.PRIKEY'
SQL>  INSERT INTO EMP(EMPNO,DEPTNO) VALUES(77,20);
1 row created.
Experiment 17
Program 17:
Create a data base trigger, which performs the action of the on delete cascade
    CREATE OR REPLACE TRIGGER DELDEPT       
AFTER DELETE ON DEPT FOR EACH ROW      
BEGIN        
DELETE FROM EMP WHERE DEPTNO=:OLD.DEPTNO;         
PRINT('RECORDS IN EMP ARE ALSO DELETED');      
END;
Experiment 18
Program 18:
Write a data base trigger, which should not delete from emp table if the day is Sunday.
CREATE OR REPLACE TRIGGER EMPNO_CHECK        
BEFORE DELETE   ON emp
BEGIN       
if  to_char(sysdate,'dAy')='SUNDAY'  then       
raise_application_error(-20001,'TO DAY IS SUNDAY  ');      
end if;
END;
Ques1:- What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
Ques2:- Write a data base trigger, for the employee whose day of joining is Sunday?
Experiment 19
Program 19:
Create or replace type address as object
(Streetno number(10),sname varchar(10));
Create table Employee
(empno number(10),ename varchar(10), add1 address);
Insert into Employee
Values(101,’ABC’,address(111,’Street’))
Select a.add1.sname from employee a;
Update employee e
Set e.add1.streetno=111
Where ename=‘ABC’
VARRAYS
Create Type icode as varray(5) of varchar(5);
Create Type iqty as varray(5) of number(5);
Create table order
(ono varchar(10),code icode, qty iqty);
Insert into order
Values(‘o1’,icode(‘i1’,’i2’,’i3’),qty(100,98,200));
Select * from table (select t.code from order t where t.ono=‘o1’)
       
NESTED TABLES
Create type order as object
(icode varchar(10),qty number(5));
Create type order_nt as table of order_ty;
Create table O_Master
(ono varchar(10), odate date,det order_nt)
Nested table det store as ord_nt_tab;
Insert into O_Master
Values(‘o1’,’12-oct-13’,order_nt(order_ty(‘i1’,12), order_ty(‘i2’,13)));
Insert into table (select p.det from O_Master p where p.ono=‘o1’)
Values (‘i1’,23);
Select * from table (Select t.det from O_Master t where t.ono=‘o1’)
Updating nested table
Update Table (Select e.det from O_Master e where e.ono=‘o1’ ) p
Set value(p) = order_ty(‘i3’,34)
Where p.icode=‘i1’;
Deleting nested table values
Delete from table (Select e.det from O_Master e where e.ono=‘o1) p where p.icode=‘i1’
   


Viva Questions
  1. What is database?
  2. What is DBMS?
  3. What is a Database system?
  4. Explain the difference between a database administrator and a data administrator
  5. Disadvantage in File Processing System?
  6. Describe the three levels of data abstraction?
  7. Define the "integrity rules"
  8. What is extension and intension?
  9. What is System R? What are its two major subsystems?
  10. How is the data structure of System R different from the relational structure?
  11. What is Data Independence?
  12. What is a view? How it is related to data independence?
  13. What is Data Model?
  14. What is E-R model?
  15. What is Object oriented model?
  16. What is an Entity?
  17. What is an Entity type?
  18. What is an Entity set?
  19. What is an Extension of entity type?
  20. What is Weak Entity set?
  21. What is an attribute?
  22. What is a Relation Schema and a Relation?
  23. What is degree of a Relation?
  24. What is Relationship?
  25. What is Relationship set?
  26. What is Relationship type?
  27. What is degree of Relationship type?
  28. What is Data storage?
  29. What is DML (Data Manipulation Language)?
  30. What is VDL (View Definition Language)?
  31. What is DML Compiler?
  32. What is Query evaluation engine?
  33. What is DDL Interpreter?
  34. What is relational Algebra?
  35. What is Relational Calculus?
  36. How does Tuple-oriented relational calculus differ from domain-oriented relational calculus
  37. What is normalization?
  38. What is Functional Dependency?
  39. When is a functional dependency F said to be minimal?
  40. What is Multivalued dependency?
  41. What is Lossless join property?
  42. What is 1 NF (Normal Form)?
  43. What is Fully Functional dependency?
  44. What is 2NF?
  45. What is 3NF?
  46. What is BCNF (Boyce-Codd Normal Form)?
  47. What is 4NF?
  48. What is 5NF?
  49. Explain the difference between an explicit and an implicit lock.
  50. Explain the meaning of the expression ACID transaction. 


EmoticonEmoticon

Ads

Recent Posts Widget