Scribd
Upload
49 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
49 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

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 [Link], [Link], [Link], [Link]

FROM Employee e

JOIN WorksOn w ON [Link] = [Link]


JOIN Project p ON [Link] = [Link]

JOIN Department d ON [Link] = [Link]

WHERE [Link] = [Link];

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 [Link]

FROM Project p

JOIN Department d ON [Link] = [Link]

WHERE [Link] = (SELECT DeptID FROM Employee WHERE EmpID = 101);

SELECT [Link]

FROM Employee e

JOIN WorksOn w ON [Link] = [Link]

JOIN Project p ON [Link] = [Link]

WHERE [Link] = 2

GROUP BY [Link]

HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT(*) FROM Project WHERE DeptID = 2);

CREATE VIEW EmployeeProjectDetails AS

SELECT [Link], [Link], [Link], [Link]


FROM Employee e

JOIN WorksOn w ON [Link] = [Link]

JOIN Project p ON [Link] = [Link];

SELECT [Link], AVG([Link]) AS AvgSalary

FROM Employee e

JOIN Department d ON [Link] = [Link]

GROUP BY [Link]

HAVING AVG([Link]) > 50000;

CREATE assertion deptemployeelimit

CHECK (

NOT EXISTS (

SELECT DeptID

FROM Employee

GROUP BY DeptID

HAVING COUNT(*) > 10

);

You might also like