6821

SQL Oracle functions. Additional clauses of SELECT statement

Лабораторная работа

Информатика, кибернетика и программирование

SQL Oracle functions. Additional clauses of SELECT statement Purpose of the lab To study SQL Oracle functions and SGROUPBY, HAVING, ORDERBY clauses of SELECT statement. To acquire practical skills in SQL Oracle function...

Английский

2013-01-08

272 KB

3 чел.

SQL Oracle functions. Additional clauses of SELECT statement

  1.  Purpose of the lab
  •  To study SQL Oracle functions and SGROUP BY, HAVING, ORDER BY clauses of SELECT statement.
  •  To acquire practical skills in SQL Oracle functions and SGROUP BY, HAVING, ORDER BY clauses of SELECT statement by using SQL*Plus.
  1.  Theoretical backgrounds
    1.  SQL Oracle functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL.

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE.

There are two categories of SQL functions:

Single-row functions

Single-row functions return a single result row for every row of a queried table. Single-row functions can appear in select lists (if the SELECT statement does not contain a SGROUP BY clause) and WHERE clauses.

Aggregate functions

Aggregate functions return a single row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and HAVING clauses.

  1.  Aggregate functions

If you use the SGROUP BY clause in a SELECT statement, SQL divides the rows of a queried table into groups. In a query containing a SGROUP BY clause, all elements of the select list must be expressions from the SGROUP BY clause, expressions containing aggregate functions, or constants. SQL applies the aggregate functions in the select list to each group of rows and returns a single result row for each group.

If you omit the SGROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. SGROUP BY and HAVING clauses will be studied in this Lab later.

Many aggregate functions accept these options:

DISTINCT

- causes an aggregate function to consider only distinct values of the argument expression.

ALL

- causes an aggregate function to consider all values, including all duplicates.

All aggregate functions except COUNT(*) ignore nulls. You can use the NVL in the argument to an aggregate function to substitute a value for a null.

If a query with an aggregate function returns no rows or only rows with nulls for the argument to the aggregate function, the aggregate function returns null

There are the following aggregate functions:

Function

Syntax

Purpose

Example

COUNT

Returns the number of rows in the query. If you specify expr, this function returns rows where expr is not null. You can count either all rows (ALL), or only distinct (DISTINCT) values of expr.

If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.

SELECT COUNT(*) AS Total

FROM  TEACHER;

SELECT COUNT(post)

FROM  TEACHER;

SELECT COUNT(DISTINCT post)

FROM  TEACHER;

AVG

Returns average of values in a column n.

SELECT AVG(Salary)

FROM   TEACHER

WHERE  Post='professor';

MIN

Returns minimum value of expr

SELECT MIN(Hiredate)

FROM   TEACHER;

MAX

Returns maximum value of expr.

SELECT MAX(Hiredate)

FROM   TEACHER

WHERE  Name LIKE 'А%';

SUM

Returns sum of values in a column n. 

SELECT SUM(Salary)

FROM   TEACHER

WHERE  Post='assistant';

STDEV

Returns standard deviation of x, a number. SQL calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function

SELECT STDEV(Salary)

FROM   TEACHER;

VARIANCE

Returns variance of x, a number. SQL calculates the variance of x using this formula:

where:

xi is one of the elements of x.
n
is the number of elements in the set x. If n is 1, the variance is defined to be 0.

SELECT VARIANCE(Salary)

FROM   TEACHER;

  1.  Single row functions

There are the following types of single-row functions:

  •  number functions,
  •  character functions,
  •  date functions,
  •  conversion functions,
  •  miscellaneous single row functions.

We will discuss only more frequently used functions of these types.

  1.  Number functions

Func-tion

Syntax

Purpose

Example

ABS

Returns the absolute value of n.  

SELECT ABS(-15) "Absolute"

FROM   DUAL;

CEIL

Returns smallest integer greater than or equal to n.  

SELECT CEIL(15.7) "Ceil"

FROM   DUAL;

FLOOR

Returns largest integer equal to or less than n.

