About Assignment 1,2 and Some Basic Knowledge of SQLPlus

1
About Assignment 1,2 and Some Basic Knowledge of
SQLPlus

• Dr. Mamoun Awad

2
Some Announcements

• Contact Information
• Awad, Mamoun
• Email maa013600_at_utdallas.edu
• Office Hours
• Room ECSS 3.614
• Tue 430-530 PM
• Wed 330-530 PM
• by appointment

3
How to setup you account?

• setup your password.
• Login Delphi with your userid and password
• Edit the .bash_profile. By appending
• if -f /oracle/env.sh
• then
• . /oracle/env.sh
• fi

4
How to change your password

• Run /oracle/oam
• The system will ask for your new password

5
How to login

• Type in sqlplus
• input your username and password
• sqlplus prompt shows up
• Or, just try sqlplus username/password

6
Example
7
Running Unix commands from SQLPlus prompt

• ! is used to access unix commands
• SQLgt ! ltunix commandgt
• Example1
• SQLgt ! pico createTables.sql
• Edit and save the file
• Example2 To display the contents of the
  file1.txt
• SQLgt!more file1.txt
• Example3 To create an empty file in the current
  directory
• SQLgt!touch empty_file.txt

8
Running SQL commands from file(as shell script or
batch file)

• You can write your sql commands in side a file
  and run all of them as a batch.
• _at_ is used to load a sql file
• Example I will create file, write a create table
  statement, and run it from the SQL prompt.
• SQLgt !pico create.sql // use pico to create and
  edit file
• SQLgt _at_ create.sql

9
Using pico, I edit the file create.sql. You can
use whatever editor youre familiar with such as
vi
10
Running the create.sql file from the sql prompt
After correcting that mistake By editing the
file. Still there is one error, but thats fine
because it says that the table was not exist,
and thats fine.
11
Notice that the result of this example might vary
depending on whether those tables were exists or
not. The drop table statement gives these kind of
errors.
Displaying the Table schema.
Here is how to display the tables in your oracle
accounts, and how to display the their schemas.
12
How to capture the sql results into a file

• Use sql commands line spool, and spool off to
  capture screen.
• Spool ltfile_namegt // the starting point.
• Spool off // the end point.
• The output is dumped in the file file_name.lst

13
Capturing the screen to a file using spool
statement
Use the spool command To capture the
screen. Notice that the output will be saved in a
file called capture_screen.lst in the current
directory of the file system.
Create/open a file insert.sql and add some
Records using the insert Statement. Notice that
the order Of inserting records is Important
because of the Referential constraints .
14
Using the ! run the insert.sql. Notice that all
rows were inserted Successfully. You can check
to see them by Displaying the contents of
student table. Finally, type in spool off to
end the Screen capture.
15
All the output of running the insert.sql will be
found in the file capture_screen.lst. Here I use
the more command in unix to display its contents.
16
A simple example1

• Capturing screen in the file myOutput.lst
• SQLgt spool myOutput.lst
• SQLgt select sysdate from dual
• any sql statements
• SQLgt spool off
• SQLgt !more myOutput.lst
• .
• What you see on the screen, will be saved into
  the file myOuptut.lst

17
A simple example2

• I want to create the customer table.
• From the SQL prompt you can type in the sql
  statement for that.
• Create table customer(
• ssn varchar2(9),
• name varchar2(33),
• Primary key (ssn)
• )

18
A simple example2

• Or you can use the _at_,! To do that.
• SQLgt !pico create.sql
• type in the create table statement and save the
  file.
• SQLgt _at_create.sql

19
A simple example2
20
Some useful points

• Select from tab
• List all the tables
• Select from cat
• list all the objects (tables, indexes..)
• Desc table_name
• Describe the table fields, types
• Drop table table_name

21
Some useful points

• Rollback
• Commit
• do not forget
• Oracle is not case-sensitive
• Employee employee are the same

22
Reminder

• TA website
• http//www.utdallas.edu/maa013600/cs6360/
• Class Email List

23
Assignment 2XML Validation

• Make sure that the XML files are validated
  against the XML Schema you designed. Use Sun
  Multi-Schema XML Validator (msv)
  http//www.sun.com/software/xml/developers/multisc
  hema

24
XML Validation

• Download/extract the sun multi-scheme XML (msv)
  validator.
• Use the command
• Cgtjava -jar ./msv-20030225/msv.jar dvdtitles.xsd
  dvdtitles.xml
• start parsing a grammar.
• validating dvdtitles.xml
• the document is valid.
• For more help,
• commandline.html

25
XQuery/QuiP

• You need t o download the QuiP from
• http//www.softwareag.com/developer/quip/
• For help/FAQ, please have a look at the file
  \QuiP-2.2.1.1\QuiP\doc\index.htm in the download.
• Linux and Windows plate-forms

26
Example

• Print each director along with the DVDs he/she
  directed.
• ltDirectorsgt
• FOR director IN document("dvdtitles.xml")//DVD/Di
  rector
• Return
• ltdirectorgt
• ltnamegt director/text() lt/namegt
• ltDVDTitlesgt
• FOR dvd IN document("dvdtitles.xml")//DVD
  Directordirector,
• title IN dvd/Title
• return title
• lt/DVDTitlesgt
• lt/directorgt
• lt/Directorsgt

27
Exmaple Results

• C\QuiP-2.2.1.1\QuiPgtquip q1.xquery
• lt?xml version"1.0" encoding"UTF-8" ?gt
• ltquipresult xmlnsquip
• "http//namespaces.softwareag.com/tamino/quip/"gt
• ltDirectorsgt
• ltdirectorgt
• ltnamegt Steven Spielberg lt/namegt
• ltDVDTitlesgt
• ltTitlegtA.I Artificial Intelligencelt/Titlegt
  
• lt/DVDTitlesgt
• lt/directorgt
• ltdirectorgt
• ltnamegt Kirk Wise, Gary Trousdale lt/namegt
• ltDVDTitlesgt
• .

28
Exmaple, the use of distinct

• Print each studio along with its DVDTitles
• ltStudiosgt
• FOR studio IN distinct-values
  (document("dvdtitles.xml")//DVD/Studio)
• Return
• ltStudiogt
• ltnamegt studio/text() lt/namegt
• ltDVDTitlesgt
• FOR dvd IN document("dvdtitles.xml")//DVD
  Studiostudio, title
• IN
  dvd/Title return title
• lt/DVDTitlesgt
• lt/Studiogt SORTBY (name,Title)
• lt/Studiosgt

29
Useful Functions

• String-length returns the length of the string
• Count like count in oracle
• Concat string concatenation
• And others