Scribd
Upload
39 views4 pages

Create Database 4m

The document outlines the creation of a database named '4m' with tables for Department, Employee, Project, and WorksOn, including their relationships through foreign keys. It also includes sample data insertion, various SQL queries for data retrieval and updates, and the creation of a view and assertion to manage employee limits within departments. The document demonstrates how to perform operations like joins, updates, and aggregations on the database structure.

Uploaded by

mutahhar82
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
39 views4 pages

Create Database 4m

The document outlines the creation of a database named '4m' with tables for Department, Employee, Project, and WorksOn, including their relationships through foreign keys. It also includes sample data insertion, various SQL queries for data retrieval and updates, and the creation of a view and assertion to manage employee limits within departments. The document demonstrates how to perform operations like joins, updates, and aggregations on the database structure.

Uploaded by

mutahhar82
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
You are on page 1/ 4

create database 4m;

use 4m;

CREATE TABLE Department (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(100),

Location VARCHAR(255)

);

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

Name VARCHAR(100),

Age INT,

Salary DECIMAL(10, 2),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

);

CREATE TABLE Project (

ProjID INT PRIMARY KEY,

ProjName VARCHAR(100),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

);

CREATE TABLE WorksOn (

EmpID INT,

ProjID INT,

Hours DECIMAL(5, 2),

PRIMARY KEY (EmpID, ProjID),

FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),

FOREIGN KEY (ProjID) REFERENCES Project(ProjID)


);

INSERT INTO Department (DeptID, DeptName, Location)

VALUES

(1, 'Human Resources', 'New York'),

(2, 'Engineering', 'San Francisco'),

(3, 'Marketing', 'Chicago');

INSERT INTO Employee (EmpID, Name, Age, Salary, DeptID)

VALUES

(1, 'John Doe', 30, 60000.00, 1),

(2, 'Jane Smith', 35, 75000.00, 2),

(3, 'Alice Johnson', 28, 50000.00, 1),

(4, 'Bob Lee', 40, 85000.00, 3),

(5, 'Charlie Brown', 45, 95000.00, 2);

INSERT INTO Project (ProjID, ProjName, DeptID)

VALUES

(1, 'Project Alpha', 1),

(2, 'Project Beta', 2),

(3, 'Project Gamma', 3);

INSERT INTO WorksOn (EmpID, ProjID, Hours)

VALUES

(1, 1, 40.00),

(2, 2, 35.50),

(3, 1, 20.00),

(4, 3, 45.00),

(5, 2, 30.00);

SELECT e.Name, e.EmpID, p.ProjName, d.DeptName

FROM Employee e

JOIN WorksOn w ON e.EmpID = w.EmpID


JOIN Project p ON w.ProjID = p.ProjID

JOIN Department d ON e.DeptID = d.DeptID

WHERE e.DeptID = p.DeptID;

ALTER TABLE WorksOn

ADD CONSTRAINT fk_employee

FOREIGN KEY (EmpID) REFERENCES Employee(EmpID);

ALTER TABLE WorksOn

ADD CONSTRAINT fk_proj

FOREIGN KEY (ProjID) REFERENCES Project(ProjID)

ON DELETE CASCADE;

UPDATE Department

SET DeptName = 'New Dept Name'

WHERE DeptID = 1;

UPDATE Employee

SET DeptID = 1

WHERE DeptID = 1 AND DeptID NOT IN (SELECT DeptID FROM Department);

SELECT p.ProjName

FROM Project p

JOIN Department d ON p.DeptID = d.DeptID

WHERE d.DeptID = (SELECT DeptID FROM Employee WHERE EmpID = 101);

SELECT e.Name

FROM Employee e

JOIN WorksOn w ON e.EmpID = w.EmpID

JOIN Project p ON w.ProjID = p.ProjID

WHERE p.DeptID = 2

GROUP BY e.EmpID

HAVING COUNT(DISTINCT p.ProjID) = (SELECT COUNT(*) FROM Project WHERE DeptID = 2);

CREATE VIEW EmployeeProjectDetails AS

SELECT e.EmpID, e.Name, p.ProjName, w.Hours


FROM Employee e

JOIN WorksOn w ON e.EmpID = w.EmpID

JOIN Project p ON w.ProjID = p.ProjID;

SELECT d.DeptName, AVG(e.Salary) AS AvgSalary

FROM Employee e

JOIN Department d ON e.DeptID = d.DeptID

GROUP BY d.DeptName

HAVING AVG(e.Salary) > 50000;

CREATE assertion deptemployeelimit

CHECK (

NOT EXISTS (

SELECT DeptID

FROM Employee

GROUP BY DeptID

HAVING COUNT(*) > 10

);

You might also like