Database - MDL, Index, Store procedure & View

DML Commands - Select - Insert - Update - Delete

INSERT command:

INSERT into Student values(101,'Adam',15);

INSERT into Student(id,name) values(102,'Alex');


UPDATE command:

update Student set age=18 where s_id=102;

UPDATE Student set s_name='Abhi',age=17 where s_id=103; 


Delete command:

DELETE from Student;

DELETE from Student where s_id=103;



TCL command


Commit command:

commit;


Rollback command:

rollback to savepoint-name;

Example:- 
INSERT into class values(5,'Rahul');
commit;
UPDATE class set name='abhijit' where id='5';
savepoint A;
INSERT into class values(6,'Chris');
savepoint B;
INSERT into class values(7,'Bravo');
savepoint C;
SELECT * from class;



SQL CREATE VIEW Examples:

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS;

SELECT * FROM CUSTOMERS_VIEW;



Index:

Unique Indexes - 

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes -

CREATE INDEX index_name
on table_name (column1, column2);

Drop Index - 

DROP INDEX index_name;


Create Trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;



Create Store Procedure:

CREATE PROCEDURE uspGetAddress
AS
SELECT * FROM AdventureWorks.Person.Address
GO

EXEC uspGetAddress