Scribd
Upload
7 views3 pages

SQL Assignment Output

The document outlines an SQL assignment that requires the creation of three tables: Department, Employee, and Project, each with specified primary and foreign keys. It includes SQL commands to create these tables, insert sample data, and write five subqueries that utilize values from all three tables. The subqueries focus on various employee and project attributes related to department characteristics and statuses.

Uploaded by

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

SQL Assignment Output

The document outlines an SQL assignment that requires the creation of three tables: Department, Employee, and Project, each with specified primary and foreign keys. It includes SQL commands to create these tables, insert sample data, and write five subqueries that utilize values from all three tables. The subqueries focus on various employee and project attributes related to department characteristics and statuses.

Uploaded by

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

SQL Assignment

Question
Question: Create three tables. In table number one use one PRIMARY KEY and 7 columns. In second
table use one PRIMARY KEY and one FOREIGN KEY referencing the first table with 8 columns. In
third table use one PRIMARY KEY, 8 columns, and one FOREIGN KEY referencing the second table.
Insert 10 rows in each table and write 5 subqueries that include values from all three tables.

SQL Commands
-- Create Table 1
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(50),
manager VARCHAR(50),
phone VARCHAR(15),
email VARCHAR(50),
established_year INT
);

-- Create Table 2
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
dept_id INT,
emp_name VARCHAR(50),
position VARCHAR(50),
salary INT,
hire_date DATE,
phone VARCHAR(15),
email VARCHAR(50),
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- Create Table 3
CREATE TABLE Project (
project_id INT PRIMARY KEY,
emp_id INT,
project_name VARCHAR(50),
budget INT,
start_date DATE,
end_date DATE,
status VARCHAR(20),
client_name VARCHAR(50),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
);

Sample Output Tables

Department Table Output


dept_id dept_name location manager phone email year
1 HR New York John 1111111111 hr@[Link] 2001
2 Finance London David 2222222222 finance@[Link] 2003
3 IT Bangalore Ravi 3333333333 it@[Link] 2005

Employee Table Output


emp_id dept_id emp_name position salary
101 1 Alan Manager 60000
102 2 Priya Accountant 55000
103 3 Rahul Developer 70000

Project Table Output


project_id emp_id project_name budget status
201 101 HR System 50000 Completed
202 102 Finance App 60000 Running
203 103 Website Dev 80000 Running

Subqueries
Subqueries:

1. Employees working in departments established after 2005


SELECT emp_name
FROM Employee
WHERE dept_id IN (
SELECT dept_id FROM Department WHERE established_year > 2005
);

2. Projects handled by employees with salary > 60000


SELECT project_name
FROM Project
WHERE emp_id IN (
SELECT emp_id FROM Employee WHERE salary > 60000
);

3. Departments having employees working on running projects


SELECT dept_name
FROM Department
WHERE dept_id IN (
SELECT dept_id FROM Employee
WHERE emp_id IN (
SELECT emp_id FROM Project WHERE status='Running'
)
);

4. Employees working in departments located in Bangalore


SELECT emp_name
FROM Employee
WHERE dept_id IN (
SELECT dept_id FROM Department WHERE location='Bangalore'
);
5. Projects handled by employees from HR department
SELECT project_name
FROM Project
WHERE emp_id IN (
SELECT emp_id FROM Employee
WHERE dept_id IN (
SELECT dept_id FROM Department WHERE dept_name='HR'
)
);

You might also like