Beginers guide for oracle sql

SQL BEGINNER’S GUIDE Prepared By N.Jagadish Kumar Referred From Oracle

What is SQL •Sql - structure query language. •It is a language to communicate with database •Most popular database management system like MYSQL,POSTGRESSQL,SQL SERVER,DB2,ORACLE etc •Developed at IBM by Donald chamber line and Raymond Boyce. Initially called “structured English Query Language”(SEQUEL) •Sql is ANSI Standard

Why Learn SQL • SQL is a standard language to communicate with RDBMS • RDBMS Manage Critical Information in many aspects of our Daily lives. • Most application developed today either desktop or mobile uses RDMS as Backend to store and manage data. • Oracle corporation continues to be the Largest and most popular Database vendor.

…. • SQL and RDBMS re 30 years Old and are still popular Widely used in Information technology. • Learning SQL is a Sound Long term Investment. • SQL is used by many Commercial Applications to communicate with Back end Databases • SQL is mostly standardized only with few minor variations we can use it with any Relational database.

…… • SQL is easy to learn, over the years most of the basic functionalities remain unchanged. • SQL Knowledge will continue to remain as golden skills as there is No Viable and alternatives available. • High earning potential, Can help you to advance your career. • Growth of data and analytics creates high demand for SQL knowledge.

An overview of SQL • SQL is the standard language used to manipulate and retrieve data from these relational databases. SQL enables a programmer or database administrator to do the following: • Modify a database's structure • Change system security settings • Add user permissions on databases or tables • Query a database for information • Update the contents of a database

Retrieving Data Using the SQL SELECT Statement

Basic SELECT Statement – SELECT identifies the columns to be displayed. – FROM identifies the table containing those columns. SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

Selecting All Columns • NnSELECT * FROM departments; Note: Numeric data types are right justified and character data types are left justified. The asterisk (*) in select * tells the database to return all the columns associated with the given table described in the FROM clause A full table scan is used whenever there is no where clause on a query

Selecting Specific Columns SELECT department_id, location_id FROM departments;

Writing SQL Statements – SQL statements are not case sensitive. – SQL statements can be on one or more lines. – Keywords cannot be abbreviated or split across lines. – Clauses are usually placed on separate lines. – In SQL*Plus, you are required to end each SQL statement with a semicolon (;).

Arithmetic Expressions • Create expressions with number and date data by using arithmetic operators. Operator Description + Add - Subtract * Multiply / Divide

SELECT last_name, salary, salary + 300 FROM employees; Using Arithmetic Operators … Note that the resultant calculated column SALARY+300 is not a new column in the EMPLOYEES table; it is for display only. By default, the name of a new column comes from the calculation that generated it—in this case, salary+300 Operator Precedence If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators in an expression are of the same priority, then evaluation is done from left to right. You can use parentheses to force the expression that is enclosed by parentheses to be evaluated first.

SELECT last_name, salary, 12*salary+100 FROM employees; Operator Precedence SELECT last_name, salary, 12*(salary+100) FROM employees; … … 1 2 The first example in the slide displays the last name, salary, and annual compensation of employees. It calculates the annual compensation by multiplying the monthly salary by 12, plus a one-time bonus of $100. Note that multiplication is performed before addition. In the second example you can override the rules of precedence by using parentheses, Because of parentheses addition takes priority over multiplication.

Defining a Null Value – A null is a value that is unavailable, unassigned, unknown, or inapplicable. – A null is not the same as a zero or a blank space. SELECT last_name, job_id, salary, commission_pct FROM employees; … … In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales manager or sales representative can earn a commission. Other employees are not entitled to earn commissions. A null represents that fact

SELECT last_name, 12*salary*commission_pct FROM employees; Null Values in Arithmetic Expressions • Arithmetic expressions containing a null value evaluate to null. … … If any column value in an arithmetic expression is null, the result is null. For example, if you attempt to perform division by zero, you get an error. However, if you divide a number by null, the result is a null or unknown

Defining a Column Alias • A column alias: – Renames a column heading – Is useful with calculations – Immediately follows the column name (There can also be the optional AS keyword between the column name and alias.) – Requires double quotation marks if it contains spaces or special characters or if it is case sensitive Specify the alias after the column in the SELECT list using a space as a separator. By default, alias headings appear in uppercase. If the alias contains spaces or special characters (such as # or $), or if it is case sensitive, enclose the alias in double quotation marks (" ").

Using Column Aliases SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees; SELECT last_name AS name, commission_pct comm FROM employees; … … The second example displays the last names and annual salaries of all the employees. Because Annual Salary contains a space, it has been enclosed in double quotation marks. Notice that the column heading in the output is exactly the same as the column alias

Concatenation Operator • A concatenation operator: – Links columns or character strings to other columns – Is represented by two vertical bars (||) – Creates a resultant column that is a character expression SELECT last_name||job_id AS "Employees" FROM employees; … Null Values with the Concatenation Operator If you concatenate a null value with a character string, the result is a character string. LAST_NAME || NULL results in LAST_NAME.

Using Literal Character Strings … SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees; In the following example, the last name and salary for each employee are concatenated with a literal to give the returned rows more meaning. Notice the spaces between the single quotation marks in the SELECT statement that improves the readability of the output.

Alternative Quote (q) Operator – Specify your own quotation mark delimiter – Choose any delimiter – Increase readability and usabilitySELECT department_name || q'[, it's assigned Manager Id: ]' || manager_id AS "Department and Manager" FROM departments; …If the literal itself contains a single quotation mark, you can use the quote (q) operator and choose your own quotation mark delimiter. You can use any of the following any of the following character pairs: [ ], { }, ( ), or < > as the quotation mark delimiter.

