practical_3.sql

SELECT Empno,FirstName,LastName, DeptId FROM Employee ORDER BY FirstName;
SELECT Empno,FirstName,LastName, DeptId FROM Employee ORDER BY LastName, FirstName;
SELECT Empno,FirstName,LastName, DeptId FROM Employee WHERE LastName LIKE '%H%';
SELECT Empno,FirstName,LastName, DeptId FROM Employee WHERE SUBSTR(LastName,3,1) = 'H';
SELECT DISTINCT City FROM Employee;
SELECT Empno,FirstName,LastName, DeptId FROM Employee WHERE Salary BETWEEN 30000 AND 45000;
SELECT Empno,FirstName,LastName, City FROM Employee WHERE City NOT IN ('Mumbai','Pune');
SELECT DeptId, SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary) FROM Employee GROUP BY DeptId;
SELECT Empno, LOWER(FirstName), UPPER(LastName), SUBSTR(LastName,3,4) FROM Employee;
SELECT Empno, CONCAT(FirstName, ' ', LastName) AS Name FROM Employee;

Practical 3

Title : Working with Queries & Functions

Using already created employee and department tables write the following queries

1. Display employee number, name of the employee, Department id and
   arrange it in ascending order on the first name of the employee.
2. Display employee number, name of the employee, Department id and
   arrange it ascending order on the Last Name and then on First name of the
   employee.
3. Display employee number, name of the employee, Department id of those
   employees whose last name contain ‘H’.
4. Display employee number, name of the employee, Department id of those
   employees whose 3rd character of the last name contain ‘H’.
5. Display the different cities from which employee belong.
6. Display employee number, name of the employee, Department id of those
   employees whose salary is between 30000 and 45000.
7. Display employee number, name of the employee, City of those
   employees who live in Mumbai or Pune.
8. Display employee number, name of the employee, City of those
   employees who does not live in Mumbai or Pune.
9. Display department id wise total salary, average salary, Maximum salary
   and minimum salary.
10. Display employee number, First name in small letters, Last name in
    capital letters and from last name 4 characters starting from 3rd character.
11. Display the employee number and join First name and Last name together
    with the heading Name.