Category Archives: Oracle Scripts For Practice

SQL Functions

SQL FUNCTIONS

LOWER function:

  • The lower function converts all letters in the specified string to lowercase.

    Output:SQL_FUNCT1
  • If the all employee names( Ename)and job columns was retrieved from the emp table by the below SQL, the value returned would be lower case only, and others columns data will display the normally.

    Output:SQLFUN2

    UPPER Function:

    • The Oracle UPPER function is used to convert strings to all upper case (i.e. capitals).

      Output:SQLFUN3

      INITCAP Function:

      • INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase.

        Output:SQLFUN4

        CONCAT Function:

        Oracle CONCAT( ) function only allows two arguments — only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using ‘||’.

        Output:SQLFUN5

        SUBSTR Function:

        • The substr functions allows you to extract a substring from a string.

        Syntax:

                   Substr(string,start_position,[length])

        • string is the source string.
        • start_position is the position for extraction. The first position in the string is always 1.
        • length is optional. It is the number of characters to extract. If this parameter is   omitted, substr will return the entire string.

        NOTE:

        • If start_position is 0, then substr treats start_position as 1 (ie: the first position in        the string).
        • If start_position is a positive number, then substr starts from the beginning of the string.
        • If start_position is a negative number, then substr starts from the end of the string and counts backwards.
        • If length is a negative number, then substr will return a NULL value.              Examples:
          • The below Query returns 5 characters starting in position 0.

            Output:SQLFUN6
          • The below query returns 3 characters starting in position 4 in job column from emp table

            Output:SQLFUN7

            LENGTH  Function:

            • The length function returns the length of the specified string.
            • Now we want to select the length of the values in the “ORACLE” column below.

              Output:SQLFUN8

              INSTR Function:

              • The instr function returns the location of a substring in a string.

              Syntax:

              Instr(string1,string2[,start_position[,nth_appearance]])

              • string1 is the string to search.
              • string2 is the substring to search for in string1.
              • start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts backstart_position number of characters from the end of string1 and then searches towards the beginning of string1.
              • nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

              Note: If string2 is not found in string1, then the instr Oracle function will return 0.

              Examples:

              Output: SQLFUN9

               

              Output:SQLFUN10

              Padding Functions LPAD, RPAD:

              LPAD: 

                       The lpad function pads the left-side of a string with a specific set of characters    (when string1 is not null).

               

                The following example left-pads a string with the characters “*” and “.”.

              Output:SQLFUN11

              RPAD:

              The rpad function pads the right-side of a string with a specific set of characters     (when string1 is not null).

               The following example right-pads a string with the characters “*” and ”.”

               

               

              Output:SQLFUN12

              Trim Functions LTRIM, RTRIM, TRIM:

               

              LTRIM:

              The ltrim function removes all specified characters from the left-hand side of a string.The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.

              It actually removes the individual occurrences of ‘x’ and ‘y’, as opposed to the pattern of  ‘xy’.

              Output:SQLFUN13

              RTRIM:

              The rtrim function removes all specified characters from the right-hand side of a string.The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.

              It actually removes the individual occurrences of ‘x’and ‘y’, as opposed to the pattern of ‘xy’.

            • Output:SQLFUN14TRIM:The trim function removes all specified characters either from the beginning or  the ending of a string. The following query removes the character “S” from a string.

              Output:SQLFUN15

              Replace Function:

              The replace function replaces a sequence of characters in a string with another set of characters.The following example replaces occurrences of J with BL:

              Output:SQLFUN16

              Translate Function:

               

              The translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

              Output:SQLFUN17

              ASCII Functions:

              The ascii function returns the NUMBER code that represents the specified character.

              Output:SQLFUN18

              SIGN Function:

              The sign function returns a value indicating the sign of a number.

              Output:SQLFUN19

              ADD MONTHS Function:

              The add_months function returns a date plus n months.

              Output:SQLFUN20

              MONTHS BETWEEN Function:

              The months_between function returns the number of months between date1 and date2.

              Output:SQLFUN21

              NEXT DAY Function:

              The next_day function returns the first weekday that is greater than a date.

              Output:SQLFUN22

              LAST DAY Function:

              The last_day function returns the last day of the month based on a date value.

              Output:SQLFUN23

              ROUND and TRUNC Functions:

              TRUNC and ROUND function looks similar but not exactly.

              ROUND function used to round the number to the nearest while TRUNC used to truncate/delete the number from some position. Some cases both returns same result.

              Output:SQLFUN24

              TO CHAR Function:

              The TO_CHAR function converts a DATETIME, number, or NTEXT expression to a TEXT expression in a specified format. This function is typically used to format output data.

              Output:SQLFUN25

              SYSDATE using different Parameters:

               

              The sysdate function returns the current system date and time on your local database.

              Output:SQLFUN26

              TO NUMBER Function:

              The to_number function converts a string to a number.

              Output:SQLFUN27

               

               

     

