Sybase Session 3-document

SYBASE SESSION 3-DOCUMENT

___________________________________________________________________________________________________ Copyrights Reserved Page 1 of 12

TRANSACT-SQL PROGRAMMING CONSTRUCTS Chapter Includes: SQL

Server Functions

String

Functions

Mathematical Date

Functions

Functions

System

Functions

Compute

and Compute by

isnull

Function

Local

and Global Variables

Cursors

___________________________________________________________________________________________________ Copyrights Reserved Page 2 of 12

SQL Server Functions

They provide advanced data manipulation through its SQL allows standard arithmetic operators (+ - * /) in both select and data modification statements. 

String functions



Math functions



Date Functions and



System functions

String Functions:

SQL server string functions can be used as a part of any character expression. They allow manipulation, parsing and conversion of character strings. Example: 1. For length and parsing datalength : returns integer number of characters in char_expr, ignoring railing spaces. 2. For basic string Manipulation upper (char_expr) : converts char_expr to upper case.

___________________________________________________________________________________________________ Copyrights Reserved Page 3 of 12

Wildcards: Sql

server provides wildcards to allow pattern matching in text searches. This

is roughly like the matching you use in windows file manager to look for a missing file. There are 3 wildcards %

: Matches any quantity of characters

_

: Matches any single character

Specifies

a range of valid characters.

Syntax: select * from authors where au_name like [Ss ]mith%

Mathematical Functions: SQL server performs standard arithmetic operations using normal precedence. It supports all standard trigonometric functions and a number of other useful ones. Example: 1. abs (numeric_expr) : Returns the absolute value of the specified value. 2. Exp (float_expr) : provides the exponential value of the specified value.

___________________________________________________________________________________________________ Copyrights Reserved Page 4 of 12

Date Functions:

SQL Server includes date functions for performing date parsing and date arithmetic. This is extremely useful for how invoices are. Example: 1. getdate : returns the current system date and time 2. datepart(datepart, date_expr) : returns specified part of date_exp value.

System Functions:

System functions are used to display information about the SQL Server, database or user. These tend to be used a lot by programmers and DBAs but not very often by users. Example: 1. For security and access information host_id : Current fost process ID number of client process. 2. For Database and Object information db_id(['db_name']) : Database ID number. 3. For DatamFunctions datalength (expression) : Returns length of expression in bytes.

___________________________________________________________________________________________________ Copyrights Reserved Page 5 of 12

Compute and Compute by:

The compute and compute by key words allow you to include both detail and summary in a result set. Compute: Compute clause reports overall aggregates for a result set. Syntax : select col_name from table name compute max(col_name) Example : select titles, prices from titles compute max(price)

Compute by: A compute by clause displays subtotals with in a result set, but not totals. Syntax : select col_name from table_name compute max(col_name) by type Example : select type, price from titles order by type compute max(price)by type

___________________________________________________________________________________________________ Copyrights Reserved Page 6 of 12

isnull Function :



Some times when calculating aggregates or printing reports to end users, you want to have null values as if they are something else. For that reason we use the isnull function. It substitutes a specified value for a null value in a column in a query or an aggregate.

Example : select avg( isnull (total_order, $0) ) from invoices

Local Variable:

Local

variables exist only for the life of the batch. When the batch is complete

all the information stored in the local variables. Syntax:

declare @variable_name datatype

Global variables:

Global

variables cannot be defined by users and are not used to pass

information across processors by applicants. Some

uses of global variables are

@@rowcount @@trancount

: Number of rows processed by preceding command. : It shows the transaction nesting level.

Set options: It

affects the way the server handles specific conditions.options exist for the

duration of our connection or for the duration of a stored procedure if used in a ___________________________________________________________________________________________________ Copyrights Reserved Page 7 of 12

stored procedure.

Cursors

By

means of a Cursor, there is an ability to address a set of rows individually, one row at a time. A Cursor is a pointer that identifies a specific working row with in a set. Steps to Declare a Cursor: Declaring

a cursor

Declaring

variables

Opening

cursors

Fetching

cursors

Main

loop

Closing

the cursor

Deallocating

cursor

Syntax: Declare cursor cursor_name for select col_name from table name. Example: declare leads_cursor cursor for select cust_id, est_sale from leads.

___________________________________________________________________________________________________ Copyrights Reserved Page 8 of 12

1) State true or false for the following mathematical functions. Ceiling

is used to return the largest integer less than or equal to specified

value. Datepart

is used to return the current system date and time.

Datalength

used to return the column length

2) How to get the current date and time?

3) What are the steps involving in creating a Cursor? 4) Give the difference between the following statements.  Local

variables and global variables

@@rowcount

and @@trancount.

@@tranchained

and @@transtate.

5) Explain the following Isnull

function

Batch Raise

error

6) What is the use of Wildcards with like? Explain the following [

]

% ___________________________________________________________________________________________________ Copyrights Reserved Page 9 of 12

_

7) How can we Send

an Error message

Register Check

an Error message

for existence of rows

8) What is meant by Event Handling?

DATABASE SCHEMA The database scheme consists of four relations: Product(maker,model,type) PC(code,model,speed,ram,hd,cd,price) Laptop(code,model,speed,ram,hd,screen,price) Printer(code,model,color,type,price) The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

___________________________________________________________________________________________________ Copyrights Reserved Page 10 of 12

Based on the above database scheme, solve the following queries 1. Find the makers producing at least three distinct models of PCs. Result set: maker, number of models. 2. For each maker find out the average screen size of the laptops produced by it. Result set: maker, average screen size 3. Find the makers of the cheapest color printers. Result set: maker, price. 4. Define the maximal price of PC produced by each maker. Result set: maker, maximal price

___________________________________________________________________________________________________ Copyrights Reserved Page 11 of 12

Topics Covered: Chapter 6 Transact-SQL Programming Constructs -(132)

Book for Reference:

Book: Sybase SQL Server 11 by Ray Rankins Jeffrey R. Garbus David Solomon

___________________________________________________________________________________________________ Copyrights Reserved Page 12 of 12