SlideShare a Scribd company logo
Retrieving Data Using  the SQL  SELECT  Statement
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 Differentiate between SQL statements and  i SQL*Plus commands
Capabilities of SQL  SELECT  Statements Selection Projection Table 1 Table 2 Table 1 Table 1 Join
Basic  SELECT  Statement SELECT  identifies the columns to be displayed. FROM  identifies the table containing those columns. SELECT *|{[DISTINCT]  column | expression  [ alias ],...} FROM  table;
Selecting All Columns SELECT * FROM  departments;
Selecting Specific Columns SELECT department_id, location_id FROM  departments;
Writing SQL Statements SQL statements are not case sensitive.  SQL statements can be 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  i SQL*Plus, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required if you execute multiple SQL statements.  In SQL*Plus, you are required to end each SQL statement with a semicolon (;).
Column Heading Defaults i SQL*Plus: Default heading alignment: Center Default heading display: Uppercase SQL*Plus: Character and Date column headings are left-aligned Number column headings are right-aligned Default heading display: Uppercase
Arithmetic Expressions Create expressions with number and date data by using arithmetic operators. Multiply * Divide / Subtract - Add + Description Operator
Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM  employees; …
Operator Precedence SELECT last_name, salary, 12*salary+100 FROM  employees; SELECT last_name, salary, 12*(salary+100) FROM  employees; 1 2 … …
Defining a Null Value A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as a zero or a blank space. SELECT last_name, job_id, salary, commission_pct FROM  employees; … …
Null Values  in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. SELECT last_name, 12*salary*commission_pct FROM  employees; … …
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 if it is case sensitive
Using Column Aliases SELECT last_name "Name" , salary*12 "Annual Salary" FROM  employees; SELECT last_name AS name, commission_pct comm FROM  employees; … …
Concatenation Operator A concatenation operator: Links columns or character strings to other columns  Is represented by two vertical bars (||) Creates a resultant column that is a character expression SELECT last_name||job_id AS "Employees" FROM  employees; …
Literal Character Strings A literal is a character, a number, or a date that is included in the  SELECT  statement. Date and character literal values must be enclosed by single quotation marks. Each character string is output once for each row returned.
Using Literal Character Strings … SELECT last_name ||' is a '||job_id  AS "Employee Details" FROM  employees;
Alternative Quote ( q ) Operator Specify your own quotation mark delimiter Choose any delimiter Increase readability and usability SELECT department_name ||  q'[, it's assigned Manager Id: ]'  || manager_id  AS "Department and Manager"  FROM departments; …
Duplicate Rows The default display of queries is all rows, including duplicate rows. SELECT department_id FROM  employees; SELECT DISTINCT department_id FROM  employees; … 1 2 …
SQL and  i SQL*Plus Interaction SQL statements Query results i SQL*Plus commands Client Formatted report Internet  browser Oracle server
SQL Statements Versus  i SQL*Plus Commands  SQL statements SQL  A language ANSI standard Keyword cannot be abbreviated Statements manipulate data and table definitions in the database i SQL*Plus An environment Oracle-proprietary Keywords can be abbreviated Commands do not allow manipulation of values in the database Runs on a browser Centrally loaded; does not have to be implemented on each machine i SQL*Plus commands
Overview of  i SQL*Plus After you log in to  i SQL*Plus, you can: Describe table structures Enter, execute, and edit SQL statements Save or append SQL statements to files  Execute or edit statements that are stored in saved script files
Logging In to  i SQL*Plus From your browser environment:
i SQL*Plus Environment 6 3 4 5 1 2 8 9 7
Displaying Table Structure Use the  i SQL*Plus  DESCRIBE  command to display the structure of a table: DESC[RIBE]  tablename
Displaying Table Structure DESCRIBE employees
Interacting with Script Files SELECT last_name, hire_date, salary FROM  employees; 1 2
Interacting with Script Files
Interacting with Script Files 1
Interacting with Script Files 2 3 D:\TEMP\emp_data.sql
i SQL*Plus History Page 1 2 3
i SQL*Plus History Page 3 4
Setting  i SQL*Plus Preferences 2 3 1
Setting the Output Location Preference 1 2
Summary 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 display more descriptive column headings Use the  i SQL*Plus environment to write, save, and execute SQL statements and  i SQL*Plus commands SELECT *|{[DISTINCT]  column|expression  [ alias ],...} FROM  table;
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 Using  i SQL*Plus
 
 
 
 
 
Ad

More Related Content

What's hot (20)

Writing Basic SQL SELECT Statements
Writing Basic SQL SELECT StatementsWriting Basic SQL SELECT Statements
Writing Basic SQL SELECT Statements
Salman Memon
 
Manipulating Data Oracle Data base
Manipulating Data Oracle Data baseManipulating Data Oracle Data base
Manipulating Data Oracle Data base
Salman Memon
 
Including Constraints -Oracle Data base
Including Constraints -Oracle Data base Including Constraints -Oracle Data base
Including Constraints -Oracle Data base
Salman Memon
 
SQL - DML and DDL Commands
SQL - DML and DDL CommandsSQL - DML and DDL Commands
SQL - DML and DDL Commands
Shrija Madhu
 
Sql oracle
Sql oracleSql oracle
Sql oracle
Md.Abu Noman Shuvo
 
Using single row functions to customize output
Using single row functions to customize outputUsing single row functions to customize output
Using single row functions to customize output
Syed Zaid Irshad
 
Introduction to-sql
Introduction to-sqlIntroduction to-sql
Introduction to-sql
BG Java EE Course
 
Cursores explicitos
Cursores explicitosCursores explicitos
Cursores explicitos
marvinarevalo83
 
SQL Queries Information
SQL Queries InformationSQL Queries Information
SQL Queries Information
Nishant Munjal
 
Aggregating Data Using Group Functions
Aggregating Data Using Group FunctionsAggregating Data Using Group Functions
Aggregating Data Using Group Functions
Salman Memon
 
Aggregate functions in SQL.pptx
Aggregate functions in SQL.pptxAggregate functions in SQL.pptx
Aggregate functions in SQL.pptx
SherinRappai
 
Group By, Order By, and Aliases in SQL
Group By, Order By, and Aliases in SQLGroup By, Order By, and Aliases in SQL
Group By, Order By, and Aliases in SQL
MSB Academy
 
SQL Overview
SQL OverviewSQL Overview
SQL Overview
Stewart Rogers
 
Les07 (using the set operators)
Les07 (using the set operators)Les07 (using the set operators)
Les07 (using the set operators)
Achmad Solichin
 
Explain the explain_plan
Explain the explain_planExplain the explain_plan
Explain the explain_plan
Maria Colgan
 
Complex queries in sql
Complex queries in sqlComplex queries in sql
Complex queries in sql
Charan Reddy
 
Sql DML Lenguaje de manipulación de datos
Sql DML Lenguaje de manipulación de datos Sql DML Lenguaje de manipulación de datos
Sql DML Lenguaje de manipulación de datos
josecuartas
 
introdution to SQL and SQL functions
introdution to SQL and SQL functionsintrodution to SQL and SQL functions
introdution to SQL and SQL functions
farwa waqar
 
