Databases Lab 5

1
Databases Lab 5

• Further Select Statements

2
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.

3
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

4
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)

5
Example day function
6
Example date, day, month and year
7
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.

8
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.

9
Example query
10
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

11
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

12
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)

13
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.

14
Naming sample
15
Mathematical Functions

• ATN2
• LOG10
• SQUARE
• CEILING
• PI
• SQRT
• COS
• POWER
• TAN
• COT
• RADIANS

• ABS
• DEGREES
• RAND
• ACOS
• EXP
• ROUND
• ASIN
• FLOOR
• SIGN
• ATAN
• LOG
• SIN

We will not do any explicit exercises on these
today.
16
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

17
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

18
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.

19
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

20
Left and right functionality
21
Replace

• Replaces one substring with another

22
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

23
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 )

24
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.

25
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.

26
B. Use CAST with arithmetic operators

• This example calculates a single column
  computation (Copies) by dividing the total
  year-to-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

27
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 gt 10.00
• GO

28
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

29
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?

30
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.

31
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)

32

• 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).

33
Exercises

• Amend the previous exercises to put names on the
  columns.
• Format your outputs so that they are easy and
  pleasant to read.

34
Achievements

• Writing Basic SQL Select Statements
• vList the capabilities of SQL SELECT statements
• vExecute a basic SELECT statement
•   Differentiate between SQL statements and
  iSQLPlus commands This is specific to Oracle.
• Restricting and Sorting Data
• vLimit the rows retrieved by a query
• v Sort the rows retrieved by a query
• Single-Row Functions
• v Describe various types of functions available
  in SQL
• v Use character, number, and date functions in
  SELECT statements
• v Use conversion functions