Databases Tutorial 1

1
Databases Tutorial 1

• Connect to SQL Server and run select statements

2
Tutorial 1 Databases

• Simple selects
• Add comments to SQL code
• Add comment to output
• Continue after an error
• Restrict rows
• Restrict columns
• Conditions

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

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

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

6
Simplest select

• Select from lttable-namegt
• E.g. Select from dog

7
WHERE search_conditions

• The WHERE clause is a filter that defines the
  conditions each row in the source tables must
  meet to qualify for the SELECT.
• Only rows that meet the conditions contribute
  data to the result set.
• Data from rows that do not meet the conditions
  are not used.

8
Selection

• Selection picks individual columns from a table

9
Projection

• Projection picks only rows that satisfy a
  condition

10
Conditions

• And, or not
• Equal, not equal, greater than, less than.
• Like, using and

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
• To denote a string, use single quotes. Double
  quotes are not recognised.
• If you want to embed a single quote (i.e. an
  apostrophe!) in a string, precede it with another
  single quote.

12
Example
13
Further exercises

• Try out the where clause on the sample
  database, changing the conditions.