Design and Implementation

1
Design and Implementation
SQL

• CIS 400 Final Project
• Dr. Bruce Maxim

2
An Overview of SQL
SQL

• SQL stands for Structured Query Language.
• It is the most commonly used relational database
  language today.
• SQL works with a variety of different
  fourth-generation (4GL) programming languages,
  such as Visual Basic.

3
SQL is used for
SQL

• Data Manipulation
• Data Definition
• Data Administration
• All are expressed as an SQL statement or command.

4
SQL Requirements
SQL

• SQL Must be embedded in a programming language,
  or used with a 4GL like VB
• SQL is a free form language so there is no limit
  to the the number of words per line or fixed line
  break.
• Syntax statements, words or phrases are always in
  lower case keywords are in uppercase.

Not all versions are case sensitive!
5
SQL is a Relational Database
A Fully Relational Database Management System
must

• Represent all info in database as tables
• Keep logical representation of data independent
  from its physical storage characteristics
• Use one high-level language for structuring,
  querying, and changing info in the database
• Support the main relational operations
• Support alternate ways of looking at data in
  tables
• Provide a method for differentiating between
  unknown values and nulls (zero or blank)
• Support Mechanisms for integrity, authorization,
  transactions, and recovery

6
Design
SQL

• SQL represents all information in the form of
  tables
• Supports three relational operations selection,
  projection, and join. These are for specifying
  exactly what data you want to display or use
• SQL is used for data manipulation, definition and
  administration

7
Table Design
SQL
Columns describe one characteristic of the entity
Rows describe the Occurrence of an Entity
8
Data Retrieval (Queries)

• Queries search the database, fetch info, and
  display it. This is done using the keyword

SELECT
SELECT FROM publishers

• The Operator asks for every column in the table.

9
Data Retrieval (Queries)

• Queries can be more specific with a few more lines

SELECT from publisherswhere state CA

• Only publishers in CA are displayed

10
Data Input

• Putting data into a table is accomplished using
  the keyword

INSERT
Variable
INSERT INTO publishersVALUES (0010,
pragmatics, 4 4th Ln, chicago, il)
Keyword

• Table is updated with new information

11
Types of Tables
There are two types of tables which make up a
relational database in SQL

• User Tables contain information that is the
  database management system
• System Tables contain the database description,
  kept up to date by DBMS itself

12
Using SQL
SQL statements can be embedded into a program
(cgi or perl script, Visual Basic, MS Access) OR
SQL statements can be entered directly at the
command prompt of the SQL software being used
(such as mySQL)
SQLDatabase
13
Using SQL
To begin, you must first CREATE a database using
the following SQL statement
CREATE DATABASE database_name
Depending on the version of SQL being used the
following statement is needed to begin using the
database
USE database_name
14
Using SQL

• To create a table in the current database, use
  the CREATE TABLE keyword

CREATE TABLE authors(auth_id int(9) not
null,auth_name char(40) not null)
15
Using SQL

• To insert data in the current table, use the
  keyword INSERT INTO

INSERT INTO authorsvalues(000000001, John
Smith)

• Then issue the statement

SELECT FROM authors
000000001
John Smith
16
Using SQL
If you only want to display the authors name and
city from the following table
SELECT auth_name, auth_cityFROM publishers
17
Using SQL
To delete data from a table, use the DELETE
statement
DELETE from authorsWHERE auth_nameJohn Smith
18
Using SQL
To Update information in a database use the
UPDATE keyword
UPDATE authorsSET auth_namehello
Hello
Hello
Sets all auth_name fields to hello
19
Using SQL
To change a table in a database use ALTER TABLE.
ADD adds a characteristic.
ALTER TABLE authorsADD birth_date datetime null
Type
Initializer
ADD puts a new column in the table called
birth_date
20
Using SQL
To delete a column or row, use the keyword DROP
ALTER TABLE authorsDROP birth_date
DROP removed the birth_date characteristic from
the table
21
Using SQL
The DROP statement is also used to delete an
entire database.
DROP DATABASE authors
DROP removed the database and returned the memory
to system
22
Conclusion

• SQL is a versatile language that can integrate
  with numerous 4GL languages and applications
• SQL simplifies data manipulation by reducing the
  amount of code required.
• More reliable than creating a database using
  files with linked-list implementation

23
References

• The Practical SQL Handbook, Third Edition,
  Bowman.