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