➤ On a Table Perform WHERE clause, HAVING, GROUP BY, ORDER BY, IN, NOT IN, BETWEEN.
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 |
