SQL - Intro

1
SQL - Intro

• Pepper

2
Tools

• Terminal Emulator for telnet
• Panther.ssh here
• Tera Term http//www.webattack.com/get/teraterm.ht
  ml
• Run Telnet
• Setting backspace (sometimes tty erase H )
• Transferring files ftp or kermit
• Need emulator support for kermit
• Run ftp
• ftp explorer

3
Environment

• Unix Environment variables
• For Oracle to find its programs
• setenv ORACLE_BASE /usr/users/db/oracle
• setenv ORACLE_HOME ORACLE_BASE/OraHome1
• For Oracle to know which set of data
• setenv ORACLE_SID adelphi

4
Environment

• Path must include
• For Oracle
• ORACLE_HOME/binORACLE_HOME

5
Environment for JDBC

• Path (for Java)
• opt/IBMJava2-141/bin/opt/IBMJava2-141/jre/bin
  /usr/X11R6/bin
• ClassPath must include (for JDBC)
• setenv CLASSPATH ".ORACLE_HOME/jdbc/lib/classe
  s12.zipORACLE_HOME/jdbc/lib/nls_charset12.zip"
  
• LD_LIBRARY_PATH (for JDBC)
• setenv LD_LIBRARY_PATH "ORACLE_HOME/libORACL
  E_HOME/jdbc/lib"

6
Where to set environment variables

• Shell - like the command prompt in Windows
• We use tcsh (t cshell), which looks for .cshrc
  and then .login for initial login only.
• Inside, you can source another file
• .mycshrc
• Good info

7
SQLPLUS

• SQL is interactive
• SQLPLUS is procedural
• Login to panther
• It will source .cshrc and .login
• Run SQLPLUS
• Login with your oracle account id and password
• Change with password
• Send me your oracle account password

8
SQL Editing

• Sqlplus userid / password goes right in
• All commands stay in current buffer until or /
  or run (run echoes command first)
• edit lets you edit current buffer only
• change /x/y changes anything in buffer
• List of edit commands
• http//www.ss64.com/orasyntax/sqlplus.html

9
SQL file saving and using

• save file saves the current command buffer to a
  file (just the last one)
• Capturing a session
• spool file
• set echo on (so it will show what is executed by
  start command)
• spool off
• Using a saved command
• start filename (.sql doesnt need to be typed)
• host more file displays the file

10
Mistakes

• Commit
• Updates database
• Always if set autoCommit on
• Automatically on DDL command grant/create/drop
• Rollback
• Drop since last commit

11
Formatting Paging

• Page set pause on
• Column field format format option heading column
  heading
• help column

12
What is in your database?

• See list of tables
• select from cat
• See fields in table
• Describe tablename
• See data in table
• Select from tablename

13
DDL vs DML

• DDL
• Define, add, delete and change schemas
• Integrity
• Define views
• Authorize
• DML
• Add, change, delete data
• Query data

14
DDL

• Create
• Drop
• Alter
• Grant

15
Domain Types

• char(n) (or character(n)) fixed-length character
  string, with user-specified length.
• varchar(n) (or character varying)
  variable-length character string, with
  user-specified maximum length.
• int or integer an integer (length is
  machine-dependent).
• smallint a small integer (length is
  machine-dependent).
• numeric(p, d) a fixed-point number with
  user-specified precision, consists of p digits
  (plus a sign) and d of p digits are to the right
  of the decimal point. E.g., numeric(3, 1) allows
  44.5 to be stored exactly but not 444.5.
• real or double precision floating-point or
  double-precision floating-point numbers, with
  machine-dependent precision. (double float)
• float(n) floating-point, with user-specified
  precision of at least n digits.
• date a calendar date, containing four digit
  year, month, and day of the month.
• time the time of the day in hours, minutes, and
  seconds.
• Some examples for mssql

16
Create Table

• CREATE TABLE schema.table
• ( column datatype DEFAULT expr
  column_constraint(s) ,column datatype
  ,...
• table_constraint)
• Exercise
• Create table customers (cust_id number primary
  key)
• create table orders
• ( order_id number primary key,
• order_dt date not null,
• description varchar(20),
• amount real default 100 check(amount gt 100),
• cust_id references customers )

17
Drop Table

• DROP TABLE schema.table CASCADE CONSTRAINTS
• Cascade constraints -gt drop all references to
  this table in other tables
• Exercise
• create table test (test date)
• select from cat
• drop table test cascade constraints
• select from cat

18
Alter Table

• Add, modify or drop columns
• ALTER TABLE column_properties
• Change the Constraints and Primary Key for an
  existing table
• ALTER TABLE constraints
• Example
• alter table orders add (paid real, type
  varchar(3))
• modify amount real default 300

19
Insert Into

• INSERT INTO schema. table (column,
  column,...) What_to_insert
• What_to_insert
• VALUES (expr, expr...)
• or
• SubQuery
• Columns optional if exact only
• insert into orders (order_id,order_dt,description)
  VALUES (1,'01-JAN-06','desc')

20
Dealing with dates

• http//www-db.stanford.edu/ullman/fcdb/oracle/or-
  time.html
• Default - DD-MON-YY
• Override format to_date(b, 'YYYY/MM/DD')
• insert into orders (order_id,order_dt,description)
  VALUES (2, to_date(01-01-2006,MM-DD-YYYY),'
  desc')

21
Select

• SELECT hintDISTINCT select_list FROM
  table_list WHERE conditions START WITH
  CONNECT BY GROUP BY group_by_list HAVING
  search_conditions ORDER BY order_list ASC
  DESC FOR UPDATE for_update_options
• http//www.ss64.com/ora/select.html
• Ex select compname, sum(points) as total,
  count() as count from scores group by compname
  having compname like p and sum(points) gt 170

22
Select parts - All/Distinct

• SELECT ALL - default shows one value for every
  row
• SELECT DISTINCT - skips rows that are identical
  in the selected columns

23
Select Parts - columns

• Select columns from tables
• Possible column formats
• column1, column2, column3
• table.column1, table.column2
• table.column1 Col_1_Alias, table.column2
  Col_2_Alias
• schema.table.column1 Col_1_Alias,
• schema.table.column2 Col_2_Alias
• schema.table.
• expr1, expr2 ex client price0.6 AS
  list_price,

24
Select parts - tables

• Select columns from tables
• Tablename alias, tablename alias
• List all tables in query
• Oracle will multiply them (cartesian product)
• Where statement selects only those that have
  matching information

25
Select Parts - group by and having

• Only selects rows that match all fields being
  grouped.
• Can only select fields that are in the group, and
  aggregate formulas (like sum, avg)
• Having clause is like where, but on the selected
  group rows

26
Order by

• ORDER BY order_list ASC DESC NULLS
  FIRST LAST
• Sorting

27
Loading the grades database

• Download from Blackboard / course information /
  grade database and download both files to your
  pc.
• On panther - make and go to new dir
• mkdir oraclework
• cd oraclework
• ftp create_grades.sql and insert_grades.sql to
  oraclework
• On panther again, run both files in sql
• sqlplus
• start create_grades
• Start insert_grades
• Verify with select from cat

28
Select Statements

• Examples to try
• select from scores
• select sid, (compname), POINTS from scores
  order by compname
• select sid, points, points2 as double from
  scores
• select from scores where points gt 200
• select fname, lname, scores. from scores,
  students where scores.sid students.sid
• select compname, sum(points) as total, count()
  as count from scores group by compname having
  compname like p and sum(points) gt 170