Databases Tutorial 2

1
Databases Tutorial 2

• Further Select Statements

2
Objectives for Week

• Data types
• Sort retrieved data
• Formatting output

3
Some additions to tutorial 1

• You do not need to use quotes for numeric fields
• Select from Product where UnitPrice lt5
• Note the format of the SELECT statement
• SELECT
• columnname/
• FROM
• table-name
• WHERE
• condition

Both SELECT and FROM are always needed.
4
Sorting

• The order by clause will allow you to sort the
  fields.
• The default order is ascending. To change it to
  descending, use the keyword desc.
• select from categories order by categoryName
• is the same as
• select from categories order by categoryName
  asc
• To use descending order
• select from categories order by categoryName
  desc

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

6
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

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

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

9
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

10
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

11
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

12
E. Use CAST with LIKE clause

• This example converts an int column (the
  ytd_sales column) to a char(20) column so that it
  can be used with the LIKE clause.
• USE pubs
• GO
• SELECT title, ytd_sales FROM titles WHERE
  CAST(ytd_sales AS char(20)) LIKE '15' AND type
  'trad_cook'
• GO

13
Syntax of CAST

• Using CAST
• CAST ( expression AS data_type )
• This allows us to do the following
• Format the output so that it is more readable.
• Change a number to a character string.
• Change a character string to a numeric format.

14
Using CAST
15
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?

16
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.
• Show the order id, the customer id and the day,
  month and year of the order date, for all orders,
  in customer id order.

17
Exercises

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

18
Achievements

• Writing Basic SQL Select Statements
• vList the capabilities of SQL SELECT statements
• vExecute a basic SELECT statement
• Restricting and Sorting Data
• vLimit the rows retrieved by a query
• v Sort the rows retrieved by a query
• Single-Row Functions
• v Use conversion functions