SQL (CSCE 330)

1
SQL (CSCE 330)

• Team Members
• Bushira Kiyemba
• LaTrice Johnson
• Curtis Merriweather

2
SQL Origins

• Relational Model formally defined by
• Dr. E.F. Codd in 1970s, reflects SQLs
  conceptual core
• A Relational Model of Data for Large Shared Data
  Banks

3
SQL Origins

• Designed by IBM research center in 1974-1975
• System/R project, prototype of a relational
  database
• Originated as SEQUEL, structured English Query
  Language, hence the pronunciation of SQL

4
SQL Origins

• 1979, commercial relational database management
  system Oracle, used SQL as its query language
• ANSI approved SQL as official standard in 1986,
  ISO standardized in 1987

5
Database Concepts

• A database is a software program which allows for
  storage and retrieval of information on a
  computer hard drives file system or other
  device. A relational database is a database that
  allows for queries which typically use Structured
  Query Language (SQL) to store and retrieve data.

6
Database Concepts

• Relational databases allow for more efficient
  queries which use less CPU power and memory
  allocation, as they are optimized for efficiency.
  However, connecting to a database is
  significantly slower than just reading a simple
  file off of your computers hard drive. The added
  features of a relational database make this speed
  decrease worthwhile in many situations.

7
Database Concepts

• One of the most popular databases used for
  dynamic web database applications is called
  mySQL. It is commonly installed on popular web
  hosting providers Unix and Linux servers.

8
Database Concepts

• Data in relational databases is stored in four
  primary areas databases, tables, rows, and
  columns. A database is an area within the
  database software that contains all the tables
  that make up this particular set of data.

9
SQL Column Data Types

• Auto Increment ID Fields
• Varchar
• Char
• Integer
• Blob
• Bit

10
Additional SQL Commands

• Show - Show can be used to show all database
  tables in a particular database, or all databases
  in a particular server.
• Describe - Describe lets you see the structure of
  an existing database table
• Insert - Insert is how you input information into
  the database.
• Update - Update lets us update values inside an
  existing row inside a table.

11
Additional SQL Commands

• Delete - Delete lets us delete rows inside a
  table.
• Select - Does queries on the database to extract
  data from it.

12
Table Creation

• CREATE TABLE STATION (ID INTEGER PRIMARY KEY,
  CITY CHAR(20), STATE CHAR(2), LAT_N REAL,
  LONG_W REAL)

13
Table Population

• INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ',
  33, 112) INSERT INTO STATION VALUES (44,
  'Denver', 'CO', 40, 105) INSERT INTO STATION
  VALUES (66, 'Caribou', 'ME', 47, 68)

14
Selection Statement
SELECT FROM STATION
ID CITY STATE LAT_N LONG_W
13 Phoenix AZ 33 112
44 Denver CO 40 105
66 Caribou ME 47 68
15
SQL and other Languages

• There are three main means for integrating SQL
  into a
• language
• Direct
• Embedded support like Powerbuilder
• Indirect embedded support through a
  pre-processor
• Database API support
• Examples
• SQL and Python
• SQL and Perl

16
Embedding SQL in Python

• SELECT name INTO my_name
• FROM segments
• WHERE segment my_segment

17
Embedding SQL in Perl

• lt?PERLgt
• my _at_data
• lt?SQL SQL"select name, address,
• from people
• where name like '' ? ''"
  PARAMS"search_name"
• MY VAR"n, a, p"gt
• push _at_data,
• name gt n, address gt a, phone gt p,
• lt?/SQLgt
• use DataDumper
• print Dumper (\_at_data)
• lt?/PERLgt

18
Conclusion

• The Structured Query Language (SQL) forms the
  backbone of most modern database systems. It
  operates on sets of data rather than one data
  element at a time. With a single statement, you
  can get just exactly the answer you wanted from
  gigabytes of data in a millisecond.
• SQL is not particularly expressive and hence
  the need for procedural languages and other
  proprietary extensions

19
Sources

• http//www.itworld.com/nl/db_mgr/05142001/
• http//networking.webopedia.com/TERM/S/SQL.html
• http//www.opengroup.org/public/tech/datam/sql.h
  tm
• http//databases.about.com/cs/sql/?oncetrue
• http//www.paragoncorporation.com/ArticleDetail.
  aspx?ArticleID6
• http//www.geocrawler.com/archives/3/184/2000/12
  /0/4789047
• http//www.python.org/search/hypermail/python-re
  cent/0551.html
• http//www.itl.nist.gov/div897/ctg/dm/sql_exampl
  es.htmlcreate20table
• http//www.expertwebinstalls.com/cgi_tutorial/ba
  sic_relational_database_concept.html
• http//developer.mimer.com/documentation/html_82
  /Mimer_SQL_Reference_Manual
• l