Transcript SQL - Intro

SQL - Intro
Pepper
Tools
• Terminal Emulator for telnet:
–
–
–
–
Panther.ssh here
Tera Term http://www.webattack.com/get/teraterm.html
Run Telnet
Setting backspace (sometimes tty erase ^H )
• Transferring files – ftp or kermit
– Need emulator support for kermit
– Run ftp
– ftp explorer
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
Environment
• Path must include:
– For Oracle:
:$ORACLE_HOME/bin:$ORACLE_HOME
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/classes12.zip:${ORACLE_HOME}/jdbc
/lib/nls_charset12.zip"
• LD_LIBRARY_PATH (for JDBC):
setenv LD_LIBRARY_PATH
"${ORACLE_HOME}/lib:${ORACLE_HOME}/jdbc/lib"
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
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
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
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 doesn’t need to be typed)
• host more file – displays the file
Mistakes
• Commit
– Updates database
– Always if set autoCommit on
– Automatically on DDL command:
grant/create/drop
• Rollback
– Drop since last commit
Formatting & Paging
• Page – set pause on
• Column field format format option heading
“column heading”
• help column
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;
DDL vs DML
• DDL
–
–
–
–
Define, add, delete and change schemas
Integrity
Define views
Authorize
• DML
– Add, change, delete data
– Query data
DDL
•
•
•
•
Create
Drop
Alter
Grant
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
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 > 100),
cust_id references customers );
Drop Table
• DROP TABLE [schema.]table [CASCADE
CONSTRAINTS];
• Cascade constraints -> 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;
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;
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');
Dealing with dates
• http://wwwdb.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-DDYYYY’),'desc');
Select
SELECT [hint][DISTINCT] 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) > 170;
Select parts - All/Distinct
• SELECT ALL - default; shows one value
for every row
• SELECT DISTINCT - skips rows that are
identical in the selected columns
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 price]*0.6 AS list_price,
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
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
Order by
• ORDER BY order_list [ ASC | DESC ] [
NULLS { FIRST | LAST } ]
• Sorting
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;
Select Statements
• Examples to try:
– select * from scores;
– select sid, (compname), “POINTS” from scores order
by compname;
– select sid, points, points*2 as double from scores;
– select * from scores where points > 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) > 170;