Difference between WHERE vs HAVING in SQL

1) WHERE clause is processed right after FROM clause in the logical order of query processing, which means it is processed before GROUP BY clause while HAVING clause is executed after groups are created. 2) If used in GROUP BY, You can refer any column from a table in WHERE clause but you can only […]

CRUD operation in SQL:

(INSERT): Create table dept: CREATE TABLE departments ( deptno NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13)); Insert a single row into a table: INSERT INTO dept VALUES (10, ‘Branding’, ‘Dhaka’); Insert rows from one table into another table: INSERT INTO emp2 SELECT * FROM emp; CREATE TABLE emp3 AS SELECT * FROM emp; […]

CRUD operation in ORACLE:

What is CRUD operation? Within programming , the acronym CRUD stands for create, read, update and delete. These are the four basic functions of CRUD. Also, each letter within the acronym can ask all functions executed in electronic database applications and mapped to a typical HTTP method, SQL statement or DDS operation. The first regard […]

ORACLE Data Type

DATA TYPE: একটি Table তৈরি করার সময় কলাম সমুহ কোন ধরনের DATA ধারন করবে তা নির্ধারন করে দিতে হয়। নিচে Data Type সমুহের একটি তালিকা প্রদান করা হলঃ Data Type কে কয়েকটি ভাগে বিভক্ত করা যায়ঃ Character Data Type Numeric Data Type Date/Time Data Type Large Object Data Type ROWID Data Type Character Data types […]

GROUP FUNCTIONS

GROUP FUNCTIONS Group functions will be applied on all the rows but produces single output Sum Avg Max Min Count SUM This will give the sum of the values of the specified column. Syntax sum (column) EX SQL> select sum(sal) from emp; SUM(SAL) ———- 38600 AVG This will give the average of the […]

CONVERSION FUNCTIONS

CONVERSION FUNCTIONS Bin_to_num Chartorowid Rowidtochar To_number To_char To_date BIN_TO_NUM This will convert the binary value to its numerical equivalent Syntax bin_to_num( binary_bits) EX SQL> select bin_to_num(1,1,0) from dual; BIN_TO_NUM(1,1,0) ———————— 6 1 If all the bits are zero then it produces zero. 2 If all the bits are null then it produces an error. […]

STRING FUNCTIONS

STRING FUNCTIONS Initcap Upper Lower Length Rpad Lpad Ltrim Rtrim Trim Translate Replace Soundex Concat (‘II Concatenation Operation’ Ascii Chr Substr Instr Decode Greatest Least Coalesce INITCAP (This will capitalize the initial letter of the string) Syntax initcap (string) Ex SQL> select initcap(‘computer’) from dual; INITCAP ———– Computer UPPER (This will convert […]

NUMERIC FUNCTIONS

NUMERIC FUNCTIONS Abs Sign Sqrt Mod Nvl Power Exp Ln Log Ceil Floor Round Trunk Bitand Greatest Least Coalesce ABS (Absolute value is the measure of the magnitude of value. Absolute value is always a positive number) Syntax abs (value) Ex: SQL> select abs(5), abs(-5), abs(0), abs(null) from dual; ABS(5) ABS(-5) […]

Date Function

DATE FUNCTIONS Sysdata Current_data Current_timestamp Systimestamp Localtimestamp Dbtimezone Sessiontimezone To_char To_date Add_month Month_between Next_day Last_day Extract Greatest Least Round Trunc New_time Coalesc Oracle default date format is DD-MON-YY. We can change the default format to our desired format by using the following command. SQL> alter session set nls_date_format = ‘DD-MONTH-YYYY’; But this will […]

SQL