Posts

Cursor in SQL

Cursor holds the pointer to the context Area. It holds the number of rows provided by the SQL statement. Number of rows is called Active Set. There are two types of Cursor 1) Implicit Cursor 2) Explicit cursor(user defined cursor) System automatically creates Implicit cursor. Cursor has attributes : %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT,%BULK_ROWCOUNT %BULK_EXCEPTIONS etc  Explicit cursor has following Steps: 1.Declaring cursor for memory initialization 2.Opening the cursor for memory allocation. 3.Fetching the cursor for data retrival  4.Closing the cursor for to release memory.
Read more

Trigger in SQL

Syntax CREATE [OR REPLACE ] TRIGGER Your_Trigger_Name {BEFORE | AFTER | INSTEAD OF }   {INSERT [OR] | UPDATE [OR] | DELETE}   [OF Column_Name]   ON Table_Name   [REFERENCING OLD AS oldd NEW AS neww]   [FOR EACH ROW]   WHEN (your_condition)  DECLARE     your Declaration statements  goes here BEGIN      your Executable statements goes here EXCEPTION    Exception related handling-statements END ; Example CREATE OR REPLACE TRIGGER display_speed_changes BEFORE DELETE OR INSERT OR UPDATE ON  tblm_riders FOR EACH ROW  WHEN (NEW.ID > 0)  DECLARE     speed_diff number; BEGIN     speed_diff := :NEW.speed  - :OLD.speed;    dbms_output.put_line('Old speed: ' || :OLD.speed);    dbms_output.put_line('New speed: ' || :NEW.speed);    dbms_output.put_line('Speed difference: ' ||...
Read more

Procedure in SQL

Syntax CREATE [OR REPLACE] PROCEDURE Your_Procedure_Name [(parameter1_name [IN | OUT | IN OUT] type [, ...])] {IS | AS}  BEGIN     Procedure_Body_Area END ; Example CREATE OR REPLACE PROCEDURE  welcomeMessage AS  BEGIN     dbms_output.put_line('Hello ! welcome to SQL World!'); END ; How to Execute Execute welcomeMessage Hello ! welcome to SQL World! or BEGIN     welcomeMessage END How to Drop procedure DROP PROCEDURE welcomeMessage Points Procedure can call the functions. Function cannot call procedure. Procedure may or may not return values. Try-Catch can be used in procedure. Transaction can be used in procedure. Procedure can take both input as well as output parameters. Procedure can use SELECT statements as well as DML statements also. Procedure is used to take certain actions not only for calculation purpose.
Read more

Function in SQL

Syntax CREATE [OR REPLACE] FUNCTION FunctionName [(Parameter_Name [IN | OUT | IN OUT] type [, ...])] RETURN ReturnType {IS | AS} BEGIN     Function_Body_Area END [function_name]; Example CREATE OR REPLACE FUNCTION totalStudents RETURN number IS     totalStud number(2) := 0; BEGIN      SELECT count(*) into totalStud     FROM Tblm_Students ;        RETURN  totalStud; END ; POINTS Function always returns a value In function only SELECT statement can be used, DML statement cannot be used. Function takes input (IN) parameter only, not output (OUT) parameter try-catch block cannot be used inside function Function be used in the SELECT statement Function can be called from procedures Function can return only one value while procedure can return more. Function purpose is to perform some basic calculations and return the result. Example2 DECLARE     num1 numb...
Read more

QUERY TO SELECT 7TH HIGHEST RECORD FROM TABLE

SELECT * FROM USERS  (   SELECT * FROM USERS  ( SELECT * FROM USERS  ( SELECT * FROM USERS ORDER BY DESC   ) WHERE ROWNUM <=7 ) ORDER BY ASC  ) WHERE ROWNUM = 7 
Read more

What is ACID properties ?

ACID : Atomicity Consistency Isolation Durability  Atomicity :  It is about 'all or nothing'. When the transaction has multiple steps involved then if any one step fail then all the steps should be failed. It should complete fully only. No partial transaction. Consistency :  It is about the state of the database. Any transaction to the database should bring the database from one valid state to another valid state. the data in the tables must be consistent as per the rules and syntax. Isolation :  When multiple transaction are running sequentially then completed transaction data must be available to the upcoming transaction. Durability :   It says that when any transaction commit the data must be available even if the system crash or electric issue or whatever disaster happens. After transaction commit successfully. Data must be available for later on.
Read more

GROUP BY, HAVING

GROUP BY is used with aggregate function to group the result based on one or more columns. SYNTAX SELECT  comma_separated_ column_names FROM   table_name WHERE   condition GROUP   BY   column_names ORDER   BY   column_names; EXAMPLE1 SELECT COUNT( id ) , city FROM users GROUP BY city Note: Above query will retrieve count of total number of users available in each city. EXAMPLE2 SELECT count( orders.order_id ) Total Orders, username  FROM users  LEFT JOIN orders ON orders.user_id=users.id  GROUP BY username Note: Above query will retrieve total number of orders made by each user. HAVING It is used to provide condition with the group by clause. EXAMPLE1 SELECT COUNT( id ) , city FROM  users GROUP BY  city HAVING COUNT( id ) > 2 Note: Above query will retrieve all the city having minimum two users in it. EXAMPLE2 SELECT count( orders.order_id )  Total Orders, username...
Read more