ID: 6817

Название работы: The SELECT statement in SQL Oracle. Basic features

Категория: Лабораторная работа

Предметная область: Информатика, кибернетика и программирование

Описание: The SELECT statement in SQL Oracle. Basic features Purpose of the lab To study general SQL Oracle possibilities in searching data in database tables. To acquire practical skills in searching data in database tables by using SQL*Plu...

Язык: Английский

Дата добавления: 2013-01-08

Размер файла: 287 KB

Работу скачали: 3 чел.

The SELECT statement in SQL Oracle. Basic features

1.  Purpose of the lab

•  To study general SQL Oracle possibilities in searching data in database tables.
•  To acquire practical skills in searching data in database tables by using SQL*Plus.

1.  Theoretical backgrounds
   1.  Query syntax

1.  Purpose

To retrieve data from one or more tables.

1.  Prerequisites

For you to select data from a table, the table must be in your own schema or you must have the SELECT privilege on the table

1.  Keywords and Parameters 

DISTINCT | UNIQUE	returns only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.
ALL	returns all rows selected, including all copies of duplicates. The default is ALL.
*	selects all columns from all tables listed in the FROM clause.
schema	is the schema containing the selected table. If you omit schema, Oracle assumes the table is in your own schema.
table.*	selects all columns from the specified table. You can use the schema qualifier to select from a table other than your own. A query that selects rows from two or more tables is a join. For more information about joins, see Lab???.
expr	selects an expression. See the syntax description of expr in this Lab later. A column name in this list can be qualified with schema only if the table containing the column is qualified with schema in the FROM clause.
c_alias	provides a different name for the column expression and causes the alias to be used in the column heading. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY, but not other clauses in the query.
FROM	specifies the table or subquery list from which data is selected.
		table		is the name of a table from which data is selected.
		t_alias		provides a correlation name for the table or subquery for evaluating the query and is most often used in a correlated query. Other references to the table throughout the query must refer to the alias.
		subquery		is a query that determines a table from which data is selected.
WHERE	restricts the rows selected to those that satisfy one or more conditions. condition can be any valid SQL condition that is based on the columns from tables in FROM clause. See the syntax description of condition in Lab2 Appendix. outer_join applies only if the FROM clause specifies more than one table. This special form of condition requires Oracle to return all the rows that satisfy the condition, as well as all the rows from one of the tables for which no rows of the other table satisfy the condition. For more information, including rules and restrictions that apply to outer joins, see Lab???. If one of the elements in the FROM clause is actually a nested table, you cannot specify the outer-join syntax in the WHERE clause. If you omit this clause, Oracle returns all rows from the tables, in the FROM clause.
GROUP BY	groups the selected rows based on the value of exp for each row, and returns a single row of summary information for each group Expressions in GROUP BY clause can contain any columns in the tables in the FROM clause, regardless of whether the columns appear in the select list.
		HAVING	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 GROUP BY and HAVING after the WHERE clause. If you specify both GROUP BY and HAVING, they can appear in either order.
		See also the syntax description of expr in Appendix of this Lab and the syntax description of condition in Appendix of Lab2.
UNION | UNION ALL | INTERSECT | MINUS	are set operators that combine the rows returned by two SELECT statements into a single result. The number and datatypes of the columns selected by each component query must be the same, but the column lengths can be different. If you combine more than two queries with set operators, Oracle evaluates adjacent queries from left to right. You can use parentheses to specify a different order of evaluation. For information on these operators, see description of set operators in ??? .   Restrictions:   To reference a column, you must use an alias to name the column.  You cannot specify the ORDER BY in the subquery of these operators.
ORDER BY	orders rows returned by the statement. 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 GROUP 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

SQL query is described with SELECT statement and consists of the following clauses:

SELECT, FROM, WHERE, GROUP BY, HAVING, UNION, UNION ALL, INTERSECT, MINUS, ORDER BY.

There are some other clauses in SELECT statement that will not be studied. Among listed above clauses only SELECT and FROM are obligatory, all others – optional.

In this lab we will study general possibilities of SELECT, FROM and WHERE clauses.

1.  The Building Blocks of Data Retrieval: SELECT and FROM clauses
   1.  The SELECT clause

In the simplest case SELECT clause contains list of the columns to be displayed and FROM identify the table name.

Display specified columns. To display specified columns they should be listed in SELECT clause. For example, the following query outputs faculty’s names and deans:

SELECT Name, Dean

FROM   FACULTY;

Display all columns of the table. To display all columns of the table you may list all of them in SELECT clause or use ‘*’ character in the list. The following query outputs all FACULTY table:

SELECT *

FROM   FACULTY;

Column name qualification with table name. Column name may be qualified with table name by using the following syntax:

table_name.column_name

The column qualification is needed when query uses many tables (see later) and different tables have the same column names. Column qualification may be used in any case even if there is no necessary to do it. For example, the following query is correct:

SELECT ROOM.Num, ROOM.Course, ROOM.Quantity

FROM  ROOM;

Remove duplication. You may remove rows duplication in output result by using DISTINCT or UNIQUE keyword in SELECT clause. This keyword is applied to the rows but not to the individual column. For example, the following two query are equivalent and give list of all different posts that present in database:

SELECT DISTINCT Post  SELECT UNIQUE Post

FROM   TEACHER;    FROM   TEACHER;

The keyword ALL means that all duplicated rows must be output. It is a default value if mentioned above keywords are absent. The following two queries are equivalent:

SELECT ALL Post    SELECT Post

FROM   TEACHER;    FROM   TEACHER;

Expressions in SELECT list. List in SELECT clause may contain not only column names but also arbitrary expressions that are supported by SQL Oracle. For example the following query is correct:

SELECT Name, Salary, Salary + Commission, Commission * 100 / (Salary + Commission)

FROM   TEACHER;

Literals in SELECT list. SELECT list item may be a literal or literal expression. During output column with literal will contain the literal in all rows. For example, the following query:

SELECT 'The teacher' Name, 'has salary' Salary, 'UAH'

FROM   TEACHER;

may have the following output:

         Name                 Salary

------------ -----------   ----------- ----------   ---

The teacher  Ivanov        has salary         500  UAH

The teacher  Petrov        has salary         470  UAH

Renaming of SELECT list items. Any item in SELECT list may be renamed with a help of so called “column aliases”. It is especially useful when it is necessary to refer to the item that is an expression. Column alias is also used as an column header during output of the query results. In the following query both items in SELECT list are renamed with column aliases:

SELECT Name       AS Teacher_name,

  Salary + Commission AS Total_salary

FROM   TEACHER;

The keyword AS is optional.

1.  The FROM clause

The from clause contains table name or list of table names that are used to show table(s) from which the data should be retrieved.

One table. In simplest case only one table is used. In all previous examples only one table was used.

Multiple tables. If you use many table in FROM clause all rows of the first table concatenated with all rows of the second table. For example, the following query:

SELECT *

FROM   FACULTY, DEPARTMENT;

lists all rows of FACULTY table concatenated with all rows of DEPARTMENT table. This query equivalent to Cartesian product operation of relational algebra.

Join two tables. In order to receive semantically sensible result it is necessary to concatenate FACULTY rows only with departments of the faculty.

Using the column FacNo that exists in both of the preceding tables, you may combine the information you had stored in the FACULTY table with information about the faculties departments from the DEPARTMENT table with the help of WHERE clause as it is shown in the next query:

SELECT *

FROM   FACULTY, DEPARTMENT

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo;

The join that was used is called an equi-join because the goal is to match the values of a column in one table to the corresponding values in the second table.

Join many tables. You may join as many tables as it is necessary. For example, the following query list faculties and their teachers:

SELECT FACULTY.Name, TEACHER,Name

FROM   FACULTY, DEPARTMENT, TEACHER

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo AND DEPARTMENT.DepNo = TEACHER.DepNo;

Note, in this example DEPARTMENT columns is not displayed, it is used only for joining faculties with teachers. (Usage of logical operators will be discussed later).

