Scribd
Upload
4 views2 pages

SQL Database Creation and Management

The document outlines SQL commands to create and manage a company database with tables for Employees and Departments. It includes commands for creating tables, inserting data, creating views, and executing various SQL queries for data retrieval and manipulation. Key operations include displaying employees, updating salaries, and deleting records, along with constraints and indexing for efficient data management.

Uploaded by

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

SQL Database Creation and Management

The document outlines SQL commands to create and manage a company database with tables for Employees and Departments. It includes commands for creating tables, inserting data, creating views, and executing various SQL queries for data retrieval and manipulation. Key operations include displaying employees, updating salaries, and deleting records, along with constraints and indexing for efficient data management.

Uploaded by

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

-- Create and use database

CREATE DATABASE company_db;


USE company_db;

-- Drop tables if they exist (for re-run)


DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;

-- 1. Create Table: Department


CREATE TABLE Department (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL UNIQUE
);

-- 2. Create Table: Employee with constraints


CREATE TABLE Employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18),
salary DECIMAL(10,2) DEFAULT 30000,
gender ENUM('M','F'),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- 3. Create an Index on employee name


CREATE INDEX idx_emp_name ON Employee(emp_name);

-- 4. Create a View combining Employee and Department


CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.emp_id, e.emp_name, [Link], [Link], [Link], d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- 5. Insert data into Department


INSERT INTO Department (dept_name) VALUES
('HR'), ('Finance'), ('IT'), ('Sales');

-- 6. Insert data into Employee


INSERT INTO Employee (emp_name, age, salary, gender, dept_id) VALUES
('Amit', 25, 50000, 'M', 1),
('Riya', 28, 60000, 'F', 1),
('Suresh', 30, 55000, 'M', 2),
('Neha', 26, 65000, 'F', 2),
('Raj', 32, 70000, 'M', 3),
('Priya', 24, 40000, 'F', 3),
('Vikas', 29, 45000, 'M', 4),
('Tina', 27, 47000, 'F', 4);

-- ----------------------------------------------------
-- 10 SQL DML Queries
-- ----------------------------------------------------

-- 1. Display all employees


SELECT * FROM Employee;

-- 2. Display all departments


SELECT * FROM Department;
-- 3. Display employee names and their departments (JOIN)
SELECT e.emp_name, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- 4. Display employees earning more than 50,000


SELECT emp_name, salary FROM Employee WHERE salary > 50000;

-- 5. Display number of employees in each department


SELECT d.dept_name, COUNT(e.emp_id) AS total_employees
FROM Department d
LEFT JOIN Employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

-- 6. Update salary of 'Vikas' to 48000


UPDATE Employee SET salary = 48000 WHERE emp_name = 'Vikas';

-- 7. Delete an employee named 'Tina'


DELETE FROM Employee WHERE emp_name = 'Tina';

-- 8. Display employees sorted by salary descending


SELECT emp_name, salary FROM Employee ORDER BY salary DESC;

-- 9. Display employees using the created View


SELECT * FROM emp_dept_view;

-- 10. Find employees older than average employee age (Subquery)


SELECT emp_name, age
FROM Employee
WHERE age > (SELECT AVG(age) FROM Employee);

You might also like