Lab 12 : Grading System using Pl/SQL



Write a program in PL/SQL to Calculate Grade, GPA and CGPA after considering a result table where subject wise marks are inserted by computer operator. Insert your calculated result to another table using Trigger or Procedure or any other suitable way (suggested by you).

Lab 11 : Triggers, Prodedure and Cursor



Design any sample table(s) with necessary field and values. After considering the below questions and demonstrate all the issues.
a.       Triggers(Insert, Update, Delete)
b.      Procedure
c.       Cursor

Lab 10 : Design a ER Diagram for a Publisher for Book and Author Management



Draw the ER diagram to design a system for a publishing company that produces scientific books on various subjects. The books are written by authors who specialize in one particular subject. The company employs editors who not necessarily being specialists in a particular area, each take sole responsibility for editing one or more publications.
       
A publication covers essentially one of the specialist subjects and is normally written by a single author. When writing a particular book, each author works with editor, but may submit another work for publication to be supervised by other editors. To improve their competitiveness, the company tries to employ a variety of authors, more than one author being a specialist in a particular subject.

Lab 9 : Organizational Database Design



     Design a complete schema with relational diagram for the following situation: An organization has two types of employees, salaried and wage earning. Both the types of employees have some common properties, such as employee code, employee name, and employee address. However, the salaried employees have other additional properties: basic, allowance, and House Rent Allowance (HRA). The wage earning employees have distinct properties that are daily wage and overtime. You have to add additional table, if required, which is not mentioned above.

Lab 8 : ER diagram for a University Database



      A university DB contains information about professors (identified by SIN) and courses (identified by course ID). Professors teach courses; each of the following situations concerns the Teaches relationship set. For each situation, draw a combined ER diagram and cardianility ratio that describes it, and list all candidate keys of the Teaches relationship set.

a. Professors can teach the same course in several semesters, and each offering must be recorded.

b. Professors can teach the same course in several semesters, but only the most recent such offering needs to be records. Assume the above Situation (b) applies in all subsequent situations. Then, draw an ER-diagram that describes each of the following situations:

i). Every professor teaches a course, and every course is taught by some professor.

ii). Every professor teaches exactly one course, and every course is taught by exactly one professor.

Lab 7 : Binary Relation with M:N cardinality ratio for a supplier and engineering parts



Consider a binary relationship with M: N cardinality ratio. A supplier supplies many engineering parts, and a part is supplied by many suppliers. Do the following action and assume the circumstances to design the table.
                        a.   Design Table and consider non redundancy
                        b.   Necessary Constraints for cardinality radio.
                        c.   Check Constraint must be used for Suppliers’ City.
                        d.   Find the supplier NUMBER and CITY who supply part P2
e.  Find the supplier NUMBER(S) who belongs to London and   supplied part P2
f.   Find the supplier NUMBER who supplied maximum part

Lab 6 : Exercise on Create, Insert, Select and Join Query with DBMS concepts

A binary relationship with 1:1 cardinality ratio, an employee can have one car and a car is used by one employee only. Do the following action as per your DBMS concepts

1. Create necessary tables with constraint and necessary field
2. Insert values to tables
3. Show values
4. Find the id and the type of car assigned to employee ‘123’
5. Find the name of the employee and the make(type) of the car, if she/he drives car 987.         Where 987 is the id of a particular car.


Lab 5 : Exercise on SQL queries.

(a) Consider the relational table given below and answer the following SQL queries.   Think that the table has filled up by employee’s records.

 EMPLOYEE (ID_NO, NAME, DEPARTMENT_ID, SALARY)

·        List all the employees whose name starts with the letter ‘L’
·        Find the maximum salary given to employees in each department
·        Find the number of employees working in ‘accounts’ department
·        Find the second maximum salary from the table

·        Find the employee who is getting the minimum salary


(b) Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time.Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some the attributes of the exam entity set, as
  •         course with attributes name, department and c-number
  •         section  with attributes s-number and enrollment, and department as a weak entity     set on course
  •         room with attributes r-number, capacity, and building
  1.         Show and E-R diagram illustrating the use of all three additional entity sets listed.
  2.         Explain what application characteristics would influence the decision to include or not to include each of the additional entity sets.

 

