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...
Английский
20130108
272 KB
3 чел.
SQL Oracle functions. Additional clauses of SELECT statement
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:
Singlerow functions 
Singlerow functions return a single result row for every row of a queried table. Singlerow 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. 
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. 
SELECT VARIANCE(Salary) FROM TEACHER; 
There are the following types of singlerow functions:
We will discuss only more frequently used functions of these types.
Function 
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; 
There are two types of character functions:
They are discussed bellow. Some of them are present in simplified variant.
Character functions returning character values
Function 
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, leftpadded 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, rightpadded 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 singlecharacter, onetoone 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
Function 
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 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 FROM DUAL; 

ASCII 
Returns the decimal representation in the database character set of the first character of char. 
SELECT ASCII('Q') FROM DUAL; 
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.
Function 
Syntax 
Purpose 
Example 
ADD_ 
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( FROM TEACHER WHERE Name = 'John'; 

LAST_ 
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_ 
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( '15MAR98','TUESDAY') AS "NEXT DAY" FROM DUAL; 

MONTHS_ 
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 31day 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_ 
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  AlaskaHawaii 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, 'DDMMYYYY HH24:MI:SS)' FROM DUAL; 
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.
Function 
Syntax 
Purpose 
Example 
TO_ (date convertion) 
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 (17) DAY  Name of day, padded with blanks to length of 9 characters DD  Day of month (131) DDD  Day of year (1366) HH  Hour of day (112) HH12  Hour of day (112) HH24  Hour of day (023) MI  Minute (059) MM  Twodigit numeric abbreviation of month (0112; 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_ (number convertion) 
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'; 
The following singlerow functions do not fall into any of the other singlerow function categories.
Function 
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( 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( 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; 
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:
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.
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.
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:
 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.
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.
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 teachersprofessors:
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 teachersprofessors:
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;
It is used to order the query result. You may order by
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','yyyymmdd'),'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;
Express in SQL the following queries:
Subject subjectname is taught by teachername to group groupnumber in room roomnumber building buildingnumber of week weeknumber.
Order results by subject name and group number.
Please, give answers to the following questions:
Express in SQL the following queries:
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';
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';
SELECT FACULTY.Name, FACULTY.Fund – SUM(DEPARTMENT.Fund)
FROM FACULTY, DEPARTMENT
WHERE FACULTY.FacNo = DEPARTMENT.FacNo
SGROUP BY FACULTY.Name;
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;
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;
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;
Subject subjectname is taught by teachername to group groupnumber in room roomnumber building buildingnumber of week weeknumber.
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';
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
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
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;
PAGE 12
А также другие работы, которые могут Вас заинтересовать  
60874.  Язык разметки гипертекста HTML  50 KB  
Ожидаемые результаты: В конце урока ученики смогут: Дать определение таким понятиям: Гипертекст; WEBстраница WEBсайт; WEBдизайн; Язык разметки гипертекста HTML; Использовать команды языка HTML для форматирования текста; Ориентировочный план урока Актуализация опорных знаний Как вы можете определить понятие Интернет Что такое служба WWW Как называются документы которые мы можем просматривать в программебраузере Изложение нового материала. Систематизация понятий Гипертекст; WEBстраница WEBсайт;...  
60875.  Нахождение площади фигур при решении практических задач  542 KB  
Четырехугольник у которого противолежащие стороны параллельны; По вертикали: Параллелограмм у которого все углы прямые; Прямоугольник у которого все стороны равны...  
60878.  Лексика. Росіянізм, калька, покруч  95.5 KB  
Русизми це окремі російські слова найчастіше з українізованою вимовою наприклад: тормозити замість гальмувати порівняйте рос. Затребуваний і кальковані від нього слова: запитаний запотребуваний затребований восребуваний витребуваний.  