VIEWS IN SQL
Can be created from a single table,multiple tables or another view
Example:, it can be used for security. If you have a "customer" table, you might want to give all of your sales
people access to the name, address, zipcode, etc. fields, but not credit_card_number. You can create a view that only
includes the columns they need access to and then grant them access on the view.
Result table=base table
Employeeinfo table
CREATING VIEW
CREATE VIEW vi_name AS SELECT emp_id,f_name,l_name FROM employeeinfo;
DESC vi_name;
SELECT * FROM vi_name;
CREATING A VIEW WITH CHECK OPTION
While selecting data it doesn’t display any NULL values;
While updating and inserting ,it ensures that the given condition is satify or not.
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;
CREATING A VIEW WITH READ ONLY-DENYING DML OPERATION
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
CREATE VIEW v3_name AS SELECT emp_id,address FROM employeeinfo;
INSERT INTO v3_name(emp_id,address) VALUES(7,'Chennai');
SELECT * FROM v3_name;
DELETING INTO VIEW
DELETE FROM v3_name WHERE emp_id=7;
MODIFYING INTO VIEW
Create new view or Replace existing view
CREATE OR REPLACE VIEW v3_name AS SELECT emp_id,address,f_name,l_name FROM employeeinfo;
SELECT * FROM v3_name;
DROP VIEW
Drop view v3_name;
SEQUENCE IN SQL
Sequence of numeric values automatically generated.
Mainly used to create primary key value.
SEQUENCE CREATION
CREATE SEQUENCE s2_name INCREMENT BY 1 START WITH 100 MAXVALUE 105;
CREATE TABLE seqe(emp_id NUMBER(4),pro_id NUMBER(4));
ALTER TABLE seqe ADD CONSTRAINT c_pk PRIMARY KEY(emp_id);
INSERTING WITH NEXTVAL
Used before CURRVAL.
Returns a next available sequence value.
INSERT INTO seqe (emp_id,pro_id) VALUES (s2_name.NEXTVAL,5);
SELECT * FROM seqe;
INSERT INTO TABLE AFTER SEQUENCE MAX VALUE
INSERT INTO seqe (emp_id,pro_id) VALUES (s2_name.NEXTVAL,7);
CURRVAL
SELECT s3_name.CURRVAL FROM seqee;
GAPS IN SEQUENCE
Rollback occurs.
System Crashes.
Sequence Used in another table.
CHECKING A SEQUENCE
SELECT sequence_name,min_value,max_value,increment_by,last_number FROM user_sequences;
ALTERING A SEQUENCE
1) We cannot alter starting sequence number.
ALTER SEQUENCE s3_name INCREMENT BY 2 START WITH 100 MAXVALUE 110;
ALTER SEQUENCE s3_name INCREMENT BY 2 MAXVALUE 110;
INSERT INTO seqee (emp_id,pro_id) VALUES (s3_name.NEXTVAL,9);
SELECT * FROM seqee;
DROP SEQUENCE
DROP SEQUENCE s3_name;
CONDITIONAL EXPRESSION
Use IF-THEN-ELSE logic
Two methods
1) CASE expression
2) Decode expression
CASE EXPRESSION
1) Searches for first WHEN and THEN pair
2) If no condition satisfies go to else part.
3) No else means,return null.
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.