LAB 4: Group By, Having Clause and Oracle AGGRIGATE FUNCTION

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and inORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BYexpressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. 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.


Using the GROUP BY Clause: Examples To return the minimum and maximum salaries for each department in the employees table, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     GROUP BY department_id
   ORDER BY department_id;

To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     WHERE job_id = 'PU_CLERK'
     GROUP BY department_id
   ORDER BY department_id;
Using the GROUP BY CUBE Clause: Example To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables hr.employees and hr.departments:
SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
      department_name) AS department_name,
   DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
   COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
   FROM employees e, departments d
   WHERE d.department_id = e.department_id
   GROUP BY CUBE (department_name, job_id)
   ORDER BY department_name, job_id;

DEPARTMENT_NAME                JOB_ID     Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting                     AC_ACCOUNT          1       99600
Accounting                     AC_MGR              1      144000
Accounting                     All Jobs            2      121800
Administration                 AD_ASST             1       52800
. . .
All Departments                ST_MAN              5       87360
All Departments                All Jobs          107  77798.1308
Using the GROUPING SETS Clause: Example The following example finds the sum of sales aggregated for three precisely specified groups:
  • (channel_desc, calendar_month_desc, country_id)
  • (channel_desc, country_id)
  • (calendar_month_desc, country_id)
Without the GROUPING SETS syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and UNION them, or run a query with a CUBE(channel_desc, calendar_month_desc, country_id) operation and filter out five of the eight groups it would generate.
SELECT channel_desc, calendar_month_desc, co.country_id,
      TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$
   FROM sales, customers, times, channels, countries co
   WHERE sales.time_id=times.time_id 
      AND sales.cust_id=customers.cust_id 
      AND sales.channel_id= channels.channel_id 
      AND customers.country_id = co.country_id
      AND channels.channel_desc IN ('Direct Sales', 'Internet') 
      AND times.calendar_month_desc IN ('2000-09', '2000-10')
      AND co.country_iso_code IN ('UK', 'US')
  GROUP BY GROUPING SETS( 
      (channel_desc, calendar_month_desc, co.country_id), 
      (channel_desc, co.country_id), 
      (calendar_month_desc, co.country_id) );

CHANNEL_DESC         CALENDAR CO SALES$
-------------------- -------- -- --------------
Direct Sales         2000-09  UK      1,378,126
Direct Sales         2000-10  UK      1,388,051
Direct Sales         2000-09  US      2,835,557
Direct Sales         2000-10  US      2,908,706
Internet             2000-09  UK        911,739
Internet             2000-10  UK        876,571
Internet             2000-09  US      1,732,240
Internet             2000-10  US      1,893,753
Direct Sales                  UK      2,766,177
Direct Sales                  US      5,744,263
Internet                      UK      1,788,310
Internet                      US      3,625,993
                     2000-09  UK      2,289,865
                     2000-09  US      4,567,797
                     2000-10  UK      2,264,622
                     2000-10  US      4,802,459
Using the HAVING Condition: Example To return the minimum and maximum salaries for the employees in each department whose lowest salary is less than $5,000, issue the next statement:
SELECT department_id, MIN(salary), MAX (salary)
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary) < 5000
   ORDER BY department_id;

DEPARTMENT_ID MIN(SALARY) MAX(SALARY)
------------- ----------- -----------
           10        4400        4400
           30        2500       11000
           50        2100        8200
           60        4200        9000

The following example uses a correlated subquery in a HAVING clause that eliminates from the result set any departments without managers and managers without departments:
SELECT department_id, manager_id 
   FROM employees 
   GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN   (SELECT department_id, manager_id FROM employees x 
      WHERE x.department_id = employees.department_id)
   ORDER BY department_id;
Using the ORDER BY Clause: Examples To select all purchasing clerk records from employees and order the results by commission in descending order, issue the following statement:
SELECT * 
   FROM employees
   WHERE job_id = 'PU_CLERK' 
   ORDER BY commission_pct DESC; 

To select information from employees ordered first by ascending department number and then by descending salary, issue the following statement:
SELECT last_name, department_id, salary
   FROM employees
   ORDER BY department_id ASC, salary DESC; 

To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement, which orders by ascending department_id, then descending salary, and finally alphabetically by last_name:
SELECT last_name, department_id, salary 
   FROM employees 
   ORDER BY 2 ASC, 3 DESC, 1; 