Duplicate Rows • The default display of queries is all rows, including duplicate rows. SELECT department_id FROM employees; … SELECT DISTINCT department_id FROM employees; … 1 2 You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.

Limiting Rows Using a Selection “retrieve all employees in department 90” EMPLOYEES …

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; Using the WHERE Clause

SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen' ; Character Strings and Dates – Character strings and date values are enclosed in single quotation marks. – Character values are case sensitive, and date values are format sensitive. – The default date format is DD-MON-RR.

The WHERE Clause SYNTAX: • SELECT <COLUMNS> FROM <TABLE> WHERE <SEARCH CONDITION>; • SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective

INPUT/OUTPUT: SQL> SELECT * FROM EMP WHERE ENAME = 'KING'; Which would yield only one record: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ---------- --------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-NOV-81 5000 10 ANALYSIS: This simple example shows how you can place a condition on the data that you want to retrieve.

INPUT SQL> SELECT * FROM BIKES WHERE ENAME != 'KING'; OR SQL> SELECT * FROM BIKES WHERE ENAME <> 'KING'; OUTPUT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 ANALYSIS: Displays all the employees other than KING.

Comparison Conditions Operator Meaning = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to BETWEEN ...AND... Between two values (inclusive) IN(set) Match any of a list of values LIKE Match a character pattern IS NULL Is a null value Note: The symbols != and ^= can also represent the not equal to condition

Comparison Conditions Comparison conditions are used in conditions that compare one expression to another value or expression. They are used in the WHERE clause in the following format: Syntax ... WHERE expr operator value Example ... WHERE hire_date = '01-JAN-95' ... WHERE salary >= 6000 ... WHERE last_name = 'Smith' An alias cannot be used in the WHERE clause. SELECT * FROM EMP WHERE SAL >= 2000; SELECT * FROM EMP WHERE SAL >= 3000 AND SAL <= 4000; SELECT * FROM EMP WHERE SAL BETWEEN 3000 AND 4000; SELECT * FROM EMP WHERE SAL NOT BETWEEN 3000 AND 4000;

SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ; Using the BETWEEN Condition • Use the BETWEEN condition to display rows based on a range of values: Lower limit Upper limit

SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ; Using the IN Condition • Use the IN membership condition to test for values in a list:

The IN condition can be used with any data type. The following example returns a row from the EMPLOYEES table for any employee whose last name is included in the list of names in the WHERE clause: SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas'); If characters or dates are used in the list, they must be enclosed in single quotation marks ('').

SELECT first_name FROM employees WHERE first_name LIKE 'S%' ; Using the LIKE Condition – Use the LIKE condition to perform wildcard searches of valid search string values. – Search conditions can contain either literal characters or numbers: • % denotes zero or many characters. • _ denotes one character. The SELECT statement in the slide returns the employee first name from the EMPLOYEES table for any employee whose first name begins with the letter S. Note the uppercase S. Names beginning with an s are not returned.

INPUT: SELECT * FROM EMP WHERE ENAME LIKE ‘A%’; ANALYSIS Displays all the employees whose names begins with letter A INPUT: SELECT * FROM EMP WHERE ENAME NOT LIKE ‘A%’; ANALYSIS Displays all the employees whose names not beginning with letter A

INPUT: SELECT * FROM EMP WHERE ENAME LIKE ‘%A%’; ANALYSIS Displays all the employees whose names contains letter A (Any number of A’s) INPUT: INPUT: SELECT * FROM EMP WHERE ENAME LIKE ‘%A%A%’; ANALYSIS Displays all the names whose name contains letter A more than one time

INPUT: SELECT * FROM EMP WHERE HIREDATE LIKE ‘%DEC%’; ANALYSIS Displays all the employees who joined in the month of December. INPUT: SELECT * FROM EMP WHERE HIREDATE LIKE ‘%81’; ANALYSIS Displays all the employees who joined in the year 81. INPUT: SELECT * FROM EMP WHERE SAL LIKE ‘4%’; ANALYSIS Displays all the employees whose salary begins with number 4. (Implicit data conversion takes place).

Underscore (_) The underscore is the single-character wildcard. INPUT: SQL> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE ‘_A%’; OUTPUT: EMPNO ENAME ---------- ---------- 7521 WARD 7654 MARTIN 7900 JAMES ANALYSIS Displays all the employees whose second letter is A

INPUT: SQL> SELECT * FROM EMP WHERE ENAME LIKE ‘__A%’; OUTPUT: ENAME ---------- BLAKE CLARK ADAMS ANALYSIS Displays all the employees whose third letter is A ( Two underscores followed by A) INPUT: SQL> SELECT * FROM EMP WHERE ENAME LIKE ‘A%_%’ ESCAPE ‘’; OUTPUT: ENAME ---------- AVINASH_K ANAND_VARDAN ADAMS_P ANALYSIS Displays all the employees with underscore (_). ‘’ Escape character Underscore is used to identify a position in the string. To treat _ as a character we have to use Escape () character,

