Rahul Thosar

  Ø  Stored Procedures  :- l  Stored procedures are the set of command applied for the database. Inother words stored procedures are the precomplied collection of sql statement . l  Why do we use procedures? 1.  Stored procedures are reusable. 2.  It executed faster . l  How to create stored procedures ?(connect-architecture) Step to follow 1.  Go to server explores. 2.  Right click in the stored procedures folder then click on add new procedures.   n  Syntax :- CREATE PROCEDURE NAME AS BEGIN   //QUERY LOGIC   END;   EX:- CREATE   PROCEDURE  MYPRO AS BEGIN   SELECT   *   FROM  STUDENT_DETAIL ;   END ;   l  How to run the procedure (we need query editor ) Syntax:- EXEC   PROCEDURENAME ;                           OR EXECUTE  ...

  ²  Data base virtual result :-   1.  Virtual table is a predefined table in a database. Ex:- SELECT  10 + 20 - 30 + 40 AS  result ; SELECT   CAST ( 10.5 AS   INT   )   AS  result ;   SELECT   TOP  2 *   FROM  STUDENT_DETAIL ;   SELECT   *   FROM  STUDENT_DETAIL WHERE  ROLL IS   NULL;   SELECT   *   FROM  STUDENT_DETAIL WHERE  ROLL IS    NOT   NULL;     SELECT  BRANCH +   ''   +   NAME   AS   'STUDENT NAME'   FROM  STUDENT_DETAIL ;    

  l  Database joining :- 1.  Joining is a special type of operation perform between 2 tables. 2.  With the help of joining we get the result between multiple data. l  Joining property:- 1.  Common field (column) 2.  Using table object Syntax :-  table name.column name 3.  To activate joining operaton we need  ON  KEYWORD. ü  TYPES OF JOINING:- 1.  INNER JOIN  :- Inner join returns all records from multiple data where the join condition is equal. Ex:- CREATE   TABLE  invent   (  id INT   ,   name   VARCHAR ( 20 )); INSERT   INTO  invent values ( 101 , 'rahul' ),( 103 , 'thosar' ),( 102 , 'asus' );   CREATE   TABLE  product   ( id INT   ,  cost int );   INSERT   INTO  product VALUES ( 201 , '1000' ),( 202 , '2000' ),( 203 , '30000' );   SELECT  invent . id ,  invent . name ,  product . cost FROM  invent INN...

  l  Union  , Intersect , Union all:- 1.  Union :- it is used to remove duplicate row between 2 table Ex:- CREATE   TABLE  undb1   ( id INT   ,   name   VARCHAR ( 50 ));   INSERT   INTO   undb1 VALUES ( 101 , 'anand' ),( 102 , 'swarup' ),( 103 , 'babu bhaiya ' ),( 104 , 'police' ),( 105 , 'chota don' );   Second table CREATE   TABLE  undb2   ( id INT   ,  cost INT   ); INSERT   INTO  undb2 VALUES ( 101 , 2000 ),( 102 , 3000 ),( 201 , 2500 ),( 202 , 3000 ),( 203 , 6000 );     SELECT  id   FROM  undb1 UNION   SELECT  id FROM  undb2 ;     SELECT  id   FROM  undb1 intersect    SELECT  id FROM  undb2 ;     SELECT  id   FROM  undb1 UNION   ALL     SELECT  id FROM  undb2 ;  

  l  DataBase Constraint:- I.  Data base constraint are the rules apply for the column in the database. l  Types od database constraint:- 1.  Primary key constraint 2.  Unique key constraint 3.  Forigen key constraint 4.  Not null constraint 5.  Defult constraint 6.  Check constraint :- Syntax :- create table tablename(columnname datatype , CONSTRAINT name CHECK (CONDITION) ); Ex:- CREATE   TABLE  QDB ( ID INT   ,  AGE INT    CONSTRAINT   RST   CHECK ( Age > 20 ));   l  How to apply constraint in existing table? Ø  Syntax:- ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME CHECK (COND); EX:- CREATE   TABLE  RK   ( ID INT   ,  AGE INT   ); ALTER   TABLE  RK ADD   CONSTRAINT  MY CHECK ( AGE > 20 ); INSERT   INTO  RK VALUES ( 2 , 265 ); l  HOW TO REMOVE CONSTRAINT? Ø  SYNTAX:- ALTER TABLE TABLENAMR DROP C...