Finding the Correct Columns. When more than one table is used in the query you may specify list of selected columns from both tables. Column names may be qualified with table names. Column names may be also renamed with column aliases as it is done in the next query:

SELECT FACULTY.Name   AS Faculty_name,

  DEPARTMENT.Name  AS Department_name,

  DEPARTMENT.Fund AS Department_fund

FROM   FACULTY, DEPARTMENT

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo;

NON-equi-joins. Because SQL supports an equi-join, you might assume that SQL also has a non-equi-join. You would be right! Whereas the equi-join uses an = sign in the WHERE statement, the non-equi-join uses everything but an = sign. For example, the following query lists deparments that have fund more than fund of the corresponding faculty:

SELECT DEPARTMENT.Name, DEPARTMENT.Fund

FROM   FACULTY, DEPARTMENT

WHERE  FACULTY.Fund < DEPARTMENT.Fund;

Pay attention that in spite of the fact that we have to join two tables in order to receive the necessary result but in the select list we use columns only from one table DEPARTMENT.

Correlated name for the table. In FROM clause table name may be correlated with any other name with the help of table alias. Such correlated name is used to refer to the table in other clauses of the SELECT statement with the name that differ from table name. Syntax of table alias definition is the following:

table_name table_alias_name

For example, the previous query may be rewritten in such a way:

SELECT d.Name, d.Fund

FROM   FACULTY f, DEPARTMENT d

WHERE  f.Fund < d.Fund;

In this example usage of table aliases is optional, but there may be such queries where table aliases are mandatory

Joining a table to itself. A table may be joined to itself. In such case usage of table aliases is mandatory because it is necessary to distinguish two occurrences of the table. In the following query we receive the list group pairs that have the same rating:

SELECT G1.Num, G2.Num, G1.Raiting

FROM   SGROUP G1, SGROUP G2

WHERE  G1.GrpNo = G2.GrpNo

Outer join. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns NULL for any select list expressions containing columns of B. In the next example we will receive the list of faculties and their deparments, faculties is present even if they have no departments

SELECT FACULTY.Name AS FacName, DEPARTMENT.Name AS DepName, DEPARTMENT.Fund AS DepFund

FROM  FACULTY, DEPARTMENT

WHERE  FACULTY.FacNo = DEPARTMENT.FacNo (+);

DUAL table. Oracle contains the table DUAL with no rows and columns. You may address to this table id you want to output any single value that do not selected (or calculated) from any table. For example, the following query outputs the current system date:

SELECT SYSDATE FROM DUAL;

Of cause, in order to receive this date you may use any existing table, for example:

SELECT SYSDATE FROM FACULTY;

but in this case the number of returned rows with current system date will be equal to the number of rows in FACULTY table.

1.  The WHERE clause

The WHERE close allows to set condition that result rows must satisfy. We already consider how to use WHERE clause to join two tables.

1.  Use of simple conditions
   1.  Use of comparison operators

Example 1: Select all professors:

SELECT Name AS List_of_all_professors

FROM   TEACHER

WHERE  Post = 'professor';

  professor is a string literal that is why it enclosed in quotation marks.

Example 2: List all faculties with fund more than 30000:

SELECT Name

FROM   FACULTY

WHERE  Fund > 300000;

Here Fund is a numeric field that is why 30000 does not enclosed in quotation marks.

1.  Logical operators.

Example 3 – Logical AND: List faculties from building 5 with funds mode 20000:

SELECT Name

FROM   FACULTY

WHERE  Building ='5' AND Fund < 200000;

Example 4 – Logical OR: List departments in building 7 or building 3:

SELECT Name

FROM   DEPARTMENT

WHERE  Building ='7' OR Building ='3';

Example 5 – Logical NOT: List of all faculties that are not “informatics”:

SELECT Name

FROM   FACULTY

WHERE  NOT Name ='informatics';

Example 6. Combining logical operators: List assistants with salary less than 150 or Commission more that 100:

SELECT Name

FROM   TEACHER

WHERE  Post ='assistant' AND ( Salary < 150 OR Commission > 100 );

1.  Column expressions in WHERE clause

Example 7. Column expression: Select teachers whose salary plus Commission exceeds 300:

SELECT Name

FROM   TEACHER

WHERE  Salary + Commission > 300;

1.  Special operators
   1.  Operator IN

Operator IN. This operator test inclusion of right hand argument into the set defined by second argument.

Example 8. Operator IN: Select lecture types that are taught in Monday, Tuesday or Wednesday:

SELECT Type

FROM   LECTURE

WHERE  Day IN ('Mon', 'Tue', 'Wed');

Any IN operator with set literal of the right hand argument may be represented with OR operator. For example the previous query has the following equivalent representation:

SELECT Type

FROM   LECTURE

WHERE  Day = 'Mon' OR Day = 'Tue' OR Day = 'Wed');

IN operator also equivalent to "=ANY" operator (see later).

NOT IN operator. Operator NOT IN is negation of IN operator.

Example 9. NOT IN operator: List of faculties that are located in all buildings except 1, 7, 8, 11:

SELECT Name

FROM   FACULTY

WHERE  Building NOT IN ('1', '7', '8', '10');

Right hand operand of [NOT] IN may be a subquery. This possibility will be studied in Lab ???.

NULLS in NOT IN operator. If any item in the list following a NOT IN operation is null, all rows evaluate to UNKNOWN (and no rows are returned). For example, the following statement returns faculties that are not located in building 1 or 4:

SELECT Name

FROM   FACULTY

WHERE  Building NOT IN ( '1', '4' );

However, the following statement returns no rows:

SELECT Name

FROM   FACULTY

WHERE  Building NOT IN ( '1', '4' );

The above example returns no rows because the WHERE clause condition evaluates to:

Num != 5 AND Num != 15 AND Num != null

Because all conditions that compare a null result in a null, the entire expression results in a null. This behaviour can easily be overlooked, especially when the NOT IN operator references a subquery.

1.  Operator BETWEEN

BETWEEN operator. Operator evaluates to TRUE if left hand argument in between the interval of two values of right hand argument. The two values of right hand argument are included into the interval.

Example 10. BETWEEN operator: List teachers with salary in interval 150 -350:

SELECT Name

FROM   TEACHER

WHERE  Salary BETWEEN 150.00 AND 350.00;

BETWEEN operator have equivalent notation. For example the following query is equivalent to the previous one:

SELECT Name

FROM   TEACHER

WHERE  Salary >= 150.00 AND Salary <= 350.00;

NOT BETWEEN operator. NOT BETWEEN operator is negation of BETWEEN operator.

Example 11. NOT BETWEEN operator: List of departments that have funds not between 20000-50000:

SELECT Name

FROM   DEPARTMENT

WHERE  Fund NOT BETWEEN 20000.00 AND 50000.00;

SELECT Name

FROM   DEPARTMENT

WHERE  Fund < 20000.00 AND Fund > 50000.00;

As you can see values 20000 and 50000 are not included into NOT BETWEEN operator.

NULL values in [NOT] BETWEEN. If left hand argument is NULL the operator evaluates to UNKNOWN and the row does not satisfy this condition.

1.  Operator LIKE

The LIKE operator is used in character string comparisons with pattern matching. The syntax for a condition using the LIKE operator is shown in this diagram:

where:

char1	is a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.
NOT	logically inverts the result of the condition, returning FALSE if the condition evaluates to TRUE and TRUE if it evaluates to FALSE.
char2	is the pattern to which char1 is compared. The pattern is a value of datatype CHAR or VARCHAR2 and can contain the special pattern matching characters % and _.
ESCAPE	identifies a single character as the escape character. The escape character can be used to cause Oracle to interpret % or _ literally, rather than as a special character. If you wish to search for strings containing an escape character, you must specify this character twice. For example, if the escape character is '/', to search for the string 'client/server', you must specify, 'client//server'.

Whereas the equal (=) operator exactly matches one character value to another, the LIKE operator matches a portion of one character value to another by searching the first value for the pattern specified by the second. Note that blank padding is not used for LIKE comparisons.