SQL Tutorial - Basic Commands
SQL Tutorial - Basic CommandsSQL Tutorial - Basic Commands
SQL Tutorial - Basic Commands
1keydata
 
Presentation slides of Sequence Query Language (SQL)
Presentation slides of Sequence Query Language (SQL)Presentation slides of Sequence Query Language (SQL)
Presentation slides of Sequence Query Language (SQL)
Punjab University
 
Writing Basic SQL SELECT Statements
Writing Basic SQL SELECT StatementsWriting Basic SQL SELECT Statements
Writing Basic SQL SELECT Statements
Salman Memon
 
Manipulating Data Oracle Data base
Manipulating Data Oracle Data baseManipulating Data Oracle Data base
Manipulating Data Oracle Data base
Salman Memon
 
Including Constraints -Oracle Data base
Including Constraints -Oracle Data base Including Constraints -Oracle Data base
Including Constraints -Oracle Data base
Salman Memon
 
SQL - DML and DDL Commands
SQL - DML and DDL CommandsSQL - DML and DDL Commands
SQL - DML and DDL Commands
Shrija Madhu
 
Using single row functions to customize output
Using single row functions to customize outputUsing single row functions to customize output
Using single row functions to customize output
Syed Zaid Irshad
 
SQL Queries Information
SQL Queries InformationSQL Queries Information
SQL Queries Information
Nishant Munjal
 
Aggregating Data Using Group Functions
Aggregating Data Using Group FunctionsAggregating Data Using Group Functions
Aggregating Data Using Group Functions
Salman Memon
 
Aggregate functions in SQL.pptx
Aggregate functions in SQL.pptxAggregate functions in SQL.pptx
Aggregate functions in SQL.pptx
SherinRappai
 
Group By, Order By, and Aliases in SQL
Group By, Order By, and Aliases in SQLGroup By, Order By, and Aliases in SQL
Group By, Order By, and Aliases in SQL
MSB Academy
 
Les07 (using the set operators)
Les07 (using the set operators)Les07 (using the set operators)
Les07 (using the set operators)
Achmad Solichin
 
Explain the explain_plan
Explain the explain_planExplain the explain_plan
Explain the explain_plan
Maria Colgan
 
Complex queries in sql
Complex queries in sqlComplex queries in sql
Complex queries in sql
Charan Reddy
 
Sql DML Lenguaje de manipulación de datos
Sql DML Lenguaje de manipulación de datos Sql DML Lenguaje de manipulación de datos
Sql DML Lenguaje de manipulación de datos
josecuartas
 
introdution to SQL and SQL functions
introdution to SQL and SQL functionsintrodution to SQL and SQL functions
introdution to SQL and SQL functions
farwa waqar
 
SQL Tutorial - Basic Commands
SQL Tutorial - Basic CommandsSQL Tutorial - Basic Commands
SQL Tutorial - Basic Commands
1keydata
 
Presentation slides of Sequence Query Language (SQL)
Presentation slides of Sequence Query Language (SQL)Presentation slides of Sequence Query Language (SQL)
Presentation slides of Sequence Query Language (SQL)
Punjab University
 

Viewers also liked (20)

SQL select statement and functions
SQL select statement and functionsSQL select statement and functions
SQL select statement and functions
Vikas Gupta
 
1. dml select statement reterive data
1. dml select statement reterive data1. dml select statement reterive data
1. dml select statement reterive data
Amrit Kaur
 
SQL Basics
SQL BasicsSQL Basics
SQL Basics
Hammad Rasheed
 
Mca ii-dbms-u-iv-structured query language
Mca ii-dbms-u-iv-structured query languageMca ii-dbms-u-iv-structured query language
Mca ii-dbms-u-iv-structured query language
Rai University
 
RDBMS and SQL
RDBMS and SQLRDBMS and SQL
RDBMS and SQL
Hamza Shah
 
Microsoft SQL Azure - Building Applications Using SQL Azure Presentation
Microsoft SQL Azure - Building Applications Using SQL Azure PresentationMicrosoft SQL Azure - Building Applications Using SQL Azure Presentation
Microsoft SQL Azure - Building Applications Using SQL Azure Presentation
Microsoft Private Cloud
 
Les01
Les01Les01
Les01
Abrianto Nugraha
 
Daffodil International University. Object oriented programming JAVA if else s...
Daffodil International University. Object oriented programming JAVA if else s...Daffodil International University. Object oriented programming JAVA if else s...
Daffodil International University. Object oriented programming JAVA if else s...
Mon Mon
 
Switch statement
Switch statementSwitch statement
Switch statement
Patrick John McGee
 
Database Systems - SQL - DDL Statements (Chapter 3/3)
Database Systems - SQL - DDL Statements (Chapter 3/3)Database Systems - SQL - DDL Statements (Chapter 3/3)
Database Systems - SQL - DDL Statements (Chapter 3/3)
Vidyasagar Mundroy
 
Pure and Hybrid Seed Production
Pure and Hybrid Seed ProductionPure and Hybrid Seed Production
Pure and Hybrid Seed Production
Junaid Abbas
 
Normalisation student summary
Normalisation student summaryNormalisation student summary
Normalisation student summary
mary_ramsay
 
The Switch Statement in java
The Switch Statement in javaThe Switch Statement in java
The Switch Statement in java
Talha Saleem
 
Switch statements in Java
Switch statements  in JavaSwitch statements  in Java
Switch statements in Java
Jin Castor
 
Time-Based Blind SQL Injection
Time-Based Blind SQL InjectionTime-Based Blind SQL Injection
Time-Based Blind SQL Injection
matt_presson
 
Prabu's sql quries
Prabu's sql quries Prabu's sql quries
Prabu's sql quries
Prabu Cse
 
DML Commands
DML CommandsDML Commands
DML Commands
Randy Riness @ South Puget Sound Community College
 
Dml and ddl
Dml and ddlDml and ddl
Dml and ddl
baabtra.com - No. 1 supplier of quality freshers
 
SQL Data Manipulation
SQL Data ManipulationSQL Data Manipulation
SQL Data Manipulation
khalid alkhafagi
 
Oracle 11g PL/SQL notes
Oracle 11g PL/SQL notesOracle 11g PL/SQL notes
Oracle 11g PL/SQL notes
anilakduygu
 
SQL select statement and functions
SQL select statement and functionsSQL select statement and functions
SQL select statement and functions
Vikas Gupta
 
1. dml select statement reterive data
1. dml select statement reterive data1. dml select statement reterive data
1. dml select statement reterive data
Amrit Kaur
 
Mca ii-dbms-u-iv-structured query language
Mca ii-dbms-u-iv-structured query languageMca ii-dbms-u-iv-structured query language
Mca ii-dbms-u-iv-structured query language
Rai University
 
Microsoft SQL Azure - Building Applications Using SQL Azure Presentation
Microsoft SQL Azure - Building Applications Using SQL Azure PresentationMicrosoft SQL Azure - Building Applications Using SQL Azure Presentation
Microsoft SQL Azure - Building Applications Using SQL Azure Presentation
Microsoft Private Cloud
 
Daffodil International University. Object oriented programming JAVA if else s...
Daffodil International University. Object oriented programming JAVA if else s...Daffodil International University. Object oriented programming JAVA if else s...
Daffodil International University. Object oriented programming JAVA if else s...
Mon Mon
 