The aggregate functions are:
Aggregate Example
The following example calculates the average salary of all employees in the hr.employees table:
SELECT AVG(salary) "Average" FROM employees;

 Average
--------
    6425
Analytic Example
The following example calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:
SELECT manager_id, last_name, hire_date, salary,
   AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date 
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      15000
       100 Raphaely                  07-DEC-94      11000 11966.6667
       100 Kaufling                  01-MAY-95       7900 10633.3333
       100 Hartstein                 17-FEB-96      13000 9633.33333
       100 Weiss                     18-JUL-96       8000 11666.6667
       100 Russell                   01-OCT-96      14000 11833.3333
Aggregate Examples
The following examples use COUNT as an aggregate function:
SELECT COUNT(*) "Total" FROM employees;

     Total
----------
       107

SELECT COUNT(*) "Allstars" FROM employees
   WHERE commission_pct > 0;

 Allstars
---------
       35

SELECT COUNT(commission_pct) "Count" FROM employees;

     Count
----------
        35

SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;

  Managers
----------
        18
Analytic Example
The following example calculates, for each employee in the employees table, the moving count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary.
SELECT last_name, salary,
   COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
      AND 150 FOLLOWING) AS mov_count FROM employees;

LAST_NAME                     SALARY  MOV_COUNT
------------------------- ---------- ----------
Olson                           2100          3
Markle                          2200          2
Philtanker                      2200          2
Landry                          2400          8
Gee                             2400          8
Colmenares                      2500         10
Patel                           2500         10
Aggregate Example
The following example determines the highest salary in the hr.employees table:
SELECT MAX(salary) "Maximum" FROM employees;
 
   Maximum
----------
      24000
Analytic Example
The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.
SELECT manager_id, last_name, salary, 
   MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
   FROM employees;

MANAGER_ID LAST_NAME                     SALARY    MGR_MAX
---------- ------------------------- ---------- ----------
       100 Kochhar                        17000      17000
       100 De Haan                        17000      17000
       100 Raphaely                       11000      17000
       100 Kaufling                        7900      17000
       100 Fripp                           8200      17000
       100 Weiss                           8000      17000
Aggregate Example
The following statement returns the earliest hire date in the hr.employees table:
SELECT MIN(hire_date) "Earliest" FROM employees;
 
Earliest
---------
17-JUN-87
Analytic Example
The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.
SELECT manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     P_CMIN
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      17000
       100 Raphaely                  07-DEC-94      11000      11000
       100 Kaufling                  01-MAY-95       7900       7900
       100 Hartstein                 17-FEB-96      13000       7900
       100 Weiss                     18-JUL-96       8000       7900
       100 Russell                   01-OCT-96      14000       7900
       100 Partners                  05-JAN-97      13500       7900
       100 Errazuriz                 10-MAR-97      12000       7900
Aggregate Example
The following example calculates the sum of all salaries in the sample hr.employees table:
SELECT SUM(salary) "Total"
     FROM employees;
 
     Total
----------
    691400
Analytic Example
The following example calculates, for each manager in the sample table hr.employees, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary. You can see that Raphaely and Cambrault have the same cumulative total. This is because Raphaely and Cambrault have the identical salaries, so Oracle Database adds together their salary values and applies the same cumulative total to both rows.
SELECT manager_id, last_name, salary,
   SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
   RANGE UNBOUNDED PRECEDING) l_csum
   FROM employees;

MANAGER_ID LAST_NAME           SALARY     L_CSUM
---------- --------------- ---------- ----------
       100 Mourgos               5800       5800
       100 Vollman               6500      12300
       100 Kaufling              7900      20200
       100 Weiss                 8000      28200
       100 Fripp                 8200      36400
       100 Zlotkey              10500      46900
       100 Raphaely             11000      68900
       100 Cambrault            11000      68900
       100 Errazuriz            12000      80900
. . .
       149 Taylor                8600      30200
       149 Hutton                8800      39000
       149 Abel                 11000      50000
       201 Fay                   6000       6000
       205 Gietz                 8300       8300
           King                 24000      24000

LAB 3: OPERATOR

SQL অপারেটর


