Assignment1Group.txt -- Create Employees table

[kanchanpal710]

CREATE TABLE Employees_data ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10, 2) );

INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES (1, 'John', 'Smith', 'Sales', 60000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(2, 'Jane', 'Doe', 'HR', 55000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(3, 'Michael', 'Johnson', 'IT', 65000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(4, 'Emily', 'Williams', 'Sales', 62000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(5, 'David', 'Brown', 'HR', 52000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(6, 'Sarah', 'Lee', 'IT', 68000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(7, 'Jessica', 'Clark', 'Sales', 58000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(8, 'Ryan', 'Taylor', 'IT', 63000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(9, 'Ashley', 'Martinez', 'Sales', 59000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(10, 'Matthew', 'Anderson', 'HR', 51000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(11, 'Samantha', 'Garcia', 'Sales', 57000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(12, 'Daniel', 'Rodriguez', 'HR', 53000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(13, 'Amanda', 'Hernandez', 'IT', 70000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(14, 'Justin', 'Wright', 'Sales', 56000); INSERT INTO Employees_data (EmployeeID, FirstName, LastName, Department, Salary) VALUES(15, 'Nicole', 'Perez', 'HR', 54000);

select * from Employees_data;

--Retrieve the total number of employees in each department

SELECT COUNT(EmployeeID), department FROM Employees_data GROUP BY department;

--Display the departments with an average salary greater than $55000

SELECT department, AVG(salary) AS avg_salary FROM Employees_data GROUP BY department HAVING AVG(salary) > 55000;

====ASSIGMENT IN PROGRESS======

[uday0097]

Good keep going on and also really happy with your progress.

[JyothiPinnam]

select * from employees3; ----Retrieve the total number of employees in each department. select Department , count(EmployeeID) as 'Number of employees' from employees3 group by department;

-----Display the departments with an average salary greater than $55000. select Department,AVG(salary) as 'Average salary' from Employees3 where Salary >55000 group by Department;

----Find the highest salary in each department.

select Department, max(salary) as 'Highest salary' from Employees3 group by Department;

---Show the employees whose first name starts with 'J' and belong to the 'Sales' department.

select * from Employees3 where FirstName like 'J%' and Department='Sales';

----List the top 5 highest-paid employees.

select * from Employees3

select top 5 * from Employees3 order by salary desc;