With the LIKE operator, you can compare a value to a pattern rather than to a constant. The pattern must appear after the LIKE keyword.

Example 12 – Like operator: Find the salaries of all teachers with names beginning with 'SM':

SELECT Salary

FROM TEACHER

WHERE Name LIKE 'SM%';

The following query uses the = operator, rather than the LIKE operator, to find the salaries of all employees with the name 'SM%':

SELECT Salary

FROM TEACHER

WHERE Name = 'SM%';

The following query finds the salaries of all employees with the name 'SM%'. Oracle interprets 'SM%' as a text literal, rather than as a pattern, because it precedes the LIKE operator:

SELECT Salary

FROM TEACHER

WHERE 'SM%' LIKE Name;

Patterns typically use special characters that Oracle matches with different characters in the value:

•  An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
•  A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. Note that the pattern '%' cannot match a null.

Case sensitivity and pattern matching. Case is significant in all conditions comparing character expressions including the LIKE and equality (=) operators. You can use the UPPER() function to perform a case-insensitive match, as in this condition:

UPPER(ename) LIKE 'SM%'

ESCAPE option. You can include the actual characters "%" or "_" in the pattern by using the ESCAPE option. The ESCAPE option identifies the escape character. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character.

Example 13 – LIKE with ESCAPE: To search for teachers with the pattern 'A_B' in their name:

SELECT Name

FROM TEACHER

WHERE Name LIKE '%A\_B%' ESCAPE '\';

The ESCAPE option identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.

Patterns without %. If a pattern does not contain the "%" character, the condition can be TRUE only if both operands have the same length.

NULL value. If left hand argument is NULL the LIKE condition evaluates to UNKNOWN and corresponding row does not satisfy the LIKE operator.

1.  Operators IS NULL and IS NOT NULL.

IS NULL operator. Operator IS NULL allows to test column value for null. It evaluates to TRUE if column value is NULL and to FALSE if column value is not null.

Example 14 - Test null value: Select teachers with undefined values of salary or Commission:

SELECT Name

FROM   TEACHER

WHERE  Salary IS NULL OR Commission IS NULL;

IS NOT NULL operator. It is negation of IS NULL operator.

Function NVL. It has the syntax NVL(expr1, expr2). If expr1 is null, returns expr2; if is not null, returns expr1. It allows to output special value if the column in question have null value.

Example15. NVL function: Output faculties with their funds. If fund is not defined output the string “Fund not defined”

SELECT Name, NVL(Fund, 'Fund not defined')

FROM   FACULTY;

1.  Oracle Lab tasks

Далее приводится 15 вариантов заданий. Каждый вариант состоит из 7 запросов, которые относятся к следующим категориям (в порядке их следования):

1.  Выражения во фразе SELECT
2.  Соединение таблиц
3.  Многократное соединение одной и той же таблицы
4.  Использование предиката BETWEEN
5.  Использование предикатов IN и LIKE
6.  Внешнее соединение, соединение не по предикату равенства
7.  Использование сложных логических условий

ВНИМАНИЕ. В предлагаемых запросах используются константы (имена преподавателей, названия кафедр и факультетов, названия дисциплин), которые могут отсутствовать в вашей базе данных. ЗАМЕНЯЙТЕ ИХ НА ТЕ, КОТОРЫЕ ДЕЙСТВИТЕЛЬНО ИМЕЮТСЯ В ВАШЕЙ БАЗЕ ДАННЫХ!

 

1.  Вариант 1

1) Для каждого из преподавателей из таблицы TEACHER выведите его имя, должность, ставку, надбавку, процент надбавки по отношению к ставке (имя этого столбца «Процент1») и процент ставки по отношению к надбавке (имя этого столбца «Процент1»).

2) По каждому факультету вывести его название и название дисциплин, читаемых преподавателями-профессорами этого факультета

3) Вывести подчиненных подчиненных Сидорова

4) Вывести названия кафедр факультета 'компьютерные науки' с фондом финансирования в диапазоне 250000-350000

5) Вывести имена преподавателей-профессоров, которые работают на факультетах, расположенных в корпусах 2,3,6,7,8,10

6)  Вывести названия кафедр и имена преподавателей, которые на них работают. Причем, если на какой-то кафедре нет преподавателей, то эта кафедра все равно выводится, а вместо имени преподавателя выводится строка 'NULL'

7) Вывести имена преподавателей факультета 'компьютерные науки' со ставкой больше 1200 ИЛИ преподавателей факультета 'компьютерные системы' со ставкой больше 1500

1.  Вариант 2

1 Для каждого из преподавателей из таблицы TEACHER выведите его имя, ставку+надбавку,  процент ставки по отношению к ставке+надбавке (имя этого столбца «Результат1»), процент надбавки по отношению к ставке+надбавке (имя этого столбца «Результат2»)

2) Вывести названия факультетов и дисциплин, которые читаются на этих факультетах преподавателями-доцентами, являющимися кураторами студентов 1-го курса

3)  Вывести руководителя руководителя Иванова

4) Вывести имена преподавателей факультета 'компьютерные науки' с зарплатой (salary+commission) в диапазоне 1000-1500

5)  Вывести имена кафедр факультета 'компьютерные науки', на которых имеются группы с рейтингами 12, 17, 25, 29, 33, 43, 50

6)  Вывести названия кафедр, номера и курс групп этих кафедр. Если на какой-то кафедре нет групп, то эта кафедра все равно выводится, а вместо номера группы и курса выводятся цифры 0. Причем, столбцы результирующей таблицы имеют следующие имена:

Столбец имени кафедры – Кафедра

Столбец номера группы – Группа

Столбец курса - Курс

7) Вывести имена и должности преподавателей, которые НЕ удовлетворяют следующему условию:

преподаватель работает на факультете, расположенном  в корпусе 4, и имеет должность ассистента ИЛИ работает на факультете, расположенном в корпусе 6, и имеет должность доцента

1.  Вариант 3

1) По каждому преподавателю выведите его имя и сумму hiredate + salary (столбец с именем «Дата»). Что получается в результате вычисления hiredate + salary?

2) По каждой дисциплине вывести ее название и названия факультетов, на которых читаются эти дисциплины на третьем курсе

3) Вывести названия кафедр, преподаватели которых преподают студентам кафедры ИПО

4) Вывести названия кафедр, на которых непосредственные подчиненные заведующих кафедр имеют зарплату (salary+commission) в диапазоне 2000-3000

5)  Вывести названия дисциплин, которые читаются преподавателями-профессорами кафедр, расположенных в корпусах 1, 4, 6, 7 , 9, 10

6) Вывести имена преподавателей и номера групп и курсов,  в которых они являются кураторами. Причем, если какой-то преподаватель не является куратором никакой группы, то его имя все равно выводится, а вместо номера группы и курса выводятся цифры 0.

       

7) Вывести номера групп и их курс факультета с фондом финансирования больше 20000 и рейтингом больше 10 ИЛИ группы факультета из корпуса 6 и с рейтингом меньше 70 ИЛИ группы факультета из корпуса 5 и с рейтингом больше 40

1.  Вариант 4

1) По каждому преподавателю выведите его имя и разность между датой приема на работу и деления ставки на надбавку (столбец с именем «Выражение»). Что получается в результате вычисления hiredate - (Salary/Commission)?

2) По каждой кафедре вывести их названия и номера аудиторий, в которых их преподаватели читают лекции по понедельникам первой недели

3)  Вывести названия факультетов, преподаватели которых преподают студентам кафедры ИПО

4) Вывести названия кафедр факультета 'компьютерные науки', непосредственные подчиненные заведующих которых (кафедр)  приняты на работу в диапазоне 01.10.2001 – 31.12.2001

5)  Вывести названия факультетов, на которых имеются преподаватели, принятые на работу в одну из следующих дат: 01.02.2000, 01.03.2000, 01.04.2000. 01.05.2000, или имеющих ставку в диапазоне 500-800

6) Вывести пары имен факультетов, удовлетворяющих следующему условию: у первого факультета фонд финансирования превышает больше, чем на  1000, фонд финансирования второго.

