14. SQL Query Based on One Table.txt

Q. Create table employee and insert following records
    
    EMPLOYEE TABLE
    +-------+--------+-----------+------+---------+------------+------+--------+--------+
    | EmpNo | EmpName| Job       | Mgr  | City    | Hiredate   | Sal  | Comm   | DeptNo |
    +-------+--------+-----------+------+---------+------------+------+--------+--------+
    | 7839  | KING   | PRESIDENT | NULL | DELHI   | 1981-11-17 | 5000 | NULL   | 10     |
    | 7698  | DEEPAK | MANAGER   | 7839 | KOLKATA | 1999-03-01 | 4500 | NULL   | 30     |
    | 7782  | AKASH  | MANAGER   | 7839 | DELHI   | 2011-03-01 | 4000 | NULL   | 10     |
    | 7566  | JONES  | MANAGER   | 7839 | MUMBAI  | 1981-04-02 | 3500 | NULL   | 20     |
    | 7654  | ALLEN  | SALESMAN  | 7698 | RANCHI  | 1997-01-08 | 2480 | 1500   | 30     |
    | 7499  | TURNER | SALESMAN  | 7698 | PATNA   | 1981-02-20 | 1600 | 500    | 30     |
    | 7844  | JAMES  | SALESMAN  | 7698 | MUMBAI  | 1993-09-08 | 1500 | 0      | 30     |
    | 7900  | WARD   | CLERK     | 7698 | CHENNAI | 1988-12-03 | 950  | NULL   | 30     |
    | 7521  | FORD   | SALESMAN  | 7698 | DELHI   | 1989-02-22 | 1250 | 400    | 30     |
    | 7902  | DONALD | ANALYST   | 7566 | DELHI   | 1987-12-03 | 3000 | NULL   | NULL   |
    | 7369  | SCOTT  | CLERK     | 7902 | KOLKATA | 1996-11-17 | 800  | NULL   | NULL   |
    | 7788  | SMITH  | ANALYST   | 7566 | NOIDA   | 1986-10-09 | 3000 | NULL   | 20     |
    | 7876  | ADAMS  | CLERK     | 7788 | KANPUR  | 1998-01-12 | 1100 | NULL   | 20     |
    | 7934  | EVE    | CLERK     | 7782 | MUMBAI  | 1994-01-23 | 1300 | NULL   | NULL   |
    +-------+--------+-----------+------+---------+------------+------+--------+--------+

A.
CREATE TABLE Employee (
    EmpNo INT NOT NULL PRIMARY KEY,
    EmpName CHAR(20),
    Job CHAR(10),
    Mgr INT,
    City VARCHAR(255), -- Assuming VARCHAR with a maximum length
    Hiredate DATE,
    Sal INT,
    Comm NUMERIC,
    DeptNo INT
);

INSERT INTO Employee (EmpNo, EmpName, Job, Mgr, City, Hiredate, Sal, Comm, DeptNo)
VALUES(7839, 'KING', 'PRESIDENT', NULL, 'DELHI', '1981-11-17', 5000, NULL, 10),
(7698, 'DEEPAK', 'MANAGER', 7839, 'KOLKATA', '1999-03-01', 4500, NULL, 30);

INSERT INTO Employee VALUES(7782, 'AKASH', 'MANAGER', 7839, 'DELHI', '2011-03-01', 4000, NULL, 10);

------------------------------------------------------------------------------

Based on these tables write SQL statements for the following queries:

Q 01. Display the list of EmpNo and EmpName.
A 01. SELECT EmpNo, EmpName FROM Employee;

Q 02. Display the details of table Employee.
A 02. DESCRIBE Employee;

Q 03. List the City of Employee. (Without duplicates).
A 03. SELECT DISTINCT City FROM Employee;

Q 04. Display the details of the employee whose salary is greater than 4000.
A 04. SELECT * FROM Employee WHERE Sal > 4000;

Q 05. Display the details of the employee whose commission is greater than 400.
A 05. SELECT * FROM Employee WHERE Comm > 400;

Q 06. List the EmpNo and their manager whose dept no is 20.
A 06. SELECT EmpNo, Mgr FROM Employee WHERE DeptNo = 20;

Q 07. List the EmpNo and their manager whose dept no is 20 or 30.
A 07. SELECT EmpNo, Mgr FROM Employee WHERE DeptNo IN (20, 30);

Q 08. Display the EmpNo and their annual salary of all employees.
A 08. SELECT EmpNo, Sal * 12 AS AnnualSalary FROM Employee;

Q 09. Display the EmpNo who have not been allotted to any department.
A 09. SELECT EmpNo FROM Employee WHERE DeptNo IS NULL;

Q 10. List the employee name in alphabetical order whose salary is more than 3500.
A 10. SELECT EmpName FROM Employee WHERE Sal > 3500 ORDER BY EmpName;

Q 11. Display the EmpNo and Name of salesman and their total monthly salary (salary + commission) as TotalMonthlySalary.
A 11. SELECT EmpNo, EmpName, Sal + IFNULL(Comm, 0) AS TotalMonthlySalary FROM Employee WHERE Job = 'SALESMAN';

Q 12. List the name of the employee whose name starts with 'A'.
A 12. SELECT EmpName FROM Employee WHERE EmpName LIKE 'A%';

Q 13. List the name of the employee whose name does not start with 'A'.
A 13. SELECT EmpName FROM Employee WHERE EmpName NOT LIKE 'A%';

Q 14. List the name of the employee in reverse alphabetical order whose name contains exactly five characters.
A 14. SELECT EmpName FROM Employee WHERE LENGTH(EmpName) = 5 ORDER BY EmpName DESC;

Q 15. List the details of the employee who belongs to either dept no 10 or 20.
A 15. SELECT * FROM Employee WHERE DeptNo IN (10, 20);

Q 16. List the employee no who belongs to dept no 20 and salary is between 2000 and 3500.
A 16. SELECT EmpNo FROM Employee WHERE DeptNo = 20 AND Sal BETWEEN 2000 AND 3500;

Q 17. List the name of the employee whose name ends with 'D'.
A 17. SELECT EmpName FROM Employee WHERE EmpName LIKE '%D';

Q 18. Display employee no, name, and dept no as 40, who have not been allotted any department.
A 18. SELECT EmpNo,EmpName, IFNULL(DeptNo,40) as DeptNo FROM Employee WHERE DeptNo IS NULL;

Q 19. Display the employee no and name whose name contains 'R' as the third alphabet.
A 19. SELECT EmpNo, EmpName FROM Employee WHERE EmpName LIKE '__R%';

Q 20. List the details of the employee who is not getting a commission.
A 20. SELECT * FROM Employee WHERE Comm IS NULL OR Comm = 0;