full.pdf

Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Computer Pride Limited Introduction s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Objectives After completing this lesson, you should be able to do the following: Define the goals of the course List the features of Oracle Database 12c Describe the salient features of Oracle Cloud Discuss the theoretical and physical aspects of a relational database Describe Oracle server’s implementation of RDBMS and as h ) object relational database management system om c ฺ l (ORDBMS) ai ideฺ m g beGused u Identify the development environments that can @ 5 t 6 n for this course ngi tude a w used Sin this course m s Describe the database and schema i y h d t d e e s t u All rights reserved. I ( oaffiliates. Copyright © 2014, G Oracle and/or tits AN ense W lic e ILanMunderstanding l In this lesson, you R gain of the relational database management system b a R r (RDBMS). YouEare alsofe introduced to Oracle SQL Developer and SQL*Plus as development D used nforsexecuting environments SQL statements, and for formatting and reporting purposes. Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 2 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Course objectives, agenda, and appendixes used in the course Overview of Oracle Database 12c and related products Overview of relational database management concepts and terminologies Introduction to SQL and its development environments Human Resource (HR) Schema and the tables used in the course as h ) Oracle database 12c SQL Documentation and Additional om c ฺ l Resources ai eฺ gm Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 3 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Course Objectives After completing this course, you should be able to: Identify the major components of Oracle Database Retrieve row and column data from tables with the SELECT statement Create reports of sorted and restricted data Employ SQL functions to generate and retrieve customized data Run complex queries to retrieve data from multiple tables as h Run data manipulation language (DML) statements toom) ilฺc eฺ update data in Oracle Database a gm Guid @ Run data definition language (DDL) statements 65 entot create i g and manage schema objects an tud w is S m y d e th d e us All rights reserved. I (t oaffiliates. Copyright © 2014, G Oracle and/or tits AN ense W M e lic L I This course offers you an to the Oracle Database technology. In this class, you bl R rintroduction a R learn the basicEconcepts feof relational databases and the powerful SQL programming D nsprovides language. This course the essential SQL skills that enable you to write queries Y a r D t D E single Tagainst non and multiple tables, manipulate data in tables, create database objects, and query metadata. Oracle Database 12c: SQL Workshop I 1 - 4 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Course Agenda Day 1: – Introduction – Retrieving Data Using the SQL SELECT Statement – Restricting and Sorting Data – Using Single-Row Functions to Customize Output Day 2: – – – – Using Conversion Functions and Conditional Expressions Reporting Aggregated Data Using the Group Functions ) Displaying Data from Multiple Tables Using Joins m o c ฺ Using Subqueries to Solve Queries ailฺ m uide g 5@ nt G 6 i ng tude a w is S m y d e th d e us All rights reserved. I (t oaffiliates. Copyright © 2014, G Oracle and/or tits AN ense W M e lic L I R rabl R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 5 has a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Course Agenda Day 3: – Using the Set Operators – Managing Tables Using DML Statements – Introduction to Data Definition Language s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 6 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Appendixes and Practices Used in the Course Appendix A: Table Descriptions Appendix B: Using SQL Developer Appendix C: Using SQL*Plus Appendix D: Commonly Used SQL Commands Activity Guide – Practices and Solutions – Additional Practices and Solutions s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 7 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Course objectives, agenda, and appendixes used in the course Overview of Oracle Database 12c and related products Overview of relational database management concepts and terminologies Introduction to SQL and its development environments Human Resource (HR) Schema and the tables used in this course as h ) Oracle database 12c SQL Documentation and Additional om c ฺ l Resources ai eฺ gm Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 8 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Database 12c: Focus Areas Infrastructure Grids Information Management Application Development s ) ha a om c ฺ l Oracle Cloud ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L extensive le features across the following focus areas: Oracle Database 12c b RIoffers a R E sGrids: er The Infrastructure Grid technology of Oracle enables pooling of f Infrastructure D Y tservers an and storage to form systems that deliver the highest quality of service r Dlow-cost D in terms high availability, and performance. Oracle Database 12c TE consolidates non of manageability, and extends the benefits of grid computing. Apart from taking full advantage of grid computing, Oracle Database 11g has unique change assurance features to manage changes in a controlled and cost-effective manner. Information Management: Oracle Database 12c extends the existing information management capabilities in content management, information integration, and information lifecycle management areas. Oracle provides content management of advanced data types such as Extensible Markup Language (XML), text, spatial, multimedia, medical imaging, and semantic technologies. Application Development: Oracle Database 12c has capabilities to use and manage all the major application development environments such as PL/SQL, Java/JDBC,.NET and Windows, PHP, SQL Developer, and Application Express. Oracle Cloud: The Oracle Cloud is an enterprise cloud for business. It provides an integrated collection of application and platform cloud services that are based on bestin-class products and open Java and SQL standards. Oracle Database 12c: SQL Workshop I 1 - 9 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Database 12c Manageability High Availability Performance Security s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M e lmultiple Organizations needRtoIL support terabytes of information for users who demand fast b a R r and secure access E tosbusiness fe applications round the clock. The database systems must be Dmust n reliable and be able to recover quickly in the event of any kind of failure. Oracle Y a r D t D Database is designed along the following feature areas to help organizations manage E n12c ongrids Tinfrastructure easily and deliver high-quality service: Information Integration Manageability: By using some of the change assurance, management automation, and fault diagnostics features, the database administrators (DBAs) can increase their productivity, reduce costs, minimize errors, and maximize quality of service. Some of the useful features that promote better management are Database Replay facility, the SQL Performance Analyzer, the Automatic SQL Tuning facility, and Real-Time Database Operations Monitoring. Enterprise Manager Database Express 12c is a web-based tool for managing Oracle databases. Enterprise Manager Database Express greatly simplifies database performance diagnostics by consolidating the relevant database performance screens into a consolidated view called Database Performance Hub. DBAs get a single, consolidated view of the current real-time and historical view of the database performance across multiple dimensions such as database load, monitored SQL and PL/SQL, and Active Session History (ASH) on a single page for the selected time period. Oracle Database 12c: SQL Workshop I 1 - 10 a Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Computer Pride Limited High availability: By using the high availability features, you can reduce the risk of down time and data loss. These features improve online operations and enable faster database upgrades. Performance: By using capabilities such as SecureFiles, compression for online transaction processing (OLTP), Real Application Clusters (RAC) optimizations, Result Caches, and so on, you can greatly improve the performance of your database. Oracle Database 12c enables organizations to manage large, scalable, transactional, and data warehousing systems that deliver fast data access using low-cost modular storage. Security: Oracle Database 12c helps organizations protect their information with unique secure configurations, data encryption and masking, and sophisticated auditing capabilities. It delivers a secure and scalable platform for reliable and fast access to all types of information by using the industry-standard interfaces. Information integration: Oracle Database 12c has many features to better integrate data throughout the enterprise. It also supports advanced information lifecycle management capabilities. This helps you manage the changing data in your database. s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I NG nse t A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 11 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Fusion Middleware Portfolio of leading, standards-based, and customer-proven software products that spans a range of tools and services from Java EE and developer tools, through integration services, business intelligence, collaboration, and content management s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M e Oracle Fusion Middleware and well-integrated family of products that RIL risaba lcomprehensive R offers complete Esupport fefor development, deployment, and management of Service-Oriented D(SOA). s n Architecture SOA facilitates the development of modular business services that can be Y a r D t D n and reused, thereby reducing development and maintenance costs, and E integrated o Teasily n providing higher quality of services. Oracle Fusion Middleware’s pluggable architecture enables you to leverage your investments in any existing application, system, or technology. Its unbreakable core technology minimizes the disruption caused by planned or unplanned outages. Some of the products from the Oracle Fusion Middleware family include: Application Server: Java EE, web services SOA and Process Management: BPEL Process Manager, SOA Governance Development Tools: Oracle Application Development Framework, JDeveloper, SOA Suite Business Intelligence: Oracle Business Activity Monitoring, Oracle Data Integrator Enterprise Management: Enterprise Manager Identity Management: Oracle Identity Management Content Management: Oracle Content Database Suite User Interaction: Portal, Rich Internet Apps Oracle Database 12c: SQL Workshop I 1 - 12 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Enterprise Manager Cloud Control Create and manage a complete set of cloud services. Manage all phases of cloud life cycle. Manage the entire cloud stack Monitor the health of all components Identify, understand, and resolve business problems s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n Complete stack Complete life cycle Complete integration i e g n ud a t w S Self-Service IT I Simple and Automated m I thisBusiness-Driven y d d se e t ( u I o Copyright © 2014, G Oracle and/or tits affiliates. All rights reserved. AN ense W M e lic L I Enterprise Manager Cloud bl is a management tool that provides monitoring and R rControl a R management capabilities fefor Oracle and non-Oracle components. It is a complete, integrated, DE ncloud s and business-driven management solution in a single product, which is referred to as Y a r D t D E Cloud onControl.” T“Total n Using Enterprise Manager Cloud Control, you can: Create and manage a complete set of cloud services, including: Infrastructure-as-aservice, Database-as-a-service, Platform-as-a-service, and others Manage all phases of cloud life cycle Manage the entire cloud stack: From application to disk, including engineered systems (Exa series) and with integrated support capabilities Monitor the health of all components, the hosts that they run on, and the key business processes that they support Identify, understand, and resolve business problems through the unified and correlated management of User Experience, Business Transactions, and Business Services across all your packaged and custom applications Oracle Database 12c: SQL Workshop I 1 - 13 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Cloud The Oracle Cloud is an enterprise cloud for business. It consists of many different services that share some common characteristics: On-demand self-service Resource pooling Rapid elasticity www.cloud.oracle.com Measured service Broad network access s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M ILenterprise le cloud for business. It provides an integrated collection of The Oracle Cloud is b Ran a R r services that are based on best-in-class products and open application and Eplatform ecloud f D s Java andYSQL standards. D n-tran The top two benefits of cloud computing are speed and cost. D Ea result, TAs nothe applications and databases deployed in the Oracle Cloud are portable and you can easily move them to or from a private cloud or on-premise environment. All Cloud Services can be provisioned through a self-service interface. Users can get their Cloud Services delivered on an integrated development and deployment platform with tools to rapidly extend and create new services. Oracle Cloud services are built on Oracle Exalogic Elastic Cloud and Oracle Exadata Database Machine, together offering a platform that delivers extreme performance, redundancy, and scalability. The following are five essential characteristics of Oracle Cloud services: On-demand self-service: Provisioning, monitoring, and management control Resource pooling: Implies sharing and a level of abstraction between consumers and services Rapid elasticity: Ability to quickly scale up or down as needed Measured service: Metering utilization for either internal chargeback (private cloud) or external billing (public cloud) Broad network access: Access through a browser on any networked device Oracle Database 12c: SQL Workshop I 1 - 14 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Cloud Services Oracle Cloud provides three types of services: Software as a Service (SaaS) Platform as a Service (PaaS) Infrastructure as a Service (IaaS) s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M ILto applications le that are delivered to end users over the Internet. Oracle SaaS generally refers b R a R r CRM On Demand example of a SaaS offering that provides both multitenant as well as E is an e f D s single-tenant options, depending on the customer’s preferences. Y tran D PaaS n- refers to an application development and deployment platform delivered as a ED generally o Tservice n to developers, enabling them to quickly build and deploy a SaaS application to end users. The platform typically includes databases, middleware, and development tools, all delivered as a service via the Internet IaaS refers to computing hardware (servers, storage, and network) delivered as a service. This service typically includes the associated software as well as operating systems, virtualization, clustering, and so on. Examples of IaaS in the public cloud include Amazon’s Elastic Compute Cloud (EC2) and Simple Storage Service (S3). The database cloud is built within an enterprise’s private cloud environment, as a PaaS model. The database cloud provides on-demand access to database services in a selfservice, elastically scalable, and metered manner. The database cloud offers compelling advantages in cost, quality of service, and agility. You can deploy a database within a virtual machine in an laaS platform. You can rapidly deploy Database clouds on Oracle Exadata which is a pre-integrated and optimized hardware platform that supports both OLTP and DW workloads. Oracle Database 12c: SQL Workshop I 1 - 15 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Cloud Deployment Models Community cloud Public cloud Cloud Deployment Models Private Private cloud cloud s ) ha a om c ฺ l ai ideฺ m g Gu @ Hybrid 5 t 6 n i e cloud g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L organization le Private cloud:R A Isingle uses a private cloud, which it typically controls, b a R r manages, E hostsfe in private data centers. However, the organization can also D and s n outsource hosting and operation to a third-party service provider. Amazon’s Virtual Private Y a r D t D is onan example of a private cloud in an external provider setting. T E Cloud n Public cloud: Multiple organizations (tenants) uses private cloud on a shared basis, hosted and managed by a third-party service provider. Example: Amazon Elastic Compute Cloud (EC2), IBM’s Blue Cloud, Sun Cloud, Google AppEngine, and so on Community cloud: A group of related organizations, who want to make use of a common cloud computing environment, uses the community cloud. It is managed by the participating organizations or by a third-party managed service provider. It is hosted internally or externally. For example, A community might consist of the different branches of the military, all the universities in a given region, or all the suppliers to a large manufacturer. Hybrid cloud: A single organization that wants to adopt both private and public clouds for a single application uses the hybrid cloud. A third model, the hybrid cloud, is maintained by both internal and external providers. For example, an organization might use a public cloud service, such as Amazon Simple Storage Service (Amazon S3) for archived data but continue to maintain in-house (private cloud) storage for operational customer data. Oracle Database 12c: SQL Workshop I 1 - 16 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Course objectives, agenda, and appendixes used in the course Overview of Oracle Database 12c and related products Overview of relational database management concepts and terminologies Introduction to SQL and its development environments Human Resource (HR) Schema and the tables used in this course as h ) Oracle database 12c SQL Documentation and Additional om c ฺ l Resources ai eฺ gm Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 17 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Relational and Object Relational Database Management Systems Relational model and object relational model User-defined data types and objects Fully compatible with relational database Supports multimedia and large objects High-quality database server features s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL both lethe relational and the object relational database models. The Oracle server R supports b a R E extends er the data-modeling capabilities to support an object relational f The Oracle D server s anprovides object-oriented programming, complex data types, complex database that r DYmodel t D E nobjects, on and full compatibility with the relational world. Tbusiness It includes several features for improved performance and functionality of the OLTP applications, such as better sharing of runtime data structures, larger buffer caches, and deferrable constraints. Data warehouse applications benefit from enhancements such as parallel execution of insert, update, and delete operations; partitioning; and parallel-aware query optimization. The Oracle model supports client/server and web-based applications that are distributed and multitiered. For more information about the relational and object relational model, refer to Oracle Database Concepts for 12c Database. Oracle Database 12c: SQL Workshop I 1 - 18 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Data Storage on Different Media s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g Electronic d Database Filing cabinet wan Stu spreadsheet ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M ILsomebinformation le Every organizationR has needs. A library keeps a list of members, books, a R r due dates, andEfines. Afcompany needs to save information about its employees, e Dand salaries. s n departments, These pieces of information are called data. Y D n-tra D E no can store data in various media and in different formats, such as a hard copy TOrganizations document in a filing cabinet, or data stored in electronic spreadsheets, or in databases. A database is an organized collection of information. To manage databases, you need a database management system (DBMS). A DBMS is a program that stores, retrieves, and modifies data in databases on request. There are four main types of databases: hierarchical, network, relational, and (most recently) object relational. Oracle Database 12c: SQL Workshop I 1 - 19 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Relational Database Concept Dr. E. F. Codd proposed the relational model for database systems in 1970. It is the basis for the relational database management system (RDBMS). The relational model consists of the following: – Collection of objects or relations – Set of operators to act on the relations – Data integrity for accuracy and consistency s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL bmodel le were first outlined by Dr. E. F. Codd in a June 1970 The principles of the Rrelational a R r of Data for Large Shared Data Banks. In this paper, Dr. Codd paper titled A E Relationalfe Model D s proposed DYthenrelational tran model for database systems. D E common o models used at that time were hierarchical and network, or even simple flat-file TThe n data structures. Relational database management systems (RDBMS) soon became very popular, especially for their ease of use and flexibility in structure. In addition, a number of innovative vendors, such as Oracle, supplemented the RDBMS with a suite of powerful, application development and user-interface products, thereby providing a total solution. Components of the Relational Model Collections of objects or relations that store the data A set of operators that can act on the relations to produce other relations Data integrity for accuracy and consistency Oracle Database 12c: SQL Workshop I 1 - 20 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Definition of a Relational Database A relational database is a collection of relations or two-dimensional tables controlled by the Oracle server. Oracle server Table name: EMPLOYEES Table name: DEPARTMENTS s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t … 6 … n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L relations le or two-dimensional tables to store information. A relational database b RIuses a R r to store information about all the employees in your company. In E might ewant f For example, you D s an you create several tables to store different pieces of information about a relational r DY database, t D E employees, Tyour non such as an employee table, a department table, and a salary table. Oracle Database 12c: SQL Workshop I 1 - 21 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Data Models Model of system in client’s mind Entity model of client’s model Table model of entity model s ) ha a Oracle serverom ilฺc eฺ a gm Guid @ 65 ent i g an StudTables on disk w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le Engineers build a model of a car to work out any Models are the cornerstone RIL raofbdesign. R details before E it finto e production. In the same manner, system designers develop D putting s n models to explore ideas Y D n-tra and improve the understanding of database design. D E nofo Models TPurpose Models help to communicate the concepts that are in people’s minds. They can be used to do the following: Communicate Categorize Describe Specify Investigate Evolve Analyze Imitate The objective is to produce a model that fits a multitude of these uses, can be understood by an end user, and contains sufficient detail for a developer to build a database system. Oracle Database 12c: SQL Workshop I 1 - 22 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Entity Relationship Model Create an entity relationship diagram from business specifications or narratives: EMPLOYEE #* number * name o job title assigned to composed of DEPARTMENT #* number * name o location Scenario: – “... Assign one or more employees to a as h ) department...” om c ฺ l i – “... Some departments do not yet have assigned a employees eฺ m d i g...” @ Gu a 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le into discrete categories or entities. An entity In an effective system, RILdataraisbdivided R relationship (ER) E model feis an illustration of the various entities in a business and the Damong s n relationships them. An ER model is derived from business specifications or narratives Y a r D t D E builtnduring on the analysis phase of the system development life cycle. ER models separate Tand the information required by a business from the activities performed within the business. Although businesses can change their activities, the type of information tends to remain constant. Therefore, the data structures also tend to be constant. Oracle Database 12c: SQL Workshop I 1 - 23 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Benefits of ER Modeling Documents information for the organization in a clear, precise format Provides a clear picture of the scope of the information requirement Provides an easily understood pictorial map for database design Offers an effective framework for integrating multiple applications Key Components Entity: An aspect of significance about which information must be known. Examples are departments, employees, and orders. Attribute: Something that describes or qualifies an entity. For example, for the employee entity, the attributes would be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional. This state is called optionality. Relationship: A named association between entities showing optionality and degree. Examples are employees and departments, and orders and items. s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I NG nse t A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 24 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Entity Relationship Modeling Conventions Entity: Attribute: Singular, unique name Singular name Lowercase Uppercase Mandatory marked with “*” Soft box Optional marked with “o” Synonym in parentheses EMPLOYEE #* number * name o job title DEPARTMENT #* number * name composed of o location assigned to s ) ha om c ฺ l ai ideฺ m g Gu @ 5 Unique Identifier (UID) t 6 n i e g Primary marked with “#” an Stud w Secondary marked with m“(#)” this y d d se e t ( u I o Copyright © 2014, G Oracle and/or tits affiliates. All rights reserved. AN ense W M e lic L I Entities R rabl R E entitysinfea model, use the following conventions: To represent Dan Y D n-unique tran entity name DSingular, TE Entity noname in uppercase Soft box Optional synonym names in uppercase within parentheses: ( ) Attributes To represent an attribute in a model, use the following conventions: Singular name in lowercase Asterisk (*) tag for mandatory attributes (that is, values that must be known) Letter “o” tag for optional attributes (that is, values that may be known) Oracle Database 12c: SQL Workshop I 1 - 25 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Relationships Each direction of the relationship contains: A label: For example, taught by or assigned to An optionality: Either must be or maybe A degree: Either one and only one or one or more Symbol Description Dashed line Optional element indicating “maybe” Solid line Mandatory element indicating “must be” Crow’s foot Degree element indicating “one or more” Single line Degree element indicating “one and only one” Note: The term cardinality is a synonym for the term degree. s ) ha Each source entity {may be | must be} in relation {one and only one | one or more} with the destination entity. a om c ฺ l ai ideฺ m Unique Identifiers g Gu @ 5 A unique identifier (UID) is any combination of attributes or relationships, or t both, that serves to 6 n i e g distinguish occurrences of an entity. Each entity occurrence uduniquely identifiable. an must tbe w S Tag each attribute that is part of the UID with ma hashthsign is “#”. y d Tag secondary UIDs with a hash sign indparentheses e se (#). t ( u I NG nse to A W lice M RIL rable R DE nsfe Y D n-tra D TE no Note: The convention is to read clockwise. Oracle Database 12c: SQL Workshop I 1 - 26 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Relating Multiple Tables Each row of data in a table can be uniquely identified by a primary key. You can logically relate data from multiple tables using foreign keys. Table name: DEPARTMENTS Table name: EMPLOYEES s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i … e g Primary key an Stud w ym this Primary key Foreign key d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL thatbdescribes le Each table contains exactly one entity. For example, the EMPLOYEES Rdata a R r table containsE information fe about employees. Categories of data are listed across the top of D s n each table, and individual cases are listed below. By using a table format, you can readily Y a r D t D and use information. E nunderstand, on Tvisualize, Because data about different entities is stored in different tables, you may need to combine two or more tables to answer a particular question. For example, you may want to know the location of the department where an employee works. In this scenario, you need information from the EMPLOYEES table (which contains data about employees) and the DEPARTMENTS table (which contains information about departments). With an RDBMS, you can relate the data in one table to the data in another by using foreign keys. A foreign key is a column (or a set of columns) that refers to a primary key in the same table or another table. You can use the ability to relate data in one table to data in another to organize information in separate, manageable units. Employee data can be kept logically distinct from the department data by storing it in a separate table. Oracle Database 12c: SQL Workshop I 1 - 27 Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Computer Pride Limited Guidelines for Primary Keys and Foreign Keys You cannot use duplicate values in a primary key. Primary keys generally cannot be changed. Foreign keys are based on data values and are purely logical (not physical) pointers. A foreign key value must match an existing primary key value or unique key value; otherwise, it must be null. A foreign key must reference either a primary key or a unique key column. s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I NG nse t A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 28 a Computer Pride Limited Relational Database Terminology Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ 3 4 2 6 5 s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L contain le one or many tables. A table is the basic storage structure of A relational database b RIcan a R an RDBMS. AE table holds erall the data necessary about something in the real world, such as f D s employees, an or customers. r DY invoices, t D E slidenshows on the contents of the EMPLOYEES table or relation. The numbers indicate the TThe following: 1 1. A single row (or tuple) representing all the data required for a particular employee. Each row in a table should be identified by a primary key, which permits no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved. 2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMPLOYEES table. In this example, the employee number column is designated as the primary key. A primary key must contain a value and the value must be unique. 3. A column that is not a key value. A column represents one kind of data in a table; in this example, the data is the salaries of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved. Oracle Database 12c: SQL Workshop I 1 - 29 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ 4. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table. In the example, DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table. 5. A field can be found at the intersection of a row and a column. There can be only one value in it. 6. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only those employees who have the role of sales representative have a value in the COMMISSION_PCT (commission) field. s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I NG nse t A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 30 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Course objectives, agenda, and appendixes used in the course Overview of Oracle Database 12c and related products Overview of relational database management concepts and terminologies Introduction to SQL and its development environments Human Resource (HR) Schema and the tables used in this course as h ) Oracle database 12c SQL Documentation and Additional om c ฺ l Resources ai eฺ gm Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 31 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Using SQL to Query Your Database Structured query language (SQL) is: The ANSI standard language for operating relational databases Efficient, easy to learn, and use Functionally complete (With SQL, you can define, retrieve, and manipulate data in tables.) SELECT department_name FROM departments; Oracle server s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M In a relational database, dole not specify the access route to the tables, and you do not b RIL you a R r need to know E data fe is arranged physically. D how the s n Y To access you execute a structured query language (SQL) statement, which is D then-database, tra D E American o National Standards Institute (ANSI) standard language for operating relational Tthe n databases. SQL is also compliant to ISO Standard (SQL 1999). SQL is a set of statements with which all programs and users access data in an Oracle Database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications, in turn, must use SQL when executing the user’s request. SQL provides statements for a variety of tasks, including: Querying data Inserting, updating, and deleting rows in a table Creating, replacing, altering, and dropping objects Controlling access to the database and its objects Guaranteeing database consistency and integrity SQL unifies all of the preceding tasks in one consistent language and enables you to work with data at a logical level. Oracle Database 12c: SQL Workshop I 1 - 32 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ SQL Statements Used in the Course SELECT INSERT UPDATE DELETE MERGE Data manipulation language (DML) CREATE ALTER DROP RENAME TRUNCATE COMMENT Data definition language (DDL) GRANT REVOKE Data control language (DCL) s ) ha a om c ฺ l ai ideฺ m COMMIT g Gu @ Transaction control ROLLBACK 5 t 6 n i SAVEPOINT e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L ble SQL Statements RI R raby Oracle comply with industry standards. Oracle Corporation E supported e f SQL statements D ns with evolving standards by actively involving key personnel in SQL Yfuture compliance a r ensures D t nED nocommittees. The industry-accepted committees are ANSI and International Tstandards Standards Organization (ISO). Both ANSI and ISO have accepted SQL as the standard language for relational databases. Statement Description SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMENT GRANT REVOKE Retrieves data from the database, enters new rows, changes existing rows, and removes unwanted rows from tables in the database, respectively. Collectively known as data manipulation language (DML) COMMIT ROLLBACK SAVEPOINT Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions Sets up, changes, and removes data structures from tables. Collectively known as data definition language (DDL) Provides or removes access rights to both the Oracle Database and the structures within it Oracle Database 12c: SQL Workshop I 1 - 33 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Development Environments for SQL There are two development environments for this course: The primary tool is Oracle SQL Developer. SQL*Plus command-line interface can also be used. SQL Developer SQL*Plus s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le SQL Developer RIL b a R r Oracle SQL Developer as the tool for running the SQL eusing f This courseD isE developed s an in the examples in the lessons and the practices. SQL Developer is the statements r DY discussed t D n this class. E tool Tdefault nofor SQL*Plus The SQL*Plus environment can also be used to run all SQL commands covered in this course. Notes See Appendix B for information about using SQL Developer, including simple instructions on installation process. See Appendix C for information about using SQL*Plus. Oracle Database 12c: SQL Workshop I 1 - 34 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Course objectives, agenda, and appendixes used in the course Overview of Oracle Database 12c and related products Overview of relational database management concepts and terminologies Introduction to SQL and its development environments Human Resource (HR) Schema and the tables used in this course as h ) Oracle database 12c SQL Documentation and Additional om c ฺ l Resources ai eฺ gm Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 35 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Human Resources (HR) Schema DEPARTMENTS LOCATIONS department_id department name manager_id location_id location_id street address postal code city state province Country id JOB_HISTORY employee_id start_date end_date job_id department_id EMPLOYEES employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id COUNTRIES country_id country_name region_id s ) ha om c ฺ l REGIONS ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL ble Description Human ResourcesR(HR)Schema R ra schema is a part of the Oracle Sample Schemas that can be E sfe(HR) The HumanD Resources installed DYin ann-Oracle tran Database. The practice sessions in this course use data from the HR D E no Tschema. JOBS job_id job_title min_salary max_salary region_id region_name Table Descriptions REGIONS contains rows that represent a region such as America, Asia, and so on. COUNTRIES contains rows for countries, each of which is associated with a region. LOCATIONS contains the specific address of a specific office, warehouse, or production site of a company in a particular country. DEPARTMENTS shows details about the departments in which the employees work. Each department may have a relationship representing the department manager in the EMPLOYEES table. EMPLOYEES contains details about each employee working for a department. Some employees may not be assigned to any department. JOBS contains the job types that can be held by each employee. JOB_HISTORY contains the job history of the employees. If an employee changes departments within a job or changes jobs within a department, a new row is inserted into this table with the earlier job information of the employee. Oracle Database 12c: SQL Workshop I 1 - 36 a Computer Pride Limited Tables Used in the Course Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ EMPLOYEES s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g DEPARTMENTS n ud a t w S JOB_GRADES ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL arebused le in this course: The following mainR tables a R E table:sfGives er details of all the employees EMPLOYEES D DEPARTMENTS DY n-trantable: Gives details of all the departments D TE JOB_GRADES table: Gives details of salaries for various grades no Apart from these tables, you will also use the other tables listed in the previous slide such as the LOCATIONS and the JOB_HISTORY table. Note: The structure and data for all the tables are provided in Appendix A. Oracle Database 12c: SQL Workshop I 1 - 37 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Course objectives, agenda, and appendixes used in the course Overview of Oracle Database 12c and related products Overview of relational database management concepts and terminologies Introduction to SQL and its development environments Human Resource (HR) Schema and the tables used in this course as h ) Oracle database 12c SQL Documentation and Additional om c ฺ l Resources ai eฺ gm Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 38 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Oracle Database Documentation Oracle Database New Features Guide Oracle Database Reference Oracle Database SQL Language Reference Oracle Database Concepts Oracle Database SQL Developer User’s Guide s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M Navigate to http://st-doc.us.oracle.com/12/121/index.htm to access the Oracle Database 12c RIL rable R documentationElibrary. fe D ns Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 39 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Additional Resources For additional information about Oracle Database 12c, refer to the following: Oracle Database 12c: New Features eStudies Oracle Learning Library: – http://www.oracle.com/goto/oll Oracle Cloud: – http://cloud.oracle.com Access the online SQL Developer Home Page, which is available at: s ) ha – http://www.oracle.com/technology/products/database/sql_developer m /index.html li ฺco ฺ a ide m g Access the SQL Developer tutorial, which is u @available G 5 t 6 n online at: ngi tude a w is S – http://download.oracle.com/oll/tutorials/SQLDeveloper/index.htm m y d e th d e us All rights reserved. I (t oaffiliates. Copyright © 2014, G Oracle and/or tits AN ense W M e lic L I R rabl R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 1 - 40 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Summary In this lesson, you should have learned: The goals of the course Features of Oracle Database 12c The salient features of Oracle Cloud The theoretical and physical aspects of a relational database Oracle server’s implementation of RDBMS and object relational database management system (ORDBMS) ) m The development environments that can be used for this o ilฺc eฺ course a gm Guid @ About the database and schema used in65 this course t has a n ngi tude a w is S m y d e th d e us All rights reserved. I (t oaffiliates. Copyright © 2014, G Oracle and/or tits AN ense W M e lic L I Relational database management R rabl systems are composed of objects or relations. They are R managed by operations DE nsfeand governed by data integrity constraints. Y Oracle DCorporation tra produces products and services to meet your RDBMS needs. The main D n E nare o the following: Tproducts Oracle Database with which you store and manage information by using SQL Oracle Fusion Middleware with which you develop, deploy, and manage modular business services that can be integrated and reused Oracle Enterprise Manager Grid Control, which you use to manage and automate administrative tasks across sets of systems in a grid environment SQL The Oracle server supports ANSI-standard SQL and contains extensions. SQL is the language that is used to communicate with the server to access, manipulate, and control data. Oracle Database 12c: SQL Workshop I 1 - 41 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Practice 1: Overview This practice covers the following topics: Starting Oracle SQL Developer Creating a new database connection Browsing the HR tables s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL the lefollowing: In this practice, youRperform b a R E SQLsDeveloper er Start Oracle and create a new connection to the ora1 account. f D n Y a Developer to examine data objects in the ora1 account. The ora1 Use D Oracle trSQL D n TE account no contains the HR schema tables. Note the following location for the lab files: /home/oracle/labs/sql1/labs If you are asked to save any lab files, save them in this location. In any practice, there may be exercises that are prefaced with the phrases “If you have time” or “If you want an extra challenge.” Work on these exercises only if you have completed all other exercises within the allocated time and would like a further challenge to your skills. 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. Note: All written practices use Oracle SQL Developer as the development environment. Although it is recommended that you use Oracle SQL Developer, you can also use SQL*Plus that is available in this course. Oracle Database 12c: SQL Workshop I 1 - 42 a Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Computer Pride Limited Retrieving Data Using the SQL SELECT Statement s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Objectives After completing this lesson, you should be able to do the following: List the capabilities of SQL SELECT statements Execute a basic SELECT statement s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL database, le you need to use the SQL SELECT statement. However, To extract data from b Rthe a R r columns that are displayed. This lesson describes the SELECT you may needE to restrict ethe f D s statement Ythat istrneeded an to perform these actions. Further, you may want to create SELECT D D n can be used more than once. E nothat Tstatements Oracle Database 12c: SQL Workshop I 2 - 2 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Capabilities of SQL SELECT statements Arithmetic expressions and NULL values in the SELECT statement Column aliases Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword DESCRIBE command s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 2 - 3 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Basic SELECT Statement SELECT FROM *|{[DISTINCT] column [alias],...} table; SELECT identifies the columns to be displayed. FROM identifies the table containing those columns. s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L bstatement le In its simplest form,RaISELECT must include the following: a R r e specifies the columns to be displayed A SELECT fwhich DEclause, s n Y AD FROM clause, tra which identifies the table containing the columns that are listed in the D n TE SELECT no clause In the syntax: SELECT * DISTINCT column|expression alias FROM table Is a list of one or more columns Selects all columns Suppresses duplicates Selects the named column or the expression Gives different headings to the selected columns Specifies the table containing the columns Note: Throughout this course, the words keyword, clause, and statement are used as follows: A keyword refers to an individual SQL element—for example, SELECT and FROM are keywords. A clause is a part of a SQL statement—for example, SELECT employee_id, last_name, and so on. A statement is a combination of two or more clauses—for example, SELECT * FROM employees. Oracle Database 12c: SQL Workshop I 2 - 4 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Selecting All Columns SELECT * FROM departments; s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M e in a table by following the SELECT keyword with an IL ofbldata You can display allR columns a R asterisk (*). InE the example er in the slide, the DEPARTMENTS table contains four columns: f D s n DEPARTMENT_ID, MANAGER_ID, and LOCATION_ID. The table Y traDEPARTMENT_NAME, D D E neight on rows, one for each department. Tcontains You can also display all columns in the table by listing them after the SELECT keyword. For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the DEPARTMENTS table: SELECT department_id, department_name, manager_id, location_id FROM departments; Note: In SQL Developer, you can enter your SQL statement in a SQL Worksheet and click the “Execute Statement” icon or press [F9] to execute the statement. The output displayed on the Results tabbed page appears as shown in the slide. Oracle Database 12c: SQL Workshop I 2 - 5 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Selecting Specific Columns SELECT department_id, location_id FROM departments; s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le to display specific columns of the table by specifying the You can use the SELECT b RIL statement a R column names, Eseparated erby commas. The example in the slide displays all the department f D s numbersYand location D n-tran numbers from the DEPARTMENTS table. D o clause, specify the columns that you want in the order in which you want them TInEthe SELECT n to appear in the output. For example, to display location before department number (from left to right), you use the following statement: SELECT location_id, department_id FROM departments 6 Oracle Database 12c: SQL Workshop I 2 - 6 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Writing SQL Statements SQL statements are not case-sensitive. SQL statements can be entered on one or more lines. Keywords cannot be abbreviated or split across lines. Clauses are usually placed on separate lines. Indents are used to enhance readability. In SQL Developer, SQL statements can be optionally terminated by a semicolon (;). Semicolons are required when you execute multiple SQL statements. has ) In SQL*Plus, you are required to end each SQL statement om c ฺ l i with a semicolon (;). eฺ ma a g Guid @ 65 ent i g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M Writing SQL Statements RIL rable R E ssimple fe rules and guidelines, you can construct valid statements that are By using theDfollowing n Y both easy D tonread traand edit: D are not case-sensitive (unless indicated). o TE SQLnstatements SQL statements can be entered on one or many lines. Keywords cannot be split across lines or abbreviated. Clauses are usually placed on separate lines for readability and ease of editing. Indents should be used to make code more readable. Keywords typically are entered in uppercase; all other words, such as table names and columns names, are entered in lowercase. Executing SQL Statements In SQL Developer, click the Run Script icon or press [F5] to run the command or commands in the SQL Worksheet. You can also click the Execute Statement icon or press [F9] to run a SQL statement in the SQL Worksheet. The Execute Statement icon executes the statement at the cursor in the Enter SQL Statement box while the Run Script icon executes all the statements in the Enter SQL Statement box. The Execute Statement icon displays the output of the query on the Results tabbed page, whereas the Run Script icon emulates the SQL*Plus display and shows the output on the Script Output tabbed page. In SQL*Plus, terminate the SQL statement with a semicolon, and then press [Enter] to run the command. Oracle Database 12c: SQL Workshop I 2 - 7 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Column Heading Defaults SQL Developer: – Default heading alignment: Left-aligned – Default heading display: Uppercase SQL*Plus: – Character and Date column headings are left-aligned. – Number column headings are right-aligned. – Default heading display: Uppercase s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL headings le are displayed in uppercase and are left-aligned. In SQL Developer,R column b a R Elast_name, er hire_date, salary f D s SELECT DY n-employees; tran FROM D E no TYou can override the column heading display with an alias. Column aliases are covered later in this lesson. Oracle Database 12c: SQL Workshop I 2 - 8 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Capabilities of SQL SELECT statements Arithmetic expressions and NULL values in the SELECT statement Column aliases Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword DESCRIBE command s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 2 - 9 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Arithmetic Expressions Create expressions with number and date data by using arithmetic operators. Operator Description + Add - Subtract * Multiply / Divide s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M lein which data is displayed, or you may want to perform You may need to modify way b RIL the a R calculations, or Elook atswhat-if er scenarios. All these are possible using arithmetic expressions. f D An arithmetic Y expression an can contain column names, constant numeric values, and the r D t D n E nooperators. Tarithmetic Arithmetic Operators The slide lists the arithmetic operators that are available in SQL. You can use arithmetic operators in any clause of a SQL statement (except the FROM clause). Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction operators only. Oracle Database 12c: SQL Workshop I 2 - 10 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM employees; s ) ha om c ฺ l ai ideฺ m g Gu … @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL usesbthe le addition operator to calculate a salary increase of $300 for The example in theR slide a R r displays a SALARY+300 column in the output. all employees.EThe slide ealso f D s Note that DYthenresultant tran calculated column, SALARY+300, is not a new column in the D E no table; it is for display only. By default, the name of a new column comes from the TEMPLOYEES calculation that generated it—in this case, salary+300. Note: The Oracle server ignores blank spaces before and after the arithmetic operator. Rules of Precedence Multiplication and division occur before addition and subtraction. Operators of the same priority are evaluated from left to right. Parentheses are used to override the default precedence or to clarify the statement. Oracle Database 12c: SQL Workshop I 2 - 11 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Operator Precedence SELECT last_name, salary, 12*salary+100 FROM employees; 1 … SELECT last_name, salary, 12*(salary+100) FROM employees; 2 s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g … an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M ILslidebdisplays le the last name, salary, and annual compensation of The first example inRthe a R r annual compensation by multiplying the monthly salary with 12, employees. It E calculates ethe f D s plus a one-time ran of $100. Note that multiplication is performed before addition. DY n-tbonus D E Use TNote: noparentheses to reinforce the standard order of precedence and to improve clarity. For example, the expression in the slide can be written as (12*salary)+100 with no change in the result. Using Parentheses You can override the rules of precedence by using parentheses to specify the desired order in which the operators are to be executed. The second example in the slide displays the last name, salary, and annual compensation of employees. It calculates the annual compensation as follows: adding a monthly bonus of $100 to the monthly salary, and then multiplying that subtotal with 12. Because of the parentheses, addition takes priority over multiplication. Oracle Database 12c: SQL Workshop I 2 - 12 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Defining a Null Value Null is a value that is unavailable, unassigned, unknown, or inapplicable. Null is not the same as zero or a blank space. SELECT last_name, job_id, salary, commission_pct FROM employees; s ) ha … a om c ฺ l ai ideฺ m g Gu … @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL forbaleparticular column, that value is said to be NULL or to contain a If a row lacks a data Rvalue R null. E sfera D Columns DYwithn-NULL tranvalue can be selected in a SELECT query and can be the part of an D E noexpression. Any arithmetic expression using NULL values results into NULL. Tarithmetic Columns of any data type can contain nulls. However, some constraints (NOT NULL and PRIMARY KEY) prevent nulls from being used in the column. In the slide example, notice that only a sales manager or sales representative can earn a commission in the COMMISSION_PCT column of the EMPLOYEES table. Other employees are not entitled to earn commissions. A null represents that fact. Note: By default, SQL Developer uses the literal, (null), to identify null values. However, you can set it to something more relevant to you. To do so, select Preferences from the Tools menu. In the Preferences dialog box, expand the Database node. Click Advanced Parameters and on the right pane, for the “Display Null value As,” enter the appropriate value. Oracle Database 12c: SQL Workshop I 2 - 13 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. SELECT last_name, 12*salary*commission_pct FROM employees; … s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le expression is null, the result is null. For example, if you If any column valueRinIL an arithmetic b a R attempt to perform E division er by zero, you get an error. However, if you divide a number by null, f D s the result DYis annull traornunknown. D Ethe example TIn no in the slide, employee King does not get any commission. Because the COMMISSION_PCT column in the arithmetic expression is null, the result is null. … For more information, see the section on “Basic Elements of Oracle SQL” in Oracle Database SQL Language Reference for 12c database. Oracle Database 12c: SQL Workshop I 2 - 14 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Capabilities of SQL SELECT statements Arithmetic expressions and NULL values in the SELECT statement Column aliases Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword DESCRIBE command s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 2 - 15 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Defining a Column Alias A column alias: Renames a column heading Is useful with calculations Immediately follows the column name (there can also be the optional AS keyword between the column name and the alias) Requires double quotation marks if it contains spaces or special characters, or if it is case-sensitive s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M e SQL Developer normally uses the name of the selected IL ofbalquery, When displaying the Rresult a R column as theE column heading. er This heading may not be descriptive and, therefore, may be f D s difficult to an You can change a column heading by using a column alias. r DYunderstand. t D n after the column in the SELECT list using blank space as a separator. By E the oalias TSpecify n default, alias headings appear in uppercase. If the alias contains spaces or special characters (such as -, !, _), or if it is case-sensitive, enclose the alias in double quotation marks (“ ”). Oracle Database 12c: SQL Workshop I 2 - 16 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Using Column Aliases SELECT last_name AS name, commission_pct comm FROM employees; … SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees; s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g … an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le and the commission percentages of all the employees. The first example displays bnames RIL rthe a R Note that the optional fekeyword has been used before the column alias name. The result of DE nsAS the query is the same whether the AS keyword is used or not. Also, note that the SQL Y a r D t D n the column aliases, name and comm, in lowercase, whereas the result of the E nohas Tstatement query displays the column headings in uppercase. As mentioned in the preceding slide, column headings appear in uppercase by default. The second example displays the last names and annual salaries of all the employees. Because Annual Salary contains a space, it has been enclosed in double quotation marks. Note that the column heading in the output is exactly the same as the column alias. Oracle Database 12c: SQL Workshop I 2 - 17 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Capabilities of SQL SELECT statements Arithmetic Expressions and NULL values in SELECT statement Column aliases Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword DESCRIBE command s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 2 - 18 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Concatenation Operator A concatenation operator: Links columns or character strings to other columns Is represented by two vertical bars (||) Creates a resultant column that is a character expression SELECT FROM last_name||job_id AS "Employees" employees; s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w … ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le You can link columns columns, arithmetic expressions, or constant values to create a b RItoL other a R r character expression the concatenation operator (||). Columns on either side of the DE nsbyftoeusing operatorY are combined make a single output column. D n-tra D o LAST_NAME and JOB_ID are concatenated, and given the alias Employees. TInEthe example, n Note that the last name of the employee and the job code are combined to make a single output column. The AS keyword before the alias name makes the SELECT clause easier to read. Null Values with the Concatenation Operator If you concatenate a null value with a character string, the result is a character string. LAST_NAME || NULL results in LAST_NAME. Oracle Database 12c: SQL Workshop I 2 - 19 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Literal Character Strings A literal is a character, a number, or a date that is included in the SELECT statement. Date and character literal values must be enclosed within single quotation marks. Each character string is output once for each row returned. s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M le or a date that is included in the SELECT list. It is not a A literal is a character, b RILa number, a R column name E or a column eralias. It is printed for each row returned. Literal strings of freef D s n format text in the query result and are treated the same as a column in the Y can tberaincluded D D E nlist. on TSELECT The date and character literals must be enclosed within single quotation marks (''); number literals need not be enclosed in a similar manner. Oracle Database 12c: SQL Workshop I 2 - 20 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Using Literal Character Strings SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees; s ) ha a om c ฺ l ai ideฺ m g Gu @ 5 t … 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL displays le the last names and job codes of all employees. The column The example in theR slide b a R has the heading E Employee er Details. Note the spaces between the single quotation marks in f D s the SELECT DY nstatement. tran The spaces improve the readability of the output. D o example, the last name and salary for each employee are concatenated with a TInEthe following n literal, to give the returned rows more meaning: SELECT last_name ||': 1 Month salary = '||salary Monthly FROM employees; Oracle Database 12c: SQL Workshop I 2 - 21 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Alternative Quote (q) Operator Specify your own quotation mark delimiter. Select any delimiter. Increase readability and usability. SELECT department_name || q'[ Department's Manager Id: ]' || manager_id AS "Department and Manager" FROM departments; s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M Many SQL statements ble literals in expressions or conditions. If the literal itself RILuseracharacter R contains a single fe mark, you can use the quote (q) operator and select your own DE quotation s n quotation mark delimiter. Y D n-tra D E can nchoose o any convenient delimiter, single-byte or multibyte, or any of the following TYou character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [ ] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string. Oracle Database 12c: SQL Workshop I 2 - 22 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Duplicate Rows The default display of queries is all rows, including duplicate rows. 1 2 SELECT department_id FROM employees; SELECT DISTINCT department_id FROM employees; s ) ha om c ฺ l ai ideฺ m g Gu @ … 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M e displays the results of a query without eliminating the IL blSQL Unless you indicate Rotherwise, a R r duplicate rows. in the slide displays all the department numbers from the EThe first eexample f D s n EMPLOYEES table. Note that the department numbers are repeated. Y D n-tra D Eeliminate To Tclause no duplicate rows in the result, include the DISTINCT keyword in the SELECT immediately after the SELECT keyword. In the second example in the slide, the EMPLOYEES table actually contains 20 rows, but there are only seven unique department numbers in the table. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns. SELECT FROM DISTINCT department_id, job_id employees; Oracle Database 12c: SQL Workshop I 2 - 23 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Capabilities of SQL SELECT statements Arithmetic expressions and NULL values in the SELECT statement Column aliases Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword DESCRIBE command s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 2 - 24 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Displaying Table Structure Use the DESCRIBE command to display the structure of a table. Or, select the table in the Connections tree and use the Columns tab to view the table structure. DESC[RIBE] tablename s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL boflea table by using the DESCRIBE command. The command You can display theRstructure R raand the data types, and it shows you whether a column must displays the column E names e f D s contain data is,nwhether the column has a NOT NULL constraint). Y (that a r D t D ontable name is the name of any existing table, view, or synonym that is TInEthe syntax, n accessible to the user. Using the SQL Developer GUI interface, you can select the table in the Connections tree and use the Columns tab to view the table structure. Note: DESCRIBE is a SQL*Plus command supported by SQL Developer. It is abbreviated as DESC. Oracle Database 12c: SQL Workshop I 2 - 25 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Using the DESCRIBE Command DESCRIBE employees s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL displays le information about the structure of the EMPLOYEES table The example in theR slide b a R using the DESCRIBE E scommand. er f D Y tdisplay, an Null indicates that the values for this column may be unknown. NOT In theD resulting r D E indicates TNULL non that a column must contain data. Type displays the data type for a column. The data types are described in the following table: Data Type Description NUMBER(p,s) Number value having a maximum number of digits p, with s digits to the right of the decimal point VARCHAR2(s) Variable-length character value of maximum size s DATE Date and time value between January 1, 4712 B.C. and December 31, A.D. 9999 Oracle Database 12c: SQL Workshop I 2 - 26 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Quiz Identify the SELECT statements that execute successfully. a. SELECT first_name, last_name, job_id, salary*12 AS Yearly Sal FROM employees; b. SELECT first_name, last_name, job_id, salary*12 "yearly sal" FROM employees; c. SELECT first_name, last_name, job_id, salary AS "yearly sal" ) m o FROM employees; ฺc il eฺ a gm Guid d. SELECT first_name+last_name AS name, job_Id, @ 65 ent i salary*12 yearly sal g an Stud FROM employees; w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M Answer: b, c RIL rable R DE nsfe Y D n-tra D TE no Oracle Database 12c: SQL Workshop I 2 - 27 has a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Summary In this lesson, you should have learned how to write a SELECT statement that: Returns all rows and columns from a table Returns specified columns from a table Uses column aliases to display more descriptive column headings s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M e IL have llearned In this lesson, you R should how to retrieve data from a database table with the b a R r SELECT statement. E sfe DSELECT Y D n-tran *|{[DISTINCT] column [alias],...} D TE noFROM table; In the syntax: SELECT * DISTINCT column|expression alias FROM table Is a list of one or more columns Selects all columns Suppresses duplicates Selects the named column or the expression Gives different headings to the selected columns Specifies the table containing the columns Oracle Database 12c: SQL Workshop I 2 - 28 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Practice 2: Overview This practice covers the following topics: Selecting all data from different tables Describing the structure of tables Performing arithmetic calculations and specifying column names s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M IL simple leSELECT queries. The queries cover most of the SELECT In this practice, youRwrite b a R clauses and operations E sfthat er you learned in this lesson. D DY n-tran D TE no Oracle Database 12c: SQL Workshop I 2 - 29 a Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Computer Pride Limited s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I NG nse t A W lice M RIL rable R DE nsfe Y D n-tra D TE no a Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Computer Pride Limited Restricting and Sorting Data s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M RIL rable R DE nsfe Y D n-tra D TE no a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Objectives After completing this lesson, you should be able to do the following: Limit the rows that are retrieved by a query Sort the rows that are retrieved by a query Use ampersand substitution to restrict and sort output at run time s ) ha om c ฺ l ai ideฺ m g Gu @ 5 t 6 n i e g an Stud w ym this d d (te o use I Copyright © 2014, G N Oraclenand/or e tits affiliates. All rights reserved. s A W lice M L thebdatabase, le When retrieving data you may need to do the following: RIfrom a R r E rowssfofedata that are displayed Restrict Dthe Y an in which the rows are displayed D nthe DSpecify trorder E lesson TThis noexplains the SQL statements that you use to perform the actions listed in the slide. Oracle Database 12c: SQL Workshop I 3 - 2 a Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Lesson Agenda Limiting rows with: – The WHERE clause – The comparison operators using =, Greater than >= Greater than or equal to < Less than = 6000... WHERE last_name = 'Smith' Remember, an alias cannot be used in the WHERE clause. Note: The symbols != and ^= can also represent the not equal to condition. Oracle Database 12c: SQL Workshop I 3 - 8 Computer Pride Limited Unauthorized reproduction or distribution prohibitedฺ Copyright© 2014, Oracle and/or its affiliatesฺ Using Comparison Operators SELECT last_name, salary FROM employees WHERE salary

Document Details

Tags

Related

Full Transcript

Upgrade to continue