Set-11

1. Retrieve all the names and their respective salaries:
SELECT Emp_Name, Salary FROM Employee;

2. Retrieve the names of employees whose salary is greater than 5000:
SELECT Emp_Name FROM Employee WHERE Salary > 5000;
3. Display the names and locations of all departments:
SELECT Dname, Location FROM Department;

4. Display the names of employees who work in the sales department:
SELECT e.Emp_Name 
FROM Employee e 
JOIN Department d ON e.Dept_no = d.Dept_No 
WHERE d.Dname = 'Sales';

5. Display the total number of employees in each department:
SELECT d.Dname, COUNT(e.Emp_id) AS Total_Employees
FROM Department d
LEFT JOIN Employee e ON d.Dept_No = e.Dept_no
GROUP BY d.Dname;

6. Update the salary of employee with empid 3 to 8000:
UPDATE Employee SET Salary = 8000 WHERE Emp_id = 3;

7. Delete the employee with empid=4:
DELETE FROM Employee WHERE Emp_id = 4;

8. Display the highest salary in each department:
SELECT d.Dname, MAX(e.Salary) AS Highest_Salary
FROM Department d
LEFT JOIN Employee e ON d.Dept_No = e.Dept_no
GROUP BY d.Dname;

9. Create a view to list the employees in ascending order of their Salaries:
CREATE VIEW Employee_Salary_View AS
SELECT * FROM Employee ORDER BY Salary ASC;

10. Create a trigger before insert on emp table to make salary zero if less than zero:
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
    IF :NEW.Salary < 0 THEN
        :NEW.Salary := 0;
    END IF;
END;

11. Aggregate Functions:
//SUM: Calculates the sum of values in a column.
SELECT SUM(Salary) FROM Employee;
//AVG: Calculates the average of values in a column.
SELECT AVG(Salary) FROM Employee;
//COUNT: Counts the number of rows in a result set or the number of non-null values in a column.
SELECT COUNT(*) FROM Employee;
//MAX: Returns the maximum value in a column.
SELECT MAX(Salary) FROM Employee;
//MIN: Returns the minimum value in a column.
SELECT MIN(Salary) FROM Employee;

12. E-R Diagram for Mobile Billing System:
Entities:

Customer (Customer_ID, Name, Address, Phone_Number)
Billing Details (Bill_ID, Customer_ID, Bill_Amount, Bill_Date)
Plan Details (Plan_ID, Plan_Name, Plan_Type, Monthly_Price)
Cities of Service (City_ID, City_Name, Service_Area)
Relationships:

Customer makes Billing Details (one-to-many)
Customer subscribes to Plan (many-to-one)
Plan is available in Cities of Service (many-to-many)