Skip to main content

Posts

Joins in SQL

                                          JOINS ➤ INNER JOIN :-  This join returns when there is at least one match in both the Tables. Example: SELECT * FROM TABLE1 T1 INNER JOIN TABLE T2 ON T1.COL1=T2.COL1; ➤ OUTER JOINS ➧Left Outer Join:-  This join returns all the rows from the left table in conjunction with matching rows from the right table.If there are no columns matching in right table it returns, NULL values. Example: SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON  T1.COL1=T2.COL1; ➧Right Outer Join:-  This join returns all the rows from the right table in conjunction with matching rows from the left table.If there are no columns matching in left table, it return NULL values. Example: SELECT * FROM TABLE1 T1 RIGHT OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1; ➧Full Outer Join:- This join combines left outer join and right outer join.It returns row from  either table when the condition are met and return null values when there is no match
Post a Comment
Read more
Recent posts

String Functions

                                      STRING FUNCTIONS ➤ Low er :- This function will return character,with all letter in lower case.                              Example: SELECT LOWER('ANKIT') FROM DUAL; NOTE:- " DUAL is demo table in SQL which contain one Row and one Column. " ➤ INITCAP:- This function will return string with the first letter in upper case.                    Example:SELECT INITCAP('ANKIT') FROM DUAL; ➤ UPPER:- This function will return,with all leter to uppercase.      Example:SELECT UPPER('Ankit') FROM DUAL; ➤ SUBSTR:- This function will return the selected charcter from the string or a portion of charcter by mentioning the position of the character in the string.The starting charcter will be 'm' and exceeding upto 'n' result upto the end character.      Example:SELECT SUBSTR('SECURE',2,4)   FROM DUAL; ➤ LTRIM:- This function will remove character from the left in th
Post a Comment
Read more

Aggregate And Numeric Functions.

                   Aggregate And Numeric Functions SQL has many built-in functions for performing processing on string or numeric data. ➥ AGGREGATEE FUNCTIONS:-    ➧ Average function : Returns average value of 'n', ignoring null values.         Example: SE LECT AVG(sell_price) from product;      ➧ COUNT  :Counts rows in a specified table or view.        Example: SELECT    COUNT ( * ) FROM products ;     ➧ MIN  : G ets the minimum value in a set of values.         Example: SELECT MIN ( unitsinstock ) FROM   products ;     ➧ MAX  : Gets the maximum value in a set of values.         Example: SELECT    MAX ( unitsinstock ) FROM    products ;     ➧ SUM  :Ca lculates the sum of values       Example:SELECT  SUM(bal_due) from client_master;         ➥ NUMERIC FUNCTIONS:- ABS(number) Returns the absolute positive value of an expression. Syntax : ABS(expression) Example : SELECT ABS(-1.0), ABS(0.0), ABS(1.0) Output : 1.0   .0   1.0 CEIL(numb
Post a Comment
Read more

Constraints

                                    CONSTRAINTS Constraints is a rule which enforce entry of only valid data in table. Constraints prevent the deletion of a table if there are dependencies. Types of Constraints:-   ➥ PRIMARY KEY= (unique + not null)   ➥ FOREIGN KEY   ➥ NOT NULL   ➥ UNIQUE   ➥ CHECK ➧PRIMARY KEY:- Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.  ➧FOREIGN KEY:- Specifies that the values in the column must correspond to values in a referenced primary key or unique key column. ➧NOT NULL:- Specifies that this column cannot hold NULL values. ➧UNIQUE:- Specifies that values in the column must be unique. Column can contain NULL value. ➧CHECK:- Specifies Domain for Column values. A CONSTRAINT can be one of the following: * A column-level constraint * A table-level constraint ➮ Primary Key: SQL> CREATE TABLE EMP(ID NUMBER(3) PRIMAR
Post a Comment
Read more

DML And TCL Queries

DML(Data Manipulation Language)/TCL(Transaction Control Lanuage) DML .  DML  is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.                 ➤INSERT ➤UPDATE ➤DELETE ➤MERGE NOTE:- (ORACLE DO NOT FIRE AUTOCOMMIT FOR ANY DML STATEMENT)  TCL :- TRANSACTION CONTROL LANGUAGE       ➤COMMIT       ➤ROLLBACK       SQL> SELECT * FROM TAB; TNAME                          TABTYPE  CLUSTERID                                ------------------------------ ------- ----------                                COUNTRIES                          TABLE                                             DEPARTMENTS                    TABLE                                             DEPT                                       TABLE                                       
Post a Comment
Read more