Dharanyadevi blogspot subsume with E-books, Notes, Lab Manual, Question Banks, Interview Tips, Viva Questions, Basics and Interview Questions for engineering students. For Any Help Contact dharanyadevi@gmail.com

SEARCH
Image

Monday, September 17, 2012

DBMS Lab Manual




QUERIES USING DDL COMMANDS:

CREATE:

mysql> create table branch(branch_name varchar(20),branch_city varchar(20),asse
ts int(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc branch;
+------------- + ------------- + ------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------- + ------------- + ------+-----+---------+-------+
| branch_name | varchar(20) | YES | | NULL | |
| branch_city | varchar(20) | YES | | NULL | |
| assets | int(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table customer(customer_name char(20),customer_street char(20),cu
stomer_city char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc customer;
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| customer_name | char(20) | YES | | NULL | |
| customer_street | char(20) | YES | | NULL | |
| customer_city | char(20) | YES | | NULL | |
+-----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table loan(loan_number varchar(20),branch_name char(20),amount in
t(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc loan;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| loan_number | varchar(20) | YES | | NULL | |
| branch_name | char(20) | YES | | NULL | |
| amount | int(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table borrower(customer_name char(20),loan_number varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc borrower;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| customer_name | char(20) | YES | | NULL | |
| loan_number | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table account(account_number varchar(20),branch_name char(20),bal
ance int(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc account;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| a_num | varchar(20) | YES | | NULL | |
| br_name | varchar(20) | YES | | NULL | |
| bal | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table depositor(customer_name char(20),account_number varchar(20)
);
Query OK, 0 rows affected (0.00 sec)

mysql> desc depositor;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| customer_name | char(20) | YES | | NULL | |
| account_number | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

NOTE:
CHAR datatype accepts only characters,INT accepts only integers whereas
VARCHAR accepts both integers and characters.

ALTER:

mysql> alter table depositor add amount int(20) after customer_name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc depositor;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| customer_name | char(20) | YES | | NULL | |
| amount | int(20) | YES | | NULL | |
| account_number | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table borrower add amount int(20) first;
Query OK, 0 rows affected(0.02)
mysql> desc borrower;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| amount | int(20) | YES | | NULL | |
| customer_name | char(20) | YES | | NULL | |
| loan_number | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table loan add primary key(loan_number);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc loan;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| loan_number | varchar(20) | NO | PRI | | |
| branch_name | char(20) | YES | | NULL | |
| amount | int(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

DROP:

mysql> alter table depositor drop amount;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc depositor;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| customer_name | char(20) | YES | | NULL | |
| account_number | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table borrower drop amount;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc borrower;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| customer_name | char(20) | YES | | NULL | |
| loan_number | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

QUERIES USING DML COMMANDS

INSERT:
mysql> insert into branch values('brighton','brooklyn',7100000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into branch values('downtown','brooklyn',90000000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from branch;
+-------------+-------------+----------+
| branch_name | branch_city | assets |
+-------------+-------------+----------+
| brighton | brooklyn | 7100000 |
| downtown | brooklyn | 90000000 |
| mianus | horseneck | 4000000 |
| northtown | rye | 37000000 |
| perryridge | horseneck | 17000000 |
| pownal | bennighton | 3000000 |
| redwood | paloalto | 2100000 |
| roundhill | horseneck | 8000000 |
+-------------+-------------+----------+
8 rows in set (0.00 sec)

mysql> mysql> insert into account(account_number,branch_name,balance) values('A_101',
'downtown',500),('A_102','perryridge',400),('A_201','brighton',900),('A_215','m
ianus',700),('A_217','brighton',750),('A_222','redwood',700),('A_305','roundhil
l',350);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from account;
+----------------+-------------+---------+
| account_number | branch_name | balance |
+----------------+-------------+---------+
| A_101 | downtown | 500 |
| A_102 | perryridge | 400 |
| A_201 | brighton | 900 |
| A_215 | mianus | 700 |
| A_217 | brighton | 750 |
| A_222 | redwood | 700 |


DELETE:
mysql> delete from account;
Query OK, 5 rows affected (0.00 sec)

mysql> desc account;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| a_num | varchar(20) | YES | | NULL | |
| br_name | varchar(20) | YES | | NULL | |
| bal | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> drop table account;
Query OK, 0 rows affected (0.00 sec)

mysql> desc account;
ERROR 1146 (42S02): Table 'ranjitha.account' doesn't exist

NOTE:
DELETE command deletes only the tuples where as DROP command deletes whole schema.


SELECT AND FROM CLAUSES :

mysql> select * from loan;
+-------------+-------------+--------+
| loan_number | branch_name | amount |
+-------------+-------------+--------+
| l_11 | roundhill | 900 |
| l_14 | downtown | 1500 |
| l_15 | perryridge | 1500 |
| l_16 | perryridge | 1300 |
| l_17 | downtown | 1000 |
| l_23 | redwood | 2000 |
| l_93 | mianus | 500 |
+-------------+-------------+--------+
7 rows in set (0.01 sec)

mysql> select loan_number
from loan;
+-------------+
| loan_number |
+-------------+
| l_11 |
| l_14 |
| l_15 |
| l_16 |
|l_17 |
7 rows in set (0.00 sec)
mysql> select distinct(branch_name)
from branch;
+-------------+
| branch_name |
+-------------+
| brighton |
| downtown |
| mianus |
| northtown |
| perryridge |
| pownal |
| redwood |
| roundhill |
+-------------+
8 rows in set (0.00 sec)

mysql> select loan_number,branch_name,amount*100
from loan;
+-------------+-------------+------------+
| loan_number | branch_name | amount*100 |
+-------------+-------------+------------+
| l_11 | roundhill | 90000 |
| l_14 | downtown | 150000 |
| l_15 | perryridge | 150000 |
| l_16 | perryridge | 130000 |
| l_17 | downtown | 100000 |
| l_23 | redwood | 200000 |
| l_93 | mianus | 50000 |
+-------------+-------------+------------+
7 rows in set (0.00 sec)

WHERE CLAUSE:

1. Find all the loan numbers made at the perryridge branch with loan amounts greater then 1200.

mysql> select loan_number
-> from loan
-> where branch_name='perryridge' and amount>1200;
-------------+
loan_number |
-------------+
l_15 |
l_16 |
-------------+
rows in set (0.00 sec)

2. Find the loan numbers of those loans who are having amounts between 900 and 1500.
mysql> select loan_number
-> from loan where amount between 900 and 1500;
loan_number |
-------------+
l_11 |
l_14 |
l_15 |
l_16 |
l_17 |
-------------+
5rows in set (0.00 sec)
OR

ysql> select loan_number
-> from loan
-> where amount<=1500 and amount>=900;
-------------+
loan_number |
-------------+
l_11 |
l_14 |
l_15 |
l_16 |
l_17 |
-------------+
rows in set (0.00 sec)
NOTE:
We can use logical connectives and,or and not and also comparison operators <,<=,>,>=,= and <>.


QUERIES INVOLVING INTEGRITY CONSTRINTS
Integrity constraints include :
 Unique
 Not null
 Primary key
 Foreign key or referential integrity
 check

UNIQUE:
mysql> create table student(sid int(10),sname char(20),unique(sid));
Query OK, 0 rows affected (0.01 sec)

mysql> select * from student;
+------+-------+
| sid | sname |
+------+-------+
| 1 | abc |
| 2 | abc |
| NULL | abc |
| NULL | abc |
mysql> insert into student7 values(2,'abc');
ERROR 1062 (23000): Duplicate entry '2' for key 1

NOTE:
Hence UNIQUE checks only for duplicate values but allows NULL values.

NOT NULL:mysql> create table boats7(bid int(10) not null,color char(20),name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from boats7;
+-----+-----------+-------+
| bid | color | name |
+-----+-----------+-------+
| 101 | interlake | blue |
| 102 | interlake | red |
| 103 | clipper | green |
| 104 | marine | red |
+-----+-----------+-------+
4 rows in set (0.00 sec)

mysql> insert into boats7 values(104,'marine','red');
Query OK, 1 row affected (0.00 sec)

mysql> insert into boats7 values(null,'marine','red');
ERROR 1048 (23000): Column 'bid' cannot be null

NOTE:
NOT NULL checks for only for null values,but it do not check for the UNIQUE values.

PRIMARY KEY:
mysql> create table sailors4(sid int(20),sname char(20),rating int(20),age int(
20),PRIMARY KEY(sid))ENGINE=INNODB;
Query O K, 0 rows affected (0.06 sec)
mysql> select * from sailors;
+------+---------+--------+------+
| sid | sname | rating | age |
+------+---------+--------+------+
| 22 | dustin | 7 | 45 |
| 29 | brutus | 7 | 33 |
| 31 | lubber | 8 | 55 |
| 32 | andy | 8 | 25 |
| 58 | rusty | 10 | 35 |
| 64 | horatio | 7 | 35 |
| 71 | zobra | 10 | 16 |
| 74 | horatio | 9 | 40 |
| 85 | art | 3 | 25 |
| 95 | bob | 3 | 63 |

10 rows in set (0.02 sec)
mysql> insert into sailors4 values(22,'bob',3,35);
ERROR 1062 (23000): Duplicate entry '22' for key 1: Duplicate entry '22' for key 1
mysql>insert into sailors4 values(null,’bob’,4,55);
ERROR 1048(23000):Column ‘sid’ cannot be null.


NOTE:
PRIMARY KEY checks for both NOTNULL and UNIQUE.
mysql> delete from sailors4 where sid=31;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`ranjitha/reserves`, CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`sid`)
REFERENCES `sailors4` (`sid`))

NOTE:
We cant delete directly from the PRIMARY KEY table first we need to delete from the FOREIGN KEY table.


PRIMARY KEY FRO MULTIPLE ATTRIBUTES:

mysql> create table bankdetails(city_code varchar(20),branch_code varchar(20),b
ranch_name char(20),PRIMARY KEY(city_code,branch_code));
Query OK, 0 rows affected (0.01 sec)
mysql> desc bankdetails;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| city_code | varchar(20) | NO | PRI | | |
| branch_code | varchar(20) | NO | PRI | | |
| branch_name | char(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from bankdetalis;
+-----------+-------------+-------------+
| city_code | branch_code | branch_name |
+-----------+-------------+-------------+
| c1 | b1 | hyderabad |
| c1 | b2 | hyderabad |
| c2 | b1 | mumbai |
| c2 | b2 | mumbai |
| c3 | b1 | delhi |
+-----------+-------------+-------------+
5 rows in set (0.00 sec)
mysql> insert into bankdetalis values('c1','b1','hyd');
ERROR 1062 (23000): Duplicate entry 'c1-b1' for key 1

FOREIGN KEY or REFERENTIAL INTEGRITY:

mysql> create table reserves(sid int(20),bid int(20),FOREIGN KEY(sid) reference
s sailors4(sid))ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)
mysql> desc reserves;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid | int(20) | YES | MUL | NULL | |
| bid | int(20) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from reserves;
+------+------+
| sid | bid |
+------+------+
| 22 | 101 |
| 22 | 102 |
| 22 | 103 |
| 22 | 104 |
| 31 | 102 |
| 31 | 103 |
| 31 | 104 |
| 64 | 101 |
| 64 | 102 |
| 74 | 103 |
mysql> insert into reserves values(100,101);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`ranjitha/reserves`, CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`sid`) REFE
RENCES `sailors4` (`sid`))

NOTE:
We use INNODB because use different types of engines for different operations, for DDL and DML commands no need to specify engines,it is a default but for FOREIGN KEY, we need to specify engine.



QUERIES USING SET OPERATORS
Set operatos include:
1.Union
2,Intersect
3.Except
UNION:
Find all the customers who are having account or loan or both at the bank

mysql> select customer_name
from
depositor
union
select
customer_name from borrower;
+----------+
| cus_name |
+----------+
| hayes |
| johnson |
| jones |
| lindsay |
| smith |
| turner |
| adams |
| curry |


INTERSECT:

Find all the customer who are having both account and loan
mysql> select cus_name from
-> dep inner join bor
-> using(cus_name);
+----------+
| cus_name |
+----------+
| hayes |
| jones |
| smith |
| smith |
+----------+
4 rows in set (0.00 sec)

EXCEPT:
Find all the customers who have only account but not loan
mysql> select customer_name
from depositor
where customer_name
not in(select cus_name from bor);
+----------+
| cus_name |
+----------+
| johnson |
| johnson |
| lindsay |
| turner |
+----------+
4 rows in set (0.00 sec)

NESTED QUERIES USING SET COMPARISION
OPERATORS
Set comparision operators include:
1.ANY
2.ALL
3.IN
4.NOT IN
5.EXITS
6.NOTEXISTS

IN:
Find names all sailors who have reserved the boats 104.

mysql> select sname from sailors where sid
IN
(select sid from reserve where b
id=102);
+---------+
| sname |
+---------+
| dustin |
| lubber |
| horatio |
+---------+
3 rows in set (0.00 sec)

NOTIN:

Find names all sailors who have not reserved boat 102

mysql> select sname from sailors where sid
NOTIN
(select sid from reserve where bid=102);
+---------+
| sname |
+---------+
| lubber |
| brutus |
| andy |
| rusty |
| zorba |
| horatio |
| art |
+---------+
7 rows in set (0.00 sec)

EXITS:

Find names all the sailors who are reserved 104.

mysql> select sname from sailors4 where
EXITS
(select sid from reserves where b
id=104 and sailors4.sid=reserves.sid);
+--------+
| sname |
+--------+
| dustin |
| lubber |
+--------+
2 rows in set (0.00 sec)

NOTEXISTS:

Find all the sailors who have not reserved 104

mysql> select sname from sailors4 where
NOTEXISTS
(select sid from reserves whe
re bid=104 and sailors4.sid=reserves.sid);


+---------+
| sname |
+---------+
| lubber |
| brutus |
| andy |
| rusty |
| horatio |
| zorba |
| horatio |
| art |
+---------+
8 rows in set (0.00 sec)

ALL:

Find the branch that has the highest average balance.

mysql> select branch_name
from account
group by branch_name
having avg(balance)>=ALL(select avg(balance)
from account
group by branch_name);
+-------------+
| branch_name |
+-------------+
| brighton |
+-------------+
1 row in set (0.00 sec)

ANY or SOME:

Find all the branch names that have asdsets greater than those of atleast one located in Brooklyn.

mysql> select branch_name
-> from branch
-> where assets>some(select assets from branch where branch_city='brooklyn'
);
+-------------+
| branch_name |
+-------------+
| downtown |
| northtown |
| perryridge |
| roundhill |
+-------------+
4 rows in set (0.03 sec)



(A)_AGGREGATE OPERATORS

To implement the aggregate operators such as
1) AVG
2) SUM
3) MAX
4) MIN
5) COUNT
6) GROUP BY
7) HAVING

1) AVG

It is used to find the average of the column_name in a specified tablename

1) To implement average balance at perryridge branch

mysql> select avg(balance)
-> from account where branch_name='perryridge';
+--------------+
| avg(balance) |
+--------------+
| 1000.0000 |
+--------------+

2) SUM

1) To find the total amount for each branch


