Starting out

1
Starting out

• The RDBMS we will be using is Oracle.
• It is held on the FERDIA server.
• The Oracle client is installed in all of the labs
  in the COMP domain.
• You interface with the Oracle database server
  using two possible interfaces
• SQLPlus
• iSQLPlus

2
Your schema

• Your schema will have your username on the COMP
  domain.
• You will be given a password.
• The Host string you will need is OracleDB.
• This is the connection string that allows you to
  connect to the correct database.
• When you connect successfully, you are in the
  SQLPlus environment.

3
The SQLPlus query tool

• This provides you with an environment in which
  you can
• Make ad hoc queries
• Edit (in a very basic way) some commands
• Load and run scripts or PL/SQL blocks, or run
  stored procedures.
• Manipulate the buffer.
• Define host or bind variables for use during the
  session.
• The buffer
• Is your current working buffer. It instantiates
  with the session when you connect to your schema.
  You can manipulate it using the SET command. E.g.
  SET SERVEROUTPUT ON shows output on the screen.
• The default command prompt is
• SQLgt_
• NOTE If you want to save your work, you need to
  commit it before exiting from SQLPLUS.
• The exit function does this automatically.

4
SQLPlus commands

• The SQLPLUS commands are quite limited. In
  essence, it is used as a development tool or an
  experts tool, so is not a GUI.
• Apart from the menu commands and the editor
  commands (see next few slides), there are some
  commands that manipulate the session
• SHOW
• EXECUTE
• VARIABLE
• SET
• DESCRIBE
• _at_
• START

5
Menu bar
6
MENU COMMANDS

• File
• Allows you to open, save or run .SQL files
• Allows you to open a spool file, that will save
  the query results as in the window.
• Edit
• Allows you to invoke another editor and to define
  one (e.g. NOTEPAD.EXE)
• Search
• Allows you to search for a string in the window.
• Options
• This allows you to set defaults.

7
Editor commands
Command Description
GET filename Writes previously saved file to the buffer.
START filename Runs a previously saved file.
_at_Filename Same as start
EDIT Invokes the default editor
EDIT filename Edit a file using the default editor
SAVE filename Save current buffer contents to a file with options to replace or append.
APPEND Replace or append
SPOOL filename OFF OUT Off closes the file and OUT sends it to the printer.
EXIT Leaves SQLPlus environment
8
Examples

• SQLgt SELECT FROB dept
• This has a syntax error. To correct
• SQLgtC\OB\OM\\
• SQLgt\
• SQLgt SELECT FROM dept
• result
• SQLgt SELECT FROM dept
• SQLgt A\ WHERE countryFrance
• SQLgt\
• SQLgtSELECT FROM dept WHERE countryFrance
• result

9
SQLPlus commands v SQL

• When you type in an SQL command in Oracle, you
  must end it with a semi-colon
• SQLPlus commands do not end in a semi-colon
• When editing a command in SQLPlus, to run the
  command, use a backslash/.

10
Your rights

• Your connection gives you all the rights /
  privileges that you as a user have been granted.
• In your case, this means that you have owner
  privileges to your own schema.
• If you want access to someone elses schema, then
  you must ask them to grant it to you.
• If you want anyone else (who doesnt have system
  privileges) to access your schema, you must grant
  that access to them.

11
Today in the lab

• Connect to SQL Plus your schema.
• Conduct simple select statements to return rows
  from the builder2 schema.

12
Find your feet

• Oracle SQL Plus
• Are you in the correct lab?
• Introduce yourselves to Oracle
• Each person has an individual schema (R,W,U,D)
• In your INDIVIDUAL schema, use the Data
  Definition Language commands to create, populate,
  update and alter 2 joined tables.

13
Find the application

• Through the Start menu, find
• Programs
• Oracle client
• Application Development
• SQL Plus (icon opposite)

14
This is an example

• Dont forget to get a username and password for
  your SCHEMA.
• The Host string defines the DATABASE. This one
  wont work for you!

15
An example from Oracle 10g
16
About the interface

• Each line is transacted immediately.
• It takes a bit of getting used to!
• Use Notepad to develop your queries and save them
  (as type .sql, not as .txt).
• When you exit the session, unless you have saved
  your queries, you lose them.