7) Вывести номера групп и их рейтинги, которые НЕ удовлетворяют следующему условию:

они находятся на факультете 'компьютерные системы' и курс не равен 5 ИЛИ

они находятся на факультете с фондом финансировании в диапазоне 150000-300000 и имеют рейтинг больше 40 ИЛИ

они находятся на факультете с фондом финансирования больше 200000 и имеют рейтинг в диапазоне 20 50

1.  Вариант 5

1) Выведите информацию о факультетах в следующем формате:

Факультет <название факультета> находится в корпусе  <номер корпуса> и имеет фонд финансирования <фонд финансирования>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о факультетах»

Б) Информация выводится в шести столбцах: первый столбец с именем «Константа1» содержит литерал ‘Факультет ’, второй столбец с именем «Факультет» содержит название факультета, третий столбец с именем «Константа2»  содержит литерал ‘находится в корпусе’, четвертый столбец с именем «Корпус» содержит номер корпуса, пятый столбец с именем «Константа3» содержит литерал ‘и имеет фонд финансирования ’, шестой столбец с именем «Финансирование» содержит фонд финансирования.

2) По каждой аудитории корпуса 6 вывести ее номер и названия кафедр, на которых проволятся занятия в этой аудитории студентам 1 курса.

3) Вывести названия кафедр, студентам которых преподают преподаватели кафедры ИПО

4) Вывести номера групп факультета 'компьютерные науки', кураторы которых (групп) были приняты на работу в одном из следующих діапазон дат: 01.06.2001 – 01.08.2001, 01.01.2002- 01.03.2002

5) Вывести имена непосредственных подчиненных заведующих кафедр ИПО, которые (заведующие кафедр) были приняты на работу в одну из следующих дат: 25.07.2001, 13.08, 2001, 01.12.2001

6) Вывести пары номеров групп, удовлетворяющих следующему условию: первая групп находится на более высоком курсе и ее рейтинг превышает больше, чем на 10, рейтинга второй группы

7) Вывести имена преподавателей и дату их приема на работу, удовлетворяющих следующему условию:

они являются кураторами групп 1 курса или групп с рейтингом больше 15 И

они имеют должность профессора или доцента И

у них зарплата (salary+commission) меньше 1000 или больше 2000.

1.  Вариант 6

1) Выведите информацию о кафедрах в следующем формате:

Кафедра <название кафедры> с первичным ключом  <номер первичного ключа> имеет удвоенный фонд финансирования <фонд финансирования, умноженный на 2>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о кафедрах»

Б) Информация выводится в шести столбцах: первый столбец с именем «Константа1» содержит литерал ‘Кафедра ’, второй столбец с именем «Кафедра» содержит название кафедры, третий столбец с именем «Константа2»  содержит литерал ‘с первичным ключом’, четвертый столбец с именем «ПК» содержит номер первичного ключа, пятый столбец с именем «Константа3» содержит литерал ‘имеет удвоенный фонд финансирования’, шестой столбец с именем «Финансирование» содержит фонд финансирования, умноженный на 2

2) Вывести номера групп 3-го курса, которым проводят лабораторные работы преподаватели факультета компьютерных наук

3) Вывести названия факультетов, студентам которых преподают преподаватели кафедры ИПО

4) Вывести названия кафедр, в группах которых проводят занятия типа 'лекция' преподаватели, принятые на работу в диапазоне 01.01.2001 01.01.2002

5) Вывести названия кафедр факультета, деканом которого является Иванов, непосредственные подчиненные заведующих которых (кафедр) имеют одну из следующих ставок: 1000, 1500, 2000, 2500, 3000.

6) Вывести пары номеров групп вместе с их курсами, удовлетворяющих следующему условию: факультет первой группы имеет фонд финансирования  больше, чем на 2000, фонда факультет второй группы.

7) Вывести имена преподавателей и дату их приема на работу, которые НЕ удовлетворяют следующему условию:

они являются кураторами групп кафедры с фондом финансирования меньше 200000 или больше 300000 И

они являются кураторами групп с рейтингом больше 15 или групп курса 5 И

они имеют зарплату (salary+commission) между 1000-1200 или между 1300-1500

1.  Вариант 7

1) Выведите информацию о преподавателях в следующем формате:

<должность преподавателя>  <имя преподавателя> был принят на работу  <дата приема на работу> и имеет зарплату <сумма ставки и надбавки>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о преподавателях»

Б) Информация выводится в шести столбцах: первый столбец с именем «Должность» содержит должность преподавателя, второй столбец с именем «Преподаватель» содержит имя преподавателя, третий столбец с именем «Константа1»  содержит литерал ‘был принят на работу’, четвертый столбец с именем «Дата» содержит дату приема на работу, пятый столбец с именем «Константа2» содержит литерал ‘и имеет зарплату’, шестой столбец с именем «Зарплата» содержит сумму ставки и надбавки.

2) Вывести имена преподавателей-доцентов, которые читают лекции студентам факультета компьютерных наук

3) Вывести названия факультетов, группы которых имеют кураторов из факультета 'компьютерные науки'

4) Вывести названия факультетов, преподаваетели-профессора которых имеют занятия  на парах в диапазоне 2-6

5) Вывести имена преподавателей-профессоров, которые проводят занятия типа 'лекция' в одной из следующих аудиторий корпуса 6: 309, 311, 313, 315, 327

6) Вывести пары номеров групп, удовлетворяющих следующим условиям: декан факультета первой группы зарабатывавет (salary+commission) болше, чем декан факультета второй группы

7)  Вывести имена преподавателей и дату их приема на работу, удовлетворяющих следующему условию:

их  зарплата (salary+commission) больше 1000 или они были приняты на работу после 01.01.2001 И

заведующие их кафедр имеют зарплату (salary+commission) больше 3000 или меньше 2500 И

деканы их факультетов  являются профессорами или доцентами.

1.  Вариант 8

1) Выведите информацию об аудиториях в следующем формате:

Аудитория <номер аудитории> находится на <номер этажа> этаже корпуса <номер корпуса> и имеет <количество мест> мест

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация об аудиториях»

Б) Информация выводится в следующих девяти столбцах:

- первый столбец с именем «Литерал1» содержит константу ‘Аудитория’

- второй  столбец с именем «Аудитория» содержит номер аудитории

- третий столбец с именем «Литерал2» содержит константу ‘находится на’

- четвертый столбец с именем «Этаж»  содержит номер этажа

- пятый столбец  с именем «Литерал3» содержит константу ‘этаже корпуса’

- шестой столбец с именем «Корпус»  содержит номер корпуса

- седьмой столбец  с именем «Литерал4» содержит константу ‘и имеет’

- восьмой столбец с именем «К-во мест»  содержит количество мест

- девятый столбец  с именем «Литерал5» содержит константу ‘мест’

2) Вывести расписание занятий преподавателей, которое содержит:

- имя преподавателя     - Преподаватель

- название дисциплины    - Предмет

- номер аудитории     - АУД

- корпус, в котором располагается аудитория - КП

- номер группы и ее курс    - ГРП   и  КРС

- неделя      - Неделя

- день недели      - ДН

- пара       - Пара

- тип занятия      - Тип

Укажите имена столбцов результирующей таблицы так, как это указано в правой части приведенного выше списка

3) Вывести названия факультетов, преподаватели которых являются кураторами групп факультета 'компьютерные науки'

4) Вывести номера групп факультета 'компьютерные науки' с количеством студентов в диапазоне 27-32 или с рейтингом в диапазоне 25-45

5) Вывести номера аудиторий и их корпуса, в которых преподаватели-ассистенты проводят занятия типа 'лабораторная' в один из следующих дней: понедельник, середа, четверг, суббота.

6)  Вывести пары имен преподавателей, удовлетворяющих следующим условиям:

- первый преподаватель профессор

- второй преподаватель доцент

- первый преподаватель зарабатывает (salary+commission) больше, чем на 50, второго

7)   Вывести имена преподавателей и дату их приема на работу, НЕ удовлетворяющих следующему условию:

