Select all the tables from the data dictionary. There is no real need to store reservations any longer. You can drop the table. Show a list of the top 10 titles rented in the last month grouped by category. Additional Practices: Case Study continued Show a list of titles who have a status of rented. Additional Practices: Case Study Solutions continued 1.
Additional Practices: Case Study Solutions continued 9. Show a list of members who have rented two or more times.
Show a list of members who have 99 in their phone numbers. Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd? Uploaded by Eugenia Simionca.
Did you find this document useful? Is this content inappropriate? Report this Document. Flag for inappropriate content. Download now. Related titles. Carousel Previous Carousel Next. Jump to Page. Search inside document. The following is a diagram of the tables and columns that you created for the video application: Note: First, run the dropvid.
Amit Sharma. Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. The database administrator can create a public synonym that is accessible to all users. Only the database administrator can drop a public synonym. Summary In this lesson, you should have learned how to: Create, use, and remove views Automatically generate sequence numbers by using a sequence generator Create indexes to improve speed of query retrieval Use synonyms to provide alternative names for objects.
Summary In this lesson, you should have learned about database objects such as views, sequences, indexes, and synonyms. Practice Overview of Part 2 This practice covers the following topics: Creating sequences Using sequences Creating nonunique indexes Creating synonyms.
Practice Overview of Part 2 Part 2 of this lessons practice provides you with a variety of exercises in creating and using a sequence, an index, and a synonym. Complete questions at the end of this lesson. Practice 11 Part 1 1. Confirm that the view works. Practice 11 continued 4. Department 50 needs access to its employee data. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department For security purposes, do not allow an employee to be reassigned to another department through the view.
Display the structure and contents of the DEPT50 view. Practice 11 continued Part 2 7. The sequence should start at and have a maximum value of 1, Have your sequence increment by To test your sequence, write a script to insert two rows in the DEPT table. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions.
Run the commands in your script. Call it EMP. Solutions for Practice I: Introduction The solutions for the Introduction lesson practices are discussed below. Double-click the sqldeveloper desktop icon. This is already done for you as a part of the classroom setup.
To create a new database connection, in the Connections Navigator, right-click Connections. Select New Connection from the menu. Create a database connection using the following information:. Username: oraxx, where xx is the number of your PC Ask your instructor to assign you one ora account out of the ora1- ora20 range of accounts. Password: oraxx. Hostname: Enter the host name of the machine where your database server is running.
Port: f. Test the new connection. If the Status is Success, connect to the database using this new connection. If the status is Success, click the Connect button.
Browsing the Tables in the Connections Navigator 7. In the Connections Navigator, view the objects available to you under the Tables node.
Expand the myconnection connection by clicking the plus sign next to it. Expand the Tables icon by clicking the plus sign next to it. Solutions for Practice I: Introduction continued 3. View the shortcut icons in the SQL Worksheet. Specifically look for the Execute Statement and Run Script icons. There are four coding errors in this statement. Can you identify them? After you have executed a saved script, make sure that you do not enter your next query in the same worksheet.
Open a new worksheet. Your first task is to create some reports based on the 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 the 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 with a comma. Run your query. Open a new SQL Worksheet. Create a report that displays the last name and department number for employee number The HR departments needs to find employees with high salary and low salary.
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. Solutions for Practice 2: Restricting and Sorting Data continued 6. 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 for 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 created in practice exercise 1 and modify it. Enter when prompted for a value in a dialog box.
Solutions for Practice 2: Restricting and Sorting Data continued The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager ID and generates the employee ID, last name, salary, and department for that managers employees.
The HR department wants the ability to sort the report on a selected column. If you have the time, complete the following exercises: 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. Write a query to display the system date. Label the column Date. Note: If your database is remotely located in a different time zone, the output will be the date for the operating system on which the database resides.
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 in uppercase and all the other letters in 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. Sort the results by the employees last names. Solutions for Practice 3: Using Single-Row Functions to Customize Output continued 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 capitalized when prompted for a letter, then the output should show all employees whose last name starts with the letter H. Modify the query such that the case of the entered letter does not affect the output.
The HR department wants to find the duration 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. Create a query to display the last name and salary for all employees. Create a query that displays the first eight characters of the employees last names and indicates the amounts of their salaries with asterisks.
Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Create a query to display the last name and the number of weeks employed for all employees in department Truncate the number of weeks value to 0 decimal places. Show the records in descending order of the employees tenure. Label the column as Dream Salaries. Display each employees last name, hire date, and salary review date, which is the first Monday after six months of service.
Format the dates to appear in the format similar to Monday, the Thirty-First of July, Display the last name, hire date, and day of the week on which the employee started.
Label the column as DAY. Order the results by the day of the week, starting with Monday. Create a query that displays the employees last names and commission amounts. If an employee does not earn commission, show No Commission. Label the column as COMM. Rewrite the statement in the preceding exercise using the CASE syntax. 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. Run the query. 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 as Number of Managers.
Find the difference between the highest and lowest salaries. If you have the 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 that will 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 the department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading.
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 the 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.
Create a report to display employees last names and employee number along with their managers last names and manager number. 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. The HR department needs to find the names and hire dates for all employees who were hired before their managers, along with their managers names and hire dates.
The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply excluding that employee. For example, if the user enters Zlotkey, find all employees who work with Zlotkey excluding Zlotkey. Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary.
Sort the results in order of ascending salary. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a u. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is Modify the query so that the user is prompted for a location ID. Create a report for HR that displays the last name and salary of every employee who reports to King.
Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department. If you have the time, complete the following exercise: 7. Solutions for Practice 8: Using the Set Operators 1. Use the set operators to create this report. The HR department needs a list of countries that have no departments located in them. Display the country ID and the name of the countries.
Produce a list of jobs for departments 10, 50, and 20, in that order. Display job ID and department ID using the set operators.
Solutions for Practice 8: Using the Set Operators continued 4. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the company that is, they changed jobs but have now gone back to doing their original job.
Write a compound query to accomplish this. This way you get to see the feedback messages on the Script Output tabbed page. From File menu, select Open. You get a Create Table succeeeded message on the Script Output tabbed page. Solutions for Practice 9: Manipulating Data continued 4. Populate the table with the next two rows of sample data listed in step 3 by running the INSERT statement in the script that you created.
Solutions for Practice 9: Manipulating Data continued Include only those columns that you need. Note, you will get Update operation not allowed on table error message. Hence, you will not be allowed to insert any row into the table because it is assigned a read only status. Now try to insert the same row again. Test your view. Attempt to reassign Matos to department You need a sequence that can be used with the primary key column of the DEPT table. Write a query to display the last name, department number, and department name for all employees.
Create a report to display the employee last name and the employee number along with the last name of the employees manager and manager number. The HR department wants to determine the names of all employees hired after Davies.
Schema Description Overall Description The Oracle database sample schemas portray a sample company that operates worldwide to fill orders for several different products.
The company has three divisions: Human Resources: Tracks information about the employees and facilities Order Entry: Tracks product inventories and sales through various channels Sales History: Tracks business statistics to facilitate business decisions Each of these divisions is represented by a schema. In this course, you have access to the objects in all the schemas. However, the emphasis of the examples, demonstrations, and practices is on the Human Resources HR schema. Human Resources HR This is the schema that is used in this course.
In the Human Resource HR records, each employee has an identification number, email address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary. The company also tracks information about jobs within the organization. Each job has an identification code, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different positions within the company.
When an employee resigns, the duration the employee was working for, the job identification number, and the department are recorded. The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name.
Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code. In places where the departments and warehouses are located, the company records details such as the country name, currency symbol, currency name, and the region where the country is located geographically.
Objectives After completing this appendix, you should be able to do the following: Write SELECT statements to access data from more than one table using equijoins and nonequijoins Join a table to itself by using a self-join View data that generally does not meet a join condition by using outer joins Generate a Cartesian product of all rows from two or more tables.
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. Cartesian Products A Cartesian product is formed when: A join condition is omitted A join condition is invalid All rows in the first table are joined to all rows in the second table.
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.
In other words, 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. Therefore, you should always include a valid join condition unless you have a specific need to combine all rows from all tables. However, Cartesian products are useful for some tests when you need to generate a large number of rows to simulate a reasonable amount of data. Generating a Cartesian Product A Cartesian product is generated when a join condition is omitted.
Types of Joins To join tables, you can use Oracles join syntax. Note: Before the Oracle9i release, the join syntax was proprietary. The SQLcompliant join syntax does not offer any performance benefits over the Oracle-proprietary join syntax. Prefix the column name with the table name when the same column name appears in more than one table. Joining Tables Using Oracle Syntax When data from more than one table in the database is required, a join condition is used.
Rows in one table can be joined to rows in another table according to common values that exist in the corresponding columns that is, usually primary and foreign key columns. Guidelines When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance database access. If the same column name appears in more than one table, the column name must be prefixed with the table name.
To join n tables together, you need a minimum of n-1 join conditions. For example, to join four tables, a minimum of three joins is required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Qualifying Ambiguous Column Names Use table prefixes to qualify column names that are in multiple tables. Use table prefixes to improve performance. Instead of full table name prefixes, use table aliases. Table aliases give a table a shorter name. Keeps SQL code smaller, uses less memory. Use column aliases to distinguish columns that have identical names, but reside in different tables.
Qualifying Ambiguous Column Names When joining two or more tables, you need to qualify the names of the columns with the table name to avoid ambiguity. Therefore, it is necessary to add the table prefix to execute your query.
If there are no common column names between the two tables, there is no need to qualify the columns. However, using a table prefix improves performance, because you tell the Oracle server exactly where to find the columns. Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. Therefore, 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, thereby using less memory. The table name is specified in full, followed by a space and then the table alias. Guidelines Table aliases can be up to 30 characters in length, but shorter aliases are better than longer ones.
Table aliases should be meaningful. Often, this type of join involves primary and foreign key complements. Note: Equijoins are also called simple joins or inner joins. The document may not be modified or altered in 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, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle.
The information contained in this document is subject to change without notice. This document is not warranted to be error-free. If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable:.
Government contract. Other names may be trademarks of their respective owners. See Full Reader. I would say best inventory software for small shops or stores. Never found an app like this before. Thanks Download ConyEdit for Windows reshim - Dec 3, Programmers feel the comfort of coding using ConyEdit which is capable to edit codes in batch mode and supports most of programming language.
Disc Cover Studio harish - Dec 19, No need to go for a professional designer for the cover of new record, you can do it on your own sitting at home PC adding your own image on it with the Disc Cover Studio. Kaspersky Security Scanner Anthony - Oct 12, Scans software to find whether it is harmful for computer and also checks for updates of security patches. Comodo Firewall Maxmuller - Sep 19, This just another awesome product from Comodo to protect your computer from unwanted inbound or outbound traffic, also helps you analyze data packets.
This lets the data packets we send split into several packages which is convenient for many applications to receive those and process one after another.
0コメント