SQL Commands and Examples
4.1 Database Creation with 3 Tables, Show Database, Delete Database
-- Create Database
CREATE DATABASE OrganizationDB;
-- Use Database
USE OrganizationDB;
-- Show all Databases
SHOW DATABASES;
-- Delete Database
DROP DATABASE OrganizationDB;
4.2 Create 3 Tables with Attributes and Constraints
-- Create DEPARTMENT Table
CREATE TABLE Department (
Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(50) NOT NULL,
Location VARCHAR(50),
Head_Name VARCHAR(50),
Budget DECIMAL(10,2)
);
-- Create EMPLOYEE Table
CREATE TABLE Employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50) NOT NULL,
Designation VARCHAR(50),
Salary DECIMAL(10,2) CHECK (Salary > 0),
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);
-- Create PROJECT Table
CREATE TABLE Project (
Proj_ID INT PRIMARY KEY,
Proj_Name VARCHAR(50),
Start_Date DATE,
End_Date DATE,
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);
4.3 Use of SQL Statements: Insert Into, Update, Delete, Alter, Truncate
-- Insert Records
INSERT INTO Department VALUES (1, 'HR', 'Delhi', 'Riya Sharma', 500000);
INSERT INTO Department VALUES (2, 'Finance', 'Mumbai', 'Arjun Mehta', 700000);
INSERT INTO Department VALUES (3, 'IT', 'Bangalore', 'Priya Singh', 900000);
INSERT INTO Employee VALUES (101, 'Aman', 'Manager', 85000, 1);
INSERT INTO Employee VALUES (102, 'Karan', 'Analyst', 60000, 2);
INSERT INTO Employee VALUES (103, 'Sanya', 'Developer', 70000, 3);
INSERT INTO Project VALUES (201, 'Payroll System', '2023-01-01', '2023-06-01', 2);
INSERT INTO Project VALUES (202, 'Recruitment Portal', '2023-02-15', '2023-07-30', 1);
INSERT INTO Project VALUES (203, 'ERP Implementation', '2023-03-10', '2023-12-15', 3);
-- Update Record
UPDATE Employee SET Salary = 90000 WHERE Emp_ID = 101;
-- Delete Record
DELETE FROM Employee WHERE Emp_ID = 102;
-- Alter Table
ALTER TABLE Employee ADD Email VARCHAR(50);
-- Truncate Table
TRUNCATE TABLE Project;
4.4 SQL Queries Using Various Commands
-- Select and Where
SELECT * FROM Employee WHERE Salary > 70000;
-- Group By and Having
SELECT Dept_ID, COUNT(Emp_ID) AS Total_Employees FROM Employee
GROUP BY Dept_ID HAVING COUNT(Emp_ID) > 1;
-- Order By
SELECT * FROM Employee ORDER BY Salary DESC;
-- Join
SELECT E.Emp_Name, D.Dept_Name
FROM Employee E
JOIN Department D ON E.Dept_ID = D.Dept_ID;
-- Union
SELECT Emp_Name FROM Employee
UNION
SELECT Head_Name FROM Department;
-- Drop Table
DROP TABLE Project;
-- Rename Table
RENAME TABLE Employee TO Staff;
4.5 SQL Queries Using Arithmetic, Comparison and Logical Operators
-- Arithmetic
SELECT Emp_Name, Salary, Salary + 5000 AS Revised_Salary FROM Employee;
-- Comparison
SELECT * FROM Employee WHERE Salary >= 70000;
-- Logical
SELECT * FROM Employee WHERE Salary > 60000 AND Dept_ID = 3;
4.6 SQL Queries Using SUM, AVG, COUNT Commands
-- SUM
SELECT SUM(Salary) AS Total_Salary FROM Employee;
-- AVG
SELECT AVG(Salary) AS Average_Salary FROM Employee;
-- COUNT
SELECT COUNT(*) AS Total_Employees FROM Employee;
4.7 SQL Statement to Create View
-- Create a View to show Employee details with Department name
CREATE VIEW Emp_Dept_View AS
SELECT E.Emp_ID, E.Emp_Name, E.Salary, D.Dept_Name
FROM Employee E
JOIN Department D ON E.Dept_ID = D.Dept_ID;
-- Display View
SELECT * FROM Emp_Dept_View;
