1 / 13

Introduction to Oracle - SQL

Introduction to Oracle - SQL. Additional information is available in speaker notes!. Donor table.

danno
Download Presentation

Introduction to Oracle - SQL

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. Introduction to Oracle - SQL Additional information is available in speaker notes!

  2. Donor table The donor table contains 8 fields or columns: IDNO, NAME, STADR, CITY, STATE, ZIP, DATEFST, YRGOAL and CONTACT. Note: the names of the fields/columns in the header below refers to STATE as ST since the column is small and the entire name will not fit over the column. We will cover creating tables in the next presentation. IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa

  3. The SELECT command is used to obtain information from a table. The listing of information on the previous slide and below used the SELECT. SELECT is one of the major commands used in working with SQL. There are many clauses that accompany the SELECT that allow the developer to obtain a wide variety of information. Donor table FORMAT: SELECT {columns) FROM {table}; The SELECT statement below was issued in Oracle at the SQL prompt. SELECT * means select all columns. The FROM clause specifies that the donor table should be used. The results will be the listing of all columns for all records/rows in the donor table. The results are shown below. SQL> SELECT * 2 FROM donor; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa

  4. Donor table IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa SQL prompt. SQL> SELECT state, name, datefst 2 FROM donor; ST NAME DATEFST -- --------------- --------- MA Stephen Daniels 03-JUL-98 RI Jennifer Ames 24-MAY-97 RI Carl Hersey 03-JAN-98 MA Susan Ash 04-MAR-92 MA Nancy Taylor 04-MAR-92 MA Robert Brooks 04-APR-98 6 rows selected. SQL> To select only certain fields/columns, the user would write the column names after the SELECT. The order that they are written determines the order of display. SELECT state, name, datefst FROM donor;

  5. Donor table SQL> SELECT idno, name, yrgoal, datefst 2 FROM donor; IDNO NAME YRGOAL DATEFST ----- --------------- --------- --------- 11111 Stephen Daniels 500 03-JUL-98 12121 Jennifer Ames 400 24-MAY-97 22222 Carl Hersey 03-JAN-98 23456 Susan Ash 100 04-MAR-92 33333 Nancy Taylor 50 04-MAR-92 34567 Robert Brooks 50 04-APR-98 6 rows selected. SQL> When the columns are show, numeric data is justified right and date and character data are justified to the left. In this table, IDNO is character, NAME is character, YRGOAL is numeric and DATEFST is date. Notice that YRGOALis justified right, while IDNO, NAME and DATEFST are justified left..

  6. Donor table COLUMN ALIAS SQL> SELECT idno "ID #", name "Donor Name", datefst as "1st Gave" 2 FROM donor; ID # Donor Name 1st Gave ----- --------------- --------- 11111 Stephen Daniels 03-JUL-98 12121 Jennifer Ames 24-MAY-97 22222 Carl Hersey 03-JAN-98 23456 Susan Ash 04-MAR-92 33333 Nancy Taylor 04-MAR-92 34567 Robert Brooks 04-APR-98 6 rows selected. A column alias may be used by the developer. The alias will show up as the header, it will not effect the name of the data. If the alias is two words or a change it case, it must be enclosed in quotes. Note that the comma is used between field/column names, there is no comma between the field name and the column alias. Note also the optional word AS between column name and column alias as shown below. SQL> SELECT idno AS "ID #", name AS "Donor Name", datefst AS "1st Gave" 2 FROM donor;

  7. Donation table The donation table is another sample table we will be looking at. It contains the information about donations that were made. The fields/columns are IDNO, DRIVENO, CONTDATE and CONTAMT. IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 12121 200 23-FEB-99 40 23456 100 03-MAR-99 20 33333 300 10-MAR-99 10 22222 100 14-MAR-99 10 12121 100 04-JUN-99 50 11111 200 12-JUN-99 35 23456 300 14-JUN-99 10 8 rows selected. SQL statement used to generate the display above. SQL> SELECT * 2 FROM donation;

  8. Donation table SQL> SELECT idno, driveno, contamt, contamt * 2 "NEW GOAL" 2 FROM donation; IDNO DRI CONTAMT NEW GOAL ----- --- --------- --------- 11111 100 25 50 12121 200 40 80 23456 100 20 40 33333 300 10 20 22222 100 10 20 12121 100 50 100 11111 200 35 70 23456 300 10 20 8 rows selected. In this example, I want to print three fields/columns from the donation table and also the project goal for next year which I have determined is twice the amount donated this year. Note: The field alias NEW GOAL is given to the column that will contain the contamt *2.

  9. Donor table IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa Note the NULL value in YRGOAL. Null values are not the same as zero or space, it is the absence of data. SQL> SELECT idno, yrgoal, (yrgoal +50)/4 "QUARTERLY GOAL" 2 FROM donor; IDNO YRGOAL QUARTERLY GOAL ----- --------- -------------- 11111 500 137.5 12121 400 112.5 22222 23456 100 37.5 33333 50 25 34567 50 25 6 rows selected. Notice that no results are returned when the field is null or when the null field is used in a calculation.

  10. Donor table In this example, city is concatenated with a comma followed by a space which is concatenated with state. The column alias of CITY, STATE appears over the resulting column. The city is concatenated with a comma and a space: city || ‘, ’. Then this is concatenated with state: ||state. The result is one field containing city and state. SQL> SELECT city || ', ' || state "CITY, STATE" 2 FROM donor; CITY, STATE -------------- Seekonk, MA Providence, RI Providence, RI Fall River, MA Fall River, MA Fall River, MA 6 rows selected. Note that the address that is shown is one field. The city is concatenated with the commas and space that is enclosed in single quotes and then concatenated with the state. That results in one field or column. Note also that the literals that are concatenated with the data are enclosed in single quotes. In this example, the literals are comma plus a space. Command shown again for clarity. SQL> SELECT city || ', ' || state "CITY, STATE" 2 FROM donor;

  11. Donor table 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa SQL> SELECT DISTINCT contact 2 FROM donor; CONTACT ------------ Amy Costa John Adams John Smith Susan Jones In this case Amy Costa appears twice in the data as does Susan Jones. However since we only want distinct occurrences they appear only once in the results of the SELECT. SQL> SELECT DISTINCT contact, idno, name 2 FROM donor; CONTACT IDNO NAME ------------ ----- --------------- Amy Costa 23456 Susan Ash Amy Costa 34567 Robert Brooks John Adams 33333 Nancy Taylor John Smith 11111 Stephen Daniels Susan Jones 12121 Jennifer Ames Susan Jones 22222 Carl Hersey Note that DISTINCT did not work when combined with other data fields from the records/rows.

  12. Donor table SQL> SELECT idno, name, yrgoal 2 FROM donor 3 ORDER BY yrgoal; IDNO NAME YRGOAL ----- --------------- --------- 33333 Nancy Taylor 50 34567 Robert Brooks 50 23456 Susan Ash 100 12121 Jennifer Ames 400 11111 Stephen Daniels 500 22222 Carl Hersey 6 rows selected. The ORDER BY clause shows the data in order by the specified field/column. Ascending is the default order. Note that the record where yrgoal is NULL appears at the bottom of the list. SQL> SELECT idno, name, yrgoal 2 FROM donor 3 ORDER BY yrgoal DESC; IDNO NAME YRGOAL ----- --------------- --------- 22222 Carl Hersey 11111 Stephen Daniels 500 12121 Jennifer Ames 400 23456 Susan Ash 100 33333 Nancy Taylor 50 34567 Robert Brooks 50 6 rows selected. Placing DESC after the name in the ORDER BY clause, sorts in descending order. Note that the record where yrgoal is NULL now appears at the top of the list.

  13. Donor table The results here include a calculation of quarterly goal. This calculation is also used as the secondary sort. The primary sort puts the results in order by state in descending order so RI comes before MA. The secondary sort puts the results in ascending order by the calculated quarterly goal within the state. SQL> SELECT idno, name, city, state, (yrgoal +50)/4 "QUARTERLY GOAL" 2 FROM donor 3 ORDER BY state DESC, (yrgoal + 50)/4 ASC; IDNO NAME CITY ST QUARTERLY GOAL ----- --------------- ---------- -- -------------- 12121 Jennifer Ames Providence RI 112.5 22222 Carl Hersey Providence RI 33333 Nancy Taylor Fall River MA 25 34567 Robert Brooks Fall River MA 25 23456 Susan Ash Fall River MA 37.5 11111 Stephen Daniels Seekonk MA 137.5 6 rows selected. Total for RI in ascending order (null at end). Total for MA in ascending order.

More Related