Structured Query Language

 Structured Query Language

SQL stands for “Structured Query Language.” The Structured Query Language is a relational database language. By itself, SQL does not make a DBMS. SQL is a medium which is used to communicate to the DBMS. SQL commands consist of English-like statements which are used to query, insert, update, and delete data. English-like statements mean that SQL commands resemble English language sentences in their construction and use and therefore are easy to learn and understand. SQL is referred to as nonprocedural database language. Here nonprocedural means that, when we want to retrieve data from the database it is enough to tell SQL what data to be retrieved, rather than how to retrieve it. The DBMS will take care of locating the information in the database. Commercial database management systems allow SQL to be used in two distinct ways. First, SQL commands can be typed at the command line directly. The DBMS interprets and processes the SQL commands immediately, and the results are displayed. This method of SQL processing is called interactive SQL. The second method is called programmatic SQL. Here, SQL statements are embedded in a host language such as COBOL, FORTRAN, C, etc. SQL needs a host language because SQL is not a really complete computer programming language as such because it has no statements or constructs that allow branch or loop. The host language provides the necessary looping and branching structures and the interface with the user, while SQL provides the statements to communicate with the DBMS. Some of the features of SQL are: – SQL is a language used to interact with the database. – SQL is a data access language. – SQL is based on relational tuple calculus. – SQL is a standard relational database management language. – The first commercial DBMS that supported SQL was Oracle in 1979. – SQL is a “nonprocedural” or “declarative” language. 4.2 History of SQL Standard The origin of the SQL language date back to a research project conducted by IBM at their research laboratories in San Jose, California in the early 1970s. The aim of the project was to develop an experimental RDBMS which would eventually lead to a marketable product. At that time, there was a lot of interest in the relational model for databases at the academic level, in conferences and seminars. IBM, which already had a large share of the commercial database market with hierarchical and network model DBMSs, realized that the relational model would dominate the future database products. The project at IBM’s San Jose labs was started in 1974 and was named System R. A language called SEQUEL (Structured English QUEry Language) was chosen as the relational database language for System R. A version of SEQUEL was developed at the IBM San Jose research facilities and tested with college students. In November 1976, specifications for SEQUEL2 were published. In 1980 minor revisions were made to SEQUEL, and it was renamed “SQL.” SEQUEL was renamed to SQL because the name SEQUEL had already been used for hardware product. In order to avoid confusion and legal problems SEQUEL was renamed to SQL. In the first phase of the System R project, researchers concentrated on developing a basic version of the RDBMS. The main aim at this stage was to verify that the theories of the relational model could be translated into a working, commercially viable product. This first phase was successfully completed by the end of 1975, and resulted in a single-user DBMS based on the relational model. The System R project was completed in 1979. The theoretical work of the System R project resulted in the development and release of IBM’s first commercial relational database management system in 1981. The product was called SQL/DS (Structured Query Language/Data Store) and ran under the DOS/VSE operating system environment. Two years later, IBM announced a version of SQL/DS for VM/CMS operating system.In 1983, IBM released a second SQL-based RDBMS called DB2, which ran under the MVS operating system. DB2 quickly gained widespread popularity and even today, versions of DB2 form the basis of many database systems found in large corporate data-centers. During the development of System R and SQL/DS, other companies were also at work creating their own relational database management systems. Some of them, Oracle being an example, even implemented SQL as the relational database language for their DBMSs concurrently with IBM. Later on, SQL language was standardized by ANSI and ISO. The ANSI SQL standards were first published in 1986 and updated in 1989, 1992, and 1999.The main advantages of standardized language are given below. 1. Reduced training cost 2. Enhanced productivity 3. Application portability Application portability means applications can be moved from machine to machine when each machine uses SQL. 4. Application longevity A standard language tends to remain so for a long time, hence there will be little pressure to rewrite old applications. 5. Reduced dependence on a single vendor SQL language development is given in a nutshell below: 1. In 1970 E.F. Codd of IBM released a paper “A relational model of data for large shared data banks.” IBM started the project System R to demonstrate the feasibility of implementing the relational model in a database management system. The language used in system R project was SEQUEL. SEQUEL was renamed SQL during the project, which took place from 1974 to 1979. 2. The first commercial RDBMS from IBM was SQL/DS. It was available in 1981. 3. Oracle from relational software (now Oracle corporation) was on the market before SQL/DS, i.e., 1979. 4. Other products included INGRES from relational Technology Sybase from Sybase, Inc. (1986), DG/SQL from Data General Corporation (1984). 4.3 Commands in SQL SQL commands can be classified in to three types: 1. Data Definition Language commands (DDL) 2. Data Manipulation Language commands (DML) 3. Data Control Language commands (DCL)