SELECT FLOOR(15.7) "Floor"

FROM   DUAL;

SIN, COS, TAN

FUN is a function name.

Return sin, cos or tan of n (an angle expressed in radians).

SELECT SIN(30*3.1415/180)

FROM    DUAL;

SINH, COSH, TANH

FUN is a function name.

Returns the hyperbolic sin, cos or tan of n.  

SELECT SINH(1) AS

"Hyperbolic sine of 1"

FROM   DUAL;

EXP

Returns e raised to the nth power, where e = 2.71828183 ...

SELECT EXP(4) AS

"e to the 4th power"

FROM   DUAL;

LN

Returns the natural logarithm of n, where n is greater than 0.

SELECT LN(95) AS

"Natural log of 95"

FROM   DUAL;

LOG

Returns the log base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.

SELECT LOG(10,100) AS

"Log base 10 of 100"

FROM   DUAL;

MOD

Returns remainder of m divided by n. Returns m if n is 0.

SELECT MOD(11,4)

FROM   DUAL;

POWER

Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.

SELECT POWER(3,2)

FROM   DUAL;

SIGN

If n<0, the function returns -1. If n=0, the function returns 0. If n>0, the function returns 1.

SELECT SIGN(-15) "Sign" 

FROM   DUAL;

SQRT

Returns square root of n. The value n cannot be negative. SQRT returns a "real" result.

SELECT SQRT(26)  

FROM   DUAL;

ROUND

Returns n rounded to m places right of the decimal point. If m is omitted, n is rounded to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

SELECT ROUND(15.193,1)

AS  "Round"

FROM   DUAL;

TRUNC

Returns n truncated to m decimal places. If m is omitted, n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.

SELECT TRUNC(15.79,1)

AS  "Truncate"

FROM   DUAL;

  1.  Character functions

There are two types of character functions:

  •  returning character values and
  •  returning number values.

They are discussed bellow. Some of them are present in simplified variant.

Character functions returning character values

Func-tion

Syntax

Purpose

Example

CHR

Returns the character having the binary equivalent to n in either the database character set or the national character set.  

SELECT CHR(67)

FROM   DUAL;

CONCAT

Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||).

SELECT CONCAT('AB','CD')

FROM   DUAL;

INITCAP

Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric

SELECT INITCAP('the soap')

FROM   DUAL;

LOWER

Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).

SELECT LOWER('Mr. Scott')

  AS "lower case"

FROM   DUAL;

UPPER

Returns char, with all letters uppercase. The return value has the same datatype as the argument char.

SELECT UPPER('Mr. Scott')

  AS "UPPER CASE"

FROM   DUAL;

LPAD

Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

SELECT LPAD('Page',8,'*.')

  AS "LPAD example"

FROM   DUAL;

RPAD

Returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

SELECT RPAD('Page',8,'*.')

  AS "RPAD example"

FROM   DUAL;

LTRIM

Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, you must enclose it in single quotes.

SELECT LTRIM('xyxYZ','xy')

  AS "LTRIM example"

FROM   DUAL;

RTRIM

Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, you must enclose it in single quotes.

SELECT RTRIM('xYZxy','xy')

AS "RTRIM example"

FROM   DUAL;

REPLACE

Returns char with every occurrence of search string replaced with replace string. If replace string is omitted or null, all occurrences of search string are removed. If search is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings

