4 IT 332 Database System 2 Information Technology

4 IT 332 Database System 2 อ. ดร. วรรณวมล นาด Information Technology www. itsci. mju. ac. th Information Technology : : MJU 1 The best education solution

������ SQL FUNCTIONS Information Technology : : MJU 2 The best education solution

Conversion Functions Data type conversion Implicit data type conversion Information Technology : : MJU Explicit data type conversion The best education solution

Implicit Data Type Conversion • Oracle server สามารถเปลยนแปลงคาไดโดยอตโนมต From To VARCHAR 2 or CHAR NUMBER VARCHAR 2 or CHAR DATE From To NUMBER VARCHAR 2 or CHAR DATE VARCHAR 2 or CHAR Information Technology : : MJU ดงน The 4 The best education solution

Explicit Data Type Conversion TO_NUMBER CHARACTER TO_CHAR Information Technology : : MJU TO_DATE TO_CHAR The best education solution

Elements of the Date Format Model Element Result YYYY Full year in numbers YEAR Year spelled out (in English) MM Two-digit value for the month MONTH Full name of the month MON Three-letter abbreviation of the month DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week DD Numeric day of the month Information Technology : : MJU The best education solution

Elements of the Date Format Model • รปแบบของเวลา HH 24: MI: SS AM • การแทรกขอความเขาไปในรปแบบ double quotation marks DD "of" MONTH 15: 45: 32 PM โดยอยภายในเครองหมาย 12 of OCTOBER • การสะกดคำตอทายของตวเลข ddspth Information Technology : : MJU fourteenth The 9 The best education solution

Using the TO_CHAR Function with Dates SELECT last_name, TO_CHAR(hire_date, 'fm. DD Month YYYY') AS HIREDATE FROM employees; … Information Technology : : MJU The best education solution

Using the TO_CHAR Function with Numbers TO_CHAR(number, 'format_model') สามารถจดการเปลยนชนดขอมล Character โดยสามารถใชคำสง Number ใหเปนชนดขอมล TO_CHAR function Element 9 Result 0 Forces a zero to be displayed $ Places a floating dollar sign L Uses the floating local currency symbol . Prints a decimal point , Prints a comma as a thousands indicator Information Technology : : MJU Represents a number The 12 The best education solution

Using the TO_CHAR Function with Numbers SELECT TO_CHAR(salary, '$99, 999. 00') SALARY FROM employees WHERE last_name = 'Ernst'; Information Technology : : MJU The best education solution

Nesting Functions • ฟงกชนสามารถทจะเรยกใช ซอน ๆ กนได • การประมวลผลจะเรยกจากระดบขางในกอน F 3(F 2(F 1(col, arg 1), arg 2), arg 3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3 Information Technology : : MJU The 15 The best education solution

Nesting Functions SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US')) FROM employees WHERE department_id = 60; Information Technology : : MJU The best education solution

Using the NVL Function 1 SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; 2 … Information Technology : : MJU 1 2 The best education solution

Simple Case 20 The best education solution

Simple Case Example Select Artistname, Region, Case Region When 'NC' Then 'South' When 'VA' Then 'South' When 'IL' Then 'Midwest' When 'VT' Then 'New England' Else 'Somewhere Else' End As Area From Artists;

Simple Case Example Artistname -------The Neurotics Louis Holiday Word Sonata The Bullets Jose Mac. Arthur Confused The Kicks Today 21 West Elm Highlander Region -------NC IL IN VA TX CA GA NY ONT VT OH Area ------South Midwest Somewhere Else South Somewhere Else Somewhere Else New England Somewhere Else

Switched Case

Switched Case Example Select Track. Num, Track. Title, Length. Seconds, Case When Track. Num=1 And Length. Seconds<240 Then 'Short 1 st Track' When Track. Num=1 And Length. Seconds>480 Then 'Long 1 st Track' Else 'Another Track' End as Eval From Tracks Where Track. Num<3

Switched Case Example Track. Num ------1 2 1 1 2 2 1 2 1 2 Track. Title -----Bob's Dream My Wizard Fat Cheeks Hottie Goodtime. March Rocky and Natasha Violin Sonata No. 1 in D Major Violin Sonata No. 2 in A Major Song 1 Song 2 I Don't Know What's the Day Length. Seconds Eval ----------185 Short 1 st Track 233 Another Track 352 Another Track 233 Short 1 st Track 293 Another Track 283 Another Track 511 Long 1 st Track 438 285 272 201 332 Another Track Short 1 st Track Another Track

DECODE Function การใชคำสง DECODE เพอกำหนดเงอนไขไดเชนเดยวกบคำสง CASE DECODE(col|expression, search 1, result 1 [, search 2, result 2, . . . , ] [, default]) Information Technology : : MJU The 26 The best education solution

Using the DECODE Function ตวอยางการใชคำสง DECODE SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1. 10*salary, 'ST_CLERK', 1. 15*salary, 'SA_REP', 1. 20*salary, salary) REVISED_SALARY FROM employees; … … … Information Technology : : MJU The best education solution