mysql> select branch_name,sum(amount)
-> from loan1 group by branch_name;
+-------------+-------------+
| branch_name | sum(amount) |
+-------------+-------------+
| downtown | 2500 |
| mianus | 500 |
| perryridge | 2800 |
| redwood | 2000 |
| roundhill | 900 |
+-------------+-------------+

3) MAX

1) To find the maximum assets for each branch

mysql> select branch_name ,max(assets)
-> from branch
-> group by branch_name;
+-------------+-------------+
| branch_name | max(assets) |
+-------------+-------------+
| brighton | 7100000 |
| downtown | 9000000 |
| mianus | 400000 |
| northtown | 3700000 |
| perryridge | 1700000 |
| pownal | 300000 |
| redwood | 2100000 |
| roundhill | 8000000 |
+-------------+-------------+

4) MIN

1)To find the minimum balance for each branch

mysql> select branch_name ,min(balance) from account group by branch_name;
+-------------+--------------+
| branch_name | min(balance) |
+-------------+--------------+
| adams | 900 |
| brighton | 2000 |
| downtown | 1000 |
| perryridge | 600 |
+-------------+--------------+

5) COUNT

1) To count the number of distinct branch_name

mysql> select count(distinct branch_name) from branch;
+-----------------------------+
| count(distinct branch_name) |
+-----------------------------+
| 8 |
+-----------------------------+


