Databases Week 1, lab 2

1
Databases Week 1, lab 2

• Simple selects

2
About the environment

• We are using SQL Server for the moment.
• The server we are using is
• Cian.student.comp.dit.ie
• The method of authentication you are using is SQL
  Server authentication.
• Your username and password is
• DT2112ltyour usernamegt
• E.g. mine would be DT2112pobyrne

3
About the environment

• When you connect, you are automatically directed
  to the database of which you are the owner.
• Please note SQL Server refers to your collection
  of tables as your database. Oracle refers to this
  area as your schema.
• There are other databases to which you have read
  access. This means you can run SELECT statements
  against them.

4
Other databases

• There are two Microsoft supplied sample
  databases.
• These are PUB and NORTHWIND.
• I have also set up a database that contains
  several models.
• It is called DT2112examples.
• You have read access to all of these databases.
• You do not have read access to the models of
  anyone else in your class, or in another class.

5
Exercises

• Today, you will be doing very simple exercises on
  the database.
• Connect to your own database, as you did on
  Tuesday, using the Query Analyser.
• Switch to whichever database is mentioned in the
  exercise, either by entering USE NORTHWIND (for
  example) or by using the drop-down box at the top
  of your screen.
• Try the examples and exercises in blue.

6
Simple selects

• The full syntax of the SELECT statement is
  complex, but the main clauses can be summarized
  as
• SELECT select_listINTO new_table_nameFROM
  table_listWHERE search_conditionsGROUP BY
  group_by_listHAVING search_conditionsORDER
  BY order_list ASC DESC
• In this lesson, we will address only those
  clauses in black.

7
Select list

• Describes the columns of the result set. It is a
  comma-separated list of expressions.
• Each expression defines both
• the format (data type and size) and
• the source of the data for the result set column.
• Each select list expression is usually a
  reference to a column in the source table or view
  the data is coming from, but can be any other
  expression, such as a constant or a Transact-SQL
  function.
• Using the expression in a select list specifies
  that all columns in the source table are returned.

8
FROM table_list

• Contains a list of the tables from which the
  result set data is retrieved. These sources can
  be
• Base tables in the local server running
  Microsoft SQL Server.
• Views in the local SQL Server. SQL Server
  internally resolves a view reference to
  references against the base tables that make up
  the view. See later in the course.

9
Simplest select

• Select from lttable-namegt
• E.g. Select from dog
• Try to do this example, using the DT2112examples
  database.

10
Selection

• Selection picks individual columns from a table
• Try it!

11
Miscellaneous

• To comment out parts of a query, surround the
  part by //
• To make the analyser pick up after an error or
  break, use the word GO
• Write a little query with comments on the top,
  with a select and a GO.
• Try again, but this time do 2 queries, with a
  deliberate error in the first one.

12
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.
• Write a query to select the companyname field
  from NORTHWINDs shippers table, calling it
  Shipper.

13
SQL

• SQL stands for Structured Query Language. There
  is an ANSI Standard 2003 for SQL.
• Most relational database vendors hit the
  standards in spots, but dont stick rigorously to
  them.
• The functions are often a place where the
  standards are ignored.
• Some of the functions used for SQL Server will be
  quite different when you use Oracle.

14
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

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

16
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
  decimal(6,2)) is 10.35.
• Write a query to return the product name and
  price (with 2 decimal places) of each product in
  the NORTHWIND products table.

17
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
• Try it!

18
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

19
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
• GO

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

21
Using CAST
22
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?