Views in SQL
Views in SQL
Views in SQL
Employeeinfo table
CREATING VIEW
DESC vi_name;
CREATE VIEW v2_name AS SELECT address FROM employeeinfo WHERE address IS NOT NULL WITH CHECK OPTION;
(Or)
CREATE VIEW v2_name AS SELECT address FROM employeeinfo WHERE address IS NOT NULL WITH CHECK OPTION
CONSTRAINT ck;
SELECT* FROM v2_name;
CREATE VIEW v3_name AS SELECT emp_id FROM employeeinfo WITH READ ONLY;
UPDATE v3_name SET emp_id=6 WHERE emp_id=7;
SELECT * FROM v3_name;
INSERTING INTO VIEW
DROP VIEW
SEQUENCE IN SQL
SEQUENCE CREATION
CURRVAL
GAPS IN SEQUENCE
Rollback occurs.
System Crashes.
Sequence Used in another table.
CHECKING A SEQUENCE
DROP SEQUENCE
CONDITIONAL EXPRESSION
1) CASE expression
2) Decode expression
CASE EXPRESSION
1)SELECT f_name,l_name,department ,
CASE department WHEN 'HR' THEN 15000
WHEN 'Account' THEN 20000
ELSE 0 END "Salary" FROM employeeinfo;
2)SELECT f_name,l_name,department ,
CASE department WHEN 'HR' THEN 15000
WHEN 'Account' THEN 20000
END "Salary" FROM employeeinfo;
DECODE EXPRESSION
SELECT f_name,l_name,department ,
DECODE (department ,'HR', 15000,
'Account',20000,
0 )salary FROM employeeinfo;
CASE DECODE
Work with logical operators,BETWEEN,LIKE other than ‘=’ Decode performs equality check only.