Database Systems - SQL - DDL Statements (Chapter 3/3)
Database Systems - SQL - DDL Statements (Chapter 3/3)Database Systems - SQL - DDL Statements (Chapter 3/3)
Database Systems - SQL - DDL Statements (Chapter 3/3)
Vidyasagar Mundroy
 
Pure and Hybrid Seed Production
Pure and Hybrid Seed ProductionPure and Hybrid Seed Production
Pure and Hybrid Seed Production
Junaid Abbas
 
Normalisation student summary
Normalisation student summaryNormalisation student summary
Normalisation student summary
mary_ramsay
 
The Switch Statement in java
The Switch Statement in javaThe Switch Statement in java
The Switch Statement in java
Talha Saleem
 
Switch statements in Java
Switch statements  in JavaSwitch statements  in Java
Switch statements in Java
Jin Castor
 
Time-Based Blind SQL Injection
Time-Based Blind SQL InjectionTime-Based Blind SQL Injection
Time-Based Blind SQL Injection
matt_presson
 
Prabu's sql quries
Prabu's sql quries Prabu's sql quries
Prabu's sql quries
Prabu Cse
 
Oracle 11g PL/SQL notes
Oracle 11g PL/SQL notesOracle 11g PL/SQL notes
Oracle 11g PL/SQL notes
anilakduygu
 
Ad

Similar to Les01 (retrieving data using the sql select statement) (20)

Oracle Baisc Tutorial
Oracle Baisc TutorialOracle Baisc Tutorial
Oracle Baisc Tutorial
bunny0143
 
01 basic orders
01   basic orders01   basic orders
01 basic orders
Soufiane Hakam
 
Retrieving data using the sql select statement
Retrieving data using the sql select statementRetrieving data using the sql select statement
Retrieving data using the sql select statement
Syed Zaid Irshad
 
Chinabankppt
ChinabankpptChinabankppt
Chinabankppt
newrforce
 
Beginers guide for oracle sql
Beginers guide for oracle sqlBeginers guide for oracle sql
Beginers guide for oracle sql
N.Jagadish Kumar
 
SQL- Introduction to MySQL
SQL- Introduction to MySQLSQL- Introduction to MySQL
SQL- Introduction to MySQL
Vibrant Technologies & Computers
 
Day1_SQL_1 for learning about sql cldarly.ppt
Day1_SQL_1 for learning about sql cldarly.pptDay1_SQL_1 for learning about sql cldarly.ppt
Day1_SQL_1 for learning about sql cldarly.ppt
consravs
 
DBMS week 2 hjghg hvgfhgf,3 BSCS 6th.pptx
DBMS week 2 hjghg hvgfhgf,3 BSCS 6th.pptxDBMS week 2 hjghg hvgfhgf,3 BSCS 6th.pptx
DBMS week 2 hjghg hvgfhgf,3 BSCS 6th.pptx
universalcomputer1
 
lect 2.pptx
lect 2.pptxlect 2.pptx
lect 2.pptx
HermanGaming
 
SQL SELECT Statement
 SQL SELECT Statement SQL SELECT Statement
SQL SELECT Statement
IslamicUniversityofL
 
Les08-Oracle
Les08-OracleLes08-Oracle
Les08-Oracle
suman1248
 
0808.pdf
0808.pdf0808.pdf
0808.pdf
ssuser0562f1
 
0808.pdf
0808.pdf0808.pdf
0808.pdf
ssuser0562f1
 
Lab1 select statement
Lab1 select statementLab1 select statement
Lab1 select statement
Balqees Al.Mubarak
 
Sql intro
Sql introSql intro
Sql intro
glubox
 
Lab
LabLab
Lab
neelam_rawat
 
SQL, consultas rapidas y sencillas, oracle
SQL, consultas rapidas y sencillas, oracleSQL, consultas rapidas y sencillas, oracle
SQL, consultas rapidas y sencillas, oracle
marycielocartagena73
 
SQL
SQLSQL
SQL
zekeLabs Technologies
 
Basic SQL Statments
Basic SQL StatmentsBasic SQL Statments
Basic SQL Statments
Umair Shakir
 
SQL-Tutorial.P1241112567Pczwq.powerpoint.pptx
SQL-Tutorial.P1241112567Pczwq.powerpoint.pptxSQL-Tutorial.P1241112567Pczwq.powerpoint.pptx
SQL-Tutorial.P1241112567Pczwq.powerpoint.pptx
BhupendraShahi6
 
Ad

More from Achmad Solichin (20)

Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)
Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)
Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)
Achmad Solichin
 
Materi Webinar Web 3.0 (16 Juli 2022)
Materi Webinar Web 3.0 (16 Juli 2022)Materi Webinar Web 3.0 (16 Juli 2022)
Materi Webinar Web 3.0 (16 Juli 2022)
Achmad Solichin
 
Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)
Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)
Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)
Achmad Solichin
 
Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)
Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)
Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)
Achmad Solichin
 
Webinar PHP-ID: Machine Learning dengan PHP
Webinar PHP-ID: Machine Learning dengan PHPWebinar PHP-ID: Machine Learning dengan PHP
Webinar PHP-ID: Machine Learning dengan PHP
Achmad Solichin
 
Webinar Data Mining dengan Rapidminer | Universitas Budi Luhur
Webinar Data Mining dengan Rapidminer | Universitas Budi LuhurWebinar Data Mining dengan Rapidminer | Universitas Budi Luhur
Webinar Data Mining dengan Rapidminer | Universitas Budi Luhur
Achmad Solichin
 
TREN DAN IDE RISET BIDANG DATA MINING TERBARU
TREN DAN IDE RISET BIDANG DATA MINING TERBARUTREN DAN IDE RISET BIDANG DATA MINING TERBARU
TREN DAN IDE RISET BIDANG DATA MINING TERBARU
Achmad Solichin
 
Metodologi Riset: Literature Review
Metodologi Riset: Literature ReviewMetodologi Riset: Literature Review
Metodologi Riset: Literature Review
Achmad Solichin
 
Materi Seminar: Artificial Intelligence dengan PHP
Materi Seminar: Artificial Intelligence dengan PHPMateri Seminar: Artificial Intelligence dengan PHP
Materi Seminar: Artificial Intelligence dengan PHP
Achmad Solichin
 
Percobaan Perpindahan Kalor melalui Konduksi, Konveksi dan Radiasi
Percobaan Perpindahan Kalor melalui Konduksi, Konveksi dan RadiasiPercobaan Perpindahan Kalor melalui Konduksi, Konveksi dan Radiasi
Percobaan Perpindahan Kalor melalui Konduksi, Konveksi dan Radiasi
Achmad Solichin
 
Metodologi Riset: Literature Review
Metodologi Riset: Literature ReviewMetodologi Riset: Literature Review
Metodologi Riset: Literature Review
Achmad Solichin
 
Depth First Search (DFS) pada Graph
Depth First Search (DFS) pada GraphDepth First Search (DFS) pada Graph
Depth First Search (DFS) pada Graph
Achmad Solichin
 