Using the DECODE Function ตวอยางการแสดงคาภาษ จากเงอนไขทกำหนดจากเงนเดอน เฉพาะแผนกหมายเลข 80 SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0. 00, 1, 0. 09, 2, 0. 20, 3, 0. 30, 4, 0. 40, 5, 0. 42, 6, 0. 44, 0. 45) TAX_RATE FROM employees WHERE department_id = 80; Information Technology : : MJU The 28 The best education solution

What Are Group Functions? ฟงกชนกลมคอ ฟงกชนทไดจากการประมวลผลขอมลหลาย ๆ แถว EMPLOYEES Maximum salary in EMPLOYEES table … Information Technology : : MJU The 29 The best education solution

Types of Group Functions – – – – AVG COUNT MAX MIN STDDEV SUM VARIANCE Information Technology : : MJU Group functions The 30 The best education solution

Group Functions: Syntax SELECT group_function(column), . . . FROM table [WHERE condition] [ORDER BY column]; Information Technology : : MJU The best education solution

Using the AVG and SUM Functions AVG คาเฉลย SUM คาผลรวม ซงจะใชกบขอมลตวเลขเทานน SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%'; Information Technology : : MJU The 32 The best education solution

Using the MIN and MAX Functions MIN คานอยทสด ตวเลข ตวอกษร MAX คามากทสด และ วนท ได สามารถใชไดกบ SELECT MIN(hire_date), MAX(hire_date) FROM employees; Information Technology : : MJU The 33 The best education solution

Using the COUNT Function COUNT(*) นบจำนวนแถวขอมลทตรงเงอนไข SELECT COUNT(*) FROM employees WHERE department_id = 50; ทกำหนด 1 COUNT(expr) นบจำนวนแถวทไมเปนคาวาง สำหรบคอลมน expr SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80; Information Technology : : MJU 2 The 34 The best education solution

Group Functions and Null Values การใชฟงกชนกลมโดยไมมการประมวลผลคาวาง SELECT AVG(commission_pct) FROM employees; หากตองการใหประมวลผลคาวางดวยสามารถใชฟงกชน functions SELECT AVG(NVL(commission_pct, 0)) FROM employees; Information Technology : : MJU 1 NVL 2 The 36 The best education solution

Creating Groups of Data EMPLOYEES 4400 9500 Average salary in EMPLOYEES table for each department 3500 6400 10033 … Information Technology : : MJU The best education solution

Creating Groups of Data: GROUP BY Clause Syntax SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; สามารถใชฟงกชนแบบกลม เพอประมวลผลขอมลเปนกลมยอย GROUP BY clause. Information Technology : : MJU ๆ ได โดยระบคำสง The 38 The best education solution

Using the GROUP BY Clause • คอลมนทงหมดทถกเลอกใหแสดงผลในคำสง ไมรวมทถกใชใน จะตองอยในคำสง GROUP BY clause SELECT ฟงกชนแบบกลม SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; Information Technology : : MJU The 39 The best education solution

Using the GROUP BY Clause • การใชคำสง GROUP BY โดยไมมในคอลมนทเลอกแตอาจจะทำใหสบสนไดวาผล ลพธทไดของแตละแถวเปนของแผนกอะไร SELECT AVG(salary) FROM employees GROUP BY department_id ; Information Technology : : MJU The 40 The best education solution

Grouping by More than One Column EMPLOYEES Add the salaries in the EMPLOYEES table for each job, grouped by department. … Information Technology : : MJU The best education solution

Using the GROUP BY Clause on Multiple Columns SELECT department_id , job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ORDER BY department_id; Information Technology : : MJU The best education solution

Illegal Queries Using Group Functions คอลมนทเลอกไมไดระบในสวนของ GROUP BY clause SELECT department_id, COUNT(last_name) FROM employees; A GROUP BY clause must be added to count the last names for each department_id. SELECT department_id, job_id, COUNT(last_name) FROM employees GROUP BY department_id; Either add job_id in the GROUP BY or remove the job_id column from the SELECT list. Information Technology : : MJU The 43 The best education solution

