Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.
-
Upload
daniela-scott -
Category
Documents
-
view
233 -
download
0
Transcript of Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.
![Page 1: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/1.jpg)
Introduction to SQL
PART Ⅰ
![Page 2: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/2.jpg)
1-
第一讲
Writing Basic SQL SELECT Statements
![Page 3: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/3.jpg)
Objectives
After completing this lesson, you should be able to
do the following:
• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
![Page 4: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/4.jpg)
Tables used in the Course
Refer to appendix A
![Page 5: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/5.jpg)
Basic SELECT Statement
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
• SELECT identifies what columns• FROM identifies which table
![Page 6: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/6.jpg)
SELECT *FROM departments;
Selecting All Columns
![Page 7: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/7.jpg)
Selecting Specific Columns
SELECT department_id, location_idFROM departments;
![Page 8: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/8.jpg)
Writing SQL Statements
• SQL statements are not case sensitive.
• SQL statements can be on one or more lines.
• Keywords cannot be abbreviated or splitacross lines.
• Clauses are usually placed on separate lines.
• Indents are used to enhance readability.
![Page 9: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/9.jpg)
Arithmetic Expressions
Create expressions with number and date data by using arithmetic operators.
Operator
+
-
*
/
Description
Add
Subtract
Multiply
Divide
![Page 10: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/10.jpg)
Using Arithmetic Operators
SELECT last_name, salary, salary + 300FROM employees;
…
![Page 11: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/11.jpg)
Operator Precedence
• Multiplication and division take priority over addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to force prioritized evaluation and to clarify statements.
**** //// ++++ ____
![Page 12: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/12.jpg)
Operator Precedence
SELECT last_name, salary, 12*salary+100FROM employees;
…
![Page 13: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/13.jpg)
Using Parentheses
SELECT last_name, salary, 12*(salary+100)FROM employees;
…
![Page 14: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/14.jpg)
Defining a Null Value
• A null is a value that is unavailable, unassigned, unknown, or inapplicable.
• A null is not the same as zero or a blank space.
SELECT last_name, job_id, salary, commission_pctFROM employees;
…
…
![Page 15: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/15.jpg)
SELECT last_name, 12*salary*commission_pctFROM employees;
Null Values in Arithmetic Expressions
Arithmetic expressions containing a null value evaluate to null.
…
…
![Page 16: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/16.jpg)
Defining a Column Alias
A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name - there can also be the optional AS keyword between the column name and alias
• Requires double quotation marks if it contains spaces or special characters or is case sensitive
![Page 17: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/17.jpg)
Using Column Aliases
SELECT last_name "Name", salary*12 "Annual Salary"FROM employees;
SELECT last_name AS name, commission_pct commFROM employees;
…
…
![Page 18: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/18.jpg)
Concatenation Operator
A concatenation operator:
• Concatenates columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression
![Page 19: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/19.jpg)
Using the Concatenation Operator
SELECT last_name||job_id AS "Employees"FROM employees;
…
![Page 20: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/20.jpg)
Literal Character Strings
• A literal is a character, a number, or a date included in the SELECT list.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for eachrow returned.
![Page 21: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/21.jpg)
Using Literal Character Strings
SELECT last_name ||' is a '||job_id AS "Employee Details"FROM employees;
…
![Page 22: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/22.jpg)
Duplicate Rows
The default display of queries is all rows, including duplicate rows.
SELECT department_idFROM employees;
SELECT department_idFROM employees;
…
![Page 23: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/23.jpg)
Eliminating Duplicate Rows
Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.
SELECT DISTINCT department_idFROM employees;
![Page 24: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/24.jpg)
Displaying Table Structure
Use the iSQL*Plus DESCRIBE command to display the structure of a table.
DESC[RIBE] tablenameDESC[RIBE] tablename
![Page 25: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/25.jpg)
Displaying Table Structure
DESCRIBE employeesDESCRIBE employees
![Page 26: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/26.jpg)
Summary
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
In this lesson, you should have learned how to:
• Write a SELECT statement that:
– Returns all rows and columns from a table
– Returns specified columns from a table
– Uses column aliases to give descriptive column headings
![Page 27: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/27.jpg)
Practice 1 Overview
This practice covers the following topics:
• Selecting all data from different tables
• Describing the structure of tables
• Performing arithmetic calculations and specifying column names
![Page 28: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/28.jpg)
Practice 1
Practice 1
![Page 29: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/29.jpg)
Appendix A
• COUNTRIES table– DESC countries
– SELECT * FROM countries;
![Page 30: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/30.jpg)
Appendix A
• DEPARTMENTS table– DESC departments
![Page 31: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/31.jpg)
Appendix A
– SELECT * FROM departments;
![Page 32: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/32.jpg)
Appendix A
• EMPLOYEES table– DESC employees
![Page 33: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/33.jpg)
– SELECT * FROM employees;
![Page 34: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/34.jpg)
continue
![Page 35: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/35.jpg)
Appendix A
• JOBS table– DESC jobs
![Page 36: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/36.jpg)
Appendix A
– SELECT * FROM jobs;
![Page 37: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/37.jpg)
Appendix A
• JOB_GRADES table– DESC job_grades
– SELECT * FROM job_grades;
![Page 38: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/38.jpg)
Appendix A
• JOB_HISTORY table– DESC job_history
![Page 39: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/39.jpg)
Appendix A
– SELECT * FROM job_history;
![Page 40: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/40.jpg)
Appendix A
• LOCATIONS table– DESC locations
![Page 41: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/41.jpg)
Appendix A
– SELECT * FROM locations;
![Page 42: Introduction to SQL PART Ⅰ. 1-1- 第一讲 Writing Basic SQL SELECT Statements.](https://reader035.fdocument.pub/reader035/viewer/2022062221/56649f1f5503460f94c3715d/html5/thumbnails/42.jpg)
Appendix A
• REGIONS table– DESC regions
– SELECT * FROM regions;