DDL DDL commands are used to define a database, including creating, altering, and dropping tables and establishing constraints. DML DML commands are used to maintain and query a database, including updating, inserting, modifying, and querying data. DCL DCL commands are used to control a database including administering privileges and saving of data. DCL commands are used to determine whether a user is allowed to carry out a particular operation or not. The ANSI standard groups these commands as being part of the DDL.

 Datatypes in SQL In relational model the data are stored in the form of tables. A table is composed of rows and columns. When we create a table we must specify a datatype for each of its columns. These datatypes define the domain of values that each column can take. Oracle provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes. Some of the built-in datatypes are string datatype to store characters, number datatype to store numerical value, and date and time datatype to store when the event happened (history, date of birth, etc.). 4.4 Datatypes in SQL 115 STRING In string we have CHAR and VARCHAR datatypes. Character datatype store data which are words and free-form text, in the database character set. CHAR Datatype The CHAR datatype specifies a fixed-length character string. The syntax of CHAR datatype declaration is: CHAR (n) – Fixed length character data, “n” characters long. Here “n” specifies the character length. If we insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If we try to insert a value that is too long for the column then Oracle returns error message. VARCHAR2 Datatype The VARCHAR2 datatype specifies a variable-length character string. The syntax of VARCHAR2 datatype declaration is: VARCHAR2 (n) – Variable length character of “n” length. Here “n” specifies the character length. VARCHAR vs. VARCHAR2 The VARCHAR datatype behaves like VARCHAR2 datatype in the current version of Oracle. In order to justify the above statement, let us create a table CHAMPION, which refers to Wimbledon Champions. The attributes of the table CHAMPION are Name, Nation, Year (the year in which the sportsman has won the title). For our example, let us use the datatype VARCHAR for the attribute Name and VARCHAR2 for the datatype Nation. The SQL command to create CHAMPION is shown in Fig. 4.1. Now let us try to see the description of the table. The description of the table is shown in Fig. 4.2. From Fig. 4.2, it is clear that both name and nation are stored as VARCHAR2(12). This means that VARCHAR datatype in the Oracle 8i version behaves the same as VARCHAR2. NUMBER Datatype The NUMBER datatype stores zero, positive, and negative fixed and floating point numbers. 116 4 Structured Query Language Fig. 4.1. CHAR and VARCHAR2 datatype Fig. 4.2. Table description The syntax to store fixed-point number is NUMBER (p, q) where “p” is the total number of digits and “q” is the number of digits to the right of decimal point. The syntax to specify an integer is NUMBER (p). DATE Datatype The DATE datatype is used to store the date and time information. For each DATE value, Oracle stores the century, year, month, date, hour, minute, and second information. The ANSI date literal contains no time portion, and must be specified in YYYY-MM-DD format where Y stands for Year, M for month, and D for date. TIME STAMP Datatype The TIME STAMP datatype is used to store both date and time. It stores the year, month, and day of the DATE datatype, and also hour, minute, and second values. LOB Datatype Multimedia data like sound, picture, and video need more storage space. The LOB datatypes such as BLOB, CLOB, and BFILE allows us to store large block of data. 4.5 Data Definition Language (DDL) 117 BLOB Datatype The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to 4 GB of binary data. CLOB Datatype The CLOB datatype can store up to 4 GB of character data in the database. BFILE Datatype The BFILE datatype stores unstructured binary data in operating system files outside the database. A BFILE can store up to 4 GB of data. 4.5 Data Definition Language (DDL) The Data Definition Language is – Used to define schemas, relations, and other database structures – Also used to update these structures as the database evolves Examples of Structure Created by DDL The different structures that are created by DDL are Tables, Views, Sequences, Triggers, Indexes, etc. 1. Tables The main features of table are: – It is a relation that is used to store records of related data. It is a logical structure maintained by the database manager. – It is made up of columns and rows. – At the intersection of every column and row there is a specific data item called a value. – A base table is created with the CREATE TABLE statement and is used to hold persistent user data. 2. Views The basic concepts of VIEW are: – It is a stored SQL query used as a “Virtual table.” – It provides an alternative way of looking at the data in one or more tables. – It is a named specification of a result table. The specification is a SELECT statement that is executed whenever the view is referenced in an SQL statement. Consider a view to have columns and rows just like a base table. For retrieval, all views can be used just like base tables. 118 4 Structured Query Language – When the column of a view is directly derived from the column of a base table, that column inherits any constraints that apply to the column of the base table. For example, if a view includes a foreign key of its base table, INSERT and UPDATE operations using that view are subject to the same referential constraints as the base table. Also, if the base table of a view is a parent table, DELETE and UPDATE operations using that view are subject to the same rule as DELETE and UPDATE operations on the base table. 3. Sequences – A sequence is an integer that varies by a given constant value. Typically used for unique ID assignment 4. Triggers – Trigger automatically executes certain commands when given conditions are met. 5. Indexes – Indexes are basically used for performance tuning. Indexes play a crucial role in fast data retrieval. Create Table Command – The CREATE TABLE command is used to implement the schemas of individual relations. Steps in Table Creation 1. Identify datatypes for attributes 2. Identify columns that can and cannot be null 3. Identify columns that must be unique 4. Identify primary key–foreign key mates 5. Determine default values 6. Identify constraints on columns (domain specifications) 7. Create the table Syntax CREATE TABLE table name (column-name1 data-type-1 [constraint], column-name2 data-type-2 [constraint], column-nameN data-type-N [constraint] ); Example Table See Table 4.1. 4.5 Data Definition Language (DDL) 119 Table 4.1. Peaks of the world Serial Peak Mountain Place Height number range 1 Everest Himalayas Nepal 8,848 2 Godwin Karakoram India 8,611 Austin 3 Kanchenjunga Himalayas Nepal 8,579 Fig. 4.3. Table creation example Syntax to Create the Table The general syntax to create the table is given below. Here the key words are shown in bold and capital letters. CREATE TABLE table name (column name1 data type (size), column name2 data type (size), column name N data type (size)); Example The SQL command to define Table 4.1 is shown in Fig. 4.3. In this example the name of the table is peaks. The table has five columns which are serial number, name of the mountain (peak), height, place where the mountain is situated, range of the mountain. To see the description of the table To see the description of the table we have created we have the command DESC. Here DESC stands for description of the table. The syntax of DESC command is:

