Tricky SQL queries

In SQL, what is the difference between count(column) and count(*)?

count(*) counts NULLs and count(column) does not

create table ExampleTable(id1 int, id2 int)

insert ExampleTable values(null,null)

insert ExampleTable values(1,null)

insert ExampleTable values(null,1)

insert ExampleTable values(1,null)

insert ExampleTable values(null,1)

insert ExampleTable values(1,null)

insert ExampleTable values(null,null)

 select count(*),count(id1),count(id2) from ExampleTable

 results 7 3 2

  

It's worth mentioning that if you have a non-nullable column such as ID, then count(ID) will significantly improve performance over count(*)

  

Another minor difference, between using * and a specific column, is that in the column case you can add the keyword DISTINCT, and restrict the count to distinct values:

 Example 

select column_a, count(distinct column_b)

from table

group by column_a

having count(distinct column_b) > 1;

What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?

For example: I have a JOBS table with the column JOB_NUMBER.

How can I find out if I have any duplicate JOB_NUMBERs, and how many times they're duplicated?

Query: 

select column_name, count(column_name)

from table

group by column_name

having count (column_name) > 1;

How can a column with a default value be added to an existing

table

ALTER TABLE {TABLENAME}

 ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}

CONSTRAINT {CONSTRAINT_NAME}

DEFAULT {DEFAULT_VALUE}[WITH VALUES]