lab6

--1. Create a new view v_clerk which retrieves the rows of the works_on table,
--for which the condition job = 'Clerk' evaluates to true.

CREATE VIEW v_clerk AS
SELECT *
FROM works_on
WHERE job = 'Clerk'

-- Create a new view v_without_budget which contains all columns of the project table except the budget column.
CREATE VIEW v_without_budget AS
SELECT project_no, project_name
FROM project
-- Create a new view containing project_no and the number of employees involved.
CREATE VIEW containing_project_no AS
SELECT  project_no ,COUNT(emp_no) AS SUM
FROM works_on
GROUP BY  project_no
-- Create a new view v_clerk2 which is derived from the v_clerk view, containing only emp_no. 5

CREATE VIEW  v_clerk2  AS
SELECT  emp_no
FROM v_clerk

--Using the ALTER VIEW statement extend the SELECT statement of
--the v_without_budget view with the new condition in the WHERE clause - project_no >= 'p3'.

ALTER VIEW v_without_budget  AS
SELECT project_no, project_name
FROM project
WHERE project_no >=  'p3'

----Remove the definition of the v_clerk view from the system tables
DROP VIEW v_clerk

-- Create a new view v_d2 which retrieves emp_no and emp_lname working at d2 department.
CREATE VIEW v_d2 AS
SELECT emp_no,emp_lname, dept_no
FROM employee
WHERE dept_no = 'd2'

-- Then select, from this view, employees whose name contain the letter J.

SELECT *
FROM v_d2
WHERE emp_lname LIKE '%[J]%'

--Create a new view v_dept containing dept_no and dept_name. Afterwards, insert a new department d4 ('Development') into this view.
CREATE VIEW v_dept AS
SELECT dept_no , dept_name
FROM department
INSERT INTO v_dept  VALUES ('d4','Development')

-- Create a new view v_p1 for p1 project, containing emp_no and job. Update this view setting NULL instead of the 'Manager' job.
CREATE VIEW v_p1 AS
SELECT emp_no, job, project_no
FROM works_on
WHERE project_no = 'p1'
UPDATE  v_p1
SET job=NULL
WHERE job ='Manager';

--- Delete rows connected with clerks from view v_p1.
DELETE FROM v_p1
WHERE job ='clerk'

--Create a view that comprises the data of all employees that work for the department d1.

CREATE VIEW empt_data AS
SELECT*
FROM employee
WHERE dept_no = 'd1'

-- For the project table, create a view that can be used by employees who are allowed to view all data of this table except
--the budget column.

CREATE VIEW lel AS
SELECT project_name, project_no
FROM project
--. Create a view that comprises the first and last names of all employees
--who entered their projects in the second half of the year 2007.
CREATE VIEW zad14 AS
SELECT emp_lname, emp_fname, works_on.enter_date
FROM employee
JOIN works_on
ON works_on.emp_no = employee.emp_no
WHERE enter_date >'01-07-2007'

--15, Solve Exercise 13 so that the original columns f_name and l_name have new names in the view: first and last, respectively.

EXEC sp_rename 'v_p1.emp_fname', 'first', 'COLUMN'
EXEC sp_rename 'v_p1.emp_lname', 'last', 'COLUMN'

---16. Use the view in 11 to display full details of every employee whose last name begins with the letter m
SELECT *
FROM v_p1
WHERE v_p1.LAST LIKE '[m]%'--dont work

--17  Create a view that comprises full details of all projects on which the employee named Smith works.
CREATE VIEW hehe AS
SELECT project.BY=udget, project.project_name
FROM project
JOIN works_on
ON works_on.emp_no = employee.emp_no
WHERE empl_lname = 'Smith'




--Create a view that comprises full details of all projects on which the employee named Smith works.
CREATE VIEW [zad17a] AS
SELECT project.budget, project.project_name
FROM project
JOIN works_on
ON project.project_no=works_on.project_no
JOIN employee
ON works_on.emp_no=employee.emp_no
WHERE employee.emp_lname='Smith'

--Using the ALTER VIEW statement, modify the condition in the view in 11.The modified view should comprise the data of all employees that work either for the department d1 or d2, or both.
ALTER VIEW new1
AS
SELECT  * FROM employee
WHERE dept_no= 'd1' OR dept_no= 'd2'

--Delete the view created in 13. What happens with the view created in 14
DROP VIEW zad13
SELECT * FROM zad14

--Using the view from 12, insert the details of the new project with the project number p2 and the name Moon
INSERT INTO[v_without_budget]
VALUES ('moon', 'p2')