Team 8A Intro to SQL

1
Team 8AIntro to SQL

• HPM Chapter 7
• Pratt Chapter 2

2
Presenters

• Kim Moore
• Kim Wray
• Ian Marvel
• Erika Pohl
• Jovita Simon

3
SQL

• Structured Query Language
• The standard for relational database management
  systems (RDBMS)
• SQL-92 StandardPurpose
• - Specify syntax/semanties for data definitions
  and manipulation
• - Define data structures
• - Enable portability
• - Specify minimal (level 1) and complete (level
  2) standards
• - Allow for later growth/ enhancement to standard

4
Benefits of Standardized relational language

• Reduced Training Costs
• Increased Productivity
• Application Longevity
• Reduced
• Dependence on Single Vendor
• Cross-System Communication

5
(No Transcript)
6
SQL Environment

• Catalog- Each database is contained in a catalog
• Describes any object that is part of the
  database regardless of which user created the
  object.
• Schema- Contains descriptions of the objects
• Base Tables
• Views
• Constraints

7
Catalog

• Catalog must contain an information schema
  which contains descriptions of all schemas in the
  catalog, tables, views, attributes, privileges,
  constraints and domains as well as other relevant
  database information.

8
Data Definition Language (DDL)

• Commands that define a database
• - Create
• - Drop
• - Alter
• - Rename

9
Data Manipulation Language (DML)

• Commands that maintain and query a database
• - Select
• - Insert
• - Update
• - Delete

10
Data Control Language (DCL)

• Commands that control a database, including
  administering privileges and committing data.
• - Grant
• - Revoke

11

• SQL- 92, the current standard, does not include a
  CREATE DATABASE command, but most systems have a
  form of this command that is used for the initial
  storage space to contain base tables, views,
  constraints, indexes, and etc.
• Database creation may be reserved for the
  database administrator (students may be assigned
  an account that grants access or allows them to
  create a database.)

12

• CREATE DATABASE- Syntax used to create a new
  database
• CREATE TABLE- Defines a new table and its
  columns.
• CREATE INDEX- Defines an index on one column or a
  number of columns that enable rapid access to the
  rows of a table in a sequence or randomly by key
  value.
• CREATE VIEW- Defines that portion of a database
  that a particular user owns. Under this command,
  multiple schemas can be ran.

13
Changing Table Definitions

• ALTER TABLE - command may be used to add new
  columns to an existing table
• ADD
• DROP
• ALTER
• DROP TABLE used to remove a table from a
  database
• RESTRICT
• CASCADE
• DROP VIEW- Destroys a views definition and any
  other view defined.

14
Creating Tables

• Once the data model is designed and normalized,
  the columns needed for each table can be defined
  using the SQL CREATE TABLE command .

15
Creating Tables cont.

• Identify the appropriate data type length,
  precision, and scale
• Identify columns that should accept null values
• Identify unique columns
• Identify all primary keys and foreign key mates.

16
Creating Tables cont.

• Determine the values to be inserted in any
  columns
• Identify columns for which domain specifications
  may be stated.
• Create the table and any desired indexes using
  the CREATE TABLE and CREATE INDEX statements

17
Data Integrity Controls

• Referential Integrity
• An integrity constraint specifying that the
  value (or existence) of an attribute in one
  relation depends on the value (or existence) of a
  primary key in the same or another relation.

18
Internal Schema

• The internal schema of a relational database can
  be controlled for processing and storage
  efficiency.
• Some techniques used to tune the operational
  performance of the relational database internal
  data model include

19
Internal Schema cont.

• Choosing to index primary and/or secondary keys
  to increase the speed of row selection, table
  joining, and row ordering.
• Selecting file organizations for base tables that
  match the type of processing activity on those
  tables.

20
Internal Schema cont.

• Selecting file organizations for indexes, which
  are also tables, appropriate to the way the
  indexes are used, and allocating extra space for
  an index file.
