Lab 3

From bd_en
Jump to: navigation, search

Objectives: working with single-row functions in SQL

Single-Row Functions

Single-row functions are functions which, when used in SQL queries, are called and executed for each row/tuple/record of the queried table (row to be included in the result of the query). The functions may take as input a user constant, a variable name, a column name or an expression. They may be used within the SELECT list, or within WHERE and ORDER BY clauses. If called with a NULL argument these functions return NULL. Exceptions are CONCAT, NVL, REPLACE and REGEXP_REPLACE.

Most of the functions illustrated in the following, are specific to the Oracle's SQL dialect.

Numeric functions

get as input and also return numeric data

CEIL (n) the smallest integer that is greater than or equal to n SELECT CEIL(51.3) FROM DUAL;
FLOOR(n) the largest integer that is smaller than or equal to n SELECT FLOOR(51.3) FROM DUAL;
ROUND(n [,m]) Rounds n to the closest number using m decimal places. By default m is 0, equivalent to rounding to the closest integer. m may also take negative values. SELECT ROUND(21.365,2) AS "Round - positive" FROM DUAL;


SELECT ROUND (21.665,-1) AS " Round - negative " FROM DUAL;

TRUNC(n [,m]) Truncates n to m decimals; by default m is 0; m may also take negative values. SELECT TRUNC(21.365,2) AS "Truncate - positive" FROM DUAL;


SELECT TRUNC(21.665,-1) AS " Truncate - negative" FROM DUAL;

MOD(m, n) Returns the remainder of the division of m to n SELECT MOD (26,11) AS "Mod" FROM DUAL;