Breadth First Search (BFS) pada Graph
Breadth First Search (BFS) pada GraphBreadth First Search (BFS) pada Graph
Breadth First Search (BFS) pada Graph
Achmad Solichin
 
Binary Search Tree (BST) - Algoritma dan Struktur Data
Binary Search Tree (BST) - Algoritma dan Struktur DataBinary Search Tree (BST) - Algoritma dan Struktur Data
Binary Search Tree (BST) - Algoritma dan Struktur Data
Achmad Solichin
 
Computer Vision di Era Industri 4.0
Computer Vision di Era Industri 4.0Computer Vision di Era Industri 4.0
Computer Vision di Era Industri 4.0
Achmad Solichin
 
Seminar: Become a Reliable Web Programmer
Seminar: Become a Reliable Web ProgrammerSeminar: Become a Reliable Web Programmer
Seminar: Become a Reliable Web Programmer
Achmad Solichin
 
The Big 5: Future IT Trends
The Big 5: Future IT TrendsThe Big 5: Future IT Trends
The Big 5: Future IT Trends
Achmad Solichin
 
Modern PHP Developer
Modern PHP DeveloperModern PHP Developer
Modern PHP Developer
Achmad Solichin
 
Seminar: PHP Developer for Dummies
Seminar: PHP Developer for DummiesSeminar: PHP Developer for Dummies
Seminar: PHP Developer for Dummies
Achmad Solichin
 
Pertemuan 1 - Algoritma dan Struktur Data 1
Pertemuan 1 - Algoritma dan Struktur Data 1Pertemuan 1 - Algoritma dan Struktur Data 1
Pertemuan 1 - Algoritma dan Struktur Data 1
Achmad Solichin
 
Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)
Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)
Kuliah Umum - Tips Publikasi Jurnal SINTA untuk Mahasiswa Galau (6 Agustus 2022)
Achmad Solichin
 
Materi Webinar Web 3.0 (16 Juli 2022)
Materi Webinar Web 3.0 (16 Juli 2022)Materi Webinar Web 3.0 (16 Juli 2022)
Materi Webinar Web 3.0 (16 Juli 2022)
Achmad Solichin
 
Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)
Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)
Webinar: Kesadaran Keamanan Informasi (3 Desember 2021)
Achmad Solichin
 
Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)
Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)
Webinar PHP-ID: Mari Mengenal Logika Fuzzy (Fuzzy Logic)
Achmad Solichin
 
Webinar PHP-ID: Machine Learning dengan PHP
Webinar PHP-ID: Machine Learning dengan PHPWebinar PHP-ID: Machine Learning dengan PHP
Webinar PHP-ID: Machine Learning dengan PHP
Achmad Solichin
 
Webinar Data Mining dengan Rapidminer | Universitas Budi Luhur
Webinar Data Mining dengan Rapidminer | Universitas Budi LuhurWebinar Data Mining dengan Rapidminer | Universitas Budi Luhur
Webinar Data Mining dengan Rapidminer | Universitas Budi Luhur
Achmad Solichin
 
TREN DAN IDE RISET BIDANG DATA MINING TERBARU
TREN DAN IDE RISET BIDANG DATA MINING TERBARUTREN DAN IDE RISET BIDANG DATA MINING TERBARU
TREN DAN IDE RISET BIDANG DATA MINING TERBARU
Achmad Solichin
 
Metodologi Riset: Literature Review
Metodologi Riset: Literature ReviewMetodologi Riset: Literature Review
Metodologi Riset: Literature Review
Achmad Solichin
 
Materi Seminar: Artificial Intelligence dengan PHP
Materi Seminar: Artificial Intelligence dengan PHPMateri Seminar: Artificial Intelligence dengan PHP
Materi Seminar: Artificial Intelligence dengan PHP
Achmad Solichin
 
Percobaan Perpindahan Kalor melalui Konduksi, Konveksi dan Radiasi
Percobaan Perpindahan Kalor melalui Konduksi, Konveksi dan RadiasiPercobaan Perpindahan Kalor melalui Konduksi, Konveksi dan Radiasi
Percobaan Perpindahan Kalor melalui Konduksi, Konveksi dan Radiasi
Achmad Solichin
 
Metodologi Riset: Literature Review
Metodologi Riset: Literature ReviewMetodologi Riset: Literature Review
Metodologi Riset: Literature Review
Achmad Solichin
 
Depth First Search (DFS) pada Graph
Depth First Search (DFS) pada GraphDepth First Search (DFS) pada Graph
Depth First Search (DFS) pada Graph
Achmad Solichin
 
Breadth First Search (BFS) pada Graph
Breadth First Search (BFS) pada GraphBreadth First Search (BFS) pada Graph
Breadth First Search (BFS) pada Graph
Achmad Solichin
 
Binary Search Tree (BST) - Algoritma dan Struktur Data
Binary Search Tree (BST) - Algoritma dan Struktur DataBinary Search Tree (BST) - Algoritma dan Struktur Data
Binary Search Tree (BST) - Algoritma dan Struktur Data
Achmad Solichin
 
Computer Vision di Era Industri 4.0
Computer Vision di Era Industri 4.0Computer Vision di Era Industri 4.0
Computer Vision di Era Industri 4.0
Achmad Solichin
 
Seminar: Become a Reliable Web Programmer
Seminar: Become a Reliable Web ProgrammerSeminar: Become a Reliable Web Programmer
Seminar: Become a Reliable Web Programmer
Achmad Solichin
 
The Big 5: Future IT Trends
The Big 5: Future IT TrendsThe Big 5: Future IT Trends
The Big 5: Future IT Trends
Achmad Solichin
 
Seminar: PHP Developer for Dummies
Seminar: PHP Developer for DummiesSeminar: PHP Developer for Dummies
Seminar: PHP Developer for Dummies
Achmad Solichin
 
Pertemuan 1 - Algoritma dan Struktur Data 1
Pertemuan 1 - Algoritma dan Struktur Data 1Pertemuan 1 - Algoritma dan Struktur Data 1
Pertemuan 1 - Algoritma dan Struktur Data 1
Achmad Solichin
 

Recently uploaded (20)

Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptxReimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
John Moore
 
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
James Anderson
 
Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...
Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...
Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...
Mike Mingos
 
UiPath Automation Suite – Cas d'usage d'une NGO internationale basée à Genève
UiPath Automation Suite – Cas d'usage d'une NGO internationale basée à GenèveUiPath Automation Suite – Cas d'usage d'une NGO internationale basée à Genève
UiPath Automation Suite – Cas d'usage d'une NGO internationale basée à Genève
UiPathCommunity
 
Slack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teamsSlack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teams
Nacho Cougil
 
AI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of DocumentsAI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of Documents
UiPathCommunity
 
Viam product demo_ Deploying and scaling AI with hardware.pdf
Viam product demo_ Deploying and scaling AI with hardware.pdfViam product demo_ Deploying and scaling AI with hardware.pdf
Viam product demo_ Deploying and scaling AI with hardware.pdf
camilalamoratta
 
AI x Accessibility UXPA by Stew Smith and Olivier Vroom
AI x Accessibility UXPA by Stew Smith and Olivier VroomAI x Accessibility UXPA by Stew Smith and Olivier Vroom
AI x Accessibility UXPA by Stew Smith and Olivier Vroom
UXPA Boston
 