ওরাকল অপারেটর এর মাধ্যমে একটি বা একাধিক আউটপুট তৈরী করা যায় । একই ভাবে অপারেটর একটি অথবা একাধিক অপারেন্ড / ভ্যালু(মান) / ভেরিয়েবল / কলামের নাম এর উপর প্রয়োগ করা যায় । ডেটা বা ভ্যালু কোন ধরনের এ্যাকশন/কর্ম সম্পাদন করবে তা অপারেটর দ্বারা নির্ধারণ করা যায় । ডেটা টাইপের উপর ভিত্তি করে অপারেটর নির্নয় করা হয়। মাঝে মাঝে ডাটাটাইপের উপর নির্ভর করে অপারেটর স্টেটমেন্ট এর সিনট্যাক্স পরিবর্তন করতে হয় । এসকিউএল স্টেটমেন্টের সাথে যে সকল অপারেটর ব্যাবহার করা যায় তা নিন্মরুপ,
  • Unary and Binary Operators
  • Precedence
  • Arithmetic Operators
  • Concatenation Operator
  • Comparison Operators
  • Logical Operators: NOT, AND, OR
  • Set Operators: UNION [ALL], INTERSECT, MINUS
  • Other Built-In Operators
  • User-Defined Operators

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 (||).

Precedence


Precedence is the order in which 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. Oracle evaluates operators with equal precedence from left to right within an expression.

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

Table 3-1 SQL Operator Precedence  
Operator  Operation 

+, - 

identity, negation 

*, / 

multiplication, division 

+, -, || 

addition, subtraction, concatenation 

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN 

comparison 

NOT 

exponentiation, logical negation 

AND 

conjunction 

OR 

disjunction 

Precedence Example


In the following expression, multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.
1+2*3 


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.

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 3-2 lists arithmetic operators.

Table 3-2 Arithmetic Operators
Operator  Purpose  Example 

+ - 

When these denote a positive or negative expression, they are unary operators.
SELECT * FROM orders
 WHERE qtysold = -1;
SELECT * FROM emp
  WHERE -sal < 0;

When they add or subtract, they are binary operators.
SELECT sal + comm FROM emp
  WHERE SYSDATE - hiredate
  > 365;

* / 

Multiply, divide. These are binary operators. 
UPDATE emp
  SET sal = sal * 1.1;

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. You should separate consecutive minus signs with a space or a parenthesis.



Concatenation Operator


The concatenation operator manipulates character strings. Table 3-3 describes the concatenation operator.

Table 3-3 Concatenation Operator
Operator  Purpose  Example 

|| 

Concatenates character strings. 
SELECT 'Name is ' || ename
   FROM emp;

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.
On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.
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 string.


Example


This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then selects these values and concatenates them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.
 
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6),
        col3 VARCHAR2(6), col4 CHAR(6) );

Table created.

INSERT INTO tab1 (col1,  col2,     col3,     col4)
        VALUES   ('abc', 'def   ', 'ghi   ', 'jkl');

1 row created.

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Concatenation
------------------------
abcdef   ghi   jkl

Comparison Operators


Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN.



Table 3-4 lists comparison operators.

Table 3-4  Comparison Operators
Operator  Purpose  Example 
=

Equality test. 
SELECT *
  FROM emp
  WHERE sal = 1500;
!=
^=
<   >
¬=

Inequality test. Some forms of the inequality operator may be unavailable on some platforms. 
SELECT *
  FROM emp
  WHERE sal != 1500;
>

<

"Greater than" and "less than" tests. 
SELECT * FROM emp
  WHERE sal > 1500;
SELECT * FROM emp
  WHERE sal < 1500;
>=

<= 

"Greater than or equal to" and "less than or equal to" tests. 
SELECT * FROM emp
  WHERE sal >= 1500;
SELECT * FROM emp
  WHERE sal <= 1500;
IN
 
 

"Equal to any member of" test. Equivalent to "= ANY". 
SELECT * FROM emp
  WHERE job IN
  ('CLERK','ANALYST');
SELECT * FROM emp
  WHERE sal IN
  (SELECT sal FROM emp
   WHERE deptno = 30);
NOT IN 

Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL
SELECT * FROM emp
  WHERE sal NOT IN
  (SELECT sal FROM emp
  WHERE deptno = 30);
