Single Row Function In SQL SERVER

Objectives

After completing this lesson, you should be able to do the following:
• Describe various types of functions available in SQL
• Use character, number, and date functions in SELECT statements
• Describe the use of conversion functions.


SQL Functions


Two Types of SQL Functions

Single-Row Functions

Single row functions:
• Manipulate data items
• Accept arguments and return one value
• Act on each row returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments which can be a column or an expression.

Character Functions

Case Manipulation Functions
These functions convert case for character strings.

Using Case Manipulation Functions
Display the employee number, name, and department number for employee Higgins:

Character-Manipulation Functions
These functions manipulate character strings:


Using the Character-Manipulation Functions


Number Functions
• ROUND: Rounds value to specified decimal
• TRUNC: Truncates value to specified decimal
• MOD: Returns remainder of division


Using the ROUND Function
DUAL is a dummy table you can use to view results from functions and calculations.

Using the TRUNC Function

Using the MOD Function
Calculate the remainder of a salary after it is divided by 5000 for all employees whose job title is sales representative.


Working with Dates
• Oracle database stores dates in an internal numeric format: century, year, month, day, hours,
minutes, seconds.
• The default date display format is DD-MON-RR.
– Allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year.
– Allowa you to store 20th century dates in the 21st century in the same way.

SYSDATE is a function that returns:
• Date
• Time

Arithmetic with Dates
• Add or subtract a number to or from a date for a resultant date value.
• Subtract two dates to find the number of days between those dates.
• Add hours to a date by dividing the number of hours by 24.

Using Arithmetic Operators with Dates

Date Functions

Basic Select Statement

SELECT Statement

Basic SELECT Statement

Selecting All Columns

Selecting Specific Columns

Writing SQL Statements

• SQL statements are not case sensitive.
• SQL statements can be on one or more lines.
• Keywords cannot be abbreviated or split
across lines.
• Clauses are usually placed on separate lines.
• Indents are used to enhance readability.

Column Heading Defaults

• iSQL*Plus:
– Default heading justification: Center
– Default heading display: Uppercase
• SQL*Plus:
– Character and Date column headings are leftjustified
– Number column headings are right-justified
– Default heading display: Uppercase

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.

Using Arithmetic Operators

Operator Precedence

• Multiplication and division take priority over addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to force prioritized evaluation and to clarify statements.

Using Parentheses

Defining a Null Value

• A null is a value that is unavailable, unassigned,unknown, or inapplicable.
• A null is not the same as zero or a blank space.

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to null.

Defining a Column Alias

A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name: there can also be the optional AS keyword between the column name and alias
• Requires double quotation marks if it contains spaces or special characters or is case sensitive.

Using Column Aliases

Concatenation Operator

A concatenation operator:
• Concatenates columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression.

Using the Concatenation Operator

Literal Character Strings

• A literal value is a character, a number, or a date included in the SELECT list.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for each row returned.

Using Literal Character Strings

Duplicate Rows

The default display of queries is all rows, including duplicate rows.

Eliminating Duplicate Rows

Eliminate duplicate rows by using the "DISTINCT" keyword in the "SELECT" clause.

SQL and iSQL*Plus Interaction

SQL Statements versus iSQL*Plus Commands

Summary

In this lesson, you should have learned how to:
• Write a SELECT statement that:
– Returns all rows and columns from a table
– Returns specified columns from a table
– Uses column aliases to give descriptive columnheadings
• Use the iSQL*Plus environment to write, save, and execute SQL statements and iSQL*Plus commands.

Sql Server Query for Interview

Conversion Functions

Overview

This practice covers the following topics:
• Writing a query that displays the current date
• Creating queries that require the use of numeric, character, and date functions.

Implicit Data-Type Conversion

For assignments, the Oracle server can automatically convert the following:
For expression evaluation, the Oracle Server can automatically convert the following:

Explicit Data-Type Conversion


Using the TO_CHAR Function with Dates

The format model:
• Must be enclosed in single quotation marks and is case sensitive
• Can include any valid date format element
• Has an fm element to remove padded blanks or suppress leading zeros
• Is separated from the date value by a comma.

Elements of the Date Format Model

• Time elements format the time portion of the date.