6) GROUP BY

1) To find number of depositers for each branch


mysql> select branch_name,count(distinct customer_name) from account,depositer1
where account.account_number=depositer1.account_number group by branch_name;
+-------------+-------------------------------+
| branch_name | count(distinct customer_name) |
+-------------+-------------------------------+
| adams | 1 |
| downtown | 1 |
| perryridge | 1 |
+-------------+-------------------------------+

7) HAVING

1) To impl
mysql> select branch_name,avg(balance) from account group by branch_name having
avg(balance)>400;
+-------------+--------------+
| branch_name | avg(balance) |
+-------------+--------------+
| adams | 900.0000 |
| brighton | 2000.0000 |
| downtown | 1000.0000 |
| perryridge | 1000.0000 |



(B) VIEWS:

Views are divided into two types:
1) simple view
2) complex view

SIMPLE VIEWS

1) creating a simple view

mysql> create view s as( select branch_name from branch);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from s;
+-------------+
| branch_name |
+-------------+
| brighton |
| downtown |
| mianus |
| northtown |
| perryridge |
| pownal |
| redwood |
| roundhill |
+-------------+

2) inserting the values into simple views
mysql> insert into s values('ashwin');
Query OK,

mysql> select * from s;
+-------------+
| branch_name |
+-------------+
| brighton |
| downtown |
| mianus |
| northtown |
| perryridge |
| pownal |
| redwood |
| roundhill |
| ashwin |
+-------------+

