NAME: Salunke Aishwarya Santosh
ROLLNO: B-18
TITLE: Design at least 10 SQL queries for suitable database application using SQL DML Statements:
Insert, Select, Update, delete with operators, functions, and set operator.
CODE:
CREATE DATABASE practical;
USE practical;
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
hire_date DATE NOT NULL,
job_title VARCHAR(50),
salary DECIMAL(10, 2) CHECK (salary > 0),
department_id INT,
CONSTRAINT fk_department FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, email, job_title, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
CREATE INDEX idx_employee_email ON employees (email);
CREATE VIEW dept_alias AS
SELECT * FROM departments;
INSERT INTO departments (department_name)
VALUES ('Human Resources'), ('Engineering'), ('Marketing');
INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary,
department_id)
VALUES
('Aishwarya', 'salunke', 'aishwarya.salunke@example.com', '2020-01-15', 'HR Manager', 60000, 1),
('Rohit', 'kumar', 'rohit.kumar@example.com', '2021-06-30', 'Software Engineer', 80000, 2),
('preeti', 'sharma', 'preeti.sharma@example.com', '2019-11-20', 'Marketing Specialist', 55000, 3),
('harshda', 'sidankar', 'harshda.sidankar@example.com', '2022-03-25', 'Project Manager', 75000, 2),
('priyanshu', 'pal', 'priyanshu.pal@example.com', '2018-09-10', 'Business Analyst', 67000, 3);
SELECT * FROM employee_view;
INSERT INTO employees (first_name, last_name, email, hire_date, job_title, salary,
department_id)
VALUES ('suyash', 'pawar', 'suyash.pawar@example.com', '2023-05-14', 'Software Developer',
85000, 2);
SELECT first_name, last_name, email, job_title, salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name = 'Engineering';
SELECT *FROM employees;
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT department_id FROM departments WHERE
department_name = 'Marketing');
SELECT *FROM employees;
DELETE FROM employees WHERE email = 'preeti.sharma@example.com';
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date >= '2020-01-01';
SELECT department_name, COUNT(employee_id) AS total_employees
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY department_name;
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
UPDATE employees
SET job_title = 'Senior Software Developer'
WHERE email = 'rohit.kumar@example.com';
SELECT first_name, last_name, email
FROM employees
WHERE UPPER(SUBSTRING(first_name, 1, 1)) IN ('A', 'P');
SELECT first_name, last_name, job_title, salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name = 'Human Resources'
UNION
SELECT first_name, last_name, job_title, salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name = 'Marketing';
OUTPUT:
