SQL

1. SQL Structured Query Language Parte 2

2. SQL Commands. Componentes. • Data retrieval • SELECT • Data manipulation language (DML). Lenguaje de manipulación de datos (LMD). • INSERT, UPDATE, DELETE • Data definition language (DDL). Lenguaje de definición de datos (LDD) • CREATE, ALTER, DROP, RENAME. • Transaction control. Control de transacciones. • COMMIT, ROLLBACK, SAVEPOINT. • Data control language (DCL). Autorización. Integridad. • GRANT, REVOKE El rollback es el espacio de anulación. Todas las bases de datos Oracle necesitan un lugar para almacenar la información para deshacer. Este espacio de tablas, que contiene los segmentos de anulación, se denomina rollback o rbs. Este es util cuando se recupera transacciones incompletas o interrumpidas.

3. The Basic Query Block SELECT [DISTINCT] {*,column [alias],...} FROM table; • SELECT identifies what columns • FROM identifies which table

4. Writing SQL Commands • Commands can be on one or many lines. • Abbreviations and splitting of words are not allowed. • Commands are not case sensitive. • Commands are entered into the SQL buffer.

5. Selecting AII Columns, AII Rows Simplest SELECT statement contains the following two clauses: • SELECT clause • Asterisk (*) indicates all columns • FROM clause SQL> SELECT * 2 FROM s_dept;

6. Selecting AII Columns, AII Rows SQL> SELECT * 2 FROM s_dept; ID NAME REGION ID --------- -------------- ------------- 10 Finance 1 31 Sales 1 32 Sales 2 33 Sales 3 34 Sales 4 35 Sales 5 41 Operations 1 42 Operations 2 43 Operations 3 44 Operations 4 45 Operations 5 50 Administration 1 12 rows selected.

7. Selecting Specific Columns SQL> SELECT dept_id, last_name, manager_id 2 FROM s_emp; • List the columns in the SELECT clause. • Separate columns by using a comma. • Specify columns in the order you want them to appear .

8. Column Label Defaults • Label default justification: • Left: date and character data • Right: numeric data • Label default display is uppercase.

9. Arithmetic Expressions Create expressions on NUMBER and DATE datatypes by using operators. • Add + • Subtract - • Multiply * • Divide /

10. Arithmetic Expressions • Display the annual salary for all employees. SQL> SELECT last_name , salary * 12 2 FROM s_emp; LAST_NAME SALARY*12 ----------- --------- ... Havel 15684 Magee 16800 Giljum 17880 Sedeghi 18180 Nguyen 18300 Dumas 17400 Maduro 16800 ...

11. Operator Precedence • Multiplication and division take priority over addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses can be used to force prioritized evaluation and to clarify statements.

12. Operator Precedence • Parentheses change the order in which a statement is evaluated. SQL> SELECT last_name, salary, 12 * salary + 100 2 FROM s_emp; ... Velasquez 2500 30100 SQL> SELECT last_name, salary, 12 * (salary + 100) 2 FROM s_emp; ... Velasquez 2500 31200

13. Column Aliases • A column alias renames a column heading. • Especially useful with calculations • Immediately follows column name • Optlonal AS keyword between column name and alias • Double quotation marks are required if an alias contains spaces, special characters, or is case-sensitive.

14. Concatenation Operator The concatenation operator • Is represented by two vertical bars (II). • Links columns or character strings to other columns. • Creates a resultant column that is a character expression.

15. Concatenation Operator: Example • Display the full names of the employees with the heading Employees. SQL> SELECT first_name||last_name AS Employees 2 FROM s_emp1; Employees --------------------------------- CarmenVelasquez LaDorisNgao MidoriNagayama MarkQuick-To-See AudryRopeburn MollyUrguhart

16. Literal Character String: Example SQL> SELECT first_name ||' '|| last_name 2 || ', '|| title Employees 3 FROM s_emp1; Employees ----------------------------------------- Carmen Velasquez, President LaDoris Ngao, VP, Operations Hidori Nagayama, VP, Sales Hark Quick-To-Soo, VP, Finance Audry Ropeburn, VP, Administration Holly Urguhart,