3) deleting the values from simple views
mysql> delete from s where branch_name='ashwin';
Query OK, 1 row affected (0.00 sec)
mysql> select * from s;
+-------------+
| branch_name |
+-------------+
| brighton |
| downtown |
| mianus |
| northtown |
| perryridge |
| pownal |
| redwood |
| roundhill |
+-------------+
COMPLEX VIEWS

1) Creating a complex view

mysql> desc branch;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| branch_name | varchar(20) | YES | | NULL | |
| branch_city | varchar(20) | YES | | NULL | |
| assets | int(10) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc account;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| account_number | varchar(10) | YES | | NULL | |
| branch_name | varchar(20) | YES | | NULL | |
| balance | int(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create view R as(select branch_city,assets,account_number,balanc
e,account.branch_name from account join branch where account.branch_nam
e=branch.branch_name);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from R;
+-------------+---------+----------------+---------+-------------+
| branch_city | assets | account_number | balance | branch_name |
+-------------+---------+----------------+---------+-------------+
| brooklyn | 7100000 | a-105 | 2000 | brighton |
| brooklyn | 9000000 | a-101 | 1000 | downtown |
| horseneck | 1700000 | a-102 | 1400 | perryridge |
| horseneck | 1700000 | a-102 | 600 | perryridge |
+-------------+---------+----------------+---------+-------------+

2) inserting a new value in complex views
mysql> insert into R values('heeklyn',330000,'a-205',2000,'grighton');
ERROR 1394 (HY000): Can not insert into join view 'cse05.R' without fie

3) deleting the values from complex views
mysql> delete from R where assets=7100000;
ERROR 1395 (HY000): Can not delete from join view 'cse05.R'

