Transcript Chapter 8

Chapter 8
Special-Purpose
Languages
SQL





SQL stands for "Structured Query Language".
Allows the user to pose complex questions of a
database.
It also provides a means of creating databases.
Originally developed by IBM, later become an ANSI
and ISO standard.
Many database products support SQL, e.g., MS
Access or Microsoft© SQL Server, Oracle.
Relational Databases




SQL works with relational databases.
A relational database stores data in tables
(relations).
A database is a collection of tables.
A table consists a list of records - each
record in a table has the same structure,
each has a fixed number of "fields" of a given
type.
Example: CIA Table
name
region
area
'Yemen'
‘Middle East' 527970
'Zaire'
population gdp
14728474
23400000000
'Africa'
2345410 44060636
18800000000
'Zambia'
'Africa'
752610
9445723
7900000000
'Zimbabwe'
'Africa'
390580
11139961
17400000000
What can you do with a DB?






Query
Create
Add
Update
Delete
Report
Database Query: SELECT


The select statement is used to query the
database and retrieve selected data that
match the criteria that you specify.
Here is the format of a simple select
statement:
select "column1"[,"column2",etc] from
"tablename"
[where "condition"];
[] = optional
Conditional Selections

Conditional selections used in WHERE clause:
=
Equal
>
Greater than
<
Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
LIKE pattern matching operator, e.g.,
SELECT name FROM cia WHERE LIKE ‘Ta%’
IN membership, e.g., id IN (1,2,3)
Nested SELECT


Not supported by all relational database
management systems.
Example:
SELECT name FROM cia WHERE population>
(SELECT population FROM cia WHERE
name='United States')
Aggregate Functions


SUM, COUNT, MAX, AVG, DISTINCT, ORDER BY,
GROUP BY and HAVING.
Examples:
–
–
–
SELECT SUM(population) FROM cia
SELECT name FROM cia WHERE gdp> (SELECT
SUM(gdp) FROM cia WHERE region='Africa')
SELECT region FROM cia GROUP BY region
HAVING SUM(population)>100000000
CREATE TABLE



The create table statement is used to create a new table.
Syntax:
create table "tablename"
("column1" "data type" [constraint],
"column2" "data type" [constraint],
"column3" "data type" [constraint]);
[ ] = optional
Example:
create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));
INSERT



The insert statement is used to insert or add a row
of data into the table.
Syntax:
insert into "tablename"
(first_column,...last_column)
values (first_value,...last_value);
[] = optional
Example:
insert into employee
(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars
Nest', 'Hazard Co', 'Georgia');
UPDATE



The update statement is used to update or change records that
match a specified criteria.
Syntax:
update "tablename"
set "columnname" = "newvalue"[,"nextcolumn" =
"newvalue2"...]
where "columnname" OPERATOR "value" [and|or
"column" OPERATOR "value"];
[] = optional
Example:
update phone_book
set area_code = 623
where prefix = 979;
DETELE


The delete statement is used to delete records or
rows from the table.
Syntax:
delete from "tablename"
where "columnname" OPERATOR "value" [and|or
"column" OPERATOR "value"];
[ ] = optional

Example:
delete from employee
where firstname = 'Mike' or firstname = 'Eric';
DROP TABLE



The drop table command is used to delete a
table and all rows in the table.
Syntax: drop table "tablename"
Example: drop table employee;
JOIN



All of the queries up until this point have been useful
with the exception of one major limitation - that is,
you've been selecting from only one table at a time
with your SELECT statement.
Joins allow you to link data from two or more tables
together into a single query result - from one single
SELECT statement.
"Join" makes relational database systems "relational".
SELECT "list-of-columns"
FROM table1,table2
WHERE "search-condition(s)"
PERL



Practical Extraction and Reporting Language
Designed to scan arbitrary text files, extract
various kinds of information that is contained
within the text, and print reports based on the
extracted information.
Perl uses sophisticated pattern-matching
techniques to speed up the process of
scanning large amounts of data for a
particular text string.
Background



Started by Larry Wall in 1987 and developed
as an open source project.
Perl's elaborate support for regular
expression matching and substitution has
made it the language of choice for tasks
involving string manipulation, whether for text
or binary data.
Perl is particularly popular for writing CGI
scripts.