они были приняты на работу в диапазоне 01.01.2000 – 31.12.2005 или они являются доцентами или ассистентами И

деканы их факультетов  получают зарплату (salary+commission) либо между 1200-1500 либо между 1700-2000 И

они являются кураторами групп с рейтингом либо меньше 40 либо больше 60.

1.  Вариант 9

1) Выведите информацию о лекциях в следующем формате:

На <неделя> неделе в <название дня недели> на <номер пары> проводится <тип занятия>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о лекциях»

Б) Информация выводится в следующих восьми столбцах:

- первый столбец с именем «Литерал1» содержит константу ‘На’

- второй столбец с именем «Неделя» содержит номер недели

- третий столбец с именем «Литерал2» содержит константу ‘неделе в’

- четвертый столбец с именем «День»  содержит название дня недели

- пятый столбец  с именем «Литерал3» содержит константу ‘на’

- шестой столбец с именем «Пара»  содержит порядковый номер пары

- седьмой столбец  с именем «Литерал4» содержит константу ‘паре проводится’

- восьмой столбец с именем «Тип»  содержит тип занятия.

2) Вывести загруженность занятиями аудитории 313 корпуса 6. Вывод содержит:

- неделя   - Неделя

- день недели   - ДН

- пара    - Пара

- тип занятия   - Тип

- номер группы и ее курс - ГРП   и  КРС

- имя преподавателя  - Преподаватель

Укажите имена столбцов результирующей таблицы так, как это указано в правой части приведенного выше списка

3) Вывести имена преподавателей кафедры, заведующим которой является Сидоров

4) Вывести имена преподавателей, являющихся кураторами групп кафедры ИПО, с зарплатой (salary+commission) в диапазоне 900-1700

5) Вывести имена преподавателей-профессоров, которые проводят занятия типа 'лекция' по дисциплинам, отличающимся от следующих: программирование,  С, PASCAL, ИПО.

6) Вывести пары имен преподавателей,  удовлетворяющих следующему условию: первый преподаватель является куратором группы, имеющей рейтинг больше, чем в два раза, рейтинга группы, куратором которой является второй преподаватель

7) Вывести имена преподавателей и их зарплату (salary+commission), которые удовлетворяют следующему условию:

дата их приема на работу находится в диапазоне 02.03.1999–31.12.2007 или они являются профессорами И

их непосредственный руководитель имеет зарплату (salary+commission) в диапазоне 1200-1500 или он был принят на работу после 01.12.1998 И

их непосредственный подчиненный является ассистентом или преподаватель или доцент

1.  Вариант 10

1) Выведите информацию о дисциплинах в следующем формате:

Название дисциплины в БД: <название дисциплины > Название дисциплины прописними буквами: <название дисциплины прописними буквами > Название дисциплины строчными буквами: <название дисциплины строчными буквами>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о дисциплинах»

Б) Информация выводится в следующих шести столбцах:

- первый столбец с именем «Константа1» содержит константу ’Название дисциплины в БД:’

- второй столбец с именем «Дисциплина1» содержит название дисциплины в БД

- третий столбец с именем «Константа2» содержит константу ’Название дисциплины прописными буквами:’

- четвертый столбец с именем «Дисциплина2» содержит Название дисциплины в БД прописными буквами

- пятый столбец с именем «Константа3» содержит константу ’Название дисциплины строчными буквами: ’

- шестой столбец с именем «Дисциплина3» содержит Название дисциплины в БД строчными буквами

2) Вывести названия дисциплин и номера аудиторий с их номерами корпусов, которые (дисциплины) читаются преподавателями-профессорами факультета 'компьютерные науки' студентам 1 курса

3) Вывести заведующего кафедрой, в которой работает преподаватель Резниченко

4) Вывести номера групп факультета 'компьютерные науки', кураторы которых имеют зарплату (salary+commission) в диапазоне 900-1700

5)  Вывести названия факультетов, на которых читаются дисциплины, отличающиеся от следующих: охрана труда, техника безопасности, физическая культура,

6) Вывести пары имен преподавателей, удовлетворяющих следующему условию: первый преподаватель преподает группе на курсе выше, чем курс группы, которой преподает второй преподаватель.

7)  Вывести имена преподавателей и дату поступления их на работу, которые НЕ удовлетворяют следующему условию:

их ставка (salary) больше 2000 или их надбавка (commission) больше одной четвертой части ставки И

их непосредственный руководитель был принят на работу либо в диапазоне дат 01.01.1995-31.12.1996 либо в диапазоне дат 01.01.1998-07.08.1999 И

их непосредственный подчиненный либо является ассистентом либо имеет зарплату (salary+commission) в диапазоне 2000-3000

1.  Вариант 11

1) Вывести информацию о группах в следующем формате:

Группа <номер группы> курса <номер курса> с количеством студентов <количество студентов в группе> имеет рейтинг <рейтинг группы>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Сведения о группах»

Б) Информация выводится в следующих восьми столбцах:

- первый столбец с именем «Константа1» содержит константу ’Группа:’

- второй столбец с именем «Группа» содержит номер группы

- третий столбец с именем «Константа2» содержит константу ’курса’

- четвертый столбец с именем «Курс» содержит номер курса

- пятый столбец с именем «Константа3» содержит константу ’ с количеством студентов’

- шестой столбец с именем «К-во студентов» содержит количество студентов в группе

- седьмой столбец с именем «Константа4» содержит константу ’имеет рейтинг’

- восьмой столбец с именем «Рейтинг» содержит рейтинг группы

2) Вывести номера аудиторий с именами преподавателей, в которых (аудиториях) проводятся лабораторные занятия для студентов факультета 'компьютерные науки'

3) Вывести имена преподавателей факультета, деканом которого является Сидоров

4) Вывести названия факультетов, на которых работают преподаватели профессора с зарплатой (salary+commission) в диапазоне 1000-1500

5)  Вывести имена преподавателей-профессоров, которые читают лекции по дисциплине СУБД и которые работают на факультете, в названии которого имеется  подстрока 'ые на'

6) Вывести пары номеров аудиторий, удовлетворяющих следующим условиям:

- первая находится на третьем этаже

- вторая находится на втором этаже

- первая имеет количество мест больше, чем на 20, количества мест второй группы

7) Вывести названия дисциплин, которые удовлетворяют следующему условию:

дисциплина читается профессорами или доцентами или ассистентами И

дисциплина читается на 3 или 4 курсе И

дисциплина читается в понедельник или вторник или четверг

1.  Вариант 12

1) Вывести информацию о группах в следующем формате

На курсе <номер курса> имеется группа <номер группы>  у которой рейтинг <рейтинг>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о группах»

Б) Информация выводится в следующих шести столбцах

- первый столбец с именем «Константа1» содержит константу ’На курсе’

- второй столбец с именем «Курс» содержит номер курса

- третий столбец с именем «Константа2» содержит константу ’имеется группа’

- четвертый столбец с именем «Группа» содержит номер группы

- пятый столбец с именем «Константа3» содержит константу ’у которой рейтинг’

- шестой столбец с именем «Рейтинг» содержит рейтинг группы

2) Вывести номера групп, номера аудиторий с корпусами и названия преподавателей кафедры ИПО, которые (преподаватели) читают этим группам лекции по дисциплине 'базы данных'.

3) Вывести декана факультета, в котором работает преподаватель Резниченко

4) Вывести названия кафедр факультета 'компьютерные науки', на которых работают преподаватели-доценты с зарплатой (salary+commission) в диапазоне 1000-1500

5) Вывести названия кафедр, преподаватели которых являются кураторами групп кафедр, в названии которых имеется буква 'п'

6) Вывести пары названий кафедр, удовлетворяющих следующему условию: фонд финансирования факультета первой кафедры больше, чем на 10000, фонда финансирования факультета второй кафедры

7) Вывести названия дисциплин, которые НЕ удовлетворяют следующему условию:

дисциплина читается преподавателями кафедры 'ИПО' или кафедры 'программирование' И