4) updating values into complex views
mysql> update view R set balance=balance*2;
ERROR 1146 (42S02): Table 'cse05.view' doesn't exist


NOTE: The MODIFICATIONS ARE NOT DONE TO THE COMPLEX VIEWS



To implement Mathematical, string and date functions

1) Mathematical functions

a) abs(x):
mysql> select abs(5);
+--------+
| abs(5) |
+--------+
| 5 |
+--------+

b) acos(x):
mysql> select acos(0);
+-----------------+
| acos(0) |
+-----------------+
| 1.5707963267949 |
+-----------------+

c) atan(x):
mysql> select atan(1);
+------------------+
| atan(1) |
+------------------+
| 0.78539816339745 |
+------------------+

d) asin(x):
mysql> select asin(1);
+-----------------+
| asin(1) |
+-----------------+
| 1.5707963267949 |
+-----------------+

e) ceil(x):
mysql> select ceil(1);
+---------+
| ceil(1) |
+---------+
| 1 |
+---------+

f)cos(x):
mysql> select cos(pi());
+-----------+
| cos(pi()) |
+-----------+
| -1 |
+-----------+

f) cot(x):
mysql> select cot(1);
+------------------+
| cot(1) |
+------------------+
| 0.64209261593433 |
+------------------+

g) degrees(x):
mysql> select degrees(pi());
+---------------+
| degrees(pi()) |
+---------------+
| 180 |
+---------------+

h) exp(x):
mysql> select exp(-1);
+------------------+
| exp(-1) |
+------------------+
| 0.36787944117144 |
+------------------+

i) floor(x):
mysql> select floor(5.68);
+-------------+
| floor(5.68) |
+-------------+
| 5 |
+-------------+

j) ln(x):
mysql> select ln(3);
+-----------------+
| ln(3) |
+-----------------+
| 1.0986122886681 |
+-----------------+

k) log(x):
mysql> select log(1);
+--------+
| log(1) |
+--------+
| 0 |
+--------+

l) log(b,x) :
mysql> select log(3,4512);
+----------------+
| log(3,4512) |
+----------------+
| 7.659204143237 |

m) log2(x) :
mysql> select log2(7215);
+-----------------+
| log2(7215) |
+-----------------+
| 12.816783679379 |
+-----------------+

n) log10(x):

mysql> select log10(5);
+------------------+
| log10(5) |
+------------------+
| 0.69897000433602 |
+------------------+

0) MOD(N,M),n%m
mysql> select mod(123,10);
+-------------+
| mod(123,10) |
+-------------+
| 3 |
+-------------+

mysql> select 111%8;
+-------+
| 111%8 |
+-------+
| 7 |
+-------+

o) pi():
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+


STRING FUNCTIONS:

1) CHAR_LENGTH(str)

mysql> select char_length("hiox");
+---------------------+
| char_length("hiox") |
+---------------------+
| 4 |
+---------------------+

2) CONCAT(str1,str2…)

mysql> select concat('hiox','india');
+------------------------+
| concat('hiox','india') |
+------------------------+
| hioxindia |
+------------------------+

3) CONCAT_WS( ):
mysql> select concat_ws('!','one','two','three');
+------------------------------------+
| concat_ws('!','one','two','three') |
+------------------------------------+
| one!two!three |
+------------------------------------+

4) LCASE(str):
mysql> select lcase('HIOX');
+---------------+
| lcase('HIOX') |
+---------------+
| hiox |
+---------------+

5) LENGTH(str):
mysql> select length('hioxindia');
+---------------------+
| length('hioxindia') |
+---------------------+
| 9 |
+---------------------+


DATE FUNCTIONS:

1) CURTIME( )

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 13:53:23 |
+-----------+

2) CURDATE( ):
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2009-03-17 |
+------------+

3) NOW( ):
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-03-17 13:54:21 |
+---------------------+

4) MONTHNAME(DATE):
mysql> select monthname("2009-1-20");
+------------------------+
| monthname("2009-1-20") |
+------------------------+
| January |
+------------------------+

5) MINUTE(TIME):
mysql> select minute("12:11:21");
+--------------------+
| minute("12:11:21") |
+--------------------+
| 11 |
+--------------------+




CONTROL FLOW STATEMENTS

Control flow statements include:
1.If
2.Case
3.Nullif
4.Ifnull

IF:
mysql> select branch_name,
if(branch_name='perryridge','yes','no')
from branch;

+-------------+-----------------------------------------+
| branch_name | if(branch_name='perryridge','yes','no') |
+-------------+-----------------------------------------+
| brighton | no |
| downtown | no |
| mianus | no |
| northtown | no |
| perryridge | yes |
| pownal | no |
| redwood | no |
| roundhill | no |
+-------------+-----------------------------------------+
8 rows in set (0.00 sec)

CASE:

mysql> select * from std;

+------+-------+
| name | marks |
+------+-------+
| pqr | 60 |
| xyz | 80 |
| abc | 75 |
| ijh | 65 |
| rs | 50 |
| ijh | 35 |
+------+-------+
6 rows in set (0.00 sec)

