Database Management Systems Laboratory
SYLLABUS
1. Introduction to SQL and installation of a database product
2. Data Types, Creating Tables, Retrieval of Rows using Select Statement, Conditional Retrieval of Rows, Alter and Drop Statements.
3.Working with Null Values, Matching a Pattern from a Table, Ordering the Result of a Query, Aggregate Functions,Grouping the Result of a Query, Update and Delete Statements.
4.Set Operators, Nested Queries, Joins, Sequences.
5.Views, Indexes, Database Security and Privileges: Grant and Revoke Commands, Commit and Rollback Commands.
6.PL/SQL Architecture, Assignments and Expressions, Writing PL/SQL Code, Referencing Non-SQL parameters.
7.Stored Procedures and Exception Handling.
8.Triggers and Cursor Management in PL/SQL.
9.Working with XML databases
10.Introduction to a data mining tool
ASSIGNMENT 1
CREATE THE TABLE:
create table client_master11
(client_no varchar2(6),
name varchar2(20),
address varchar2(30),
city varchar2(15),
state varchar2(15),
pincode varchar2(15),
balance_due number(10,2)
)
insert into client_master11 values
('&client_no','&name','&address','&city','&state','&pincode',&balance_due)
SQL> select*from client_master11;
CLIENT NAME ADDRESS CITY STATE PINCODE BALANCE_DUE
------ -------------------- ------------------------------ --------------- -----
0001 Ivan NULL bombay maharastra 400054 15000
0002 vandana NULL madras tamil nadu 780001 0
0003 pramada NULL bombay maharastra 400057 5000
0004 basu NULL bombay maharastra 400056 0
0005 ravi NULL delhi delhi 100001 2000
0006 rukmani NULL bombay maharastra 400050 0
0007 ankit NULL ghaziabad uttar pradesh 201010 0
7 rows selected
CREATE THE TABLE:
create table product_master11
(product_no varchar2(8),
description varchar2(15),
profitpercent number(6),
unitmeasure varchar2(15),
quantity number(4),
reorder number(4),
sellprice number(8),
costprice number(8)
)
insert into product_master11 values
('&product_no','&description',&profitpercent,'&unitmeasure',&quantity,&reorder,&sellprice,&costprice);
SQL> select*from product_master11;
PRODUCT DESCRIPTION PROFIT% UNITMEASURE QUANTITY REORDER SELLPRICE COSTPRICE
-------- --------------- ------------- --------------- --------- ---------- ---------- ---------
p00061 1.44 floppies 5 pieces 100 20 525 500
p03453 monitor 6 pieces 10 20 12000 11200
p06734 mouse 5 pieces 20 5 1050 500
p07865 1.22 floppies 5 pieces 100 20 525 500
p07868 keyboard 2 pieces 10 3 3150 3050
p07885 cd drive 3 pieces 10 3 5250 5100
p07965 540HDD 4 pieces 10 3 8400 8000
p07975 1.44 drive 5 pieces 10 3 1050 1000
p08865 1.22 drive 5 pieces 2 3 1050 1000
9 rows selected
QUERIES
- FIND THE NAME OF ALL THE CLIENTS.
SQL> select name from client_master11;
NAME
--------------------
pramada
basu
ravi
rukmani
Ivan
vandana
ankit
7 rows selected.
- RETRIVE THE LIST OF NAMES AND CITIES OF ALL THE CLIENTS.
SQL> select name,city from client_master11;
NAME CITY
-------------------- ---------------
pramada bombay
basu bombay
ravi delhi
rukmani bombay
Ivan bombay
vandana madras
ankit ghaziabad
7 rows selected.
- LIST THE VARIOUS PRODUCT AVAILABLE FROM THE PRODUCT_MASTER TABLE.
SQL> select description from product_master11;
DESCRIPTION
---------------
1.44 floppies
monitor
monitor
mouse
1.22 floppies
keyboard
cd drive
540HDD
1.44 drive
1.22 drive
10 rows selected.
- List all the client who are located in Bombay.
CLIENT NAME ADDRESS CITY STATE PINCODE BALANCE_DUE
------ -------------------- ------------------------------ --------------- --------------- --------------- -----------
0003 pramada NULL bombay maharastra 400057 5000
0004 basu NULL bombay maharastra 400056 0
0006 rukmani NULL bombay maharastra 400050 0
0001 Ivan NULL bombay maharastra 400054 15000
4 rows selected.
- DISPLAY INFORMATION FOR CLIENT NO 0001 AND 0002.
SQL> select * from client_master11 where client_no='0001' or client_no='0002';
CLIENT NAME ADDRESS CITY STATE PINCODE BALANCE_DUE
------ -------------------- ------------------------- --------------- - -------------- ------------ -----------
0001 Ivan NULL bombay maharastra 400054 15000
0002 vandana NULL madras tamilnadu 780001 0
- FIND THE PRODUCT WITH DESCRIPTION 1.44DRIVE AND 1.22 DRIVE.
SQL> select * from product_master11 where description='1.44 drive'or description
='1.22 drive';
PRODUCT_ DESCRIPTION PROFITPERCENT UNITMEASURE QUANTITY REORDER SELLPRICE COSTPRICE
-------- --------------- ------------- --------------- ---------- ---------- ---------- ----------
p07975 1.44 drive 5 pieces 10 3 1050 1000
p08865 1.22 drive 5 pieces 2 3 1050 1000
- FIND ALL THE PRODUCTS WHERE SELLING PRICE IS GREATER THAN RS. 5000.
SQL> select*from product_master11 where sellprice>=5000;
PRODUCT_N DESCRIPTION PROFITPERCENT UNITMEASURE QUANTITY REORDER SELLPRICE COSTPRICE
-------- -------------- ------------- - ------------- - ---------- ---------- ---------- ----------
p03453 monitor 6 pieces 10 20 12000 11200
p07885 cd drive 3 pieces 10 3 5250 5100
p07965 540HDD 4 pieces 10 3 8400 8000
- FIND ALL THE LIST OF CLIENTS WHO STAY IN CITY BOMBAY OR DELHI OR MADRAS.
SQL> select*from client_master11 where city='madras'or city='delhi'or city='bomb
ay';
CLIENT NAME ADDRESS CITY STATE PINCODE BALANCE_DUE
------ -------------------- ------------------------------ --------------- --------------- --------------- - ----------
0003 pramada NULL bombay maharastra 400057 5000
0004 basu NULL bombay maharastra 400056 0
0005 ravi NULL delhi delhi 100001 2000
0006 rukmani NULL bombay maharastra 400050 0
0001 Ivan NULL bombay maharastra 400054 15000
0002 vandana NULL madras tamil nadu 780001
- Find the Product whose sellingprice>2000and less than or equal to 5000.
SQL> select*from product_master11 where sellprice between 1999 and 5001;
PRODUCT_ DESCRIPTION PROFITPERCENT UNITMEASURE QUANTITY REORDER SELLPRICE COSTPRICE
--------------------------------- --------------- ------------- --------------- ---------- -------------------- ----------
p07868 keyboard 2 pieces 10 3 3150 3050
- 10.List the name city and state of client who not stay in state of maharastra.
SQL> select*from client_master11 where state not in 'maharastra';
CLIENT NAME ADDRESS CITY STATE PINCODE BALANCE_DUE
------ -------------------- -------------------------- -------------- - -------------- - --------------- -----------
0005 ravi NULL delhi delhi 100001 2000
0002 vandana NULL madras tamil nadu 780001 0
0007 ankit NULL ghaziabad uttar pradesh 201010 0
ASSIGNMENT 2
USING THE TABLE CLIENT MASTER AND PRODUCT MASTER ANSWER THE FOLLOWING QUESTIONS.
- Q1 CHANGE THE SELLING PRICE OF 1.44 FLOPPY DRIVE TO 1150.
SQL> update product_master set
sell_price = 1150
where description='1.44 floppies'
- Q2 DELETE THE RECORD WITH CLIENT 0001 FROM THE CLIENT MASTER.
SQL> delete from client_master
where client_no=0001;
- Q3 CHANGE THE CITY OF CLIENT_NO 0005 TO BOMBAY.
SQL> update client_master set
city ='bombay'
where client_no=0005;
- Q4 CHANGE THE BAL_DUE OF CLIENT_NO 0001 TO 1000.
SQL> update client_master set
bal_due=1000
where client_no=0002;
- Q5 FIND THE PRODUCTS WHOSE SELLING PRICE IS MORE THAN 1500 AND ALSO FIND THE NEW SELLING PRICE AS ORIGINAL SELLING PRICE *15.
SQL> select description,sell_price,(sell_price)*15 "new SP" from product_master
where sell_price>1500
- Q6 FIND OUT THE CLIENT WHO STAYS IN THE CITY WHOSE SECOND LETTER IS A.
SQL> select*from client_master
where city like '_a%'
- Q7 FIND OUT THE NAME OF THE CLIENTS HAVING A AS THE SECOND LETTER IN THEIR NAMES.
SQL> select*from client_master
where name like '_a%'
- Q8 LIST THE PRODUCTS IN SORTED ORDER OF THEIR DESCRIPTION.
SQL> select description from product_master order by description asc;
- Q9 COUNT THE TOTAL NUMBER OF PRODUCTS.
SQL> SELECT count(DISTINCT description)
FROM product_master;
- Q10 CALCULATE THE AVERAGE PRICE OF ALL THE PRODUCTS.
SQL> SELECT AVG(costprice)
FROM product_master;
- Q11 CALCULATE THE MINIMUM PRICE OF THE PRODUCTS.
SQL> SELECT MIN(costprice)
FROM product_master;
- Q12 DETERMINE THE MAXIMUM AND MINIMUM PRICES. RENAME THE TITLE AS ‘MAX _PRICE’AND ‘MIN_PRICE’ RESPECTIVELY.
SQL> SELECT MIN(costprice)"MIN_PRICE" ,MAX(costprice) "MAX_PRICE"
FROM product_master;
- Q13 COUNT THE NUMBER OF PRODUCTS HAVING PRICE GREATER THAN OR EQUAL TO 1500.
SQL> SELECT COUNT(product_no)
FROM product_master
WHERE costprice >1499;
ASSIGNMENT 3
(RESTRICTION ON TABLE )
CREATE THE FOLLOWING TABLE WITH THE FOLLOWING RESTRICTION
Sales_master
SQL> create table sales_master
( salesman_no varchar2(6)CHECK(salesman_no LIKE 's%'),
sal_names varchar2(20)NOT NULL,
Address varchar2(20)NOT NULL,
city varchar2(20),
state varchar2(20),
pincode varchar2(6),
sal_amount number(8,2)NOT NULL,
tgt_to_get number(6,2)NOT NULL,
ytd_sales number(6,2)NOT NULL,
remarks varchar2(30),
PRIMARY KEY(salesman_no),
CHECK(tgt_to_get !=0),
CHECK(sal_amount !=0),
CHECK(ytd_sales!=0 ));
CHECK(ytd_sales!=0 ));
SQL> desc sales_master11;
SQL> select*from sales_master011;
Saless_order
SQL> create table sales_order11(
s_order_no varchar2(6)CHECK(s_order_no LIKE 'o%'),
s_order_date date,
client_no varchar2(25),
dely_add varchar2(6),
salesman_no varchar2(6),
dely_type char(1) default('f'),
billed_yn char(1),
dely_date date,
order_status varchar2(10),
CHECK(s_order_date>dely_date),
CHECK(order_status IN('in process','fulfilled','back order','canceled')),
PRIMARY KEY(s_order_no),
FOREIGN KEY(salesman_no)REFERENCES sales_master11,
CHECK(dely_type IN('p','f')));
SQL> desc sales_order11
SQL> select*from sales_order011;
sales_order_details
SQL> create table sales_order_details11(
s_order_no varchar2(6)PRIMARY KEY,
product_no varchar2(6),
qty_no number(8),
qty_disp number(8),
product_rate number(10,2),
FOREIGN KEY(s_order_no )REFERENCES sales_order11);
SQL> desc sales_order_details11;
SQL> select* from sales_order_details011;
ASSIGNMENT 4
Q1 CREATE THE FOLLOWING TABLE WITH NAME CHALLAN HEADER
SQL> create table challanheader
(challan_no varchar2(6) PRIMARY KEY,
s_order_no varchar2(6),
challan_date date NOT NULL,
billed_yn char(1) default('f'),
CHECK(billed_yn IN('y','n')),
FOREIGN KEY(s_order_no)REFERENCES sales_order);
Q2 CREATE THE TABLE WITH NAME CHALLAN_DETAILS
SQL>create table challan_details
(challan_no varchar2(6) PRIMARY KEY,
product_no varchar2(6),
qty_displ number(4,2) NOT NULL)
PERFORM THE FOLLOWING
- Q1 MAKE THE PRIMARY KEY TO CLIENT_NO IN CLIENT_MASTER.
SQL> alter table
client_master
ADD PRIMARY KEY(client_no);
- Q2 ADD A NEW COLUMN PHONE_NO IN THE CLIENT_MASTER TABLE.
SQL> alter table
product_master
ADD(phone_no number(12));
- Q3 ADD THE NULL CONSTRAINT IN THE PRODUCT_MASTER TABLE WITH THE COLUMN DESCRIPTION, PROFIT PERCENT, SELL PRICE AND COST PRICE.
SQL> alter table
product_master
MODIFY(sellprice number(8) NOT NULL)
MODIFY(costprice number(8) NOT NULL)
MODIFY(profitpercent number(6) NOT NULL)
MODIFY(description varchar2(15) NOT NULL);
- Q4 CHANGE THE SIZE OF THE CLIENT_NO FIELD IN THE CLIENT_MASTER TABLE.
SQL> alter table
client_master
MODIFY(client_no varchar2(20));
- Q5 SELECT PRODUCT_NO, DESCRIPTION WHERE PROFIT PERCENT IS BETWEEN 20 AND 30 BOTH INCLUSIVE
SQL> select product_no,description from product_master
where profitpercent between 20 and 30;
ASSIGNMENT 5
IMPLEMENT THE CONCEPT OF JOINS
- FIND OUT THE PRODUCT WHICH HAS BEEN SOLD TO ‘IVAN SAYROSS.’
Select product_no,description
From product_master11
Where Item_sold_to=’Ivan Sayross’;
- FIND OUT THE PRODUCT AND THEIR QUANTITIES THAT WILL HAVE DO DELIVERED.
Select product_description,product_quantity
From product_master
Where status=’deliver’;
- FIND OUT THE NAMES OF CLIENTS WHO HAVE PURCHASED ‘CD DRIVE’
Select c.name
From client_master11 c, product_master11 p
Where c.product_id=p.product_id
AND p.description =’Cd drive’;
- LIST THE PRODUCT_NO AND S_ORDER_NO OF CUSTOMERS HAAVING QTY ORDERED LESS THAN 5 FROM THE ORDER DETAILS TABLE FOR THE PRODUCT “1.44 FLOPPIES”.
Select p.product_no,p.s_orderno
From sales_order11 p,sales_order_detail s
Where p.product_no=s.product_no
And s.quantity<5 AND s.description =’1.44 floppies’;
- FIND THE PRODUCTS AND THEIR QUANTITIES FOR THE ORDERS PLACED BY CLIENT_NO “ C00001” AND “C00002”
Select p.product_no,p.quantities,p.description
From sales_order p,client_master c
Where p.product_no=c.itemorder-id
And c.client_no=’C000001’ OR ’C000002’;
ASSIGNMENT 6
CONCEPT OF GROUPING OF DATA.
- Print the description and total quantity sold for each product.
Select description,quantity_sold
From product_master11
Order by quantity sold ASC;
- Find the names of clients who have ‘CD Drive’.
Select name from client_master
Where item_bought =’CD DRIVE’
Group by name;
- Select product_no, product description and qty ordered for each product.
Select product_no,quantity_ordered,description
From sales_order_details11
Group by product_no;
ASSIGNMENT 7
CONCEPT OF INDEXES AND VIEWS.
- CREATE A VIEW THA CONTAINS DETAILS OF EMPLOYEE IN DEPARTMENT 40.
SQL> create view employees11
2 AS select employee_id,last_name,salary
3 from employees
4 where department_id =40;
View created.
UPDATING
SQL> select * from employees11;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
203 Mavris 6500
SQL> update employees11 set salary = 6600
2 where employee_id=203;
1 row updated.
INSERTION NOT ALLOWED
SQL> insert into employees11 values('787','Marwadi',2345);
insert into employees11 values('787','Marwadi',2345)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("IT11"."EMPLOYEES"."EMAIL")
- CREATE A VIEW OF DEPARTMENT NAME,MINIMUM,MAXIMUM,AVERAGE SALARY FROM EMPLOYEE AND DEPARTMENT TABLE.
SQL> create view new_sal(dname,maxsal,minsal,avgsal)
2 as select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
3 from employees e, departments d
4 where e.department_id=d.department_id
5 group by department_name
6 /
View created.
SQL> select*from new_sal;
DNAME MAXSAL MINSAL AVGSAL
------------------------------ ---------- ---------- ----------
Accounting 8300 12000 10150
Administration 4400 4400 4400
Executive 17000 24000 19333.3333
Finance 6900 12000 8600
Human Resources 6600 6600 6600
IT 4200 9000 5760
Marketing 6000 13000 9500
Public Relations 10000 10000 10000
Purchasing 2500 11000 4150
Sales 6100 14000 8955.882
Shipping 2100 8200 3475.55556
11 rows selected.
ASSIGNMENT 8
- WAP in pl/sql for addition of two numbers.
SQL> declare
2 a number(2);
3 b number(2);
4 c number(2);
5 begin
6 a:=&a;
7 b:=&b;
8 c:=a+b;
9 dbms_output.put_line(a|| ' + '||b||' = '||c);
10 end;
11 /
Enter value for a: 44
old 6: a:=&a;
new 6: a:=44;
Enter value for b: 43
old 7: b:=&b;
new 7: b:=43;
44 + 43 = 87
PL/SQL procedure successfully completed.
- WAP in pl/sql for addition of 1 to 100.
SQL> Declare
2 i number;
3 z number:=0;
4 Begin
5 for i in 1..100 loop
6 z:=z+i;
7 end loop;
8 dbms_output.put_line('TOTAL SUM IS='||z);
9 end;
10 /
TOTAL SUM IS=5050
PL/SQL procedure successfully completed.
- Wap in pl/sql to check whether the given number is even or odd.
SQL> declare
2 a number(4);
3 begin
4 a:=&a;
5 if mod(a,2)=0 then
6 dbms_output.put_line('A is an even NUMBER');
7 else
8 dbms_output.put_line('A is an odd NUMBER');
9 end if;
10 end;
11 /
Enter value for a: 32
old 4: a:=&a;
new 4: a:=32;
A is an even NUMBER
PL/SQL procedure successfully completed.
- Wap in sql/pl to inverse a number.
SQL> declare
2 n number := &n;
3 s number := 0;
4 i number;
5 begin
6 while(n>0)
7 loop
8 i := n mod 10;
9 s :=(s*10) +i;
10 n := floor(n/10);
11 end loop;
12 dbms_output.put_line(s||'is reverse of a number');
13 end;
14 /
Enter value for n: 456
old 2: n number := &n;
new 2: n number := 456;
654is reverse of a number
PL/SQL procedure successfully completed.
- TO GENERATE FIBONNACI SERIES
SQL> declare
2
3 i number(4):=0;
4 j number(4):=1;
5 k number(4):=0;
6 begin
7 dbms_output.put_line(i);
8 dbms_output.put_line(j);
9 for l IN 2..8 loop
10 k:=i+j;
11 i:=j;
12 j:=k;
13 dbms_output.put_line(k);
14 end loop;
15 end;
16 /
0
1
1
2
3
5
8
13
21
PL/SQL procedure successfully completed.
Introduction to SQL and installation of a database product
ReplyDelete2. Data Types, Creating Tables, Retrieval of Rows using Select Statement, Conditional Retrieval of Rows, Alter and Drop Statements.
3.Working with Null Values, Matching a Pattern from a Table, Ordering the Result of a Query, Aggregate Functions,Grouping the Result of a Query, Update and Delete Statements.
4.Set Operators, Nested Queries, Joins, Sequences.
5.Views, Indexes, Database Security and Privileges: Grant and Revoke Commands, Commit and Rollback Commands.
6.PL/SQL Architecture, Assignments and Expressions, Writing PL/SQL Code, Referencing Non-SQL parameters.
7.Stored Procedures and Exception Handling.
8.Triggers and Cursor Management in PL/SQL.
9.Working with XML databases
10.Introduction to a data mining tool