Chapter 2 Connecting to Oracle 12 c and

Chapter 2 Connecting to Oracle 12 c and Basic SQL SELECT Statements Jason C. H. Chen, Ph. D. Professor of MIS School of Business, Gonzaga University Spokane, WA 99258 USA chen@gonzaga. edu Dr. Chen, Business Database Systems (Oracle)

Objectives • Learn how to connect to Oracle 12 c (client/server and Personal) • Create the initial database • Learn database objects and SQL commands and components • Identify keywords, mandatory clauses, and optional clauses in a SELECT statement • Select and view one/all columns of a table • Display multiple columns of a table • Use a column alias to clarify the contents of a particular column • Perform basic arithmetic operations in the SELECT clause • Remove duplicate lists using either the DISTINCT or UNIQUE keyword • Use concatenation to combine fields, literals, and other data Dr. Chen, Business Database Systems (Oracle) 2

Connecting to Oracle via VPN Connecting to an assigned Jepson computer Exit VPN Your local computer (Do Not Dr. Chen, Business Database Systems (Oracle) touch while working with Oracle)

Connecting to Oracle via VPN Connecting to an assigned Jepson computer Exit VPN Your local computer (Do Not Dr. Chen, Business Database Systems (Oracle) touch while working with Oracle)

Connect to Oracle 12 c • There are two ways connecting to and working with Oracle 12 c • SQL Plus – a command driven facility • SQL Developer – a GUI facility Indicate the following link to include java. exe (use “Browse”): c: program Files(x 86)javajdk 1. 7. 0…binjava. exe (choose the most recent version, may not be jdk 1. 7. 0…) Dr. Chen, Business Database Systems (Oracle) 5

YOUR TURN How to Access Your Oracle 12 c Account • User Name: C##your lastname • Password: BMIS 441 [case sensitive] Notes: 1. Use the information stated above if you run Oracle from campus or VPN. 2. If you run Oracle from installed version, username and pwd are the ones you created during the installation process. Dr. Chen, Business Database Systems (Oracle) 6

Connect to Oracle 12 using SQL Plus • User Name: C##your lastname (NO space) • Password: BMIS 441 [case sensitive] type “exit” to quit the system Dr. Chen, Business Database Systems (Oracle) Figure 2 -1 SQL*Plus Interface

APEX Installation Oracle Database Express Edition 11 g Release 2 • http: //www. oracle. com/technetwork/databas e/database-technologies/expressedition/downloads/index. html Dr. Chen, Business Database Systems (Oracle) 8

Database Objects and Queries • An Oracle database consists of multiple user accounts • Each user account owns database objects • • – Tables – Views – Stored programs, etc. Query: command to perform operation on database object Structured Query Language (SQL) – Industry standard query language for most of relational databases – Consists of about 30 commands Dr. Chen, Business Database Systems (Oracle) 9

Basic SQL Concepts and Commands SQL (Structured Query Language) is used to manipulate the database. There are two basic types of SQL commands: 4 Data Definition Language (DDL) 4 Data Manipulation Language (DML) DDL commands work with the structure of the objects (tables, indexes, views) in the database. DML commands work with the data in the database (i. e. , manipulate the data). Reserved words - SQL command words Dr. Chen, Business Database Systems (Oracle)

SQL Components ORACLE (SQL Components) DDL DML D. B. DCL (Create Table structure and insert database) Dr. Chen, Business Database Systems (Oracle) 11

DDL Commands • Used to create and modify the structure of database objects – – – CREATE ALTER DROP GRANT REVOKE • DDL commands execute as soon as they are issued, and do not need to be explicitly saved Dr. Chen, Business Database Systems (Oracle) 12

DML Commands • Used to insert, view, and modify database data – – INSERT UPDATE DELETE SELECT • DML commands need to be explicitly saved or rolled back – COMMIT – ROLLBACK – SAVEPOINT Dr. Chen, Business Database Systems (Oracle) 13