Related posts:

GROUP FUNCTIONS

                                            Group Functions

Average, Sum, Distinct, Maximum, Minimum, Count:

Average:

The Avg function returns the average value of an expression.

Output: SQLFUN28

Sum:

 

The SQL SUM function returns the summed value of an expression.

Output:SQLFUN29

Maxmimum and Minimum:

The SQL MAX and MIN function returns the maximum and minimum value of an expression.

Output:SQLFUN30

Count:

The SQL COUNT function returns the number of rows in a query.

Output:SQLFUN31

Group By; Group By with Having; Group By with Where, Having and Order By:

Group By:

The SQL GROUP BY clause can be used in an SQL SELECT statement to collect data across multiple records and group the results by one or more columns.

Output:SQLFUN32

Group By with Having:

  • Oracle GROUP BY HAVING can be used to limit the returned rows after the grouping.  With Oracle GROUP BY HAVING, the HAVING clause is applied after the GROUP BY has taken place.
  • Oracle GROUP BY HAVING will group values that have a particular value. Oracle GROUP BY HAVING can be used in conjunction with other logical functions such as MIN, MAX, COUNT, and SUM.
  • With Oracle GROUP BY HAVING, the HAVING clause filters rows after the grouping with the Oracle GROUP BY clause.An example of Oracle GROUP BY HAVING using the MAX function can be seen below:

    Output:SQLFUN33

    Group By with Where:

    Output:SQLFUN34

    Having clause:

    The SQL HAVING clause is used in combination with the SQL Group By Clause. It can be used in an SQL Select Statement to filter the records that a SQL GROUP BY returns.

    Output:SQLFUN35

    Order By:

    The SQL ORDER BY clause allows you to sort the records in your result set. The SQL ORDER BY clause can only be used in SQL Select Statement.

    Output:SQLFUN36

 

Related posts:

SQL Practice 1

SQL Practice – 1

If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like

 

Output:

SQLC1

If we want only specific columns (as is usually the case), we can/should explicitly specify them in a comma-separated list, as in

 

Output:

SQLC2

We want to select only the distinct values from the column named “deptno” from the emp table

 

Ouput:

SQLC3

We want to select only the employees whose working as “Manager” from the emp  table.

 

Ouput:

SQLC4

We could also use the NOT operator, to fetch those rows that are not  between the specified columns.

 

Ouput:

SQLC5

To get a list of employees who were hired on or after a given date, you would write like this

 

Ouput:

SQLC6

We wanted to see if the employees name is”FORD” and ” ALLEN”, we would write

 

Ouput:

SQLC7

This query retrieves data only from those rows of the emp table where the Comm is NULL.

 

Output:

SQLC8

To select employees  whose name begins with ‘S’

 

Output:

SQLC9

It is also possible to select the employees does NOT contain the pattern “S” from the “emp” table, by using the NOT keyword.

 

Ouput:

SQLC10

If you want to sort the emp table by the name, the query would be like,

 

Ouput:

SQLC11

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below

 

Ouput:

SQLC12

If you want to sort the emp table by the sal+nvl(comm.,0), the query would be like,

 

Ouput:

SQLC13

NOTE:The columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.

You can represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name.

The above query can also be written as given below,

 

Ouput:

SQLC14

Related posts: