3/20/2016

Objectives
2

To discuss:

Database
How

Basics

to

execute

SQL queries
SQL SELECT Statement

WORKING WITH DATABASES

Data-Related

Objects

Using SQL Server DBs + Visual Studio

Database Basics
4

Database Basics

A database is a collection of one or more

tables, each containing data related to a
particular topic
A table is a logical grouping of related
information

In

a relational database, the tables can be related

to each other by common fields

A primary key consists of one or more table

columns that uniquely identify each row of the
table

3/20/2016

Primary Key
5

SQL Server Data Types

6

Primary keys can be either numbers or strings

but

numeric values are processed by the database

software more efficiently.
Sometimes a primary key will consist of two or more
combined columns, called a compound primary key.
The
primary key's value is often generated
automatically by the database server each time a new
row is added to the table.

Microsoft

SQL Server, .NET, and Visual Basic data

types are similar.
See the next slide for a table with a partial list of SQL
Server data types
These data types are recognised when you pass
parameters to database queries.

i.e.

an auto-generated field, or identity field.

each new row's key value is generated by
adding an integer to the value in the previous row.

Generally,

Sample Design for a Database that Keeps Track Of

Club Members: The Members Table

SQL Server Data Types

7

When you use Visual Basic to read a database, you

must select variable types that match the type of
data in the table.
We will be using Microsoft SQL Server databases.

Avoid spaces in your column names as it causes SQL queries to be more complicated.

3/20/2016

Recommendations When
Choosing Column Types

Linked Tables
10

Avoid redundant data

Use numeric codes in columns that contain repeated values

associate these codes with other tables containing details

Example (next slide) Employee table contains Dept_Id, which

links to a Departments table

Instead of having the dept name in the same table as:

1.

2.

the same department name appears many times within the Employee
table, leading to wasted storage space.
someone typing in employee data might easily misspell a department
name.

3.

Linked Tables (continued)

11

s/he would require less time to input a numeric department ID, and there
would be less chance of a typing error.

if the company decided to rename a department, every occurrence of the

department name in the Employee table (and possibly other tables) must
be found and corrected

One-To-Many Relationship
12

A key field in a parent table matches a similar

column in a child table.
One value in Departments may match many values
in Employees:

foreign key

3/20/2016

The SQL SELECT Statement

14

13

The SQL SELECT Statement

Structured Query Language (SQL) is a universal

language for creating, updating, and retrieving
data from databases
The SELECT statement retrieves rows from one or
more database tables.
The most basic format for a single table is as
follows:

SELECT column-list
FROM table

SELECT Example

Sample Data From the Company Salesstaff Table

15

16

The following statement selects the ID and Salary

from the SalesStaff table:

SELECT ID, Salary

FROM SalesStaff

The * character in the column list selects all columns

from a table:

SELECT *
FROM SalesStaff

3/20/2016

Creating an Alias Column

17

Calculated Columns
18

A query can create a new column from one or more

existing columns in the same table.
The following statement combines Last_Name and
First_Name from a table named Members:

SELECT Last_Name + ', ' + First_Name AS Full_Name

FROM Members

SELECT
employeeId,
hoursWorked
*
hourlyRate AS payAmount FROM PayRoll

Now the Full_Name column can be inserted into a

ListBox or ComboBox.

ORDER BY
19

You can create new columns whose contents are

calculated from existing column values.
Suppose a table named Payroll contains columns
named employeeId, hoursWorked, and hourlyRate.
The following statement creates a new column named
payAmount, using hoursWorked and hourlyRate:

Another ORDER BY Example

20

Controls the display order of the table rows

Can sort by one or more columns

Examples:

ascending

or descending

ORDER BY Last_Name
ORDER BY Last_Name DESC
ORDER BY Last_Name, First_Name
ORDER BY Last_Name ASC, Salary DESC

Select all rows and sort on two columns

SELECT First_Name, Last_Name, Date_Joined

FROM Members
ORDER BY Last_Name, First_Name

3/20/2016

Selecting Rows

Relational Operators

21

Use the WHERE clause

it's

optional

Example:

SELECT First_Name, Last_Name, Salary

FROM SalesStaff
WHERE Last_Name = 'Muturi'
22

Bit Field (Boolean) Values

23

WHERE Last_Name >= 'L' (last names starting with letters L . . . Z)

WHERE Salary <> 0 (nonzero salary values)

Numeric and Date Values

24

SQL Server stores Boolean values in columns that

use the bit type.

WHERE
WHERE
WHERE
WHERE

Full_Time
Full_Time
Full_Time
Full_Time

= 1
= 'True'
= 0
<> 'False'

