Saturday, 5 May 2018

Difference between Count(*) and Count(Column Name)

 Count(*) vs Count(column_name)

We use count(*) to get the total number of records.

SQL>select count(*) from emp2;

Note:-There is no difference between count(*) and count(1) .

SQL>select count(1) from emp2;

But there is a difference between count(*) and count(column_name) .Count(*) return the total number of records of table and count(column_name) return the count of records that have not null value in that column.

SQL>select count(empno) from emp2;

See the reference screen


SYSDATE in Oracle

SYSDATE

SYSDATE is used to get the system date and time.This return values in format of "nls_date_time"
SQL>SELECT sysdate FROM DUAL;

Note:-We can change the format of nls_date_time for session by below command.

SQL>ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

Now you will get the result in above mentioned format.
 
SQL>SELECT sysdate FROM DUAL;


Below is expression list that can be used in different queries

 Examples:-

Sunday, 29 April 2018

dual in SQL

DUAL in SQL


DUAL:-DUAL is dummy table in Oracle .Owner of dual table is SYS user. 
                This table is used for calculation purpose.

SQL>SELECT * FROM DUAL;
SQL>DESC DUAL;
SQL>SELECT TABLE_NAME,OWNER FROM ALL_TABLES WHERE       TABLE_NAME='DUAL';





Examples:-

SQL>SELECT 2+5 FROM DUAL;
SQL>SELECT SYSDATE FROM DUAL;
SQL>SELECT USER FROM DUAL;
SQL>SELECT 1,2,3,4 FROM DUAL;
SQL>SELECT (SELECT COUNT(*) FROM EMP)+(SELECT COUNT(*) FROM DEPT) TOTAL FROM DUAL;
SQL>SELECT SQRT(144) FROM DUAL;
SQL>SELECT POWER(3,4) FROM DUAL;
SQL>SELECT EXP(3) FROM DUAL;--exponential e*e*e (e=2.718)
SQL>SELECT MOD(17,3) FROM DUAL;
SQL>SELECT ROUND(12.345678,2) FROM DUAL;
SQL>SELECT TRUNC(12.34567,2) FROM DUAL;
SQL>SELECT FLOOR(12.345678) FROM DUAL;
SQL>SELECT CEIL(12.345678) FROM DUAL;
SQL>SELECT 2*6 FROM DUAL;
SQL>SELECT 2*(13-9) FROM DUAL;
SQL>SELECT TRUNC(13.2365,2) FROM DUAL;
SQL>SELECT ROUND(13.2365,2) FROM DUAL;
SQL>SELECT CEIL(13.5674) FROM DUAL;
SQL>SELECT FLOOR(13.5674) FROM DUAL;
SQL>SELECT POWER(3,4) FROM DUAL;
SQL>SELECT SQRT(81) FROM DUAL;





Difference between Count(*) and Count(Column Name)

  Count(*) vs Count(column_name) We use count(*) to get the total number of records. SQL>select count(*) from emp2; Note:-There is...