mysql> select marks,case when marks>=75 then 'distinction' when marks<75 and ma rks>=60 then 'first class' when marks<60 and marks>=50 then 'second class' when
marks<50 and marks>=40 then 'third class' else 'fail' end from std;


+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------+
| marks | case when marks>=75 then 'distinction' when marks<75 and marks>=60 the
n 'first class' when marks<60 and marks>=50 then 'second class' when marks<50 a nd marks>=40 then 'third class' else 'fail' end |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------+
| 60 | first class

|
| 80 | distinction

|
| 75 | distinction

|
| 65 | first class

|
| 50 | second class

|
| 35 | fail

|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------+
6 rows in set (0.00 sec)

IFNULL:

mysql> select ifnull(null,1);
+----------------+
| ifnull(null,1) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)

mysql> select ifnull(1,2)
-> ;
+-------------+
| ifnull(1,2) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

mysql> select ifnull(1,null);
+----------------+
| ifnull(1,null) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)

mysql> select ifnull(null,2);
+----------------+
| ifnull(null,2) |
+----------------+
| 2 |
+----------------+
1 row in set (0.00 sec)

NULLIF:

mysql> select nullif(1,1);
+-------------+
| nullif(1,1) |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select nullif(1,2);
+-------------+
| nullif(1,2) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

CREATING PROCEDURES

1. CREATE PROCEDURE p1 ()
Select 'Hello, world'


Output:
mysql> call p1()//
+-------------+
| Hello,world |
+-------------+
| Hello,world |
+-------------+
1 row in set (0.02 sec)


2. CREATE PROCEDURE p2()
select * from ACCOUNT ;

Output:

mysql> call p2()//
+----------------+-------------+---------+
| account_number | branch_name | balance |
+----------------+-------------+---------+
| A-305 | Roundhill | 1100 |
| A-222 | Redwood | 1000 |
| A-215 | Mianus | 1000 |
| A-102 | Perryridge | 900 |
+----------------+-------------+---------+
4 rows in set (0.01 sec)

PASSING PARAMETERS IN,OUT,IN-OUT

1. CREATE PROCEDURE `p3`(IN p INT)
set @x=p


Output:

mysql> call p3(10)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @x//
+------+
| @x |
+------+
| 10 |
+------+
1 row in set (0.00 sec)

2.
CREATE PROCEDURE `p4`(in acc int,in bal int)
insert into bank values(acc,bal)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 100 | 10000 |
| 101 | 10000 |
| 102 | 20000 |
| 103 | 5000 |
| 104 | 15000 |
| 105 | 6000 |
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
| 1000 | 400 |
+-------+---------+
10 rows in set (0.03 sec)

Output:

mysql> call p4(1,10000)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 100 | 10000 |
| 101 | 10000 |
| 102 | 20000 |
| 103 | 5000 |
| 104 | 15000 |
| 105 | 6000 |
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
| 1000 | 400 |
| 1 | 10000 |
+-------+---------+
11 rows in set (0.00 sec)



5.
CREATE PROCEDURE `p5`(in acc int,out bal int)
select balance into bal from bank where accno=acc |

Output:

mysql> call p5(1,@y)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @y//
+-------+
| @y |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)


6.
CREATE PROCEDURE `p6`(out p int)
set p=-5 |

Output:

mysql> call p6(@y)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @y//
+------+
| @y |
+------+
| -5 |
+------+
1 row in set (0.00 sec)



CREATING PROCEDURES

1. CREATE PROCEDURE p1 ()
Select 'Hello, world'


Output:
mysql> call p1()//
+-------------+
| Hello,world |
+-------------+
| Hello,world |
+-------------+
1 row in set (0.02 sec)


2. CREATE PROCEDURE p2()
select * from ACCOUNT ;

Output:

mysql> call p2()//
+----------------+-------------+---------+
| account_number | branch_name | balance |
+----------------+-------------+---------+
| A-305 | Roundhill | 1100 |
| A-222 | Redwood | 1000 |
| A-215 | Mianus | 1000 |
| A-102 | Perryridge | 900 |
+----------------+-------------+---------+
4 rows in set (0.01 sec)

PASSING PARAMETERS IN,OUT,IN-OUT

1. CREATE PROCEDURE `p3`(IN p INT)
set @x=p


Output:

mysql> call p3(10)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @x//
+------+
| @x |
+------+
| 10 |
+------+
1 row in set (0.00 sec)

2.
CREATE PROCEDURE `p4`(in acc int,in bal int)
insert into bank values(acc,bal)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 100 | 10000 |
| 101 | 10000 |
| 102 | 20000 |
| 103 | 5000 |
| 104 | 15000 |
| 105 | 6000 |
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
| 1000 | 400 |
+-------+---------+
10 rows in set (0.03 sec)

Output:

