I am doing a database in SQL and I need to create a set of view statements that
ID: 3578585 • Letter: I
Question
I am doing a database in SQL and I need to create a set of view statements that would help the HR director. Can You tell me how to Generate a list of positions and their required skills (use a join)? Here is my database so far. Thank You use [Human Resources (HR) Department] create table employee( emp_id int identity not null, first_name varchar(30), last_name varchar (30), e_mail varchar (30), hire_date varchar (30), phone_number varchar (30), constraint pk_employee primary key(emp_id) ); create table job( job_id int identity not null, emp_id int not null, job_title varchar (30), min_salary numeric (5,2), max_salary numeric (5,2), constraint pk_job primary key (job_id), constraint fk_job_employee foreign key (emp_id) references employee (emp_id) on delete cascade on update cascade, ); create table skill( skill_id int identity not null, skill_type varchar (30), skill_description varchar (30), emp_id int not null, job_id int not null, constraint pk_skill primary key (skill_id), constraint fk_ps_skill_employee foreign key (emp_id) references employee (emp_id) on delete cascade on update cascade, constraint fk_ps_skill_job foreign key (job_id) references job (job_id) on delete cascade on update cascade, ); create table position( job_title varchar(30), job_id int not null, emp_id int not null, job_grade varchar(30), constraint fk_ps_position_job foreign key (job_id) references job (job_id) on delete cascade on update cascade, contraint fk_ps_position_employee foreign key (emp_id) references employee (emp_id) on delete cascade on update cascade, ); Use [Human Resources (HR) Department]; insert into employee (emp_id, first_name, last_name, e_mail, hire_date, phone_number) values (1, 'frank','douglas','frank@hotmail.com','2012-12-12','233-33-44') (2, 'john','smith','john@hotmail.com','2014-10-10','224-49-82') (3, 'brax','rodriguez','brax@hotmail.com','2015-10-10','224-49-99') (4, 'damian','golec','damian@hotmail.com','2016-10-10','225-55-55') (5, 'mariusz','chorazy','mariusz@hotmail.com','2015-10-11','223-66-66'); insert into job (job_id, emp_id, job_title, min_salary, max_salary) values (6,1,'business analyst','30000.00', '50000.00') (7,2,'system analyst','40000.00','50000.00') (8,3,'website designer','50000.00','60000.00') (9,4,'database developer','50000.00','70000.00') (10,5,'network administrator','50000.00','80000.00'); insert into skill (skill_id, skill_type, skill_description, emp_id, job_id) values (11,'c++','programming',1,6) (12,'visual basic','programming',7,2) (13,'sql','database',8,3) (14,'html','web design',9,4) (15,'links computers','networking',10,5); insert into position (job_title,job_id,emp_id,job_grade) values ('business analyst',6,1,'b') ('system analyst',7,2,'a') ('website designer',8,3,'c') ('database developer',9,4,'a') ('network administrator',10,5,'b'); This is in a new query(same database) use [Human Resources (HR) Department]; ( select s.skill_id, s.skill_type, s.skill_description, e.emp_id, j.job_id from skill s inner join employee e on e.emp_id = e.emp_id inner join job j on j.job_id = s.job_id ); use [Human Resources (HR) Department]; ( select p.job_title, e.emp_id, j.job_id from position p inner join employee e on e.emp_id = p.emp_id inner join job j on j.job_id = p.job_id );
Explanation / Answer
create table position( job_title varchar(30), job_id int not null, emp_id int not null, job_grade varchar(30), constraint fk_ps_position_job foreign key (job_id)
skill( skill_id int identity not null, skill_type varchar (30), skill_description varchar (30), emp_id int not null, job_id int not null, constraint pk_skill primary key (skill_id))
select * from position, skill JOIN skill where position.job_id = skill.job_id;
Explain to Query: Selecting all rows data from both position and skills table where condition is both foreign keys job_id should match.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.