Create the Just. Lee Database • Use the provided script to create the database so you can follow the chapter examples • Verify table contents (structure) using the DESCRIBE command Dr. Chen, Business Database Systems (Oracle) 14

Scripts • Scripts are simply files that contain a sequence of SQL and/or PL/SQL commands that can be executed in SQL*Plus. • Note: – SQL and PL/SQL(to be introduced) are two languages Oracle uses. – SQL*Plus is the tool we use to access Oracle. – SQL*Plus can execute both SQL and PL/SQL commands plus it has its own commands. Dr. Chen, Business Database Systems (Oracle) 15

Class Exercise • Create a new folder on c: as follows: c: oradata 01chapter 2 (for sec. 01) c: oradata 02chapter 2 (for sec. 02) • Download all files in chapter 2 from Bb (under “Course Documents”) into c: oradatachapter 2 • Logon to SQL*Plus and type the following command (not on SQL Developer) SQL> SELECT * FROM customers; SQL> start c: oradata 01chapter 2JLDB_Build. sql or, SQL> @ c: oradata 01chapter 2JLDB_Build. sql SQL> SELECT * FROM customers; Dr. Chen, Business Database Systems (Oracle) 16

Class Exercise (continued) • A table name only can be created once and saved in the current database. Therefore, if you re-do the previous start command you will receive error message. • To avoid the problem, you need DROP the tables from the database before those tables (using the same names) are recreated. • Type the following command for SQL> start c: oradatachapter 2JLDB_Drop. sql SQL> SELECT * FROM customers; SQL> start c: oradata 01chapter 2JLDB_Build. sql Dr. Chen, Business Database Systems (Oracle) 17

How to verify “Created” Tables and their Structure? Figure 2 -3 List of existing (created) tables Figure 2 -3 List the structure of the BOOKS table Also try to type the same commands on SQL> Dr. Chen, Business Database Systems (Oracle)

SELECT Statement Syntax • SELECT statements are used to retrieve data from the database • A SELECT statement is referred to as a query • Syntax gives the basic structure, or rules, for a command • Optional clauses and keywords are shown in brackets Dr. Chen, Business Database Systems (Oracle) 19

SELECT Statement Syntax (continued) Figure 2 -5 Syntax for the SELECT statement • • SELECT and FROM clauses are required SELECT clause identifies column(s) FROM clause identifies table(s) Each clause begins with a keyword Dr. Chen, Business Database Systems (Oracle)

Your turn … • -- chapter 2, Figure 2 -6; p. 32 • SELECT * FROM CUSTOMERS; • -- chapter 2, Figure 2 -7; p. 33 • SELECT title FROM books; • • • -- chapter 2, Figure 2 -8; p. 34 SELECT TITLE FROM BOOKS; SELECT title from books; SELECT title FROM books; SELECT TITLE FROM BOOKS; Dr. Chen, Business Database Systems (Oracle) 21

Your turn … • • • -- chapter 2, Figure 2 -9; p. 35 SELECT title, pubdate FROM books; -- chapter 2, Figure 2 -10; p. 36 SELECT pubdate, title FROM books; -- chapter 2, Figure 2 -11; p. 37 SELECT title AS "Title of Book", category FROM books; -- chapter 2, Figure 2 -12; p. 38 SELECT title, retail price FROM books; -- chapter 2, Figure 2 -13; p. 40 SELECT title, retail-cost profit FROM books; • -- chapter 2, Figure 2 -14; p. 41 • SELECT * FROM books; Dr. Chen, Business Database Systems (Oracle) 22

Selecting All Data in a Table • Substitute an asterisk for the column names in a SELECT clause Figure 2 -6 Command to select all data in a table Dr. Chen, Business Database Systems (Oracle)

Selecting One Column from a Table • Enter column name in SELECT clause -- CASE is not sensitive -- chapter 2, Figure 2 -8; p. 34 SELECT TITLE FROM BOOKS; SELECT title from books; SELECT title FROM books; SELECT TITLE FROM BOOKS; Figure 2 -7 Command to select in a single column Dr. Chen, Business Database Systems (Oracle)

