DBA_Training_Surin

Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |

Agenda  Day 1 : • Introduction – Introduction to Database Systems – Database Management Systems – Oracle Database 11g – Relational Database Concept Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 3 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 1 : • Retrieving Data Using the SQL SELECT Statement –Basic SELECT Statement –Using Arithmetic Operators and Arithmetic Expressions –Column Aliases –Use of concatenation operator, literal character strings, and the DISTINCT keyword Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 4 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 1 : • Restricting and Sorting Data –Limiting rows with:  The WHERE clause  The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL operators  Logical conditions using AND, OR operators –Sorting rows using the ORDER BY clause –Workshop #1 Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 5 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 1 : • Using Single-Row Functions to Customize Output –Single-row SQL functions –Character functions –Number functions –General functions: o NVL - NVL2 - NULLIF Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 6 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 2 : • Reporting Aggregated Data Using the Group Functions –Group functions: o Types and syntax o Use AVG, SUM, MIN, MAX, COUNT o Use DISTINCT keyword within group functions –Grouping rows: o GROUP BY clause and HAVING clause –Workshop #2 Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 7 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 2 : • Displaying Data from Multiple Tables –Obtaining Data from Multiple Tables –Joining Tables with Join Syntax –Creating Joins with the ON Clause – Self-join –OUTER join –Cartesian product - Workshop #3 Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 8 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 3 : • Using Subqueries to Solve Queries –Subquery : Types, syntax, and guidelines –Single-row subqueries –Multiple-row subqueries –Null values in a subquery –Workshop #4 Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 9 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 3 : • Create Database Objects –Using DDL Statements to Create Tables –Manipulating Data  INSERT Statement syntax  UPDATE Statement syntax  DELETE Statement syntax Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 10 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Agenda  Day 3 : –Workshop #5 –Why to use Oracle Database? –Q & A , Closing speech. Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 11 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Introduction Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 12 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Introduction to Database Systems Application Database Approach Application DATABASE Application Integrated data Application which has Relationship Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 13 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Introduction to Database Systems Database System Input Device People Output Device Data Application Program Information People Processing People Input Data Retrieve Data Analysis Data People DATABASE Integrated data which DBMS has Relationship Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 14 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Introduction to Database Systems DBMS : Database Management Systems Application S DBMS DATABASE Program Q L Application User Program Application Program Application Program Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 15 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Oracle Database 11g Infrastructure Information Application Grids Management Development Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Oracle Database 11g Manageability High availability Performance Security Information integration Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 17 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Oracle Database 11g Database Management DBMS RDBMS Relational Database System Management System Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 18 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Relational Database : ฐานข้อมลู เชิงสัมพนั ธ์ • A relational database is a collection of relations or two-dimensional tables. Degree คือ จานวนของ Column (Attribute) Attribute Cardinality คือ จานวน Table ของ Tuple (Relation) Row (Tuple) Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 19 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Relational Database : ฐานข้อมลู เชิงสัมพนั ธ์ • A relational database is a collection of relations or two-dimensional tables. Oracle server Table name: EMPLOYEES Table name: DEPARTMENTS …… Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 20 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Data Models Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 21 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Primary Key (PK) and Foreign Key (FK) – Each row of data in a table is uniquely identified by a primary key. – You can logically relate data from multiple tables using foreign keys. Table name: DEPARTMENTS Table name: EMPLOYEES … Foreign key Primary key Primary key Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 22 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Primary Key (PK) • Primary Key Constraint – No duplicate data in each row. PK ห้ามซ้า (Key Constraint) – Primary key is not null. PK ห้ามเป็นค่าว่าง (Entity Integrity Constraint) Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 23 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Primary Key (PK) PK ชอื่ -สกุล รหัส PK ช่ือคณะ คณะ นกั ศึกษา คณะ 01 รหสั ประจาตัว 02 รหสั คณะ 03 PK 5914110089 น.ส.ปานวาด เหมกลุ 01 01 เทคโนโลยีสารสนเทศ - ห้ามซ้า 5912110090 น.ส.จิตรลดา หัสดิน 02 วิศวกรรมศาสตร์ - ห้ามเป็น 5913110056 น.ส.ธดิ ารตั น์ ธนสารกุล 03 บริหาร Null 5914110067 น.ส.นภาทิพย์ ปิน่ แกว้ 04 วทิ ยาศาสตร์ Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 24 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Primary Key (PK) and Foreign Key (FK) PK ชอ่ื -สกุล FK PK ช่อื คณะ นักศึกษา รหสั คณะ คณะ รหสั ประจาตัว 01 รหัสคณะ 02 PK 5914110089 น.ส.ปานวาด เหมกุล 03 01 เทคโนโลยสี ารสนเทศ - ห้ามซา้ 5912110090 น.ส.จิตรลดา หสั ดิน 01 02 วศิ วกรรมศาสตร์ - ห้ามเป็น 5913110056 น.ส.ธดิ ารัตน์ ธนสารกลุ 03 บริหาร Null 5914110067 น.ส.นภาทิพย์ ปน่ิ แกว้ 04 วิทยาศาสตร์ Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 25 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Foreign Key (FK) • Foreign Key Constraint (Referential Integrity Constraint) – Foreign Key (FK) เป็นตวั เชอื่ มตาราง ซึง่ ค่าของ FK จะต้องเป็นค่าที่มีอยู่จริงใน PK ทมี่ นั ไป อา้ งองิ ถึง หรอื เปน็ คา่ Null ได้ Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 26 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Foreign Key (FK) นกั ศึกษา Foreign Key รหัส คณะ ช่อื คณะ คณะ รหสั ประจาตัว ช่ือ-สกลุ รหสั คณะ 01 5914110089 น.ส.ปานวาด เหมกลุ 02 01 เทคโนโลยสี ารสนเทศ 5912110090 น.ส.จติ รลดา หัสดิน Null 02 วิศวกรรมศาสตร์ 5913110056 น.ส.ธดิ ารัตน์ ธนสารกลุ 05 03 บรหิ าร 5914110067 น.ส.นภาทิพย์ ปิ่นแก้ว 04 วิทยาศาสตร์ คา่ วา่ ง (Null value) เป็ นไปไมไ่ ด้ Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 27 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Structured Query Language (SQL) • Structured query language (SQL) is : – The ANSI standard language for operating relational databases. – Efficient, easy to learn, and use. – Functionally complete (With SQL, you can define, retrieve, and manipulate data in the tables.) Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 28 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Structured Query Language (SQL) • Using SQL to Query Your Database SELECT department_name FROM departments; Oracle Server Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 29 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Structured Query Language (SQL) IUSNEPSLDEEARCTTTE Data manipulation language (DML) DMEELREGTEE CARLTEEARTE Data definition language (DDL) RDTCERORNUOMANPMMCEAENTTE GRANT Data control language (DCL) REVOKE Transaction control RSCAOOVLMELMPBOAITICNKT Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 30 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Tables Used in the Course Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 31 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Retrieving Data Using the SQL SELECT Statement Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 32 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Basic SELECT Statement SELECT column 1, column 2, … | * FROM table; – SELECT identifies the columns to be displayed. – FROM identifies the table containing those columns. Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 33 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Selecting Specific Columns SELECT department_id, location_id FROM departments; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 34 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Selecting All Columns SELECT * FROM departments; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 35 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Using Arithmetic Operators and Arithmetic Expressions • Create expressions with number and date data by using arithmetic operators. Operator Description + Add - Subtract * Multiply / Divide Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 36 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM employees; … Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 37 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Operators Precedence 1 SELECT last_name, salary, 12*salary+100 FROM employees; … 2 SELECT last_name, salary, 12*(salary+100) FROM employees; … Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 38 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Column Alias SELECT last_name AS name, commission_pct comm FROM employees; … SELECT last_name \"Name\" , salary*12 \"Annual Salary\" FROM employees; … Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 39 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Concatenation Operators • 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 first_name||last_name AS \"Name\" FROM employees; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 40 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Concatenation Operators SELECT first_name|| ' ' || last_name AS \"Name\" FROM employees; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 41 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Duplicate Rows • The default display of queries is all rows, including duplicate rows. SELECT department_id 1 FROM employees; … 2 SELECT DISTINCT department_id FROM employees; … Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 42 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; – SELECT identifies the columns to be displayed. – FROM identifies the table containing those columns. Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 43 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Restricting and Sorting Data Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 44 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Restricting and Sorting Data • Limit the rows that are retrieved by a query EMPLOYEES … “retrieve all employees in department 90” Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 45 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Limiting the Rows That Are Selected • Limit the rows that are retrieved by a query – Restrict the rows that are returned by using the WHERE clause: SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)]; – The WHERE clause follows the FROM clause. Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 46 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Using the WHERE Clause SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 47 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Character Strings and Dates – Character strings and date values are enclosed with single quotation marks. – Character values are case-sensitive and date values are format-sensitive. – The default date display format is DD-MON-RR. SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'King' ; SELECT last_name FROM employees WHERE hire_date = '17-FEB-96' ; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 48 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Comparison Operators Operator Meaning = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not Equal to Between two values (inclusive) BETWEEN ...AND... Match any of a list of values IN(set) Match a character pattern Is a null value LIKE IS NULL Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 49 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted

Using Comparison Operators SELECT last_name, salary FROM employees WHERE salary <= 3000 ; Sasiwimol Hongma. Information Science and Technology, Mahanakorn University of Technology. 50 Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted