Scribd
Upload
3 views17 pages

Introduction to SQL Functions and Operators

The document provides an introduction to SQL including: 1) Built-in and user defined functions that can perform calculations on data including aggregate functions like COUNT, MAX, MIN, and SUM and scalar functions like UCASE, LCASE, and ROUND. 2) Comparison operators like =, <, >, <=, >=, and <> and arithmetic operators like +, -, *, /, and %. 3) Logical operators like ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR and SOME. 4) How to alias columns to change column names and wildcards that can be used to substitute for characters in searches.

Uploaded by

Jorge Arturo Roque Cárdenas
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views17 pages

Introduction to SQL Functions and Operators

The document provides an introduction to SQL including: 1) Built-in and user defined functions that can perform calculations on data including aggregate functions like COUNT, MAX, MIN, and SUM and scalar functions like UCASE, LCASE, and ROUND. 2) Comparison operators like =, <, >, <=, >=, and <> and arithmetic operators like +, -, *, /, and %. 3) Logical operators like ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR and SOME. 4) How to alias columns to change column names and wildcards that can be used to substitute for characters in searches.

Uploaded by

Jorge Arturo Roque Cárdenas
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

WELCOME TO . . .

THE WORLD OF SQL


B Brace yourself, lf its it from f Brads B d point po tof o view e
1101011101001000111000000011101001010110110101010101010101

SELECT * FROM Customers WHERE state = NY

SELECTName,Continent,Region FROMCountry JOINCity ONID=C Capital it l ORDERBYNameDESC;

FUNCTIONS
Built B iltIn I SQLhasmanybuiltinfunctionsforperformingcalculationsondata. Cannotbemodified UserDefined Createdandmodifiedbyusers RowsetFunctions Usedtoreferencetables A Aggregate t F Functions ti Operateonacollectionofvaluesbutreturnsasinglevalue ScalarFunctions Operateonasinglevalueandreturnasinglevalue

AggregateFunctions
SQLAggregateFunctions
SQLaggregatefunctionsreturnasinglevalue,calculatedfromvaluesinacolumn TypicallyignoresNULLvalues

Usefulaggregatefunctions: AVG() Returnstheaveragevalue COUNT() Returnsthenumberofrows FIRST() Returnsthefirstvalue LAST() Returnsthelastvalue MAX() Returnsthelargestvalue MIN() Returnsthesmallestvalue SUM() Returnsthesum

10

ScalarFunctions
ScalarFunctions Operateonasinglevalueandreturnasinglevalue

Usefulscalarfunctions: UCASE() Convertsafieldtouppercase LCASE() Convertsafieldtolowercase MID() Extractcharactersfromatextfield LEN() Returnsthelengthofatextfield ROUND() Roundsanumericfieldtothenumberofdecimals specified NOW() Returnsthecurrentsystemdateandtime FORMAT() Formatshow h afield fi ldis i to t be b displayed di l d

11

ComparisonOperators
=(Equals) >(GreaterThan) <(LessThan) >=(GreaterThanorEqualTo) < (L <= (LessTh ThanorEqual E lTo) T ) <>(NotEqualTo) ! (Not != (N E Equal lT To) ) !<(NotLessThan) !>(NotGreaterThan)

12

ArithmeticOperators
+(Add) (Subtract) *(Multiply) /(Divide) ( ) %(Modulo)

13

LogicalOperators
ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR SOME

14

ColumnAliasing
SELECTproductName ASproduct,unitPrice AS priceFROMproductsWHEREunitPrice >50 Changesthe productName columnto product

15

Wildcards
Wildcard % _ [charlist] Description Asubstituteforzeroormorecharacters Asubstituteforexactlyonecharacter Anysinglecharacterincharlist

[^charlist]or Anysinglecharacternotincharlist [!charlist]

16

THATS AN INTRODUCTION TO . . .

THE WORLD R OF SQL Q


1101011101001000111000000011101001010110110101010101010101

17

You might also like