• To exit the application, type in EXIT
• Note that the full path name needs to be entered,
  so keep path names SHORT!!.

17
SQL

• SQL or Structured Query Language is used widely
  to access databases.
• There is an ANSI SQL 2003 that specifies standard
  SQL.
• Oracle deviates from the standard in some
  respects.
• Implemented SQL (Oracle, MS SQL Server, Access)
  varies a little from the standard, but is
  reasonably standard

18
Elements of SQL

• It consists of a data definition language - DDL
  (Create, Alter, Drop)
• And a Data Manipulation Language - DML (Select,
  Union, Update, Delete, Insert Into, Select Into,
  Transform, Parameter)
• A Data Control Language DCL (GRANT, REVOKE) and
  
• Transaction control statements
• COMMIT, ROLLBACK

19
Simple SELECTs

• Today, as you have not set up anything in your
  own schema, we will use the builder2 schema.
• The details of this schema are available on your
  web page.

20
Using a table from a different schema

• Any student who has been granted access to the
  table stock in the schema builder2
• Select FROM builder2.stock
• The builder2. prefix tells the DBMS that you are
  looking outside your own schema.

21
Simple selects

• The full syntax of the SELECT statement is
  complex, but the main clauses can be summarized
  as
• SELECT select_listINTO new_table_nameFROM
  table_listWHERE search_conditionsGROUP BY
  group_by_listHAVING search_conditionsORDER
  BY order_list ASC DESC
• In this lesson, we will address only those
  clauses in black.

22
Select list

• Describes the columns of the result set. It is a
  comma-separated list of expressions.
• Each expression defines both
• the format (data type and size) and
• the source of the data for the result set column.
• Each select list expression is usually a
  reference to a column in the source table or view
  the data is coming from, but can be any other
  expression, such as a constant or a Transact-SQL
  function.
• Using the expression in a select list specifies
  that all columns in the source table are returned.

23
FROM table_list

• Contains a list of the tables from which the
  result set data is retrieved. These sources can
  be
• Base tables or views in your own schema.
• Base tables or views in schemae to which you have
  select access.

24
Simplest SELECT

• SELECT FROM lttable-namegt
• E.g. SELECT FROM builder2.corderline
• or

25
Catalog

• SQLgt SELECT FROM cat
• TABLE_NAME TABLE_TYPE--------
  ---------------------- -----------BINdiXeGjXrQFi
  1kU/ypNLsQ0 TABLEBIN6btY4yriRdic8z2ONHRQrg
  0 TABLEBINSuh8BiYvRGuAAEdTnfho0w0
  TABLEBIN4DwYo9kfTciwcX3xGgcz3A0
  TABLECUSTOMER TABLECORDER
  TABLESUPPLIER
  TABLESTOCK
  TABLEsorderline TABLEstaff
  TABLESORDER
  TABLE TABLE_NAME
  TABLE_TYPE------------------------------
  -----------corderline
  TABLEBINCW8kbb3QWljFuHIBZosQ0
  TABLEBINJb8z6PBiTL6Hy8yXBB8W3g0
  TABLEBIN5AZXhMuMS4mosvy8TqkfqQ0
  TABLEBINhkvkMrD9RrqOMe4W2key9g0
  TABLEBINpFcvgvUlSDmjAyQPR5Gz7A0
  TABLEBINPk3/ceHkRvOH06D3q/6WeA0
  TABLEBINkZs92llMTwKdMcBRyIwlbg0
  TABLEBINL4P807SFTNm63jXRmPA7XA0
  TABLEBINoV13pVZNQrow/hMlzjzww0
  TABLEBINK4j8mchgSh6CseBlwvlJxw0 TABLE

• TABLE_NAME TABLE_TYPE--------
  ---------------------- -----------BINw54kQhBiQci
  Ap90csiFiWw0 TABLEBINEDmYcaIQxiLOa0euxpTaA
  0 TABLEBINWtulD5u9T2CdH/eYXdC6PQ0
  TABLEBINsUogRgsJT221fbL/tFGcCw0
  TABLEBINgCyduUbEQrOk1DVLgPpr2Q0
  TABLEBIN/uY2loJiTGu3UFWNjUBxQ0
  TABLEBINlv9wTq5gRmGK2th8kl4hnw0 TABLE29
  rows selected.
