Using the HAVING Clause and where clause

    
SQL>
SQL> CREATE TABLE SAT (
  2     StudentID  INT NOT NULL,
  3     ExamID     INT NOT NULL,
  4     Mark       INT,
  5     IfPassed   SMALLINT,
  6     Comments   VARCHAR(255),
  7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created.

SQL>
SQL>
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,'Satisfactory');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,'Good result');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,'Hard');

1 row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1);

1 row created.

SQL>
SQL>
SQL> SELECT StudentID, AVG(Mark) AS AverageMark
  2  FROM SAT
  3  WHERE ExamID IN ( 5, 8, 11 )
  4  GROUP BY StudentID
  5  HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;

no rows selected

SQL>
SQL>
SQL>
SQL>
SQL> drop table SAT;

Table dropped.

Related examples in the same category

1.	Example using the MAX function with having clause
2.	Using the HAVING Clause
3.	Any conditions based on the outcome of a group function must be in the HAVING clause
4.	Using the SUM function in HAVING Clause
5.	Using HAVING with an Analytical Function
6.	Sub query inside having clause
7.	Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
8.	Use sum in having clause
9.	Using the HAVING Clause with aggregate function
10.	Using avg() function in having clause
11.	Using and, or operator in having clause
12.	Born after '1960-01-01', group by department number with count(*) >= 4;
13.	Using the same condition in having and where

Using the HAVING Clause and where clause : HAVING « Select Query « Oracle PL / SQL

Related examples in the same category