– You can combine pattern-matching characters: – You can use the ESCAPE identifier to search for the actual % and _ symbols. SELECT last_name FROM employees WHERE last_name LIKE '_o%' ; Using the LIKE Condition

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ; Using the NULL Conditions • Test for nulls with the IS NULL operator. The NULL conditions include the IS NULL condition and the IS NOT NULL condition. The IS NULL condition tests for nulls. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, you cannot test with = because a null cannot be equal or unequal to any value. The slide example retrieves the last names and managers of all employees who do not have a manager

Logical Conditions Operator Meaning AND Returns TRUE if both component conditions are true OR Returns TRUE if either component condition is true NOT Returns TRUE if the following condition is false You can use several conditions in one WHERE clause using the AND and OR operators.

INPUT: SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%A%’ and ENAME NOT LIKE ‘%A%A%’ OUTPUT ENAME ---------- ALLEN WARD MARTIN BLAKE CLARK JAMES ANALYSIS Displays all the employees whose names contains letter A exactly one time. SELECT * FROM EMP WHERE SAL >= 3000 AND SAL <= 4000; SELECT * FROM EMP WHERE SAL BETWEEN 3000 AND 4000; SELECT * FROM EMP WHERE SAL NOT BETWEEN 3000 AND 4000;

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ; Using the OR Operator OR requires either condition to be true: Therefore, any employee who has a job ID that contains the string ‘MAN’ or earns $10,000 or more is selected.

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ; Using the NOT Operator Note: The NOT operator can also be used with other SQL operators, such as BETWEEN, LIKE, and NULL. ... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP') ... WHERE salary NOT BETWEEN 10000 AND 15000 ... WHERE last_name NOT LIKE '%A%' ... WHERE commission_pct IS NOT NULL

Rules of Precedence You can use parentheses to override rules of precedence. Operator Meaning 1 Arithmetic operators 2 Concatenation operator 3 Comparison conditions 4 IS [NOT] NULL, LIKE, [NOT] IN 5 [NOT] BETWEEN 6 Not equal to 7 NOT logical condition 8 AND logical condition 9 OR logical condition

SELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id = 'AD_PRES' AND salary > 15000; Rules of Precedence SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000; 1 2

Rules of Precedence (continued) 1. Example of the Precedence of the AND Operator In this example, there are two conditions: The first condition is that the job ID is AD_PRES and the salary is greater than $15,000. The second condition is that the job ID is SA_REP. Therefore, the SELECT statement reads as follows: “Select the row if an employee is a president and earns more than $15,000, or if the employee is a sales representative.” 2. Example of Using Parentheses In this example, there are two conditions: The first condition is that the job ID is AD_PRES or SA_REP. The second condition is that salary is greater than $15,000. Therefore, the SELECT statement reads as follows: “Select the row if an employee is a president or a sales representative, and if the employee earns more than $15,000.”

Using the ORDER BY Clause – Sort retrieved rows with the ORDER BY clause: • ASC: ascending order, default • DESC: descending order – The ORDER BY clause comes last in the SELECT statement: SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; …

Sorting – Sorting in descending order: – Sorting by column alias: – Sorting by multiple columns: SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ; 1 SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal ; 2 SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC; 3

Default Ordering of Data The default sort order is ascending: Numeric values are displayed with the lowest values first (for example, 1 to 999). Date values are displayed with the earliest value first (for example, 01-JAN- 92 before 01-JAN-95). Character values are displayed in alphabetical order (for example, A first and Z last). Null values are displayed last for ascending sequences and first for descending sequences. You can sort by a column that is not in the SELECT list. Examples 1. To reverse the order in which rows are displayed, specify the DESC keyword after the column name in the ORDER BY clause. The slide example sorts the result by the most recently hired employee. 2. You can use a column alias in the ORDER BY clause. The slide example sorts the data by annual salary. 3. You can sort query results by more than one column. The sort limit is the number of columns in the given table. In the ORDER BY clause, specify the columns and separate the column names using commas. If you want to reverse the order of a column, specify DESC after its name.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ; Using the & Substitution Variable • Use a variable prefixed with an ampersand (&) to prompt the user for a value: Single-Ampersand Substitution Variable When running a report, users often want to restrict the data that is returned dynamically. iSQL*Plus provides this flexibility with user variables. Use an ampersand (&) to identify each variable in your SQL statement.

SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ; Character and Date Values with Substitution Variables • Use single quotation marks for date and character values: In a WHERE clause, date and character values must be enclosed in single quotation marks. The same rule applies to the substitution variables. Enclose the variable in single quotation marks within the SQL statement itself.

Using Single-Row Functions to Customize Output

Two Types of SQL Functions Single-row functions Multiple-row functions Return one result per row Return one result per set of rows Functions Single-Row Functions These functions operate on single rows only and return one result per row. Multiple-Row Functions Functions can manipulate groups of rows to give one result per group of rows.

Single-Row Functions • Single-row functions: – Manipulate data items – Accept arguments and return one value – Act on each row that is returned – Return one result per row – May modify the data type – Can be nested – Accept arguments that can be a column or an expression function_name [(arg1, arg2,...)] In the syntax: function_name is the name of the function arg1, arg2 is any argument to be used by the function. This can be represented by a column name or expression.

Single-Row Functions Conversion Character Number Date General Single-row functions

