1 / 21

Oracle SQL Setup Guide at IU

Learn how to set up Oracle access at Indiana University, install Oracle Client, connect with SQL*Plus, and execute SQL statements.

alicelang
Download Presentation

Oracle SQL Setup Guide at IU

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL in Oracle

  2. Set up Oracle access at IU • You need to install Oracle Client: • http://kb.iu.edu/data/anhl.html • For windows: http://kb.iu.edu/data/aznp.html • Connecting to Oracle with SQL*Plus • http://kb.iu.edu/data/aznp.html#connecting • Go to Start->All programs->Oracle->Application Development->SQL*Plus • Your username should be: username@oed1prd.world

  3. Set up Oracle access at IU • Or you can use Aqua Data Studio to access Oracle

  4. Aqua Data Studio • Server connection: • host: dbserv.uits.indiana.edu • port: 1521 • username: your username • passcode: your password • System Identifier of the database (SID): oed1prd

  5. Relational Model • Data stored in relations (tables) attributes (or columns) tuples (or rows) course

  6. SQL • Data Definition Language (DDL) • CREATE TABLE • ALTER TABLE • DROP TABLE • Data Manipulation Language (DML) • INSERT INTO • SELECT • UPDATE • DELETE

  7. SQL • Basic structure – query block • SELECT – FROM – WHERE clauses • GROUP BY clause • HAVING clause • ORDER BY clause • Aggregate functions • COUNT, MIN, MAX, AVG, SUM

  8. SQL in Oracle • SQL*Plus • Command line interface to access Oracle database • Enter, edit, store, retrieve, and run SQL statements • Start SQL*Plus • Go to Start->All programs->Oracle->Application Development->SQL*Plus • Your username should be: username@oed1prd.world

  9. SQL*Plus Commands • DESCRIBE: list the columns with data types of a table • EXIT: exit the SQL*Plus program • GET: load a SQL statement into the buffer • LIST: list the current statement in the buffer • RUN: execute the current SQL statement in the buffer • SAVE: save the current SQL statement to a script file • SPOOL: send the output from a SQL statement to a file • START: load a SQL statement located in a script file and then run that SQL statement • Commit: save your input from buffer to disk. http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch4.htm#CHDGEEFE

  10. Example

  11. DDL in Oracle • Basic data types • CHAR(size) • VARCHAR2(size) • NUMBER(p, s) • DATE • BLOB/CLOB See: http://www.techonthenet.com/oracle/datatypes.php

  12. DDL in Oracle • CREATE TABLE • ALTER TABLE • DROP TABLE CREATE TABLE student ( student_id NUMBER(10), name VARCHAR2(25), major VARCHAR2(15), CONSTRAINT pk_students PRIMARY KEY (student_id) ); ALTER TABLE student ADD (GPA NUMBER(6,3)); DROP TABLE student;

  13. DML in Oracle • INSERT • UPDATE • DELETE • SELECT INSERT INTO student VALUES (101, 'Bill', 'CIS', 3.45); UPDATE student SET GPA=3.55 where student_id=101; DELETE FROM student where student_id=101; SELECT * FROM student;

  14. Create tables

  15. Queries SELECT * FROM course WHERE rownum<=3; SELECT * FROM enroll WHERE grade=‘A’;

  16. Queries SELECT * FROM student WHERE student.student_id=(SELECT enroll.student_id FROM enroll WHERE grade='A-'); SELECT * FROM student WHERE student.student_id IN (SELECT enroll.student_id FROM enroll WHERE grade='A’); SELECT student.name FROM student, enroll WHERE student.student_id=enroll.student_id AND enroll.grade=‘A’;

  17. Sorting and Grouping SELECT * FROM enroll ORDER BY grade, course_id; SELECT major, max(gpa) FROM student GROUP BY major HAVING max(gpa)>3.40; SELECT DISTINCT grade FROM enroll;

  18. Joining tables SELECT student.name, enroll.course_id, enroll.grade FROM student INNER JOIN enroll ON student.student_id=enroll.student_id;

  19. Joining tables • SELECT * FROM student LEFT JOIN enroll ON student.student_id=enroll.student_id;

  20. Joining tables • SELECT * FROM student RIGHT JOIN enroll ON student.student_id=enroll.student_id;

  21. References • www.oracle.com Oracle tutorial: • http://dbis.ucdavis.edu/courses/sqltutorial/tutorial.pdf • http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/

More Related