版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、Introduction to Oracle - SQLAdditional information is available in speaker notes!1IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT- - - - - - - - -11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jon
2、es22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy CostaDonor tabl
3、eThe 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
4、 the next presentation.2Donor tableThe 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
5、developer to obtain a wide variety of information.IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT- - - - - - - - -11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones22222 Carl Hersey 24 Benefit S
6、t Providence RI 02045 03-JAN-98 Susan Jones23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy CostaFORMAT:SELECT columns)FROM table;SQL SELEC
7、T * 2 FROM donor;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.3I
8、DNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT- - - - - - - - -11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones23456 Su
9、san Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy CostaDonor tableTo select only certain fields/columns, the user would write the column names after
10、 the SELECT. The order that they are written determines the order of display.SQL SELECT state, name, datefst 2 FROM donor;ST NAME DATEFST- - -MA Stephen Daniels 03-JUL-98RI Jennifer Ames 24-MAY-97RI Carl Hersey 03-JAN-98MA Susan Ash 04-MAR-92MA Nancy Taylor 04-MAR-92MA Robert Brooks 04-APR-986 rows
11、selected.SQL SELECT state, name, datefstFROM donor;SQL prompt.4Donor tableWhen 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 YRGOAL i
12、s justified right, while IDNO, NAME and DATEFST are justified left.SQL SELECT idno, name, yrgoal, datefst 2 FROM donor;IDNO NAME YRGOAL DATEFST- - - -11111 Stephen Daniels 500 03-JUL-9812121 Jennifer Ames 400 24-MAY-9722222 Carl Hersey 03-JAN-9823456 Susan Ash 100 04-MAR-9233333 Nancy Taylor 50 04-M
13、AR-9234567 Robert Brooks 50 04-APR-986 rows selected.SQL 5Donor tableA 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 bet
14、ween 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 ID #, name Donor Name, datefst as 1st Gave 2 FROM donor;ID # Donor Name 1st Gave- - -11111 Stephen Daniels 03-JUL-
15、9812121 Jennifer Ames 24-MAY-9722222 Carl Hersey 03-JAN-9823456 Susan Ash 04-MAR-9233333 Nancy Taylor 04-MAR-9234567 Robert Brooks 04-APR-986 rows selected.SQL SELECT idno AS ID #, name AS Donor Name, datefst AS 1st Gave 2 FROM donor;COLUMN ALIAS6Donation tableIDNO DRI CONTDATE CONTAMT- - - -11111 1
16、00 07-JAN-99 2512121 200 23-FEB-99 4023456 100 03-MAR-99 2033333 300 10-MAR-99 1022222 100 14-MAR-99 1012121 100 04-JUN-99 5011111 200 12-JUN-99 3523456 300 14-JUN-99 108 rows selected.The donation table is another sample table we will be looking at. It contains the information about donations that
17、were made. The fields/columns are IDNO, DRIVENO, CONTDATE and CONTAMT.SQL SELECT * 2 FROM donation;SQL statement used to generate the display above.7Donation tableSQL SELECT idno, driveno, contamt, contamt * 2 NEW GOAL 2 FROM donation;IDNO DRI CONTAMT NEW GOAL- - - -11111 100 25 5012121 200 40 80234
18、56 100 20 4033333 300 10 2022222 100 10 2012121 100 50 10011111 200 35 7023456 300 10 208 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
19、 alias NEW GOAL is given to the column that will contain the contamt *2.8Donor tableIDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT- - - - - - - - -11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan J
20、ones22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy CostaNote the
21、 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.512121 400 112.52222223456 100 37.533333 50 2534567 50 256 rows selected.Notice that no res
22、ults are returned when the field is null or when the null field is used in a calculation.9Donor tableSQL SELECT city | , | state CITY, STATE 2 FROM donor;CITY, STATE-Seekonk, MAProvidence, RIProvidence, RIFall River, MAFall River, MAFall River, MA6 rows selected.In this example, city is concatenated
23、 with a comma followed by a space which is concatenated with state. The column alias of CITY, STATE appears over the resulting column.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
24、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.SQL SELECT city | , | state CITY, STATE 2 FROM donor;Command shown again for clarity.The city is concatenate
25、d with a comma and a space: city | , . Then this is concatenated with state: |state. The result is one field containing city and state. 10Donor table11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jon
26、es22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy CostaSQL SELECT
27、 DISTINCT contact 2 FROM donor;CONTACT-Amy CostaJohn AdamsJohn SmithSusan JonesIn 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
28、;CONTACT IDNO NAME- - -Amy Costa 23456 Susan AshAmy Costa 34567 Robert BrooksJohn Adams 33333 Nancy TaylorJohn Smith 11111 Stephen DanielsSusan Jones 12121 Jennifer AmesSusan Jones 22222 Carl HerseyNote that DISTINCT did not work when combined with other data fields from the records/rows.11Donor tab
29、leSQL SELECT idno, name, yrgoal 2 FROM donor 3 ORDER BY yrgoal;IDNO NAME YRGOAL- - -33333 Nancy Taylor 5034567 Robert Brooks 5023456 Susan Ash 10012121 Jennifer Ames 40011111 Stephen Daniels 50022222 Carl Hersey6 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 Hersey11111 Stephen Daniels 50012121 Jennifer Ames 40023456 Susan Ash 10033333 Nanc
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 某垃圾填埋场课程设计
- sg旋风除尘器课程设计
- 高中语文活动课程设计
- 命理教学课程设计
- 2024至2030年中国玫瑰鲜切花数据监测研究报告
- 2024至2030年中国烧烤炉左右侧板数据监测研究报告
- 2024年西班牙式地砖模具项目可行性研究报告
- 2024年牛皮耐高温电焊手套项目可行性研究报告
- 2024至2030年中国实木拼花台面板数据监测研究报告
- 露天采矿学课程设计
- 黄河商品交易市场介绍稿
- 人格障碍(分析“人格障碍”)49
- Unit 3 My friends Part C Story time(教学设计)-2024-2025学年人教PEP版英语四年级上册
- 2024中国海油校园招聘2024人(高频重点提升专题训练)共500题附带答案详解
- 孙中山诞辰纪念日主题班会主题班会
- 派出所外观建设形象规范
- 2024-2030年全球及中国半导体级磷烷行业现状动态及产销需求预测报告
- 2024年团务附有答案
- 液压动力滑台的PLC控制新版专业系统设计
- 2024年北京出版集团有限责任公司招聘笔试冲刺题(带答案解析)
- 24春国家开放大学《教育学》期末大作业
评论
0/150
提交评论