………….. • Single-Row Functions (continued) – This lesson covers the following single-row functions: • Character functions: Accept character input and can return both character and number values • Number functions: Accept numeric input and return numeric values • Date functions: Operate on values of the DATE data type (All date functions return a value of DATE data type except the MONTHS_BETWEEN function, which returns a number.) • Conversion functions: Convert a value from one data type to another • General functions: – NVL – NVL2 – NULLIF – COALESCE – CASE – DECODE

Character Functions Character functions LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE Case-manipulation functions Character-manipulation functions Single-row character functions accept character data as input and can return both character and numeric values

Case-Manipulation Functions • These functions convert case for character strings: Function Result LOWER('SQL Course') sql course UPPER('SQL Course') SQL COURSE INITCAP('SQL Course') Sql Course LOWER: Converts mixed-case or uppercase character strings to lowercase UPPER: Converts mixed-case or lowercase character strings to uppercase INITCAP: Converts the first letter of each word to uppercase and remaining letters to lowercase SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;

SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins'; Using Case-Manipulation Functions • Display the employee number, name, and department number for employee Higgins: SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected The WHERE clause of the first SQL statement specifies the employee name as higgins. Because all the data in the EMPLOYEES table is stored in proper case, the name higgins does not find a match in the table, and no rows are selected.

……. • The WHERE clause of the second SQL statement specifies that the employee name in the EMPLOYEES table is compared to higgins, converting the LAST_NAME column to lowercase for comparison purposes. Since both names are now lowercase, a match is found and one row is selected. The name in the output appears as it was stored in the database

Character-Manipulation Functions • These functions manipulate character strings: Function Result CONCAT('Hello', 'World') HelloWorld SUBSTR('HelloWorld',1,5) Hello LENGTH('HelloWorld') 10 INSTR('HelloWorld', 'W') 6 LPAD(salary,10,'*') *****24000 RPAD(salary, 10, '*') 24000***** REPLACE ('JACK and JUE','J','BL') BLACK and BLUE TRIM('H' FROM 'HelloWorld') elloWorld

Character-Manipulation Functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character-manipulation functions that are covered in this lesson. CONCAT: Joins values together (You are limited to using two parameters with CONCAT.) SUBSTR: Extracts a string of determined length LENGTH: Shows the length of a string as a numeric value INSTR: Finds the numeric position of a named character LPAD: Pads the character value right-justified RPAD: Pads the character value left-justified TRIM: Trims heading or trailing characters (or both) from a character string (If trim_character or trim_source is a character literal, you must enclose it in single quotation marks.) Note: You can use functions such as UPPER and LOWER with ampersand substitution. For example, use UPPER('&job_title') so that the user does not have to enter the job title in a specific case.

….. CHR CHR returns the character equivalent of the number it uses as an argument. The character it returns depends on the character set of the database. For this example the database is set to ASCII INPUT: SQL> SELECT CHR(65) FROM DUAL; OUTPUT: A