Selecting Multiple Columns from a Table • Separate column names with a comma -- chapter 2, Figure 2 -10; p. 36 SELECT pubdate, title FROM books; Figure 2 -10 Reversed column sequence in the SELECT clause Since the output from SQL_Developer can’t be saved (captured) in a file we will learn how to capture it next. Figure 2 -9 Command to select multiple columns from a table Dr. Chen, Business Database Systems (Oracle)

Break ! Hands-On Assignments: First try on saving both source SQL and output in a “spooled” file • In class exercise (p. 54) – #1 and #3, or • Create and save as an *. sql file, eg. Oracle_ch 2_Chen_Jason. sql • as Assignment due next class – Turn in a hardcopy • Hint: Select … --#1 FROM … SELECT * FROM books; --#3 SELECT title, pubdate "Publication Date" FROM books; Dr. Chen, Business Database Systems (Oracle) 26

Dr. Chen, Business Database Systems (Oracle)

Capture (Spool) an output SQL> Spool c: oradatachapter 2Oracle_ch 2_Spool_Lname_Fname. txt -- BMIS 441 -01; Row: 1 -- Oracle 2 Assignment, Jason Chen --#1 SELECT * FROM books; --#3 SELECT title, pubdate AS "Publication Date" FROM books; SQL> Spool off Then, open Oracle_ch 2_Spool_Lname_Fname. txt in the folder of oradatachapter 2 Verify the output See detailed steps on (Bb): Instruction_on_Creating_sql_and_SPOOL. pptx Dr. Chen, Business Database Systems (Oracle) 28

Process of working on Oracle Assignments First, open Notepad to create a source file of SQL e. g. , Oracle_ch 2_Chen_Jason. sql and test individually until all are running successful Spool c: oradataOracle_ch 2_Spool_Chen_Jason. txt -- Oracle SQL, chapter 2 -- BMIS 441/MBUS 699 Business Database Systems -- Jason Chen; -- Row: 0 -- File name: Oracle_ch 2_Chen_Jason. sql --1 (at the end) --2. SELECT title FROM books; --3. SELECT title, pubdate "Publication Date" FROM books; --4. … … … --10 SELECT lname || ', ' || fname AS "Full Name" FROM author; --1. SET LINESIZE 80 SET PAGESIZE 45 column title format a 12 heading 'Book Title' column Pub. ID format 99 heading 'ID' column Cost format $999. 99 column retail format $999. 99 SELECT * FROM books; SPOOL OFF Dr. Chen, Business Database Systems (Oracle) SQL Plus Q: What is the output file name? ______________

Homework - Hands-On Assignments Upload the SQL and spooled files (*. sql and *. txt) to the Bb (under “Assignments & Projects”) by the deadline. Read and Practice up to today’s examples on Chapters 2 1. Run the following two script files from SQL*Plus (since you have created JLDB tables) – Start c: oradatachapter 2JLDB_Drop. sql – Start c: oradatachapter 2JLDB_Build. sql 2. Re-DO all commands from the previous slides 3. Open Oracle_ch 2_Spool_Lname_Fname. txt in the folder of oradata 01chapter 2 4. Print Oracle_ch 2_Spool_Lname_Fname. txt Turn in a hardcopy of spooled file (*. txt ONLY) to me in the class. 5. Figure out is the output satisfactory? (and Why? ) Dr. Chen, Business Database Systems (Oracle)

Part II Dr. Chen, Business Database Systems (Oracle) 31

Connect to Oracle 12 c using SQL Developer for the first time If asked if you like to migrate settings, You click on “No” Dr. Chen, Business Database Systems (Oracle)

