BCA RDBMS PRACTICAL NUMBER_3

 ➤ On a Table Perform WHERE clause, HAVING, GROUP BY, ORDER BY, IN, NOT IN, BETWEEN.

Steps to perform the given commands one by one mentioned below-

1. Create a table Employee by given commands-

    SQL>CREATE TABLE Employee

            (

                Emp_ID int,

                Emp_Name varchar(20),

                Department varchar(10),

                Salary numeric(10,2)

            );

            Table Created.

2. Insert 5 Records in Employee Table by given commands-

    SQL> INSERT INTO Employee VALUES(1, 'Shreya', 'HR', 12000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(2, 'Tanu', 'Manager', 15000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(3, 'Roshni', 'Caller', 10000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(4, 'Sejal', 'Caller', 10000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(5, 'Manisha', 'Caller', 10000);

            1 row created.

3. Now we can see our table record by given command-

    SQL> SELECT * FROM Employee;

Emp_ID	Emp_Name	Department	Salary
1	Shreya	HR	12000
2	Tanu	Manager	15000
3	Roshni	Caller	10000
4	Sejal	Caller	10000
5	Manisha	Caller	10000

4. Now we can perform the practical on given keywords or clauses-

    A. WHERE Clause- it is used for filtering the record by conditions. 

        For an Example we can filter record of Employee whose Emp_ID is 3 by given query-

        SQL> SELECT * FROM Employee WHERE Emp_ID=3;

Emp_ID	Emp_Name	Department	Salary
3	Roshni	Caller	10000

    B. Having Clause- It is used with GROUP BY Clause for filtering the record.

        Example- we can filter record to count Employees whose department='Caller' by given query.

        SQL> SELECT Department, COUNT(*) as Total_Emp FROM Employee GROUP BY Department HAVING department='Caller';

Department	Total_Emp
Caller	3

    C. Group By Clause- It is used for grouping the data according to given attribute with Aggregate function.

        Example- We can calculate sum of salary grouping by Department by given query.

        SQL>SELECT Department, SUM(Salary) as Salary FROM Employee GROUP BY Department; 

Department	Salary
Manager	12000
Caller	30000
HR	12000

    D. Order By- it is used for fetching record according to ascending or descending column values.

        Example- we can show data in descending order of Emp_ID by given query.

        SQL> SELECT * FROM Employee ORDER BY Emp_ID DESC;

Emp_ID	Emp_Name	Department	Salary
5	Manisha	Caller	10000
4	Sejal	Caller	10000
3	Roshni	Caller	10000
2	Tanu	Manager	15000
1	Shreya	HR	12000

    E. IN - This Logical Operator is used to compare matching record from set of values which is separated by comma.

    Example- We can show data whose Emp_ID in 2,4,5 by given query.

    SQL> SELECT * FROM Employee WHERE Emp_ID IN (2,4,5);

Emp_ID	Emp_Name	Department	Salary
2	Tanu	Manager	15000
4	Sejal	Caller	10000
5	Manisha	Caller	10000

    F. NOT IN- This Logical Operator is used to compare not matching record from set of values which is separated by comma.

    Example- We can show data whose Emp_ID except 2,4,5 by given query.

    SQL> SELECT * FROM Employee WHERE Emp_ID NOT IN (2,4,5);

Emp_ID	Emp_Name	Department	Salary
1	Shreya	HR	12000
3	Roshni	Caller	10000

    G. BETWEEN- This Range Operator is used to select record from given range.

    Example- We can show data whose salary lies between 10000 to 12000 by given query.

    SQL> SELECT * FROM Employee WHERE Salary BETWEEN 10000 AND 12000;

Emp_ID	Emp_Name	Department	Salary
1	Shreya	HR	12000
3	Roshni	Caller	10000
4	Sejal	Caller	10000
5	Manisha	Caller	10000

➤ Practical Screen Shot of Oracle 10g

BCA RDBMS Practical Number 3.1 Screen Shot

BCA RDBMS Practical Number 3.2 Screen Shot

BCA RDBMS Practical Number 3.3 Screen Shot