• Add character strings by enclosing them in double quotation marks.
• Number suffixes spell out numbers.

Using the TO_CHAR Function with Dates


Using the TO_CHAR Function with Numbers

These are some of the format elements you can use with the TO_CHAR function to display a number value as a character:

Using the TO_NUMBER and TO_DATE Functions

• Convert a character string to a number format using the TO_NUMBER function:
• Convert a character string to a date format using the TO_DATE function:
• These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function.

RR Date Format
Example of RR Date Format

Purpose of Database Systems

Database systems arose in response to early methods of computerized management of commercial data. An example of such methods,typical of 1960s,consider part of a bank enterprise that,among other data,keeps information about all customer is to store it in operating system files.To allow users to manipulate the information,the system has a number of applicationprograms that manipulate the files including programs to:

• Debit or credit an account
• Add a new account
• Find the balance of an account
• Generate monthly statements
  

System programmers wrote these application programs to meet the needs of the bank.
New application programs are added to the system as the need arises.For example,suppose that a saving bank decides to offer checking accounts.As a result,the bank creates new permanent files that contain information about all the checking accounts maintained in the bank,and it may have to write new application program to deal with situation that do not arisein saving accounts such as overdrafts.Thus,as time goes by,the system acquires more files and more application programs.
This typical file-processing system is supported by a conventional operating system.The system stores permanent recordsin various files and it need various different application programs to extract records from,and add records to,the appropriate file.Before database management systems(DBMSs) came along,organisation usually store information in such system.
Keeping organizational information in a file-processing system has a number of major disadvantages:

• Data redundancy and inconsistency: Same information may be duplicated in several places. All copies may not be updated properly.

• Difficulty in accessing data: May have to write a new application program to satisfy an unusual request. E.g. find all customers with the same postal code. Could generate this data manually, but a long job...

• Data isolation: Data in different files, Data in different formats Difficult to write new application programs.

• Security problems
• Every user of the system should be able to access only the data they are permitted to see.
• E.g. payroll people only handle employee records, and cannot see customer accounts; tellers only access account data and cannot see payroll data.
• Difficult to enforce this with application programs.

• Integrity problems
• Data may be required to satisfy constraints.
• E.g. no account balance below $25.00.
• Again, difficult to enforce or to change constraints with the file-processing approach.

Restricting and Sorting Data

Objectives

After completing this lesson, you should be able to do the following:
• Limit the rows retrieved by a query
• Sort the rows retrieved by a query

Limiting Rows Using a Selection

Limiting the Rows Selected

• Restrict the rows returned by using the WHERE clause.
• The WHERE clause follows the FROM clause.

Using the WHERE Clause

Character Strings and Dates

• Character strings and date values are enclosed in single quotation marks.
• Character values are case sensitive, and date values are format sensitive.
• The default date format is DD-MON-RR.

Comparison Conditions

Using Comparison Conditions
Other Comparison Conditions

Using the BETWEEN Condition

Using the IN Condition

Using the LIKE Condition
• Use the LIKE condition to perform wild card searches of valid search string values.
• Search conditions can contain either literal characters or numbers:

– % denotes zero or many characters.

– _ denotes one character.
• You can combine pattern-matching characters.
• You can use the ESCAPE identifier to search for the actual % and _ symbols.


Using the NULL Conditions

Test for nulls with the IS NULL operator.

Logical Conditions

Using the AND Operator

AND requires both conditions to be true.

Using the OR Operator

OR requires either condition to be true.

Using the NOT Operator

Rules of Precedence
Override rules of precedence by using parentheses.

Use parentheses to force priority.

ORDER BY Clause

• Sort rows with the ORDER BY clause
– ASC: ascending order (the default order)
– DESC: descending order
• The ORDER BY clause comes last in the SELECT statement.

Sorting in Descending Order

Sorting by Column Alias

Sorting by Multiple Columns
• The order of ORDER BY list is the order of sort.
• You can sort by a column that is not in the SELECT list.

Summary
In this, you should have learned how to:

• Use the WHERE clause to restrict rows of output
– Use the comparison conditions
– Use the BETWEEN, IN, LIKE, and NULL conditions
– Apply the logical AND, OR, and NOT operators