SEQUENCE

SEQUENCE: A sequence is an object in oracle, which is used to generate a series of numbers. It can generate unique sequential numbers for using in a primary key column of a table.

 

CURRVAL and NEXTVAL are called pseudo columns. CURRVAL returns the current value of a sequence. NEXTVAL increments the sequence and then returns the next value. A sequence can be modified using ALTER statement.

 SQL> ALTER SEQUENCE SS MAXVALUE 99999;

 

SQL> DROP SEQUENCE SS;

LOCK TABLE

 LOCK TABLE : The LOCK TABLE command is used to prevent concurrent processes from changing a table or from using it.

 The two Locking modes are

1)IN SHARE MODE: In which concurrent processes are allowed to perform only read-only operations.
2)IN EXCLUSIVE MODE:Prevents concurrent processes from performing any operation the table.

 Only the Owner of the table, DBA, or a user having ALTER, DELETE, INSERT, SELECT, UPDATE can lock a table. A table can have multiple SHARE LOCKS but only one EXCLUSIVE LOCK

 

Example: For obtaining EXCLUSIVE LOCK we write.

 SQL> LOCK TABLE STUD in EXCLUSIVE MODE;

 SQL> LOCK TABLE STUD in EXCLUSIVE MODE NOWAIT;

If we try to obtain a lock using the first statement then Oracle waits if the table is not available for locking. In second statement it returns immediately.

Example: For obtaining SHARE LOCK we write.

SQL> LOCK TABLE STUD in SHARE MODE; --- to obtain shared mode lock

HAVING Clause

A HAVING clause in SQL is an optional clause of the SELECT statement, it only returns row where aggregate values meet the specified conditions. The HAVING clause is used to filter the data on the group row but not on the individual row. 

Note : HAVING clause filter group of rows while WHERE clause filters individual rows.

Syntax:

SELECT Column1, Column2,
Aggregate_function(expression1)
  FROM Table_Name
[WHERE Clause]
GROUP BY Column1, Column2
HAVING Having_Condition;

Example:
Table Name “EMPLOYEE”
ID
NAME
ADDRESS
DEPARTMENT
SALARY
101
Rajesh
Ghaziabad
IT
10000
102
Swati
Delhi
Non IT
8000
103
Manoj
Noida
IT
15000
104
Sachin
Noida
IT
12000
105
Swati
Delhi
Non IT
8000
·         Write a query to find Department where total salary is greater than 20000.
SELECT DEPARTMENT,
           SUM(SALARY) as TOTAL_SALARY,
           COUNT(ID) as NO_OF_EMP
  FROM EMPLOYEE
GROUP BY DEPARTMENT
HAVING SUM(SALARY) > 20000;
Output of the above query will be:

DEPARTMENT
TOTAL_SALARY
NO_OF_EMP
IT
37000
3
·         Write a query to find the address where more than one employee.
SELECT ADDRESS, COUNT(ID) as NO_OF_EMP
  FROM EMPLOYEE
GROUP BY ADDRESS
HAVING COUNT(ID) > 1;
Output of the above query will be:

ADDRESS
NO_OF_EMP
Noida
1
Delhi
2
·         Write a query to find out duplicate name Employee.
SELECT NAME, COUNT(ID)
  FROM EMPLOYEE
GROUP BY NAME
HAVING COUNT(ID) > 1;
Output of the above query will be:

Name
NO_OF_EMP
Swati
2

Group By Clause

Group by clause is used with the select statement to arrange identical data into groups. The Group by clause returns one row per group.

The GROUP BY clause is often used with aggregate functions such as AVG(), COUNT(), MIN(), MAX() and SUM(). In this case, the aggregate function returns the summary information per group.

Syntax:

SELECT Column1, Column2, Aggregate_function(expression1)

  FROM Table_Name

[WHERE Clause]

GROUP BY Column1, Column2;

Note: Aggregate_functions are MIN, MAX, SUM, Count, AVG etc.

Example:

Table Name “EMPLOYEE”

ID	NAME	ADDRESS	DEPARTMENT	SALARY
101	Rajesh	Ghaziabad	IT	10000
102	Swati	Delhi	Non IT	8000
103	Manoj	Noida	IT	15000
104	Sachin	Noida	IT	12000
105	Swati	Delhi	Non IT	8000

·         Write a query to find Department wise total salary and number of employee

SELECT DEPARTMENT,

            SUM(SALARY) as TOTAL_SALARY,

            COUNT(ID) as NO_OF_EMP

  FROM EMPLOYEE

GROUP BY DEPARTMENT;

Output of the above query will be:

DEPARTMENT	TOTAL_SALARY	NO_OF_EMP
IT	37000	3
Non IT	16000	2

·         Write a query to find how many employees in an address.

SELECT ADDRESS, COUNT(ID) as NO_OF_EMP

  FROM EMPLOYEE

GROUP BY ADDRESS;

Output of the above query will be:

ADDRESS	NO_OF_EMP
Ghaziabad	1
Delhi	2
Noida	2

·         Write a query to find out number of employee with the same name.

SELECT NAME, COUNT(ID)

  FROM EMPLOYEE

GROUP BY NAME;

Output of the above query will be:

Name	NO_OF_EMP
Rajesh	1
Swati	2
Manoj	1
Sachin	1

Update Statement

The UPDATE statement in SQL is used to modify the existing data of a table in database. We can update single columns as well as multiple columns and single record as well as multiple records using UPDATE statement as per our requirement.

Syntax:

UPDATE table_name

      SET column1 = value1, column2 = value2,...

WHERE condition;

table_name: name of the table

SET: Set statement is used to set new values to the particular column.

column1: name of first, second, third column....

value1: new value for first, second, third column....

WHERE: WHERE clause is used to select the rows for which the columns are needed to be updated. If we have not used the WHERE clause then the columns in all the rows will be updated.

condition: condition to select the rows for which the

values of columns need to be updated.

Example:

ID	NAME	ADDRESS	DEPARTMENT
101	Rajesh	Ghaziabad	IT
102	Swati	Delhi	Non IT
103	Manoj	Noida	IT
104	Sachin	Noida	IT

•          Update address to Delhi of ID 104:

SCRIPT:

    UPDATE EMPLOYEE

         SET ADDRESS = ‘Delhi’

    WHERE ID = 104;

Now EMPLOYEE table have following records:

ID	NAME	ADDRESS	DEPARTMENT
101	Rajesh	Ghaziabad	IT
102	Swati	Delhi	Non IT
103	Manoj	Noida	IT
104	Sachin	Delhi	IT

• Update all employee address to Delhi:

SCRIPT:

    UPDATE EMPLOYEE

         SET ADDRESS = ‘Delhi’;

Now EMPLOYEE table have following records:

ID	NAME	ADDRESS	DEPARTMENT
101	Rajesh	Delhi	IT
102	Swati	Delhi	Non IT
103	Manoj	Delhi	IT
104	Sachin	Delhi	IT

•          Update address to Delhi of ID 104 and also change Department to “Non IT”:

 SCRIPT:

   UPDATE EMPLOYEE

         SET ADDRESS = ‘Delhi’,

                DEPARTMENT = ‘Non IT’

   WHERE ID = 104;

Now EMPLOYEE table have following records:

ID	NAME	ADDRESS	DEPARTMENT
101	Rajesh	Ghaziabad	IT
102	Swati	Delhi	Non IT
103	Manoj	Noida	IT
104	Sachin	Delhi	Non IT