Connect to Oracle 12 c using SQL Developer for the first time Sometimes you need to do the following … (also available on the Installation Guide on Bb) Now run SQL Developer. You will be prompted to enter the pathname for your newly installed Java JDK program. If the program was installed in C and you run a 64 -bit version of Windows, the destination will be as follows: C: Program Files (x 86)Javajdk 1. 8. 0…binjava. exe. If not, you click Browse and find the Java program yourself, just make sure you point to the JDK version not the JRE. Dr. Chen, Business Database Systems (Oracle)

YOUR TURN How to Access Your Oracle 12 c Account • User Name: C##your lastname • Password: BMIS 441 [case sensitive] SBORA 12 Dr. Chen, Business Database Systems (Oracle) 34

Complete the information on the Database Connection Screen Create a new connection click on Dr. Chen, Business Database Systems (Oracle)

Complete the information on the Database Connection Screen Click on “Basic” and select “TNS” Dr. Chen, Business Database Systems (Oracle)

Complete the information on the Database Connection Screen Dr. Chen, Business Database Systems (Oracle)

Complete the information on the Database Connection Screen #1 #2 SBAORA 12 #3 Dr. Chen, Business Database Systems (Oracle)

Complete the information on the Database Connection Screen Dr. Chen, Business Database Systems (Oracle)

Connect to Oracle 12 c using SQL Developer Dr. Chen, Business Database Systems (Oracle) Figure 2 -2 SQL Developer Interface

Connect to Oracle 11 g using Personal SQL Developer click on TNS (password is created during the Oracle installation) Dr. Chen, Business Database Systems (Oracle)

Click on “+” (Expand), the Connection Box is prompted (other information are no longer needed) Dr. Chen, Business Database Systems (Oracle)

Click , if “output screen” not displayed Dr. Chen, Business Database Systems (Oracle)

An output screen from a SQL command: SELECT * FROM customers; Dr. Chen, Business Database Systems (Oracle)

Oracle Download Dr. Chen, Business Database Systems (Oracle)

Create a script file for your HW • Create a script file of Oracle_ch 2_Lname_Fname. sql • The file should contain all commands in the previous assignment. Test each SQL before you spool Spool c: oradatachapter 2Oracle_ch 2_Spool_Lname_Fname. txt -- BMIS 441 -01; Row: 1 -- Oracle 2 Assignment, Jason Chen --#1 SELECT * FROM books; --#3 SELECT title, pubdate AS "Publication Date" FROM books; Spool off Dr. Chen, Business Database Systems (Oracle) 46

The following output is from SQL> select * from books; ISBN TITLE PUBDATE PUBID COST -------------------- ----- RETAIL DISCOUNT CATEGORY ----------1059831198 BODYBUILD IN 10 MINUTES A DAY 21 -JAN-05 4 18. 75 30. 95 FITNESS 0401140733 REVENGE OF MICKEY 14 -DEC-05 1 14. 2 22 FAMILY LIFE 4981341710 BUILDING A CAR WITH TOOTHPICKS 18 -MAR-06 2 37. 8 59. 95 3 CHILDREN 8843172113 DATABASE IMPLEMENTATION 04 -JUN-03 3 31. 4 55. 95 COMPUTER 3437212490 COOKING WITH MUSHROOMS 28 -FEB-04 4 12. 5 19. 95 COOKING 3957136468 HOLY GRAIL OF ORACLE 31 -DEC-05 3 47. 25 75. 95 3. 8 COMPUTER Question: How can we improve the output? (see chapter 2, Figure 2 -14(b) on Ch 2 Queries. sql) We will learn it later and more in the later chapters. Dr. Chen, Business Database Systems (Oracle)

