Oracle Application Express PDF

Database Programming with SQL 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: −Distinguish between application software and system software and give an e...

Database Programming with SQL 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: −Distinguish between application software and system software and give an example of each −Log-in to the Oracle Application Express practice environment −Execute a simple query to retrieve information from the Database −Apply the rules of SQL to display all columns and a subset of columns specified by criteria DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 3 Purpose Every day, in one way or another, we come in contact with computer applications If you checked your email today, it was probably done using an application If you bought an item at a grocery store, the clerk scanned the item using an application that calculated your bill and updated the store inventory In this course, you will learn the syntax of SQL using the application called Oracle Application Express DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 4 Application Programs Although computers have been around for a very long time (possibly before you were born), their use for business and personal computing didn't take place until application software programs were developed Application programs allowed the end user—people like you and me—to be able to buy fully developed, ready-to-use programs It was no longer necessary to know how the program worked, just that it did work and accomplished what we wanted it to do DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 5 Application Programs Application program software is different from system software System software consists of low-level programs designed to interact with the computer hardware Operating systems, compilers, and system utilities are examples of system software In contrast, application software includes programs for word processing, databases, gaming, email, and graphics DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 6 Application Programs Yahoo.com uses the Oracle database to store data Rather than having everyone who wants to search the database or retrieve email learn SQL, an application has all of the SQL (and other coding languages) pre- programmed into it With a few mouse clicks, users have access to all of the information they need DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 7 Using Applications An application is like a car To drive a car, you need to know enough to make it work It has a friendly "shell" to hide all the things that you don't need to know, such as how the transmission works or how fuel like petrol or diesel is used to power the engine Could you ever get your driver's license if you had to demonstrate an understanding of every system— electrical, powertrain, hydraulic, fuel, etc.—used to make the car run? DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 8 Oracle Application Express In this course, you will use Oracle Application Express This application enables many developers to build and access applications as if they were running in separate databases With built-in features such as design themes, navigational controls, form handlers, and flexible reports, Oracle Application Express accelerates the application development process DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 9 Oracle Application Express Two components in Oracle Application Express are: −SQL Workshop −Application Builder To learn SQL, you will use the SQL Workshop component To design an application, you use Application Builder DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 10 Oracle Application Express Oracle Application Express (APEX) is the tool that we will use to allow you to build tables and retrieve information from an Oracle database When retrieving information from a database, you will often have to find a subset of the data based on specific search criteria Becoming familiar with SQL will help you more quickly find the information that you need DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 11 Oracle Application Express Oracle Application Express (APEX) accounts are supplied without tables or data To obtain the script file go to the Section 0 – Course Resources of the Learner – Learning Path for the course and select the desired script. On running the Script, the tables and data used throughout the course, will be added to your schema For more information on using APEX see the iAcademy APEX Learner and Instructor Guides DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 12 Basic SELECT Statement The SELECT * command returns all the rows in a table The syntax is: SELECT * FROM ; For example: SELECT * FROM employees; DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 13 SELECT Statement with a Condition To return a subset of the data, modify the SELECT statement The syntax is: SELECT FROM WHERE ; For example: SELECT first_name, last_name, job_id FROM employees WHERE job_id = 'SA_REP'; DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 14 Correcting errors When entering SQL commands, it is important to use the correct spelling, otherwise you will get an error message For example (SELECT: spelling incorrect): SELCT * FROM employees; −Would result in the error message: To rectify, simply correct the spelling and run again DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 15 Correcting errors It is also important to use the correct names and spelling for columns and tables For example (employees table name - spelling incorrect): SELECT * FROM employee; −Would result in the error message: To rectify, simply correct the spelling and run again DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 16 Correcting errors For example (first_name column - entered incorrectly): SELECT name FROM employees; −Would result in the error message: To rectify, simply enter the correct column name and run again DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 17 Terminology Key terms used in this lesson included: −Application software −System software −Oracle Application Express −Syntax −Subset −Comparison Operator DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 18 Summary In this lesson, you should have learned how to: −Distinguish between application software and system software and give an example of each −Log-in to the Oracle Application Express practice environment −Execute a simple query to retrieve information from the Database −Apply the rules of SQL to display all columns and a subset of columns specified by criteria DP 1-1 Oracle Application Express Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 19