内容文稿案例les

1、Retrieving Data Using the SQL SELECT StatementObjectivesAfter completing this lesson, you should be able to do the following:List the capabilities of SQL SELECT statementsExecute a basic SELECT statementLesson AgendaBasic SELECT statementArithmetic expressions and NULL values in the SELECT statement

2、Column aliasesUse of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keywordDESCRIBE commandCapabilities of SQL SELECT StatementsSelectionProjectionTable 1Table 2Table 1Table 1JoinBasic SELECT StatementSELECT identifies the columns to be displayed.FROM

3、 identifies the table containing those columns.SELECT *|DISTINCT column|expression alias,.FROM table;Selecting All ColumnsSELECT *FROM departments;Selecting Specific ColumnsSELECT department_id, location_idFROM departments;Writing SQL StatementsSQL statements are not case sensitive.SQL statements ca

4、n be entered on one or more lines.Keywords cannot be abbreviated or split across lines.Clauses are usually placed on separate lines.Indents are used to enhance readability.In SQL Developer, SQL statements can be optionally terminated by a semicolon (;). Semicolons are required when you execute multi

5、ple SQL statements.In SQL*Plus, you are required to end each SQL statement with a semicolon (;).Column Heading DefaultsSQL Developer:Default heading alignment: Left-alignedDefault heading display: UppercaseSQL*Plus:Character and Date column headings are left-aligned.Number column headings are right-

6、aligned.Default heading display: UppercaseLesson AgendaBasic SELECT statementArithmetic expressions and NULL values in the SELECT statementColumn AliasesUse of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keywordDESCRIBE commandArithmetic Expression

7、sCreate expressions with number and date data by using arithmetic operators.Multiply*Divide/Subtract-Add+DescriptionOperatorSELECT last_name, salary, salary + 300FROM employees;Using Arithmetic OperatorsSELECT last_name, salary, 12*salary+100FROM employees;Operator PrecedenceSELECT last_name, salary

8、, 12*(salary+100)FROM employees;12注：题17 Defining a Null ValueNull is a value that is unavailable, unassigned, unknown, or inapplicable.Null is not the same as zero or a blank space.SELECT last_name, job_id, salary, commission_pctFROM employees; SELECT last_name, 12*salary*commission_pctFROM employee

9、s;Null Values in Arithmetic ExpressionsArithmetic expressions containing a null value evaluate to null.Lesson AgendaBasic SELECT statementArithmetic expressions and NULL values in the SELECT statementColumn aliasesUse of concatenation operator, literal character strings, alternative quote operator,

10、and the DISTINCT keywordDESCRIBE commandDefining a Column AliasA column alias:Renames a column headingIs useful with calculationsImmediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)Requires double quotation marks if it contains spaces

11、 or special characters, or if it is case-sensitive Using Column AliasesSELECT last_name Name , salary*12 Annual SalaryFROM employees;SELECT last_name AS name, commission_pct commFROM employees;注：题12、题15Lesson AgendaBasic SELECT StatementArithmetic Expressions and NULL values in SELECT statementColum

12、n AliasesUse of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keywordDESCRIBE commandConcatenation OperatorA concatenation operator:Links columns or character strings to other columnsIs represented by two vertical bars (|)Creates a resultant column t

13、hat is a character expressionSELECTlast_name|job_id AS EmployeesFROM employees;Literal Character StringsA literal is a character, a number, or a date that is included in the SELECT statement.Date and character literal values must be enclosed within single quotation marks.Each character string is out

14、put once for each row returned.Using Literal Character StringsSELECT last_name | is a |job_id AS Employee DetailsFROM employees;Alternative Quote (q) OperatorSpecify your own quotation mark delimiter.Select any delimiter.Increase readability and usability.SELECT department_name | q Departments Manag

15、er Id: | manager_id AS Department and Manager FROM departments;注：题13Duplicate RowsThe default display of queries is all rows, including duplicate rows.SELECT department_idFROM employees;SELECT DISTINCT department_idFROM employees;12注：题14Lesson AgendaBasic SELECT statementArithmetic expressions and N

16、ULL values in the SELECT statementColumn aliasesUse of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keywordDESCRIBE commandDisplaying the Table StructureUse the DESCRIBE command to display the structure of a table.Or, select the table in the Connect

17、ions tree and use the Columns tab to view the table structure.DESCRIBE tablenameUsing the DESCRIBE CommandDESCRIBE employees QuizIdentify the SELECT statements that execute successfully.SELECT first_name, last_name, job_id, salary*12 AS Yearly SalFROM employees; SELECT first_name, last_name, job_id,

18、 salary*12 yearly salFROM employees; SELECT first_name, last_name, job_id, salary AS yearly salFROM employees;SELECT first_name+last_name AS name, job_Id, salary*12 yearly salFROM employees;SummaryIn this lesson, you should have learned how to:Write a SELECT statement that:Returns all rows and columns from a tableReturns specified columns from a tableUses column aliases to disp