SELECT * FROM emp
  WHERE job NOT IN
  ('CLERK', 'ANALYST');
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 emp
  WHERE sal = ANY
  (SELECT sal FROM emp
  WHERE deptno = 30);
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 emp
  WHERE sal >=
  ALL ( 1400, 3000);
[NOT] 
BETWEEN x 
AND y

[Not] greater than or equal to x and less than or equal to y
SELECT * FROM emp
  WHERE sal
  BETWEEN 2000 AND 3000;
EXISTS 

TRUE if a subquery returns at least one row. 
SELECT ename, deptno 
  FROM dept
  WHERE EXISTS
  (SELECT * FROM emp
    WHERE dept.deptno 
    = emp.deptno);
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.
    See Also: "LIKE Operator"
SELECT * FROM tab1
  WHERE col1 LIKE
  'A_C/%E%' ESCAPE '/';
IS [NOT] 
NULL 

Tests for nulls. This is the only operator that you should use to test for nulls.
    See Also: "Nulls".
SELECT ename, deptno
  FROM emp
  WHERE comm IS NULL;

Additional information on the NOT IN and LIKE operators appears in the sections that follow.

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 the string 'TRUE' for each row:
SELECT 'TRUE' 
    FROM emp 
    WHERE deptno NOT IN (5,15); 


However, the following statement returns no rows:
SELECT 'TRUE' 
    FROM emp 
    WHERE deptno NOT IN (5,15,null); 


The above example returns no rows because the WHERE clause condition evaluates to:
deptno != 5 AND deptno != 15 AND deptno != null 


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

LIKE Operator


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:



char1 

Specify a value to be compared with a pattern. This value can have datatype CHAR or VARCHAR2.  

NOT 

The NOT keyword logically inverts the result of the condition, returning FALSE if the condition evaluates to TRUE and TRUE if it evaluates to FALSE.  

char2 

Specify 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 

Specify for esc_char 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. For example, you can issue the following query to find the salaries of all employees with names beginning with 'SM':
SELECT sal 
    FROM emp 
    WHERE ename 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 sal 
    FROM emp 
    WHERE ename = '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 sal 
    FROM emp 
    WHERE 'SM%' LIKE ename;


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%' 

Pattern Matching on Indexed Columns


When LIKE is used to search an indexed column for a pattern, Oracle can use the index to improve the statement's performance if the leading character in the pattern is not "%" or "_". In this case, Oracle can scan the index by this leading character. If the first character in the pattern is "%" or "_", the index cannot improve the query's performance because Oracle cannot scan the index.

LIKE Operator Examples


This condition is true for all ename values beginning with "MA":
ename LIKE 'MA%' 


All of these ename values make the condition TRUE:
MARTIN, MA, MARK, MARY 


Case is significant, so ename values beginning with "Ma," "ma," and "mA" make the condition FALSE.

Consider this condition:
ename LIKE 'SMITH_' 


This condition is true for these ename values:
SMITHE, SMITHY, SMITHS 


This condition is false for 'SMITH', since the special character "_" must match exactly one character of the ename value.

To search for employees with the pattern 'A_B' in their name:
SELECT ename 
    FROM emp 
    WHERE ename 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.

ESCAPE Option Example


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.

Patterns Without %


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

Example:


Consider the definition of this table and the values inserted into it:
CREATE TABLE freds (f CHAR(6), v VARCHAR2(6));
INSERT INTO freds VALUES ('FRED', 'FRED');


Because Oracle blank-pads CHAR values, the value of f is blank-padded to 6 bytes. v is not blank-padded and has length 4.

Logical Operators: NOT, AND, OR


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 3-5 lists logical operators.

Table 3-5 Logical Operators  
Operator  Function  Example 
NOT 

Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN
SELECT *
  FROM emp
  WHERE NOT (job IS NULL);
SELECT *
  FROM emp
  WHERE NOT 
  (sal BETWEEN 1000 AND 2000);
AND 

Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN
SELECT *
  FROM emp
  WHERE job = 'CLERK'
  AND deptno = 10;
OR 

Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN
SELECT *
  FROM emp
  WHERE job = 'CLERK'
  OR deptno = 10;