The projection operation performs column wise filtering. Specific columns are selected in projection operation. Syntax of PROJECTION Operation SELECT column name1, column name2, Column name N FROM table name; If all the columns of the table are selected, then it cannot be considered as PROJECTION. The SQL command to perform PROJECTION operation on the relation PEAKS and the corresponding results are shown in Fig. 4.9. From Fig. 4.9, it is clear that only three columns are selected in the result, even though there are five columns in the Table 4.1. SELECTION and PROJECTION Operation We can perform both selection and projection operation in a relation. If we combine selection and projection operation means naturally we are restricting the number of rows and the columns of the relation.SELECT column name1, column name 2. . . . . column name N FROM table name WHERE condition;Aggregate Functions SQL provides seven built-in functions to facilitate query processing. The seven built-in functions are COUNT, MAX, MIN, SUM, AVG, STDDEV, and VARIANCE. The uses of the built-in functions are shown in Table 4.2. 4.8.1 COUNT Function The built-in function returns the number of rows of the table. There are variations of COUNT function. First let us consider COUNT (*) function. In order to understand the COUNT (*) function consider the relation PERSON SKILL as shown in Table 4.3, the relation PERSON has only two columns, name of the person and skills associated with the person. It is to be noted that some persons may have more than one skill and some persons may not have any skills. From Table 4.3, we can observe that the table PERSON SKILL has six rows and two columns and the person Ashok has more than one skill and Sam has no skill hence a NULL is inserted against Sam. (A) COUNT (*) Function The syntax of Count (*) function is: SELECT COUNT (*) FROM table name; Table 4.2. Built-in functions Serial Built-in Use number function 1 COUNT to count the number of rows of the relation 2 MAX to find the maximum value of the attribute (column) 3 MIN to find the minimum value of the attribute 4 SUM to find the sum of values of the attribute provided the datatype of the attribute is number 5 AVG to find the average of n values, ignoring null values 6 STDDEV standard deviation of n values ignoring null values 7 VARIANCE variance of n values ignoring null values.