дисциплина читается в аудитории 313 корпуса 6 или в аудитории 202 корпуса 5 И

дисциплина читается в понедельник первой недели или вторник второй недели

1.  Вариант 13

1) Выведите информацию о преподавателях в следующем формате:

Принятый на работу <дата приема на работу> <должность преподавателя>  <имя преподавателя>, имеет ставку <ставка> и надбавку <надбавка> Его телефон <номер телефона >

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация о преподавателях»

Б) Информация выводится в следующих шести столбцах:

- первый столбец с именем «Константа1» содержит константу ‘Принятый на работу’

- второй столбец с именем «Дата» содержит дату приема на работу

- третий столбец с именем «Должность» содержит должность преподавателя,

- четвертый столбец с именем «Преподаватель» содержит имя преподавателя,

- пятый столбец с именем «Констатна2» содержит константу ‘имеет ставку’

- шестой столбец с именем «Ставка» содержит ставку преподавателя

- седьмой  столбец с именем «Констатна3» содержит константу ‘и надбавку’

- восьмой столбец с именем «Надбавка» содержит надбавку преподавателя

- девятый  столбец с именем «Констатна4» содержит константу ‘Его телефон’

- десятый столбец с именем «Телефон» содержит номер телефона преподавателя

2) Вывести имена преподавателей-доцентов и названия дисциплин, которые они преподают студентам 3-го курса факультета 'компьютерные науки'

3) Вывести имена преподавателей, преподающие группе, у которой куратором является преподаватель Иванов.

4) Вывести имена преподавателей кафедры ИПО, являющихся кураторами групп с рейтингом в диапазоне 20-30

5) Вывести имена деканов факультетов, имена заведующих кафедр которых не содержат подстроки 'петр'.

6) Вывести пары названий кафедр, удовлетворяющих следующему условию: первая кафедра находится на  факультете с фондом финансирования большим, чем  на 10000, фонда финансирования факультета второй кафедры

7) Вывести номера аудиторий и их корпуса, которые удовлетворяют следующему условию:

они имеют вместимость в диапазоне 20-30 или в диапазоне 50-70 И

в них проводятся занятия в группах факультета 'компьютерные науки' или 'компьютерные системы' И

в них проводят занятия преподаватели доценты или ассистенты

1.  Вариант 14

1)  Выведите информацию о преподавателях в следующем формате:

<дата приема на работу>  был принят на работу  <имя преподавателя> на должность <должность преподавателя>  и с зарплатой <зарплата (salary+commission) преподавателя>

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Сведенияо преподавателях»

Б) Информация выводится в следующих шести столбцах:

- первый столбец с именем «Дата» содержит дату приема на работу

- второй столбец с именем «Констатна1 содержит константу ‘был принят на работу‘

- третий столбец с именем «Преподаватель» содержит имя преподавателя

- четвертый столбец с именем «Констатна2» содержит константу ‘на должность’

- пятый столбец с именем «Должность» содержит должность преподавателя,

- шестой  столбец с именем «Констатна3» содержит константу ‘и с зарплатой’

- восьмой столбец с именем «Зарплата» содержит зараплату (salary+commission) преподавателя

2) Вывести имена преподавателей-доцентов факультета 'компьютерные науки', читающих лекции по дисциплине 'базы данных', которые являются кураторами групп 3-го курса.

3) Вывести имена преподавателей, являющихся кураторами групп, которым преподает преподаватель Иванов

4)  Вывести номера групп и имена преподавателей, которые (преподаватели) проводят занятия в этих группах в аудиториях, расположенных в диапазоне этажей 3-10 в корпусе 6

5) Вывести названия кафедр, группам которых на 3 курсе читают дисциплины, в названии которых отсутствует подстрока 'ого_обесп'

6) Вывести пары названий кафедр, удовлетворяющих следующему условию: декан факультета первой кафедры имеет зарплату (salary+commission) большим, чем на 500, декана факультета второй кафедры

7) Вывести номера аудиторий и их корпуса, которые НЕ удовлетворяют следующему условию:

они находятся на любом из этих этажей: ’1’,  ’3’, ’4’, ’5’, ’7’, ’9’, ’10’ И

в них проводят занятия преподаватели из кафедр 'ИПО' или 'программирование' И

в них проводят занятия для студентов кафедр 'ИПО' или 'проектирование систем'

1.  Вариант 15

1)  По каждому преподавателю выведите его имя и процент отношения разности между ставкой и надбавкой и суммы ставки с надбавкой

2) Вывести номера групп и их курсов кафедры ИПО, которые имеют кураторов-профессоров, читающих лекции в аудиториях корпуса 6

3) Вывести номера групп, которым преподает преподаватель, являющийся куратором группы 304 3-го курса

4) Вывести имена преподавателей, которые проводят занятия в группах с количеством студентов в диапазоне 15-35 и в аудиториях с количеством мест в диапазоне 25-50

5) Вывести названия кафедр, группам которых на 3 курсе читают дисциплины, в названии которых имеется следующая подстрока: между буквами 'с'и 'д' имеются две любих других буквы.

6) Вывести пары названий кафедр, удовлетворяющих следующему условию: куратор группы первой кафедры имеет зарплату (salary+commission) меньшим, чем  300, зарплаты куратора группы второй кафедры

7 Вывести имена преподавателей и названия читаемых ими дисциплин, которые удовлетворяют следующему условию:

преподаватель принят на работу в диапазоне 01.01.1996-31.12.1998 или 01.01.2000-07-08-2005 И

дисциплины читаются в аудитории 309 корпуса 6 или в аудитории 202 корпуса 5  И

занятия по этим дисциплинам проводятся в понедельник первой недели  или четверг второй недели И

эти дисциплины читаются группам кафедр, расположенных в корпусах 5, 6 или 7

1.  Вариант 16

1) Для каждого из преподавателей из таблицы TEACHER выведите его имя, должность, ставку, надбавку, процент надбавки по отношению к ставке (имя этого столбца «Процент1») и процент ставки по отношению к надбавке (имя этого столбца «Процент1»).

2) Вывести названия факультетов и дисциплин, которые читаются на этих факультетах преподавателями-доцентами, являющимися кураторами студентов 1-го курса

3) Вывести названия кафедр, преподаватели которых преподают студентам кафедры ИПО

4) Вывести названия кафедр факультета 'компьютерные науки', непосредственные подчиненные заведующих которых (кафедр)  приняты на работу в диапазоне 01.10.2001 – 31.12.2001

5) Вывести имена непосредственных подчиненных заведующих кафедр ИПО, которые (заведующие кафедр) были приняты на работу в одну из следующих дат: 25.07.2001, 13.08, 2001, 01.12.2001

6) Вывести пары номеров групп вместе с их курсами, удовлетворяющих следующему условию: факультет первой группы имеет фонд финансирования  больше, чем на 2000, фонда факультет второй группы.

7)  Вывести имена преподавателей и дату их приема на работу, удовлетворяющих следующему условию:

их  зарплата (salary+commission) больше 1000 или они были приняты на работу после 01.01.2001 И

заведующие их кафедр имеют зарплату (salary+commission) больше 3000 или меньше 2500 И

деканы их факультетов  являются профессорами или доцентами.

1.  Вариант 17

1) Выведите информацию об аудиториях в следующем формате:

Аудитория <номер аудитории> находится на <номер этажа> этаже корпуса <номер корпуса> и имеет <количество мест> мест

Используйте для этого два варианта:

А) Информация выводится в одном столбце с названием «Информация об аудиториях»

Б) Информация выводится в следующих девяти столбцах:

- первый столбец с именем «Литерал1» содержит константу ‘Аудитория’

- второй  столбец с именем «Аудитория» содержит номер аудитории

- третий столбец с именем «Литерал2» содержит константу ‘находится на’

- четвертый столбец с именем «Этаж»  содержит номер этажа

- пятый столбец  с именем «Литерал3» содержит константу ‘этаже корпуса’

- шестой столбец с именем «Корпус»  содержит номер корпуса

- седьмой столбец  с именем «Литерал4» содержит константу ‘и имеет’