Illegal Queries Using Group Functions • ไมสามารถใชเงอนไขฟงกชนกลม ในสวนของคำสง WHERE ได • อนญาตใหใชคำสง HAVING clause ในการกำหนดเงอนไขของฟงกชนกลม เทานน SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; Cannot use the WHERE clause to restrict groups Information Technology : : MJU The 44 The best education solution

Restricting Group Results EMPLOYEES The maximum salary per department when it is greater than $10, 000 … Information Technology : : MJU The best education solution

Restricting Group Results with the HAVING Clause กรณทตองการใชคำสง HAVING clause สามารถใชกำหนดเงอนไขไดดงน 1. แถวทถกจดกลม 2. ฟงกชนกลม 3. กลมขอมลทตรงกบเงอนไขจะถกแสดงผล SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; Information Technology : : MJU The 46 The best education solution

Using the HAVING Clause SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ; Information Technology : : MJU The best education solution

Using the HAVING Clause SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary); Information Technology : : MJU The best education solution

Nesting Group Functions แสดงคาสงสดของคาเฉลยของเงนเดอนในแตละแผนก SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; Information Technology : : MJU The 49 The best education solution

��������� DATABASE OBJECTS ����� Information Technology : : MJU 50 The best education solution

What Is a View? EMPLOYEES table

Advantages of Views To restrict data access To provide data independence To make complex queries easy To present different views of the same data

Simple Views and Complex Views Feature Simple Views Complex Views Number of tables One or more Contain functions No Yes Contain groups of data No Yes DML operations through a view Yes Not always

Creating a View • เปนการนำชดคำสง SQL ในการสรางวว CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]. . . )] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; • สามารถใชคำสง SQL ทงแบบปกตและแบบซบซอน 54 The best education solution

Creating a View • ตวอยางการสราง EMPVU 80 view ซงประกอบดวยขอมลพนกงานในแผนกหมายเลข 80 CREATE VIEW empvu 80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; • การเรยกดโครงสรางของ DESCRIBE วว ใน SQL*Plus โดยใชคำสง DESCRIBE empvu 80 55 The best education solution

Creating a View • การสรางววโดยใชนามแฝงของคอลมน CREATE VIEW salvu 50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50; 56 The best education solution

Retrieving Data from a View SELECT * FROM salvu 50;

Modifying a View • การแกไขวว VIEW EMPVU 80 โดยใชคำสง CREATE OR REPLACE VIEW empvu 80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80; • การใชนามแฝงของคอลมน จะเรยงลำดบตามลำดบของคอลมนทถกเลอกในคำสง SQL ของควร 58 The best education solution

Creating a Complex View การสรางววแบบซบซอน โดยประกอบดวยฟงกชนกลม CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d. department_name, MIN(e. salary), MAX(e. salary), AVG(e. salary) FROM employees e JOIN departments d ON (e. department_id = d. department_id) GROUP BY d. department_name; 59 The best education solution

Denying DML Operations CREATE OR REPLACE VIEW empvu 10 (employee_number, employee_name, job_title) AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY ;

Sequences Object Description Table Basic unit of storage; composed of rows View Logically represents subsets of data from one or more tables Sequence Generates numeric values Index Improves the performance of some queries Synonym Gives alternative names to objects

CREATE SEQUENCE Statement : Syntax รปแบบการสรางตวเลขเรยงลำดบอตโนมต CREATE SEQUENCE sequence-name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 65 The best education solution

Creating a Sequence • ตวอยางการสราง ตงชอวา เพอใชเปนคยหลกของตาราง • ไมอนญาตใหใช CYCLE DEPT_DEPTID_SEQ DEPARTMENTS CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; 67 The best education solution

Using a Sequence • ตวอยางการเพมแถวขอมลใหม โดยใชตวเลขลำดบ INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq. NEXTVAL, 'Support', 2500); • การเรยกดคาปจจบนของ DEPT_DEPTID_SEQ SELECT FROM ตวเลขลำดบ dept_deptid_seq. CURRVAL dual; 70 The best education solution

Modifying a Sequence • การแกไขคาทเพมขน และ การจดการหนวยความจำ คาสงสด คาตำสด การวนคา ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; 72 The best education solution

Synonyms Object Description Table Basic unit of storage; composed of rows View Logically represents subsets of data from one or more tables Sequence Generates numeric values Index Improves the performance of some queries Synonym Gives alternative names to objects

Creating and Removing Synonyms • การสรางชอวว DEPT_SUM_VU ใหสนลง CREATE SYNONYM d_sum FOR dept_sum_vu; • การลบ synonym DROP SYNONYM d_sum; 76 The best education solution