For example, in the WHERE clause of the following SELECT statement, the AND logical operator is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:
SELECT * 
    FROM emp 
    WHERE hiredate < TO_DATE('01-JAN-1984', 'DD-MON-YYYY') 
        AND sal > 1000;

NOT Operator


Table 3-6 shows the result of applying the NOT operator to a condition.

Table 3-6 NOT Truth Table


TRUE 

FALSE 

UNKNOWN 

NOT 

FALSE 

TRUE 

UNKNOWN 

AND Operator


Table 3-7 shows the results of combining two expressions with AND.

Table 3-7 AND Truth Table

AND 

TRUE 

FALSE 

UNKNOWN 

TRUE 

TRUE 

FALSE 

UNKNOWN 

FALSE 

FALSE 

FALSE 

FALSE 

UNKNOWN 

UNKNOWN 

FALSE 

UNKNOWN 

OR Operator


Table 3-8 shows the results of combining two expressions with OR.

Table 3-8 OR Truth Table

OR 

TRUE 

FALSE 

UNKNOWN 

TRUE 

TRUE 

TRUE 

TRUE 

FALSE 

TRUE 

FALSE 

UNKNOWN 

UNKNOWN 

TRUE 

UNKNOWN 

UNKNOWN 

Set Operators: UNION [ALL], INTERSECT, MINUS


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

Table 3-9 Set Operators
Operator  Returns 

UNION  

All rows selected by either query. 

UNION ALL 

All rows selected by either query, including all duplicates. 

INTERSECT 

All distinct rows selected by both queries. 

MINUS 

All distinct rows selected by the first query but not the second. 

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.

Set Operator Examples


Consider these two queries and their results:
SELECT part
    FROM orders_list1;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 

SELECT part 
    FROM orders_list2;

PART 
---------- 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 


The following examples combine the two query results with each of the set operators.

UNION Example


The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_DATE and TO_NUMBER functions) when columns do not exist in one or the other table:
SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_number(null), date_in
    FROM orders_list2;

PART       PARTNUM DATE_IN
---------- ------- -------- 
SPARKPLUG  3323165 
SPARKPLUG          10/24/98
FUEL PUMP  3323162
FUEL PUMP          12/24/99
TAILPIPE   1332999
TAILPIPE           01/01/01
CRANKSHAFT 9394991
CRANKSHAFT         09/12/02

SELECT part 
    FROM orders_list1 
UNION 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 


UNION ALL Example


The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:
SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 


Note that the UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. A part value that appears multiple times in either or both queries (such as 'FUEL PUMP') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.

INTERSECT Example


The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT part 
    FROM orders_list1 
INTERSECT 
SELECT part 
    FROM orders_list2;

PART 
---------- 
TAILPIPE 


MINUS Example


The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT part 
    FROM orders_list1 
MINUS 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 

Other Built-In Operators


Table 3-10 lists other SQL operators.

Table 3-10 Other SQL Operators
Operator  Purpose  Example 

(+) 

Indicates that the preceding column is the outer join column in a join.
SELECT ename, dname
  FROM emp, dept
  WHERE dept.deptno =
     emp.deptno(+);

PRIOR 

Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured, query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between parent and child rows. You can also use this operator in other parts of a SELECT statement that performs a hierarchical query. The PRIOR operator is a unary operator and has the same precedence as the unary + and - arithmetic operators.
    See Also: "Hierarchical Queries".
SELECT empno, ename, 
mgr
  FROM emp
  CONNECT BY
     PRIOR empno = 
mgr;


User-Defined Operators


Like built-in operators, user-defined operators take a set of operands as input and return a result. However, you create them with the CREATE OPERATOR statement, and they are identified by names (e.g., MERGE). They reside in the same namespace as tables, views, types, and stand-alone functions.

Once you have defined a new operator, you can use it in SQL statements like any other built-in operator. For example, you can use user-defined operators in the select list of a SELECT statement, the condition of a WHERE clause, or in ORDER BY clauses and GROUP BY clauses. However, you must have EXECUTE privilege on the operator to do so, because it is a user-defined object.

For example, if you define an operator CONTAINS, which takes as input a text document and a keyword and returns 1 if the document contains the specified keyword, you can then write the following SQL query:
SELECT * FROM emp WHERE contains (resume, 'Oracle and UNIX') = 1;




Popular Posts