Scribd
Upload
9 views3 pages

SQL Queries for Employee Management

The document contains SQL commands to create 'Departments' and 'Employees' tables, including their structures and relationships. It also includes various queries for data retrieval, such as finding employees with salaries over 50,000, joining employee and department data, calculating average salaries by department, and identifying employees hired in the last two years. Additionally, it features commands for inserting sample data into the tables.

Uploaded by

mahapraasad.jena
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views3 pages

SQL Queries for Employee Management

The document contains SQL commands to create 'Departments' and 'Employees' tables, including their structures and relationships. It also includes various queries for data retrieval, such as finding employees with salaries over 50,000, joining employee and department data, calculating average salaries by department, and identifying employees hired in the last two years. Additionally, it features commands for inserting sample data into the tables.

Uploaded by

mahapraasad.jena
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

-- Create Departments table

CREATE TABLE Departments (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(50)

);

-- Create Employees table

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

salary DECIMAL(10,2),

dept_id INT,

hire_date DATE,

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

INSERT INTO Departments VALUES

(1, 'HR'),

(2, 'IT'),

(3, 'Finance');

INSERT INTO Employees VALUES

(101, 'Alice', 50000, 2, '2022-01-15'),

(102, 'Bob', 60000, 2, '2021-03-10'),

(103, 'Charlie', 45000, 1, '2023-06-20'),

(104, 'David', 70000, 3, '2020-11-05'),

(105, 'Eva', 55000, 1, '2022-09-01');

Select* from Departments;


Select* from Employees;

----Find employees with salary greater than 50,000.

Select emp_name, salary

from Employees

WHERE salary>50000;

-------Employee name with department name

Select e.emp_name, d.dept_name

from employees e

JOIN departments d

ON e.dept_id=d.dept_id;

----Average salary department-wise

Select dept_id,AVG(salary) AS Avgsalary

FROM employees

GROUP BY dept_id;

---Second highest salary

Select MAX(salary)

from Employees

Where Salary < (Select MAX(salary) from Employees);

-----Find employees earning more than the average salary

Select emp_name, salary

FROM employees

Where Salary > (Select AVG(salary) from Employees);

Select* from Departments;

Select* from Employees;

----Display department name with total number of employees

Select d.dept_name, COUNT(emp_id) AS total_emp

FROM departments d

JOIN employees e
ON d.dept_id = e.dept_id

GROUP BY d.dept_name;

----Find employees hired in the last 2 years

Select * from employees

WHERE hire_date >= DATEADD (YEAR, -4, GETDATE());

You might also like