mysql> call p4(1,10000)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 100 | 10000 |
| 101 | 10000 |
| 102 | 20000 |
| 103 | 5000 |
| 104 | 15000 |
| 105 | 6000 |
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
| 1000 | 400 |
| 1 | 10000 |
+-------+---------+
11 rows in set (0.00 sec)



5.
CREATE PROCEDURE `p5`(in acc int,out bal int)
select balance into bal from bank where accno=acc |

Output:

mysql> call p5(1,@y)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @y//
+-------+
| @y |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)


6.
CREATE PROCEDURE `p6`(out p int)
set p=-5 |

Output:

mysql> call p6(@y)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @y//
+------+
| @y |
+------+
| -5 |
+------+
1 row in set (0.00 sec)

PROCEDURES USING DECLARATION AND EXCXEPTION HANDLING AND LOOPS

CREATE PROCEDURE `p7`()
Begin declare name char(20); set name='svpcet'; select name; end |

Output:

mysql> call p7()//
+--------+
| name |
+--------+
| svpcet |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

8. CREATE PROCEDURE `p8`()
begin declare x1 char(5) default 'outer'; begin declare x1 char(5) default 'inner'; select x1; end; select x1; end |

Output:

mysql> call p8()//
+-------+
| x1 |
+-------+
| inner |
+-------+
1 row in set (0.00 sec)

+-------+
| x1 |
+-------+
| outer |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)



9.
CREATE PROCEDURE `p9`(in p int)
begin if(p=0) then select 'hai';else select 'bye'; end if; end |

Output:

mysql> call p9(0)//
+-----+
| hai |
+-----+
| hai |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p9(1)//
+-----+
| bye |
+-----+
| bye |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

10.
CREATE PROCEDURE `p10`(IN n INT)
begin declare i INT; set i=1; while i call p10(5)//
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

11.
CREATE PROCEDURE `p11`(IN n INT)
begin declare i Int; set i=1; repeat select i; set i=i+1; until i=n end repeat;
end |

Output:

mysql> call p11(6)//
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

+------+
| i |
+------+
| 5 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

12.
CREATE PROCEDURE `p14`(IN n INT)
Begin Declare v int; set v=0; l1:loop select v; set v=v+1; if v>=n then leave l1
; end if; end loop; end |

Output:

mysql> call p14(3)//
+------+
| v |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

+------+
| v |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| v |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

13.
CREATE PROCEDURE `p15`( IN n INT)
begin declare v int;set v=0; l2:loop select v; set v=v+1; if v<=n then iterate l2; else leave l2; end if; end loop;end | Output: mysql> call p15(4)//
+------+
| v |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

+------+
| v |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| v |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

+------+
| v |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

+------+
| v |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

14.
CREATE PROCEDURE `p16`(IN n INT)
Begin Declare v int; set v=n; case v when 0 then select 'hai'; when 1 then selec
t 'bye'; else select 'who r u?'; end case; end |

mysql> call p16(0)//
+-----+
| hai |
+-----+
| hai |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p16(1)//
+-----+
| bye |
+-----+
| bye |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p16(5)//
+----------+
| who r u? |
+----------+
| who r u? |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

15.
CREATE PROCEDURE `p17`()
Begin
Declare continue Handler
for SQLSTATE '23000' set @x=@x+1;
insert into bank values(1001,11000);
insert into bank values(1002,10000);
insert into bank values(1003,2000);
insert into bank values(1003,20000);
end |

mysql> truncate table bank//
Query OK, 0 rows affected (0.00 sec)

mysql> call p17()//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
+-------+---------+
3 rows in set (0.00 sec)

19.
CREATE PROCEDURE `p18`()
Begin
Declare EXIT Handler
for SQLSTATE '23000' set @x=@x+1;
insert into bank values(1001,10000);
insert into bank values(1006,10000);
insert into bank values(1007,10000);
end |

mysql> call p17()//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
+-------+---------+
3 rows in set (0.00 sec)

mysql> call p18()//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bank//
+-------+---------+
| accno | balance |
+-------+---------+
| 1001 | 11000 |
| 1002 | 10000 |
| 1003 | 2000 |
+-------+---------+
3 rows in set (0.00 sec)



FUNCTIONS


CREATE FUNCTION `f1`(n Int) RETURNS int(11)
DETERMINISTIC
Begin Declare bal Int; select balance into bal from bank where accno=n; return b
al; end |

mysql> select f1(1003)//
+----------+
| f1(1003) |
+----------+
| 2000 |
+----------+
1 row in set (0.01 sec)


20.
create procedure p19 (IN s Int)
-> begin
-> if f1(s)>500 then
-> select ' do ur withdraw';
-> else
-> select 'no ur transaction failed';
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)

mysql> call p19(1003)//
+-----------------+
| do ur withdraw |
+-----------------+
| do ur withdraw |
+-----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into bank values(1004,200)//
Query OK, 1 row affected (0.00 sec)

mysql> call p19(1004)//
+--------------------------+
| no ur transaction failed |
+--------------------------+
| no ur transaction failed |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


