ORACLE-SQL

1. When we have to same task everyday then instead of writing queries we can save the query in procedure and then just run it when required. 2. Named PLSQL program 3. Permanently saved in DB Features :  1. Procedures executed explicitly. Execute Command               Exec Procedurename (arg1, arg2...); 2. Procedures can also executed  implicitly. Using procedured calling statement          Procedurename(arg1, arge2...); 3. Saved in a DB as pre compiled obbject. Complied once only when there is changes in logic of SP then again it will complied. Can call procedure from other proc, func, programs. 4. Procedure can be static or dynamic mean proc may or maynot take arguments. 5. By default proc should not return any value. Types of Proc:  1. Static : Not having arg 2. Dynamic : With arg Syntax : Create or replace procedure procedure_name( arguments ..) is/as  ----------any keyword use declaration  ----...

 Categories of widow function 1. Aggregated fun : avg, sum , max, min , count 2. Ranking func :  Rank, dense_rank, row_number 3. Analytical func : Lead, Lag, first_value, last_value Lag : Return previous row and depends on offset LAG ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause ) Lead : Return next row and depends on offset. LEAD( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )

  Define based on business rulex. 1.        Not Null : a.   Doesnot allow any   Null values. b.    Can be define at column level only.   2.          Default : a.        Filled with default value if we donot provide any value b.       There is no restriction for the no of default value in one table. 3.         Check : a.        Allows only a valid range of values b.       Can be define at column level or table level. c.        Can be define at one column only ( not more than one column) d.       Cannot be define on composite key 4.        Unique : a.        Doesnot allow duplicate value b.     ...

  Aggegation of NULLS                                                               

 View is a database object.  It doesnot contain any data within the view itsel;f. It always brig the data from base tables. View also known as Stoed select statement.  Simple view  Complex view  Force view  Read only view  View with check options Materialised view is a different object. Grant create view to user ----It will grant the acces to user to create a view. View name cannot be duplicate Object should also be different. If view doesnot store any data then how it bring the data.  Select * from view1;  Select * from ( select text from user_views where view_name=’view1’)  Select * from ( select * from employee_table) This is how it works internally. It check the view in Data dictionary user_views then change  the exaxt slect statemtn that used while creating view.] View always bring current data from base tables. SQL> select * from tab; TNAME                          TABTYPE...