Cybersecurity Threat Vectors and Mitigation
Cybersecurity Threat Vectors and MitigationCybersecurity Threat Vectors and Mitigation
Cybersecurity Threat Vectors and Mitigation
VICTOR MAESTRE RAMIREZ
 
fennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solutionfennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solution
shallal2
 
DevOpsDays SLC - Platform Engineers are Product Managers.pptx
DevOpsDays SLC - Platform Engineers are Product Managers.pptxDevOpsDays SLC - Platform Engineers are Product Managers.pptx
DevOpsDays SLC - Platform Engineers are Product Managers.pptx
Justin Reock
 
Top-AI-Based-Tools-for-Game-Developers (1).pptx
Top-AI-Based-Tools-for-Game-Developers (1).pptxTop-AI-Based-Tools-for-Game-Developers (1).pptx
Top-AI-Based-Tools-for-Game-Developers (1).pptx
BR Softech
 
The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...
The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...
The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...
SOFTTECHHUB
 
May Patch Tuesday
May Patch TuesdayMay Patch Tuesday
May Patch Tuesday
Ivanti
 
AI-proof your career by Olivier Vroom and David WIlliamson
AI-proof your career by Olivier Vroom and David WIlliamsonAI-proof your career by Olivier Vroom and David WIlliamson
AI-proof your career by Olivier Vroom and David WIlliamson
UXPA Boston
 
IT484 Cyber Forensics_Information Technology
IT484 Cyber Forensics_Information TechnologyIT484 Cyber Forensics_Information Technology
IT484 Cyber Forensics_Information Technology
SHEHABALYAMANI
 
Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...
Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...
Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...
Raffi Khatchadourian
 
Q1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor PresentationQ1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor Presentation
Dropbox
 
An Overview of Salesforce Health Cloud & How is it Transforming Patient Care
An Overview of Salesforce Health Cloud & How is it Transforming Patient CareAn Overview of Salesforce Health Cloud & How is it Transforming Patient Care
An Overview of Salesforce Health Cloud & How is it Transforming Patient Care
Cyntexa
 
Shoehorning dependency injection into a FP language, what does it take?
Shoehorning dependency injection into a FP language, what does it take?Shoehorning dependency injection into a FP language, what does it take?
Shoehorning dependency injection into a FP language, what does it take?
Eric Torreborre
 
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptxReimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
John Moore
 
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
James Anderson
 
Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...
Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...
Optima Cyber - Maritime Cyber Security - MSSP Services - Manolis Sfakianakis ...
Mike Mingos
 
UiPath Automation Suite – Cas d'usage d'une NGO internationale basée à Genève
UiPath Automation Suite – Cas d'usage d'une NGO internationale basée à GenèveUiPath Automation Suite – Cas d'usage d'une NGO internationale basée à Genève
UiPath Automation Suite – Cas d'usage d'une NGO internationale basée à Genève
UiPathCommunity
 
Slack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teamsSlack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teams
Nacho Cougil
 
AI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of DocumentsAI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of Documents
UiPathCommunity
 
Viam product demo_ Deploying and scaling AI with hardware.pdf
Viam product demo_ Deploying and scaling AI with hardware.pdfViam product demo_ Deploying and scaling AI with hardware.pdf
Viam product demo_ Deploying and scaling AI with hardware.pdf
camilalamoratta
 
AI x Accessibility UXPA by Stew Smith and Olivier Vroom
AI x Accessibility UXPA by Stew Smith and Olivier VroomAI x Accessibility UXPA by Stew Smith and Olivier Vroom
AI x Accessibility UXPA by Stew Smith and Olivier Vroom
UXPA Boston
 
Cybersecurity Threat Vectors and Mitigation
Cybersecurity Threat Vectors and MitigationCybersecurity Threat Vectors and Mitigation
Cybersecurity Threat Vectors and Mitigation
VICTOR MAESTRE RAMIREZ
 
fennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solutionfennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solution
shallal2
 
DevOpsDays SLC - Platform Engineers are Product Managers.pptx
DevOpsDays SLC - Platform Engineers are Product Managers.pptxDevOpsDays SLC - Platform Engineers are Product Managers.pptx
DevOpsDays SLC - Platform Engineers are Product Managers.pptx
Justin Reock
 
Top-AI-Based-Tools-for-Game-Developers (1).pptx
Top-AI-Based-Tools-for-Game-Developers (1).pptxTop-AI-Based-Tools-for-Game-Developers (1).pptx
Top-AI-Based-Tools-for-Game-Developers (1).pptx
BR Softech
 
