drop table project
drop table employee
create table employee
employee_id number primary key
first_name varchar(50)
last_name varchar(50)
department varchar(100)
position varchar(100
)
create table project
project_id number primary key
project_name varchar(100)
project_description varchar(255)
start_date date
end_date date
employee_id number
constraint fk_proj_employee_id foreign key
(employee_id) references employee(employee_id
)
-- Insert records into Employee tabl
insert into employee (employee_id, first_name,
last_name, department, position
values (1, 'John', 'Doe', 'IT', 'Software Engineer')
insert into employee (employee_id, first_name,
last_name, department, position
values (2, 'Jane', 'Smith', 'HR', 'HR Manager')
insert into employee (employee_id, first_name,
last_name, department, position
values (3, 'Michael', 'Johnson', 'Marketing',
'Marketing Specialist')
/
insert into employee (employee_id, first_name,
last_name, department, position
values (4, 'Emily', 'Williams', 'Finance', 'Financial
Analyst')
insert into employee (employee_id, first_name,
last_name, department, position
values (5, 'David', 'Brown', 'Operations',
'Operations Manager')
-- Insert records into Project tabl
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (101, 'Website Redesign', 'Redesigning company
website', to_date('2024-01-15','yyyy-mm-dd'),
to_date('2024-05-15','yyyy-mm-dd'), 1)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (102, 'Employee Training', 'Training program
for new employees', to_date('2024-02-01','yyyy-mm-
dd'), to_date('2024-03-31','yyyy-mm-dd'), 2)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (103, 'Product Launch', 'Launching new product
line', to_date('2024-03-10','yyyy-mm-dd'),
to_date('2024-06-30','yyyy-mm-dd'), 3)
/
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (104, 'Financial Audit', 'Annual financial
audit', to_date('2024-04-15','yyyy-mm-dd'),
to_date('2024-05-30','yyyy-mm-dd'), 4)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (105, 'Warehouse Optimization', 'Optimizing
warehouse operations', to_date('2024-02-15','yyyy-mm-
dd'), to_date('2024-04-30','yyyy-mm-dd'), 5)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (106, 'Mobile App Development', 'Developing a
new mobile app', to_date('2024-03-01','yyyy-mm-dd'),
to_date('2024-07-31','yyyy-mm-dd'), 1)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (107, 'Financial Planning', 'Strategic
financial planning', to_date('2024-04-01','yyyy-mm-
dd'), to_date('2024-12-31','yyyy-mm-dd'), 3)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (108, 'Social Media Campaign', 'Launching
social media campaign', to_date('2024-03-15','yyyy-
mm-dd'), to_date('2024-05-30','yyyy-mm-dd'), 4)
/
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (109, 'Logistics Optimization', 'Optimizing
supply chain logistics', to_date('2024-05-01','yyyy-
mm-dd'), to_date('2024-08-31','yyyy-mm-dd'), 5)
insert into project (project_id, project_name,
project_description, start_date, end_date,
employee_id
values (110, 'Employee Benefits Review', 'Reviewing
employee benefits packages',
to_date('2024-02-15','yyyy-mm-dd'),
to_date('2024-04-30','yyyy-mm-dd'), 3)
1. selects all columns from both the employee and
project tables where the employee_id is 1 and the
project_id is 10
SELECT e.*, p.
FROM employee e, project
WHERE e.employee_id = p.employee_id AND e.employee_id = 1 AND
p.project_id = 106
2. selects the first_name and last_name columns from
the employee table, and the project_name, start_date,
and end_date columns from the project table where the
employee's position is 'HR Manager'
SELECT e.first_name, e.last_name, p.project_name, p.start_date,
p.end_dat
FROM employee e, project
WHERE
e.employee_id = p.employee_id
and e.position = 'HR Manager’
3. select the first_name and last_name ,
project_name, start_date and end_date columns where
the employee's department is ‘Marketing' and the
project's start_date is on or after January 1, 2024
/
select e.first_name, e.last_name, p.project_nam
, p.start_date, p.end_dat
from employee e, project p
where e.employee_id = p.employee_i
and e.department = 'Marketing'
and p.start_date >= date ‘2024-01-01'
4. Write SQL query to select the first_name,
last_name project_name, start_date and end_date
columns from employee and project tables where
employees working in the Marketing departmen
and employees with the position of Marketing
Specialist also projects with a start date on or
after January 1, 202
select e.first_name, e.last_name, p.project_name, p.start_date,
p.end_dat
from employee e, project p
where e.employee_id = p.employee_i
and e.department = 'Marketing'
and e.position = 'Marketing Specialist
and p.start_date >= date ‘2024-01-01'
5. Write SQL query to select the first_name,
last_name project_name, start_date, and end_date
columns from the employee and project tables where
employee working in the Finance department, the
position of Financial Analyst and projects with an
end date on or before May 30, 202
select e.first_name, e.last_name, p.project_name, p.start_date,
p.end_dat
from employee e, project p
where e.employee_id = p.employee_i
and e.department = 'Finance
and e.position = 'Financial Analyst
and p.end_date <= date '2024-05-30'
e
'
'
'
