Null Values in SQL Group By

SQL Having

SQL Group By

Examples of SQL Group By and SQL Having

It is known that Null <> Null. When two different NULL values are compared, the result is NULL (not TRUE), i.e. the two NULL values are not considered to be equal. Applying the same rule to the GROUP BY clause would force SQL to place each row with a NULL grouping column into a separate group by itself.

But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.

Simply put if the grouping column contains more than one null value, the null values are put into a single group.

For example, the grouped query:

SELECT A, B, SUM(amount_purchased) AS 'C'
FROM customers
GROUP BY A, B
ORDER BY A, B

will display a results table similar to

A	B	C
NULL	NULL	61438.0000
NULL	101	196156.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	181632.0000

for CUSTOMERS that contain the following rows.

A	B	amount_purchased
NULL	NULL	45612.00000
NULL	NULL	15826.00000
NULL	101	45852.0000
NULL	101	74815.0000
NULL	101	75489.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	96385.0000
LA	NULL	85247.0000

Examples of SQL Having & SQL Group By

SQL Having

SQL Group By

Null in SQL Group By

Some of the examples found on the Internet:

Find the average sales price for each beer.

Sells (bar, beer, price)

SELECT beer, AVG (price)
FROM Sells
GROUP BY beer;

Find, for each drinker, the average price of Bud at the bars they frequent.

Sells (bar, beer, price)
Frequents (drinker, bar)

SELECT drinker, AVG (price)
FROM Frequents, Sells
WHERE beer = 'Bud' AND
Frequents.bar = Sells.bar
GROUP BY drinker;

Find the average price of those beers that are either served in at least 3 bars or manufactured by Anheuser-Busch.

Beers (name, manf)
Sells (bar, beer, price)

SELECT beer, AVG (price)
FROM Sells
GROUP BY beer
HAVING
COUNT (*) > = 3 OR
beer IN(

SELECT name
FROM Beers
WHERE manf = 'Anheuser-Busch'

);

SQL Inline Views

SQL Modifying View

SQL Join View

SQL Views

It is a subquery that appears in the From clause of the Select statement. The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name. This subquery is enclosed in parenthesis and may be given an alias name. The columns selected in the subquery can be referenced in the parent query.

As an example let us suppose that we want to select first 3 employees hired by the company.



When considering using an inline view, ask the following questions:

* What value does the inline view add to the readability and, more importantly, the performance of the containing query?
* How large will the result set generated by the inline view be?
* How often, if ever, will I have need of this particular data set?

In general, using an inline view should enhance the readability and performance of the query, and it should generate a manageable data set that is of no value to other statements or sessions; Most common usage of an Inline View is in “TOP-N” analysis

SQL Join View

SQL Modifying View

SQL Views

SQL Inline View

A join view is a view which is based on more than one base table. Any insert, update or delete statement on a view can update only one of the base tables involved in the view. A table is called a key preserved in a join view, if the primary and unique keys are unique on the views result set.

Sql> create view t1_view as select a.empno, a.ename, a.deptno, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
View created.

Sql>desc t1_view;

Name	Null?	Type
EMPNO	NOT NULL	NUMBER(4)
ENAME		VARCHAR2(10)
DEPTPNO		NUMBER(2)
DNAME		VARCHAR2(14)

Sql> select * from t1_view;

EMPNO	ENAME	DEPTNO	DNAME
7369	SMITH	20	RESEARCH
7499	ALLEN	30	SALES
7521	WARD	30	SALES
7566	JONES	20	RESEARCH
7654	MARTIN	30	SALES

5 rows selected.

In the above view table emp is key preserved because primary key of the emp is also unique in the view. The dept table is not key preserved as it primary key deptno is duplicated many times.
You can update only the key preserved table through the view.

If the view is defined with the WITH CHECK OPTION then you cannot update columns that join the base tables.

Insert statements also cannot refer to any column of the non key preserved table. If a view is created with “WITH CHECK OPTION” , then no insert is permitted on view.

Modifying SQL Views (Insert, Update and Delete on SQL Views)

SQL Views

SQL Join View

SQL Inline View

You can use the “OR Replace” option. If the view exists it will be replaced with the new definition or a new view will be created. We can use Create or Replace option to create views instead of dropping the view and recreating it, as with this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.

The view will become invalid whenever the base table is altered. We can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed. On recompiling the dependent objects become invalid.

ALTER VIEW View-Name COMPILE ;

Use Drop View statement to drop a view.

SQL> drop view emp_info;<>View dropped.

Insert, Delete and Update on Views

When you update a view oracle will update the underlying base table. You can use DML operations on a view if the view does not include Group by, Start with, Connect by, Distinct clauses or any subqueries and set operations(Union, union all, intersect and minus).

Sql> create table t1(name varchar2(10), id number);
Table created.