The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...
The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...
The No-Code Way to Build a Marketing Team with One AI Agent (Download the n8n...
SOFTTECHHUB
 
May Patch Tuesday
May Patch TuesdayMay Patch Tuesday
May Patch Tuesday
Ivanti
 
AI-proof your career by Olivier Vroom and David WIlliamson
AI-proof your career by Olivier Vroom and David WIlliamsonAI-proof your career by Olivier Vroom and David WIlliamson
AI-proof your career by Olivier Vroom and David WIlliamson
UXPA Boston
 
IT484 Cyber Forensics_Information Technology
IT484 Cyber Forensics_Information TechnologyIT484 Cyber Forensics_Information Technology
IT484 Cyber Forensics_Information Technology
SHEHABALYAMANI
 
Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...
Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...
Challenges in Migrating Imperative Deep Learning Programs to Graph Execution:...
Raffi Khatchadourian
 
Q1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor PresentationQ1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor Presentation
Dropbox
 
An Overview of Salesforce Health Cloud & How is it Transforming Patient Care
An Overview of Salesforce Health Cloud & How is it Transforming Patient CareAn Overview of Salesforce Health Cloud & How is it Transforming Patient Care
An Overview of Salesforce Health Cloud & How is it Transforming Patient Care
Cyntexa
 
Shoehorning dependency injection into a FP language, what does it take?
Shoehorning dependency injection into a FP language, what does it take?Shoehorning dependency injection into a FP language, what does it take?
Shoehorning dependency injection into a FP language, what does it take?
Eric Torreborre
 

Les01 (retrieving data using the sql select statement)

  • 1. Retrieving Data Using the SQL SELECT Statement
  • 2. 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 Differentiate between SQL statements and i SQL*Plus commands
  • 3. Capabilities of SQL SELECT Statements Selection Projection Table 1 Table 2 Table 1 Table 1 Join
  • 4. Basic SELECT Statement SELECT identifies the columns to be displayed. FROM identifies the table containing those columns. SELECT *|{[DISTINCT] column | expression [ alias ],...} FROM table;
  • 5. Selecting All Columns SELECT * FROM departments;
  • 6. Selecting Specific Columns SELECT department_id, location_id FROM departments;
  • 7. Writing SQL Statements SQL statements are not case sensitive. SQL statements can be 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 i SQL*Plus, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required if you execute multiple SQL statements. In SQL*Plus, you are required to end each SQL statement with a semicolon (;).
  • 8. Column Heading Defaults i SQL*Plus: Default heading alignment: Center Default heading display: Uppercase SQL*Plus: Character and Date column headings are left-aligned Number column headings are right-aligned Default heading display: Uppercase
  • 9. Arithmetic Expressions Create expressions with number and date data by using arithmetic operators. Multiply * Divide / Subtract - Add + Description Operator
  • 10. Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM employees; …
  • 11. Operator Precedence SELECT last_name, salary, 12*salary+100 FROM employees; SELECT last_name, salary, 12*(salary+100) FROM employees; 1 2 … …
  • 12. Defining a Null Value A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as a zero or a blank space. SELECT last_name, job_id, salary, commission_pct FROM employees; … …
  • 13. Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. SELECT last_name, 12*salary*commission_pct FROM employees; … …
  • 14. 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 if it is case sensitive
  • 15. Using Column Aliases SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees; SELECT last_name AS name, commission_pct comm FROM employees; … …
  • 16. Concatenation Operator A concatenation operator: Links columns or character strings to other columns Is represented by two vertical bars (||) Creates a resultant column that is a character expression SELECT last_name||job_id AS "Employees" FROM employees; …
  • 17. Literal Character Strings A literal is a character, a number, or a date that is included in the SELECT statement. Date and character literal values must be enclosed by single quotation marks. Each character string is output once for each row returned.
  • 18. Using Literal Character Strings … SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;
  • 19. Alternative Quote ( q ) Operator Specify your own quotation mark delimiter Choose any delimiter Increase readability and usability SELECT department_name || q'[, it's assigned Manager Id: ]' || manager_id AS "Department and Manager" FROM departments; …
  • 20. Duplicate Rows The default display of queries is all rows, including duplicate rows. SELECT department_id FROM employees; SELECT DISTINCT department_id FROM employees; … 1 2 …
  • 21. SQL and i SQL*Plus Interaction SQL statements Query results i SQL*Plus commands Client Formatted report Internet browser Oracle server
  • 22. SQL Statements Versus i SQL*Plus Commands SQL statements SQL A language ANSI standard Keyword cannot be abbreviated Statements manipulate data and table definitions in the database i SQL*Plus An environment Oracle-proprietary Keywords can be abbreviated Commands do not allow manipulation of values in the database Runs on a browser Centrally loaded; does not have to be implemented on each machine i SQL*Plus commands
  • 23. Overview of i SQL*Plus After you log in to i SQL*Plus, you can: Describe table structures Enter, execute, and edit SQL statements Save or append SQL statements to files Execute or edit statements that are stored in saved script files
  • 24. Logging In to i SQL*Plus From your browser environment:
  • 25. i SQL*Plus Environment 6 3 4 5 1 2 8 9 7
  • 26. Displaying Table Structure Use the i SQL*Plus DESCRIBE command to display the structure of a table: DESC[RIBE] tablename
  • 27. Displaying Table Structure DESCRIBE employees
  • 28. Interacting with Script Files SELECT last_name, hire_date, salary FROM employees; 1 2
  • 31. Interacting with Script Files 2 3 D:\TEMP\emp_data.sql
  • 32. i SQL*Plus History Page 1 2 3
  • 33. i SQL*Plus History Page 3 4
  • 34. Setting i SQL*Plus Preferences 2 3 1
  • 35. Setting the Output Location Preference 1 2
  • 36. Summary 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 display more descriptive column headings Use the i SQL*Plus environment to write, save, and execute SQL statements and i SQL*Plus commands SELECT *|{[DISTINCT] column|expression [ alias ],...} FROM table;
  • 37. 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 Using i SQL*Plus
  • 38.  
  • 39.  
  • 40.  
  • 41.  
  • 42.  

