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’);
