Archive for the ‘MySQL’ Category

highest salary in every department

Posted: June 18, 2012 in MySQL

Using Group By:

create table named mydept

CREATE TABLE `mydept` (

`deptid` int(5) NOT NULL,

`deptname` varchar(15) DEFAULT NULL,

`salary` decimal(15,2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert the following records in mydept table:

1   aaa    5000.00
2  aaa   10000.00
3  bbb   6000.00
4  bbb   8000.00
5  ccc   5000.00

6 ccc    2000.00

1) select deptname,max(salary) from mydept group by deptname

Ans: deptname  salary

aaa             10000

bbb              8000

ccc                5000

Advertisements

second highest salary

Posted: June 18, 2012 in MySQL

we can get 2nd highest salary by inner query ,  not in , order by column desc limit 0,1

Create table named employee:

CREATE TABLE `employee` (

`eno` int(5) NOT NULL AUTO_INCREMENT,

`ename` varchar(50) DEFAULT NULL,

`sal` float DEFAULT NULL,

PRIMARY KEY (`eno`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Insert the following records:

1              gopal       16000

2              sanjana   14000

3              prem      10000

Queries:

1) select sal from employee where sal = (select max(sal) from employee where sal < (select max(sal) from employee))

Ans: 14000

2) select max(sal) from employee where sal not in (select max(sal) from employee)

Ans: 14000

3)select sal from employee order by sal desc limit 1,1 (1 means 2nd highest means position ,1 no of records)

Ans: 14000

Highest salary in table using above query:

1)select sal from employee order by sal desc limit 0,1

Ans: 16000

2)select sal from employee order by sal desc limit 2,1

Ans: 10000

3) select sal from employee order by sal desc limit 0,2

Ans: 16000

14000

 

MySQL – Funda’s

Posted: April 11, 2012 in MySQL

  1. Show databases

a)      It shows all the databases in the server.

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| cismysqldb         |

| mysql              |

| test               |

+——————–+

4 rows in set (0.02 sec)

2) Use Database

a)      We can use one of the database

mysql> use mysql;

Database changed

3) how to create user?

a)  create user sanjana identified by sanjana;

4) how to grant the permission to user?

a) grant dba to sanjana;

5) how to connect to the particular user?

a) 1.conn gopi/gopi@orcl;

2.conn sanjana/sanjana@orcl;

6) how to see the existing tables?

a) select * from tab;

7)      Select  host, user from user;

a)      We can get hosts and users granted permission by us.

 

mysql> select host, user from user;

+————–+——+

| host         | user |

+————–+——+

| 192.168.1.27 | root |

| 192.168.1.30 | root |

| 192.168.1.9  | root |

| localhost    | root |

+————–+——+

4         rows in set (0.01 sec)

 

8)      Granted Permission to a particular IP Address from Server?

a)      We can grant the permission to a particular IP Address using the below command.

 

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.1.29’ IDENTIFIED BY ‘PASSWORD’

WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)