ORACLE-SQL

 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...