Transcript Databases Lab 5 Further Select Statements

Databases Lab 5
Further Select Statements
Functions in SQL
• There are many types of functions provided.
• The ones that are used most are:
– Date and Time functions
– Mathematical functions
– String functions
• There follows a list of all functions in these
categories.
• We will practice only the most popularly used.
All about dates
• Dates are relative – i.e. the date and time are
the same function.
• The current date and time depends on where
you are in the
world.
• When you show
• the standard
• datetime, it
• looks like this:
Date display functions
• The functions day, month and year return the
fields in the date that show these things:
SELECT day(orderdate) from orders
where orderid = 10248
• Returns the day of the month in the order date
field.
• Note, it does not have a column name. Only
columns that are returned in their raw state have
column names. (See next slide)
Example ‘day’ function
Example date, day, month and year
Getting the current date
• This is a function – we are selecting from
the computer on which we are running the
query, not from a table.
• GetDate returns the current date.
• As the current date is relative, this function
is non deterministic.
• Select getdate() returns the current date
and time.
The datepart function
• The datepart function can take as a first
parameter either ‘day’, ‘month’, ‘year’,
‘quarter’, ‘dayofyear’, ‘week’, ‘weekday’,
‘hour’, ‘minute’, ‘second’, ‘millisecond’.
– There are various abbreviations you can
use – see the Transact SQL help.
Example query
Adding to dates
• DateAdd adds a number of dateparts to a
date
• DATEADD ( datepart , number, date )
• E.g. to add 4 days to the orderdate:
Subtracting from a date
• Note: to subtract 4 days from a date, use
the DATEADD function and add a negative
number of days:
select orderdate,
dateadd(day, -4, orderdate)
from orders
Showing date differences
• DateDiff returns the difference between
two dates in the datepart
• DATEDIFF ( datepart , startdate , enddate )
– E.g. Show the order date, the required date
and the difference in weeks.
select orderdate, requireddate,
datediff(week, orderdate,
requireddate) from orders
order by datediff(week, orderdate,
requireddate)
To put a name on a column
• Transact SQL allows the use of the ‘as’ clause to
give a name to a column.
– Unitprice as Price or
– UnitPrice as ‘Unit Price’
• This can be used on any column, but is
especially useful in a derived column.
• New columns can be derived from existing
fields:
• E.g. the value of an item in stock is the number
in stock by the unit price.
Naming sample
Mathematical Functions
•
•
•
•
•
•
•
•
•
•
•
•
ABS
DEGREES
RAND
ACOS
EXP
ROUND
ASIN
FLOOR
SIGN
ATAN
LOG
SIN
•
•
•
•
•
•
•
•
•
•
•
ATN2
LOG10
SQUARE
CEILING
PI
SQRT
COS
POWER
TAN
COT
RADIANS
We will not do any explicit exercises on these today.
String Functions
•
•
•
•
•
•
•
•
•
•
•
LEN
LEFT
LOWER
UPPER
RIGHT
LTRIM
RTRIM
STR
DIFFERENCE
QUOTENAME
REPLICATE
•
•
•
•
•
•
•
•
•
•
•
•
STUFF
SUBSTRING
REVERSE
ASCII
NCHAR
SOUNDEX
CHAR
PATINDEX
SPACE
CHARINDEX
REPLACE
UNICODE
String functions - LEN
• Len returns the number of characters in a
string. E.g. len(name) returns the length
of characters in a the field name.
• Using Northwind, try the following:
select categoryName,
len(categoryname) from
categories
Upper, Lower, L/Rtrim
• UPPER converts the string to Upper case,
LOWER converts it to Lowercase.
• Try displaying CategoryName from
categories in upper and lower case.
• Strip leading and trailing spaces from
– Rtrim strips trailing spaces
– Ltrim strips leading spaces.
Taking out part of the field
• String fields can be manipulated by taking
out the left number of characters or the
right number of characters.
• SUBSTRING takes out a specified number
of characters from the start. Try:
select categoryname,
substring( categoryname, 3, 4)
from categories
Left and right functionality
Replace
• Replaces one substring with another:
Cast and CONVERT
• CAST and CONVERT
• Explicitly converts an expression of one
data type to another. CAST and
CONVERT provide similar functionality.
• This can be used to format output and
convert data fields from one format to
another
Cast and Convert
• CAST and CONVERT
– Explicitly converts an expression of one data type
to another. CAST and CONVERT provide similar
functionality.
• Syntax
–
–
–
–
Using CAST:
CAST ( expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ ,
style ] )
Date conversion
select convert(char(12),getdate(), 3)
as TODAY
TODAY
-----------18/01/05
(1 row(s) affected)
• You can truncate unwanted date parts when converting
from datetime or smalldatetime values by using an
appropriate char or varchar data type length.
Number conversion
• When data types are converted with a different
number of decimal places, the value is truncated
to the most precise digit.
– For example, the result of SELECT CAST(10.6496 AS
int) is 10.
• When data types in which the target data type
has fewer decimal points than the source data
type are converted, the value is rounded.
– For example, the result of CAST(10.3496847 AS
money) is $10.3497.
B. Use CAST with arithmetic
operators
• This example calculates a single column
computation (Copies) by dividing the total yearto-date sales (ytd_sales) by the individual book
price (price).
• This result is converted to an int data type after
being rounded to the nearest whole number.
– USE pubs
– GO
– SELECT CAST(ROUND(ytd_sales/price, 0) AS int)
AS 'Copies' FROM titles GO
C. Use CAST to concatenate
• This example concatenates
noncharacter, nonbinary expressions
using the CAST data type conversion
function.
– USE pubs
– GO
– SELECT 'The price is ' + CAST(price AS
varchar(12)) FROM titles WHERE price >
10.00
– GO
D. Use CAST for more readable
text
• This example uses CAST in the select
list to convert the title column to a
char(50) column so the results are
more readable.
– USE pubs
– GO
– SELECT CAST(title AS char(50)), ytd_sales
FROM titles WHERE type = 'trad_cook'
– GO
Exercises
• Retrieve the system date, using the
‘GetDate’ function.
• Convert it into char(12) format.
• Display it using style 3.
• Now display it using styles 1 through 14,
noting the differences.
• Which of these formats do you think would
be most useful in this country and for what
purposes?
Exercises (Northwind)
• To demonstrate the effectiveness of ‘order
by’:
– Select all from the order details table
– Select all from the order details table ordered
by product id.
– Select all from the order details table in
descending product id order.
More exercises
• Show the order id, the customer id and the
day, month and year of the order date, for
all orders, in customer id order.
• Using getdate(), show the current date in
every style (3 in the example) from 1 to 14.
• Display the order date, the shipped date
and the difference in days between the
two, for all orders that have been shipped
(i.e. shippeddate is not null)
• Display the order id, the required date, the
shipped date where the shipped date is
after the required date, the number of days
by which it is late, ordered by the number
of days by which it is late.
• Display the orderdate and two weeks after
the orderdate for each order that has not
been shipped (shippeddate is null).
Exercises
• Amend the previous exercises to put
names on the columns.
• Format your outputs so that they are easy
and pleasant to read.
Achievements
• Writing Basic SQL Select Statements
– [√]List the capabilities of SQL SELECT statements
– [√]Execute a basic SELECT statement
– [ ]Differentiate between SQL statements and iSQL*Plus
commands This is specific to Oracle.
• Restricting and Sorting Data
– [√]Limit the rows retrieved by a query
– [√] Sort the rows retrieved by a query
• Single-Row Functions
– [√] Describe various types of functions available in SQL
– [√] Use character, number, and date functions in SELECT
statements
– [√] Use conversion functions