The following output is from SQL> select * from books; ISBN TITLE PUBDATE PUBID COST -------------------- ----- RETAIL DISCOUNT CATEGORY ----------1059831198 BODYBUILD IN 10 MINUTES A DAY 21 -JAN-05 4 18. 75 30. 95 FITNESS 0401140733 REVENGE OF MICKEY 14 -DEC-05 1 14. 2 22 FAMILY LIFE 4981341710 BUILDING A CAR WITH TOOTHPICKS 18 -MAR-06 2 37. 8 59. 95 3 CHILDREN 8843172113 DATABASE IMPLEMENTATION 04 -JUN-03 3 31. 4 55. 95 COMPUTER 3437212490 COOKING WITH MUSHROOMS 28 -FEB-04 4 12. 5 19. 95 COOKING 3957136468 HOLY GRAIL OF ORACLE 31 -DEC-05 3 47. 25 75. 95 3. 8 COMPUTER Question: How can we produce a readable output format? Dr. Chen, Business Database Systems (Oracle)

Formatting Output in SQL*Plus • To change default column headings: – Specify alternate column headings: SELECT fieldname 1 "heading 1_text", fieldname 2 "heading 2_text", . . . – Use an alias for column headings: SELECT fieldname 1 AS alias_name 1. . . – Use column statement • To change SQL*Plus line and page size settings – Select Options/Environment on menu bar – Modify linesize and pagesize to desired values • You may also type the following commands at SQL> – SQL> SET LINESIZE 80 – SQL> SET PAGESIZE 45 • Please download chapter 14 (pdf file on Bb) for more commands on formatting output. Dr. Chen, Business Database Systems (Oracle) 49

Advanced SQL Commands clear screen; column <fieldname> format a 15 wrap column <fieldname> format a 15 word column <fieldname> format a 20 heading ‘description for the field’ column <fieldname> format a 15 truncate column <fieldname> format $999. 99 -- note: default is wrap -- formatting output (try it on the SQL>) -- chapter 2, Figure 2 -14(b); p. 41 clear screen SET LINESIZE 80 SET PAGESIZE 45 column title format a 12 heading 'Book Title' WORD column Pub. ID format 99 heading 'ID' column Cost format $999. 99 column retail format $999. 99 SELECT * FROM books; Dr. Chen, Business Database Systems (Oracle) Read and practice all the SQL commands in Ch 2 Queries. sql file (esp. those at the end of the file)

Here is a better output after formatting output: ISBN Book Title PUBDATE ID COST RETAIL DISCOUNT CATEGORY ------------ ------ ------1059831198 BODYBUILD IN 21 -JAN-05 4 $18. 75 $30. 95 FITNESS 10 MINUTES A DAY 0401140733 REVENGE OF M 14 -DEC-05 1 $14. 20 $22. 00 FAMILY LIFE ICKEY 4981341710 BUILDING A C 18 -MAR-06 2 $37. 80 $59. 95 3 CHILDREN AR WITH TOOT HPICKS 8843172113 DATABASE IMP 04 -JUN-03 3 $31. 40 $55. 95 COMPUTER LEMENTATION 3437212490 COOKING WITH 28 -FEB-04 4 $12. 50 $19. 95 COOKING MUSHROOMS 3957136468 HOLY GRAIL O 31 -DEC-05 3 $47. 25 $75. 95 3. 8 COMPUTER F ORACLE 1915762492 HANDCRANKED 21 -JAN-05 3 $21. 80 $25. 00 COMPUTERS 9959789321 E-BUSINESS T 01 -MAR-06 2 $37. 90 $54. 50 COMPUTER HE EASY WAY 2491748320 PAINLESS CHI 17 -JUL-04 5 $48. 00 $89. 95 4. 5 FAMILY LIFE LD-REARING 0299282519 THE WOK WAY 11 -SEP-04 4 $19. 00 $28. 75 COOKING TO COOK 8117949391 BIG BEAR AND 08 -NOV-05 5 $5. 32 $8. 95 CHILDREN LITTLE DOVE 0132149871 HOW TO GET F 11 -NOV-06 4 $17. 85 $29. 95 1. 5 SELF HELP ASTER PIZZA 9247381001 HOW TO MANAG 09 -MAY-03 1 $15. 40 $31. 95 BUSINESS E THE MANAGE R ISBN Book Title PUBDATE ID COST RETAIL DISCOUNT CATEGORY ------------ ------ ------2147428890 SHORTEST POE 01 -MAY-05 5 $21. 85 $39. 95 LITERATURE MS 14 rows selected. Dr. Chen, Business Database Systems (Oracle)

