Select an Output Location option. Click the Apply button. Statements can be executed from the SQL prompt or from a script file. Practice 1: Overview This is the first of many practices in this course. The solutions if you require them can be found in Appendix A. Practices are intended to cover all topics that are presented in the corresponding lesson.
The URL requires the host name, which your instructor will provide. Perform the practices slowly and precisely. You can experiment with saving and running command files.
If you have any questions at any time, ask your instructor. There are four coding errors in the following statement. Can you identify them? Your first task is to create some reports based on data from the Human Resources tables. The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first.
The HR department wants more descriptive column headings for its report on employees. Then run your query again. The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID separated by a comma and space and name the column Employee and Title. Separate each column output by a comma.
Limiting Rows Using a Selection In the example in the slide, assume that you want to display all the employees in department If the condition is true, the row meeting the condition is returned. In the syntax: WHERE restricts the query to rows that meet a condition condition is composed of column names, expressions, constants, and a comparison operator The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions.
Number constants, however, should not be enclosed in single quotation marks. All character searches are case sensitive.
Comparison Conditions Comparison conditions are used in conditions that compare one expression to another value or expression. Note: The symbols! The range that you specify contains a lower limit and an upper limit. You must specify the lower limit first. The IN condition is also known as the membership condition.
The IN condition can be used with any data type. You can select rows that match a character pattern by using the LIKE condition. The character pattern—matching operation is referred to as a wildcard search.
Two symbols can be used to construct the search string. Note the uppercase S. Names beginning with an s are not returned. The example in the slide displays the names of all employees whose last names have the letter o as the second character. This option specifies what the escape character is.
This causes the Oracle Server to interpret the underscore literally. A null value means the value is unavailable, unassigned, unknown, or inapplicable. The slide example retrieves the last names and managers of all employees who do not have a manager. Logical Conditions A logical condition combines the result of two component conditions to produce a single result based on those conditions, or it inverts the result of a single condition.
A row is returned only if the overall result of the condition is true. Character strings must be enclosed in quotation marks. Using the OR Operator In the example, either condition can be true for any record to be selected. Rules of Precedence The rules of precedence determine the order in which expressions are evaluated and calculated.
The table lists the default order of precedence. You can override the default order by using parentheses around the expressions that you want to calculate first. Rules of Precedence continued 1.
You can specify an expression, an alias, or a column position as the sort condition. Examples 1. The slide example sorts the result by the most recently hired employee. The slide example sorts the data by annual salary. You can sort query results by more than one column. The sort limit is the number of columns in the given table. If you want to reverse the order of a column, specify DESC after its name. I want to query different values.
Substitution Variables The examples so far have been hard-coded. In a finished application, the user would trigger the report, and the report would run without further prompting.
You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which the values are temporarily stored. When the statement is run, the value is substituted. Single-Ampersand Substitution Variable When running a report, users often want to restrict the data that is returned dynamically. You do not need to define the value of each variable. With the single ampersand, the user is prompted every time the command is executed, if the variable does not exist.
The same rule applies to the substitution variables. Enclose the variable in single quotation marks within the SQL statement itself. For each substitution variable in the SELECT statement, you are prompted to enter a value, and you then click the Continue button to proceed. If you do not enter a value for the substitution variable, you get an error when you execute the preceding statement.
The user sees the prompt for the value only once. At run time, this displays the employee number, name, salary, and department number for that employee. You should also have learned how to implement various operators and conditions. You can query users at run time and enable them to specify criteria. You make the SQL statements more reusable and generic by including ampersand substitution.
Run your query. Create a report that displays the last name and department number for employee number The HR department needs to find high-salary and low-salary employees. Create a report to display the last name, job ID, and start date for the employees with the last names of Matos and Taylor. Order the query in ascending order by start date. Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name.
Label the columns Employee and Monthly Salary, respectively. The HR department needs a report that displays the last name and hire date for all employees who were hired in Create a report to display the last name and job title of all employees who do not have a manager. Create a report to display the last name, salary, and commission of all employees who earn commissions.
Sort data in descending order of salary and commissions. Members of the HR department want to have more flexibility with the queries that you are writing. They would like a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt.
You can use the query that you created in practice exercise 1 and modify it. The HR department wants to run reports based on a manager. The HR department wants the ability to sort the report on a selected column.
Display all employee last names in which the third letter of the name is a. Display the last names of all employees who have both an a and an e in their last name. If you want an extra challenge, complete the following exercises: Objectives Functions make the basic query block more powerful, and they are used to manipulate data values.
This is the first of two lessons that explore functions. It focuses on single-row character, number, and date functions, as well as those functions that convert data from one type to another for example, conversion from character data to numeric data. Note: Most of the functions that are described in this lesson are specific to the Oracle version of SQL. There are different types of single-row functions.
These functions are also known as group functions covered in lesson 4. Single-Row Functions Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row that is returned by the query.
This can be represented by a column name or expression. Character Functions Single-row character functions accept character data as input and can return both character and numeric values. If n is omitted, all characters to the end of the string are returned. Note: The functions discussed in this lesson are only some of the available functions.
This is a feature that is available in Oracle8i and later versions. Using Case-Manipulation Functions The slide example displays the employee number, name, and department number of employee Higgins. Because both names are now lowercase, a match is found and one row is selected.
Using the Character-Manipulation Functions 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 fourth position of the job ID. Example Modify the SQL statement in the slide to display the data for those employees whose last names end with the letter n.
Number Functions Number functions accept numeric input and return numeric values. This section describes some of the number functions. Function Purpose ROUND column expression, n Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places If n is negative, numbers to left of the decimal point are rounded. TRUNC column expression, n Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero MOD m,n Returns the remainder of m divided by n Note: This list contains only some of the available number functions.
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 You will see examples later in this lesson. The DUAL table is useful when you want to return a value once only for example, the value of a constant, pseudocolumn, or expression that is not derived from a table with user data.
If the second argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is —2, the value is truncated to two decimal places to the left. If the second argument is —1, the value is truncated to one decimal place to the left.
Note: The MOD function is often used to determine if a value is odd or even. Oracle Date Format The Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. Valid Oracle dates are between January 1, B. However, dates are not stored in the database in this format. All the components of the date and time are stored. The complete data might be June 17, , p. However, when the date column is displayed on the screen, the century component is not displayed by default.
The DATE data type always stores year information as a four-digit number internally: two digits for the century and two digits for the year. For example, the Oracle database stores the year as or , and not just as 87 or Arithmetic with Dates 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. Arithmetic with Dates continued The example in the slide displays the last name and the number of weeks employed for all employees in department 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.
Your results may differ from the example. If a more current date is subtracted from an older date, the difference is a negative number. Date Functions Date functions operate on Oracle dates. 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. The noninteger part of the result represents a portion of the month.
The value of n must be an integer and can be negative. The value of char may be a number representing a day or a character string. If the format model fmt is omitted, date is rounded to the nearest day. If the format model fmt is omitted, date is truncated to the nearest day. This list is a subset of the available date functions. The format models are covered later in this lesson. Examples of format models are month and year. Date Functions continued For example, display the employee number, hire date, number of months employed, six-month review date, first Friday after hire date, and last day of the hire month for all employees who have been employed for fewer than 80 months.
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 For Part 1, complete questions 1—6 at the end of this lesson. However, the Oracle server internally converts such data types to Oracle data types. In some cases, the Oracle server uses data of one data type where it expects data of a different data type.
When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user. Implicit data type conversions work according to the rules that are explained in the next two slides. Explicit data type conversions are done by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type.
The first data type is the input data type; the second data type is the output. Note: Although implicit data type conversion is available, it is recommended that you do explicit data type conversion to ensure the reliability of your SQL statements. Implicit Data Type Conversion The assignment succeeds if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.
Implicit Data Type Conversion continued In general, the Oracle server uses the rule for expressions when a data type conversion is needed in places that are not covered by a rule for assignment conversions. If this parameter is omitted, this function uses the default date languages for the session. Be sure to separate the date value from the format model by a comma. Indicates B. Date Format Elements: Time Formats Use the formats that are listed in the following tables to display time information and literals and to change numerals to spelled numbers.
The hire date appears as 17 June This technique is especially useful with concatenation. The default is a period.
Decimal point in position specified You can specify multiple group separators in a number format model. The format model that you choose is based on the previously demonstrated format elements.
Without fx, Oracle ignores extra blanks. Without fx, numbers in the character argument can omit leading zeros.
There are two spaces after the month May and the number 24 in the following example. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two-digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element. Because the current year is greater than , the RR format interprets the year portion of the date from to The following command, on the other hand, results in no rows being selected because the YY format interprets the year portion of the date in the current century Nesting Functions Single-row functions can be nested to any depth.
Nested functions are evaluated from the innermost level to the outermost level. Some examples follow to show you the flexibility of these functions. Nesting Functions continued The slide example displays the last names of employees in department The evaluation of the SQL statement involves three steps: 1. The inner function retrieves the first eight characters of the last name. The outermost function converts the results to uppercase. The entire expression becomes the column heading because no column alias was given.
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, Order the results by hire date. General Functions These functions work with any data type and pertain to the use of null values in the expression list.
If expr1 is null, NVL2 returns expr3. The argum ent expr1 can have any data type. If any column value in an expression is null, the result is null. To calculate values for all employees, you must convert the null value to a number before applying the arithmetic operator. In the example in the slide, the NVL function is used to convert null values to zero.
If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned. The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.
If the data types of expr2 and expr3 are different, the Oracle server converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In the latter case, a data type conversion is not necessary. If they are equal, the function returns null. If they are not equal, the function returns the first expression.
You cannot specify the literal NULL for the first expression. When the lengths of the names are equal, a null value is displayed. When the lengths of the names are not equal, the length of the first name is displayed. If none of the WHEN Otherwise, the Oracle server returns null. For all other job roles, there is no increase in salary. 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 the expression is the same as search, result is returned. If the default value is omitted, a null value is returned where a search value does not match any of the result values. In this example, you determine the tax rate for each employee in department 80 based on the monthly salary.
Summary Single-row functions can be nested to any level. For Part 2, complete exercises 7— Remember that for nested functions, the results are evaluated from the innermost function to the outermost function. Write a query to display the current date. Label the column Date. The HR department needs a report to display the employee number, last name, salary, and salary increased by Label the column New Salary.
Label the column Increase. Run the revised query. Write a query that displays the last name with the first letter uppercase and all other letters lowercase and the length of the last name for all employees whose name starts with the letters J, A, or M.
Give each column an appropriate label. Rewrite the query so that the user is prompted to enter a letter that starts the last name.
For example, if the user enters H when prompted for a letter, then the output should show all employees whose last name starts with the letter H. The HR department wants to find the length of employment for each employee. For each employee, display the last name and calculate the number of months between today and the date on which the employee was hired.
Order your results by the number of months employed. Round the number of months up to the closest whole number. Note: Your results will differ. Label the column Dream Salaries.
Create a query to display the last name and salary for all employees. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week, starting with Monday. Each asterisk signifies a thousand dollars.
Sort the data in descending order of salary. Rewrite the statement in the preceding exercise using the CASE syntax. Objectives This lesson further addresses functions. It focuses on obtaining summary information such as averages for groups of rows. It discusses how to group rows in a table into smaller sets and how to specify search criteria for groups of rows.
Group functions operate on sets of rows to give one result per group. Group Functions Unlike single-row functions, group functions operate on sets of rows to give one result per group.
These sets may comprise the entire table or the table split into groups. Types of Group Functions Each of the functions accepts an argument.
The default is ALL and therefore does not need to be specified. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.
The slide example displays the most junior and most senior employees. In contrast, COUNT expr returns the number of non-null values that are in the column identified by expr. The slide example displays the number of employees in department The slide example displays the number of employees in department 80 who can earn a commission. Group Functions and Null Values All group functions ignore null values in the column.
The NVL function forces group functions to include null values. The average is calculated as the total commission that is paid to all employees divided by the number of employees receiving a commission four.
The average is calculated as the total commission that is paid to all employees divided by the total number of employees in the company Creating Groups of Data Until this point in our discussion, all group functions have treated the table as one large group of information. At times, however, you need to divide the table of information into smaller groups. You can then use the group functions to return summary information for each group. The example in the slide displays the department number and the average salary for each department.
The rows are grouped by department number, so the AVG function that is applied to the salary column calculates the average salary for each department. For example, the SELECT statement in the slide displays the average salaries for each department without displaying the respective department numbers.
Without the department numbers, however, the results do not look meaningful. Groups Within Groups Sometimes you need to see results for groups within groups. The slide shows a report that displays the total salary that is paid to each job title in each department. For example, the four stock clerks in department 50 are grouped together, and a single result total salary is produced for all stock clerks in the group.
Find the average salary for each department by grouping by department number. Rows are grouped. The group function is applied. The group function is applied to the group. The example excludes sales representatives and sorts the list by the total monthly salary. Nesting Group Functions Group functions can be nested to a depth of two. The slide example displays the maximum average salary. The Oracle server evaluates the clauses in the following order: 1.
Practice 4: Overview At the end of this practice, you should be familiar with using group functions and selecting groups of data. Circle either True or False. Group functions work across many rows to produce one result per group. Group functions include nulls in calculations. Find the highest, lowest, sum, and average salary of all employees.
Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Write a query to display the number of people with the same job. Generalize the query so that the user in the HR department is prompted for a job title. Determine the number of managers without listing them.
Label the column Number of Managers. Find the difference between the highest and lowest salaries. If you have time, complete the following exercises: 9. Create a report to display the manager number and the salary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Sort the output in descending order of salary. Create a query to display the total number of employees and, of that total, the number of employees hired in , , , and Create appropriate column headings.
Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading. Objectives This lesson explains how to obtain data from more than one table. A join is used to view information from multiple tables. Therefore, you can join tables together to view information from more than one table.
Obtaining Data from Multiple Tables Sometimes you need to use data from more than one table. The SQL—compliant join syntax does not offer any performance benefits over the Oracle- proprietary join syntax that existed in prior releases. For detailed information about the proprietary join syntax, see Appendix C. Defining Joins In the syntax: table1. Creating Natural Joins You can join tables automatically based on columns in the two tables that have matching data types and names.
Note: The join can happen on only those columns that have the same names and data types in both tables. If other common columns were present, the join would have used them all.
Therefore, columns that have the same name in both tables must be used without any qualifiers. Frequently, this type of join involves primary and foreign key complements. Note: Equijoins are also called simple joins or inner joins. Qualifying Ambiguous Column Names You need to qualify the names of the columns with the table name to avoid ambiguity. However, using the table prefix improves performance, because you tell the Oracle server exactly where to find the columns.
Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. Using Table Aliases 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.
Notice how table aliases are identified in the FROM clause in the example. The table name is specified in full, followed by a space and then the table alias. This lets you specify join conditions separate from any search or filter conditions in the WHERE clause.
You can also use the ON clause to join columns that have different names. Joining a Table to Itself Sometimes you need to join a table to itself. In this process, you look in the table twice.
Joining a Table to Itself continued The ON clause can also be used to join columns that have different names, within the same table or in a different table. Applying Additional Conditions to a Join You can apply additional conditions to the join.
Three-Way Joins A three-way join is a join of three tables. In SQL—compliant syntax, joins are performed from left to right. The second join condition can reference columns from all three tables. Nonequijoins A nonequijoin is a join condition containing something other than an equality operator.
The salary must be between any pair of the low and high salary ranges. It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salary grade table. Table aliases have been specified in the slide example for performance reasons, not because of possible ambiguity.
Returning Records with No Direct Match with Outer Joins If a row does not satisfy a join condition, the row does not appear in the query result. 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.
Any unmatched rows are not displayed in the output. To return the unmatched rows, you can use an outer join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other table satisfy the join condition.
Cartesian Products When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed.
All rows in the first table are joined to all rows in the second table. A Cartesian product tends to generate a large number of rows, and the result is rarely useful. You should always include a valid join condition unless you have a specific need to combine all rows from all tables.
Cartesian products are useful for some tests when you need to generate a large number of rows to simulate a reasonable amount of data. Cartesian Products continued A Cartesian product is generated if a join condition is omitted. Summary There are multiple ways to join tables. Practice 5: Overview This practice is intended to give you practical experience in extracting data from more than one table using SQL—compliant joins.
Write a query for the HR department to produce the addresses of all the departments. Show the location ID, street address, city, state or province, and country in the output. The HR department needs a report of all employees. Write a query to display the last name, department number, and department name for all employees. The HR department needs a report of employees in Toronto.
Display the last name, job, department number, and department name for all employees who work in Toronto. Label the columns Employee, Emp , Manager, and Mgr , respectively. Order the results by the employee number. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee.
The HR department needs a report on job grades and salaries. Then create a query that displays the name, job, department name, salary, and grade for all employees. The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies. This lesson covers single-row subqueries and multiple-row subqueries.
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 other query. The inner query or subquery returns a value that is used by the outer query or main query.
Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. 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.
The subquery generally executes first, and its output is used to complete the query condition for the main or outer query. Using a Subquery 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.
This type of subquery uses a single-row operator. The slide gives a list of single-row operators. The example in the slide displays employees whose job ID is the same as that of employee and whose salary is greater than that of employee The example consists of three query blocks: the outer query and two inner queries. The outer query block is then processed and uses the values that were returned by the inner queries to complete its search conditions. Note: The outer and inner queries can get data from different tables.
Using Group Functions in a Subquery You can display data from a main query by using a group function in a subquery to return a single row. The subquery is in parentheses and is placed after the comparison condition. 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 to the outer query. The SQL statement in the slide displays all the departments that have a minimum salary greater than that of department Errors with Subqueries One common error with subqueries occurs when more than one row is returned for a single-row subquery. In this case, the result of the subquery are , , , , , , and Problems with Subqueries A common problem with subqueries occurs when no rows are returned by the inner query.
Presumably, the intention is to find the employee whose name is Haas. The statement is correct but selects no rows when executed. There is no employee named Haas. So the subquery returns no rows.
The outer query finds no employee with a job ID equal to null, and so returns no rows. If a job existed with a value of null, the row is not returned because comparison of two null values yields a null; therefore, the WHERE condition is not true. Multiple-Row Subqueries Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row operator, instead of a single-row operator, with a multiple-row subquery.
Oracle Database Administration Workshop 12c and Higher 4. Student Guide - Volume I. Download PDF Read online. Students will achieve success with extensive end-of-chapter exercises and cases in this introductory Oracle10g text.
The Oracle10g Developer Suite on 2 CDs is included with every book to give students more hands-on experience using the database. This comprehensive text provides students with everything they will need to succeed without certification constraints or parameters. Get this from a library!
Guide to Oracle 10g. Guide To Oracle 10g. Disclaimer Pam Gamer This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your Technical Contributors own use in an Oracle training course.
The document may not be modified or altered in and Reviewers any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, Laurent Dereac license, post, transmit, or distribute this document in whole or in part without the Sujatha Kalastriraju express authorization of Oracle.
Knowing she would be called to account for herself C-THR Latest Dumps Free before she reached her destination, Garr waved an arm to draw the gaze of a man-at-arms on the outer wall, One of its advantages is supporting any electronic equipment when you practice 1z getfreedumps review. Tu was one of the Clerics who had hunted me this morning, MS Free Sample It's weird having a woman as a coach, not that I have anything against it, We have nothing to say against it. They are relations of Mrs, I shoved my bag into the luggage bin and Valid Practice Materials reached for hers, Rybba asked, still frightened, Michelle swallowed and started to bow and Samuel cleared his throat, do not bow.
It is the miracle of this test engine that 1z Latest Exam Registration any one of you can realize all his dreams and success has become the assured part ofyour future, It didn't take her long like 1z Latest Exam Registration this, before she shoved my head down, and kissed me furiously, moaning into our kiss. Fanny, Fanny, I see you smile and look cunning, New 1z Dumps Questions but, upon my honour, I never bribed a physician in my life, Then he went upunto the rock and found the lion which always 1z Formal Test kept him fellowship, and he stroked him upon the back and had great joy of him.
File Name: oracle 10g student guide.
0コメント