Structured Query Language SQL

1
Structured Query Language(SQL)
2
S.Q.L. POLICY PAPER   Instructor Office
Extension Liz Stacey 1B44 3253 Email
lstacey_at_conestogac.on.ca WebPage
www.conestogac.on.ca/lstacey  
3
Assignments Assignments will contribute 40 of
the final grade. There will be 4-8 assignments
in SQL . Working together on programming
assignments is encouraged as better understanding
results. However, assignments that are copied do
not help anyone and if detected , will result in
a mark of zero. Assignments must be handed in on
the due date to avoid a late penalty.
4
Late Policy A deduction of 20 will be applied
per week on the assigned mark.
5
Tests Tests will contribute 60 of the final
grade. There will be two tests weighted as
follows Midterm Test 1 25 Final Test 2
35 The actual day(s) of testing will be
determined as the course material is completed.
If you are not able to write a test you must call
me at my their extension before the scheduled
test time. Arrangements can then be made for you
to write the test on the day you return to
school. As stated in the academic program
standards for the School of Business and IT, you
will be required to defend your absence with an
appropriate official document. Extended illness
will be dealt with on an individual basis. If
the above policy is not adhered to you will
receive a mark of zero for that test.
6
Assignment Standards   1. When handing in
assignments the following standards must be
adhered to   SQL script file name must include
your initials and assignment number as
follows xxAsn99.sql eg. JDAsn01.sql    
7

• 2. Write SQL statements according to the
  following standards
• Use uppercase for SQL keywords (i.e. SELECT,
  FROM, AND, etc.)
• Use lowercase for fieldnames, tablenames, and
  aliases
• Start each clause on a new line
• Indent subsequent clauses within the same
  statement.
•  
• Sample
• SELECT city, state
• FROM store
• WHERE state 'NV'
• ORDER BY city ASC
•  
•  
•  

8

• 3. Hand in SQL script file listing (refer to
  sample script file template ) which includes
• Your Name at the top
• Assignment number
• System Date
• For each question
• A comment header line indicating question number
  and question followed by the SQL statement in the
  proper format
•  

9

• 4. Hand in a listing of output results run in SQL
  Plus
• (attached after script file listing)
• This will direct the output to spool to the file
  xxAsn01.out as per required naming conventions.
• Run the sql script file using SOURCE
• Use Textpad to print the file xxAsn01.out
•  
•  
•  

10

• Using TextPad to Create SQL Assignment script
  files
•   Why use TextPad?
•  TextPad is a file editor that facilitates
  entering and editing text files
• It is more robust than WordPad or Notepad, yet is
  more suitable than Word for typing program and
  command files. It does not include the formatting
  features used and saved in a .doc file which are
  not handled in Oracle or other programming
  environments.
• Conestoga College has a site licence for TextPad,
  thus it is available in all labs on campus.
• You may download a copy for personal evaluation
  from www.textpad.com
• TextPad has features that make it particularly
  useful for working on Java files  
•  

11

•  Using TextPad to create SQL script files
•  
• It would be desirable to save all SQL script
  files with a .sql extension
• Following the conventions for this course you
  should name your SQL script file as
  xxAsn99.sql
• xx represents your initials
• 99 represents the assignment number
• so JDAsn01.sql
• would be Jane Does assignment 1 SQL script file
•  

12

•  To avoid having the default extension of .txt
  added to your file when you save it either
• Place the file name in quotes ( JDAsn01.sql
  )
• Remove the default extension and specify file
  name as JDAsn01.sql (no quotes necessary)
• Configure, Preferences, File, remove .txt from
  default
• Set the default extension to .sql and specify the
  file name JDAsn01 (no
  quotes or extension required)
• Configure, Preferences, File, set default to
  .sql

13

• Associate the .sql extension with TextPad so that
  Explorer or File Manager will automatically start
  TextPad to edit files with a .sql extension
• Configure, Preferences, Associated Files,
• Add a new extension of .sql and description
•  
• There are many other settings you can set to
  configure TextPad to your personal preferences.
  Explore at your leisure.
•  

14
Mainframe World
Checking Account Program
Organizational Database
Database Management System
Loan Program
Saving Account programs
15
Personal Database World (Access)

• File Server
• Listens for file requests
• Sends and receives files

File requests And Updates files
Network
Requested Files
Requested Files

• User 1 Client Workstation
• Sends file requests
• Receives files
• Filters data from files
• Adds new data to files

• User 2 Client Workstation
• Sends file requests
• Receives files
• Filters data from files
• Adds new data to files

16
Client/Server Database

• Database Server
• Listens for file requests
• Filters requested data from the database
• and sends it to client
• Or
• 2. Adds or updates data

File requests And Data inputs/updates
File requests And Data inputs/updates
Network
Filtered Data

• User 1 Client Workstation
• Sends file requests
• Receives files data
• Or
• 2. Sends data input or update

• User 2 Client Workstation
• Sends file requests
• Receives files data
• Or
• 2. Sends data input or update

17
Structured Query Language

• SQL Plus for creating and testing command-line
  SQL queries to create, update, and delete
  database tables, views, and sequences

18
Chapter 2
19
Database(Page 18 in Book for complete layout)
20
Prints the name and data types of the columns of
a table

• DESCRIBE table_name
• DESCRIBE can be abbreviated DESC

21
The SOURCE command executes a command/script file

• SQLgtSOURCE myscript

22
With and Without TextPad

• SELECT
• FROM store
• SELECT store_number, city, state
• FROM store
• SELECT DISTINCT state
• FROM store

23
To STOP a command

• SQLgtCTRL C