• This is unclear and unhelpful. We dont want to
  see the dropped tables. Later on we will learn
  how to improve this.

26
Miscellaneous

• To denote a string, use single quotes. Double
  quotes are not recognised.
• If you want to embed a single quote (i.e. an
  apostrophe!) in a string, precede it with another
  single quote (see slide 3 for example) .
• Note If you copy single quotes from word, they
  dont work, but they work from Notepad.
• You do not need to use quotes for numeric fields
• SELECT FROM Product where UnitPrice lt5

27
Date functions

• Commonly used date functions are
• SYSDATE
• NEXT_DAY
• ADD_MONTHS
• LAST_DAY
• MONTHS_BETWEEN
• least
• greatest
• round
• trunc

28
Date functions

• Current date and time
• SQLgt SELECT SYSDATE FROM dual
• SYSDATE
• ---------
• 30-SEP-05

29
Sample date functions

• SQLgt SELECT
• 2 order_date AS "Date",
• 3 TO_CHAR(order_date,'DAY'),
• 4 NEXT_DAY(order_date,'MONDAY') AS "Monday
  following",
• 5 LAST_DAY(order_date) as "Last day of
  month",
• 6 ADD_MONTHS (order_date,3) AS "3 months
  later"
• 7 FROM corder

30
Produces

