Chapter 8 SpecialPurpose Languages SQL l l l

Chapter 8 Special-Purpose Languages

SQL l l l 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 l l 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 2340000 'Africa' 2345410 44060636 1880000 'Zambia' 'Africa' 752610 9445723 790000 'Zimbabwe' 'Africa' 390580 11139961 1740000

What can you do with a DB? l l l Query Create Add Update Delete Report

Database Query: SELECT l l 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 "column 1"[, "column 2", etc] from "tablename" [where "condition"]; [] = optional

Conditional Selections l 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 l l 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 l l 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)>10000

CREATE TABLE l l l The create table statement is used to create a new table. Syntax: create table "tablename" ("column 1" "data type" [constraint], "column 2" "data type" [constraint], "column 3" "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 l l l 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 l l l The update statement is used to update or change records that match a specified criteria. Syntax: update "tablename" set "columnname" = "newvalue"[, "nextcolumn" = "newvalue 2". . . ] where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [] = optional Example: update phone_book set area_code = 623 where prefix = 979;

DETELE l l 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 l Example: delete from employee where firstname = 'Mike' or firstname = 'Eric';

DROP TABLE l l l 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 l l l 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 table 1, table 2 WHERE "search-condition(s)"

PERL l l l 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 l l l 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.