Sql> select * from t1;
no rows selected

Sql> create view t1_view as select * from t1;
View created.

Sql> insert into t1_view values('a',1);
1 row created.

Sql> select * from t1;

NAME	ID
a	1

Sql> select * from t1_view;

NAME	ID
a	1

You can check whether the particular columns in a view are updateable or not:

For the t1_view created above we can check like this

Sql> select * from user_updatable_columns where table_name = 'T1_VIEW';

OWNER	TABLE_NAME	COLUMN_NAME	UPD	INS	DEL
SCOTT	T1_VIEW	NAME	YES	YES	YES
SCOTT	T1_VIEW	ID	YES	YES	YES

Another example of non updatable view

Sql> create view t1_secret as select name, id * 2 "secret id" from t1;
View created.
SQL> desc t1_secret;

Name	Null?	Type
NAME		VARCHAR2(10)
Secret ID		NUMBER

Sql> select * from user_updatable_columns where table_name = 'T1_SECRET';

OWNER	TABLE_NAME	COLUMN_NAME	UPD	INS	DEL
SCOTT	T1_SECRET	NAME	YES	YES	YES
SCOTT	T1_SECRET	SECRET ID	NO	NO	NO

WITH CHECK OPTION

Suppose we have a table t1

Sql> select * from t1;

NAME	ID
a	1
b	2
c	3
d	4
e	5

We will create a view t1_view on table t1:

Sql> create view t1_view as select name, id from t1 where id > 3;
View created.
Sql> desc t1_view;

Name	Null?	Type
NAME	NOT NULL	VARCHAR2(10)
ID		NUMBER

Sql> select * from t1_view;

NAME	ID
d	4
e	5

Now if we insert any row in the table through view with id less than or equal to 3 it will let us insert that.

Sql> insert into t1_view values('g',0);
1 row created.
Sql> select * from t1;

NAME	ID
a	1
b	2
c	3
d	4
e	5
g	0

6 rows selected.

Now we will use With Check option to create that view again:

Sql> create or replace view t1_view as select name, id from t1 where id > 3 with check option;
View created.

Now we will not be able to insert any row less than or equal to 3, only greater than 3 so as to match the view definition.

Sql> insert into t1_view values('k',1.5);
insert into t1_view values('k',1.5)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
Sql> insert into t1_view values('k',6);
1 row created

SQL Scalar subquery

SQL inline View subquery

SQL Correlated subquery

SQL Subqueries

A scalar subquery returns a single row and a single column value.

Example 1 of Scalar subquery

SELECT last_name, job_id, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees);

Example 2 of Scalar subquery

Select dname, (select count(*)
             from emp
             where emp.deptno= dept.deptno) cnt
from dept

SQL Subqueries can be single row, multiple rows, single column and multiple columns.

You can use single-row subqueries with single-row operators and multiple-row subqueries with multiple-row operators.

Example of Single Row Subquery

Find the highest salary :

SELECT last_name, salary
FROM employees
WHERE salary = (Select MAX(salary) FROM employees);

Example of Multiple Row Subquery

SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT Min(salary)
               FROM employees
               GROUP BY department_id);

Example of single column subquery

A query to retrieve the name of highest paid person in the department 30

Please see that ALL is used with comparison operator because subquery is not returning single value, It is returning multiple values.

Select first_name, last_name
from employee
where dept_id =30 and salary >=ALL (Select salary
                                    from employee
                                    where dept_id = 30);

Example of Multiple column subquery

A subquery that compares more than just one column between the parent query and the subquery is called multiple-column subquery.

Extract employees that make the same salaries as other employee with employee_id 420 with the same job

Select employee_ID, last_name, job_id, salary
from employees
where (job_id, salary) in (select job_id, salary
                           from employees
                           where employee_id =420);

SQL Correlated Subquery

SQL inline View subquery

SQL Subqueries

SQL Scalar subquery

When you reference a column from the table in the parent query in the subquery, it is known as a correlated subquery. For each row processed in the parent query, the correlated subquery is evaluated once.

While processing Correlated subquery:

• The first row of the outer query is fetched.
  
• The inner query is processed using the outer query’s value or values.
  
• The outer query is processed using the inner query’s value or values.
  
• This process is continued until the outer query is done.
  

Example 1 of Correlated subquery

Display all employees who have switched jobs at least twice.

SELECT e.last_name, e.salary, e.job_id
FROM employees e
WHERE 2 <= (SELECT Count(*) 
            FROM job_history j 
            WHERE j.employee_id = e.employee_id); 

Example 2 of Correlated subquery

SELECT S.Number, S.Name
FROM Salesman S
WHERE S.Number IN(SELECT C.Salesman
                 FROM Customer C
                 WHERE C.Name = S.Name);