Databases Week 1, lab 2

Simple selects

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:– DT2112<your username>

• E.g. mine would be DT2112pobyrne

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.

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.

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.

Simple selects

• The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

• SELECT select_list[INTO new_table_name]FROM table_list[WHERE search_conditions][GROUP BY group_by_list][HAVING search_conditions][ORDER BY order_list [ASC | DESC] ]

• In this lesson, we will address only those clauses in black.

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.

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.

Simplest select

• Select * from <table-name>– E.g. Select * from dog

• Try to do this example, using the DT2112examples database.

Selection

• Selection picks individual columns from a table:

• Try it!

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.

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

NORTHWIND’s shippers table, calling it Shipper.

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 don’t 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.

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

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.

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!

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– GO

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.

Using CAST

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?