• Clustering data
• Marinating statistics about tables and their
  indexes.

21
Creating Indexes

• Indexes are created to provide rapid random and
  sequential access to base table data.
• Indexes may be dropped or created at any given
  time

22
Relational Database

• A relational database is essentially a collection
  of tables.
• Examples
• Sales_rep
• Customer
• Orders
• Order_line

23
Relational Database cont

• Entity a person, place, or thing in the concept
  of the user environment
• Attribute property or characteristic of an
  entity type
• Relationship association between entity types

24
Relational Example

• One entity can have many relationships with
  another entity
• Example Customers and Orders
• Each customer is related to many orders

25
Tables

• Each entity becomes a table with each attribute
  of the entity becoming a column in the table
• Rows are called records
• Columns are called fields or attributes.

26
Tables cont

• Relation- 2D table in which entries are single
  valued, each column has a distinct name, all
  values in a column are values of the same
  attribute, and each row contains unique values.
• The shorthand for representing a relational
  database is to write the table name followed by
  the column names in parenthesis.

27
Null Value

• Represents the situations in which an actual
  value is unknown or not applicable
• When creating each column in a table, you can
  choose whether to allow nulls
• Uses a special format of the INSERT command
• Identifies the names of the columns that will
  accept non-null values
• Lists only the non-null values after the VALUES
  command
• Must have a mechanism to which columns cannot
  contain null values

28
Insert or Edit a Row

• Type the table in which to insert/edit
• Wrote file afiedt.buf
• INSERT INTO SALES_REP
• VALUES
• (03,Jones,Mary,123 Main,Grant,
• 49219,2150.)

Appears when You use the Edit command
Displays and runs Current Command
29
Create a Row

• SQLgtINSERT INTO SALES_REP(SALSREP_NUMBER,LAST,FIRS
  T)
• INSERT INTO SALES_REP
• VALUES
• (03,Jones,Mary,123 Main,Grant,MI,4921
  9,2150)
