How to find out Max Salary from each department

You can find maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate maximum salary in each group or each department.

SQL Query:

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

This questions become more interesting if Interviewer will ask you to print department name instead of department id, in that case you need to join Employee table with Department using foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well.  Here is the query

What is RDBMS ?

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database

MYSQL Commands

# [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server.
mysql> create database [databasename];
List all databases on the sql server.
mysql> show databases;
Switch to a database.

Sql Queries

List the emps who are either ‘CLERK’ or ‘ANALYST’ in the Desc order.
A) select * from emp where job = ‘CLERK’ or job = ‘ANALYST’ order by job desc;
List the emps who joined on 1-MAY-81,3-DEC-81,17-DEC-81,19-JAN-80 in asc order of seniority.
A) select * from emp where hiredate in (’01-may-81’,’03-dec-81’,’17-dec-81’,’19-jan-80’) order by hiredate asc;

List the emps along with their Exp and Daily Sal is more than Rs.100.

A) select * from emp where (sal/30) >100;

List the emps in the asc order of Designations of those joined after the second half of 1981.

Answer:

select * from emp where hiredate > (’30-jun-81’) and to_char(hiredate,’YYYY’) = 1981 order by job asc;

Display all the details of the emps whose Comm. Is more than their Sal.

Answer:

List the Empno, Ename, Sal, Exp of all emps working for Mgr 7369.

Answer:

Display the Empno, Ename, job, Hiredate, Exp of all Mgrs

Answer:

List the Empno, Ename, Sal, Daily sal of all emps in the asc order of Annsal.

Answer:

List the emps who joined before 1981.

Answer:

select * from emp where hiredate < (’01-jan-81’);

Display all the details of all Mgrs

Answer:

Display all the unique job groups in the descending order?

Answer:

List the details of the emps in asc order of the Dptnos and desc of Jobs?

Answer:

List the emps in the asc order of their Salaries?

Answer:

Display unique Jobs from EMP table?

Answer:

Display all the information of the EMP table?

Query for selecting all data from table write below select sql query

Using REPLACE in an UPDATE statement

This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.

Replace searches for certain characters in a string and replaces them with other characters. So this statement:

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'Rolls')

will return

SQLTeam.com Rolls!

REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string. You can also do replacements of different sizes. For example,

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'is cool')

gives us

SQLTeam.com is cool!

I replaced a five character string with a seven character string with no problem. If the string isn't found, no changes will be made.

SELECT Replace('SQLTeam.com Rocks!', 'Yak', 'Tibetan bison')

returns exactly what we started with which is

SQLTeam.com Rocks!

If it doesn't find anything to change it just returns the string unchanged. You can use REPLACE in an UPDATE statement. Using the pubs database we could write:

Update dbo.authors
Set    city = replace(city, 'Salt', 'Olympic');

There were two authors that had "Salt Lake City" in the CITY field. Now that field holds "Olympic Lake City" for those two authors. The CITY field is unchanged for all the other authors.

A more common approach is to use this in conjuntion with a WHERE clause like this:

UPDATE dbo.authors
SET    city = replace(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';

This only affects the rows that start with 'Salt'.