To the above functions we may also add common trigonometrical functions: (COS(n), ACOS(n), SIN(n), ASIN(n), TAN(n), ATAN(n)), logarithm functions: (LN(n), LOG(base, n)), exponentiation (EXP(n), POWER(m,n), square root (SQRT(n)).

Functions taking character data as input and returning numeric values

LENGTH (char) Returns the length of the string char. SELECT LENGTH('word') AS "Word lengtht" FROM DUAL;


SELECT fname, LENGTH(fname) FROM instructors WHERE UPPER(TRIM(fname))='COSMIN'; -- why 10?

ASCII (char) Returns the ASCII code for the first character in the string SELECT ASCII('A') FROM DUAL;
INSTR(char1, char2

[, n [, m ]])

Looks for the m th match of char2 within char1, staring search from position n; returns the position in char1 of the first character of char2 string, relative to n. By default n and m are 1. If no match is found, the result is 0. SELECT INSTR('CORPORATE FLOOR','OR',3,2) AS "Instring" FROM DUAL;


SELECT lname, INSTR(lname,'ra') FROM instructors;

Functions taking character data as input and returning character values

INITCAP(char) Returns char formatted such that the first character is in uppercase and the rest in lowercase. SELECT INITCAP('first leTTER in uPPERCASE') FROM DUAL;
UPPER(char) Returns char typed in uppercase SELECT UPPER('Stefan') FROM DUAL;
LOWER(char) Returns char typed in lowercase SELECT LOWER('LOWER') FROM DUAL;
CONCAT(char1, char2) Returns char1 concatenated with char2; equivalent to the concatenation operator SELECT CONCAT( CONCAT(fname, ' is student in year '), year) FROM students;
SUBSTR(char, m [, n ]) Extract n characters from char starting with position m; missing n leads to using the end of char; a negative value for m determines the counting to be performed from right to left. SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;
RPAD(char1,n [,char2 ]) Returns char1 filled with empty spaces at right untill length n is reached, if char2 is missing; otherwise, char2 is coped at the right of char1 until length n SELECT RPAD(lname,12,'ab') FROM students WHERE lname = 'Popescu';
LPAD(char1,n [,char2 ]) Similar to RPAD but the operations are performed at the left of char1 SELECT LPAD('Page',15,'*.') FROM DUAL;
CHR(n) returns the character having the binary equivalent to n, as a VARCHAR2 SELECT CHR(68)||CHR(65)||CHR(84)||CHR(69) FROM DUAL;
TRIM (char) Eliminates blanks from the beginning and the end of char
LTRIM(char) Eliminates blanks from the left/beginning of char
REPLACE(char, search_string [, replacement_string]) Returns char after replacing search_string with replacement_string; if replacement_string is not given, search_string is simply eliminated SELECT REPLACE('Andrei','i','ea') FROM DUAL;
TRANSLATE(char, from, to) Returns char modified as follows: every occurrence of a character in from is replaced by the character in to having the same position; if from is longer then to, the characters in from having no corresponding character are deleted. SELECT TRANSLATE('48KLM980', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "COD" FROM DUAL;

The functions that change the case of the letters (transform the text in lowercase or in uppercase) are frequently used in the WHERE clause in order to look for text for which we d not know how it was stored (in uppercase or lowercase or initcap format). For example, if we do not know how the first names were stored and we would like to retrieve information for a student named Andrei, we will process the field extracted from the database to bring it to a convenient fromat: SELECT * FROM students WHERE first_name = 'ANDREI'; --returns 0 rows

SELECT * FROM students WHERE UPPER(fname) = 'ANDREI'; --returns the wanted data

Also, TRIM is a function largely used to retrieve data when the attributes are declared as CHAR():

SELECT * FROM instructors WHERE UPPER(lname) = 'BREABAN'; --returns 0 rows

SELECT * FROM instructors WHERE TRIM(UPPER(lname)) = 'BREABAN'; --returns the wanted records

Functions for Dates

The default date format for an Oracle Database date value is derived from the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE. Most of you, after installing the database server on your systems will probably have the "DD-MON-YY" format (ex: '10-JUN-15'), but this can be modified by the user (ALTER SESSION SET NLS_DATE_FORMAT....). To overcome incompatibility issues, it is recommended when working with Dates to make use of the function TO_DATE (described in the next section), which will specify the format.

CURRENT_DATE Returns the current date as given by the time zone of the current session SELECT CURRENT_DATE FROM DUAL;
SYSDATE Returns the current date of the system SELECT SYSDATE FROM DUAL;
MONTHS_BETWEEN(d1, d2 ) The number of months elapsed between d1 and d2 SELECT MONTHS_BETWEEN(

TO_DATE('02-02-2015','MM-DD-YYYY'), TO_DATE('01-01-2015','MM-DD-YYYY') ) AS "Months" FROM DUAL;

ADD_MONTHS(d, n) SELECT dob, ADD_MONTHS(dob,1) FROM students WHERE lname = 'Popescu';
NEXT_DAY(d, char) Returns the date corresponding to the first week day given by char, after date d SELECT NEXT_DAY('13-OCT-15','TUESDAY') FROM DUAL;
LAST_DAY(d) Returns the date corresponding to the last day in the month specified by d. SELECT SYSDATE, LAST_DAY(SYSDATE) AS "Last" FROM DUAL;
ROUND(d [, fmt ]) Rounds to the closest date based on the given format. SELECT ROUND (TO_DATE ('27-OCT-16','DD-MON-YY'),'YEAR')"New Year" FROM DUAL;
TRUNC(d [, fmt ]) If the format is missing, truncates to the closest day. SELECT TRUNC(TO_DATE('27-OCT-16','DD-MON-YY'), 'YEAR')"First Day of the Year" FROM DUAL;

SELECT TRUNC(TO_DATE('27-OCT-16','DD-MON-YY'), 'MONTH')"Beginning of Oct." FROM DUAL;

Study the fmt format at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions230.htm

The database stores dates internally as numbers. Dates are stored in fixed-length fields of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second. Being stored as numbers, applying numerical operators is possible:

  • summing or subtracting a number to/from a date results in a new date:

SELECT SYSDATE + 10 FROM dual; How was the resultant date computed? How can we add a number of hours to a date?

  • subtracting two dates generates the number of days between the two: SELECT (SYSDATE - dob)/365 FROM students;

Conversion functions

Oracle converts by default the following types:

VARCHAR2 -> NUMBER

CHAR -> NUMBER

VARCHAR2 -> DATE

CHAR -> DATE

NUMBER -> VARCHAR2

DATE -> VARCHAR2

For other conversions the following functions are supplied:

TO_CHAR(char) Converts a char value to VARCHAR2
TO_CHAR(n [, fmt]) Converts a numerical value n to VARCHAR2, according to the fmt format. Numeric formats are described a: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570 SELECT TO_CHAR('01110' + 1) FROM dual;
SELECT TO_CHAR(scholarship,'$99,999') FROM students;
TO_CHAR({ datetime} [, fmt ]) Converts datetime to VARCHAR2 according to fmt SELECT TO_CHAR (SYSDATE, 'Day, Month, DD, YYYY')"TO_CHAR example" FROM DUAL;
TO_DATE(char [, fmt ]) Converts a character string to a date according to fmt. Study the format here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#i34924 SELECT TO_DATE('January 26, 1996, 12:38 A.M.', 'Month dd YYYY HH:MI A.M.') FROM DUAL;
TO_NUMBER(char [, fmt ]) http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570 SELECT TO_NUMBER('100.52','9,999.99') FROM DUAL;
CAST(expr AS type_name) SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(30)) FROM DUAL;
CONVERT(char, dest_char_set

[, source_char_set ])

Converts char (any character string) to a new encoding. Common encoding are:

-US7ASCII: US 7-bit ASCII character set -WE8DEC: West European 8-bit character set -F7DEC: DEC French 7-bit character set -WE8EBCDIC500: IBM West European EBCDIC Code Page 500 -WE8ISO8859P1: ISO 8859-1 West European 8-bit character set -UTF8: Unicode 4.0 UTF-8 Universal character set, CESU-8 compliant -AL32UTF8: Unicode 4.0 UTF-8 Universal character set

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;

To study the formats for dates to be used within to_char function, study the information at: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

Other functions

USER Returns the name of the current schema SELECT USER FROM DUAL;
NVL(expr1, expr2) If expr1 is NULL returns expr2, otherwise returns expr1 SELECT lname, NVL(scholarship,0) "scholarship" FROM students;
SELECT lname, NVL(TO_CHAR(scholarship),'No scholarship') "scholarship" FROM students;
NULLIF(expr1, expr2) If expr1 is equal to expr2 returns NULL, otherwise returns expr1
DECODE(expr, search1, result1

[, search2, result2]... [, default ])

Compares expr with every value search_i in the list. In case of equality returns the corresponding result_i. If no match exists, DEFAULT is returned; if this is not indicated, returns NULL. If expr is NULL, equality is considered only if there exists a search_i which is NULL. SELECT DECODE(scholarship, 450, '1st prize',350, '2nd prize',250, '3rd prize', '?') FROM students;
LEAST(expr1

[, expr2 ]... )

Returns the smallest element from the list SELECT LEAST('Mihai','Andrei','Anca') "LEAST" FROM DUAL;
GREATEST (expr1

[, expr2 ]... )

Returns the largest element from the list SELECT GREATEST('Mihai','Andrei','Anca') "GREATEST" FROM DUAL;

NVL function is frequently used to avoid null results when applying operators/functions. For example, if all students would be given some funds (ex. 100 RON), the total amount of money received by a student cannot be obtained by using

SELECT 100+scholarship FROM students;

but using

SELECT 100+NVL(scholarship,0) FROM students;

Exercises

  1. Write a query to show the current date. Label the column "Today".
  2. For every student, show the first name, date of birth and the number of months between the current date and the date of birth.
  3. For every student, show the day of the week when he/she was born.
  4. Using the concatenation function, show for every student the text: 'Student <fname> is in group <groupno>'.
  5. Show the scholarships on 10 characters, filling the empty slots at the right of the number with character '$'.
  6. For the instructors whose last names begin with B, show the name with the first letter in lowercase and the rest in uppercase, and the number of characters.
  7. For every student show the name, the date of birth, the date when the student is going to celebrate his/her birthday and the date corresponding to the first Sunday after celebration.
  8. Order the students who do not earn a scholarship based on the month when they were born; print the first name, last name, and the month of birth.
  9. For every student show first name, scholarship and the text: '1st place' for value 450, '2nd place' for 350, 3rd place' for 250 and 'mention' for the students not earning a scholarship. Give alias "Prize" for the third column.
  10. Show all students' first names, replacing letter i with a and letter a with i.
  11. For every student print first name, age as '<x> years <y> months and <z> days' (i.e. '19 years 3 months and 2 days'), and the number of days until the student will celebrate his/her birthday.
  12. Assuming that next month the scholarships are increased as follows: 450 with 10%, 350 with 15% and 250 with 20%, show for every student his/her first name, the date corresponding to the first day of next month and the new value of the scholarship. The students not earning a scholarship will have assigned value 0.
  13. For the students earning a scholarship, show the first name and the corresponding scholarship using only the star symbol (*) as follows: every symbol corresponds to 50 RON. In the table, align the symbols at the right.