• Do not enter the value of Null
• Enter the Non-Null (must indicate which values
  you are entering by listing the corresponding
  columns

30
View Data

• Use the SELECT command
• SQLgtSELECT
• 2 FROM SALES_REP
• SL LAST FIRST STREET CITY
  ST ZIP_C TOT_COM
• 03 Jones Mary 123 Main
  Grant MI 49219 2150
• 04 Smith William 102 Raymond Ada
  MI 49441 4912.5
• 12 Diaz Miguel 419 Harper
  Lansing MI 49224 2150
• 18 Martin Elyse

31
Additional Abilities

• Add rows to character columns in a table using
  the INSERT or EDIT command
• Correct errors with the UPDATE command
• Drop a unnecessary table

32
Shorthand for Representing a Relational Database

• ExampleORDERS(ORDER_NUMBER, ORDER_DATE,
  CUSTOMER_NUMBER)
• Note SQL isnt case sensitive. Exception- use
  correct case when inserting character values into
  a table.

33
Primary Key

• Definition- a column or collection of columns
  that uniquely identifies a given row. It is
  indicated by an underlined column name.
• ExampleORDERS( ORDER_NUMBER, ORDER_DATE,
  CUSTOMER_NUMBER)

34
Table Layout

• To describe the layout of a table, use CREATE
  TABLE followed by the name of the table to be
  created and the names and type of data types of
  the columns in parentheses.
• Data type- type of data the column can contain
  (ex. characters, numbers, or dates)

35
Column Naming Conventions

• Name can be no longer than 18 characters
• Name must begin with a letter
• Name can contain letters, numbers, and
  underscores
• Name cannot contain spaces

36
Drop Table Command

• If a table in the database is no longer needed,
  use the DROP TABLE command to delete it. The
  command is followed by the name of the table to
  delete, followed by a semicolon.
• ExDROP TABLE SALES_REP

37
Common Data Types

• CHAR (n)- character string n characters long
• DATE- dates in the form DD-MON-YYYY
• DECIMAL (p,q)- decimal numbers p digits long with
  q of these places to the right of the decimal
  point.
• INTEGER-integers (numbers w/o a decimal)
• SMALLINT- indicated range -32768 to 32767

38
Null

• Null- when an actual value is unknown or not
  applicable
• If a column cant have null values, use the NULL
  clause within the CREATE TABLE command.
• Example LAST CHAR(10) NOT NULL,

39
Table Creation

• After table creation, you can insert data into
  tables
• INSERT INTO followed by the name of the table
  where you are adding data. Then type VALUES,
  followed by specific values to be inserted in
  parentheses.

40
Example of the INSERT command

• INSERT INTO SALES_REPVALUES(03,Jones,Mary,
  123, Main,Grant,MI)
• note- you must enclose values in single
  quotation marks for CHAR data type.

41
Run Command

• To run the command, save the file then close out
  notepad. Type a _at_ then followed by the name of
  the file
• _at_cre_cust
•  
• Then press the ENTER key, SQL will then execute
  the command and creates the table.

42
Insert Command

• After the table is created, you can create
  another file that contains all INSERT commands to
  add records that are applicable to the table.
• INSERT INTO CUSTOMER
• VALUES
• ('112', 'Williams', '315 Elm','New
  Windsor','NY','12553',454 23,1000,'03')

43
Describe Command

• The DESCRIBE can be used , which lists all the
  columns in the SALES_REP TABLE and the data types
  that correspond to the table.

44

• SQLgt DESCRIBE
• SALES_REP
• Name
  Null? Type
• SLREP_Number Char(2)
• Last Char(10)
• First Char(8)
• Street Char(15)
• City Char(15)
• State Char(2)
• Zip_Code Char(5)
• Total_Commission Char(5)
• Commission_Rate
  Number(7,2)
• Number(3,2)

45

• Relational Database- collection of related
  tables 
• Entity- person,place,thing, or event
• Attribute- a property of an entity
• Relationship- an association between tables in a
  database. Relationships are defined by how the
  data is related in the columns for that table.

46
Create Table

• To create a table, use the CREATE TABLE
  command.Then type the table name and then
  listing, in between parenthesis, the columns of
  the table.
• The columns will become the entity attributes.

47

• The primary keys of the table are the columns
  used to identify the rows uniquely.
• (sometimes it may take two attributes or columns
  to uniquely identify a given row) 
• Underlines are usually used under the columns to
  identify the columns.

48

• You could use the editing commands located in
  table 2.1 (Pratt book page 25) to edit the SQL
  command in the command buffer.
• To delete a table use the DROP TABLE command. It
  will also delete all the data from the database.

49
Possible Data Types For Databases

• Date
•  
• Decimal
•  
• Integer
• Small Integers
• Char

50

• A null data value is a special value that is used
  whenever the value is unavailable . 
• The NOT VALUE command is used to identify columns
  that are not allowed to have a null value.For
  example a primary key can not have a null value. 
• The INSERT command loads a table.

51

• The SELECT command is used to view the data in a
  table.
• The UPDATE command to change the value in a
  column.
• The DELETE command to delete a row in the table.

52

• Notepad can be used to create commands that
  create tables and add rows to them. To run a
  save command, type the _at_ followed by the name of
  the file.
• The DESCRIBE COMMAND can be used to describe the
  layout for the table.

53
Jovitas Question

• List and briefly describe the benefits of a
  standardize relational language

54
Kim Wrays Question

• What are some of the many advantages of using a
  view?

55
Kim Moores Question

• Why should Primary Keys never be allowed to have
  a NULL value?

56
Erikas Question

• By inserting or editing information is SQL, what
  type of advantages and disadvantages will the
  user experience?

57
Ian's Question

• Name and define the three categories that the
  SQL commands are classified in.