SQL Queries on Views, Complex View, Insert, update views.

                                                              

1 Q1)Create your own EMP and DEPT by using CREATE TABLE EMP AS syntax

Sol: Create table emp(empno number,ename varchar2(15),job varchar2(15),mgr number,hiredate date, sal number,comm number,deptno number);

Create table dept(deptno number, deptname varchar2(15), loc varchar2(15));

Q2)Add PK on both tables and FK as necessary.

                Sol: alter table employee add primary key(emp_no);

Alter table employee add foreign key(dept_no) references dept(dept_no);

Q3)Add NOT NULL constraint on Deptno in EMP table

Sol: alter table dept modify dept_no constraint dept_no_nn NOT NULL;

Q4)Add constraint on ENAME to ensure all records inserted will have ENAME in UPPERCASE.  ALTER TABLE table_name add CONSTRAINT constraint_name CHECK (column_name condition)

Sol:  alter table S_12345_EMP add constraint NAME_UPPER_CK check(FIRST_NAME = upper(FIRST_NAME));

Q5)Write a query to display all constraints for table EMP.

Sol: SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERRED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'S_12345_EMP';

Q6)Create a view that display only the empno/ename/deptno in SALES dept. (deptno=30). Describe the structure of the view (combined 6 and 7)

Sol: create view emp_details AS select emp_no, emp_name, dept_no from employee where dept_no = 14;

Q7)Describe the structure of the view CREATE OR REPLACE VIEW sales_dept_emp_vw (emp_num,emp_name,dept_no) AS SELECT empno employee_number, ename, deptno FROM emp WHERE deptno = 30;

Q8)SELECT/INSERT/UPDATE/DELETE on this view.

SOL:

Sol8: UPDATE EMP_DETAINLS2_VIEW SET FIRST_NAME = ‘VIEW_NAME’ WHERE DEPT_ID =50;

Q9)Create a view With CHECK Option CREATE OR REPLACE VIEW sales_dept_emp_vw_1 (emp_num,emp_name,dept_no) AS SELECT empno employee_number, ename, deptno FROM emp WHERE deptno = 30 WITH CHECK OPTION;

Sol: CREATE VIEW EMP_CHECK_VIEW AS SELECT * FROM S_12345_EMP WHERE DEPT_ID>30 WITH CHECK OPTION CONSTRAINT EMP_CHECK_VIEW_FIRST_NAME;

Q10. Try inserting a record with deptno !=30.

SOL10)

INSERT INTO EMP_CHECK_VIEW(ID,LAST_NAME,FIRST_NAME,USERID,START_DATE,COMMENTS,MANAGER_ID,TITLE,DEPT_ID,SALARY,COMMISSION_PCT) VALUES (123,’TYAGI’,’BHAVI’,’BHAVI123’,’16-NOV-14’,’NO_COMMENTS’,124,’MANAGER’,29,20000,10);

INSERT INTO EMP_CKECK_VIEW (

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

Q11. Create a view with READ ONLY CREATE OR REPLACE VIEW sales_dept_emp_vw_2(emp_num,emp_name,dept_no) AS SELECT empno employee_number, ename, deptno FROM emp WHERE deptno = 30 WITH READ ONLY;

SOL11) CREATE VIEW EMP_DETAILS2_VIEW AS SELECT * FROM S_12345_EMP WHERE DEPT_ID < 15 WITH READ ONLY CONSTRAINT EMP_DETAILS2_VIEW_READ_ONLY;

Q12. Try inserting a record to sales_dept_emp_vw _2

INSERT INTO EMP_DETAILS2_VIEW  (ID, LAST_NAME, FIRST_NAME,USERID) VALUES (123,’TEST’,’TEST1’,’3ABC’);

Q13. Create a Complex View Create or replace view sales_dept_emp_vw_3 as SELECT a.empno, a.ename, a.deptno, b.dname, b.loc FROM emp a, dept b WHERE a.deptno = b.deptno AND b.dname='SALES';

SOL13)

Complex views contain subqueries that

A)Retrieve rows from multiple base tables.

B)Group rows using a GROUP BY or DISTINCT clause.

C)Contain a function call.

CREATE VIEW EMP_DETAILS_VIEW3 AS SELECT FIRST_NAME, AVG(SALARY) AVERAGE_SALARY FROM S_12345_EMP WHERE SALARY > 10000 GROUP BY DEPT_ID,FIRST_NAME HAVING AVG(SALARY) > 5000 ORDER BY DEPT_ID;

Q14. Try inserting a record to sales_dept_emp_vw_3.

SOL14)

INSERT INTO EMP_DETAILS_VIEW3(FIRST_NAME) VALUES (‘BHAVISHYA’);