GoConqr hat ein neues Design! Sag uns, wie es dir gefällt.

Kostenlos registrieren

Karteikarten

SQL_Experts

Ricardo Villegas SQL ORACLE SQL Englisch 117 Karten

SQL_Experts

Karten in diesem Stapel (117)

  1. 1—
  2. 2—
  3. 3Alternative quote (q) operadorAlternative quote (q) operadorAlternative quote (q) operador
  4. 4concat(coluna1|expression1,coluna2|expression2)select concat('Buenas',' tardes') from dual;--retorna 'Buenas tardes'.concat(coluna1|expression1,coluna2|expression2)concat(coluna1|expression1,coluna2|expression2)
  5. 5initcap(cadena|coluna):select initcap('buenas tardes alumno') from dual;--retorna 'Buenas Tardes Alumno'.
  6. 6lower(cadena|coluna)select lower('Buenas tardes ALUMNO') from dual;--retorna "buenas tardes alumno".
  7. 7upper(cadena|coluna)select upper('www.oracle.com') from dual;-- 'WWW.ORACLE.COM'
  8. 8lpad(cadena|coluna,longitud,cadenarelleno) Syntax: LPAD(s1, n, s2), RPAD(s1, n, s2). Parameters: s1 (character string—required); n (number—required); s2 (character string—optional—s2 defaults to a single blank space if omitted).select lpad('alumno',10,'xyz') from dual;-- retorna 'xyzxalumno'lpad(cadena|coluna,longitud,cadenarelleno) Syntax: LPAD(s1, n, s2), RPAD(s1, n, s2). Parameters: s1 (character string—required); n (numbe...
  9. 9rpad(cadena,longitud,cadenarelleno)select rpad('alumno',10,'xyz') from dual;-- retorna 'alumnoxyzx'
    SELECT substr(last_name,1,8), RPAD('*', salary/1000, '*'), salary FROM employees order by salary desc; returns numero de asteriscos si salary/1000 es 24 retorna 24 asteriscos.
  10. 10ltrim(cadena1,cadena2)select ltrim(' es la casa de la cuadra','la') from dual;-- no elimina ningún caracter
  11. 11rtrim(cadena1,cadena2)select rtrim('la casa lila ','la') from dual;-- no borra ningún caracter
  12. 12trim(cadena)TRIM(' ' FROM ' tech ') Result: 'tech' TRIM(LEADING '0' FROM '000123') Result: '123' TRIM(TRAILING '1' FROM 'Tech1') Result: 'Tech' TRIM(BOTH '1' FROM '123Tech111') Result: '23Tech'
  13. 13replace(text,search_string,replace_string)select replace('xxx.oracle.com','x','w') from dual;
    result: www.oracle.com
  14. 14substr(cadena,inicio,longitud)select substr('www.oracle.com',5,6) from dual;-- 'oracle'substr(cadena,inicio,longitud)
  15. 15length(column|expression)select length('www.oracle.com') from dual;-- devuelve 14.
  16. 16instr (cadena,subcadena); INSTR(s1, s2, pos, n) Parameters: s1 is the source string (required); s2 is the substring (required); pos is the starting position in s1 to start looking or occurrences of s2 (optional, default is 1); n is the occurrence of s2 to locate (optional, default is 1). If pos is negative, the search in s1 for occurrences of s2 starts at the end of the string and moves backward.INSTR('Tech on the net', 'e') Result:2 INSTR('Tech on the net', 'e', 1, 1) Result: 2 (the first occurrence of 'e') INSTR('Tech on the net', 'e', 1, 3) Result: 14 (the third occurrence of 'e')
  17. 17Order byselect *from libros order by titulo asc, editorial desc;Order byOrder by
  18. 18Syntax: SELECT expr FROM table [WHERE condition(s)] ORDER BY {column,expr,numeric_position} [ASC|DESC]];Syntax: SELECT expr FROM table [WHERE condition(s)] ORDER BY {column,expr,numeric_position} [ASC|DESC]];Syntax: SELECT expr FROM table [WHERE condition(s)] ORDER BY {column,expr,numeric_position} [ASC|DESC]];
  19. 19orselect *from libros where autor='Borges' or editorial='Planeta';
  20. 20notselect *from libros where not editorial='Planeta'; SELECT * FROM customers WHERE last_name IS NOT NULL; SELECT customer_name FROM customers WHERE customer_name NOT LIKE 'S%'; SELECT * FROM customers WHERE customer_id NOT BETWEEN 4000 AND 4100; SELECT * FROM suppliers WHERE NOT EXISTS (SELECT * FROM orders WHERE suppliers.supplier_id = orders.supplier_id);
  21. 21—SELECT * FROM customers WHERE customer_name IN ('IBM', 'Hewlett Packard', 'Microsoft'); SELECT * FROM customers WHERE customer_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');SELECT * FROM customers WHERE customer_name IN ('IBM', 'Hewlett Packard', 'Microsoft'); SELECT * FROM customers WHERE customer_name NOT I...
  22. 22likeSELECT last_name FROM customers WHERE last_name LIKE '%er%'; SELECT supplier_name FROM suppliers WHERE supplier_name LIKE 'Sm_th'; --- returns Smith', 'Smyth', 'Smath', 'Smeth'likelike
  23. 23like escape characterSELECT * FROM suppliers WHERE supplier_name LIKE 'Water!%' ESCAPE '!'; ---- returns Water% SELECT * FROM suppliers WHERE supplier_name LIKE 'H%!_' ESCAPE '!'; --- returns Hello_like escape character
  24. 24ADD_MONTHS( date1, number_months ) use ADD_MONTHS to add a negative number of months, and you’ll subtract them instead.ADD_MONTHS('01-Aug-03', 3) Result: '01-Nov-03' ADD_MONTHS('01-Aug-03', -3) Result: '01-May-03' ADD_MONTHS('21-Aug-03', -3) Result: '21-May-03' ADD_MONTHS('31-Jan-03', 1) Result: '28-Feb-03'ADD_MONTHS( date1, number_months ) use ADD_MONTHS to add a negative number of months, and you’ll subtract them instead.
  25. 25LAST_DAY( date )LAST_DAY(TO_DATE('2003/03/15', 'yyyy/mm/dd')) Result: Mar 31, 2003 LAST_DAY(TO_DATE('2003/02/03', 'yyyy/mm/dd')) Result: Feb 28, 2003 LAST_DAY(TO_DATE('2004/02/03', 'yyyy/mm/dd')) Result: Feb 29, 2004LAST_DAY( date )LAST_DAY( date )
  26. 26MONTHS_BETWEEN( date1, date2 ) Output: Number.MONTHS_BETWEEN (TO_DATE ('2003/01/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') ) returns -2.41935483870968
    MONTHS_BETWEEN('01-JAN-12', '01-FEB-12') = -1
    MONTHS_BETWEEN('01-JAN-12', '01-MAR-12') = -2
    MONTHS_BETWEEN('10-AUG-14', '10-JUL-14') = 1    MONTHS_BETWEEN( date1, date2 ) Output: Number.MONTHS_BETWEEN( date1, date2 ) Output: Number.
  27. 27NEXT_DAY( date, weekday ) Syntax: NEXT_DAY(d, c) Parameters: d is a date, required; c is a text reference to a day of the week, required. Process: Returns a valid date representing the first occurrence of the c day following the date represented in d. Output: Date.NEXT_DAY('01-Aug-03', 'TUESDAY') Result: '05-Aug-03' NEXT_DAY('06-Aug-03', 'WEDNESDAY') Result: '13-Aug-03' NEXT_DAY('06-Aug-03', 'SUNDAY') Result: '10-Aug-03'NEXT_DAY( date, weekday ) Syntax: NEXT_DAY(d, c) Parameters: d is a date, required; c is a text reference to a day of the week, required....
  28. 28ROUND( date [, format] ); ROUND(d, i) Parameters: d is a date (required); i is a format model (optional). Process: d is rounded off to the nearest date value, at a level of detail specified by i. d is required and specifies a value of the DATE datatype. i is a format model, and specifies the level of detail to which the DATE value will be rounded—i.e., to the nearest day, nearest hour, nearest year, etc. i is optional. Values are biased toward rounding up.ROUND(TO_DATE ('22-AUG-03'),'YEAR') Result: '01-JAN-04' ROUND(TO_DATE ('22-AUG-03'),'Q') Result: '01-OCT-03' ROUND(TO_DATE ('22-AUG-03'),'MONTH') Result: '01-SEP-03' ROUND(TO_DATE ('22-AUG-03'),'DDD') Result: '22-AUG-03' ROUND(TO_DATE ('22-AUG-03'),'DAY') Result: '24-AUG-03'
  29. 29TRUNC ( date [, format ] )TRUNC(TO_DATE('22-AUG-03'), 'YEAR') : '01-JAN-03' TRUNC(TO_DATE('22-AUG-03'), 'Q') : '01-JUL-03' TRUNC(TO_DATE('22-AUG-03'), 'MONTH') : '01-AUG-03' TRUNC(TO_DATE('22-AUG-03'), 'DDD') : '22-AUG-03' TRUNC(TO_DATE('22-AUG-03'), 'DAY') : '17-AUG-03'TRUNC ( date [, format ] )
  30. 30Conversion FunctionsConversion FunctionsConversion Functions
  31. 31—
  32. 32—
  33. 33—
  34. 34—
  35. 35—
  36. 36NESTING FUNCTIONSNESTING FUNCTIONS
  37. 37—
  38. 38—
  39. 39—
  40. 40—
  41. 41FUNÇÕES VALORES NULOS NVL( string1, replace_with ); NVL(e1, e2) Parameters: e1 and e2 are expressions, both are required, and both should be of the same datatype. Process: If e1 has a value of NULL, then NVL returns the value for e2. Otherwise it returns e1.SELECT NVL(supplier_city, 'n/a') FROM suppliers; NVL(TO_CHAR(commission_pct), 'Sem Comissão')
  42. 42NVL2( string1, value_if_not_null, value_if_null )select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;NVL2( string1, value_if_not_null, value_if_null )
  43. 43NULLIF( expr1, expr2 )NULLIF(12, 12) : NULL NULLIF(12, 13) : 12 NULLIF('apples', 'apples') : NULL NULLIF('apples', 'oranges') : 'apples' NULLIF(NULL, 12) : ORA-00932 error (because expr1 can not be the literal NULL)NULLIF( expr1, expr2 )NULLIF( expr1, expr2 )
  44. 44COALESCE( expr1, expr2, ... expr_n )IF address1 is not null THEN result := address1; ELSIF address2 is not null THEN result := address2; ELSIF address3 is not null THEN result := address3; ELSE result := null; END IF;COALESCE( expr1, expr2, ... expr_n )
  45. 45EXPRESSÕES CONDICIONALEXPRESSÕES CONDICIONALEXPRESSÕES CONDICIONAL
  46. 46CASE omitted ELSESELECT table_name, CASE owner WHEN 'SYS' THEN 'The owner is SYS' WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END FROM all_tables; returns NULL
  47. 47CASE Comparing 2 ConditionsSELECT CASE WHEN salary < 20000 THEN 'hello' WHEN salary > 21000 THEN 'goodbye' END FROM employees;
  48. 48CASE statement that evaluates two different fieldsSELECT employee_id, CASE WHEN JOB_ID = 'IT_PROG' and Last_name Like 'A%' THEN 'North office' WHEN JOB_ID = 'ST_CLERK' and Last_name Like 'M%' THEN 'South office' END FROM employees;
  49. 49DECODE( expression , search , result [, search , result]... [, default] )SELECT supplier_name,
    DECODE(supplier_id, 10000, 'IBM',
    10001, 'Microsoft',
    10002, 'Hewlett Packard',
    'Gateway') result
    FROM suppliers;
  50. 50—
  51. 51Group FunctionsGroup Functions
  52. 52—
  53. 53—
  54. 54—
  55. 55—
  56. 56—
  57. 57—
  58. 58—
  59. 59—
  60. 60—
  61. 61—
  62. 62—
  63. 63—
  64. 64—
  65. 65—
  66. 66—
  67. 67—
  68. 68—
  69. 69—
  70. 70—
  71. 71—
  72. 72—
  73. 73—
  74. 74—
  75. 75—
  76. 76—
  77. 77—
  78. 78—
  79. 79—
  80. 80—
  81. 81—
  82. 82—
  83. 83—
  84. 84—
  85. 85—
  86. 86—
  87. 87—
  88. 88—
  89. 89—
  90. 90—semsem
  91. 91—
  92. 92—
  93. 93—
  94. 94—
  95. 95—
  96. 96—
  97. 97—
  98. 98—
  99. 99—
  100. 100—
  101. 101—
  102. 102—
  103. 103—
  104. 104—
  105. 105—
  106. 106—
  107. 107—
  108. 108—
  109. 109—
  110. 110—
  111. 111—
  112. 112—
  113. 113—
  114. 114—
  115. 115—
  116. 116—
  117. 117—

Informationen

Beschreibung
You need to pay attention the the command words used in exam questions. The command word gives you information about what type of answer you need to write. Here are some flash cards to help you remember what type of answer they are looking for...
Erstellt von
Ricardo Villegas
Aufrufe
1
Verlauf
  • Erstellt von Mr Mckinlay · 7 Mai 2014
  • Kopiert von Ricardo Villegas · 14 Mai 2016
  • Kopiert von Ricardo Villegas · 7 Jun 2016
Bewertung

Feedback

Worum geht es?

Wie gefällt dir das neue Design?

Dein Eindruck