TCL
TRASACTION WITH OUT COMMIT:
mysql> select * from trans;
+-----+---------+
| num | balance |
+-----+---------+
| 1 | 900 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 400 |
| 5 | 550 |
| 6 | 4500 |
+-----+---------+
6 rows in set (0.00 sec)

mysql> START TRANSACTION;
update trans set balance=balance-500 where num=2;
update trans set balance=balance+500 where num=4;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from trans;;
+-----+---------+
| num | balance |
+-----+---------+
| 1 | 900 |
| 2 | 1500 |
| 3 | 3000 |
| 4 | 900 |
| 5 | 550 |
| 6 | 4500 |
+-----+---------+
6 rows in set (0.00 sec)
ERROR:
No query specified

ROLLBACK WITH OUT COMMIT:
mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from trans;;
+-----+---------+
| num | balance |
+-----+---------+
| 1 | 900 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 400 |
| 5 | 550 |
| 6 | 4500 |
+-----+---------+
6 rows in set (0.00 sec)

ERROR:
No query specified

TRANSACTION WITH COMMIT:
mysql> START TRANSACTION;
update trans set balance=balance-50 where num=1;
COMMIT;
update trans set balance=balance+50 where num=4
COMMIT;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> select *from trans;
+-----+---------+
| num | balance |
+-----+---------+
| 1 | 850 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 450 |
| 5 | 550 |
| 6 | 4500 |
+-----+---------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)


ROLLBACK WITH COMMIT:
mysql> select * from trans;
+-----+---------+
| num | balance |
+-----+---------+
| 1 | 850 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 450 |
| 5 | 550 |
| 6 | 4500 |
+-----+---------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from trans;
+-----+---------+
| num | balance |
+-----+---------+
| 1 | 850 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 450 |
| 5 | 550 |
| 6 | 4500 |
+-----+---------+
6 rows in set (0.00 sec)

TRIGGER
Create trigger ins after insert on emp for each row
Begin
@sum=@sum+new.esal;
End;

Set @sum=0;

Insert into emp values(1,1000);
Insert into emp values(2,2000);

Select @sum;
--------
@sum
----------
3000

Create trigger upt before insert on emp for each row
Begin
@sum1=new.sal-old.sal;
End;

Set @sum=0;

Update emp set sal=5000 where eid=1;

Select @sum1;

@sum
40000


CURSOR PROGRAM1

mysql> select * from emp2;
+------+--------+------+-------+
| sno | ename | acno | city |
+------+--------+------+-------+
| 1 | sree | 100 | hyd |
| 2 | hari | 200 | chenn |
| 3 | raji | 4000 | blore |
| 4 | ramana | 9000 | mum |
+------+--------+------+-------+
4 rows in set (0.02 sec)

mysql> create procedure dcur()
begin declare x int default 0;
declare sno,acno int;
declare ename,city varchar(10);
-> declare cs cursor for select * from emp2;
-> declare continue handler for SQLSTATE '02000' set x=1;
-> declare continue handler for SQLSTATE '23000' set x=1;
-> open cs;
-> a:loop
-> if x=1 then
-> leave a;
-> end if;
-> if not x=1 then
-> fetch cs into sno,ename,acno,city;
-> insert into emp5 values(sno,ename,acno,city);
-> end if;
-> end loop;
-> close cs;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> call dcur();
-> //
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp5;
-> //
+------+--------+---------+--------+
| name | code | company | salary |
+------+--------+---------+--------+
| 1 | sree | 100 | hyd |
| 2 | hari | 200 | chenn |
| 3 | raji | 4000 | blore |
| 4 | ramana | 9000 | mum |
| 4 | ramana | 9000 | mum |
+------+--------+---------+--------+
5 rows in set (0.00 sec)
CURSOR PROGRAM2:


mysql> select * from emp5;
+------+-------+------+
| eno | ename | esal |
+------+-------+------+
| 1 | SCOTT | 8000 |
| 2 | SMITH | 9000 |
| 3 | SMITH | 6000 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure cn(in x int,out y varchar(10))
-> begin
-> declare 1empname varchar(20);
-> declare cz cursor for select ename from ename where eno=x;
-> open cz;
-> fetch cz into 1empname;
-> close cz;
-> set y=1empname;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)

mysqlmysql> call cn(@a,@b);
-> //
ERROR 1146 (42S02): Table 'ranjitha.ename' doesn't exist
mysql> call cn1(@a,@b);
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> select @b;
-> //
+-------+
| @b |
+-------+
| SCOTT |
_ _ _ _ _ _
1 row in set (0.00 sec)
Posted by at
Labels:

1 comment:

  1. Madarapu Naresh KumarSeptember 21, 2012 at 10:23 PM

    Hi mam,

    you have stated
    "DELETE command deletes only the tuples where as DROP command deletes whole schema".

    Drop command will delete entire table structure, where as schema is something different i guess. Please do clarify my doubt..

    ReplyDelete

Subscribe to: Post Comments (Atom)
Refer this site 2 ur frndz