Giáo trình SQL Nâng cao

410 trang hoanguyen 8290

Download

Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình SQL Nâng cao", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

giao_trinh_sql_nang_cao.pdf

Nội dung text: Giáo trình SQL Nâng cao

ORACLE
Index Contents Ch:1 Introduction to RDBMS Ch:2 SQL, SQL * Plus Ch:3 Managing Tables and Data Ch:4 Other ORACLE database objects Ch:5 Transaction and Decision Control Language Ch:6 Introduction to PL/SQL Ch:7 Advanced PL/SQL Ch:8 Oracle Database Structure file:///D|/JigneshDhol/Oracle/Index.htm [6/26/02 11:58:05 AM]
0101. Contents Ch:1 Introduction to RDBMS Top:1 What is Database Management System ? Top:2 Database Models Hierarchical Model - Network Model - Relational Model Top:3 What is Relational Database Management System ? Top:4 Difference between DBMS / RDBMS Top:5 E - R diagram Top:6 Types of Relationship One to One - One to Many - Many to Many Top:7 Normalization Top:8 Codd's Rules file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch1\0101.%20Contents.htm [6/26/02 12:06:29 PM]
0201. Contents Ch:2 SQL, SQL *Plus Top:1 Introduction to SQL Top:2 SQL Commands and Datatypes Top:3 Expression, Conditions and Operators Top:4 SELECT statement Top:5 Special Operator Top:6 Join, Subquery file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch2\0201.%20Contents.htm [6/26/02 12:06:30 PM]
0301. Contents Ch:3 Managing Tables and Data Top:1 Creating and Altering tables (Including Constraints) Top:2 Data Definition Language Top:3 Data Manipulation Commands like Insert, Update, Delete and Alter Top:4 Functions Aggregate, Date - Time, Arithmetic, Character, Conversion, Miscellaneous Top:5 SQL *Plus file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch3\0301.%20Contents.htm [6/26/02 12:06:32 PM]
0401. Contents Ch:4 Other ORACLE database objects Top:1 View Top:2 Sequence Top:3 Synonyms Top:4 Index Top:5 Database Links file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch4\0401.%20Contents.htm [6/26/02 12:06:34 PM]
0501. Contents Ch:5 TCL and DCL Top:1 What is transaction ? Top:2 Starting and Ending of Transaction Top:3 Commit, Rollback, Save Point Top:4 Grant, Revoke Top:5 Role, Creating Users, Change Password file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch5\0501.%20Contents.htm [6/26/02 12:06:35 PM]
0601. Contents Ch:6 Introduction to PL/SQL Top:1 SQL v/s PL/SQL Top:2 PL/SQL Block Structure Top:3 Language construct of PL/SQL (Variables, Basic Datatypes, Composite Datatypes, Conditions looping etc.) Top:4 %TYPE and %ROWTYPE Top:5 Using Cursor (Implicit, Explicit) file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch6\0601.%20Contents.htm [6/26/02 12:06:37 PM]
file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch7\0701.%20Contents.htm Ch:7 Advanced PL/SQL Top:1 Creating and Using Procedure Top:2 Functions Top:3 Package Top:4 Trigger Top:5 Creating Objects Top:6 PL/SQL Tables Top:7 Nestead Tables Top:8 Varrays file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch7\0701.%20Contents.htm [6/26/02 12:06:38 PM]
0801. Contents Ch:8 Oracle Database Structure Top:1 Initialization Parameter Top:2 Control Files, Redo Logs files Top:3 Processes Top:4 Tablespace (Create, Alter, Drop) Top:5 Oracle Blocks Top:6 Import, Export Top:7 SQL * Loader Top:8 Instance Architecture 1. Database Processes 2. Memory Structure file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch8\0801.%20Contents.htm [6/26/02 12:06:40 PM]
0102. What is DBMS TOP:1 What is Database Management System ? C. J. Dates define a database system, as a computer base record keeping system whose overall purpose to record and maintain information. In other word, database is a collection of related records and a set of programs to access this data. Because it is an entire system and enables ones to enter, store and manage data it is call Database Management System. Modern Database Management System comes in many different classifications, and with many different capabilities, but in general they try or accomplish three things. Data consolidation refers to the combining or unifying of separate data file into centralized structure, and storing data in a non-redundant format. A redundant format is a structure is that stores the same data item two or more location. For instance, as seen in the examples above, if within a company an employee address is stored not only by the HRD Department in the employee history file, but also the account department in the payroll file, the employee own department in a project file, etc. then you have non centralized structure carrying redundant information. An integrated (non-redundant) system stores the employee's address stores only one location. Data Sharing Data sharing refer to the ability of the system to aloe multiple user concurrent access to the individual pieces of data in the database. You can think of the database as a 'pool' of sharable information. Data Protection file:///D|/JigneshDhol/Oracle/0102.%20What%20is%20DBMS.htm (1 of 3) [6/26/02 12:10:58 PM]
0102. What is DBMS Data protection refers to the ability of a database management system to maintain integrity of its data in the face of certain type of processing adversity such as crashes, program failures, etc. if this type of events occurs, the DBMS must have the ability to back out (or undo) incomplete of erroneous changes to data stored in the database. How is a Database System Beneficial? • The amount of redundancy in the stored data can be reduced. • No more inconsistencies. • The store data can be shared. • Standards can be set and followed. • Data integrity is maintained. • Security of data can be implemented. • Data independence. DBMS Users • The Database Designers • The Database Administrator or DBA • The Application Programmer • The actual End-Users of the application file:///D|/JigneshDhol/Oracle/0102.%20What%20is%20DBMS.htm (2 of 3) [6/26/02 12:10:58 PM]
0102. What is DBMS file:///D|/JigneshDhol/Oracle/0102.%20What%20is%20DBMS.htm (3 of 3) [6/26/02 12:10:58 PM]
0103. Database Models TOP:2 Database Models Database Management Systems organize data in what is known as data model. You and think of a data model as the infrastructure of the data organization, in other word how the data is presented to the user. There are three basic data models: • The Hierarchical Model • The Network Model • The Relational Model (i) The Hierarchical Model One of the earliest database management systems was based on the Hierarchical Model. In a hierarchical data model the records have a parent-child relationship. The application used was Production planning for automobile manufacturing companies. The model of database is shown in following figure. An automobile manufacture may manufacture various model of car. Each car model was decomposed into its assemblies (Engine, Body and Chassis). Each assembly is further decomposed into sub-assemblies (valves, spark plugs&ldots;) and so on. If manufacturer wanted to generate the Bill of Materials for a particular model of an automobile the hierarchical data model would be very suitable because the bill of materials for a product has hierarchical structure. Each record represents a particular part and since the records have a parent-child relation-ship each part is linked to its sub-part. The hierarchical model of support multiple occurrences file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (1 of 12) [6/26/02 12:11:01 PM]
0103. Database Models of the same record type. One of the most popular hierarchic database management system was IBM's Information Management System (IMS) introduced in 1968. IMS is still most widely used DBMS in IBM mainframes. The Characteristics of DBMS are: • Data is represented as hierarchical trees. The hierarchical database is characterized by parent-child relationship between records. A record type, R1, is said to the parent of record type, R@, if R1 is one level higher than R2 in the hierarchic tree. The root of the hierarchy is the most important record type and all records at different levels of the hierarchy are dependent of the root. Each child record has only one parent record. The parent record can have one or more children record type. file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (2 of 12) [6/26/02 12:11:01 PM]
0103. Database Models • Represents a set of related records. There can be one or more than one record occurrences for given record type. When writes into database, one occurrence of record of the record type is written. Similarly, whenever a record is retrieved from the database, one occurrence if the record type is retrieved. • Hierarchy is established through pointers. In the hierarchic database, the pointers link the records. Pointers determine whether a particular record occurrence is a parent of child record and path from parent to the child. • Simple structure The database is simple hierarchical tree. The parent and child file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (3 of 12) [6/26/02 12:11:01 PM]
0103. Database Models records can be stored close to each other on the disk, minimizing disk input and output. The hierarchic data model is simpler than a network model. • High performance The parent-child relationship is stored as a pointer from one record to another; hence navigation through the database is very fast resulting in high performance. • Relationships between record types are pro-defined The hierarchical DBMS is based on the hierarchic tree structure in which the parent-child relationship is supported. A record type, R!, is said to be the parent of record type, R2, if R1 is one level higher than R2 in the hierarchical tree. Records types at different level of the hierarchy are dependent on the root, which is most important record type in the hierarchy. Since the relationships are predefined, flexibility is lost but a high performance compared to other data models is achieved. • Tedious to reorganize. It is tedious to reorganize the database because the hierarchy has to be maintained. Each time a record type is inserted of deleted, the pointer have to be manipulated to maintain the parent-child relationship. The reorganization is static and appropriate changes have to be made to the application programs. • Real life requirement are more complex The hierarchic DBMS is based on a simple parent-child relationship, but real life applications are more complex and cannot be represented by a hierarchic structure. In an order- processing database, a single order might participate in three file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (4 of 12) [6/26/02 12:11:01 PM]
0103. Database Models different parent-child relationships linking the order to the customer who placed the order, the items ordered and the sales person who took the order. This complex structure cannot be represented in a hierarchical structure. (ii) The Network Model To overcome the problem posed by the hierarchical data model, the network model was developed. The network model modified the hierarchical model by allowing multiple parent-child relationships. This relation is known as set in network model was developed. The network model together with the hierarchical data model was major a data model for implementing numerous commercial DBMSs. The network model structure and language construct were defined by the CODASYL (Conference on Data Systems Language). The characteristics of a network DBMS are: • Data record types are represented as a network. • A network is used when hierarchy is not established or when a record participates is more than one relationship. file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (5 of 12) [6/26/02 12:11:01 PM]
0103. Database Models • Each sub-module can have one or more super-ordinate modules. Since each multiple parent child relationship is supported child record type could have one than more parent record types. • Represents a set of related records. The sets that support multiple parent-child relationships and the structure of the record have to be specified in advance. • Complex structure Since multiple parent-child relationship is supported, database structure is very complicated. The network database implements sets that support multiple parent-child relationships. The sets have to be specified in advance. In the tradeoff between flexibility and performance, a network model is not very flexible to reorganize but has high performance level. • Difficult to reorganize The network database is very difficult to reorganized because insert and deleting a record would trace the pointers and changing the appropriate links. • Navigation done by the programmer The programmer will have to write 3-GL programs specifying the relationship and direction in which to navigate in the database. • 3-GL needed to program database To access records the programmer has to navigate the database record-by-record. Program will have to be written specifying to which relationship to navigate and the direction. file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (6 of 12) [6/26/02 12:11:01 PM]
0103. Database Models • 3-GL inadequate for handling sets. The records network model are processed one set at a time. 3- GLs handle only one record at a time and hence are inadequate for handling sets. • Query facility not available Network database management system do not have any query facility and hence 3-GL programs will have to written specifying the path and the relationship. (iii) The Relational Model An IBM research scientist Dr. E. F. Codd, was unhappy with the way the DBMSs available in those day handled large volumes of data. He felt the need to apply the rules and decline of mathematics to help address the problems associated with the earlier models as Data integrity Data redundancy In June 1970, he presented his paper titled ' A Relational Model of Data for Large shared Databanks'. This paper actually laid down 12 rules. Which a true RDBMS would have to satisfy. The term 'Relation' is derived from the set theory of mathematics. The basis characteristics of a relational model are discussed here. First, in a relation model, data is stored in relations. 'What are relations?' will be the next question that we will answer. Before that, consider the following example. Given below are two different lists. One is a list of countries and their capitals. The other lists countries file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (7 of 12) [6/26/02 12:11:01 PM]
0103. Database Models and the local currencies used by them. You will notice that their two different lists shown here. However, there is a column, which is the common to both lists. This the column, which contains names of the country. Now if someone wants to know the currency used in Rome, first one should find out the name of the country. Next that country should be looked up the next list to find out the currency. It is possible to get this information because it is possible to establish relation between the two lists through a common column called "country". In the relational model data is stored in relations, relation is a formal term for the table. In the example above we have stored information file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (8 of 12) [6/26/02 12:11:01 PM]
0103. Database Models about countries as a table. A table in a database as a unique table name that identifies its contents. Each table can be called an intersection of row and columns. One of the most important properties of a table is that the rows are unordered. A row cannot be identified by its position in the table. Every table must have a column that uniquely identifies by each row in the table. It is essential that no two rows should contain identical information. This is prevented by the use of primary key. Now we will exam the relational model in detail. The relational model - the details Let us consider any of the tables we have considered in the example above less us take the currency table. a new column called "codes" has been introduced as the primary key. The table show above consist of the components listed below, file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (9 of 12) [6/26/02 12:11:01 PM]
0103. Database Models according to a relational model: Domain Domain is pool of values from where one or more attributes (columns) can draw their actual values. For example, the values in the field "country" are available from the name of all the countries in the world. Hence, the domain name for this field is country. tupple according to the relational model, every relation or table is made up of many tuples. They are called records- a term that we are already familiar with. They are the rows that a table is made up of. Given below are some of the tuples that are part of the currency table. CHN China Remnimbi (quan) FRN France Francs PRT Portugal Escudo The number of tuples in a table is the cardinality of the tuple. Attributes The term "attributes" refers to characteristic. The characteristic of the tuple is reflected by its attributes or field. This simply means that what the column contains will be define by the attributes of that column. The number of attributes is called the degree of that table. Look at some of the attributes shown below. Peso file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (10 of 12) [6/26/02 12:11:01 PM]
0103. Database Models Australia New Zealand Although are relational model prescribe these above terms they do not appear in the daily usage. The terms "records" and "fields" are commonly used. Advantages of a Relational Database Model Some of the salient advantages of a relational database model have been listed below: Built in integrity at various levels. Allow data integrity to be incorporated at the field level to ensure data accuracy; integrity at the table level to avoid duplication of records and to detect records with missing primary key values; At the relationship level to ensure that relationships between tables are valid. Logical and Physical data Independence from database applications Changes made in the logical design of the database or changes made in the database software will adversely affect the implementation of the database. Data consistency and accuracy Due to the various levels, at which data integrity can be built in, data is accurate and consistent. Easy data retrieval and data sharing file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (11 of 12) [6/26/02 12:11:01 PM]
0103. Database Models Data cab be easily extracted from one or more than one tables. Data can also be easily shared users. file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (12 of 12) [6/26/02 12:11:01 PM]
0104. What is RDBMS TOP:3 What is Relational Database Management System ? A relational database is a database structured on the relational model. A Relational Database Management System or RDBMS is a suite of software programs that can be used for creating, maintaining, modifying and manipulating a relational database. It can also be used to create the application that a user will require for interacting with the data stored within the database. A very important point to note here is that an RDBMS that satisfies the 12 criteria lay down by Dr. Codd is called a true RDBMS. (Refer to Appendix A for Dr. Codd's 12 Rules). Kinds of Relations Various kinds of relations (tables) can exist in a relational system. They are listed below: Base Tables Query results Views We are familiar with the concept of base tables. The other two types will be discussed in subsequent sessions. Base Tables A base table is a table with a name that physically exists in a database. It is a created by the user, not something that is derived file:///D|/JigneshDhol/Oracle/0104.%20What%20is%20RDBMS.htm (1 of 5) [6/26/02 12:11:03 PM]
0104. What is RDBMS from another table. A base table can be created, altered and removed from a database. All these tasks are accomplished using SQL statement, which will be covered in detail in subsequent sessions. Query Results When a 'question' is asked to a table, the resultant data is also stored in tables. Such tables are called query result tables. Views A view is a virtual table. Some columns of a base table may not be required by the user. In such cases, a view is created. This view will consist only of those columns of the base table that the user is interested in seeing. This format can be saved as a view by giving it a name. This concept will be covered in subsequent sessions in further detail. Now that we have a better understanding of RDBMS concept, we progress and understand how this relational model is represented. In a previous session, we understood what the relational model is. A database is meant to store data and this data in turn should provide information needs of an organization, a conceptual model has to be designed first. The first step in this direction will be the collection and gathering of the data that will be required information. The data analysis process is where this will take place. Data analysis entails the collection of bills, reports, forms and other such records. The next step is to assess the uses of the organizations data and removing the data that is not required or is getting repeated. A data analysis also involves identifying tables, their fields and records and establishing relationships between them. The completion of data analysis is marked by the drawing of the entity - relationship diagram or the ER diagram. In order to understand how file:///D|/JigneshDhol/Oracle/0104.%20What%20is%20RDBMS.htm (2 of 5) [6/26/02 12:11:03 PM]
0104. What is RDBMS to draw the ER diagram, it is important to first appreciate the ER model. In this section, we will learn about the ER model and ER diagrams. The ER data model is based on the object-based logical models. Chen introduced this model and its diagramming technique. Let's examine the components of this model here. Thus model comprises of the following components. Entities Attributes Relationship Entities An entity can be defined as anything, which can be distinctly identified. A place, person, picture, thing, concept, process, result or data are some of the examples of an entity. As can be seen, the term concept a very broad spectrum. Entity Set An entity set of entities of some type. The set of person studying in a class can be defined as an entity set of student. Similarly it is possible to define to customers of a shop, patients of a clinic etc. as entity sets. Attributes Each entity has a specific characteristic that is defined by the attribute Relationships A relationship is defined as an association among entities. We shall understand this model with the help of an example. file:///D|/JigneshDhol/Oracle/0104.%20What%20is%20RDBMS.htm (3 of 5) [6/26/02 12:11:03 PM]
0104. What is RDBMS Consider an organization having many departments in it. Each department has several employees are managed by a department head. Each entity is related with another relationships. In example that we have considered above, each of the entities i.e. the department are all distinct entities. Relationships in a relational database model are categorized as: One to One (1:1) One to Many (or many to one)(1:M) Many to Many (M:M) Let us understand how these types of relationships apply in our example. Each employee given a unique employee number. Since any one employee can have only one employee number, this is called a one to one relationship; one department can have only one department head. This is also an example of a one to one relationship. However, one department can have several employees working it. This is an example of a one to many relationships. Thus, it can be said that the relationship existing between employees and a department is of the 1:M type. In most real life situations, is difficult to find a many to many relationship. In our example, here we do not have many to many file:///D|/JigneshDhol/Oracle/0104.%20What%20is%20RDBMS.htm (4 of 5) [6/26/02 12:11:03 PM]
0104. What is RDBMS relationships between any of entities. If user is a familiar with the relationship among the tables in the database, data can be accessed in a number of ways. Data can be accessed from tables. Which are directly as well as indirectly related. In the next section, we will learn how to represent these relationships pictorially. The ER diagram is a way of expressing of representing this relationship. We will learn more about this in detail in the next section. file:///D|/JigneshDhol/Oracle/0104.%20What%20is%20RDBMS.htm (5 of 5) [6/26/02 12:11:03 PM]
0105. Difference DBMS vs RDBMS TOP:4 Difference between DBMS v/s RDBMS DBMS RDBMS The concept of relationships It is based on the concept is missing in a DBMS. If it of relationships. exists it is very less Speed of operation is very Speed of operation is very slow. fast. Hardware and software Hardware and software requirements are less. requirements are high. Facilities and Utilities Facilities and Utilities offered are limited. offered are many. file:///D|/JigneshDhol/Oracle/0105.%20Difference%20DBMS%20vs%20RDBMS.htm (1 of 2) [6/26/02 12:11:04 PM]
0105. Difference DBMS vs RDBMS Platform is used is normally Platform used can by any DOS DOS, UNIX, VAX,VMS etc. Uses concept of a file. Uses concept of a table. DBMS normally use a 3GL. RDBMS normally use a 4GL. Examples are dBASE, Examples are ORACLE, FOXBASE etc. INGERS etc. file:///D|/JigneshDhol/Oracle/0105.%20Difference%20DBMS%20vs%20RDBMS.htm (2 of 2) [6/26/02 12:11:04 PM]
0106. E - R Diagram TOP:5 Entity Relationship Diagram Diagrams are one of the better ways to communicate different of a components of a system. They are also too easy to understand by everyone. They offer an overview of the entire system. An E-R diagram is graphical method of representing entity classes, attributes and relationships. An E-R diagram uses six basic symbols: • A rectangle to denote an entity or entity set. • A diamond to denote a relationship between two entities. • An oval to denote attributes. • A '1' to denote a single occurrence. • An 'M' to denote multiple occurrences. • A line which links attributes to an entity or entity set and entity sets to relationships. When an E-R diagram is built. The first step is defining entities. The next step is to define the relationship between the entities. The final step to identify the attributes that belong to each entity. Once the E-R diagram is completed. The entities will become the files (or table). Figure illustrates a many-to-one relationship between the entity sets Employee and Department. The next process is that of normalization. Which will be covered in detail in the next session. file:///D|/JigneshDhol/Oracle/0106.%20E%20-%20R%20Diagram.htm (1 of 3) [6/26/02 12:11:06 PM]
0106. E - R Diagram The importance of relationships We will understand why relationships are a vital part of the database. The main reasons are listed and described below. • Relationships establish a connection between a pair of tables that are logically related to each other in some manner. Data in a customer's table and orders tables are logically related. When a customer orders for an item, this order is recorded in the order table. Hence a customer record in the customer's table is related to the record in the order table. • Relationships help refine and streamline table structures. This helps in further in removing data redundancy. • Data for many tables can be extracted at the same time if relationship exists among the table. • A well-defined relationship helps maintain a high level integrity. For example a customer record from the customer table cannot be deleted if a record for that customer exists in the order table. We will learn as progress with our study of RDBMS, that establish file:///D|/JigneshDhol/Oracle/0106.%20E%20-%20R%20Diagram.htm (2 of 3) [6/26/02 12:11:06 PM]
0106. E - R Diagram relationship carefully help in designing database that are easy to use. file:///D|/JigneshDhol/Oracle/0106.%20E%20-%20R%20Diagram.htm (3 of 3) [6/26/02 12:11:06 PM]
0107. Types of Relationship TOP:6 Types of Relationship One to One (1:1) One to Many (1:M) or Many to One Many to Many (M:M) One to One A pair of table is define as having one to one relationship if one record in the first table is related to only one record in the second table. Let's consider an example to understand this. In our library example, supports we have a books table with details of books as follows: Book code Book name Book Author Book Publisher Book Cost etc. The primary key is the book code. It is required to stored publisher details in separate tables because many books can have the same publisher. This leads to redundancy. Hence, a new table called publisher is created the structure is follows: Book Code file:///D|/JigneshDhol/Oracle/0107.%20Types%20of%20Relationship.htm (1 of 3) [6/26/02 12:11:07 PM]
0107. Types of Relationship Publisher Name Publisher Add. Publisher Tele. / Fax etc. In this table, the book code is foreign key. Since any book can have only one publisher, this is an example of one to one relationship. Look at the pictorial representation of this relationship. One to Many A relationship is defined as one to many when a single record in the first table points to many records in the second table. However, a single record in the second table can only point to one record in the table. Let's consider our library example again. Like our publisher table suppose we create author table. One book title can be written by several authors take the example of the book title-oracle power object- developers guide. A publisher is Mc. Grow Hill and the authors are R. Finkelstern, R. Greenwald and kasu sista file:///D|/JigneshDhol/Oracle/0107.%20Types%20of%20Relationship.htm (2 of 3) [6/26/02 12:11:07 PM]
0107. Types of Relationship In the fig. Above, notice that the three arrows from a picture that reassembles a foot of crow. A crowfoot is normally used to signify the one to many relationships. Many to Many Relationship A many to relationship exists between two tables if a single record in the first table points to more then one record in the second table and a single record in the second table points to many records in the first table. We will understand this with the help of an example. We have already seen the issue table and the member table in this case one member can be issued (can borrow) many books at one instance. At the same time, one book title can be borrowed by more than one member at any instance. Not the crow feet pointing at both tables file:///D|/JigneshDhol/Oracle/0107.%20Types%20of%20Relationship.htm (3 of 3) [6/26/02 12:11:07 PM]
0108. Normalization TOP:7 Normalization It is one of the most important concepts in the study of the RDBMS. The case with which information is stored and retrieved. Depends a lot on the way of the tables have been defined. Tables that are hugged and bulky often defeat the purpose of having an RDBMS all together. This is because such tables may not be easy to maintain In this section, we will discuss the concept of normalization in detail. It can be defined as a processed of putting data right-making it normal. Normalization is important from the database design. Designer point of view as it enables him to design better. It is concerned with database design. There are two ways of approaching logical database design- The top down approach The bottom-up approach In the top down approach, first entities and relationship are identified; the ER diagram is made and mapped with the tables. The ER modeling technique uses the top down approach. Normalization uses the bottom up approach. Normalization is the technique that makes the relational data files differ from other data files, which are referred to as flat files we will understand this with the help of an example. A library maintains a register of all books issued to its members. The register contain the following column file:///D|/JigneshDhol/Oracle/0108.%20Normalization.htm (1 of 5) [6/26/02 12:11:10 PM]
0108. Normalization No. Name of the book Borrowed by Date of issue Date of return Every time a member failed to return the book, a letter is sent/a telephone call is made to that member's house for reminding. In separate part of the register. A list of all members name along with their address and telephone numbers is maintain. Member name Address and telephone number. Now every time a member defaults in returning a book, the librarian looks of the name of that member in the list and makes a reminder call. In this case we can say that the librarian has a relational database on paper. This is called a normalized data. Normalization is here referring to data being collect and stored in natural grouping. Issue detail of book and member details are stored as separate groups or lists. Like this library register even in RDBMS, it is imperative to have normalized table. Normalization can be defined as the process of the restructuring a relation (table). For reducing it to a form where each domain would consist of single non composite values Benefits of normalization Normalization reduces repetition for example data redundancy. When the same data is reputedly is stored it cases storage and access problems. file:///D|/JigneshDhol/Oracle/0108.%20Normalization.htm (2 of 5) [6/26/02 12:11:10 PM]
0108. Normalization • Inconsistency in data retrieval • error while updating data tables Suppose the address and the telephone nos. of the members were Grouped together in one table along with book details. How would it make difference? First let take a look at the table below: No Name Borrowed Address Telephone Date of Date of of By Issue Return the book As you can see there are no groups everything is put in one table now consider a member who borrow above four books every weeks, it's now easy to see how many times the address and telephone no of this member will be stored. In the table leading to a huge amount of data Suppose one member is changed his address. The no. of rows were the address will have to be changed will be many. This duplication of effort due to poor database design. as it can be seen data that has not been normalized can be lead to a several problems two of which we have discussed. Having understood normalization and its benefits let's proceed what happens after a table is normalized. Normal Form Dr. Codd originally defines three levels of normalization. These three file:///D|/JigneshDhol/Oracle/0108.%20Normalization.htm (3 of 5) [6/26/02 12:11:10 PM]
0108. Normalization levels were called first normal form. Second normal form and third normal form respectively. Normalization is usually discussed in terms of forms. Normal forms are table structures with minimum redundancy. Normal forms that have been identified are: • First normal form (1st NF) • Second normal form (2nd NF) • Third normal form (3rd NF) • Boyce-Codd normal form The first three forms were defined by Dr. codd. Later Dr. codd and Boyce introduced one more normal form, which called the Boyce-Codd normal form. Theory of normalization isa based on the concept of function dependency. The diagram below illustrates the levels of normalization. In order to understand more about normal form we must have understand is meant by functional dependency. Establish deletion rules Data integrity is one of the main advantages that relationships offer in an RDBMS. Deletion rules must be established for a relationship. This rule states what will happen if a record has to be deleted. Defining a deletion rule prevents records from being operand; i.e. the record will file:///D|/JigneshDhol/Oracle/0108.%20Normalization.htm (4 of 5) [6/26/02 12:11:10 PM]
0108. Normalization exist in a subordinate table but will not exist in the main table. There can be two option applicable for deletion rule. Restrict: when deletion is restrict, a record in a subordinate table of a one to one or many to one relationship can not be deleted for example a publisher whose books are still found in the books table can not be deleted. There are several examples where significance of such a rule can be appreciated further. In an employ table, an employ cannot be deleted because a salary table still hold his record. In a pending order table, pending order cannot be deleted until the order has been serviced. Customer who has not paid these dues cannot be deleted from the customer table. Cascade: in this type of rule when a record is deleted, all related records in all subordinate tables will also be deleted. In the first session we had talk about RDBMS maintaining data integrity. This is how it is possible. file:///D|/JigneshDhol/Oracle/0108.%20Normalization.htm (5 of 5) [6/26/02 12:11:10 PM]
0109. Codd's Rules TOP:8 Codd's Rules E.F.TED CODD'S LAWS For a fully functional Relational Database Management System Relational Database Management A relational database management system uses only its relational capabilities to manage the information stored in its database. Information Representation All information stored in a relational database is represented only by data item values, which are stored in the tables that make up the database. Associations between data items are not logically represented in any other way, such as, by the use of pointers from one table to the other. Logical Accessibility Every data item value stored in a relational database is accessible by stating the name of the table it is stored in, the name of the column under which it is stored and the value of the primary key that defines the row in which it is stored. Representation of null values The database management system has a consistent method for representing null values. For example, null values for numeric data must be distinct from zero or any other numeric value and for character data it must be different from a string of blanks or any other character value. file:///D|/JigneshDhol/Oracle/0109.%20Codd's%20Rules.htm (1 of 3) [6/26/02 12:11:11 PM]
0109. Codd's Rules Catalog facilities The logical description of a relational database is represented in the same manner as ordinary data. This is done so that the facilities of the relational database management system itself can be used to maintain database description. Data Language A relational database management system may support many types of languages for describing data and accessing the database. However, there must be at least one language that uses ordinary character strings to support the definition of data, the definition of views, the manipulation of data, constraints on data integrity, information concerning authorization and the boundaries for recovery of units. View Updatibility Any view that can be defined combinations of base tables, which are theoretically updateable, is capable of being updated by the relational database management system. Insert, Update and Delete Any operand that describes the results of a single retrieval operation is capable of being applied to an insert, update or delete operation as well. Physical Data independence Changes made to physical storage representations or access methods do not require changes to be made to application programs. Logical data independence Changes made to tables, that do not modify any data stored in that file:///D|/JigneshDhol/Oracle/0109.%20Codd's%20Rules.htm (2 of 3) [6/26/02 12:11:11 PM]
0109. Codd's Rules table, do not require changes to be made to application programs. Integrity Constraints Constraints that apply to entity integrity and referential integrity are specifiable by the data language implemented by the database management system and not by the statements coded into the applications program. Database Distribution The data language implemented by the relational database management system supports the ability to distribute the database without requiring changes to be made to application programs. This facility must be provided in the data language, Whether or not the database management system itself supports distributed databases. Non-Subversion If the relational database management system supports facilities that allow application programs to operate on the tables a row at a time, an application program using this type of database access is prevented from bypassing entity integrity or referential integrity constraints that are defined for the database. file:///D|/JigneshDhol/Oracle/0109.%20Codd's%20Rules.htm (3 of 3) [6/26/02 12:11:11 PM]
0202. Introduction to SQL TOP:1 Introduction to SQL (Structured Query Language) Every data table that we have created so far us. All this data would data serve very little purpose if it could not be retrieved. In order to retrieve this data, one needs to be able to 'talk' to the tables. The structure query language allows users of the database communicate with the database. Data in a relational database can be retrieved using a standard language like SQL. In an English like computer language, which makes interaction between user and the database very simple. Let us first look at its evolution. SQL - A Brief History SQL was first introduced by Dr. Codd in his pioneering work 'A relational Model for Large Shared Data Banks'. Since introduction many researchers at the San Jose Research Laboratories made efforts in implementing Dr. Codd's ideas, the mid of 70s saw the development of many computer programming language based on this relational model. One of these was called Structure English Query Language of SEQUEL language. System T did well with relational database. One of such organization called relational software from Belmont, California made it software commercially available. It was a relational database called ORACLE. This company later changed ins name to oracle corporation because of the success of this software. Today oracle manufactures a wide range of SQL products along with Relational Database Management System software. Since many software vendors started offering a large number of SQL file:///D|/JigneshDhol/Oracle/0202.%20Introduction%20to%20SQL.htm (1 of 4) [6/26/02 12:12:14 PM]
0202. Introduction to SQL base products, there was a dire need to standardize this language. The first SQL standards were later adopted by the international standard organization (ISO) in 1987. The database language SQL was form under the approval both ANSI and ISO. Why SQL? Let us understand that why there is a need to SQL. Although other programming language exist. The primary reason for doing so is that SQL was created for the relational database. The relation model was the considered when creating a SQL. SQL therefore is like a co-worker. Assisting the RDBMS achieve the user's requirement. SQL simplifies the task of creating, Manipulating and communicating the database. A traditional and general programming language like C would be difficult to use this purpose. Characteristics of SQL SQL is Non Procedural Language in the conventional programming language, coding is essential to achieve a given task. In addition to specifying the task to achieved needs to be specifying the tasks to be achieved. How to go about doing it has also to be specified. In SQL only the task that has to be achieved needs to specify. For example, to retrieve rows from a table we simply use the SELECT command. A Data Sub language SQL does not support programming language constructs. Conditional statements like 'If Then', 'While' can not be used in SQL. Not a database management system SQL is an important tool for communication with the DBMS and supports database management statements. file:///D|/JigneshDhol/Oracle/0202.%20Introduction%20to%20SQL.htm (2 of 4) [6/26/02 12:12:14 PM]
0202. Introduction to SQL Can be embedded in third generation languages like 'C' or 'COBOL' to facilities database access. Use of SQL SQL is language used for communication with the DBMS. It is a language that can be used by all users such as System Administrator Database Administrator Application Programmer Management Personnel End Users it can used for the following: It is an interactive query language that allows users to use SQL statements to retrieve the data and display on it screen. SQL is an important tool that allows adhoc queries on the database. A database programming language that allows programmer to embed SQL statement in third generation language program to access data from the database. A database Administration Language that define the structure of database, controls the user access to data and also the level of user access. Client/Server Language that allows application programs on PCs connected via LAN to communicate with the database server using SQL. Application using client/server Language make optimum use of PCs and servers and also reduce traffic file:///D|/JigneshDhol/Oracle/0202.%20Introduction%20to%20SQL.htm (3 of 4) [6/26/02 12:12:14 PM]
0202. Introduction to SQL over the LAN. Database Gateway Language uses SQL to communicate with the distributed database. Distributed Database language is used when data is distributed over many machine. The Distributed Database Management System uses SQL to communicated with the distributed database. Types of SQL SQL is two types-interactive and embedded. While both operate exactly the same way their usage differs. Interactive SQL is used to interactive directly with the database there the output of the operation is used of human consumption. Once a command is specific, it is execute and the user can immediately view the output. In the case embedded SQL, commands are SQL commands that are written in some other languages, such as COBOL or Pascal. This makes to programs very fast and powerful. In this course, we will use SQL only in its interactive form. file:///D|/JigneshDhol/Oracle/0202.%20Introduction%20to%20SQL.htm (4 of 4) [6/26/02 12:12:14 PM]
0203. SQL Commands and Datatypes TOP:2 SQL Command and DataTypes SQL Commands can classified as under: DDL (Data Definition Language) CREATE to create table or objects ALTER to alter existing database DROP to drop existing objects TRUNCATE to remove whole data at a time DML (Data Manipulation Language) INSERT to insert data in table UPDATE to update existing data in table SELECT to view database DELETE to delete particular records in database TCL (Transaction Control Language) COMMIT to save buffer data to storage device file:///D|/JigneshDhol/Oracle/0203.%20SQL%20Commands%20and%20Datatypes.htm (1 of 5) [6/26/02 12:12:17 PM]
0203. SQL Commands and Datatypes ROLLBACK to undo save SAVEPOINT to keep break in save action DCL (Decision Control Language) GRANT to provide rights for user on database REVOKE to revoke user rights DATATYPES NUMERIC The NUMBER datatype is used to store zero, negative, positive, fixed and floating point numbers with up to 38 digits of precision.numbers range between 1.0 * 10 -130 and 1.0 * 10 126 . NUMBER( p , s ) Where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point ).The scale can range between - 84 and 127. NUMBER( p ) This is a fixed point number with a scale of zero and a precision of p. NUMBER file:///D|/JigneshDhol/Oracle/0203.%20SQL%20Commands%20and%20Datatypes.htm (2 of 5) [6/26/02 12:12:17 PM]
0203. SQL Commands and Datatypes This is a floating point number with a precession of 38. The following list shows how Oracle stores different scales and precisions.: Actual Data Defined as Stored as 123456.789 NUMBER(6,2) 123456.79 123456.789 NUMBER(6) 123457 123456.789 NUMBER(6,-2) 123400 123456.789 NUMBER 123456.789 DATE Instead of storing date and time information in a character or numeric format. IBM created a separate datatype. for each DATE datatype, the following information is stored. Century - Year - Month - Day - Hour - Minute - Second You can easily retrieve the current date and time by using the function SYSDATE. Date arithmetic is possible using number constants or other dates.Only addition and subtraction are supported. For example, SYSDATE + 7 returns oneweek from today. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD- MON-YY, where DD is the day of the month (the first day of the month file:///D|/JigneshDhol/Oracle/0203.%20SQL%20Commands%20and%20Datatypes.htm (3 of 5) [6/26/02 12:12:17 PM]
0203. SQL Commands and Datatypes is 01), MON is the abbreviated month name,and YY is a two-digit designation. If you do not specify a time, the default time is 12:00:00 a.m. if only the time component is captured,the default date is the first day of the current month. CHARACTER There are six character types available: • The CHAR datatype is used where fixed-length fields are necessary. Any length up to 2,000 characters can be specified. The default length is 1. When Data is entered any space left over is filled with blanks. All alphanumeric characters are allowed. • The VARCHAR2 is used for variable-length fields. A length component must be supplied when you use this datatype. The maximum length is 4000 characters. All alphanumeric characters are allowed. • The LONG datatype is used to store large amounts of variables-length. Any length up to 2GB can be specified. Be aware that there are some restrictions to using this datatype: Only one column per table can be defined as LONG. A LONG column cannot be indexed. A LONG column cannot be passed as an argument to a procedure. You cannot use a function to return a LONG column. You cannot use a LONG column in WHERE, ORDER BY, GROUP BY or DISTINCT by clauses. file:///D|/JigneshDhol/Oracle/0203.%20SQL%20Commands%20and%20Datatypes.htm (4 of 5) [6/26/02 12:12:17 PM]
0203. SQL Commands and Datatypes • The VARCHAR datatype is synonymous with VARCHAR2.Oracle Corporation is reserving this for future use. Do not use this datatype. BINARY Two datatypes, RAW and LONG RAW, are available for storing binary type data such as digitized sound and images. These datatypes take on characteristics similar to the VARCHAR2 and LONG datatypes already mentioned. Use the RAW datatype to store binary data up to 2,000 bytes and use the LONG RAW datatype to store binary data up to 2GB. Oracle stores and retrieves only binary data; no string manipulations are allowed. Data is retrieved as hexadecimal character values. file:///D|/JigneshDhol/Oracle/0203.%20SQL%20Commands%20and%20Datatypes.htm (5 of 5) [6/26/02 12:12:17 PM]
0206. Operators and Expression TOP:3 Operators and Expressions EXPRESSION The definition of an expression is simple: An expression returns a value. Expression types are very broad, covering different data types such as String, Numeric, and Boolean. In fact, pretty much anything following a clause (SELECT or FROM, for example) is an expression. In the following example amount is an expression that returns the value contained in the amount column. SELECT amount FROM checks; In the following statement NAME, ADDRESS, PHONE and ADDRESSBOOK are expressions: SELECT NAME, ADDRESS, PHONE FROM ADDRESSBOOK; Now, examine the following expression: WHERE NAME = 'BROWN' It contains a condition, NAME = 'BROWN', which is an example of a Boolean expression. NAME = 'BROWN' will be either TRUE or FALSE, depending on the condition =. CONDITIONS If you ever want to find a particular item or group of items in your file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (1 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression database, you need one or more conditions. Conditions are contained in the WHERE clause. In the preceding example, the condition is NAME = 'BROWN' To find everyone in your organization who worked more than 100 hours last month, your condition would be NUMBEROFHOURS > 100 Conditions enable you to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator. In the first example the variable is NAME, the constant is 'BROWN', and the comparison operator is =. In the second example the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >. You need to know about two more elements before you can write conditional queries: the WHERE clause and operators. The WHERE Clause The syntax of the WHERE clause is SYNTAX: WHERE SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective. Without the WHERE clause, the most useful thing you could do with a query is display all records in the selected table(s). For example: INPUT: SQL> SELECT * FROM BIKES; file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (2 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression lists all rows of data in the table BIKES. OUTPUT: NAME FRAMESIZE COMPOSITIONMILESRIDDENTYPE TREK 2300 22.5 CARBON 3500 RACING FIBER BURLEY 22 STEEL 2000 TANDEM GIANT 19 STEEL 1500 COMMUTER FUJI 20 STEEL 500 TOURING SPECIALIZED 16 STEEL 100 MOUNTAIN CANNONDALE22.5 ALUMINUM 3000 RACING 6 rows selected. If you wanted a particular bike, you could type INPUT/OUTPUT: SQL> SELECT * FROM BIKES WHERE NAME = 'BURLEY'; which would yield only one record: NAME FRAMESIZE COMPOSITIONMILESRIDDENTYPE BURLEY 22 STEEL 2000 TANDEM ANALYSIS: file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (3 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression This simple example shows how you can place a condition on the data that you want to retrieve. OPERATORS Arithmetic Logical Like Relational Miscellaneous + AND LIKE In * NOT = Any / = The != , =, <= BETWEEN&ldots;&ldots;AND , IN LIKE, IS NULL • NOT file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (4 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression • AND OPERATORS in Details: Operators are the elements you use inside an expression to articulate how you want specified conditions to retrieve data. Operators fall into six groups: arithmetic, comparison, character, logical, set, and miscellaneous. Arithmetic Operators The arithmetic operators are plus (+), minus (-), divide (/), multiply (*), and modulo (%). Modulo returns the integer remainder of a division. Here are two examples: 5 % 2 = 1 6 % 2 = 0 If you place several of these arithmetic operators in an expression without any parentheses, the operators are resolved in this order: multiplication, division, modulo, addition, and subtraction. The following sections examine the arithmetic operators in some detail and give you a chance to write some queries. Plus (+) You can use the plus sign in several ways. Type the following statement to display the PRICE table: INPUT: SQL> SELECT * FROM PRICE; OUTPUT: file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (5 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression ITEM WHOLESALE TOMATOES 0.34 POTATOES 0.51 BANANAS 0.67 3 rows selected. Now type: INPUT/OUTPUT: SQL> SELECT ITEM, WHOLESALE, WHOLESALE + 0.15 FROM PRICE; Here the + adds 15 percents to each price to produce the following: ITEM WHOLESALES WHOLESALE + 0.15 TOMATOES 0.34 0.49 POTATOES 0.51 0.66 BANANAS 0.67 0.82 3 rows selected. ANALYSIS: What is this last column with the unattractive column heading WHOLESALE+0.15 ? It's not in the original table. (Remember, you used * in the SELECT clause, which causes all the columns to be shown.) SQL allows you to create a virtual or derived column by file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (6 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression combining or modifying existing columns. Retype the original entry: INPUT/OUTPUT: SQL> SELECT * FROM PRICE; The following table results: ITEM WHOLESALE TOMATOES 0.34 POTATOES 0.51 BANANAS 0.67 3 rows selected. ANALYSIS: The output confirms that the original data has not been changed and that the column heading WHOLESALE+0.15 is not a permanent part of it. In fact, the column heading is so unattractive that you should do something about it. NOTE: It is simple to use all the other arithmatic operators like Plus (+). Comparison Operators file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (7 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression Comparison operators compare expressions and return one of three values: TRUE, FALSE, or Unknown. Wait a minute! Unknown? TRUE and FALSE are self-explanatory, but what is Unknown? To understand how you could get an Unknown, you need to know a little about the concept of NULL. In database terms NULL is the absence of data in a field. It does not mean a column has a zero or a blank in it. A zero or a blank is a value. NULL means nothing is in that field. If you make a comparison like Field = 9 and the only value for Field is NULL, the comparison will come back Unknown. Because Unknown is an uncomfortable condition, most flavors of SQL change Unknown to FALSE and provide a special operator, IS NULL, to test for a NULL condition. Here's an example of NULL: Suppose an entry in the PRICE table does not contain a value for WHOLESALE. The results of a query might look like this: INPUT: SQL> SELECT * FROM PRICE; OUTPUT: ITEM WHOLESALE TOMATOES 0.34 POTATOES 0.51 ORANGES Notice that nothing is printed out in the WHOLESALE field position for oranges. The value for the field WHOLESALE for oranges is NULL. The NULL is noticeable in this case because it is in a numeric column. However, if the NULL appeared in the ITEM column, it would be impossible to tell the difference between NULL and a blank. file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (8 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression Try to find the NULL: INPUT/OUTPUT: SQL> SELECT * FROM PRICE WHERE WHOLESALE IS NULL; ITEM WHOLESALE ORANGES ANALYSIS: As you can see by the output, ORANGES is the only item whose value for WHOLESALE is NULL or does not contain a value. What if you use the equal sign (=) instead? INPUT/OUTPUT: SQL> SELECT * FROM PRICE WHERE WHOLESALE = NULL; no rows selected ANALYSIS: You didn't find anything because the comparison WHOLESALE = NULL returned a FALSE the result was unknown. It would be more appropriate to use an IS NULL instead of =, changing the WHERE statement to WHERE WHOLESALE IS NULL. In this case you would get all the rows where a NULL existed. This example also illustrates both the use of the most common comparison operator, the equal sign (=), and the playground of all comparison operators, the WHERE clause. You already know about the WHERE clause, so here's a brief look at the equal sign. file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (9 of 10) [6/26/02 12:12:20 PM]
0206. Operators and Expression file:///D|/JigneshDhol/Oracle/0206.%20Operators%20and%20Expression.htm (10 of 10) [6/26/02 12:12:20 PM]
0304. SELECT statement TOP:4 SELECT Statement The general syntax for a SELECT statement: SYNTAX: SELECT [DISTINCT | ALL] { * | { [schema.]{table | view | snapshot}.* | expr } [ [AS] c_alias ] [, { [schema.]{table | view | snapshot}.* | expr } [ [AS] c_alias ] ] } FROM [schema.]{table | view | snapshot}[@dblink] [t_alias] [, [schema.]{table | view | snapshot}[@dblink] [t_alias] ] [WHERE condition ] [GROUP BY expr [, expr] [HAVING condition] ] [{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ] [ORDER BY {expr|position} [ASC | DESC] [, {expr|position} [ASC | DESC]] ] Query:1 The simple first query to perform, which yields all records available in table INPUT: file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (1 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement SQL> select * from dept; OUTPUT: DEPTNO DNAME LOC 10 ACCOUNTINGNEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ANALYSIS: This output looks just like the code in the example. Notice that columns 1 in the output statement are right-justified and that columns 2 and 3 are left-justified. This format follows the alignment convention in which numeric data types are right-justified and character data types are left-justified. The asterisk (*) in select * tells the database to return all the columns associated with the given table described in the FROM clause. The database determines the order in which to return the columns. Terminating an SQL Statement In implementations of SQL, the semicolon at the end of the statement tells the interpreter that you are finished writing the query. Query:2 Changing the Order of the Columns or selecting file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (2 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement particular columns The preceding example of an SQL statement used the * to select all columns from a table, the order of their appearance in the output being determined by the database. To specify the order of the columns, you could type something like: INPUT: SQL> SELECT dname, deptno from dept; Notice that each column name is listed in the SELECT clause. The order in which the columns are listed is the order in which they will appear in the output. Notice both the commas that separate the column names and the space between the final column name and the subsequent clause (in this case FROM). The output would look like this: OUTPUT: DNAME DEPTNO ACCOUNTING 10 RESEARCH 20 SALES 30 OPERATIONS 40 Another way to write the same statement follows. INPUT: file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (3 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement SQL> SELECT dname, DEPTNO FROM dept; Notice that the FROM clause has been carried over to the second line. This convention is a matter of personal taste when writing SQL code. The output would look same as above. Now you have the columns you want to see. Notice the use of upper- and lowercase in the query. It did not affect the result. Query:3 Queries with Distinction If you look at the original table, EMP, you see that some of the data repeats. For example, if you looked at the JOB column using INPUT: SQL> select JOB from emp; you would see OUTPUT: JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (4 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK 14 rows selected. Notice that the job 'salesman' is repeated. What if you wanted to see how may different jobs were in this column? Try this: INPUT: SQL> select DISTINCT job from emp; The result would be OUTPUT: JOB file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (5 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement CLERK SALESMAN MANAGER ANALYST PRESIDENT 5 rows selected. ANALYSIS: Notice that only five rows are selected. Because you specified DISTINCT, only one instance of the duplicated data is shown. ALL is a keyword that is implied in the basic SELECT statement. You almost never see ALL because SELECT and SELECT ALL have the same result. Query:4 WHERE Clause With WHERE in your vocabulary, you can be more selective. To find all the employee having salary more than 2500 INPUT: SQL> SELECT ename, job, sal FROM emp WHERE SAL> 2500; The WHERE clause returns the five instances in the table that meet the required condition: file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (6 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement OUTPUT: ENAME JOB SAL JONES MANAGER 2975 BLAKE MANAGER 2850 SCOTT ANALYST 3000 KING PRESIDENT 5000 FORD ANALYST 3000 Query:5 The LIKE Clause LIKE is an addition to the WHERE clause that works as a helping hand to WHERE. Compare the results of the following query: INPUT: SQL> SELECT empno, ename FROM emp WHERE ename LIKE 'S%'; OUTPUT: EMPNO ENAME 7369 SMITH file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (7 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement 7788 SCOTT Query:6 The ORDER BY Clause From time to time you will want to present the results of your query in some kind of order. As you know, however, SELECT FROM gives you a listing, and unless you have defined a primary key, your query comes out in the order the rows were entered. Consider a beefed-up DEPT table: INPUT: SQL> select * from dept order by dname; OUTPUT: DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 40 OPERATIONS BOSTON 20 RESEARCH DALLAS 30 SALES CHICAGO INPUT: SQL> select * from dept order by deptno; file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (8 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement OUTPUT: DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON ANALYSIS: The ORDER BY clause gives you a way of ordering your results. For example, to order the preceding listing by check DNAME in alphabetic order, you would use ORDER BY clause: Query:6 The GROUP BY Clause You have/had learned how to use aggregate functions (COUNT, SUM, AVG, MIN, and MAX). If you wanted to find the total amount of money spent on salary for employee, you would type: INPUT: SQL> SELECT SUM(sal) from emp; OUTPUT: SUM(SAL) file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (9 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement 29025 You might get different result for this, it depends on salary column value in your database. Now if you want to calculate sum of salary department wise then you have to perform this way, INPUT: SQL> SELECT deptno, sum(sal) from emp group by deptno; OUTPUT: DEPTNO SUM(SAL) 10 8750 20 10875 30 9400 NOTE: One simple rule, if you wish any table column along with aggregate function then you must use to write that column with group by clause else you'll get error like this; INPUT: SQL> SELECT deptno, job, avg(sal) from emp group by deptno; OUTPUT: SELECT deptno, job, avg(sal) from emp group by deptno file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (10 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement * ERROR at line 1: ORA-00979: not a GROUP BY expression ANALYSIS: Here job is not included in group by expression, now let's correct this, INPUT: SQL> SELECT deptno, job, avg(sal) from emp group by deptno, JOB; OUTPUT: DEPTNO JOB AVG(SAL) 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 3000 20 CLERK 950 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (11 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement 30 SALESMAN 1400 9 rows selected. ANALYSIS: This provides group value for each deptno and available job for that department. AVG function yields result for each department and each job. Query:7 The HAVING Clause How can you qualify the data used in your GROUP BY clause? Use the table EMP and above example and try this:The following statement qualifies this query to return only those departments with average salaries more than 1300: INPUT: SQL> SELECT deptno, job, avg(sal) from emp group by deptno, JOB HAVING AVG(sal)>1300; OUTPUT: DEPTNO JOB AVG(SAL) 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 3000 file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (12 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement 20 MANAGER 2975 30 MANAGER 2850 30 SALESMAN 1400 6 rows selected. ANALYSIS: On sort, to provide condition on aggregate function or group by function one must require HAVING clause. FINAL EXAMPLE: INPUT: SQL> SELECT deptno, job, avg(sal) from emp group by deptno, JOB HAVING AVG(sal)<6000 and deptno=10 order by job desc; OUTPUT: DEPTNO JOB AVG(SAL) 10 PRESIDENT 5000 10 MANAGER 2450 10 CLERK 1300 ANALYSIS: file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (13 of 14) [6/26/02 12:12:24 PM]
0304. SELECT statement This query shows all the clauses including Group by, Order by and Having. Order By job Desc - here desc stands for descending. it pays results in descending alphabetic order for job, you can mark in above table. file:///D|/JigneshDhol/Oracle/0304.%20SELECT%20statement.htm (14 of 14) [6/26/02 12:12:24 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm TOP:5 Special Operators Operator:1 The LIKE Clause LIKE is an addition to the WHERE clause that works as a helping hand to WHERE. Compare the results of the following query: INPUT: SQL> SELECT empno, ename FROM emp WHERE ename LIKE 'S%'; OUTPUT: EMPNO ENAME 7369 SMITH 7788 SCOTT INPUT: SQL> SELECT empno, ename FROM emp WHERE ename LIKE '%N'; OUTPUT: EMPNO ENAME file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (1 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm 7499 ALLEN 7654 MARTIN INPUT: SQL> SELECT empno, ename FROM emp WHERE ename LIKE '%A%'; OUTPUT: EMPNO ENAME 7499 ALLEN 7521 WARD 7654 MARTIN 7698 BLAKE 7782 CLARK 7869 ADAMS 7900 JAMES Underscore ( _ ) The underscore is the single-character wildcard. Using a modified version of the table EMP, type this: file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (2 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm INPUT: SQL> SELECT empno, ename FROM emp WHERE ename LIKE '_LA%'; OUTPUT: EMPNO ENAME 7698 BLAKE 7782 CLARK ANALYSIS: Result shows the ename like any one character at the place of underscore ( _ ) then two words as 'LA' and rest any (%) character. You can use several underscores in a statement. Concatenation ( || ) The || (double pipe) symbol concatenates two strings. Try this: INPUT: SQL> SELECT empno || ename FROM emp WHERE job like 'MANAGER'; OUTPUT: EMPNO||ENAME file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (3 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm 7566JONES 7698BLAKE 7782CLARK ANALYSIS: Notice that || is used instead of +. If you use + to try to concatenate the strings, the SQL interpreter used for this example (Personal Oracle8) returns the following error: INPUT/OUTPUT: SQL> SELECT EMPNO + ENAME FROM emp; ERROR: ORA-01722: invalid number It is looking for two numbers to add and throws the error invalid number when it doesn't find any. NOTE: Some implementations of SQL use the plus sign to concatenate strings. Check your implementation. Here's a more practical example using concatenation: INPUT/OUTPUT: SQL> SELECT deptno || ' - ' || dname NAME FROM dept; NAME file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (4 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm 10 - ACCOUNTING 20 - RESEARCH 30 - SALES 40 - OPERATIONS ANALYSIS: This statement inserted a des ( - ) between the department no and the name. Notice the extra spaces between the first name and the last name in these examples. These spaces are actually part of the data. With certain data types, spaces are right-padded to values less than the total length allocated for a field. Operator:2 Logical Operators Logical operators separate two or more conditions in the WHERE clause of an SQL statement. INPUT/OUTPUT: SQL> SELECT empno, ename, deptno, sal FROM emp WHERE deptno = 10 AND sal > 2000; EMPNO ENAME DEPTNO SAL - 7782 CLARK 10 2450 file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (5 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm 7839 KING 10 5000 ANALYSIS: This query performs data search in a way that department no must be 10 as well salary must be greater than 2000. This means this query use logical operator and relational operator also. AND AND means that the expressions on both sides must be true to return TRUE. If either expression is false, AND returns FALSE. For example, to find out which employees have salary greater than 2000 and have registration in department 10. OR You can also use OR to sum up a series of conditions. If any of the comparisons is true, OR returns TRUE. To illustrate the difference, conditions run the last query with OR instead of with AND: INPUT/OUTPUT: SQL> SELECT empno, ename, deptno, sal FROM emp WHERE deptno = 10 OR sal > 2000; EMPNO ENAME DEPTNO SAL - 7566 JONES 20 2975 7698 BLAKE 30 2850 file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (6 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm 7782 CLARK 10 2450 7788 SCOTT 20 3000 7839 KING 10 5000 7902 FORD 20 3000 7934 MILLER 10 1300 ANALYSIS: The original names are still in the list, but you have three new entries.These five new names made the list because they satisfied one of the conditions. OR requires that only one of the conditions be true in order for data to be returned. NOT NOT means just that. If the condition it applies to evaluates to TRUE, NOT make it FALSE. If the condition after the NOT is FALSE, it becomes TRUE. For example, the following SELECT returns the only two names not beginning with S in the table: INPUT: SQL> SELECT ename, job FROM emp WHERE job NOT LIKE 'S%'; ENAME JOB SMITH CLERK JONES MANAGER file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (7 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK 10 rows selected. NOT can also be used with the operator IS when applied to NULL. Operator:3 Set Operators The following sections examine set operators. UNION and UNION ALL UNION returns the results of two queries minus the duplicate rows. The following two tables represent the rosters of teams: INPUT: SQL> SELECT ename, deptno FROM emp UNION SELECT dname, deptno FROM dept; file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (8 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm OUTPUT: ENAME DEPTNO ACCOUNTING 10 ADAMS 20 ALLEN 30 BLAKE 30 CLARK 10 FORM 20 JAMES 30 JONES 20 KING 10 MARTIN 30 MILLER 10 OPERATIONS 40 RESEARCH 20 SALES 30 SCOTT 20 file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (9 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm SMITH 20 TURNER 30 WARD 30 18 rows selected. ANALYSIS: The combined list courtesy of the UNION ALL statement has 18 names. UNION ALL works just like UNION except it does not eliminate duplicates. INTERSECT INTERSECT returns only the rows found by both queries. The next SELECT statement shows the list of deptno who are available on both tables: INPUT: SQL> SELECT deptno FROM emp INTERSECT SELECT deptno FROM dept; OUTPUT: file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (10 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm DEPTNO 10 20 30 ANALYSIS: In this example INTERSECT returns only those record which are available in both the tables. Graphical representation is shown below. MINUS (Difference) Minus returns the rows from the first query that were not present in the second. For example: INPUT: SQL> SELECT deptno FROM dept MINUS SELECT deptno FROM emp; OUTPUT: file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (11 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm DEPTNO 40 The preceding examples shows department no available in DEPT table but not available in table EMP. Graphical representation is shown below. Operators:4 IN and BETWEEN The two operators IN and BETWEEN provide a shorthand for functions you already know how to do. If you wanted to find employee having job any from ANALYST, MANAGER or PRESIDENT then rather using two or more OR statement, one should use IN clause. INPUT: SQL> SELECT ename, job FROM emp WHERE job = 'ANALYST' OR job = 'MANAGER' OR job = 'PRESIDENT' ; or SQL> SELECT ename, job FROM emp WHERE job IN ('ANALYST', 'MANAGER', 'PRESIDENT'); file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (12 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm OUTPUT: ENAME JOB JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT FORD ANALYST 6 rows selected. ANALYSIS: The second example is shorter and more readable than the first. You never know when you might have to go back and work on something you wrote months ago. INPUT: SQL> SELECT ename, sal FROM emp WHERE sal >= 2000 AND sal SELECT ename, sal FROM emp WHERE sal BETWEEN 2000 file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (13 of 14) [6/26/02 12:12:27 PM]
file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm AND 3000; OUTPUT: ENAME SAL JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 FORD 3000 file:///D|/JigneshDhol/Oracle/0305.%20Special%20Operator.htm (14 of 14) [6/26/02 12:12:27 PM]
0306. Join, Subquery, Built in functions TOP:6 Join, Subquery Join Today you will learn about joins. This information will enable you to gather and manipulate data across several tables. You will understand and be able to do the following: • Perform an equi-join • Join a table to itself (self join) • Perform an outer join • Perform a Between Join Introduction One of the most powerful features of SQL is its capability to gather and manipulate data from across several tables. Without this feature you would have to store all the data elements necessary for each application in one table. Without common tables you would need to store the same data in several tables. Imagine having to redesign, rebuild, and repopulate your tables and databases every time your user needed a query with a new piece of information. The JOIN statement of SQL enables you to design smaller, more specific tables that are easier to maintain than larger tables. A Few Join Considerations file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (1 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions 1. You may join up to 15 tables in one SELECT, But this limit would have to be considered a bit impractical. Four tables is more sensible - especially if you are using medium to large sized tables (10000 rows or more). 2. If there are identical column names referred to in the join, those column names must be predefined by a table name (or alias) to ensure uniqueness. 3. A join will retrieve all possible combinations of rows that satisfy the join condition. The datatypes for the columns in the join condition should be compatible (either both numeric or both character). Note that NULL values never satisfy a join condition. Even if both rows from both tables match with NULL values in them, the rows will not be selected. 4. The condition after the WHERE creates the vital link between the tables, that is necessary to restrict the selection to useful rows. 5. The WHERE clause may specify multiple selection criteria. 6. You must have authority to access the tables that you are going to join. Multiple Tables in a Single SELECT Statement (Equi-join) INPUT: SQL> SELECT emp.empno, emp.ename, emp.deptno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno; OUTPUT: file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (2 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions EMPNO ENAME DEPTNO DNAME 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING ANALYSIS: Check the query, in that table name is used to refer column before all the column. Now say dept table have 4 rows and emp table have 14 file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (3 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions rows. If you omit WHERE clause in query then you will get 14 * 4 = 56 records as a result. So it is compulsory to use WHERE clause in join query. This way equi join example. In place of table name as a reference of column you can generate table alias, so you need not to write long name of tables. To generate alias of table one need to write alias name in FROM clause. Example of the same is given here. SQL> SELECT e.empno, e.ename, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno; Self Join (Join to it self) A self join is the most powerful illustrator of the complex queries that can be written using the simple SELECT statement. A self join is a join of a table with itself. This query is executed by logically making two copies of the same table. To do this the same table has to be given two aliases, which can then be compared to one another. Suppose you need to find employee name along with name of manager instead of MGR (manager code), one must use self join query as : SQL> SELECT e1.empno, e1.ename, e1.mgr, e2.ename FROM emp e1, emp e2 WHERE e1.mgr=e2.empno; EMP NO ENAME MGR ENAME 7369 SMITH 7902 FORD 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7566 JONES 7839 KING file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (4 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions 7654 MARTIN 7698 BLAKE 7698 BLAKE 7839 KING 7782 CLARK 7839 KING 7788 SCOTT 7566 JONES 7844 TURNER 7698 BLAKE 7876 ADAMS 7788 SCOTT 7900 JAMES 7698 BLAKE 7902 FORD 7566 JONES 7934 MILLER 7782 CLARK 13 rows selected. ANALYSIS: Here, one can mark that KING is not available as employee but available in list of managers. That is because of KING does not have MGR value, and what we perform is equality concept as e1.mgr=e2.empno. Outer Join An outer join is a join similar to a simple join. A simple join is a join of two based on a common column. A special kind of simple join is the equi join. We have already discussed this type of join. An outer join is different from all other types of joins as it returns: file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (5 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions All the rows that are returned by a simple join and All those rows of one table that do not match with the rows in the other table. An outer join is used to join unmatched rows of a table. A table can be outer joined to at the most one table. SQL> SELECT c.client_no FROM client_master c, sales_order s WHERE s.client_no(+)=c.client_no; CLIENT C00001 C00001 C00002 C00003 C00004 C00005 C00006 As you can see there is a small '+' sign after the sales_order table. This sign is used to indicate an outer join. It can be put on either side of WHERE clause. It is however, append to that table which does not have matching rows. Here all customer may not available in sales_order table, but you are getting that record also because of outer join applies on client_master table. file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (6 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions Between Joins SQL has a special join that allows you to match column values in one table with a range of values in another table. A 'between join' is used to match rows of one table to rows in a second table by specifying that a column value in one table falls into a range of values specified in the other table. If you want to display the employee number, emp name, basic salary, grade and the salary limits for each employee, you will have to write the following query: SQL> SELECT empno, ename, sal, grade, losal, hisal FROM emp, salgrade WHERE sal BETWEEN losal AND hisal; EMP NO ENAME SAL GRADE LOSAL HISAL 7369 SMITH 800 1 700 1200 7499 ALLEN 1600 1 700 1200 7521 WARD 1250 1 700 1200 7566 JONES 2975 2 1201 1400 7654 MARTIN 1250 2 1201 1400 7698 BLAKE 2850 2 1201 1400 7782 CLARK 2450 3 1401 2000 7788 SCOTT 3000 3 1401 2000 file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (7 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions 7839 KING 5000 4 2001 3000 7844 TURNER 1500 4 2001 3000 7876 ADAMS 1100 4 2001 3000 7900 JAMES 950 4 2001 3000 7902 FORD 3000 4 2001 3000 7934 MILLER 1300 5 3001 9999 Subquery A subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together. You will understand and be able to use the keywords EXISTS, ANY, and ALL with your subqueries Building a Subquery Simply put, a subquery lets you tie the result set of one query to another. The general syntax is as follows: SYNTAX: SQL> SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE) Notice how the second query is nested inside the first. INPUT: SQL> SELECT client_no, name FROM client_master WHERE file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (8 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions client_no=(SELECT client_no FROM sales_order WHERE s_order_no='O19001'); OUTPUT: CLIENT NAME C00001 Ivan Bayross ANALYSIS: Above query can be explain in a way, Find the customer no, name for the client who has placed order no 'O19001'. This query can be evaluated with the use of joins also as, SQL> select c.client_no, c.name from client_master c, sales_order s where c.client_no=s.client_no and s.s_order_no='O19001'; The difference between join query and subquery is nothing but some time it is compulsory to use sub query. This can be evaluated from material given below. Nested SubQuery Nesting is the act of embedding a subquery within another subquery. For example: Select * FROM SOMETHING WHERE (SUBQUERY (SUBQUERY (SUBQUERY))); Find the department no which does not have any employee SQL> SELECT deptno FROM dept WHERE deptno NOT IN (SELECT DISTINCT(deptno) from emp); file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (9 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions DEPTNO 40 IN clause provides multiple records available in EMP table. And NOT clause makes result and returns value to DEPT table. Here deptno 40 is not available in EMP table, so result is 40. In nested subquery there can be used more than one nesting or more than one table to evaluate appropriate result. Example: Find out the products which has been sold to 'Ivan Bayross'. INPUT: SQL> SELECT sod.product_no, p.description FROM sales_order_details sod, product_master p WHERE sod.product_no=p.product_no AND sod.s_order_no IN (SELECT so.s_order_no FROM sales_order so, client_master c WHERE so.client_no=c.client_no AND c.client_no IN (SELECT client_no FROM client_master WHERE name LIKE 'Ivan Bayross') ); OUTPUT: PRODUC DESCRIPTION P00001 1.44 Floppies P07885 CD Drive P07965 540 HDD file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (10 of 11) [6/26/02 12:12:31 PM]
0306. Join, Subquery, Built in functions P03453 Monitors P06734 Mouse 5 rows selected. Example:2 Find the product_no and description of moving product SQL> SELECT product_no, description FROM product_master WHERE product_no IN (SELECT DISTINCT(product_no) FROM sales_order_details); Example:3 Find the product_no and description of non-moving product SQL> SELECT product_no, description FROM product_master WHERE product_no NOT IN (SELECT DISTINCT(product_no) FROM sales_order_details ); file:///D|/JigneshDhol/Oracle/0306.%20Join,%20Subquery,%20Built%20in%20functions.htm (11 of 11) [6/26/02 12:12:31 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm Top:1 Creating and Altering Tables (with constraints) Creating Tables A) Simple Create Table Command Syntax: CREATE TABLE tablename (ColumnName DataType(Size) Column Level Constraint, ColumnName DataType(Size), Table level Constraints); Example:1 Column Name Data Type Size client_No Varchar2 6 name Varchar2 20 address1 Varchar2 30 address2 Varchar2 30 city Varchar2 15 state Varchar2 15 file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (1 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm pincode Number 6 remarks Varchar2 60 bal_due Number 10,2 CREATE TABLE client_master ( client_no varchar2(6), name varchar2(20), address1 varchar2(30), address2 varchar2(30), city varchar2(15), state varchar2(15), pincode Number(6), remarks varchar2(60), bal_due number(10,2)); B) Creating Table from existing Table Command Syntax: CREATE TABLE tablename [(columnname, columnname)] AS SELECT columnname, columnname FROM tablename; Example:2 Create table supplier_master from table client_master, select all fields and rename client_no with supplier_no and name with supplier_name. CREATE TABLE supplier_master (supplier_no, supplier_name, address1, address2, city, state, pincode, remarks) AS SELECT client_no, name, address1, address2, city, state, pincode, remarks FROM client_master; NOTE: If the Source table from which the Target table is being created, has records in it then the Target table is populated with these records as well. To eliminate this use where condition which is false in its sense, that will create structure of table only. Below is a example of that, (Where 1=2 is false condition) CREATE TABLE supplier_master (supplier_no, supplier_name, file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (2 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm address1, address2, city, state, pincode, remarks) AS SELECT client_no, name, address1, address2, city, state, pincode, remarks FROM client_master WHERE 1=2; Constraints in Create Tables The Create table statement enforce you several different kinds of constraints on a table: candidate key, primary key, foreign key, check conditions. A constraint clause can contain a single column or a group of columns in a table. It maintains integrity on your database. The more constraint you add on table less you work in application. On other hand more constraint on a table, slower data to update in table. There are two ways to define constraints: as part of column definition ( a column constraint) , at the end of Create Table command ( a table level constraint) CANDIDATE KEY Candidate key is a combination of one or more columns, the value of which uniquely identify each row of a table. The following listing shows the creation of a UNIQUE constraint for the BILL table. CREATE TABLE bill (bill_no number(6), bill_date date, client_no varchar2(6), remarks varchar2(60), Constraint uq_bill UNIQUE (bill_no, bill_date)); The key of this table is the combination of bill_no and bill_date. Notice that both the column are declared as NOT NULL. This feature allows you to prevent data from being entered into the table without certain columns having data into them. PRIMARY KEY file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (3 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm You can have only one primary key and a primary key column does not contain NULL values, CREATE TABLE bill (bill_no number(6), bill_date date, client_no varchar2(6), remarks varchar2(60), Constraint pk_bill PRIMARY KEY (bill_no, bill_date)); Above create table has the same effect as the previous one, except the you can have several UNIQUE constraint but only one PRIMARY KEY constraint. For, Single-column primary or candidate keys, you can define the key on the column with a column name constraint instead of a table constraint: CREATE TABLE client_master (client_no varchar2(6) PRIMARY KEY, name varchar2(20), address1 varchar2(20)); In this case client_no is primary key, and oracle will generate name for the PRIMARY KEY constraint. FOREIGN KEY A foreign key is a combination of columns with values based on the primary key values from another table. A Foreign key constraint, also known as a referential integrity constraint, specifies that the value of foreign key correspond to actual value of the primary key in the other table. Example: Create table Sales_Order_Details with primary key as s_order_no and product_no and foreign key as s_order_no referencing column s_order_no in the sales_order table. FOREIGN KEY as a column constraint file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (4 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm CREATE TABLE sales_order_details (s_order_no varchar2(6) REFERENCES sales_order, product_no varchar2(6), qty_ordered number(8), qty_disp number(8), product_rate number(8,2), PRIMARY KEY (s_order_no, product_no)); FOREIGN KEY as a table constraint CREATE TABLE sales_order_details (s_order_no varchar2(6), product_no varchar2(6), qty_ordered number(8), qty_disp number(8), product_rate number(8,2), PRIMARY KEY (s_order_no, product_no), FOREIGN KEY (s_order_no) REFERENCES sales_order); You can refer to a primary key or unique key, even in a same table. However, you can not refer to a table in a remote database in the reference clause. You can use table form instead of the column form to specify foreign keys with multiple columns. CHECK CONSTRAINT Many column must have values that are within a certain range or that satisfy certain conditions. With a CHECK constraint, you can specify an expression that must always be true for every row in a table. Never use CHECK constraint if the constraint can be defined using the not null, primary key or foreign key constraint. following are a few examples of appropriate CHECK constraints: • a CHECK constraint on the client_no column of the client_master so that no client_no value starts with 'C'. • a CHECK constant on name column of the client_master so that the name is entered in upper case. • a CHECK constraint on the city column of the client_master so that only the cities "BOMBAY", "NEWDELHI", "MADRAS", and file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (5 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm "CULCATTA" are allowed. CREATE TABLE client_master (client_no varchar2(6) CONSTRAINT ck_clientno CHECK (client_no like 'C%'), name varchar2(20) CONSTRAINT ck_cname CHECK (name=upper(name)), address1 varchar2(30), address2 varchar2(30), city varchar2(15) CONSTRAINT ck_city CHECK (city IN ('NEWDELHI', 'BOMBAY', 'MADRAS', 'CULCATTA')), state varchar2(15), pincode number(6), remarks varchar2(60), bal_due number(10,2)); Restriction on CHECK Constraint: A CHECK integrity constraint requires that a condition be true or unknown for every row of the table. If a statement causes the condition to evaluate to false; the statement is rolled back. The condition of a CHECK constraint has the following limitations; • The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated. • The condition can not contain subqueries or sequences. • The condition can not include the SYSDATE, UID, USER or USERENV SQL functions. NOT NULL CONSTRAINT NOT NULL constraint can be used to restrict field for having no data. This means field having NOT NULL constraint must be entered by the user. Example of the same is given below. CREATE TABLE sales_order_details (s_order_no varchar2(6), product_no varchar2(6), qty_ordered number(8) NOT NULL, qty_disp number(8), product_rate number(8,2) NOT NULL, PRIMARY KEY file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (6 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (s_order_no, product_no)); Altering Tables Tables can be altered in one of three ways : By adding a column to an existing table - by changing column definition - or by dropping column of table. Adding a column is straightforward, and similar to creating table. • ALTER TABLE supplier_master ADD (state varchar2(20), country varchar2(20)); You can drop column in Oracle 8i, for the you have to type simple command like, • ALTER TABLE supplier_master DROP column state; To modify column several way are defined as under, • ALTER TABLE supplier_master ADD PRIMARY KEY (supplier_no); • ALTER TABLE supplier_master MODIFY (state varchar2(30)); Some other examples for alter table commands are given under, that can be used alternatively as an when required. • ALTER TABLE supplier_master DROP PRIMARY KEY; (this command drops the primary key constraint from supplier_master) • ALTER TABLE sales_order_details DROP CONSTRAINT product_fkey; (this command drop foreign key constraint on column product_no in table sales_order_details) • ALTER TABLE sales_order_details ADD CONSTRAINT file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (7 of 11) [6/26/02 12:13:56 PM]
file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm order_fkey FOREIGN KEY (s_order_no) REFERENCES sales_order MODIFY (qty_ordered number(8) NOT NULL); RULES for Adding or Modifying a Column These are rules for adding a column to table 1. You may add a column at any time if NOT NULL isn't specified. 2. You may add a NOT NULL column in three steps: o Add a column without NOT NULL specified. o Fill every row in that column with data. o Modify the column to be NOT NULL. These are rules for modifying a column to table 1. You can increase a character column's width at any time. 2. You can increase the number of digits in a NUMBER column at any time. 3. You can increase or decrease the number of decimal places in a NUMBER column at any time. In case only if whole column to be modify is NULL, you can 1. You can change the Column's DataType. 2. You can decrease a character column's width. 3. You can decrease the number of digits in a NUMBER column. file:///D|/JigneshDhol/Oracle/0302.%20Creating%20and%20Altering%20tables.htm (8 of 11) [6/26/02 12:13:56 PM]