CS 111

1
CS 111 Nov. 8

• Databases
• Database Management Systems (DBMS)
• Structured Query Language (SQL)
• Commitment
• Please review sections 9.1 9.2.

2
Database

• A file containing 1 tables
• Table 2-d arrangement of data into rows and
  columns
• Rows correspond to records info about 1
  customer, 1 student, 1 animal, 1 house, whatever
• Columns correspond to fields individual
  attributes about each record. For example, name,
  address, phone number, ID number, amount

3
DBMS

• Data Base Management System
• Software that allows us to manipulate a database
  file.
• Most often, we want to query the database.
• Examples
• Microsoft Access
• Open Office Base
• phpMyAdmin ?
• Oracle
• Datatel
• SQL Structured Query Language
• All DBMS support SQL
• We use SQL to communicate with the database.

4
SQL

• Structured Query Language
• DBMS accepts commands written in this language,
  so we can manipulate the database file.
• DBMS may actually have point--click shortcut
  features to save time on tedious tasks, such as
  entering all the data, or creating tables from
  scratch.
• Most common SQL command is the select
  statement, which asks the DBMS to return some of
  the data in the database. Examples
• Show me everybodys address
• How many employees make over 100,000 ?

5
How to begin

• Create the database file
• Create first table specify its format
• For each field (column), it needs a name, data
  type and maximum length.
• Common data types are
• Int/number
• Date
• Varchar (variable-length character string).
  Here you must specify a maximum length, such as
  20 characters.
• Sometimes, you may want to indicate whether a
  field is required, must have unique values, etc.
• Enter data into the table.
• Make queries about the table.

6
Example
An Employee table
First Last Location Title Salary
Peter Jacobs Brussels Broker 55000
Denise Lambert Brussels Accountant 42500
Robert Nijs Brussels Broker 66700
Ruth Molloy Chicago Manager 68650
Declan Murphy Chicago Accountant 84125
Susan Patterson Chicago Economist 51000
Rachel Brady Cincinnati Broker 43300
David Cunningham Cincinnati Accountant 48000
John Whelan Cincinnati Broker 60500
Yvonne Butler San Diego Broker 48500
Veronica Keating San Diego Broker 72000
Mary Walsh Dublin Accountant 46850
7
The select statement

• Very commonly used in SQL.
• Some possible formats
• select columns from table
• select from table
• select columns from table where condition
• Examples
• select First, Last from Employee
• select Last, Location, Salary from Employee
• select Last, Salary from Employee
• where Salary gt 70000
• select from Employee where Location Dublin
• select from Employee where Last like M

8
Aggregate functions

• In SQL, we can ask questions that involve
  arithmetic, such as finding the max, min, avg of
  numerical values.
• select max(Salary) from Employee
• select min(Salary), max(Salary) from Employee
• select avg(Salary) from Employee
• where Location Chicago
• select avg(Salary) from Employee
• where Title Broker