Break ! Hands-On Assignments HW (Odd numbers only (pp. 54 -55), but do #1 at the end as it will affect the rest of problems) - run the following two commands: Start c: oradatachapter 2JLDB_Drop. sql Start c: oradatachapter 2JLDB_Build. sql Output should be formatted in a very good manner. Scripting file name: Oracle_ch 2_Lname_Fname. sql Spooled file name: Oracle_ch 2_Spool_Lname_Fname. txt Upload the spooled file (*. txt) to the Bb (under “Assignments & Projects”) by the deadline. Dr. Chen, Business Database Systems (Oracle)

Dr. Chen, Business Database Systems (Oracle)

Homework - Hands-On Assignments Upload the spooled file (*. txt) to the Bb (under “Assignments & Projects”) by the deadline. Read and Practice all examples on Chapters 2 • 1. Run two script files: JLDB_Drop. sql and JLDB_Build. sql • 2. Read Oracle assignment and create a script file Oracle_ch 2_Lname_Fname. sql for odd number questions on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs • 3. Execute and test one problem a time (do #1 at the end, why? ) and make sure they are all running successfully. Include class and personal information. • 4. When you done, spool the script files (see next slide for spooling instructions) and upload the spooled file (Oracle_ch 2_Spool_Lname_Fname. txt) to Bb by midnight this coming Sunday. Turn in a hardcopy to me in the class. Dr. Chen, Business Database Systems (Oracle)

How to Spool your Script and Output Files After you tested the script file of Oracle_ch 2_Lname_Fname. sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following two script files from SQL*Plus (since you have created JLDB tables) – Start c: oradatachapter 2JLDB_Drop. sql – Start c: oradatachapter 2JLDB_Build. sql • 1. type the following on SQL> – Spool c: oradatachapter 2Oracle_ch 2_Spool_Lname_Fname. txt (make sure your name is entered) • 2. open Oracle_ch 2_Lname_Fname. sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the. txt file and saved in C: drive (oradatachapter 2 folder) Upload the spooled file (*. txt) to the Bb (under “Assignments & Projects”) by the deadline. Dr. Chen, Business Database Systems (Oracle) 55

Operations within the SELECT Statement • • Column alias can be used for column headings Perform arithmetic operations Suppress duplicates Concatenate data • List the alias after the column heading • AS keyword is optional • Enclose in double quotation marks: – If it contains blank space(s) – If it contains special symbol(s) – To retain case Dr. Chen, Business Database Systems (Oracle) 56

Column Alias Example Figure 2 -11 Using a column alias Dr. Chen, Business Database Systems (Oracle) Figure 2 -12 Using a column alias without the AS keyword 57

Using Arithmetic Operations • Arithmetic operations – Executed left to right – Multiplication and division are solved first – Addition and subtraction are solved last – Override order with parentheses Dr. Chen, Business Database Systems (Oracle) 58

Example Arithmetic Operation with Column Alias Figure 2 -13 Using a column alias for an arithmetic expression Dr. Chen, Business Database Systems (Oracle) 59

Figure 2 -14 NULL values in the Discount column Dr. Chen, Business Database Systems (Oracle)

Using DISTINCT and UNIQUE • Enter DISTINCT or UNIQUE after SELECT keyword to suppress duplicates -- chapter 2, Figure 2 -16; p. 43 -- without DISTINCT SELECT state FROM customers; SQL> SELECT state FROM customers; ST -FL CA FL ID WA NY TX WY CA GA IL MA FL WY FL CA MI GA NJ NJ 20 rows selected. Dr. Chen, Business Database Systems (Oracle) Figure 2 -16 List of unduplicated states for customers

More example -- chapter 2, Figure 2 -17; p. 44 SELECT DISTINCT state, city FROM customers; Figure 2 -17 Unduplicated list of cities for customers Dr. Chen, Business Database Systems (Oracle)

Using Concatenation • You can combine data with a string literal • Use the concatenation operator, || • It allows the use of column aliases SELECT firstname, lastname FROM customers; Dr. Chen, Business Database Systems (Oracle) Figure 2 -18 Concatenation of two columns 63

Concatenation Examples with Alternate name Figure 2 -219 Using a string literal in concatenation Dr. Chen, Business Database Systems (Oracle) Figure 2 -20 Using a column alias for concatenated values 64

Break ! Hands-On Assignments HW (Odd numbers only (pp. 54 -55), but do #1 at the end as it will affect the rest of problems) - run the following two commands: Start c: oradatachapter 2JLDB_Drop. sql Start c: oradatachapter 2JLDB_Build. sql Output should be formatted in a very good manner. Scripting file name: Oracle_ch 2_Lname_Fname. sql Spooled file name: Oracle_ch 2_Spool_Lname_Fname. txt Upload the SQL and spooled files (*. sql and *. txt) to the Bb (under “Assignments & Projects”) by the deadline Dr. Chen, Business Database Systems (Oracle)

Homework - Hands-On Assignments Upload the SQL and spooled files (*. sql and *. txt) to the Bb (under “Assignments & Projects”) by the deadline. Read and Practice all examples on Chapters 2 • 1. Run two script files: JLDB_Drop. sql and JLDB_Build. sql • 2. Read Oracle assignment and create a script file Oracle_ch 2_Lname_Fname. sql for odd number questions on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs • 3. Execute and test one problem a time (do #1 at the end, why? ) and make sure they are all running successfully. Include class and personal information. • 4. When you done, spool the script files (see next slide for spooling instructions) and upload SQL and the spooled files (Oracle_ch 2_Spool_Lname_Fname. txt) to Bb by midnight this coming Sunday. Turn in a hardcopy of spooled file (*. txt ONLY) to me in the class. Dr. Chen, Business Database Systems (Oracle)

How to Spool your Script and Output Files After you tested the script file of Oracle_ch 2_Lname_Fname. sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following two script files from SQL*Plus (since you have created JLDB tables) – Start c: oradatachapter 2JLDB_Drop. sql – Start c: oradatachapter 2JLDB_Build. sql • 1. type the following on SQL> – Spool c: oradatachapter 2Oracle_ch 2_Spool_Lname_Fname. txt (make sure your name is entered) • 2. open Oracle_ch 2_Lname_Fname. sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the. txt file and saved in C: drive (oradatachapter 2 folder) Upload the SQL and spooled files (*. sql and *. txt) to the Bb (under “Assignments & Projects”) by the deadline. Dr. Chen, Business Database Systems (Oracle) 67

Summary • A basic query in Oracle 12 c SQL includes the SELECT and FROM clauses, the only mandatory clauses in a SELECT statement • To view all columns in the table, specify an asterisk (*) or list all of the column names individually in the SELECT clause • To display a specific column or set of columns, list the column names in the SELECT clause (in the order in which you want them to appear) • When listing column names in the SELECT clause, a comma must separate column names Dr. Chen, Business Database Systems (Oracle) 68

Summary (continued) • A column alias can be used to clarify the contents of a particular column; if the alias contains spaces or special symbols, or if you want to display the column with any lowercase letters, you must enclose the column alias in double quotation marks (" ") • Indicate the table name following the FROM keyword • Basic arithmetic operations can be performed in the SELECT clause • NULL values indicate an absence of a value Dr. Chen, Business Database Systems (Oracle) 69

Summary (continued) • To remove duplicate listings, include either the DISTINCT or UNIQUE keyword • To specify which table contains the desired columns, you must list the name of the table after the keyword FROM • Use vertical bars (||) to combine, or concatenate, fields, literals, and other data 70 Dr. Chen, Business Database Systems (Oracle) 70

• End of chapter 2 Dr. Chen, Business Database Systems (Oracle) 71