17. Duplicate Rows • The default display of queries is all rows including duplicate rows. SQL> SELECT name 2 FROM s_dept; • Eliminate duplicate rows by using DISTINCT in the SELECT clause. SQL> SELECT DISTINCT name 2 FROM s_dept;

18. DISTINCT with Multiple Columns • DISTINCT applies to all columns in the SELECT list. SQL> SELECT DISTINCT dept_id, title 2 FROM s_emp; • When DISTINCT is applied to multiple columns, the result represents the distinct combination of the columns.

19. Displaying Table Structure The SQL*Plus DESCRIBE command displays the structure of a table (column names, NOT NULL columns, and datatypes). SQL> DESCRIBE s_dept • NOT NULL columns must contain data. • Example column datatype and length • NUMBER (p,s) • VARCHAR2(s) • DATE • CHAR(s)

20. SET PAUSE Command • Allows you to control scrolling of your terminal. You must press [Return] after seeing each pause. SET PAUSE {OFF|ON| text}

21. COLUMN Command: Syntax COL[UMN] [{column|alias} [option]] • CLE[AR] -Clears any column formats. • FOR[MAT] format - Changes the display of the column using a format model. • HEA[DING] text - Sets the column heading. • JUS[TIFY] {align} - Aligns the column heading to be left, center, or right.

22. COLUMN Command: Examples • Create column headings COLUMN last_name HEADING 'Employee | Name' FORMAT A15 COLUMN salary JUSTIFY LEFT FORMAT $99,990.00 • Display the current setting for the LAST_NAME column. COLUMN last-name • Clear settings for the LAST_NAME column. COLUMN last_name CLEAR

23. The ORDER BY Clause Sort rows with the ORDER BY clause. • ASC - ascending order, default. • DESC - descending order • ORDER BY clause is last in SELECT command. SQL> SELECT last_name 2 FROM s_emp 3 ORDER BY last_name;

24. The ORDER BY Clause • The default sort order is ascending. • The sort order can be reversed by using DESC. • You can sort by expressions or aliases. SQL> SELECT last_name EMPLOYEE 2 FROM s_emp 3 ORDER BY EMPLOYEE DESC;

25. Limiting Rows Selected Restrict the rows returned by using the WHERE clause. • The WHERE clause follows the FROM clause. • Conditions consist of the following: • Column name, expression, constant • Comparison operator • Literal SQL> SELECT last_name, salary 2 FROM s_emp 3 WHERE salary >= 2200;

26. Character Strings and Dates • Character strings and dates are enclosed within single quotation marks. • Number values are not enclosed within quotation marks. • Character values are case-sensitive. • The default date format is 'DD-MON-YY'. SQL> SELECT first_name, last_name, title 2 FROM s_emp1 3 WHERE last_name = 'Nagayama';

27. Comparison and Logical Operators • Logical comparison operators = > >= < <= • .SQL comparison operators • BETWEEN ... AND... • IN(lisf) • LlKE • IS NULL • Logical operators • AND • OR • NOT

28. Negating Expressions Sometimes it is easier to exclude rows you know you do not want. • Logical Operators != <> ^= • SQL Operators • NOT BETWEEN • NOT IN • NOT LlKE • IS NOT NULL

29. BETWEEN and IN SQL Operators • Use the BETWEEN operator to test for values between, and inclusive of, a range of values. SQL> SELECT last_name, salary 2 FROM s_emp 3 WHERE salary BETWEEN 1500 4 AND 2200; • Use IN to test for values in a list. SQL> SELECT id, name, region_id 2 FROM s_dept 3 WHERE region_id IN (1,3);

30. LIKE SQL Operator • You can use the LIKE operator to perform wildcard searches of valid search string values. • Search conditions can contain either literal characters or numbers. • "%" denotes none or many characters. • "_" denotes one character SQL> SELECT last_name 2 FROM s_emp 3 WHERE last_name LIKE 'M%';

31. LIKE SQL Operator SQL> SELECT last_name 2 FROM s_emp 3 WHERE salary LIKE '%00'; • You can combine pattern matching characters. SQL> SELECT last_name 2 FROM s_emp 3 WHERE last_name LIKE '_a%';