SELECT REPLACE('JACK and

JUE','j', 'BL')

AS "REPLACE example"

FROM   DUAL;

SUBSTR

Returns a portion of char, beginning at character m, n characters long.

If m is 0, it is treated as 1. If m is positive, SQL counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. If n is omitted, SQL returns all characters to the end of char. If n is less than 1, a null is returned.

SELECT SUBSTR('ABCDE',2,2)

AS "SUBSTR example"

FROM   DUAL;

SELECT SUBSTR('ABCDE',-4,2)

AS "SUBSTR example"

FROM   DUAL;

TRANSLATE

Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value.

SELECT TRANSLATE('ABCDE',

  'ABCD', '1234')

AS TRANSLATE example"

FROM   DUAL;

Character functions returning number values

Func-tion

Syntax

Purpose

Example

INSTR

Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle counts and searches backward from the end of char1. The value of m must be positive. The

SELECT INSTR('CORPORATE
      FLOOR
','OR','3','2')

FROM    DUAL;

default values of both n and m are 1, meaning Oracle begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.

LENGTH

Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.

SELECT INSTR('CORPORATE
      FLOOR','OR','3','2')

FROM    DUAL;

ASCII

Returns the decimal representation in the database character set of the first character of char.

SELECT ASCII('Q')

FROM    DUAL;

  1.  Date functions

Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number.

Func-tion

Syntax

Purpose

Example

ADD_
MONTHS

Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.  

SELECT TO_CHAR(
 ADD_MONTHS(Hiredate,1),
 
'DD-MON-YYYY')

FROM   TEACHER

WHERE  Name = 'John';

LAST_
DAY

Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.

SELECT SYSDATE,

 LAST_DAY(SYSDATE),

 LAST_DAY(SYSDATE)-SYSDATE

FROM DUAL;

NEXT_
DAY

Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language, either the full name or the abbreviation.

SELECTNEXT_DAY(

 '15-MAR-98','TUESDAY') 

 AS "NEXT DAY"

FROM DUAL;

MONTHS_
BETWEEN

Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer. Otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2

SELECT MONTHS_BETWEEN (

 TO_DATE('28.10.2002',

           'DD.MM.YYYY'),

 TO_DATE('28.10.2002',

           'DD.MM.YYYY'))

FROM  DUAL

NEW_
TIME

Returns the date and time in time zone z2 when date and time in time zone z1 are d. The arguments z1 and z2 can be any of these text strings:

AST, ADT

- Atlantic Standard or Daylight Time

BST, BTD

- Bering Standard or Daylight Time

CST, CDT

- Central Standard or Daylight Time

EST, EDT

- Eastern Standard or Daylight Time

GMT

- Greenwich Mean Time

HST, HDT

- Alaska-Hawaii Standard Time or Daylight Time

MST, MDT

- Mountain Standard or Daylight Time

NST

- Newfoundland Standard Time

PST, PDT

- Pacific Standard or Daylight Time

YST, YDT

- Yukon Standard or Daylight Time

SYSDATE

Returns the current date and time. Requires no arguments. You can’t use this function in the condition of CHECK constraint

SELECT TO_CHAR(SYSDATE,

  'DD-MM-YYYY HH24:MI:SS)'

FROM   DUAL;

  1.  Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. This section lists main SQL conversion functions.

Func-tion

Syntax

Purpose

Example

TO_
CHAR

(date conver-tion)

Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. More used elements of fmt are the following:

- / , . ; : 'text'- punctuation and quoted text is reproduced in the result,

D

- Day of week (1-7)

DAY

- Name of day, padded with blanks to length of 9 characters

DD

- Day of month (1-31)

DDD

- Day of year (1-366)

HH

- Hour of day (1-12)

HH12

- Hour of day (1-12)

HH24

- Hour of day (0-23)

MI

- Minute (0-59)

MM

- Two-digit numeric abbreviation of month (01-12; JAN = 01)

MON

- Abbreviated name of month

MONTH

- Name of month, padded with blanks to length of 9 characters

YYYY

- digit year

YYY,

YY, Y

- Last 3, 2, or 1 digit(s) of year.

TO_
CHAR

(number conver-tion)

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. More used elements of fmt are the following:

Element

Example

 Description

, (comma)

9,999

Returns a comma in the specified position. You can specify multiple commas in a number format model

. (period)

99.99

Returns a decimal point, which is a period (.) in the specified position.

$

$9999

Returns value with a leading dollar sign.

9

9999

Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.

0

0999

9990

Returns leading zeros.

Returns trailing zeros.

FM

FM90.9

Returns a value with no leading or trailing blanks

EEEE

9.9EEEE

Returns a value using in scientific notation.

TO_

NUMBER

Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.

UPDATE TEACHER SET

salary = salary +

TO_NUMBER('100.00','99.9')

WHERE Name = 'John';

TO_

DATE

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format.

UPDATE TEACHER SET

Hiredate = TO_DATE(

'October 30, 2002, 11:00',

'Month dd, YYYY, HH:MI')

WHERE Name = 'John';

  1.  Miscellaneous Single Row Functions

The following single-row functions do not fall into any of the other single-row function categories.

Func-tion

Syntax

Purpose

Example

GREATEST

Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Character comparison is based on the value of the character in the database character set.

SELECT GREATEST(
'HARRY','HARRIOT','HAROLD')
 AS
"Greatest"

FROM   DUAL;

LEAST

Returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison.

SELECT LEAST(
'HARRY','HARRIOT','HAROLD')
 AS "Least"

FROM   DUAL;

NVL

If expr1 is null, returns expr2; if expr1 is not null, returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are differrent, SQL converts expr2 to the datatype of expr1 before comparing them.

SELECT Name,

   NVL(TO_CHAR(Salary),

   'NOT APPLICABLE')

FROM TEACHER;

USER

Returns the current SQL user with the datatype VARCHAR2

SELECT  USER

FROM  DUAL;

  1.  SGROUP BY and HAVING clause
    1.  Syntax:

  1.  Purpose

Purpose of SGROUP BY clause is to define column or list of column (column expression or list of column expressions) that will be used to group rows of the table. Expressions in SGROUP BY clause can contain any columns in the tables in the FROM clause, regardless of whether the columns appear in the select list. If query contains SGROUP BY clause select list may contains only:

  •  constants,
  •  expressions involving only aggregate functions,
  •  expressions from the SGROUP BY clause,
  •  expressions that involve only mentioned above expressions.

Scope of aggregate functions is all rows in every group. That is SQL applies the aggregate functions in the select list to each group of rows and returns a single result row for each group. That is why every group generates only one resulting row.

Purpose of HAVING clause is to determine condition to select groups. It restricts the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause, Oracle returns summary rows for all groups.

Specify SGROUP BY and HAVING after the WHERE clause. If you specify both SGROUP BY and HAVING, they can appear in either order.

See also the syntax description of expr in Appendix of Lab4 and the syntax description of condition in Appendix of Lab2.  

  1.  ORDER BY clause
    1.  Syntax:

  1.  Purpose

ORDER BY clause allows to order of the rows output by the query. Without this clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

  •  expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables in the FROM clause.
  •  position orders rows based on their value for the expression in this position of the select list; position must be an integer.
  •  c_alias orders rows based on their value of the column (expression) with this alias in select list.
  •  ASC and DESC specify either ascending or descending order. ASC is the default.

You can specify multiple expressions in the ORDER BY clause. Oracle first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. Oracle sorts nulls following all others in ascending order and preceding all others in descending order.  

Restrictions: 

  •  If you have specified the DISTINCT operator in this statement, this clause cannot refer to columns unless they appear in the select list.
  •  If you specify SGROUP BY clause in the same statement, this ORDER BY clause is restricted to the following expressions:

- Constants

-  Aggregate functions

- Expressions identical to those in the group by clause

- Expressions involving the above expressions that evaluate to the same value for all rows in a group.

  1.  Description and examples
    1.  Aggregate functions

Aggregate functions without WHERE and SGROUP BY clauses. If WHERE and SGROUP BY clause are absent scope of aggregate function is all rows of a table.

Example. How many rows in TEACHER table:

SELECT COUNT(*) AS Total_number_of_rows_in_TEACHER_table

FROM  TEACHER;

Example. What is a total fund of all faculties:

SELECT SUM(Fund)

FROM  FACULTY;

Example. What is average fund of all departments:

SELECT AVG(Fund)

FROM  DEPARTMENT;

Aggregate functions with WHERE clause. If WHERE clause is present scope of aggregate functions is those rows that satisfy WHERE clause.

Example. What is average salary of assistants that does not have increments.

SELECT AVG(Salary)

FROM   TEACHER

WHERE Post = 'assistant' AND Increment IS NULL;

Example. What is maximum fund of departments in informatics faculty?

SELECT MAX(Fund)

FROM   DEPARTMENT, FACULTY

WHERE  DEPARTMENT.FacNo = FACULTY.FacNo AND FACULTY.Name = 'informatics';

Expressions in aggregate functions. Arguments of aggregate functions may be expressions involving table columns.

Example. What is maximum value of salary+increment?

SELECT MAX(Salary + Increment)

FROM   TEACHER;

Example. What is a difference between maximum and minimum salaries?

SELECT MAX(Salary) – MIN(Salary)

FROM   TEACHER;

DISTINCT in aggregate functions. DISTINCT keyword means that only unique values of considered column (expression) will be taken into consideration.

Example. How many distinct telephone numbers are in TEACHER table:

SELECT COUNT(DISTINCT Tel) AS Number_of_telephones_in_all_teachers

FROM  TEACHER;

Example. How many posts are in TEACHER table:

SELECT COUNT(DISTINCT Post)

FROM  TEACHER;

Many aggregate functions in select list. Select list may contains many aggregate functions.

Example. How many professors in the university and what is their average salary:

SELECT COUNT(*), SUM( Salary )

FROM  TEACHER

WHERE  Post = 'professor';

Aggregate functions with SGROUP BY clause. See examples of SGROUP BY clause.

  1.  SGROUP BY and HAVING clauses

Aggregate functions in select list and SGROUP BY. When SGROUP BY is present any aggregate function in select list is applied to the rows in every group. Every group generate one row in query result.

Example. How many departments in every building:

SELECT Building, COUNT(*)

FROM   DEPARTMENT

SGROUP BY Building;

Example. What is the sum of salary+increment of each posts:

SELECT Post, SUM(Salary + Increment)

FROM   TEACHER

SGROUP BY Post;

Grouping and WHERE clause. If query contains WHERE and SGROUP BY clauses, the WHERE clause is processed the first and grouping is applied only to those rows that satisfy WHERE clause.

Example. For every building calculates number of rooms with seats more than 50:

SELECT Building, COUNT(*)

FROM  ROOM

WHERE  Seats > 50

SGROUP BY Building;  

Example. For buildings 5, 7 and 12 calculates number of rooms with seats more than 50:

SELECT Building, COUNT(*)

FROM  ROOM

WHERE  Building IN ('5', '7', '12') AND Seats > 50

SGROUP BY Building;

Grouping by many columns. It is possible to group table by mote that one column.

Example. For every week and day calculate number of lectures of type “lab”:

SELECT Day, Week, COUNT(*)

FROM  LECTURE

WHERE  Type = 'lab'

SGROUP BY Week, Day;

Grouping and join different tables. It is possible to join two or more tables and to do grouping in joined table.

Example. For every faculty calculate number of departments:

SELECT f.Name, COUNT(*)

FROM  FACULTY f, DEPARTMENT d

WHERE  f.FacNo = d.FacNo

SGROUP BY f.Name;

Example. For every faculty calculate number of teachers-professors:

SELECT f.Name, COUNT(*)

FROM  FACULTY f, DEPARTMENT d, TEACHER t

WHERE  f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND t.Post = 'professor'

SGROUP BY f.Name;

Example. For every department of every faculty calculate number of teachers-professors:

SELECT f.Name, d.Name, COUNT(*)

FROM  FACULTY f, DEPARTMENT d, TEACHER t

WHERE  f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND t.Post = 'professor'

SGROUP BY f.Name, d.Name;

Using HAVING clause. It is used to determine condition to select groups. Conditions is formulated on the whole group. That is why HAVING clause usually contain aggregate functions.

Example. Output that buildings the total sum of seats exceeds 1000:

SELECT Building

FROM   ROOM

SGROUP BY Building

HAVING SUM(Seats) > 1000;

  1.  ORDER BY clause

It is used to order the query result. You may order by

  •  any column of the table,
  •  any columns expression,
  •  any list of columns or column expressions.

Order by column in select list. You may order by any column of the select list.

Example. Select name and dean of faculties ordered by faculty names:

SELECT Name, Dean

FROM  FACULTY

ORDER BY Name;

Order by any column of the table. You may order by any column of the table even if it is not in the select list. This possibility does not supported by ANSI standard but is permissible in SQL Oracle.

Example. Select teacher names and salaries ordered by post:

SELECT Name, Salary

FROM  TEACHER

ORDER BY Post;

Order by list of columns. By using list of columns you may create multilevel ordering.

Example. Output group’s course, number and quantity. Group query result by group’s course and number:

SELECT Course, Num, Quantity

FROM  SGROUP

ORDER BY Course, Num;

Ordering by column expression. You may order by any column expressions.

Example. Output teacher’s names, salary and increment. Order results by salary+increment:

SELECT Name, Salary, Increment

FROM  TEACHER

ORDER BY Salary + Increment ASC;

Using column’s number in select list. You may use column’s sequential number in select list to reference to the column in ordering clause. It may be useful if select list contains expression

Example. Output teacher’s names and salary+increment. Order results by salary+increment in descending order:

SELECT Name, Salary + Increment

FROM  TEACHER

ORDER BY 2 DESC;

Using columns alias in select list. You may use column’s alias in select list to reference to the column in ordering clause. It may be useful if select list contains expression:

Example. Output teacher’s names and hire date. If hire date is not defined output string literal “not defined”. Order results by hire date in descending order:

SELECT Name, NVL(TO_CHAR('hiredate','yyyy-mm-dd'),'not defined') AS Teacher_hiredate

FROM  TEACHER

ORDER BY Teacher_hiredate DESC;

Joining tables and ordering. If you join tables it is possible to order results by any column of the joined table.

Example. Output teacher’s names and salary+increment of the informatics faculty. Order results by salary+increment in descending order:

SELECT t.Name, Salary + Increment

FROM  FACULTY f, DEPARTMENT d, TEACHER t

WHERE  f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND f.Name = 'informatics'

ORDER BY 2 DESC;

Grouping and ordering. On ordering of groups it is necessary to satisfy restrictions described in paragraph “2.3. ORDER BY clause”.

Example. Output number of students in every course. Order results by course in ascending order.

SELECT Course, SUM( Quantity )

FROM   SGROUP

SGROUP BY Course

ORDER BY Course ASC;

  1.  Lab tasks

Express in SQL the following queries:

  1.  How many students in informatics faculty?
  2.  How many subjects are taught in informatics faculty?
  3.  For every faculty outputs its name and difference between faculty fund and sum of funds of all faculty departments.
  4.  For teachers of informatics faculty output their names and number of lectures that they have in week 1.
  5.  For teachers of informatics faculty output their names and number of subjects that they are taught.
  6.  For every group of informatics faculty output group course, number and curator name. If curator is absent output literal “no curator”. Query result order by group course in descending order.
  7.  For every subject that is taught in informatics faculty output the only column that have the following string literal:

Subject subject-name is taught by teacher-name to group group-number in room room-number building building-number of week week-number.
Order results by subject name and group number.

  1.  Output number of students in every department of informatics faculty. (That is query result contains pair of columns: department name, number of students in the department).
  2.  Output faculties that fund exceed total funds of their departments more than 2000. Query result should contain the columns: faculty name, faculty fund, total fund of all faculty departments.
  3.  For every teacher output the name and number of taught subjects.
  4.  
  5.  Control questions

Please, give answers to the following questions:

  1.  What type of SQL functions do you know?
  2.  What is the scope of aggregate functions if SGROUP BY clause is absent and present?
  3.  In what clauses of SELECT statement aggregate functions may be used?
  4.  What does DISTINCT and ALL keywords in aggregate functions mean?
  5.  How do aggregate functions operate with null values?
  6.  How does SQL Oracle operates with date values?
  7.  What expressions may select list contain if SGROUP BY clause is present in SELECT statement?
  8.  What is a purpose of SGROUP BY and HAVING clauses?
  9.  What are restrictions of column expressions in ORDER BY clause if SGROUP BY clause is used?
  10.  How does Oracle order null values?
  11.  Appendices
    1.  Appendix A. Answer to lab task

Express in SQL the following queries:

  1.  How many students in informatics faculty?

SELECT SUM(SGROUP.Quantity) AS Number_Of_Students_In_Computer_Science_Faculty

FROM   FACULTY, DEPARTMENT, SGROUP

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo AND

  DEPARTMENT.DepNo = SGROUP.DepNo AND

  FACULTY.Name = 'informatics';

  1.  How many subjects are taught in informatics faculty?

SELECT COUNT(DISTINCT SbjNo)

FROM   FACULTY f, DEPARTMENT d, TEACHER t, LECTURE l, SUBJECT s

WHERE  f.FacNo = d.FacNo AND

  d.DepNo = t.DepNo AND

  l.TchNo = t.TchNo AND

  l.SbjNo = s.SbjNo AND

  FACULTY.Name = 'informatics';

  1.  For every faculty outputs its name and difference between faculty fund and sum of funds of all faculty departments.

SELECT FACULTY.Name, FACULTY.Fund – SUM(DEPARTMENT.Fund)

FROM   FACULTY, DEPARTMENT

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo

SGROUP BY FACULTY.Name;

  1.  For teachers of informatics faculty output their names and number of lectures that they have in week 1.

SELECT t.Name, COUNT(*)

FROM   FACULTY f, DEPARTMENT d, TEACHER t, LECTURE l,

WHERE  f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND l.TchNo = t.TchNo AND

  FACULTY.Name = 'informatics' AND Week = 1;

  1.  For teachers of informatics faculty output their names and number of subjects that they are taught.

SELECT t.Name, COUNT(DISTINCT SbjNo)

FROM   FACULTY f, DEPARTMENT d, TEACHER t, LECTURE l, SUBJECT s

WHERE  f.FacNo = d.FacNo AND

  d.DepNo = t.DepNo AND

  l.TchNo = t.TchNo AND

  l.SbjNo = s.SbjNo AND

  FACULTY.Name = 'informatics'

SGROUP BY t.TchNo, t.Name;

  1.  For every group of informatics faculty output group course, number and curator name. If curator is absent output literal “no curator”. Query result order by group course in descending order.

SELECT SGROUP.Course, SGROUP.Num, NVL(TEACHER.Nmae, 'no curator')

FROM   FACULTY, DEPARTMENT, SGROUP, TEACHER

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo AND

  DEPARTMENT.DepNo = SGROUP.DepNo AND

  SGROUP.Curator = TEACHER.TchNo

  FACULTY.Name = 'informatics'

ORDER BY SGROUP.Course DESC;

  1.  For every subject that is taught in informatics faculty output the only column that have the following string literal:

Subject subject-name is taught by teacher-name to group group-number in room room-number building building-number of week week-number.
Order results by subject name and group number.

SELECT 'Subject ' + s.Name + ' is taught by ' + t.Name +

  ' to group ' + TO_CHAR(g.Num) + ' in room ' + TO_CHAR(r.Num) +

  ' building ' + r.Building + ' of week ' + TO_CHAR(l.Week)

FROM   FACULTY f, DEPARTMENT d, SGROUP g, TEACHER t, LECTURE l, SUBJECT s ROOM r

WHERE  f.FacNo = d.FacNo AND

  d.DepNo = g.DepNo AND

  g.GRPNo = l.GRPNo AND

  l.TchNo = t.TchNo AND

  l.SbjNo = s.SbjNo AND

  l.RomNo = r.RomNo;

  FACULTY.Name = 'informatics';

  1.  Output number of students in every department of informatics faculty. (That is query result contains pair of columns: department name, number of students in the department).

SELECT DEPARTMENT.Name,

  SUM(SGROUP.Quantity) AS Number_Of_Students_In_The_Departments

FROM   FACULTY, DEPARTMENT, SGROUP

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo AND

  DEPARTMENT.DepNo = SGROUP.DepNo AND

  FACULTY.Name = 'informatics'

SGROUP BY DEPARTMENT.Name

  1.  Output faculties that fund exceed total funds of their departments more than 2000. Query result should contain the columns: faculty name, faculty fund, total fund of all faculty departments.

SELECT FACULTY.Name, FACULTY.Fund, SUM(DEPARTMENT.Fund)

FROM   FACULTY, DEPARTMENT

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo AND

SGROUP BY FACULTY.Name

HAVING ( Faculty.Fund - SUM(DEPARTMENT.Fund) ) > 2000

  1.  For every teacher output the name and number of taught subjects.

SELECT TEACHER.Name, COUNT(DISTINCT SbjNo ) AS Number_Of_Subjects_Taught_By_The_Teacher

FROM   TEACHER, LECTURE

WHERE  TEACHER.TchNo = LECTURE.TchNo

SGROUP BY TEACHER.Name;

  1.  

PAGE  12


 

А также другие работы, которые могут Вас заинтересовать

10774. Ввод и редактирование формул в MS Word 301.5 KB
  Лабораторная работа №8 Ввод и редактирование формул в MS Word Цель: Освоить принципы работы по созданию и редактированию формул в Microsoft Word Рекомендации к выполнению Варианты запуска редактора формул: Первый вариант запуска команда: Вставка / Объект / Microsoft Equation 3.0 и
10775. Оформление научных документов (рефератов, курсовых, дипломных работ) 320.5 KB
  Лабораторная работа №9 Оформление научных документов рефератов курсовых дипломных работ Цель: Освоить правила создания оглавлений сносок ссылок на литературу подписей рисунков и таблиц для оформления научных работ в Microsoft Word. Рекомендации к выполнению Пользов
10776. Шаблоны и стили оформления в MS Word 296.5 KB
  Лабораторная работа №10 Шаблоны и стили оформления в MS Word Цель: Иметь представление о шаблонах и стилях в оформлении документов научиться создавать свои шаблоны Microsoft Word. Рекомендации к выполнению Деловые документы отчеты письма приглашения бланки и пр. часто име...
10777. Библиотекарь отдела обслуживания: профессиональные требования 287 KB
  Разработке теоретических, методических и практических проблем библиотечного обслуживания уделялось внимание на всех этапах развития отечественного библиотековедения. Большой интерес представляют современные концепции библиотечного обслуживания
10778. Теоретическое исследование влияния режимов сварки на распределение температур в свариваемом изделии 300.5 KB
  Цель работы: Приобрести навыки теоретического исследования с использованием персонального ЭВМ влияние режимов сварки на распределение температур в свариваемом изделии. Оборудование: Персональная ЭВМ. Исходные данные: Материал ...
10779. Исследование влияния режимов сварки плавлением на температурное поле 628.5 KB
  Исследование влияния режимов сварки плавлением на температурное поле индекс Т1 по дисциплине Теория сварочных процессов Цель работы: приобрести навыки исследования влияния режимов сварки на распределения температур в свариваемом изделии с использованием перс
10780. Исследование температурного поля при сварке разнородных металлов с использованием метода электромоделирования 6.15 MB
  Отчет по лабораторной работе №3 Исследование температурного поля при сварке разнородных металлов с использованием метода электромоделирования по дисциплине Теория сварочных процессов Цель работы: понять физический механизм лежащий в основе особенносте...
10781. Экспериментальные исследования процесса распределения тепла при сварке 2.43 MB
  Отчет по лабораторной работе №4 Экспериментальные исследования процесса распределения тепла при сварке по дисциплине Теория сварочных процессов Цель работы: приобрести опят и навыки экспериментального определения температуры в ходе нагрева и охлаждения ...
10782. Захід. У світі казок Виховний захід 59 KB
  Виховний захід для учнів ІІ VI класів У світі казок. Розроблений і складений учителем англійської мови загальноосвітнього навчального закладу № 14 м. Херсона Хоменко Н.Б. Питання про роль позакласної роботи в учбовому процесі про вплив її на мотивацію вивче...