Use single quotes around dates

No commas in numbers
Example:
Select

rows falling between two hire dates, order them

by last name:

SELECT First_Name, Last_Name, Hire_Date

FROM SalesStaff
WHERE (Hire_Date BETWEEN '1/1/2005' AND '12/31/2009')
ORDER BY Last_Name

3/20/2016

LIKE Operator
25

LIKE Operator
26

Can be used to create partial matches

with string column values.

When combined with LIKE, the underscore

character matches a single unknown
character.

E.g.

to match all last names starting with

the letter A:

WHERE Last_Name LIKE 'A%

WHERE Account_ID LIKE 'P_4'

LIKE Operator
27

The wildcard symbol (%) matches

multiple unknown characters.

AND, OR, and NOT

28

You can combine wildcard characters.

E.g.

to match all First_Name values in

the table that have d and r in the 2nd
and 3rd positions, respectively:

WHERE First_Name LIKE '_dr%'

The character comparisons are caseinsensitive.

Use these operators to create compound

boolean expressions in the WHERE clause
of a SQL query.
Use parentheses to clarify the order of
operations, such as:

WHERE (expr1 OR expr2) AND expr3

WHERE NOT(expr1 OR expr2) AND (NOT expr3)

3/20/2016

Examples

AND, OR, and NOT Examples

29

30

Select rows in which a person was hired before 2005

or after 2010:

WHERE (Hire_Date < '1/1/2005')

(Hire_Date > '12/31/2010')

OR

Select rows in which a person was hired after 2005

and before 2010:

WHERE (Hire_Date > '12/31/2005') AND

(Hire_Date < '1/1/2010')

Select rows in which a person was hired after

1/1/2005 and the salary is greater than
40,000:
WHERE (Hire_Date > '1/1/2005') AND
(Salary > 40000)

Select all employees either hired after 1/1/2005

and whose salary is greater than 40,000, or those
that are not full-time:
WHERE (Hire_Date > '1/1/2005') AND
(Salary > 40000) OR (Full_Time =
False)

AND, OR, and NOT Examples

31

Select rows in which the hire date was either earlier

than 1/1/2008 or later than 12/31/2010:

32

Using the DataGridView Control

WHERE
(Hire_Date
NOT
BETWEEN
'1/1/2008' AND '12/31/2010')
The following expression matches rows in which the
last name does not begin with the letter A:
WHERE (Last_Name NOT LIKE 'A%')

3/20/2016

The DataGridView Control

33

Data-Related Objects
34

A convenient tool for displaying the contents of

database tables in rows and columns.
Data binding is used to link database tables to
controls on a programs forms.
Special objects named components provide the
linking mechanism.
When you link a control to a database, a wizard
guides you through the process.
We will use several data-related objects

Usually

a database but can also be a text

file, Excel spreadsheet, XML file, or Web
service
Keeps track of the database name, location,
username, password, and other connection
information.
Our data sources will be MSSQL Server
database files

Data-Related Objects
35

Data source

Data-Related Objects
36

TableAdapter
Pulls

data from one or more database tables

and copies it into a DataSet
Can select some or all table rows, add new
rows, delete rows, and modify existing rows.
Can also delete, insert, or update table rows.
Uses SQL queries to retrieve and update
database tables.

DataSet
In-memory
Virtual

copy of data pulled from database tables.

container for your tables

An

application can modify rows in the DataSet, add

new rows, and delete rows.
Changes to DataSets become permanent when an
application uses a TableAdapter to write the
changes back from the DataSet to the database.
can get data from more than
DataSets
one data source and from more than one
TableAdapter.

3/20/2016

Data-Related Objects
37

Data-Related Objects
38

DataTable

Table

inside a DataSet
Holds data generated by a TableAdapters
SELECT query.
Has a Rows collection that corresponds to
database table rows.
You can loop through the Rows collection and
inspect or modify individual column values
within each row.

Optional.
Provides

a link between a DataSet and one

or more controls on a form.
These
controls are called data-bound
controls.
If the user modifies the data in a control, the
BindingSource can copy the changes to the
DataSet.

Conceptual View
39

BindingSource

Key Terms
40

2-way Data Flow Between a Data Source and an Application

auto-generated field
BindingSource object
components
compound primary key
database
database schema
data binding
data-bound control
DataGridView control
DataSet
DataTable
data source
DataSource property
DisplayMember property

foreign key
identity field
LIKE operator
one-to-many relationship
ORDER BY clause
primary key
query parameter
relational database model
SELECT statement
Structured Query Language (SQL)
TableAdapter
WHERE clause
wildcard symbol
xcopy deployment

10