design and implementation l.
Download
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


  • 259 Views
  • 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.

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

PowerPoint Slideshow about 'Design and Implementation' - jaden


Download Now 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

SQL

Design and Implementation

CIS 400 Final Project

Dr. Bruce Maxim

Brad Lloyd & Michelle Zukowski

an overview of sql

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

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

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

design

SQL

Design
  • 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

SQL

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)

SELECT

  • 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

Variable

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

  • Putting data into a table is accomplished using the keyword

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)

OR

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

SQLDatabase

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

000000001

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’

Hello

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

Type

Initializer

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 DATABASE authors

DROP removed the database and returned the memory to system

Brad Lloyd & Michelle Zukowski

conclusion
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

Brad Lloyd & Michelle Zukowski

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

Brad Lloyd & Michelle Zukowski