- восьмой столбец с именем «К-во мест»  содержит количество мест

- девятый столбец  с именем «Литерал5» содержит константу ‘мест’

2) Вывести загруженность занятиями аудитории 313 корпуса 6. Вывод содержит:

- неделя   - Неделя

- день недели   - ДН

- пара    - Пара

- тип занятия   - Тип

- номер группы и ее курс - ГРП   и  КРС

- имя преподавателя  - Преподаватель

Укажите имена столбцов результирующей таблицы так, как это указано в правой части приведенного выше списка

3) Вывести заведующего кафедрой, в которой работает преподаватель Резниченко

4) Вывести названия факультетов, на которых работают преподаватели профессора с зарплатой (salary+commission) в диапазоне 1000-1500

5) Вывести названия кафедр, преподаватели которых являются кураторами групп кафедр, в названии которых имеется буква 'п'

6) Вывести пары названий кафедр, удовлетворяющих следующему условию: первая кафедра находится на  факультете с фондом финансирования большим, чем  на 10000, фонда финансирования факультета второй кафедры

7) Вывести номера аудиторий и их корпуса, которые НЕ удовлетворяют следующему условию:

они находятся на любом из этих этажей: ’1’,  ’3’, ’4’, ’5’, ’7’, ’9’, ’10’ И

в них проводят занятия преподаватели из кафедр 'ИПО' или 'программирование' И

в них проводят занятия для студентов кафедр 'ИПО' или 'проектирование систем'

1.  Вариант 18

1) По каждому преподавателю выведите его имя и разность между датой приема на работу и деления ставки на надбавку (столбец с именем «Выражение»). Что получается в результате вычисления hiredate - (Salary/Commission)?

2) По каждой аудитории корпуса 6 вывести ее номер и названия кафедр, на которых проволятся занятия в этой аудитории студентам 1 курса.

3) Вывести названия факультетов, студентам которых преподают преподаватели кафедры ИПО

4) Вывести названия факультетов, преподаваетели-профессора которых имеют занятия  на парах в диапазоне 2-6

5) Вывести номера аудиторий и их корпуса, в которых преподаватели-ассистенты проводят занятия типа 'лабораторная' в один из следующих дней: понедельник, середа, четверг, суббота.

6) Вывести пары имен преподавателей,  удовлетворяющих следующему условию: первый преподаватель является куратором группы, имеющей рейтинг больше, чем в два раза, рейтинга группы, куратором которой является второй преподаватель

7)  Вывести имена преподавателей и дату поступления их на работу, которые НЕ удовлетворяют следующему условию:

их ставка (salary) больше 2000 или их надбавка (commission) больше одной четвертой части ставки И

их непосредственный руководитель был принят на работу либо в диапазоне дат 01.01.1995-31.12.1996 либо в диапазоне дат 01.01.1998-07.08.1999 И

их непосредственный подчиненный либо является ассистентом либо имеет зарплату (salary+commission) в диапазоне 2000-3000

1.  Control questions

Please, give answers to the following questions:

1) How can you specify output of all columns of the table?

2) Why it is necessary to qualify column names? How it is done?

3) Does query result contain duplicate values? How can you delete duplicate values of query result?

1.  What is the value of  an expression with null value?
2.  Why it is necessary to qualify table names in FROM query? How it is done?
3.  The following statement are not correct. Why?

 a) Select *

 b) Select * from teacher

 c) select name post salary FROM teacher;

1.  What output the following query:

 SELECT *

 FROM TEACHER

 WHERE Salary < 300 OR NOT(Hiredate = '17.06.2002' AND Post != 'professor');

1.  How can you join two or more tables.
2.  What is an outer join? Formulate and write query with outer join.
3.  What is the DUAL table?
4.  Show truth tables of logical operators NOT, AND, OR.
5.  Appendices 
   1.  Appendix A. SQL Oracle operators

An operator manipulates individual data items and returns a result. Data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.

1.  Unary and binary operators

The two general classes of operators are:

unary	A unary operator operates on only one operand. A unary operator typically appears with its operand in this format: operator operand.
binary	A binary operator operates on two operands. A binary operator appears with its operands in this format: operand1 operator operand2.

Other operators with special formats accept more than two operands. If an operator is given a null operand, the result is always null. The only operator that does not follow this rule is concatenation (||).

1.  Precedence 

Precedence is the order in which SQL Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence. SQL Oracle evaluates operators with equal precedence from left to right within an expression.

Table bellow lists the levels of precedence among SQL operators from high to low. Operators listed on the same line have the same precedence.

Operator	Operation
+, -	unary plus and minus
*, /	multiplication, division
+, -, ||	addition, subtraction,  concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN	comparison
NOT	logical negation
AND	conjunction
OR	disjunction

You can use parentheses in an expression to override operator precedence. Oracle evaluates expressions inside parentheses before evaluating those outside.

SQL also supports set operators (UNION, UNION ALL, INTERSECT, and MINUS), which combine sets of rows returned by queries, rather than individual data items. All set operators have equal precedence.

1.  Arithmetic Operators 

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. Table bellow lists arithmetic operators.

Operator	Purpose	Example
+, -	Denotes a positive or negative expression. These are unary operators.	SELECT * FROM orders  SELECT * FROM TEACHER WHERE qtysold = -1;  WHERE -Salary < 0;
*, /	Binary multiplication and division.	UPDATE TEACHER SET Salary = Salary * 1.1;
+, -	Binary addition and subtraction	SELECT Salary + Commission FROM TEACHER WHERE SYSDATE - Hiredate > 365;