32. IS NULL SQL Operator • Test for null values with the IS NULL operator. • Do not use the = operator . SQL> SELECT last_name 2 FROM s_emp1 3 WHERE title IS NULL;

33. Multiple Conditions • Use complex criteria. • Combine conditions with AND or OR operators. • AND requires both conditions to be TRUE. SQL> SELECT last_name, salary 2 FROM s_emp 3 WHERE salary > 1350 4 AND last_name like 'M%'; • OR requires either condition to be TRUE. SQL> SELECT last_name, salary 2 FROM s_emp 3 WHERE salary > 1350 4 OR last_name like 'M%';

34. Functions in SQL. Two Types of SQL Functions • Single row functions • Character • Number • Date • Conversion • Multiple row functions • Group

35. Character Functions • LOWER Converts to lowercase • UPPER Converts to uppercase • INITCAP Converts to initial capitalization • CONCAT Concatenates values

36. Case Conversion Functions Convert case for character string • LOWER('SQL Course') - sql course • UPPER('SQL Course') - SQL COURSE • INITCAP('SQL Course') - Sql Course

37. Example Using Character Functions • Display the fist name and last name of all employees with the last name of Patel. SQL> SELECT last_name, salary 2 FROM s_emp 3 WHERE last_name = 'MAGEE'; no rows returned SQL> SELECT last_name, salary 2 FROM s_emp 3 WHERE UPPER(last_name) = 'MAGEE'; LAST_NAME SALARY ---------- ---------- Magee 1400

38. Otra forma de concatenar SQL> SELECT first_name ||' '|| last_name 2 || ', '|| birth "Presidentes" 3 FROM president; ----------------------------------------- Presidentes ------------------------------------------ George Washington, 22-FEB-32 John Adams, 22-FEB-89 Thomas Jefferson, 22-FEB-01

39. Number Functions • ROUND Rounds value to specified decimal • TRUNC Truncates value to specified decimal • MOD Returns remainder of division

40. ROUND and TRUNC Functions • ROUND (45.923, 2) 45.92 • ROUND (45.923, 0) 46 • TRUNC (45.923, 2) 45.92 • TRUNC (45.923) 45

41. MOD Function • Find the remainder of one value divided by another value using MOD. • MOD(1600,300) 100

42. Oracle Date Format • Oracle stores dates in an internal numeric format. • Century, year, month. day. hours, minutes, seconds • Default date display is DD-MON-YY. • SYSDATE is a function returning date and time. • Select sysdate from sys.dual;

43. Conversion Functions: Overview • TO_CHAR converts a number or date string to a character string. • TO_NUMBER converts a character string containing digits to a number . • TO_DATE converts a character string of a date to a date value. • Conversion functions can use a format model composed of many elements.

44. TO_CHAR Function with Dates: Syntax TO_CHAR(date, 'fmt') The format model • Must be enclosed in single quotation marks. • Can include any valid date format element. • Is separated from the date value by a comma.

45. Date Format Model Elements • YYYY represents the full year in numbers. • YEAR represents the year spelled out. • MM represents the 2-digit value for month. • MONTH represents the full name of the month. • DY represents the 3-letter abbreviation of the day of the week. • DAY represents the full name of the day.

46. Date Format Model Elements • Time elements format the time portion of the date. • HH24:MI:SS AM 15:45:32 PM • Add character strings by enclosing them in double quotation marks. • DD " of " MONTH 12 of OCTOBER • Number suffixes spell out numbers. • ddspth fourteenth

47. TO_CHAR Function with Dates SQL> SELECT last_name, TO_CHAR(birth, 2 'fmDdspth "of" Month YYYY 3 fmHH:MI:SS AM') Nacimiento 4 FROM president;

48. TO_CHAR Function with Numbers SQL> SELECT last_name as "Nombre",' y su salario es de ' 2 ||TO_CHAR(salary,'fm$9,999') as "y Salario" 3 FROM s_emp;

49. What Is a Join? • A join is used to query data from more than one table. • Rows are joined using common values, typically primary and foreign key values.

50. Relations Between Tables