SOME MORE SAMPLE QUERIES (conti...)
Q4.
Make a database and perform queries below:
1. Get the employee no and name
who works in deptt 10.
2. Display the employee name of
those employees whose salary >2000.
3. Display name and jobs of
salesperson and clerks.
4. Display all the details of
employees whose salary is between 2000 and 3000.
5. Display all the details of
employees whose deptt no is10, 20 and 30.
6. Display names of those
employees whose commission is NULL.
7. Display deptt no and salary
is ascending order of deptt no and within each deptt salary should be in
descending order.
8. Display names of employees
having two “a” or “A” characters in names.
9. Display the names of
employees whose second character is “b” or “B”.
10.Display
the name of employees whose first or last character is “a” or “A”.
11. Display
those jobs which are present only in deptt no 20 not in any other deptt no.
12.Display
names of jobs which are common in all deptt or in other words jobs which are
present in all the deptt.
13.Display
names of jobs present in deptt no 10,20 or 30.
SOLUTIONS:
mysql> use
mywork2;
Database changed
mysql> show
tables;
+-------------------+
| Tables_in_mywork2 |
+-------------------+
| dept |
| employee2 |
+-------------------+
2 rows in set (0.06
sec)
mysql> describe
employee2;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno | int(11)
| NO | |
| |
| ename | varchar(20) | NO |
| | |
| job | varchar(20) | YES | |
NULL | |
| mgr | int(11) | NO
| | |
|
| date | date
| NO | |
| |
| sal | int(11) | NO
| | |
|
| comm | int(11)
| YES | | NULL
| |
| deptno |
int(11) | NO | PRI | |
|
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.08
sec)
mysql> drop table
dept;
Query OK, 0 rows
affected (0.13 sec)
mysql> drop table
smployee2;
ERROR 1051 (42S02):
Unknown table 'smployee2'
mysql> drop table
employee2;
Query OK, 0 rows
affected (0.08 sec)
mysql> source
assignemp02.txt;
Query OK, 0 rows
affected (0.44 sec)
mysql> source
assigndept02.txt;
Query OK, 0 rows
affected (0.11 sec)
mysql> describe
employee2;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno | int(11)
| NO | |
| |
| ename | varchar(20) | NO |
| | |
| job | varchar(20) | YES | |
NULL | |
| mgr | int(11) | NO
| | |
|
| date | date
| NO | |
| |
| sal | int(11) | NO
| | |
|
| comm | int(11)
| YES | | NULL
| |
| deptno |
int(11) | NO |
| | |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.00
sec)
mysql> describe
dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra
|
+--------+-------------+------+-----+---------+-------+
| deptno |
int(11) | NO |
| | |
| dname | varchar(20) | NO |
| | |
| city | varchar(20) | NO |
| | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01
sec)
mysql> drop table
employee2;
Query OK, 0 rows
affected (0.41 sec)
mysql> source
assignemp02.txt;
Query OK, 0 rows
affected (0.09 sec)
mysql> describe
employee2;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno | int(11)
| NO | |
| |
| ename | varchar(20) | NO | |
| |
| job | varchar(20) | YES | |
NULL | |
| mgr | int(11) | YES
| | NULL |
|
| date | date
| NO | |
| |
| sal | int(11) | NO
| | |
|
| comm | int(11)
| YES | | NULL
| |
| deptno |
int(11) | NO |
| | |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.00
sec)
mysql> source
assignresult02.txt;
Query OK, 14 rows
affected (0.06 sec)
Records: 14 Duplicates: 0
Warnings: 0
mysql> select *
from employee2;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | date | sal
| comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith
| Clerk | 7902 | 1980-12-17
| 800 | NULL | 20 |
| 7499 | Allen
| Salesman | 7698 | 1981-02-20 |
1600 | 300 | 30 |
| 7521 | Ward
| Salesman | 7698 | 1981-02-22 |
1250 | 500 | 30 |
| 7566 | Jones
| Manager | 7839 | 1981-04-02 |
2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | Blake
| Manager | 7839 | 1981-05-01 |
2850 | NULL | 30 |
| 7782 | Clark
| Manager | 7839 | 1981-06-09 |
2450 | NULL | 10 |
| 7788 | Scott
| Analyst | 7566 | 1982-12-09 |
3000 | NULL | 20 |
| 7839 | King
| President | NULL | 1981-11-17 | 5000 | NULL | 30 |
| 7844 | Turner | Salesman | 7698 | 1981-09-08 | 1500 | 0 |
30 |
| 7876 | Adams
| Clerk | 7788 | 1983-01-12 |
1100 | NULL | 20 |
| 7900 | James
| Clerk | 7698 | 1981-12-03
| 950 | NULL | 30 |
| 7902 | Ford
| Analyst | 7566 | 1981-12-14 |
3000 | NULL | 20 |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00
sec)
mysql> select
empno and ename from employee2 where deptno=10;
+-----------------+
| empno and ename |
+-----------------+
| 0 |
| 0 |
+-----------------+
2 rows in set (0.05
sec)
mysql> select
empno,ename from employee2 where deptno=10;
+-------+--------+
| empno | ename |
+-------+--------+
| 7782 | Clark
|
| 7934 | Miller |
+-------+--------+
2 rows in set (0.00
sec)
mysql> select
ename from employee2 where job="clerk" and sal>2000;
Empty set (0.00 sec)
mysql> select
ename,job from employee2 where job="salesperson" or
job="clerk";
+--------+-------+
| ename | job
|
+--------+-------+
| Smith | Clerk |
| Adams | Clerk |
| James | Clerk |
| Miller | Clerk |
+--------+-------+
4 rows in set (0.00
sec)
mysql> select
ename,job from employee2 where job="salesperson" and
job="clerk";
Empty set (0.00 sec)
mysql> select
ename,job from employee2 where job="salesperson" and
"clerk";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename,job from employee2 where job="salesman" and "clerk";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename,job from employee2 where job="salesman" and
job="clerk";
Empty set (0.00 sec)
mysql> select
ename,job from employee2 where job="salesman" and "clerk";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename,job from employee2 where job="salesman" or
job="clerk";
+--------+----------+
| ename | job
|
+--------+----------+
| Smith | Clerk
|
| Allen | Salesman |
| Ward | Salesman |
| Martin | Salesman |
| Turner | Salesman |
| Adams | Clerk
|
| James | Clerk
|
| Miller | Clerk |
+--------+----------+
8 rows in set (0.02
sec)
mysql> select
ename from employee2 where sal>2000 and sal<3000;
+-------+
| ename |
+-------+
| Jones |
| Blake |
| Clark |
+-------+
3 rows in set (0.00
sec)
mysql> select *
from employee2 where sal>2000 and sal<3000;
+-------+-------+---------+------+------------+------+------+--------+
| empno | ename |
job | mgr | date
| sal | comm | deptno |
+-------+-------+---------+------+------------+------+------+--------+
| 7566 | Jones | Manager | 7839 | 1981-04-02 |
2975 | NULL | 20 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 |
2850 | NULL | 30 |
| 7782 | Clark | Manager | 7839 | 1981-06-09 |
2450 | NULL | 10 |
+-------+-------+---------+------+------------+------+------+--------+
3 rows in set (0.00
sec)
mysql> select *
from employee2 where deptno=10 or deptno=20 or deptno=30;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | date | sal
| comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith
| Clerk | 7902 | 1980-12-17
| 800 | NULL | 20 |
| 7499 | Allen
| Salesman | 7698 | 1981-02-20 |
1600 | 300 | 30 |
| 7521 | Ward
| Salesman | 7698 | 1981-02-22 |
1250 | 500 | 30 |
| 7566 | Jones
| Manager | 7839 | 1981-04-02 |
2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | Blake
| Manager | 7839 | 1981-05-01 |
2850 | NULL | 30 |
| 7782 | Clark
| Manager | 7839 | 1981-06-09 |
2450 | NULL | 10 |
| 7788 | Scott
| Analyst | 7566 | 1982-12-09 |
3000 | NULL | 20 |
| 7839 | King
| President | NULL | 1981-11-17 | 5000 | NULL | 30 |
| 7844 | Turner | Salesman | 7698 | 1981-09-08 | 1500 | 0 |
30 |
| 7876 | Adams
| Clerk | 7788 | 1983-01-12 |
1100 | NULL | 20 |
| 7900 | James
| Clerk | 7698 | 1981-12-03
| 950 | NULL | 30 |
| 7902 | Ford
| Analyst | 7566 | 1981-12-14 |
3000 | NULL | 20 |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00
sec)
mysql> select
ename from employee2 where comm="NULL";
+--------+
| ename |
+--------+
| Turner |
+--------+
1 row in set, 4
warnings (0.00 sec)
mysql> select
deptno,sal from employee2 order by deptno asc;
+--------+------+
| deptno | sal |
+--------+------+
| 10 | 1300 |
| 10 | 2450 |
| 20 | 3000 |
| 20 | 1100 |
| 20 | 3000 |
| 20 | 2975 |
| 20 |
800 |
| 30 | 2850 |
| 30 | 1250 |
| 30 | 5000 |
| 30 | 1500 |
| 30 | 1250 |
| 30 |
950 |
| 30 | 1600 |
+--------+------+
14 rows in set (0.39
sec)
mysql> select
deptno,sal from employee2 order by deptno asc , sal desc;
+--------+------+
| deptno | sal |
+--------+------+
| 10 | 2450 |
| 10 | 1300 |
| 20 | 3000 |
| 20 | 3000 |
| 20 | 2975 |
| 20 | 1100 |
| 20 |
800 |
| 30 | 5000 |
| 30 | 2850 |
| 30 | 1600 |
| 30 | 1500 |
| 30 | 1250 |
| 30 | 1250 |
| 30 |
950 |
+--------+------+
14 rows in set (0.00
sec)
mysql> exit;
mysql> use
mywork2;
Database changed
mysql> show
databases;
+--------------------+
| Database |
+--------------------+
| information_schema
|
| mitali |
| mysql |
| mywork2 |
| sakshi |
| test |
+--------------------+
6 rows in set (0.09
sec)
mysql> show
tables;
+-------------------+
| Tables_in_mywork2 |
+-------------------+
| dept |
| employee2 |
+-------------------+
2 rows in set (0.00
sec)
mysql> describe
employee2;
+--------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno | int(11)
| NO | |
| |
| ename | varchar(20) | NO |
| | |
| job | varchar(20) | YES | |
NULL | |
| mgr | int(11) | YES
| | NULL |
|
| date | date
| NO | |
| |
| sal | int(11) | NO
| | |
|
| comm | int(11)
| YES | | NULL
| |
| deptno |
int(11) | NO |
| | |
+--------+-------------+------+-----+---------+-------+
8 rows in set (0.17
sec)
mysql> select job
from employee2 where deptno=20 and deptno=10 and deptno=30;
Empty set (0.05 sec)
mysql> select job
from employee2 where deptno=20 or deptno=10 or deptno=30;
+-----------+
| job |
+-----------+
| Clerk |
| Salesman |
| Salesman |
| Manager |
| Salesman |
| Manager |
| Manager |
| Analyst |
| President |
| Salesman |
| Clerk |
| Clerk |
| Analyst |
| Clerk |
+-----------+
14 rows in set (0.05
sec)
mysql> select job
from employee2 where deptno=20 and job not in(select job from employee2 where
deptno!=20);
+---------+
| job |
+---------+
| Analyst |
| Analyst |
+---------+
2 rows in set (0.00
sec)
mysql> select
ename from employee2 where ename like "a";
Empty set (0.00 sec)
mysql> select *
from employee2;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | date | sal
| comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith
| Clerk | 7902 | 1980-12-17
| 800 | NULL | 20 |
| 7499 | Allen
| Salesman | 7698 | 1981-02-20 |
1600 | 300 | 30 |
| 7521 | Ward
| Salesman | 7698 | 1981-02-22 |
1250 | 500 | 30 |
| 7566 | Jones
| Manager | 7839 | 1981-04-02 |
2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | Blake
| Manager | 7839 | 1981-05-01 |
2850 | NULL | 30 |
| 7782 | Clark
| Manager | 7839 | 1981-06-09 |
2450 | NULL | 10 |
| 7788 | Scott
| Analyst | 7566 | 1982-12-09 |
3000 | NULL | 20 |
| 7839 | King
| President | NULL | 1981-11-17 | 5000 | NULL | 30 |
| 7844 | Turner | Salesman | 7698 | 1981-09-08 | 1500 | 0 |
30 |
| 7876 | Adams
| Clerk | 7788 | 1983-01-12 |
1100 | NULL | 20 |
| 7900 | James
| Clerk | 7698 | 1981-12-03
| 950 | NULL | 30 |
| 7902 | Ford
| Analyst | 7566 | 1981-12-14 |
3000 | NULL | 20 |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00
sec)
mysql> select
ename from employee2 where ename like "a" or "A";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename from employee2 where ename like"%a";
Empty set (0.00 sec)
mysql> select
ename from employee2 where ename like"%a%" or "%A%";
+--------+
| ename |
+--------+
| Allen |
| Ward |
| Martin |
| Blake |
| Clark |
| Adams |
| James |
+--------+
7 rows in set, 1
warning (0.00 sec)
mysql> select
ename from employee2 where ename like"%a%" and "%a%"or
"%A%" and "%A%";
Empty set, 2 warnings
(0.00 sec)
mysql> select
ename from employee2 where ename like"a%a" or "%A%A";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename from employee2 where ename like"a%a" or "A%A";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename from employee2 where ename like"a%a";
Empty set (0.00 sec)
mysql> select
ename from employee2 where ename like"a%a";
Empty set (0.00 sec)
mysql> select
ename from employee2 where ename like "a%a";
Empty set (0.00 sec)
mysql> select
ename from employee2 where ename like "%a%a%";
+-------+
| ename |
+-------+
| Adams |
+-------+
1 row in set (0.00
sec)
mysql> select
ename from employee2 where ename like "a%a";
Empty set (0.00 sec)
mysql> select
ename from employee2 where ename like "%a" or "a%";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename from employee2 where ename like "%a" or "a%";
Empty set, 1 warning
(0.00 sec)
mysql> select
ename from employee2 where ename like "_b%";
Empty set (0.00 sec)
mysql> insert into
employee2 values(7856,"Ahana","clerk",7839,"1980-03-20",1200,NULL,30),(7056,"Ebyana","clerk",7839,"1981-03-22",1000,NULL,30);
Query OK, 2 rows
affected (0.09 sec)
Records: 2 Duplicates: 0
Warnings: 0
mysql> select *
from employee2;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | date | sal
| comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith
| Clerk | 7902 | 1980-12-17
| 800 | NULL | 20 |
| 7499 | Allen
| Salesman | 7698 | 1981-02-20 |
1600 | 300 | 30 |
| 7521 | Ward
| Salesman | 7698 | 1981-02-22 |
1250 | 500 | 30 |
| 7566 | Jones
| Manager | 7839 | 1981-04-02 |
2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | Blake
| Manager | 7839 | 1981-05-01 |
2850 | NULL | 30 |
| 7782 | Clark
| Manager | 7839 | 1981-06-09 |
2450 | NULL | 10 |
| 7788 | Scott
| Analyst | 7566 | 1982-12-09 |
3000 | NULL | 20 |
| 7839 | King
| President | NULL | 1981-11-17 | 5000 | NULL | 30 |
| 7844 | Turner | Salesman | 7698 | 1981-09-08 | 1500 | 0 |
30 |
| 7876 | Adams
| Clerk | 7788 | 1983-01-12 |
1100 | NULL | 20 |
| 7900 | James
| Clerk | 7698 | 1981-12-03
| 950 | NULL | 30 |
| 7902 | Ford
| Analyst | 7566 | 1981-12-14 |
3000 | NULL | 20 |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 |
| 7856 | Ahana
| clerk | 7839 | 1980-03-20 |
1200 | NULL | 30 |
| 7056 | Ebyana | clerk | 7839 | 1981-03-22 | 1000 | NULL | 30 |
+-------+--------+-----------+------+------------+------+------+--------+
16 rows in set (0.00
sec)
mysql> select
ename from employee2 where ename like "%a" or "a%";
+--------+
| ename |
+--------+
| Ahana |
| Ebyana |
+--------+
2 rows in set, 1
warning (0.00 sec)
mysql> select
ename from employee2 where ename like "_b%";
+--------+
| ename |
+--------+
| Ebyana |
+--------+
1 row in set (0.00
sec)
mysql> select ename
from employee2 where ename like "a%a";
+-------+
| ename |
+-------+
| Ahana |
+-------+
1 row in set (0.00
sec)
mysql> select
ename from employee2 where ename like "%a%a%";
+--------+
| ename |
+--------+
| Adams |
| Ahana |
| Ebyana |
+--------+
3 rows in set (0.00
sec)
mysql> exit;
Q5.
Use all In-built functions applicable to mysql.
1. Char(n)
2. Concat(char1.char2)
3. Instr(string,char)
4. Length(n)
5. Lpad(char1,string,char2)
6. Rpad(char1,string,char2)
7. Replace(string,search_string,replace_string)
8. Substr(string_position,substring length)
9. Lower(string)
10.
Upper(string)
11.
Abs(n)
12.
Ceil(n)
13.
Cos(n)
14.
Exp(n)
15.
Floor(n)
16.
Mod(m,n)
17.
Power(m,n)
18.
Round(x,[y])
19.
Sign(n)
20.
Sqrt(n)
21.
Trunc(n,m)
22.
Sysdate
SOLUTIONS:
mysql> show
databases;
+--------------------+
| Database |
+--------------------+
| information_schema
|
| mitali |
| mysql |
| mywork2 |
| sakshi |
| test |
| trigr |
+--------------------+
7 rows in set (0.08
sec)
mysql> use
mywork2;
Database changed
mysql> select
char(4);
+---------+
| char(4) |
+---------+
|
|
+---------+
1 row in set (0.00
sec)
mysql> select
char(65);
+----------+
| char(65) |
+----------+
| A |
+----------+
1 row in set (0.00
sec)
mysql> select concat("mitali","rohini");
+---------------------------+
|
concat("mitali","rohini") |
+---------------------------+
| mitalirohini |
+---------------------------+
1 row in set (0.00
sec)
mysql> select
instr("my name is","M");
+-------------------------+
| instr("my name
is","M") |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.06
sec)
mysql> select
instr("my name is","n");
+-------------------------+
| instr("my name
is","n") |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00
sec)
mysql> select
length("mitali");
+------------------+
|
length("mitali") |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
mysql> select
lpad("*",8,"0");
+-----------------+
|
lpad("*",8,"0") |
+-----------------+
| 0000000* |
+-----------------+
1 row in set (0.38
sec)
mysql> select
lpad("mitali",8,"0");
+----------------------+
|
lpad("mitali",8,"0") |
+----------------------+
| 00mitali |
+----------------------+
1 row in set (0.00
sec)
mysql> select
rpad("mitali",8,"0");
+----------------------+
|
rpad("mitali",8,"0") |
+----------------------+
| mitali00 |
+----------------------+
1 row in set (0.36
sec)
mysql> select
replace("mitali","m","n");
+---------------------------+
|
replace("mitali","m","n") |
+---------------------------+
| nitali |
+---------------------------+
1 row in set (0.00
sec)
mysql> select
substr("mitalie",3,3);
+-----------------------+
|
substr("mitalie",3,3) |
+-----------------------+
| tal |
+-----------------------+
1 row in set (0.03
sec)
mysql>
mysql> select
lower("mitali");
+-----------------+
|
lower("mitali") |
+-----------------+
| mitali |
+-----------------+
1 row in set (0.00
sec)
mysql> select
upper("mitali");
+-----------------+
|
upper("mitali") |
+-----------------+
| MITALI |
+-----------------+
1 row in set (0.00
sec)
mysql> select
abs("mitali");
+---------------+
|
abs("mitali") |
+---------------+
| 0 |
+---------------+
1 row in set, 1
warning (0.00 sec)
mysql> select
abs("123456");
+---------------+
|
abs("123456") |
+---------------+
| 123456 |
+---------------+
1 row in set (0.00
sec)
mysql> select
abs(789.098);
+--------------+
| abs(789.098) |
+--------------+
| 789.098 |
+--------------+
1 row in set (0.05
sec)
mysql> select
ceil("mitali");
+----------------+
|
ceil("mitali") |
+----------------+
| 0 |
+----------------+
1 row in set, 1
warning (0.00 sec)
mysql>
mysql> select
ceil("123456");
+----------------+
|
ceil("123456") |
+----------------+
| 123456 |
+----------------+
1 row in set (0.00
sec)
mysql> select ceil("123.456");
+-----------------+
|
ceil("123.456") |
+-----------------+
| 124 |
+-----------------+
1 row in set (0.00
sec)
mysql> select
cos(90);
+-------------------+
| cos(90) |
+-------------------+
| -0.44807361612917 |
+-------------------+
1 row in set (0.00
sec)
mysql> select
cos(0);
+--------+
| cos(0) |
+--------+
| 1 |
+--------+
1 row in set (0.00
sec)
mysql> select
cos(45);
+------------------+
| cos(45) |
+------------------+
| 0.52532198881773 |
+------------------+
1 row in set (0.00
sec)
mysql> select
exp(6);
+-----------------+
| exp(6) |
+-----------------+
| 403.42879349274 |
+-----------------+
1 row in set (0.00
sec)
mysql> select
floor(56.8907);
+----------------+
| floor(56.8907) |
+----------------+
| 56 |
+----------------+
1 row in set (0.00
sec)
mysql> select
mod(6,3);
+----------+
| mod(6,3) |
+----------+
| 0 |
+----------+
1 row in set (0.00
sec)
mysql> select
mod(6,4);
+----------+
| mod(6,4) |
+----------+
| 2 |
+----------+
1 row in set (0.00
sec)
mysql> select
power(2,4);
+------------+
| power(2,4) |
+------------+
| 16 |
+------------+
1 row in set (0.05
sec)
mysql> select
round(3,12.123456);
+--------------------+
| round(3,12.123456)
|
+--------------------+
| 3.000000000000 |
+--------------------+
1 row in set (0.00
sec)
mysql> select
round(3, (12.123456));
+-----------------------+
| round(3,
(12.123456)) |
+-----------------------+
| 3.000000000000 |
+-----------------------+
1 row in set (0.00
sec)
mysql> select
round(12.123456,4);
+--------------------+
| round(12.123456,4)
|
+--------------------+
| 12.1235 |
+--------------------+
1 row in set (0.00
sec)
mysql> select
sign(-56);
+-----------+
| sign(-56) |
+-----------+
| -1 |
+-----------+
1 row in set (0.05
sec)
mysql> select
sign(12);
+----------+
| sign(12) |
+----------+
| 1 |
+----------+
1 row in set (0.00
sec)
mysql> select
sqrt(64);
+----------+
| sqrt(64) |
+----------+
| 8 |
+----------+
1 row in set (0.00
sec)
mysql> select
sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2011-04-19 11:26:43
|
+---------------------+
1 row in set (0.05
sec)
mysql> exit;
Q6.
Give examples of Procedure and cursor
SOLUTION:
Enter password:
******
Welcome to the MySQL
monitor. Commands end with ; or \g.
Your MySQL connection
id is 1
Server version:
6.0.0-alpha-community-nt-debug MySQL Community Server (GPL)
Type 'help;' or '\h'
for help. Type '\c' to clear the buffer.
mysql> show
databases;
+--------------------+
| Database |
+--------------------+
| information_schema
|
| mitali |
| mysql |
| mywork2 |
| sakshi |
| test |
| trigr |
+--------------------+
7 rows in set (0.13
sec)
mysql> tee log
Logging to file 'log'
mysql> use mitali
Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_mitali |
+------------------+
| 1st_div |
| 1stdivision |
| 2nddivision |
| company |
| employee |
| managers |
| p |
| result |
| results |
| s |
| sp |
| student |
| students |
| works |
+------------------+
14 rows in set (0.00
sec)
mysql> select *
from 1st_div;
+--------+--------+------+
| rollno | name | sub1 |
+--------+--------+------+
| 7006 | Mitali | 65 |
| 7001 | aman
| 85 |
+--------+--------+------+
2 rows in set (0.11
sec)
mysql> delimiter /
mysql> create
procedure p()
-> begin
-> select * from 1st_div;
-> end;
-> /
Query OK, 0 rows
affected (0.38 sec)
mysql> select *
from 1st_div;
-> /
+--------+--------+------+
| rollno | name | sub1 |
+--------+--------+------+
| 7006 | Mitali | 65 |
| 7001 | aman
| 85 |
+--------+--------+------+
2 rows in set (0.00
sec)
mysql> call p();
-> /
+--------+--------+------+
| rollno | name | sub1 |
+--------+--------+------+
| 7006 | Mitali | 65 |
| 7001 | aman
| 85 |
+--------+--------+------+
2 rows in set (0.06
sec)
Query OK, 0 rows
affected (0.08 sec)
mysql> create
procedure c()
-> begin
-> declare a,sub1 int;
-> declare b varchar(20);
-> declare cur1 cursor for select * from
1st_div;
-> open cur1;
-> loop
-> fetch cur1 into a,sub1,b;
-> end loop;
-> end;
-> /
Query OK, 0 rows
affected (0.38 sec)
mysql>exit;
Comments
Post a Comment