The UNIQUE constraint imposes that every value in a column or set of columns be unique. It means that no two rows of a table can have duplicate values in a specified column or set of columns. Example In order to understand unique constraint, let us create the table CELLPHONE, which has three attributes. The three attributes are model of the cellphone, make which refers to manufacturer, and the price. The relation CELLPHONE is created as shown in Fig. 4.52 with unique constraint on model. When a unique constraint is imposed on the attribute model, then no two models should have same number. The values are inserted into the table CELLPHONE. The resulting tables after inserting the values are shown in Fig. 4.53. From Fig. 4.53, we can observe that the table CELLPHONE has three rows. Case 1: Now let us try to insert a row in the relation CELLPHONE by violating the UNIQUE constraint, i.e., we are trying to insert a row with model number 1100 which already exists. The insertion and the corresponding result are shown in Fig. 4.54. From this figure, we can observe that there is an error message “unique constraint (SCOTT.SYS C00820) violated.” The reason for getting this error message is we tried to enter the model (1100) which exists already in the CELLPHONE relation as shown in Fig. 4.53. Case 2: Insertion of NULL Value to the Model Attribute. Let us try to insert a null value to the attribute model. The SQL command to insert a null value to the attribute model and the corresponding result are shown in Fig. 4.55. Fig. 4.52. Unique constraint on a column 150 4 Structured Query Language Fig. 4.53. Values inserted into the table CELLPHONE Fig. 4.54. Violation of UNIQUE constraint Difference Between NOT NULL and UNIQUE Constraint The unique constraint accepts NULL value as shown in Fig. 4.55, whereas the NOT NULL constraint will not accept NULL values. Note NOT NULL constraint accepts duplicate values, whereas UNIQUE constraint will not accept null values. Moreover when a UNIQUE constraint is imposed on an attribute means that attribute can accept NULL values. Whereas NOT NULL constraint will not accept NULL values. 4.12 Imposition of Constraints 151 Fig. 4.55. Insertion of NULL value into CELLPHONE 4.12.3 Primary Key Constraint When an attribute or set of attributes is declared as the primary key, then the attribute will not accept NULL value moreover it will not accept duplicate values. It is to be noted that “only one primary key can be defined for each table.” Example Consider the relation EMPLOYEE with the attributes ID which refers to Employee identity, NAME of the employee, and SALARY of the employee. Each employee will have unique ID hence ID is declared as the primary key as shown in Fig. 4.56. From Fig. 4.56, it is clear that the attribute employee ID is declared as the primary key. Case 1: Insertion of NULL Value to the Primary Key Attribute. It is to be noted that the primary key will not take any NULL value. This is called entity integrity. Now let us try to insert a NULL value to the employee ID in the SQL syntax, and the corresponding output is shown in Fig. 4.57. From Fig. 4.57, it is evident that an attribute or set of attributes declared as primary key will not accept NULL values. Case 2: Insertion of Duplicate Values into an Attribute Declared as Primary Key. 152 4 Structured Query Language Fig. 4.56. Attribute declared as primary key Fig. 4.57. Inserting NULL value into primary key attribute When an attribute is declared as primary key, all the values of the attribute should be UNIQUE. The primary key attribute will not accept duplicate values. Let us try to insert duplicate values to the attribute employee ID which is declared as primary key. The SQL command and the corresponding output are shown in Fig. 4.58. We got an error message in Fig. 4.54, because we have tried to insert the employee ID e101 twice. From this we can understand that when an attribute is declared as primary key, the values of the attribute should be UNIQUE. 4.12 Imposition of Constraints 153 Fig. 4.58. Insertion of duplicate values to an attribute declared as primary key Difference Between UNIQUE and NOTNULL Constraint The difference between UNIQUE and NOTNULL constraint is given in the tabular form as NOTNULL constraint UNIQUE constraint an attribute declared as NOTNULL will not accept NULL values an attribute declared as UNIQUE can accept NULL values an attribute declared as NOTNULL will accept duplicate values an attribute declared as UNIQUE will not accept duplicate values Difference Between UNIQUE and PRIMARY KEY Constraint The difference between UNIQUE and PRIMARY KEY is given in tabular form as 154 4 Structured Query Language Fig. 4.59. Check constraint on an attribute PRIMARY KEY constraint UNIQUE constraint an attribute declared as primary key will not accept NULL values an attribute declared as UNIQUE will accept NULL values only one PRIMARY KEY can be defined for each table more than one UNIQUE constraint can be defined for each table 4.12.4 CHECK Constraint CHECK constraint is added to the declaration of the attribute. The CHECK constraint may use the name of the attribute or any other relation or attribute name may in a subquery. Attribute value check is checked only when the value of the attribute is inserted or updated. Syntax of CHECK Constraint In order to understand check constraint, consider the relation VOTERS. In India, only those who have completed the age of 19 are eligible to vote. Let us impose this constraint on age in our relation VOTERS. The VOTERS relation has the attributes name, which refers to the name of the voter, age of the voter, address of the voter. The creation of the table VOTERS with CHECK constraint imposed on age is shown in Fig. 4.59. From Fig. 4.59, we can observe that CHECK constraint is imposed on the attribute age. Case 1: Insertion of Data Without Violating the Constraint. Let us try to insert the values into the table VOTERS without violating the constraint, that is the age of the voter is greater than 19. The SQL syntax and the corresponding output are shown in Fig. 4.60. From this figure, it is evident that the data are successfully inserted into the table VOTERS because the age of the voter is greater than 19. 4.12 Imposition of Constraints 155 Fig. 4.60. Data insertion without violating the constraint Case 2: Insertion of Data into the Table VOTERS by Violating the CHECK Constraint. Now let us try to insert data into the table VOTERS by violating the CHECK constraint, that is inserting the record of the voter with age less than 19. The SQL command to insert the data and the corresponding output are shown in Fig. 4.61. From Fig. 4.61, we can observe that we try to insert a value which violates the CHECK constraint, we get error message. Case 3: CHECK Constraint During Updation of Record. The content of the VOTER table is given in Fig. 4.62. For simplicity, there is only one record in the VOTERS table. Now let us try to update the record by changing the age of the voter to less than 19, as shown in Fig. 4.63. From Fig. 4.63, we can observe that it is not possible to update the record by violating the CHECK constraint. 4.12.5 Referential Integrity Constraint According to referential integrity constraint, when a foreign key in one relation references primary key in another relation, the foreign key value must 156 4 Structured Query Language Fig. 4.61. Data insertion by violating the CHECK constraint Fig. 4.62. The content of VOTERS table match with the primary key value. In other words, the referential integrity says “pointed to” information must exist. Example In order to understand referential constraint, consider two relation DEPARTMENT and EMPLOYEE. Here the DEPARTMENT relation forms the parent table. The meaning is the DEPARTMENT table contains the primary key. The relation EMPLOYEE forms the child table. The meaning is the relation EMPLOYEE has foreign key which references to primary key in DEPARTMENT table. Figure 4.64 shows parent–child relationship. 4.12 Imposition of Constraints 157 Fig. 4.63. Updation of record voters by violating CHECK constraint Fig. 4.64. Primary key and foreign key relationship In our example, the relation DEPARTMENT is the parent table which holds the parent table, and the relation EMPLOYEE forms the child table which has foreign key which references primary key in DEPARTMENT table. It is to be noted that the parent table should be created first, then the child table. DEPARTMENT DeptID Dname Location D100 electrical B D101 civil A D102 computer C EMPLOYEE EID DID Ename E201 D100 Raman E202 D101 Ravi E203 D101 Krishnan 158 4 Structured Query Language Fig. 4.65. DEPARTMENT table Fig. 4.66. EMPLOYEE table The SQL syntax to create the two relations DEPARTMENT and EMPLOYEE with primary key and foreign key constraints is shown in Fig. 4.65 and Fig. 4.66, respectively. Case 1: Now let us try to insert a value into DepartmentID of the employee table which is not in department table. The department relation has only three department IDs D100, D101, D102. Now we are trying to insert D103 in the DID (which stands for department ID) of employee table. The SQL command and the corresponding output are shown in Fig. 4.67. From Fig. 4.67, it is evident that the values are not able to insert into the employee table. The reason for not able to insert value into the employee table is: we have tried to insert the DID (department id) into the employee table (child table) which is not matching with DeptID (department id) of the department table (parent table). In other words the foreign key value in the child table does not match with the primary key value in the parent relation. The referential integrity rule says that the foreign key value should match with the primary key value. Case 2: NULL Value into Foreign Key Attribute. Now let us try to insert a null value into the foreign key attribute. The SQL command and the corresponding output are shown in Fig. 4.68. 4.12 Imposition of Constraints 159 Fig. 4.67. Violation of referential integrity Fig. 4.68. NULL value to the foreign key attribute From Fig. 4.68, it is evident that NULL value cannot be inserted into foreign key attribute unless it matches with the primary key attribute. 4.12.6 ON DELETE CASCADE When the clause ON DELETE CASCADE is included in the child table, and if a row is deleted from the parent table then the corresponding referenced value in the child table will also be deleted. Example Let us consider the DEPARTMENT (parent table) and EMPLOYEE (child table) relation. The employee relation is modified as shown in Fig. 4.69. From this figure, it is clear that we have included the clause ON DELETE CASCADE in the child table. 160 4 Structured Query Language Fig. 4.69. Modified EMPLOYEE relation The content of the table DEPARTMENT and EMPLOYEE are shown below. DEPARTMENT DeptID Dname Location D100 electrical B D101 civil A D102 computer C EMPLOYEE EID DID Ename E201 D100 Raman E202 D101 Ravi E203 D101 Krishnan Now let us try to delete the department “Civil” in the DEPARTMENT table. If we delete the row “civil” in the DEPARTMENT table, what will be the impact in the EMPLOYEE table? First the content of employee table is shown in Fig. 4.70. The number of tuples in the EMPLOYEE relation is three. Now we are going to delete the department “civil” in the table DEPARTMENT. The SQL command and the corresponding output are shown in Fig. 4.71. Now let us see the impact of deleting the record “civil” in the child table which is EMPLOYEE in our case. The modified table EMPLOYEE is shown in Fig. 4.72. By carefully analyzing the Figs. 4.71 and 4.72, we can observe that the record “civil” in the child table (employee) being deleted. 4.12 Imposition of Constraints 161 Fig. 4.70. EMPLOYEE table (child table) before deletion of record in parent table Fig. 4.71. DEPARTMENT table without “civil” department If ON DELETE CASCADE clause is included in the child table means whatever record deleted in the parent table will be deleted in the child table. 4.12.7 ON DELETE SET NULL If ON DELETE SET NULL clause is include in the child table means, whenever a row in the parent table is deleted, then the corresponding referenced value in the child table will be set null. 162 4 Structured Query Language Fig. 4.72. Modified EMPLOYEE table Fig. 4.73. Modified employee table definition Example Let us consider the parent table as DEPARTMENT and the child table as EMPLOYEE as before. The child table is created with ON DELETE SET NULL as shown in Fig. 4.73. The EMPLOYEE table before modification is shown below. EID DID Ename E201 D100 Raman E202 D101 Ravi E203 D101 Krishnan 4.13 Join Operation 163 Fig. 4.74. Modified table DEPARTMENT Fig. 4.75. Modified child table (EMPLOYEE) Now modify the table DEPARTMENT by deleting the “electrical” department record. The SQL command to delete the record “electrical” and the corresponding output are shown in Fig. 4.74. The impact of deleting the record “electrical” in parent table DEPARTMENT on the child table EMPLOYEE is shown in Fig. 4.75. From Fig. 4.75, we can observe that a NULL value is there corresponding to the ID of the “electrical” department. This is due to inclusion of the clause ON DELETE NULL in the child table (EMPLOYEE). 4.13 Join Operation Join operation is used to retrieve data from more than one table. Before proceeding to JOIN operation let us discuss first the Cartesian product. Cartesian product with suitable selection and projection operation forms different types of join. 164 4 Structured Query Language Cartesian Product If we have two tables A and B, then Cartesian product combines all rows in the table A with all rows in the table B. If n1 is the number of rows in the table A and n2 is the number of rows in the table B. Then the Cartesian product between A and B will have n1 × n2 rows. Example In order to understand Cartesian product, let us consider two relations doctor and nurse. The relation doctor has the attribute ID which refers to identity of the doctor, name and department. Similarly, the relation nurse has three attributes NID, which refers to nurse identity, name and department. The doctor relation is shown in Fig. 4.76. Similarly the nurse relation is shown in Fig. 4.77. Fig. 4.76. DOCTOR relation Fig. 4.77. NURSE relation 4.13 Join Operation 165 From Figs. 4.76 and 4.77 we can observe that the number of rows in doctor and nurse relation is 4. Now let us try to find the Cartesian product between the two relations doctor and nurse. The Cartesian product should return 4×3 = 12 rows. The SQL command to perform Cartesian product between the two relations doctor and nurse and the corresponding output are shown in Fig. 4.78. From this figure, it is evident that the Cartesian product between two relations has 12 tuples (rows). 4.13.1 Equijoin In equijoin, the join condition is based on equality between values in the common columns. Moreover the common columns appear redundantly in the result. Equijoins are also called as simple joins or inner joins. The equijoin between the two relations doctor and nurse (The relations doctor and nurse are shown in Figs. 4.76 and 4.77, respectively) is shown in Fig. 4.79. Fig. 4.78. Cartesian product between the relations doctor and nurse Fig. 4.79. Equijoin between doctor and nurse relation 166 4 Structured Query Language From Fig. 4.79, it is evident that the join condition is equality condition on the attribute department. We can also observe that the common columns appear redundantly in the result. 4.14 Set Operations The UNION, INTERSECTION, and the MINUS (Difference) operations are considered as SET operations. Out of these three set operations, UNION, INTERSECTION operations are commutative, whereas MINUS (Difference) operation is not commutative. All the three operations are binary operations. The relations that we are going to consider for UNION, DIFFERENCE, and MINUS operations are IBM DESKTOP and DELL DESKTOP as shown in Figs. 4.80 and 4.81, respectively. 4.14.1 UNION Operation If we have two relations R and S then the set UNION operation contains tuples that either occurs in R or S or both. Case 1: UNION command. The union of two relations IBM DESKTOP, DELL DESKTOP is given in From.