I am doing a database in SQL and I need to create a set of view statements that

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 determine how many employees per position there are by using view statements. 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

I have already answered a similiar question. A view is similar to a table. In order to create a view you have to use the following syntax

CREATE VIEW view_name AS (....some select query goes here...);

Now, in order to access it, you have to access it as if it were a normal table. For example
SELECT * FROM view_name;

The following is the ways to create views for the last two queries you asked for

CREATE VIEW employee_job_skills AS
(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)

CREATE VIEW employee_job_positions AS (
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)

Now finally to tell how to determine how many employees per position there are by using view statements, we can use the above generated view 'employee_job_position' to find it using the following query

SELECT job_title, count(*) FROM employee_job_positions GROUP BY job_title

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.