Transcript Introduction

SQL
introduction
Getting data out of databases
• Databases are ”just” containers of data
• We could – in principle – just put data in a
text file instead
• The real strength of databases is the
ability to efficiently retrieve a specified
subset of data
SWC - 2011
2
Getting data out of databases
• Suppose we have
information about a
set of persons:
–
–
–
–
–
–
Name
Address
Date of birth
Occupation
Income
…
• We wish to find all
persons, who are
older than 40 years,
and have an income
of more than $50,000
• Is that an easy or
hard task…?
SWC - 2011
3
Getting data out of databases
Name
Address
Date of birth
Occupation
Income
John
---
12-05-1980
None
$20,000
Mira
---
30-01-1956
Doctor
$120,000
Alex
---
09-10-1971
Programmer
$240,000
Steven
---
19-06-1966
Teacher
$60,000
Joanne
---
03-12-1961
Doctor
$90,000
Hannah
---
22-03-1977
Nurse
$45,000
Susanne
---
01-08-1960
None
$20,000
SWC - 2011
4
Getting data out of databases
Name
Address
Date of birth
Occupation
Income
John
---
12-05-1980
None
$20,000
Mira
---
30-01-1956
Doctor
$120,000
09-10-1971
Programmer
$240,000
19-06-1966
Teacher
$60,000
Alex
Steven
--”Older
than
40
years”
---
Joanne
---
03-12-1961
Doctor
$90,000
Hannah
---
22-03-1977
Nurse
$45,000
Susanne
---
01-08-1960
None
$20,000
SWC - 2011
5
Getting data out of databases
Name
Address
Date of birth
Occupation
Income
Mira
---
30-01-1956
Doctor
$120,000
Steven
---
19-06-1966
Teacher
$60,000
Joanne
---
03-12-1961
Doctor
$90,000
Susanne
---
01-08-1960
None
$20,000
SWC - 2011
6
Getting data out of databases
Name
Address
Date of birth
Occupation
Income
Mira
---
30-01-1956
Doctor
$120,000
Steven
---
19-06-1966
Teacher
$60,000
Doctor
$90,000
None
$20,000
Joanne
Susanne
”Income of more
--- $50,000” 03-12-1961
than
---
01-08-1960
SWC - 2011
7
Getting data out of databases
Name
Address
Date of birth
Occupation
Income
Mira
---
30-01-1956
Doctor
$120,000
Steven
---
19-06-1966
Teacher
$60,000
Joanne
---
03-12-1961
Doctor
$90,000
SWC - 2011
8
Getting data out of databases
• Pretty easy; we could almost do it just by
looking at the table
• What if we had 100,000 records…?
• Maybe I could write a small program to
pick out the relevant records
SWC - 2011
9
Getting data out of databases
for (Records r : recordList)
{
if ((r.getAge() > 40) and
(r.getIncome() > 50000))
{
selectedRecords.add();
}
}
SWC - 2011
10
Getting data out of databases
• Fine, but…
• The code is very task-specific; can only
solve this specific problem
• The code might be inefficient; we examine
all records to find the specified subset
• Enter SQL!
SWC - 2011
11
SQL
• SQL – Structured Query Language
• A language which enables us to specify
subsets of data in a database
• Subsets in terms of
– Tables
– Fields
– Conditions on fields
SWC - 2011
12
SQL
• SQL is the standard for
database languages
• It is non-procedural;
you specify what data
to find, not how to find it
• Fairly easy to learn and
understand
SWC - 2011
13
SQL
• SQL enables us to
– Create databases and tables
– Perform ”data management” like inserting,
updating and deleting records
– Perform queries on data; i.e. retrieve
specified subsets of data
• We concentrate on queries initially
SWC - 2011
14