Editor's Notes

  • #3: Oracle Database 10 g : SQL Fundamentals I 1 - Objectives To extract data from the database, you need to use the structured query language (SQL) SELECT statement. You may need to restrict the columns that are displayed. This lesson describes all the SQL statements that are needed to perform these actions. You may want to create SELECT statements that can be used more than once. This lesson also covers the i SQL*Plus environment in which you execute SQL statements.
  • #4: Oracle Database 10 g : SQL Fundamentals I 1 - Capabilities of SQL SELECT Statements A SELECT statement retrieves information from the database. With a SELECT statement, you can use the following capabilities: Projection: Choose the columns in a table that are returned by a query. Choose as few or as many of the columns as needed. Selection: Choose the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved. Joining: Bring together data that is stored in different tables by specifying the link between them. SQL joins are covered in more detail in the lesson titled “Displaying Data from Multiple Tables.”
  • #5: Oracle Database 10 g : SQL Fundamentals I 1 - Basic SELECT Statement In its simplest form, a SELECT statement must include the following: A SELECT clause, which specifies the columns to be displayed A FROM clause, which identifies the table containing the columns that are listed in the SELECT clause In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns Note: Throughout this course, the words keyword , clause , and statement are used as follows: A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, ... is a clause. A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement.
  • #6: Oracle Database 10 g : SQL Fundamentals I 1 - Selecting All Columns of All Rows You can display all columns of data in a table by following the SELECT keyword with an asterisk ( * ). In the example in the slide, the department table contains four columns: DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , and LOCATION_ID . The table contains seven rows, one for each department. You can also display all columns in the table by listing all the columns after the SELECT keyword. For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the DEPARTMENTS table: SELECT department_id, department_name, manager_id, location_id FROM departments ;
  • #7: Oracle Database 10 g : SQL Fundamentals I 1 - Selecting Specific Columns of All Rows You can use the SELECT statement to display specific columns of the table by specifying the column names, separated by commas. The example in the slide displays all the department numbers and location numbers from the DEPARTMENTS table. In the SELECT clause, specify the columns that you want, in the order in which you want them to appear in the output. For example, to display location before department number going from left to right, you use the following statement: SELECT location_id, department_id FROM departments; …
  • #8: Oracle Database 10 g : SQL Fundamentals I 1 - Writing SQL Statements Using the following simple rules and guidelines, you can construct valid statements that are both easy to read and easy to edit: SQL statements are not case sensitive (unless indicated). SQL statements can be entered on one or many lines. • Keywords cannot be split across lines or abbreviated. Clauses are usually placed on separate lines for readability and ease of editing. • Indents should be used to make code more readable. Keywords typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase. Executing SQL Statements Using i SQL*Plus, click the Execute button to run the command or commands in the editing window. Using SQL*Plus, terminate the SQL statement with a semicolon and then press the Enter key to run the command.
  • #9: Oracle Database 10 g : SQL Fundamentals I 1 - Column Heading Defaults In i SQL*Plus, column headings are displayed in uppercase and centered. SELECT last_name, hire_date, salary FROM employees; You can override the column heading display with an alias. Column aliases are covered later in this lesson. …
  • #10: Oracle Database 10 g : SQL Fundamentals I 1 - Arithmetic Expressions You may need to modify the way in which data is displayed, or you may want to perform calculations or look at what-if scenarios. These are all possible using arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators. Arithmetic Operators The slide lists the arithmetic operators that are available in SQL. You can use arithmetic operators in any clause of a SQL statement (except the FROM clause). Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction operators only.
  • #11: Oracle Database 10 g : SQL Fundamentals I 1 - Using Arithmetic Operators The example in the slide uses the addition operator to calculate a salary increase of $300 for all employees. The slide also displays a SALARY+300 column in the output. Note that the resultant calculated column SALARY+300 is not a new column in the EMPLOYEES table; it is for display only. By default, the name of a new column comes from the calculation that generated it—in this case, salary+300 . Note: The Oracle server ignores blank spaces before and after the arithmetic operator. Operator Precedence If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators in an expression are of the same priority, then evaluation is done from left to right. You can use parentheses to force the expression that is enclosed by parentheses to be evaluated first. Rules of Precedence: Multiplication and division occur before addition and subtraction . Operators of the same priority are evaluated from left to right. Parentheses are used to override the default precedence or to clarify the statement .
  • #12: Oracle Database 10 g : SQL Fundamentals I 1 - Operator Precedence (continued) The first example in the slide displays the last name, salary, and annual compensation of employees. It calculates the annual compensation by multiplying the monthly salary by 12, plus a one-time bonus of $100. Note that multiplication is performed before addition. Note: Use parentheses to reinforce the standard order of precedence and to improve clarity. For example, the expression in the slide can be written as (12*salary)+100 with no change in the result. Using Parentheses You can override the rules of precedence by using parentheses to specify the desired order in which operators are to be executed. The second example in the slide displays the last name, salary, and annual compensation of employees. It calculates the annual compensation as follows: adding a monthly bonus of $100 to the monthly salary, and then multiplying that subtotal by 12. Because of the parentheses, addition takes priority over multiplication.
  • #13: Oracle Database 10 g : SQL Fundamentals I 1 - Null Values If a row lacks a data value for a particular column, that value is said to be null or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as a zero or a space. Zero is a number, and a space is a character. Columns of any data type can contain nulls. However, some constraints ( NOT NULL and PRIMARY KEY ) prevent nulls from being used in the column. In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales manager or sales representative can earn a commission. Other employees are not entitled to earn commissions. A null represents that fact.
  • #14: Oracle Database 10 g : SQL Fundamentals I 1 - Null Values in Arithmetic Expressions If any column value in an arithmetic expression is null, the result is null. For example, if you attempt to perform division by zero, you get an error. However, if you divide a number by null, the result is a null or unknown. In the example in the slide, employee King does not get any commission. Because the COMMISSION_PCT column in the arithmetic expression is null, the result is null. For more information, see “Basic Elements of SQL” in SQL Reference .
  • #15: Oracle Database 10 g : SQL Fundamentals I 1 - Column Aliases When displaying the result of a query, i SQL*Plus normally uses the name of the selected column as the column heading. This heading may not be descriptive and, therefore, maybe difficult to understand. You can change a column heading by using a column alias. Specify the alias after the column in the SELECT list using a space as a separator. By default, alias headings appear in uppercase. If the alias contains spaces or special characters (such as # or $), or if it is case sensitive, enclose the alias in double quotation marks (" ").
  • #16: Oracle Database 10 g : SQL Fundamentals I 1 - Column Aliases (continued) The first example displays the names and the commission percentages of all the employees. Notice that the optional AS keyword has been used before the column alias name. The result of the query is the same whether the AS keyword is used or not. Also notice that the SQL statement has the column aliases, name and comm , in lowercase, whereas the result of the query displays the column headings in uppercase. As mentioned in a previous slide, column headings appear in uppercase by default. The second example displays the last names and annual salaries of all the employees. Because Annual Salary contains a space, it has been enclosed in double quotation marks. Notice that the column heading in the output is exactly the same as the column alias.
  • #17: Oracle Database 10 g : SQL Fundamentals I 1 - Concatenation Operator You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the concatenation operator (||). Columns on either side of the operator are combined to make a single output column. In the example, LAST_NAME and JOB_ID are concatenated, and they are given the alias Employees . Notice that the employee last name and job code are combined to make a single output column. The AS keyword before the alias name makes the SELECT clause easier to read. Null Values with the Concatenation Operator If you concatenate a null value with a character string, the result is a character string. LAST_NAME || NULL results in LAST_NAME .
  • #18: Oracle Database 10 g : SQL Fundamentals I 1 - Literal Character Strings A literal is a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. It is printed for each row returned. Literal strings of free-format text can be included in the query result and are treated the same as a column in the SELECT list. Date and character literals must be enclosed by single quotation marks ( ' ' ); number literals need not be so enclosed.
  • #19: Oracle Database 10 g : SQL Fundamentals I 1 - Literal Character Strings (continued) The example in the slide displays last names and job codes of all employees. The column has the heading Employee Details. Notice the spaces between the single quotation marks in the SELECT statement. The spaces improve the readability of the output. In the following example, the last name and salary for each employee are concatenated with a literal to give the returned rows more meaning: SELECT last_name ||': 1 Month salary = '||salary Monthly FROM employees; …
  • #20: Alternative Quote ( q ) Operator Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote ( q ) operator and choose your own quotation mark delimiter. You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, the brackets [] are used as the quotation mark delimiter. The string between the brackets delimiters is interpreted as a literal character string.
  • #21: Oracle Database 10 g : SQL Fundamentals I 1 - Duplicate Rows Unless you indicate otherwise, i SQL*Plus displays the results of a query without eliminating duplicate rows. The first example in the slide displays all the department numbers from the EMPLOYEES table. Notice that the department numbers are repeated. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. In the second example in the slide, the EMPLOYEES table actually contains 20 rows, but there are only seven unique department numbers in the table. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns. SELECT DISTINCT department_id, job_id FROM employees;
  • #22: Oracle Database 10 g : SQL Fundamentals I 1 - SQL and i SQL*Plus SQL is a command language for communication with the Oracle server from any tool or application. Oracle SQL contains many extensions. iSQL*Plus is an Oracle tool that recognizes and submits SQL statements to the Oracle server for execution and contains its own command language. Features of SQL Can be used by a range of users, including those with little or no programming experience Is a nonprocedural language Is an English-like language Features of i SQL*Plus Is accessed from a browser Accepts SQL statements Provides online editing for modifying SQL statements Controls environmental settings Formats query results into a basic report Accesses local and remote databases
  • #23: Oracle Database 10 g : SQL Fundamentals I 1 - SQL and i SQL*Plus (continued) The following table compares SQL and i SQL*Plus:
  • #24: Oracle Database 10 g : SQL Fundamentals I 1 - i SQL*Plus i SQL*Plus is an environment in which you can do the following: Execute SQL statements to retrieve, modify, add, and remove data from the database Format, perform calculations on, store, and print query results in the form of reports Create script files to store SQL statements for repeated use in the future i SQL*Plus commands can be divided into the following main categories:
  • #25: Oracle Database 10 g : SQL Fundamentals I 1 - Logging In to i SQL*Plus To log in from a browser environment: 1. Start the browser. 2. Enter the URL address of the i SQL*Plus environment. 3. On the Login page, enter appropriate values in the Username, Password, and Connect Identifier fields.
  • #26: Oracle Database 10 g : SQL Fundamentals I 1 - i SQL*Plus Environment In the browser, the i SQL*Plus Workspace page has several key areas: 1. Text box: Area where you type the SQL statements and i SQL*Plus commands 2. Execute button: Click to execute the statements and commands in the text box 3. Load Script button: Brings up a form where you can identify a path and file name or a URL that contains SQL, PL/SQL, or SQL*Plus commands and load them into the text box 4. Save Script button: Saves the contents of the text box to a file 5. Cancel button: Stops the execution of the command in the text box 6. Clear Screen button: Click to clear text from the text box 7. Logout icon: Click to end the i SQL*Plus session and return to the i SQL*Plus Login page 8. Preferences icon: Click to change your interface configuration, system configuration, or password 9. Help icon: Provides access to i SQL*Plus help documentation
  • #27: Oracle Database 10 g : SQL Fundamentals I 1 - Displaying the Table Structure In i SQL*Plus, you can display the structure of a table by using the DESCRIBE command. The command displays the column names and data types, and it shows you whether a column must contain data (that is, whether the column has a NOT NULL constraint). In the syntax, tablename is the name of any existing table, view, or synonym that is accessible to the user.
  • #28: Oracle Database 10 g : SQL Fundamentals I 1 - Displaying the Table Structure (continued) The example in the slide displays the information about the structure of the EMPLOYEES table. In the resulting display, Null? indicates that the values for this column maybe unknown. NOT NULL indicates that a column must contain data. Type displays the data type for a column. The data types are described in the following table:
  • #29: Oracle Database 10 g : SQL Fundamentals I 1 - Interacting with Script Files Placing Statements and Commands into a Text Script File You can save commands and statements from the text box in i SQL*Plus to a text script file as follows: 1. Type the SQL statements in the text box in i SQL*Plus. 2. Click the Save Script button. This opens the Windows File Save dialog box. Identify the name of the file. Note that the file extension defaults to . sql . You can change the file type to a text file or save it as a . sql file.
  • #30: Interacting with Script Files (continued) In the example shown, the SQL SELECT statement typed in the text box is saved to a file named emp_data.sql . You can choose the type of the file, name of the file, and location of where you want to save the script file.
  • #31: Oracle Database 10 g : SQL Fundamentals I 1 - Interacting with Script Files (continued) Using Statements and Commands from a Script File in i SQL*Plus You can use previously saved commands and statements from a script file in i SQL*Plus as follows: 1. Click the Load Script button. This opens a form where you can enter the name of the file or a URL containing the SQL, PL/SQL, or SQL*Plus commands that you want to enter in the text box.
  • #32: Interacting with Script Files (continued) 2. Enter the script name and path, or the URL location. Or you can click the Browse button to find the script name and location. 3. Click the Load button to bring the contents of the file or URL location into the text box.
  • #33: Running Previous Statements The History page in i SQL*Plus lets you execute previously run statements in your session. The History page shows your most recently run SQL statements and i SQL*Plus commands. To rerun the statements: 1. Select the statement that you want to execute. 2. Click the Load button. Note You can control the number of statements that are shown on the History page with Preferences settings. You can choose to delete selected statements by clicking the Delete button.
  • #34: Running Previous Statements (continued) 3. Return to the Workspace page. 4. Click the Execute button to run the commands that have been loaded into the text box.
  • #35: i SQL*Plus Preferences 1. You can set preferences for your i SQL*Plus session by clicking the Preferences icon. 2. The preferences are divided into categories. You can set preferences for script formatting, script execution, and database administration, and you can change your password. 3. When you choose a preference category, a form is displayed that lets you set the preferences for that category.
  • #36: Oracle Database 10 g : SQL Fundamentals I 1 - Changing the Output Location You can send the results that are generated by a SQL statement or i SQL*Plus command to the screen (the default), a file, or another browser window. On the Preferences page: 1. Select an Output Location option. 2. Click the Apply button.
  • #37: Oracle Database 10 g : SQL Fundamentals I 1 - SELECT Statement In this lesson, you should have learned how to retrieve data from a database table with the SELECT statement. SELECT *|{[DISTINCT] column [ alias ],...} FROM table ; In the syntax: SELECT is a list of one or more columns * selects all columns DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings FROM table specifies the table containing the columns i SQL*Plus i SQL*Plus is an execution environment that you can use to send SQL statements to the database server and to edit and save SQL statements. Statements can be executed from the SQL prompt or from a script file.
  • #38: Oracle Database 10 g : SQL Fundamentals I 1 - Practice 1: Overview This is the first of many practices in this course. The solutions (if you require them) can be found in Appendix A. Practices are intended to cover all topics that are presented in the corresponding lesson. Note the following location for the lab files: E:labsSQL1labs If you are asked to save any lab files, save them at this location. To start i SQL*Plus, start your browser. You need to enter a URL to access i SQL*Plus. The URL requires the host name, which your instructor will provide. Enter the following command, replacing the host name with the value that your instructor provides: http://<HOSTNAME:5560>/isqlplus In any practice, there maybe exercises that are prefaced with the phrases “If you have time” or “If you want an extra challenge.” Work on these exercises only if you have completed all other exercises in the allocated time and would like a further challenge to your skills. Perform the practices slowly and precisely. You can experiment with saving and running command files. If you have any questions at any time, ask your instructor.
  • #39: Oracle Database 10 g : SQL Fundamentals I 1 - Practice 1 Part 1 Test your knowledge: 1. Initiate an i SQL*Plus session using the user ID and password that are provided by the instructor. 2. i SQL*Plus commands access the database. True/False 3. The following SELECT statement executes successfully: SELECT last_name, job_id, salary AS Sal FROM employees; True/False 4. The following SELECT statement executes successfully: SELECT * FROM job_grades; True/False 5. There are four coding errors in the following statement. Can you identify them? SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; Part 2 Note the following location for the lab files: E:labsSQL1labs If you are asked to save any lab files, save them at this location. To start iSQL*Plus, start your browser. You need to enter a URL to access iSQL*Plus. The URL requires the host name, which your instructor will provide. Enter the following command, replacing the host name with the value that your instructor provides: http://<HOSTNAME:5560>/isqlplus You have been hired as a SQL programmer for Acme Corporation. Your first task is to create some reports based on data from the Human Resources tables. 6. Your first task is to determine the structure of the DEPARTMENTS table and its contents.
  • #40: Oracle Database 10 g : SQL Fundamentals I 1 - Practice 1 (continued) 7. You need to determine the structure of the EMPLOYEES table. The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column. Save your SQL statement to a file named lab_01_07.sql so that you can dispatch this file to the HR department.
  • #41: Practice 1 (continued) Test your query in the lab_01_07.sql file to ensure that it runs correctly. 9. The HR department needs a query to display all unique job codes from the EMPLOYEES table. …
  • #42: Oracle Database 10 g : SQL Fundamentals I 1 - Practice 1 (continued) Part 3 If you have time, complete the following exercises: 10. The HR department wants more descriptive column headings for its report on employees. Copy the statement from lab_01_07.sql to the i SQL*Plus text box. Name the column headings Emp # , Employee , Job , and Hire Date , respectively. Then run your query again. 11. The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID (separated by a comma and space) and name the column Employee and Title . … …
  • #43: Oracle Database 10 g : SQL Fundamentals I 1 - Practice 1 (continued) If you want an extra challenge, complete the following exercise: 12. To familiarize yourself with the data in the EMPLOYEES table, create a query to display all the data from that table. Separate each column output by a comma. Name the column title THE_OUTPUT . …
  翻译: