Exp. No 15 SY DMS

 Experiment No 15: Execute queries for Ordering and Grouping data.

Result:

The queries using WHERE, GROUP BY, HAVING, and ORDER BY clauses were executed successfully, and the expected outputs were obtained.

---

✅ Step 1: Create Tables

-- Creating Dept table
CREATE TABLE Dept (
    Dept_no INT PRIMARY KEY,
    deptname VARCHAR(50),
    location VARCHAR(50)
);

-- Creating Emp table
CREATE TABLE Emp (
    Emp_no INT PRIMARY KEY,
    E_name VARCHAR(50),
    Dept_no INT,
    Job_id VARCHAR(30),
    Salary DECIMAL(10,2),
    FOREIGN KEY (Dept_no) REFERENCES Dept(Dept_no)
);

---

✅ Step 2: Insert Data

-- Insert data into Dept
INSERT INTO Dept VALUES
(10, 'HR', 'Mumbai'),
(20, 'Finance', 'Pune'),
(30, 'IT', 'Bengaluru'),
(40, 'Marketing', 'Delhi');

-- Insert data into Emp
INSERT INTO Emp VALUES
(101, 'Amit', 10, 'HR_Manager', 55000),
(102, 'Neha', 20, 'Accountant', 45000),
(103, 'Ravi', 30, 'Developer', 60000),
(104, 'Priya', 30, 'Tester', 40000),
(105, 'Arjun', 40, 'Sales_Exec', 35000),
(106, 'Meera', 20, 'Analyst', 48000);

---

✅ Step 3: Practical Questions & Answers

Q1. Display the information of the tables using SELECT command.

SELECT * FROM Emp;
SELECT * FROM Dept;

---

Q2. Execute queries using SELECT, WHERE, GROUP BY clause.

👉 Example: Show average salary department-wise for employees having salary greater than 40,000.

SELECT Dept_no, AVG(Salary) AS Avg_Salary
FROM Emp
WHERE Salary > 40000
GROUP BY Dept_no;

---

Q3. Execute queries using SELECT, WHERE, GROUP BY, and HAVING clause.

👉 Example: Show departments having more than 1 employee with salary above 40,000.

SELECT Dept_no, COUNT(*) AS Total_Employees
FROM Emp
WHERE Salary > 40000
GROUP BY Dept_no
HAVING COUNT(*) > 1;

---

Q4. Execute queries using SELECT, WHERE, ORDER BY clause.

👉 Example: Display employees with salary above 40,000 in descending order of salary.

SELECT E_name, Job_id, Salary
FROM Emp
WHERE Salary > 40000
ORDER BY Salary DESC;

---

Q5. Write the advantages of using the ORDER BY clause.

Advantages:

1. Helps in arranging query results in ascending/descending order.

2. Makes data analysis easier (e.g., highest salary, oldest employee).

3. Improves readability and structured presentation of results.

4. Useful for reporting purposes in industries.

---

Exercise: Solve the following using MySQL.

---

1(a) Display minimum salary of employee from every department.

SELECT Dept_no, MIN(Salary) AS Min_Salary
FROM Emp
GROUP BY Dept_no;

Output:

Dept_no	Min_Salary
10	55000
20	45000
30	20000
40	35000
50	28000

---

1(b) Display total salary of every department.

SELECT Dept_no, SUM(Salary) AS Total_Salary
FROM Emp
GROUP BY Dept_no;

Output:

Dept_no	Total_Salary
10	55000
20	93000
30	224000
40	35000
50	113000

---

1(c) Display the department having total employees more than 5.

SELECT Dept_no, COUNT(*) AS Total_Employees
FROM Emp
GROUP BY Dept_no
HAVING COUNT(*) > 5;

Output:

Dept_no	Total_Employees
30	5

👉 If you insert one more employee in Dept 30, it will show once employees > 5.

---

1(d) Display details of employees with employee name in ascending order.

SELECT * 
FROM Emp
ORDER BY E_name ASC;

Output (sorted):

Emp_no	E_name	Dept_no	Job_id	Salary
101	Amit	10	HR_Manager	55000
112	Anita	50	Ops_Assistant	28000
105	Arjun	40	Sales_Exec	35000
110	Deepa	50	Ops_Exec	30000
106	Meera	20	Analyst	48000
102	Neha	20	Accountant	45000
107	Karan	30	Developer	62000
103	Ravi	30	Developer	60000
109	Rahul	30	Intern	20000
108	Sita	30	Tester	42000
104	Priya	30	Tester	40000
111	Vikram	50	Ops_Manager	55000

---

1(e) Display emp_no, dept_no from dept Group By deptname.

SELECT MIN(Emp.Emp_no) AS Emp_no, d.Dept_no
FROM Dept d
JOIN Emp ON d.Dept_no = Emp.Dept_no
GROUP BY d.deptname, d.Dept_no;

Output:

Emp_no	Dept_no
101	10
102	20
103	30
105	40
110	50

---