SQL & PLSQL
1. DDL Statements:
a) Create the table Invoice with fields inv_no (char, Primary key), mv_no(number), cust_id (char), Issue_date(date), return_date(date). Insert data in the table invoice as follows:
Inv_no mv_no cust_id issue_date return_date 101 1 a01 21-Dec-06 25-Dec-06
102 3 a02 19-Oct-06 30-Oct-06
.....
b) Modifying Table Structure by adding the new field price (number) and increasing the size of Inv_no.
C) Add and Drop Constraints c
1) Add primary key constraint
ii) Drop primary key constraint
lil) Add Unique key constraint
iv) Drop Unique key constraint
v) Add check constraint
Answer=
>create table Invoice (inv_no char (5) primary key, mv_no number (4), cust_id char (3), issue_date date, return_date date);
> select * from Invoice;
>insert into Invoice values ('101', 1, 'a01', '21-Dec-06', '25-Dec-06');
>alter table Invoice add (price number (7));
>alter table Invoice modify (inv_no char (7));
1)alter table Invoice add primary key (inv_no);
2)alter table Invoice drop primary key;
3)alter table invoice modify (inv_no char 10) unique);
4)alter table Invoice drop unique (inv_no);
5)alter table Invoice add constraint check_inv_no CHECK (inv_no < '114');
>insert into Invoice values ('106', 3, 'a06', '17-jan-06', '21-feb-06',7000);
>insert into Invoice values ('114', '3', 'a06', '17-jan-06', '21- feb-06',7000);
___________________________________________
2) DML Statements:
a) Add a records in above table
b) i)Change inv_no of 112 with 1111". ii)Change issue_date of customer of cust_id a01" to "22- Dec-06".
iii) Change movie no of cust_id,,a05" to 11.
iv)Display all movies with issue_date between 01-jan-06 to 01-sep-06
v)Display information for cust_id,a01 & a02".
c) i) Delete records with inv_no,111 from invoice table.
ii) Delete records having return date <="20-Jan-06".
Answer=
a)
>create table Invoice (inv_no char (5) primary key, mv_no number (4), cust_id char (3), issue_date date, return_date date,price number (7));
>insert into Invoice values ('101', 1, 'a01', '21-Dec-06', '25-Dec-06',5000);
B)
1) update Invoice set inv_no-'I111' where inv_no='112';
2) update Invoice set issue_date='22-dec-06' where cust_id='a01';
3) update Invoice set mv_no=11 where cust_id='a05';
4)select * from Invoice where issue_date between '01-jan-06' and '01-sep-06';
5)select * from Invoice where cust_id='a01' or cust_id='a02';
C) 1) delete from Invoice where inv_no='I11';
2) delete from Invoice where return_date<='20-jan-06';
____________________________________________
3) DCL and TCL Statements
i) Grant insertion and deletion privileges on invoice table to another user XYZ.
ii) revoke the deletion privileges from user XYZ.
iii) after insertion of 2 records in invoice table create savepoint A.
iv) delete a record and create savepoint B.
v) rollback to A
Answer=
1) grantinsert,delete on invoice1 to XYZ;
Deletefrom system.invoice1 where inv_no='103';
2)revoke delete on invoicel from XYZ;
3)insert into system.invoice values ('106',10, 'a06', '13-jan- 06', '30-mar-06', 8900);
insert into system.invoice values ('111', 11, 'all', '15-apr-06', '30- aug-06', 8000);
savepoint A;
4) delete from invoicel where inv_no='111';
savepoint B;
5) rollback to A;
____________________________________________
4) Create the table Movie with fields mv_no (number, Primary Key), title (Char), Type(char), star(char), price (number),releasedate (date)
i) Add few records in above table
ii) Count total number of customer
iii) Calculate total price of all the movies.
iv) Calculate average price of all the movies.
v) Determine the maximum & minimum movies prices and rename the title as MAX-PRICE and MIN-PRICE.
vi) Count the number of movies having price greater than or equal to 150.
vii) Display total no. of movies released in the current year. viii) Display all the movie which start with letter „K".
Answer=
create table movie (mv_no number (3) primary key, title char (30), type char (30), star char (30), price number (4), releasedate date);
1) insert into movie values (6, 'kick', 'thiller', 'salman', 500, '21-aug-14');
....
2) select count (mv_no) as "Total number of customer" from movie;
3) select sum(price) as "Total Price of all movies" from movie;
4) select avg(price) as "Average of all movies" from movie;
5) select max(price) as "MAX-PRICE", min (price) as "MIN-PRICE" from movie;
6) select count(mv_no) from movie where price>=150;
7) SELECT COUNT (MV_NO) FROM MOVIE WHERE releasedate between '31-dec-15' and '31-dec-16';
8) select * from movie where title like 'k%';
____________________________________________
5)i) Create a View of table Invoice with following column inv_no(char, Primary key), mv_no(number), cust_id(char)
ii) Insert and delete records
iii) drop view
Answer=
1) create view vw_invoice asselect inv_no,mv_no, cust_id from invoice;
select * from vw invoice;
2) insert into vw_invoice values ('111', 6, 'a03');
delete from vw_invoice where inv_no='I11';
3) drop view vw_invoice;
____________________________________________
6) PL/SQL Programming:
Write a PL/SQL block
i) to find largest of 2 numbers
ii) to reverse the number
Answer=
1) setserveroutput on;
DECLARE
num1 number;
num2 number;
BEGIN
num1:=&number;
num2:=&number;
if num1>num2 then
dbms_output.put_line (num1 || IS GERATER');
ELSE
dbms_output.put_line (num2 || IS GERATER');
END IF;
end;
/
2) setserveroutput on;
FIND REVERSE OF A NUMBER :
setserveroutput on;
DECLARE
num number;
rev number;
last number;
BEGIN
num:=#
rev:=0;
while num>0 loop
last:=mod(num, 10);
rev:=(rev*10)+last;
num: trunc (num/10);
end loop;
dbms_output.put_line (rev || IS REVERSE');
end;
/
____________________________________________
7) Cursor
i) Write a Cursor to fetch all rows of table invoice
ii) Write a Cursor to display all movies with issue_date between 01-jan-06 to 01-sep-06. If no record found then use Exception
to display proper message.
iii) Write a PI/SQL to accept the name and age of student and insert into table STUD after validating age not less than 18
otherwise raise exception
Answer=
1) >create table Invoice (inv_no char (5) primary key, mv_no number (4), cust_id char (3), issue_date date, return_date date);
> select * from Invoice;
.....
>insert into Invoice values ('101', 1, 'a01', '21-Dec-06', '25-Dec-06');
declare
p invoice.inv_no%type;
q invoice.mv_no%type;
r invoice.cust_id%type;
s invoice.issue_date%type;
t invoice.return_date type;
u invoice.price%type;
cursor invoicecursor is
select * from invoice;
begin
open invoicecursor;
dbms_output.put_line ('Invoice_no' II 'movie_no'l'customer_id' || 'Is suedate'||'Returndate'||'Price');
loop
fetch invoicecursor into
p,q,r,s,t,u;
dbms_output.put_line (RPAD (p, 10) ||RPAD (q, 10)||RPAD (r, 10) || RPAD (s, 10) || RPAD (t, 10) || RPAD (u, 10));
exit when invoicecursor%NOTFOUND;
end loop;
close invoicecursor;
end;
/
2) answer Not found
3)>create table stud(sname varchar(10), sage number (4));
> select * from Invoice;
insert into stud values ('tate', 23);
insert into stud values ('robert', 21);
insert into stud values ('andrew', 23);
Select * from stud;
setserveroutput on;
declare
err exception;
sname varchar2 (10);
sage number (3);
begin
sname:= '&sname';
sage:=&sage;
if sage<18 then
raise err;
else
insert into stud values (sname, sage);
sage:=&sage;
if sage<18 then
raise err;
else
end if;
exception
when err then
dbms_output.put_line ('plz enter age greater than 18');
end;
/
____________________________________________
8) Create Procedure
i) to swap two values
ii) to display the cust_id, mv_no and call the procedure throughmain program.
Answer=
1) Set serveroutput on;
create or replace procedure swap
a number (4);
b number (4);
begin
a:=&a;
b:=&b;
dbms_output.put_line ('Before swap');
dbms_output.put_line ('a = ' || a); dbms_output.put_line ('b = ' || b);
a:=a+b;
b:-a-b;
a:=a-b;
dbms_output.put_line ('Before swap');
dbms_output.put_line ('a = || a); dbms_output.put_line ('b = ' || b);
end swap;
/
exec swap;
2) >create table Invoice (inv_no char (5) primary key, mv_no number (4), cust_id char (3), issue_date date, return_date date);
> select * from Invoice;
>insert into Invoice values ('101', 1, 'a01', '21-Dec-06', '25-Dec-06');
......
set serveroutput on;
create or replace procedure display is cursor cn is select * from invoice;
crn cn%ROWTYPE;
begin
open cn;
loop
fetch cn into crn;
exit when cn%NOTFOUND;
dbms_output.put_line (crn.mv_no|| ' ' || crn. cust_id) ;
end loop;
close cn;
end;
exec display;
____________________________________________
9) Create Function
i) to find gcd of two numbers.
ii) to find the total price of all the movie start with letter "K" and call the function through main program
Answer=
1)
Set serveroutput on;
create or replace function gcd (a number, b number)
return number is
begin
if b = 0 then
return a;
else
return gcd (b, mod (a,b));
end if;
end;
/
2) set serveroutput on;
CREATE OR REPLACE FUNCTION totalmovie RETURN number IS
total number (30) := 0;
BEGIN
SELECT sum (price) into total FROM movie where title like 'k';
RETURN total;
END;
DECLARE
c number (30);
BEGIN
c:= totalmovie();
dbms_output.put_line ('Total price of movie start with letter k: ' || c);
End;
/
____________________________________________
10) Create Trigger
i) that restricts the user from performing a DML on movie table on Monday"
ii) Covert all the movie name into the upper case on insertion
of each row
Answer=
1)
create or replace trigger no_update_on_monday
before insert or update or delete on movie
begin
if rtrim (to_char (sysdate, 'DAY')) = 'MONDAY' then
raise_application_error(-20224, 'cannot modify movie on monday');
End if;
End;
/
insert into movie
values (24, 'agni', 'drama', 'amitabh', 115, '14-aug-68');
insert into movie values (24, 'agni', 'drama', 'amitabh', 115, '14-aug-68')
2) >create table movie (mv_no number (4),title varchar(25));
> select * from movie;
create or replace trigger abc
before insert or update on movie for each row
begin
:new.title :=upper (:new.title);
end;
/
insert into movie (mv_no, title) values (11, 'kaal');
insert into movie (mv_no, title) values (12, 'xyz');
insert into movie (mv_no, title) values (13, 'abc');
insert into movie (mv_no, title) values (14, 'pqr');
insert into movie (mv_no, title) values (15, 'meet');
select mv_no, title from movie Where mv_no between 11 to 15;
