Basics on DB access Elke A Rundensteiner 1

Basics on DB access Elke A. Rundensteiner 1

DBMS? l Oracle l l l my. SQL: l l l Accounts already created Documentation: http: //www. oracle. com You would need to create an account via CCC Documentation: http: //www. mysql. com Many other DBMS engines out there 2

Oracle l l l Your accounts on Oracle are ready : l Upper case l user names cannot have a “. ” in them. Must log into CCC to ccc. wpi. edu source /cs/bin/oracle-setup 3

Oracle Version at WPI l The oracle server SID is ORCL l Version: 12. 1. 0. 2. 0 l host name is oracle. wpi. edu port is #1521 l 4

Problems while setting up Oracle l you may run into problems, typically due to typos or minor setup issues l If you get really stuck, please send a message to CANVAS to show a screen-dump of what happens. 11

Oracle introduction l Connecting l l l sqlplus <user. Name>/<PASSWORD> sqlplus rundenst@cs Change passwd using password command You may end up submitting your passwd; Therefore don’t use password that you use for other purposes. 12

Oracle useful commands These commands can be executed from SQL shell SELECT * FROM cat; -- lists tables you have created SELECT table_name FROM user_tables; -- as above. DESCRIBE <table. Name>; -- describes schema for table with name table. Name help index; -- shows list of help topics; help start; -- illustrates how to use command start exit; -- exit from SQL shell 13

Using Oracle from Windows l Multiple ways: l Use aquastudio software from aquafold. com. connect to -server: oracle. wpi. edu port: 1521 (this is the default) SID: ORCL l Download oracle client for windows. Connect using sqlplus client or other tools: sqlplus rundenst/rundenst@//oracle. wpi. edu: 1521/cs. wpi. edu 14

Working with the Data Server 15

Basic SQL Commands CREATE TABLE student(s. Num INTEGER, s. Name VARCHAR (30)); -- creates table student with two columns INSERT INTO student VALUES (1, ‘Joe’); -- insert one row into the student table SELECT * FROM student; -- select all rows from student table DELETE FROM student; -- delete all rows in the student table DROP TABLE student; -- drop student table Purge recyclebin; -- purge recyclebin tables that get created. -- Only works if you are logged onto CCC 1 16

Running scripts in SQLPlus l To enter OS environment, use sqlplus command: Host l Now you can execute OS commands, like : cd. . , exit, etc. 17

Running scripts in SQLPlus l Create a file in your file system in the current directory called : create. Table. sql l @create. Table -- executes the script start create. Table -- also executes the script If you want to save your output to a file (similar to script in Unix): l l l spool <file. Name> <execute. Cmds. . . > spool off; 18

Loading data from a text file l Create a table : l l Create data file, say: l l CREATE TABLE my. Table 1 (a int, b int); sample. dat Put data into the file : 1, 11 2, 22 3, 33 19

Loading from text file (Contd) l Create control file, say load. ctl LOAD DATA INFILE sample. dat INTO TABLE my. Table 1 FIELDS TERMINATED BY ‘, ’ (a, b) l Invoke SQL Loader (from your UNIX shell): l $ sqlldr <user/password> control=load. ctl 20

Connecting to Oracle DBMS from SQL Developer l l l Install Juniper Networks - > Network Connect(WPI VPN) Download Oracle SQL Developer from oracl; e Configure SQL Developer Connection