SQL - Intro

1. SQL - Intro Pepper

2. 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

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/bin:$ORACLE_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/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"

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 doesn’t 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 • CREATETABLE [schema.]table ( columndatatype [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 );

17. 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;

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 [hint][DISTINCT] select_list FROM table_list [WHERE conditions] [START WITH] [CONNECT BY] [GROUP BYgroup_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;

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 price]*0.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, 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;