This Oracle Database: Introduction to SQL training helps you write subqueries, combine Run data manipulation statements (DML) in Oracle Database 11g. ITCourseware, LLC. Introduction to Oracle 11g SQL Programming. Introduction to Oracle 11g SQL Programming. Contributing Authors: Danielle Hopkins. Oracle Database 11g: SQL Fundamentals free PDF. Introduction; Retrieving Data Using the SQL SELECT Statement; Restricting and Sorting.
|Language:||English, Spanish, Dutch|
|ePub File Size:||15.79 MB|
|PDF File Size:||15.24 MB|
|Distribution:||Free* [*Regsitration Required]|
Product 6 - 33 1 Retrieving Data Using the SQL SELECT Statement. Objectives 1- .. This course offers you an introduction to Oracle Database 11g database technology. In this class . generated in multiple formats such as PDF, HTML, Excel. Oracle Database SQL Language Reference, 11g Release 2 (). E Copyright . 1 Introduction to Oracle SQL. History of SQL. Product 6 - 33 O ly l & On n a e t e r U s I n c l e r a O Oracle Database 11g: SQL types available in DDL are shown and schema concepts are introduced.
Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Stops the execution of any statements currently being executed c 6. Quoted identifiers can be reserved words, although this is not recommended. For example, if the user enters Zlotkey, find all employees who work with Zlotkey excluding Zlotkey. U I n Note: Example e A c l In the Create Table dialog box, if you do not select the Advanced check box, you can create a r a table quickly by specifying columns and some frequently used features. This way you get to see the feedback messages on the Script Output tab page.
Who share their knowledge, you can discover the extent of our being selected to easily learn without spending a fortune! Oracle Database 11g: SQL Fundamentals. But also many other tutorials are accessible just as easily! Computer PDF guide you and allow you to save on your studies. You should come see our Database documents.
You will find your happiness without trouble! The latest news and especially the best tutorials on your favorite topics, that is why Computer PDF is number 1 for courses and tutorials for download in pdf files - Oracle Database 11g: Download other tutorials for advice on Oracle Database 11g: We will do everything to help you! And you dear surfers what you need?
The best course and tutorial, and how to learn and use Oracle Database 11g: Useful resource for oracle learners. Watch this video also. It is very helpful for sql beginners https: Home Database Oracle Database 11g: Download free Oracle Database 11g: Beginners Created: December 10, Size: Puja Singh - Brian Pottle Downloads: Summary on tutorial Oracle Database 11g: Download the file.
Alternative Links Oracle Database 11g: SQL Fundamentals pdf. When you drop a table, the database loses all the data in the table and all the indexes associated with it. This is discussed in detail in the course titled Oracle Database 11g: Enforce rules on the data in a table whenever a row is inserted, updated, or deleted.
Prevent the deletion of a table. Prevent the creation of a table. Prevent the creation of data in a table. Create the DEPT table based on the following table instance chart.
Confirm that the table is created. Include only columns that you need. Create the EMP table based on the following table instance chart. Now, try to insert the same row again. You should get the following messages: You are taught c a the basics of creating and using views, sequences, and indexes.
In this lesson, you learn about the c views, sequences, indexes, and synonyms. O ly Many applications require the use of unique numbers as primary key values. You can also use indexes to enforce uniqueness on a column or a collection of columns. A view is a c r a logical table based on a table or another view. A view contains no data of its own, but is like a window through which data from tables can be viewed or changed.
The tables on which a view is O ly based are called base tables. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement. One view can be used to retrieve data from several tables. The feedback messages will be shown on the Script Output tabbed page. The number of aliases listed must match the number of expressions selected in the subquery. You can display either the contents c a of the entire view or just specific rows and columns.
Add an alias for each column name: This means that the view can be altered without dropping, re-creating, and regranting object privileges. O ly Note: Note that alternative names have been specified for the view.
This is a requirement if any column of the view is derived from a function or an expression. All the required values must be present in the view. Remember that you are adding values directly to the underlying table through the view. O ly Reference 11g, Release 1 You can also enforce constraints c r a at the database level.
The view can be used to protect data integrity, but the use is very limited. If there is an attempt to perform DML operations on rows that the view has not selected, an error is displayed, along with the UPDATE empvu20 n a e constraint name if that has been specified. No rows are updated because, if the department number were to change to 10, the view would no longer be able to see that employee.
The statement removes the view definition c r a from the database. However, dropping views has no effect on the tables on which the view was based. On the other hand, views or other applications based on the deleted views become invalid. In the syntax: Overview of Part 1 This practice covers the following topics: Complete questions 1—6 at the end of this lesson.
You can create sequences and then use c them to generate numbers. O ly A typical usage for sequences is to create a primary key value, which must be unique for each row. This can be a time-saving object because it can reduce the amount of application code needed to write a sequence- generating routine. Therefore, the same sequence can I n c l e r a O Oracle Database 11g: Syntax Define a sequence to generate sequential numbers automatically: The sequence starts at , does not allow I n Do not use the CYCLE option if the sequence is used to generate primary key values, unless you have a reliable mechanism that purges old rows faster than the sequence cycles.
The sequence is not tied to a table. Generally, you should name the sequence after its intended O use. However, the sequence can be used anywhere, regardless of its name.
It returns a unique value every time it is referenced, even for different users. I n When you reference sequence. The output of the query is shown below: The cache is c r a populated the first time you refer to the sequence. Each request for the next sequence value is retrieved from the cached sequence.
After the last sequence value is used, the next request for the O ly sequence pulls another cache of sequences into memory. Therefore, if you roll back a statement containing a sequence, the number is lost. If the sequence caches values in I n memory, those values are lost if the system crashes.
However, if you do so, each table can contain gaps in the sequential numbers. If you do not have an index on the column, then a O ly An index provides direct and fast access to rows in a table. An index is used and maintained automatically by the Oracle server. After an index is created, no direct activity is required by the user. This means that they t e r U s can be created or dropped at any time, and have no effect on the base tables or other indexes.
When you drop a table, the corresponding indexes are also dropped. Users can create nonunique indexes on columns to speed up access to the rows. The name of the index is the name that is given to the constraint. This is an index that a user can create. You can manually create a unique index, but it is recommended that you create a unique t e r U s constraint, which implicitly creates a unique index.
Specify BITMAP to indicate that the index is to be created with a bitmap for each n t e r with a key value as a bitmap. U s distinct key, rather than indexing each row separately. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes that O ly you have associated with a table, the more effort the Oracle server must make to update all the indexes after a DML operation.
A unique index is then created automatically. To change an index, you must drop it and then re-create it. O ly In the syntax, index is the name of the index.
If you drop a table, indexes and constraints are automatically dropped but views and sequences remain. You can create c a synonyms to give an alternative name to a table.
With synonyms, you can: Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, O ly procedure, or other objects.
This method can be especially useful with lengthy object names, such as views. Only the database administrator can drop a public synonym. Indexes are designed to speed up query performance. However, not all indexes are created c r a manually. Overview of Part 2 This practice covers the following topics: Confirm that the view works. 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. Test your view. Attempt to reassign Matos to department 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. Creating a database connection 1. Access the Demo: When you start SQL Developer for the first time, you need to provide the path to the java.
This is already done for you as a part of the classroom setup. In any case, if you are prompted, enter the following path: To create a new database connection, in the Connections Navigator, right-click Connections. Create a database connection using the following information: Connection Name: Enter the host name of the machine where your database server is running.
Select the Save Password check box.
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. Verify that the following tables are present: Expand the myconnection connection by clicking the plus sign next to it. Expand the Tables icon by clicking the plus sign next to it.
Click the Data tab. The tables data is displayed. Open a new SQL Worksheet. Examine the shortcut icons available for the SQL Worksheet. 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? 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 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. 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 hire date for employees with the last c names of Matos and Taylor. Order the query in ascending order by hire date. Display the last name and department ID of all employees in departments 20 or 50 in O ly ascending alphabetical order by name.
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. 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 O ly earn more than an amount that the user specifies after a prompt. Enter when prompted for a value in a dialog box. Restricting and Sorting Data continued 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. You can test the data with the following values: Write a query to display the system date. Label the column Date. 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. Run the revised query.
Label the column Increase. 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. 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 m y date on which the employee was hired. Order your d e whole number. Round the number of months up to the closest A Note: Create a query to display the last name and salary for all employees.
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. Using Conversion Functions and Conditional Expressions 1.
Create a report that produces the following for each employee: Label the column as Dream Salaries. Order the results by the day of the week, starting with Monday. Display the last name, hire date, and day of the week on which the employee started. Using Conversion Functions and Conditional Expressions continued 5. 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. Determine the number of managers without listing them.
Label the column as Number of Managers. Find the difference between the highest and lowest salaries. 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 e l 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.
Displaying Data from Multiple Tables 1. 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.
Label the columns Employee, Emp , Manager, and Mgr , respectively. Run e the query. Displaying Data from Multiple Tables continued 6. 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. Give each column an appropriate label. 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 e employee Davies. Using Subqueries to Solve Queries 1.
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.
The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is Using Subqueries to Solve Queries continued 5. 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. 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. 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.
The HR department needs a report with the following specifications: This way you get to see the feedback messages on the Script Output tabbed page. From File menu, select Open. In the Open dialog box, navigate to D: You get a Create Table succeeeded message on the Script Output tabbed page.
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. Manipulating Data continued To confirm that the table was created and to view its structure, issue the following command: Include only those columns that you need. To confirm that the table was created and to view its structure: 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. Creating Other Schema Objects Part 1 1. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all m y e employees in department They have requested that you label the view columns d reassigned to another department through the view. Creating Other Schema Objects continued Part 2 7.
You need a sequence that can be used with the primary key column of the DEPT table. Oracle Join Syntax 1. Write a query to display the last name, department number, and department name for all employees. Order the results by the employee number.
Oracle Join Syntax continued 6. The HR department wants to determine the names of all employees hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies. The company has three divisions: 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. Each employee is assigned to a department, and each department is identified either by c a a unique department number or a short name. Each department is associated with one location, and the country code. A join is used to view information c table. Therefore, you can join tables together to view information from more than one O ly Note: In the example in the slide, the report c displays data from two separate tables: In other words, all rows in the first table are joined to all rows in O ly A Cartesian product tends to generate a large number of rows and the result is rarely useful.
Before the Oracle9i release, the join syntax was proprietary. The SQL: Rows in one c r a 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. For example, to join four O 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. Therefore, it is necessary O ly to add the table prefix to execute your query. However, using a table prefix improves performance, because you tell the Oracle server exactly where to find the columns. 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.
The table name is specified in full, followed by a space and then the table alias. Equijoins are also called simple joins or inner joins. Other columns that are not present in both the tables need not be qualified r a by a table alias, but it is recommended for better performance.
Table aliases are used to qualify the columns and avoid ambiguity. The example in the slide limits the rows of output to those with a department ID equal to 20 or For example, to join three tables, a minimum of two joins is required.
The relationship is obtained using an operator other than the n a e t e r U s I n c l e r a O Oracle Database 11g: The salary c r a 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 O ly employee is repeated in the list. There are two reasons for this: 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. I n Note: O Table aliases have been specified in the example in the slide for performance reasons, not because of possible ambiguity. To return the department record that does not have any employees, or to return the employee record that does not belong to any department, you can use the outer join.
Syntax e A l Missing rows can be returned if an outer join operator is used in the join condition. This operator has the effect of creating one or more null rows, to which one or more O ly rows from the nondeficient table can be joined. The c Outer Join Restrictions r a Contracting department does not have any employees.
The empty value is shown in the output. Overview e A l This practice is intended to give you practical experience in extracting data from more than one table c using the Oracle join syntax. Create a report for the HR department that displays employee last names, department numbers, and all employees who work in the same department as a given employee.
Give each column an l appropriate label. Oracle SQL contains many extensions. It contains its own command language. To log in from a Windows environment: Enter the username, password, and database name.
To log in from a command-line environment: Log on to your machine. Enter the sqlplus command shown in the slide. I n username Your database username password Your database password Your password is visible if you enter it here.
To ensure the integrity of your password, do not enter it at the operating system prompt. Instead, enter only your username. Enter your password at the password prompt.