• Date TO_CHAR(O Monday fo Last day 3 months
• --------- --------- --------- --------- ---------
• 02-FEB-02 SATURDAY 04-FEB-02 28-FEB-02 02-MAY-02
• 04-FEB-05 FRIDAY 07-FEB-05 28-FEB-05 04-MAY-05
• 04-FEB-05 FRIDAY 07-FEB-05 28-FEB-05 04-MAY-05
• 06-FEB-05 SUNDAY 07-FEB-05 28-FEB-05 06-MAY-05
• 06-FEB-05 SUNDAY 07-FEB-05 28-FEB-05 06-MAY-05
• 10-FEB-05 THURSDAY 14-FEB-05 28-FEB-05 10-MAY-05
• 12-FEB-05 SATURDAY 14-FEB-05 28-FEB-05 12-MAY-05
• 18-FEB-05 FRIDAY 21-FEB-05 28-FEB-05 18-MAY-05
• 22-FEB-05 TUESDAY 28-FEB-05 28-FEB-05 22-MAY-05
• 12-FEB-05 SATURDAY 14-FEB-05 28-FEB-05 12-MAY-05
• 10 rows selected.

31
Meaning

• TO_CHAR(date,format)
• Converts the date to the specified format
• NEXT_DAY(date,dayofweek)
• Gives the date of the next dayofweek after the
  date given.
• LAST_DAY(date)
• Gives the last day of the month in the date
  specified.
• ADD_MONTHS (date,int)
• Adds int months to the given date.

32
Date functions

• SYSDATE gives current date
• NEXT_DAY(d,day) where d is a date and day is a
  string representing a day of the week.
• E.g. NEXT_DAY(14-dec-2005,Monday) will return
  19-dec-2005
• ADD_MONTHS(d,count) adds n months to d.
• LAST_DAY(d) returns the date corresponding to the
  last day of the month in which d belongs.
• MONTHS_BETWEEN(d1,d2)
• LEAST(d1,d2,,dn)
• GREATEST(d1,,dn)
• TRUNC(d) returns the date (d) with the time at
  midnight.

33
Functions in SQL

• There are many types of functions provided.
• The ones that are used most are
• Date and Time functions
• Mathematical functions
• String functions
• There follows a list of all functions in these
  categories.
• We will practice only the most popularly used.

34
All about dates

• Dates are relative i.e. the date and time are
  the same function.
• The current date and time depends on where you
  are in the world.
• The date format '12-dec-2005' will work, but NOT
  12-dec-2005. Microsoft Word / PowerPoint will
  automatically change FROM ' to .

35
Formatting the date

• TO_CHAR(d,format) returns the date in the format
  specified
• Mm returns month number
• Mon returns the month in 3-character format
• D returns the day number in the week
• DD returns the day number in the month
• DDD returns the day number in the year
• DY gives the weekday in 3-character format
• DAY gives the weekday name
• Y returns the last digit of the year
• Yy returns the last 2 digits of the year
• Yyyy returns the 4-digit year
• Hh12 returns the hours of the day(1 -12)
• Hh24 returns the hours of the day (1 24)
• Mi returns the minutes of the hour
• Ss returns the seconds of the minute
• AM returns AM or PM

36
resources

• These sites are helpful
• http//www.techonthenet.com/oracle/index.php
• http//www.ss64.com/orasyntax/

37
To put a name on a column

• Use the AS clause to give a name to a column.
• Unitprice AS Price or
• UnitPrice AS Unit Price
• Note double quotes.
• This can be used on any column, but is especially
  useful in a derived column.
• New columns can be derived from existing fields
• E.g. the value of an item in stock is the number
  in stock by the unit price.
• Surround the alias with double quotes

• SQLgt SELECT stock_description AS "Name" FROM
  stock
• Name
• --------------------
• Brick - red, 30x100
• Cavity blocks(100)
• 2"x4" lengths
• 6" Nails(50)
• 6" Nails(100)
• Workbench
• cordless Drill
• Cavity blocks(500)
• Cavity blocks(200)
• 9 rows selected.
• SQLgt

38
Naming sample

• SQLgt SELECT
• 2 stock_description AS "Name",
• 3 QuantityRequired AS "Quantity",
• 4 Unit_Price AS "at Price",
• 5 Unit_Price QuantityRequired AS
  "SubTotal"
• 6 FROM Stock join Corderline on
• 7 stock.stock_code corderline.stock_code
• Name Quantity at Price SubTotal
• ---------------- ---------- ---------- ----------
• Brick - red, 30x100 200 2.5 500

39
String functions - Concatenation

• Concatenation
• SQLgt SELECT supplier_name','supplier_address
  FROM supplier
• SUPPLIER_NAME','SUPPLIER_ADDRESS
• --------------------------------------------------
  ------------------------------
• Buckleys,Quarry town, Quarrysville, D44.
• Brendan Moore,44 Kevin St., D8
• James McGovern,33 Synge St.
• Liam Keenan,33 Mount Vernon Ave
• Mary O'Brien,Appian Way, D2
• Oliver Moore,Georges St., D2
• June Browne,33 Liberty Lane
• Paul Sloan,44 Liberty Lane
• Kevin Kelly,33 Bride St, D8
• Robert O'Mahony,Fitzwilliam Sq
• Patricia O'Brien,21 Liberty Lane, D8
• 11 rows selected.

40
String manipulation

• Concatenation use instead of ,
• Pad out a string (from the left) to a specified
  length
• Lpad(string,length,padding char)
• Rpad does the same, but pads from the right.
• Trim strings of characters uses
• LTRIM(string,trim char)
• RTRIM trims from the right.

41
Example of lpad

• SQLgt SELECT lpad(stock_code,12,'x') AS "stock
  code", stock_description FROM stock
• stock code STOCK_DESCRIPTION
• ------------ --------------------
• xxxxxxxxA111 Red bricks(100)
• xxxxxxxBRK11 Brick - red, 30x100
• xxxxxxxxA101 Cavity blocks(100)
• xxxxxxxxB101 2"x4" lengths
• xxxxxxxxB111 Window Frames 2'x4'
• xxxxxxxxC101 6" Nails(50)
• xxxxxxxxC121 6" Nails(100)
• xxxxxxxxD101 Workbench
• xxxxxxxxD131 cordless Drill
• xxxxxxxxE101 Cavity blocks(500)
• xxxxxxxxE141 Cavity blocks(200)
• 11 rows selected.

42
Trim from the right

• SQLgt SELECT stock_code, RTRIM(stock_code,'1')
  FROM stock
• STOCK RTRIM
• ----- -----
• A101 A10
• A111 A
• B101 B10
• B111 B
• BRK11 BRK
• C101 C10
• C121 C12
• D101 D10
• D131 D13
• E101 E10
• E141 E14
• 11 rows selected.

43
Look up

• LOWER(string)
• UPPER(string)
• LENGTH(string)
• SUBSTR(string,start,n)
• INSTR(string,chars,start ,n)