design and implementation l.
Skip this Video
Loading SlideShow in 5 Seconds..
Design and Implementation PowerPoint Presentation
Download Presentation
Design and Implementation

Loading in 2 Seconds...

play fullscreen
1 / 23

Design and Implementation - PowerPoint PPT Presentation

  • Uploaded on

SQL. Design and Implementation. CIS 400 Final Project Dr. Bruce Maxim. SQL. An Overview of SQL. SQL stands for S tructured Q uery L anguage. It is the most commonly used relational database language today.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Design and Implementation' - jaden

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
design and implementation


Design and Implementation

CIS 400 Final Project

Dr. Bruce Maxim

Brad Lloyd & Michelle Zukowski

an overview of sql


An Overview of 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.

Brad Lloyd & Michelle Zukowski

sql is used for


SQL is used for:
  • Data Manipulation
  • Data Definition
  • Data Administration
  • All are expressed as an SQL statement or command.

Brad Lloyd & Michelle Zukowski

sql requirements


SQL Requirements
  • 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!

Brad Lloyd & Michelle Zukowski

sql is a relational database
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

Brad Lloyd & Michelle Zukowski



  • 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

Brad Lloyd & Michelle Zukowski

table design


Table Design

Columns describe one characteristic of the entity

Rows describe the Occurrence of an Entity

Brad Lloyd & Michelle Zukowski

data retrieval queries
Data Retrieval (Queries)


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

SELECT * FROM publishers

The * Operator asks for every column in the table.

Brad Lloyd & Michelle Zukowski

data retrieval queries9
Data Retrieval (Queries)
  • Queries can be more specific with a few more lines

SELECT *from publisherswhere state = ‘CA’

Only publishers in CA are displayed

Brad Lloyd & Michelle Zukowski

data input
Data Input



INSERT INTO publishersVALUES (‘0010’, ‘pragmatics’, ‘4 4th Ln’, ‘chicago’, ‘il’)

  • Putting data into a table is accomplished using the keyword


Table is updated with new information

Brad Lloyd & Michelle Zukowski

types of tables
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

Brad Lloyd & Michelle Zukowski

using sql
Using SQL

SQL statements can be embedded into a program (cgi or perl script, Visual Basic, MS Access)


SQL statements can be entered directly at the command prompt of the SQL software being used (such as mySQL)


Brad Lloyd & Michelle Zukowski

using sql13
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

Brad Lloyd & Michelle Zukowski

using sql14
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)

Brad Lloyd & Michelle Zukowski

using sql15
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


John Smith

Brad Lloyd & Michelle Zukowski

using sql16
Using SQL

If you only want to display the author’s name and city from the following table:

SELECT auth_name, auth_cityFROM publishers

Brad Lloyd & Michelle Zukowski

using sql17
Using SQL

To delete data from a table, use the DELETE statement:

DELETE from authorsWHERE auth_name=‘John Smith’

Brad Lloyd & Michelle Zukowski

using sql18
Using SQL

To Update information in a database use the UPDATE keyword

UPDATE authorsSET auth_name=‘hello’



Sets all auth_name fields to hello

Brad Lloyd & Michelle Zukowski

using sql19
Using SQL

To change a table in a database use ALTER TABLE. ADD adds a characteristic.

ALTER TABLE authorsADD birth_date datetime null



ADD puts a new column in the table called birth_date

Brad Lloyd & Michelle Zukowski

using sql20
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

Brad Lloyd & Michelle Zukowski

using sql21
Using SQL

The DROP statement is also used to delete an entire database.


DROP removed the database and returned the memory to system

Brad Lloyd & Michelle Zukowski

  • 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

Brad Lloyd & Michelle Zukowski

  • “The Practical SQL Handbook”, Third Edition, Bowman.

Brad Lloyd & Michelle Zukowski