…………….. SQL>SELECT ENAME,UPPER(ENAME) UPPER_CASE,LOWER(ENAME) LOWER_CASE FROM EMP; ENAME ` UPPER_CASE LOWER_CASE ---------- ---------- ---------- SMITH SMITH smith ALLEN ALLEN allen WARD WARD ward JONES JONES jones MARTIN MARTIN martin BLAKE BLAKE blake CLARK CLARK clark SCOTT SCOTT scott KING KING king TURNER TURNER turner ADAMS ADAMS adams JAMES JAMES james FORD FORD ford MILLER MILLER miller

LPAD & RPAD • LPAD and RPAD take a minimum of two and a maximum of three arguments. The first argument is the character string to be operated on. The second is the number of characters to pad it with, and the optional third argument is the character to pad it with. The third argument defaults to a blank, or it can be a single character or a character string.

substr • This three-argument function enables you to take a piece out of a target string. • The first argument is the target string. • The second argument is the position of the first character to be output. • The third argument is the number of characters to show.

Instr • To find out where in a string a particular pattern occurs, use INSTR. Its first argument is the target string. The second argument is the pattern to match. The third and forth are numbers representing where to start looking and which match to report.

SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP'; Using the Character-Manipulation Functions 2 31 2 1 3 The slide example displays employee first names and last names joined together, the length of the employee last name, and the numeric position of the letter a in the employee last name for all employees who have the string REP contained in the job ID starting at the

Modify the SQL statement in the slide to display the data for those employees whose last names end with the letter n. SELECT employee_id, CONCAT(first_name, last_name) NAME, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(last_name, -1, 1) = 'n';

Number Functions – ROUND: Rounds value to specified decimal – TRUNC: Truncates value to specified decimal – MOD: Returns remainder of division Function Result ROUND(45.926, 2) 45.93 TRUNC(45.926, 2) 45.92 MOD(1600, 300) 100 Number functions accept numeric input and return numeric values

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL; Using the ROUND Function 3 31 2 1 2 The ROUND function rounds the column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is –2, the value is rounded to two decimal places to the left (rounded to the nearest unit of 10).

SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP'; Using the MOD Function • For all employees with job title of Sales Representative, calculate the remainder of the salary after it is divided by 5,000. The MOD function finds the remainder of the first argument divided by the second argument. The slide example calculates the remainder of the salary after dividing it by 5,000 for all employees whose job ID is SA_REP. Note: The MOD function is often used to determine if a value is odd or even.

Working with Dates • SYSDATE is a function that returns: – Date – Time SYSDATE is a date function that returns the current database server date and time. You can use SYSDATE just as you would use any other column name. Display the current date using the DUAL table. SELECT SYSDATE FROM DUAL;

Arithmetic with Dates – Add or subtract a number to or from a date for a resultant date value. – Subtract two dates to find the number of days between those dates. – Add hours to a date by dividing the number of hours by 24. Because the database stores dates as numbers, you can perform calculations using arithmetic operators such as addition and subtraction. You can add and subtract number constants as well as dates.

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90; Using Arithmetic Operators with Dates The example in the slide displays the last name and the number of weeks employed for all employees in department 90. It subtracts the date on which the employee was hired from the current date (SYSDATE) and divides the result by 7 to calculate the number of weeks that a worker has been employed

Date Functions Function Result MONTHS_BETWEEN Number of months between two dates ADD_MONTHS Add calendar months to date NEXT_DAY Next day of the date specified LAST_DAY Last day of the month ROUND Round date TRUNC Truncate date MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative.

ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Using Date Functions Function Result MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 ADD_MONTHS ('11-JAN-94',6) '11-JUL-94' NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95' LAST_DAY ('01-FEB-95') '28-FEB-95'

Last Day • LAST_DAY returns the last day of a specified month. For example, you need to know what the last day of the month

Months Between • Used to find the number of months between two given months.

Using Date Functions Assume SYSDATE = '25-JUL-03': Function Result ROUND(SYSDATE,'MONTH') 01-AUG-03 ROUND(SYSDATE ,'YEAR') 01-JAN-04 TRUNC(SYSDATE ,'MONTH') 01-JUL-03 TRUNC(SYSDATE ,'YEAR') 01-JAN-03 The ROUND and TRUNC functions can be used for number and date values. When used with dates, these functions round or truncate to the specified format model. Therefore, you can round dates to the nearest year or month.

Example Compare the hire dates for all employees who started in 1997. Display the employee number, hire date, and start month using the ROUND and TRUNC functions. SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH') FROM employees WHERE hire_date LIKE '%97';

Conversion Functions Implicit data type conversion Explicit data type conversion Data type conversion

Explicit Data Type Conversion NUMBER CHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE SQL provides three functions to convert a value from one data type to another:

To_Char • The primary use of TO_CHAR is to convert a number into a character. • Different Implementations may also use it to convert other data types, like Date, into a character, or to include different formatting arguments.

Using the TO_CHAR Function with Dates • • The format model: – Must be enclosed by single quotation marks – Is case-sensitive – Can include any valid date format element – Has an fm element to remove padded blanks or suppress leading zeros – Is separated from the date value by a comma TO_CHAR(date, 'format_model') Previously, all Oracle date values were displayed in the DD-MON-YY format. You can use the TO_CHAR function to convert a date from this default format to one that you specify.

Elements of the Date Format Model Element Result YYYY Full year in numbers YEAR Year spelled out (in English) MM Two-digit value for month MONTH Full name of the month MON Three-letter abbreviation of the month DY Three-letter abbreviation of the day of the week DAY Full name of the day of the week DD Numeric day of the month

Elements of the Date Format Model – Time elements format the time portion of the date: – Add character strings by enclosing them in double quotation marks: – Number suffixes spell out numbers: DD "of" MONTH 12 of OCTOBER ddspth fourteenth HH24:MI:SS AM 15:45:32 PM

Using the TO_CHAR Function with Numbers • These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character: Element Result 9 Represents a number 0 Forces a zero to be displayed $ Places a floating dollar sign L Uses the floating local currency symbol . Prints a decimal point , Prints a comma as thousands indicator TO_CHAR(number, 'format_model') TO_CHAR function, which translates a value of NUMBER data type to VARCHAR2 data type

SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst'; Using the TO_CHAR Function with Numbers

Using the TO_NUMBER and TO_DATE Functions – Convert a character string to a number format using the TO_NUMBER function: – Convert a character string to a date format using the TO_DATE function: – These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function. TO_NUMBER(char[, 'format_model']) TO_DATE(char[, 'format_model'])

Nesting Functions – Single-row functions can be nested to any level. – Nested functions are evaluated from deepest level to the least deep level. F3(F2(F1(col,arg1),arg2),arg3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3

SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US')) FROM employees WHERE department_id = 60; Nesting Functions 1. The inner function retrieves the first eight characters of the last name. Result1 = SUBSTR (LAST_NAME, 1, 8) 2. The outer function concatenates the result with _US. Result2 = CONCAT(Result1, '_US') 3. The outermost function converts the results to uppercase.

Example Display the date of the next Friday that is six months from the hire date. The resulting date should appear as Friday, August 13th, 1999. Order the results by hire date. SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS (hire_date, 6), 'FRIDAY'), 'fmDay, Month DDth, YYYY') "Next 6 Month Review" FROM employees ORDER BY hire_date;

General Functions • The following functions work with any data type and pertain to using nulls: – NVL (expr1, expr2) – NVL2 (expr1, expr2, expr3) – NULLIF (expr1, expr2) – COALESCE (expr1, expr2, ..., exprn)

NVL Function • Converts a null value to an actual value: – Data types that can be used are date, character, and number. – Data types must match: • NVL(commission_pct,0) • NVL(hire_date,'01-JAN-97') • NVL(job_id,'No Job Yet') Syntax NVL (expr1, expr2) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the target value for converting the null You can use the NVL function to convert any data type, but the return value is always the same as the data type of expr1.

SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80); Using the NVL2 Function 1 2 2 1 Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr2 is null

SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees; Using the NULLIF Function … 1 2 3 1 2 3 The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. Syntax NULLIF (expr1, expr2) In the syntax: expr1 is the source value compared to expr2 expr2 is the source value compared with expr1 (If it is not equal to expr1, expr1 is returned.)

Using the COALESCE Function – The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values. – If the first expression is not null, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions. The COALESCE function returns the first non-null expression in the list. Syntax COALESCE (expr1, expr2, ... exprn) In the syntax: expr1 returns this expression if it is not null expr2 returns this expression if the first expression is null and this expression is not null exprn returns this expression if the preceding expressions are null All expressions must be of the same data type.

SELECT last_name, COALESCE(manager_id,commission_pct, -1) comm FROM employees ORDER BY commission_pct; Using the COALESCE Function … In the example shown in the slide, if the MANAGER_ID value is not null, it is displayed. If the MANAGER_ID value is null, then the COMMISSION_PCT is displayed. If the MANAGER_ID and COMMISSION_PCT values are null, then the value –1 is displayed.

Conditional Expressions – Provide the use of IF-THEN-ELSE logic within a SQL statement – Use two methods: • CASE expression • DECODE function

CASE Expression • Facilitates conditional inquiries by doing the work of an IF- THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees; Using the CASE Expression • Facilitates conditional inquiries by doing the work of an IF- THEN-ELSE statement: … …

In the SQL statement in the slide, the value of JOB_ID is decoded. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary. The same statement can be written with the DECODE function. This is an example of a searched CASE expression. In a searched CASE expression, the search occurs from left to right until an occurrence of the listed condition is found, and then it returns the return expression. If no condition is found to be true, and if an ELSE clause exists, the return expression in the ELSE clause is returned; otherwise, NULL is returned. SELECT last_name,salary, (CASE WHEN salary<5000 THEN 'Low' WHEN salary<10000 THEN 'Medium' WHEN salary<20000 THEN 'Good' ELSE 'Excellent' END) qualified_salary FROM employees

SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees; Using the DECODE Function … …

In the SQL statement in the slide, the value of JOB_ID is tested. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary. The same statement can be expressed in pseudocode as an IF- THEN-ELSE statement: IF job_id = 'IT_PROG' THEN salary = salary*1.10 IF job_id = 'ST_CLERK' THEN salary = salary*1.15 IF job_id = 'SA_REP' THEN salary = salary*1.20 ELSE salary = salary

Group function • These functions are also referred to as group functions. They return a value based on the values in a column.

Count • The function COUNT returns the number of rows that satisfy the condition in the WHERE clause.

Group by • Group by statement groups all the rows with the same column value.Use to generate summary output from the available data. • Whenever we use a group function in the SQL statement, we have to use a group by clause • You cannot use a column alias in the GROUP BY clause. • The GROUP BY column does not have to be in the SELECT clause.

HAVING CLAUSE Whenever we are using a group function in the condition, we have to use having clause. Having clause is used along with group by clause. Use the HAVING clause to restrict groups: 1. Rows are grouped. 2. The group function is applied. 3. Groups matching the HAVING clause are displayed. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];

JOINS • One of the most powerful features of SQL is its capability to gather and manipulate data from across several tables. • Without this feature you would have to store all the data elements necessary for each application in one table. • Without common tables you would need to store the same data in several tables There are six types of joins 1. Equi-Join 2. Non Equi-Join 3. Left Outer Join 4. Right Outer Join 5. Full Outer Join 6. Self Join

Obtaining Data from Multiple Tables EMPLOYEES DEPARTMENTS … …

Joining Tables Using SQL:1999 Syntax • Use a join to query data from more than one table: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ; Retrieving Records with Natural Joins In the example in the slide, the LOCATIONS table is joined to the DEPARTMENT table by the LOCATION_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.

Natural Joins with a WHERE Clause Additional restrictions on a natural join are implemented by using a WHERE clause. The following example limits the rows of output to those with a department ID equal to 20 or 50: SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN (20, 50);

Creating Joins with the USING Clause – If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin. – Use the USING clause to match only one column when more than one column matches. – Do not use a table name or alias in the referenced columns. – The NATURAL JOIN and USING clauses are mutually exclusive. Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns that are referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

SELECT employees.employee_id, employees.last_name, departments.location_id, department_id FROM employees JOIN departments USING (department_id) ; Retrieving Records with the USING Clause … The slide example joins the DEPARTMENT_ID column in the EMPLOYEES and DEPARTMENTS tables, and thus shows the location where an employee works.

For example, the following statement is valid: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400; The following statement is invalid because the LOCATION_ID is qualified in the WHERE clause: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400;

SELECT e.employee_id, e.last_name, d.location_id, department_id FROM employees e JOIN departments d USING (department_id) ; Using Table Aliases – Use table aliases to simplify queries. – Use table aliases to improve performance. Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. You can use table aliases instead of table names. Just as a column alias gives a column another name, a table alias gives a table another name. Table aliases help to keep SQL code smaller, therefore using less memory.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id); Retrieving Records with the ON Clause … In this example, the DEPARTMENT_ID columns in the EMPLOYEES and DEPARTMENTS table are joined using the ON clause. Wherever a department ID in the EMPLOYEES table equals a department ID in the DEPARTMENTS table, the row is returned. You can also use the ON clause to join columns that have different names.

Self-Joins Using the ON Clause MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table. EMPLOYEES (WORKER) EMPLOYEES (MANAGER) … … Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join.

…… – For example, to find the name of Lorentz’s manager, you need to: • Find Lorentz in the EMPLOYEES table by looking at the LAST_NAME column. • Find the manager number for Lorentz by looking at the MANAGER_ID column. Lorentz’s manager number is 103. • Find the name of the manager with EMPLOYEE_ID 103 by looking at the LAST_NAME column. Hunold’s employee number is 103, so Hunold is Lorentz’s manager. – In this process, you look in the table twice. The first time you look in the table to find Lorentz in the LAST_NAME column and MANAGER_ID value of 103. The second time you look in the EMPLOYEE_ID column to find 103 and the LAST_NAME column to find Hunold.

Self-Joins Using the ON Clause SELECT e.last_name emp, m.last_name mgr FROM employees e JOIN employees m ON (e.manager_id = m.employee_id); …

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ; Applying Additional Conditions to a Join To add additional conditions to the ON clause, you can add AND clauses. Alternatively, you can use a WHERE clause to apply additional conditions: SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.manager_id = 149;

SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id; Creating Three-Way Joins with the ON Clause … So the first join to be performed is EMPLOYEES JOIN DEPARTMENTS. The first join condition can reference columns in EMPLOYEES and DEPARTMENTS but cannot reference columns in LOCATIONS. The second join condition can reference columns from all three tables.

SELECT e.last_name, e.salary, j.grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal; Retrieving Records with Non-Equijoins … All of the employees’ salaries lie within the limits that are provided by the job grade table. That is, no employee earns less than the lowest value contained in the LOWEST_SAL column or more than the highest value contained in the HIGHEST_SAL column. Note: Other conditions (such as <= and >=) can be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN. Table aliases have been specified in the slide example for performance reasons, not because of possible ambiguity.

Outer Joins EMPLOYEESDEPARTMENTS There are no employees in department 190. … If a row does not satisfy a join condition, the row does not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, department ID 190 does not appear because there are no employees with that department ID recorded in the EMPLOYEES table. Instead of seeing 20 employees in the result set, you see 19 records. To return the department record that does not have any employees, you can use an outer join.

INNER Versus OUTER Joins – In SQL:1999, the join of two tables returning only matched rows is called an inner join. – A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join. – A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join. Joining tables with the NATURAL JOIN, USING, or ON clauses results in an inner join. Any unmatched rows are not displayed in the output. To return the unmatched rows, you can use an outer joinThere are three types of outer joins: LEFT OUTER RIGHT OUTER FULL OUTER

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ; LEFT OUTER JOIN … This query retrieves all rows in the EMPLOYEES table, which is the left table even if there is no match in the DEPARTMENTS table

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ; RIGHT OUTER JOIN … This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no match in the EMPLOYEES table.

SELECT e.last_name, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; FULL OUTER JOIN … This query retrieves all rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table.

SELECT last_name, department_name FROM employees CROSS JOIN departments ; Creating Cross Joins – The CROSS JOIN clause produces the cross- product of two tables. – This is also called a Cartesian product between the two tables. …

Using Subqueries to Solve Queries

Using a Subquery to Solve a Problem • Who has a salary greater than Abel’s? Which employees have salaries greater than Abel’s salary? Main query: What is Abel’s salary? Subquery: To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the

– The subquery (inner query) executes once before the main query (outer query). – The result of the subquery is used by the main query. SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); Subquery Syntax In the syntax: operator includes a comparison condition such as >, =, or IN A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause

SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel'); Using a Subquery 11000 In the slide, the inner query determines the salary of employee Abel. The outer query takes the result of the inner query and uses this result to display all the employees who earn more than this amount

Guidelines for Using Subqueries – Enclose subqueries in parentheses. – Place subqueries on the right side of the comparison condition. – The ORDER BY clause in the subquery is not needed unless you are performing Top-N analysis. – Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries.

Types of Subqueries – Single-row subquery – Multiple-row subquery Main query Subquery returns ST_CLERK ST_CLERK SA_MAN Main query Subquery returns Single-row subqueries: Queries that return only one row from the inner SELECT statement Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement

Single-Row Subqueries – Return only one row – Use single-row comparison operators Operator Meaning = Equal to > Greater than >= Greater than or equal to < Less than <= Less than or equal to <> Not equal to

SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143); Executing Single-Row Subqueries ST_CLERK 2600 The example in the slide displays employees whose job ID is the same as that of employee 141 and whose salary is greater than that of employee 143.

SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); Using Group Functions in a Subquery 2500 The example in the slide displays the employee last name, job ID, and salary of all employees whose salary is equal to the minimum salary. The MIN group function returns a single value (2500) to the outer query

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); The HAVING Clause with Subqueries – The Oracle server executes subqueries first. – The Oracle server returns results into the HAVING clause of the main query. 2500 The SQL statement in the slide displays all the departments that have a minimum salary greater than that of department 50.

SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); What Is Wrong with This Statement? ERROR at line 4: ORA-01427: single-row subquery returns more than one row Single-row operator with multiple-row subquery The WHERE clause contains an equal (=) operator, a single-row comparison operator that expects only one value. The = operator cannot accept more than one value from the subquery and therefore generates the error. To correct this error, change the = operator to IN.

SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); Will This Statement Return Rows? no rows selected Subquery returns no values. There is no employee named Haas. So the subquery returns no rows. The outer query takes the results of the subquery (null) and uses these results in its WHERE clause. The outer query finds no employee with a job ID equal to null, and so returns no rows

Multiple-Row Subqueries – Return more than one row – Use multiple-row comparison operators Operator Meaning IN Equal to any member in the list ANY Compare value to each value returned by the subquery ALL Compare value to every value returned by the subquery The multiple-row operator expects one or more values: SELECT last_name, salary, department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Using the ANY Operator in Multiple-Row Subqueries 9000, 6000, 4200 … The slide example displays employees who are not IT programmers and whose salary is less than that of any IT programmer. The maximum salary that a programmer earns is $9,000. <ANY means less than the maximum. >ANY means more than the minimum. =ANY is equivalent to IN.

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Using the ALL Operator in Multiple-Row Subqueries 9000, 6000, 4200 . The slide example displays employees whose salary is less than the salary of all employees with a job ID of IT_PROG and whose job is not IT_PROG. >ALL means more than the maximum, and <ALL means less than the minimum. The NOT operator can be used with IN, ANY, and ALL operators.

Data Manipulation Language – A DML statement is executed when you: • Add new rows to a table • Modify existing rows in a table • Remove existing rows from a table – A transaction consists of a collection of DML statements that form a logical unit of work.

Adding a New Row to a Table DEPARTMENTS New row Insert new row into the DEPARTMENTS table

Inserting New Rows – Insert a new row containing values for each column. – List values in the default order of the columns in the table. – Optionally, list the columns in the INSERT clause. – Enclose character and date values in single quotation marks. INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); 1 row created.

INSERT INTO departments VALUES (100, 'Finance', NULL, NULL); 1 row created. INSERT INTO departments (department_id, department_name ) VALUES (30, 'Purchasing'); 1 row created. Inserting Rows with Null Values – Implicit method: Omit the column from the column list. • Explicit method: Specify the NULL keyword in the VALUES clause.

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100); 1 row created. Inserting Special Values • The SYSDATE function records the current date and time. The slide example records information for employee Popp in the EMPLOYEES table. It supplies the current date and time in the HIRE_DATE column. It uses the SYSDATE function for current date and time.

– Add a new employee. – Verify your addition. Inserting Specific Date Values INSERT INTO employees VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30); 1 row created.

INSERT INTO departments (department_id, department_name, location_id) VALUES (&department_id, '&department_name',&location); Creating a Script – Use & substitution in a SQL statement to prompt for values. – & is a placeholder for the variable value. 1 row created.

Copying Rows from Another Table – Write your INSERT statement with a subquery: – Do not use the VALUES clause. – Match the number of columns in the INSERT clause to those in the subquery. INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%'; 4 rows created. The number of columns and their data types in the column list of the INSERT clause must match the number of values and their data types in the subquery To create a copy of the rows of a table, use SELECT * in the subquery: INSERT INTO copy_emp SELECT * FROM employees;

Changing Data in a Table EMPLOYEES Update rows in the EMPLOYEES table:

– Modify existing rows with the UPDATE statement: – Update more than one row at a time (if required). UPDATE table SET column = value [, column = value, ...] [WHERE condition]; UPDATE Statement Syntax In the syntax: table is the name of the table column is the name of the column in the table to populate value is the corresponding value or subquery for the column condition identifies the rows to be updated and is composed of column names, expressions, constants, subqueries, and comparison operators

– Specific row or rows are modified if you specify the WHERE clause: – All rows in the table are modified if you omit the WHERE clause: Updating Rows in a Table UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated. UPDATE copy_emp SET department_id = 110; 22 rows updated.

UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 1 row updated. Updating Two Columns with a Subquery • Update employee 114’s job and salary to match that of employee 205.

UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated. Updating Rows Based on Another Table • Use subqueries in UPDATE statements to update • rows in a table based on values from another table:

Delete a row from the DEPARTMENTS table: Removing a Row from a Table DEPARTMENTS

DELETE Statement • You can remove existing rows from a table by using the DELETE statement: DELETE [FROM] table [WHERE condition];

Deleting Rows from a Table – Specific rows are deleted if you specify the WHERE clause: – All rows in the table are deleted if you omit the WHERE clause: DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. DELETE FROM copy_emp; 22 rows deleted. If you omit the WHERE clause, all rows in the table are deleted. The second example in the slide deletes all the rows from the COPY_EMP table, because no WHERE clause has been specified.

Deleting Rows Based on Another Table • Use subqueries in DELETE statements to remove rows from a table based on values from another table: DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 1 row deleted.

TRUNCATE Statement – Removes all rows from a table, leaving the table empty and the table structure intact – Is a data definition language (DDL) statement rather than a DML statement; cannot easily be undone – Syntax: – Example: TRUNCATE TABLE table_name; TRUNCATE TABLE copy_emp; TRUNCATE statement to quickly remove all rows from a table or cluster. Removing rows with the TRUNCATE statement is faster than removing them with the DELETE statement for the following reasons: The TRUNCATE statement is a data definition language (DDL) statement and generates no rollback information

Beginers guide for oracle sql

More Related Content

What's hot

Similar to Beginers guide for oracle sql

More from N.Jagadish Kumar

Recently uploaded

Beginers guide for oracle sql