Do not use two consecutive minus signs (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements (See Appendix C). You should separate consecutive minus signs with a space or a parenthesis.

1.  Concatenation Operator

The concatenation operator manipulates character strings. Table bellow describes concatenation operator.

Operator	Purpose	Example
||	Concatenates character strings.	SELECT 'Name is ' || Name FROM FACULTY;

The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings' datatypes.

Oracle provides the CONCAT character function as an alternative to the vertical bar operator.

Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length

1.  Comparison Operators 

Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN. For information on conditions, see Appendix A of Lab2. The table bellow lists SQL comparison operators.

Operator	Purpose	Example
=	Equality test	SELECT * FROM DEPARTMENT WHERE  Fund = 15000;
!=, ^=, <>, ¬=	Inequality test.	SELECT * FROM DEPARTMENT WHERE  Fund != 15000;
>, <	"Greater than" and "less than" tests	SELECT * FROM FACULTY SELECT * FROM FACULTY WHERE  Fund > 1500;  WHERE  Fund < 1500;
>=, <=	"Greater than or equal to" and "less than or equal to" tests	SELECT  * FROM FACULTY SELECT * FROM FACULTY WHERE  Fund >= 1500; WHERE Fund <= 1500;
IN	"Equal to any member of" test. Equivalent to "= ANY"	SELECT Name, Post FROM  TEACHER WHERE Post IN ('professor', 'assistant');
NOT IN	Equivalent to !=ALL. Evaluates to FALSE if any member of the set is NULL	SELECT * FROM FACULTY WHERE  Fund NOT IN   (SELECT Fund FROM FACULTY     WHERE Building IN ('3','5')};
ANY, SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=.  Evaluates to FALSE if the query returns no rows.	SELECT * FROM TEACHER WHERE  Salary = ANY    (SELECT Salary FROM TEACHER     WHERE Post = 'professor');
ALL	Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=.  Evaluates to TRUE if the query returns no rows.	SELECT * FROM TEACHER WHERE  Salary >= ALL (350, 400, 420);
[NOT] BETWEEN x AND y	[Not] greater than or equal to x and less than or equal to y.	SELECT * FROM TEACHER WHERE  Salary BETWEEN 350 AND 420;
EXISTS	TRUE if a subquery returns at least one row. It is unary operator.	SELECT Name FROM TEACHER WHERE   EXISTS (SELECT * FROM LECTURE  WHERE LECTURE.TchNo=TEACHER. TchNo);
x [NOT] LIKE y [ESCAPE ‘z’]	TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character, excepting percent (%) and underbar (_) may follow ESCAPE. A wildcard character is treated as a literal if preceded by the character designated as the escape character.	SELECT Name FROM TEACHER WHERE  Name LIKE 'Ив%';
IS [NOT] NULL	Tests for nulls. This is the only operator that you should use to test for nulls.	SELECT Name, Tel FROM   TEACHER WHERE  Commission IS NULL;

1.  Logical Operators 

A logical operator combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table that follows lists logical operators.

Operator	Purpose	Example
NOT	Unary operator. Returns TRUE if the  condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM TEACHER WHERE NOT (Salary BETWEEN 450 AND 500);
AND	Binary operator. Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.	SELECT * FROM   TEACHER WHERE  Hiredate < TO_DATE('01-JAN-2000', 'DD-MMM-YYYY') AND Salary > 500;
OR	Binary operator. Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.	SELECT * FROM   TEACHER WHERE  Post = 'assistant' AND Salary > 500;

Logical operators have the following meaning:

AND TRUE FALSE UNKNOWN TRUE TRUE FALSE UNKNOWN FALSE FALSE FALSE FALSE UNKNOWN UNKNOWN FALSE UNKNOWN

OR TRUE FALSE UNKNOWN TRUE TRUE TRUE TRUE FALSE TRUE FALSE UNKNOWN UNKNOWN TRUE UNKNOWN UNKNOWN

NOT TRUE FALSE UNKNOWN FALSE TRUE UNKNOWN

1.  Set Operators 

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Table lists SQL set operators.

Operator	Purpose	Example
UNION	Returns all rows selected by either query. Duplicate rows are deleted.	SELECT Building FROM DEPARTMENT UNION SELECT Building FROM FACULTY;
UNION ALL	Returns all rows selected by either query, including all duplicates.	SELECT Building FROM DEPARTMENT UNION ALL SELECT Building FROM ROOM;
INTERSECT	Returns all distinct rows selected by both queries.	SELECT Building FROM FACULTY INTERSECT SELECT Building FROM ROOM;
MINUS	Returns all distinct rows selected by the first query but not the second.	SELECT Building FROM ROOM MINUS SELECT Building FROM FACULTY;

All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:

•  If both queries select values of datatype CHAR, the returned values have datatype CHAR.
•  If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2.
  1.  Appendix B. Expressions 

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. An expression generally assumes the datatype of its components.

The expression “2*2” evaluates to 4 and has datatype NUMBER (the same datatype as its components).

The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:

TO_CHAR(TRUNC(SYSDATE+7))

You can use expressions in:

•  The select list of the SELECT statement
•  A condition of the WHERE clause and HAVING clause
•  The CONNECT BY and ORDER BY clauses
•  The VALUES clause of the INSERT statement
•  The SET clause of the UPDATE statement

For example, you could use an expression in place of the quoted string 'smith' in this UPDATE statement SET clause:

SET ename = 'smith';

This SET clause has the expression LOWER(ename) instead of the quoted string 'smith':

SET ename = LOWER(ename);

Expressions have several forms, as shown in the following syntax:

expr::= 

Oracle does not accept all forms of expressions in all parts of all SQL statements. You must use appropriate expression notation whenever expr appears in conditions, SQL functions, or SQL statements in other parts of SQL.

1.  Simple Expressions 

A simple expression specifies column, constant or NULL.

simple_expression::= 

Some valid simple expressions are:

emp.ename

'this is a text string'

10

1.  Compound Expressions 

A compound expression specifies a combination of other expressions.

compound_expression::= 

Note that some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within an aggregate function.

Some valid compound expressions are:

('CLARK' || 'SMITH')

LENGTH('MOOSE') * 57

SQRT(144) + 72

my_fun(TO_CHAR(sysdate,'DD-MMM-YY')

1.  Variable Expressions 

This type of expressions are not discussed here.

1.  Built-In Function Expressions 

A built-in function expression specifies a call to a single-row SQL function.

built_in_function_expression::= 

Some valid built-in function expressions are:

LENGTH('BLAKE')

ROUND(1234.567*43)

SYSDATE

For information on built-in functions, see Appendix of Lab 5.

1.  User-Defined Function Expressions 

A user-defined function expression specifies a call to a user-defined function.  Syntax of such calls are not discussed here.

1.  Type Constructor Expressions 

A type constructor expression specifies a call to a type constructor. The argument to the type constructor is any expression or subquery.  Syntax are not discussed here.

1.  CAST Expressions 

A CAST expression converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

CAST_expression::= 

CAST allows you to convert built-in datatypes or collection-typed values of one type into another built-in datatype or collection type. You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

For the operand, expr can be either a built-in datatype or a collection type, and subquery must return a single value of collection type or built-in type. MULTISET informs Oracle to take the result set of the subquery and return a collection value.

To cast a named collection type into another named collection type, the elements of both collections must be of the same type.

If the result set of subquery can evaluate to multiple rows, you must specify the MULTISET keyword. The rows resulting from the subquery form the elements of the collection value into which they are cast. Without the MULTISET keyword, the subquery is treated as a scalar subquery, which is not supported in the CAST expression. In other words, scalar subqueries as arguments of the CAST operator are not valid in Oracle8i.

Examples:

SELECT CAST ('1997-10-22' AS DATE) FROM DUAL;

SELECT * FROM t1 WHERE CAST (ROWID AS VARCHAR2) = '01234';

1.  CURSOR Expressions

A CURSOR expression returns a nested cursor. This form of expression is similar to the PL/SQL REF cursor.

CURSOR_expression::= 

A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:

•  The nested cursor is explicitly closed by the user
•  The parent cursor is reexecuted
•  The parent cursor is closed
•  The parent cursor is cancelled
•  An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up).

Restrictions: The following restrictions apply to the CURSOR expression:

•  Nested cursors can appear only in a SELECT statement that is not nested in any other query expression, except when it is a subquery of the CURSOR expression itself.
•  Nested cursors can appear only in the outermost SELECT list of the query specification.
•  Nested cursors cannot appear in views.
•  You cannot perform BIND and EXECUTE operations on nested cursors.

Example:

SELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum, p.projname

                                           FROM   projects p

                                           WHERE  p.empno = e.empno)

                       FROM TABLE(d.employees) e)

FROM dept d

WHERE d.dno = 605;

1.  Object Access Expressions 

This type of expressions are not discussed here.

1.  DECODE Expressions 

This type of expressions are not discussed here. 

1.  Expression List 

An expression list is a series of expressions separated by a comma. The entire series is enclosed in parentheses.

expression_list::= 

An expression list can contain up to 1000 expressions. Some valid expression lists are:

(10, 20, 40)

('SCOTT', 'BLAKE', 'TAYLOR')

(LENGTH('MOOSE') * 57, -SQRT(144) + 72, 69)

1.  Appendix C. Comments in SQL

Comments within SQL statements do not affect the statement execution, but they may make your application easier for you to read and maintain. You may want to include a comment in a statement that describes the statement's purpose within your application.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using either of these means:

•  Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
•  Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example:

These statements contain many comments:

SELECT  ename, sal + NVL(comm, 0), job, loc

   /* Select all employees whose compensation is

      greater than that of Jones.*/

FROM  emp, dept

       /*The DEPT table is used to get the department name.*/

WHERE  emp.deptno = dept.deptno

   AND sal + NVL(comm,0) >           /* Subquery:       */

        (SELECT sal + NLV(comm,0)

                             /* total compensation is sal + comm */

            FROM  emp

            WHERE ename = 'JONES');

SELECT ename,                          -- select the name

     sal + NVL(comm, 0),               -- total compensation

  job,                              -- job

     loc                               -- and city containing the office

FROM  emp,                            -- of all employees

  dept

WHERE  emp.deptno = dept.deptno

     AND sal + NVL(comm, 0) >          -- whose compensation

                                      -- is greater than

         (SELECT sal + NVL(comm,0)   -- the compensation

           FROM  emp

           WHERE  ename = 'JONES');   -- of Jones.

PAGE  32