Page 1 :
Unit 2 Relational Data Model, , 2.1 Concept of RDBMS:, What is RDBMS?, RDBMS stands for Relational Database Management Systems. All modern database, management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and, Microsoft Access are based on RDBMS. It is called Relational Data Base Management, System (RDBMS) because it is based on relational model introduced by E.F. Codd . The, RDBMS database uses tables to store data. A table is a collection of related data entries and, contains rows and columns to store data. A table is the simplest example of data storage in, RDBMS., Let's see the example of student table., , ID, , Name, , AGE, , COURSE, , 1, , Ajeet, , 24, , B.Tech, , 2, , aryan, , 20, , C.A, , 3, , Mahesh, , 21, , BCA, , 4, , Ratan, , 22, , MCA, , 5, , Vimal, , 26, , BSC, , 1

Page 2 :
➢ E F CODD’S RULES IN RDBMS :, , ▪, , The system has to qualify as a relational, a database and a management, system”., , ▪, , A system to qualify as a relational DBMS, must utilize the facilities to manage, database., , 1. Information Rule, “All the information including metadata (data about data ) has to be represented as, stored data in cells of tables”., ▪, , Also says that the rows and columns have to be strictly unordered., , ▪, , All the data must be in a table format., 2. Guaranteed Access Rule, “Each unique piece of data should be accessible by the combination of table name,, , primary key and attribute”., ▪, , It simply means that All the data has to be accessible., , ▪, , Accessed by: table name + primary key (Row) + attribute (column) and not by other, means like pointers., 3. Systematic Treatment Of Null Values, “RDBMS should be capable of allowing each attribute to remain as null, should also, , support the representation of missing information and inapplicable information”., ▪, , Has to be handled consistently, cannot be zero or blank., , ▪, , Primary key cannot be Null., , ▪, , Null can be missing data, data that is not applicable or also no data., , ▪, , Can Used when data does not exist or when data should not be used for the particular, case., , 2

Page 3 :
4. Active Online Catalog Based On The Relational Model, “Catalog called as database dictionary should have the description of the, database”., ▪, , Data dictionary should be stored as relational tables and accessible through the regular, data access language., , ▪, , The same query language is to be used on the catalog as on the application database., , 5. Comprehensive Data Sub-Language Rule, “One well defined language having linear syntax has to be provided to provide all ways of, access to the data”., ▪, , Example is SQL, , ▪, , It supports data definition, data manipulation, transaction management and integrity, constraints., , ▪, , It becomes a violation to the rules if database allows the access to data with no help,, the language has to be used by means of some or the other application., , 6. View Updating Rule, “All views that can be theoretically updated must be updated by the system”., ▪, , View is nothing but the virtual table which is temporarily derived from the base, tables., , 7. Relational level Operation Rule (High-Level Insert, Update And Delete Rule), “The system must support Insert, Update and Delete operations at each level of relations”., ▪, , They should also support union, intersection and minus operations., , 8. Physical Data Independence Rule, “The physical storage of the data should never matter to the system”., ▪, , Say for example, if any file supporting table was renamed or moved from one disk to, other then it should not effect the application., , ▪, , Change in physical structure should never have any effect on how actually the data is, being accessed., , 3

Page 4 :
9.Logical Data Independence Rule, “If there is change in the logical structure or table structure of the database then the user view, of the data must not be changed implemented through views”., ▪, , In simple words, the logical data should not be dependent upon its application, view(user view)., , Say, if a table has been split into two tables a new view should give the result as the join of, the two tables., , 10. Integrity Independence Rule, “The database must be able to enforce its own integrity rather using other programs”., ▪, , The integrity rules filters to allow correct data, stored in data dictionary., , ▪, , Key and check constraints, triggers should be stored in data dictionary., , ▪, , Makes RDBMS independent of front-end., , 11. Distribution Independence Rule, A database should work properly irrespective of its distribution across a network” ., ▪, , It forms the base for distributed database., , ▪, , The distribution of portion of the database to various locations should be invisible to, the end-user of the database., , 12. Non-Subversion Rule, “If low level access to the records is provided to a system by using the interface, then neither, it should be able to subvert ( undermine the authority) nor bypass integrity rules to change the, data”., ▪, , It can be achieved by locking or encryption., , So, these were the 12 codd’s rule which can be applied on any database that has the capability, of managing the stored data using relational capabilities., , 2.2 Key Concept:, Keys are very important part of Relational database model. They are used to establish and, identify relationships between tables and also to uniquely identify any record or row of data, inside a table., , 4

Page 5 :
A Key can be a single attribute or a group of attributes, where the combination may act as a, key., Let's take a simple Student table, with fields student_id, name, phone and age., , Exam_Id, , Student_ID, , Stud_name, , Phone_no, , Age, , 101, , 1, , A, , 6423678951, , 20, , 102, , 2, , B, , 8234567892, , 23, , 103, , 3, , C, , 7253647895, , 21, , 104, , 4, , D, , 8795462314, , 22, , •, , Super Key:, , Super Key is defined as a set of attributes within a table that can uniquely identify each, record within a table. Super Key is a superset of Candidate key., In the table defined above super key would include student_id, (student_id, name), phone etc., Confused? The first one is pretty simple as student_id is unique for every row of data, hence, it can be used to identity each row uniquely., Next comes, (student_id, name), now name of two students can be same, but, their student_id can't be same hence this combination can also be a key., Similarly, phone number for every student will be unique, hence again, phone can also be a, key., So they all are super keys., •, , Candidate Key:, , Candidate keys are defined as the minimal set of fields which can uniquely identify each, record in a table. It is an attribute or a set of attributes that can act as a Primary Key for a, table to uniquely identify each record in that table. There can be more than one candidate key., 5

Page 6 :
In our example, student_id and phone both are candidate keys for table Student., •, , A candidate key can never be NULL or empty. And its value should be unique., , •, , There can be more than one candidate keys for a table., , •, , A candidate key can be a combination of more than one columns (attributes)., , •, , Primary Key:, , Primary key is a candidate key that is most appropriate to become the main key for any table., It is a key that can uniquely identify each record in a table., , For the table Student we can make the student_id column as the primary key., , •, , Foreign Key:, It is combination of attributes in a relation whose value matches with a primary key in, another relation., Consider a table having examination information as shown below:, , 6

Page 7 :
•, , Exam_Id, , Student_ID, , Stud_name, , 101, , 1, , A, , 102, , 2, , B, , 103, , 3, , C, , 104, , 4, , D, , In this table exam_id is the primary key but see that this table has an attribute, “ Student_id” which is the primary key of the student table drawn before it., , 2.3 Normalization:, Normalization is a database design technique that reduces data redundancy and eliminates, undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization, rules divides larger tables into smaller tables and links them using relationships. The purpose, of Normalisation in SQL is to eliminate redundant (repetitive) data and ensure data is stored, logically., 2.3.1 Functional Dependency:, Functional dependency in DBMS, as the name suggests is a relationship between attributes of, a table dependent on each other. Introduced by E. F. Codd, it helps in preventing data, redundancy and gets to know about bad designs., To understand the concept thoroughly, let us consider P is a relation with attributes A and B., Functional Dependency is represented by -> (arrow sign), Then the following will represent the functional dependency between attributes with an arrow, sign −, A -> B, , Above suggests the following:, , 7

Page 8 :
Example, The following is an example that would make it easier to understand functional dependency −, We have a <Department> table with two attributes − DeptId and DeptName., DeptId = Department ID, DeptName = Department Name, , The DeptId is our primary key. Here, DeptId uniquely identifies the DeptName attribute., This is because if you want to know the department name, then at first you need to have, the DeptId., , DeptId, , DeptName, , 001, , Finance, , 002, , Marketing, , 003, , HR, , 8

Page 9 :
Therefore, the above functional dependency between DeptId and DeptName can be, determined as DeptId is functionally dependent on DeptName −, DeptId -> DeptName, , 2.3.2 Normal Forms : 1NF, 2NF,3NF, BCNF, The database normalization process is divided into following the normal form:, •, , First Normal Form (1NF), , •, , Second Normal Form (2NF), , •, , Third Normal Form (3NF), , •, , Boyce-Codd Normal Form (BCNF), 1.First Normal Form (1NF), Each column is unique in 1NF., Example:, Sample Employee table, it displays employees are working with multiple departments., , Employee Age, , Department, , Melvin, , 32, , Marketing, Sales, , Edward, , 45, , Quality Assurance, , Alex, , 36, , Human Resource, , 9

Page 10 :
Employee table following 1NF:, , Employee Age, , Department, , Melvin, , 32, , Marketing, , Melvin, , 32, , Sales, , Edward, , 45, , Quality Assurance, , Alex, , 36, , Human Resource, , 2. Second Normal Form (2NF), The entity should be considered already in 1NF, and all attributes within the entity should, depend solely on the unique identifier of the entity., , Sample Products table:, productID, , product, , Brand, , 1, , Monitor, , Apple, , 2, , Monitor, , Samsung, , 3, , Scanner, , HP, , 4, , Head phone, , JBL, , Product table following 2NF:, Products Category table:, productID, , product, , 1, , Monitor, , 2, , Scanner, , 3, , Head phone, , 10

Page 11 :
Brand table:, , brandID, , brand, , 1, , Apple, , 2, , Samsung, , 3, , HP, , 4, , JBL, , Products Brand table:, pbID, , productID brandID, , 1, , 1, , 1, , 2, , 1, , 2, , 3, , 2, , 3, , 4, , 3, , 4, , 3.Third Normal Form (3NF), The entity should be considered already in 2NF, and no column entry should be dependent on, any other entry (value) other than the key for the table., If such an entity exists, move it outside into a new table., 3NF is achieved, considered as the database is normalized., , Transitive Dependency:, A Transitive Dependency is a type of functional dependency which happens when “t” is, indirectly formed by two functional dependencies. Let’s understand with the following, Transitive Dependency Example., Example:, Company, , CEO, , Age, , Microsoft, , Satya Nadella, , 51, , Google, , Sundar Pichai, , 46, , Alibaba, , Jack Ma, , 54, , 11

Page 12 :
{Company} -> {CEO} (if we know the company, we know its CEO’s name), {CEO } -> {Age} If we know the CEO, we know the Age, Therefore according to the rule of rule of transitive dependency:, { Company} -> {Age} should hold, that makes sense because if we know the company name,, we can know his age., Note: You need to remember that transitive dependency can only occur in a relation of three, or more attributes., , 4 .BCNF(Boyce Codd Normal Form):, •, , BCNF is the advance version of 3NF. It is structured than 3NF., , •, , A table is in BCNF if every functional dependency X → Y, X is the super key of the, table., , For BCNF, the table should be in 3NF, and for every FD, LHS is super key., Example: Let's assume there is a company where employees work in more than one, department., EMPLOYEE table:, EMP_ID, , EMP_COUNTRY, , EMP_DEPT, , DEPT_TYPE, , EMP_DEPT_NO, , 264, , India, , Designing, , D394, , 283, , 264, , India, , Testing, , D394, , 300, , 364, , UK, , Stores, , D283, , 232, , 364, , UK, , Developing, , D283, , 549, , In the above table Functional dependencies are as follows, EMP_ID → EMP_COUNTRY, EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}, Candidate key: {EMP-ID, EMP-DEPT}va, The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys., To convert the given table into BCNF, we decompose it into three tables, , 12

Page 14 :
2.4 Introduction To Structured Query Language:, SQL is Structured Query Language, which is a computer language for storing, manipulating, and retrieving data stored in relational database. SQL is the standard language for Relation, Database System., All relational database management systems like MySQL, MS Access, Oracle, Sybase,, Informix, postgres and SQL Server use SQL as standard database language., SQL statements are categorized into four different type of statements, which are, 1. DML (Data Manipulation Language), 2. DDL (Data Definition Language), 3. DCL (Data Control Language), 4. TCL (Transaction Control Language), 5. DQL (Data Query Language), 1. Data Manipulation Language(DML), Data Manipulation Language (DML) can be defined as a set of syntax elements that are used, to manage the data in the database. The commands of DML are not auto-committed and, modification made by them is not permanent to the database. It is a computer programming, language that is used to perform select, insert, delete and update data in a database. The user, requests are assisted by Data Manipulation Language. This language is responsible for all, forms of data modification in a database., 2 .Data Definition Language(DDL):, This part includes changes to the Structure of the table like creation of table, altering table,, deleting etc. All DDL commands are auto-committed, that means it saves all the changes, permanently in the database., 3 . Data Control Language(DCL):, Data control language (DCL) is used to access the stored data. It is mainly used for revoke, and to grant the user the required access to a database., , 14

Page 15 :
4 . Transaction Control Language(TCL):, Transaction Control language is a language that manages transactions within the database., It is used to execute the changes made by the DML statements., , 5 . Data Query Language(DQL):, Data Query Language (DQL) is used to fetch the data from the database., 2.4.1 Data Types of SQL:, Each column in a database table is required to have a name and a data type., An SQL developer must decide what type of data that will be stored inside each column when, creating a table., Following are the data types:, , 1. Char(size), 2. Varchar and Varchar 2(Size), 3. Date, 4. Number(P,S), 5. Long, 6. Raw/Long Raw, 7. Boolean, •, , Char(size), A FIXED length string (can contain letters, numbers, and special characters)., The size parameter specifies the column length in characters - can be from 0 to, 255. Default is 1, , •, , Varchar and Varchar 2(Size), A VARIABLE length string (can contain letters, numbers, and special, characters). The size parameter specifies the maximum column length in, characters - can be from 0 to 65535., 15

Page 16 :
•, , Date, A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to, '9999-12-31', , •, , Number(P,S), Number (P,S) data type is used to store numbers . P is the precision which, Determines the maximum length of the date and specifies the maximum length, of the decimal places on the right side are scale., The precision P can range from 1 to 38. The Scale 8 can range from- 84 to, 127., , •, , Long, This data type is used to store the variable length containing data upto 2 GB., Long data can be used to store array of binary data in ASCII format., , •, , Raw/Long Raw, For starting binary data this type of data this type of data type is used . Binary, data represents the data in bytes., , •, , Boolean, This data type is used to state the true or false conditions., , 2.5 Data Definition Language(DDL):, CREATE:, CREATE statements is used to define the database structure schema:, Syntax:, CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);, For example:, Create database university;, Create table students;, , 16

Page 17 :
ALTER, Alters command allows you to alter the structure of the database., Syntax:, To add a new column in the table, ALTER TABLE table_name ADD column_name COLUMN-definition;, To modify an existing column in the table:, ALTER TABLE MODIFY(COLUMN DEFINITION....);, For example:, Alter table guru99 add subject varchar;, , DROP, Drops commands remove tables and databases from RDBMS., Syntax, DROP TABLE ;, For example:, Drop object_type object_name;, Drop database university;, Drop table student;, , TRUNCATE:, This command used to delete all the rows from the table and free the space containing the, table., Syntax:, TRUNCATE TABLE table_name;, 17

Page 18 :
Example:, TRUNCATE table students;, , DESC, , The Table structure can be seen with the help of desc command. To check structure of table, this command is used as follows., Syntax:, Desc < Table_name>, , Output:, Table, , EMP, , Column, , Data type, , lengt Precisio, h, , n, , scale, , Primary, , nullable, , Default, , Comment, , key, , Emp_id, , NUMBER, , -, , 5, , 0, , -, , ✓, , -, , -, , Emp_name, , VARCHAR, , 10, , -, , -, , -, , ✓, , -, , -, , Salary, , NUMBER, , -, , 5, , 2, , -, , ✓, , -, , -, , Joining date, , DATE, , 7, , -, , -, , -, , ✓, , -, , -, , Dept_no, , NUMBER, , -, , 3, , 0, , -, , ✓, , -, , -, , Dept_name, , VARCHAR, , 5, , -, , -, , -, , ✓, , -, , 16, , Rename:, This Command is used to rename a table from database i.e we can give a new name to, table., Syntax:, Rename < Table_Name > to < New Table Name >, e.g, rename emp to emp-123;, , Creating User, Create user command is used to create the user. we need to specify user name and identify by, clause., 18

Page 19 :
User can be identified by externally or globally. Password expire is another clause used with, creating user. Identified by clause does not allow password of the user to get expire., e.g, Create user user 1 identified externally;, Output:, 0.10 seconds., 2.6 Data Integrity Constraints:, •, , Integrity Constraints are used to ensure accuracy and consistency of the data in a, relational database., , •, , In RDBMS accuracy and consistency stands for completeness and correctness, the, table are connected with the help of foreign key., , •, , Database modification can be done with the help of INSERT, DELETE or UPDATE, statement we may lose the integrity of the data as :, a) Invalid data may get entered, b) Changes made in DB may get lost due failure of system., c) Existing data may get lost due failure of system., d) Existing data may get modified due to the wrong values., e) Parent record may get deleted though child records exists., e.g In Employee table general record may get deleted., , •, , In Integrity or data integrity has following types:, a. I/O constraints :, It includes primary key, Foreign key, Unique Key constraints., b. Business Rule Constraints:, This Type of integrity constraints include default, not null, check constraints., , 19

Page 20 :
2.6.1 I/O Constraints:, Constrains that control data insertion and data retrieval speed are known as I/O (InputOutput) constraints., ▪, , It includes the following constraints., , 1. Primary Key, 2. Foreign Key, 3. Unique Key, Primary Key, ▪, , Primary key is used to identify a record uniquely from the database table., , ▪, , A primary key means UNIQUE + NOT NULL., , ▪, , Value must be available for the column on which primary key has been defined., , ▪, , User cannot leave the field blank., , ▪, , It cannot contain duplicate values., , ▪, , Primary key can be defined either at table level or at column level., , ▪, , Primary key keyword is used to define primary key constraint, , ▪, , When primary key is defined then the behaviour of the transaction will be as follow:, , 1. It does not allow duplicate value for the primary key column., 2. It does not allow null value that means we cannot left the field blank., 3. A value must be present and it must be unique., Foreign Key, ▪, , A foreign key constraint is used to establish logical relationship between two or more tables., , ▪, , Foreign key is also known as referential key., , ▪, , It can be defined using the keyword “references”., , ▪, , Normally we can establish relationship between two or more tables by using some common, fields., , ▪, , Generally a primary key is considered for defining relationship with other tables., , ▪, , The main table which is logically linked with other table is known as ‘Parent, table’ or “master table” while other table is referred to as ‘Child table’ or “detail table”., 20

Page 21 :
▪, , The following point should be kept in the mind while defining foreign key., , 1. Data type and size of the parent table and child table should be the same., 2. Record can be inserted in detail table only if relevant record is available into the master table, that means while inserting record first we need to insert record into the parent table then and, then we can insert record into the child table., 3. To delete a specific record, first we need to delete a relevant record from the detail table and, after that record can be deleted from the parent table., Unique Key, ▪, , A unique key constraint can be defined when we do not want the user to enter duplicate, values., , ▪, , A unique constraint allows only unique value to be inserted., , ▪, , However it allows null value to be inserted., , ▪, , If the value is present for the unique constraint field then it must be unique., , ▪, , Similar to not null constraint unique key can also be define on multiple columns., , ▪, , “unique” keyword is use to define unique constraint., , 2.6.2 Business Rule Constraints:, , 2.7 DML Commands:, DML commands abbreviation of Data manipulation language., INSERT, Insert statement is used to insert a set of values into database table. Insert statement it, used with Values., Example:, Insert Into Student (StudentName, StudentAge, Gender) Values(ram,’10’,male), , UPDATE, Update statement is used to update existing values in atable, which is based on, somecondition., Example:, update student set StudentName=’Manoj’ where StudentName=’Kumar’, 21

Page 22 :
The query given above will update the studentName from Manoj to Kumar where, student Name Kumar., •, , DELETE, , Delete statement is used to delete the existing record in the table, which is based on, some condition., Example: Delete from Student where StudentName=’Manoj’, The query given above will delete records which has StudentName as Manoj., •, , Call:, CALL command is used to call stored procedure written in SQL language., e.g CALL fact;, , 2.8 DCL Commands:, , In Data Control Language(DCL), it defines the control over the data in the database. We have, two different commands, which are, •, , GRANT, , Grant is allowed to do the specified user to the specified tasks., Syntax, GRANT privilege_name, ON object_name, TO {user_name |PUBLIC |role_name}, [WITH GRANT OPTION];, •, , REVOKE, , It is used to cancel previously granted or denied permissions., , 22

Page 23 :
Syntax, REVOKE privilege_name, ON object_name, FROM {user_name |PUBLIC |role_name}, •, , COMMIT, , Commit commands is used to end the transaction and also make its effect permanent to the, database., Syntax:, Commit work;, OR, Commit, , •, , SAVEPOINT, Savepoint command is used to temporarily save transactions so that you can, rollback to that point whenever required., Syntax :, Savepoint savepoint_name;, , •, , ROLLBACK, This command restores the database to last committed state. If we have used the, update command to make some changes into the database and realize that those, changes were not required, then we can use the Rollback command to undo those, changes, if they were not committed., Syntax:, Roll back work;, OR, Roll back, 23

Page 24 :
2.9 DQL Commands:, DQL commands stands for Data Query Language which includes only one command, select. Select Command is used to display the table., Syntax:, Select * from table_name;, e.g, select * from prod_dtls;, then it will display rows and columns in a table as shown below:, Prod_id, , Prod_name, , price, , P-101, , Hard disk, , 2500, , P-102, , CPU, , 10000, , P-103, , Mouse, , 150, , P-104, , Key-Board, , 250, , If you want specific column attribute you can write the command as shown below:, e.g, select prod_id, price from prod_dtls;, output: prodt_dtls, Prod_id, , price, , P-101, , 2500, , P-102, , 10000, , P-103, , 150, , P-104, , 250, , To select particular rows we can use where clause:, select * from prod_dtls where price >= 7000;, for applying more than one conditions we can use logical operator AND, OR, NOT with, select statements:, e.g, select prod_id, prod_name where price >= 1500 AND price < = 5000;, 24

Page 25 :
output:, Prod_id, , Prod_name, , price, , P-101, , Hard disk, , 2500, , 2.10 SQL Operator:, SQL operators are used to operate on value or numbers. operators are the characters, that represents actions, which also used in SQL queries directly for the more efficient, selection., SQL operators:, a) Arithmetic Operator, b) Comparison Operator, c) Logical Operator, a) Arithmetic Operator:, Arithmetic names indicates that this operator is used for arithmetic operations on, column like addition, substraction etc., , Operator, , Meaning, , +, , Addition, , -, , Substraction, , *, , Multiplication, , /, , Division, , E.g, Emp_id, , Emp_name, , salary, , Bonus, , Address, , 101, , Netra, , 50000, , 5000, , Pune, , 104, , Vivek, , 20000, , 8000, , Mumbai, , 102, , Smith, , 21000, , 4000, , Pune, , 115, , Tanvi, , 51000, , 2000, , Nashik, , 25

Page 26 :
118, , Amruta, , 34000, , 5000, , Satara, , Select Emp_id, Emp_name, Salary + Bonus from Emp_dtls;, Output:, Emp_id, , Emp_name, , Salary + Bonus, , 101, , Netra, , 55000, , 104, , Vivek, , 28000, , 102, , Smith, , 25000, , 115, , Tanvi, , 53000, , 118, , Amruta, , 39000, , b) Comparison Operator:, Comparison Operator are used mostly for comparing attribute values are checking, relations of attributes., Operators are between, In , Like and Null. Also NOT operator., Operator, , Meaning, , =, , Equal to, , !=, , Not equal to, , <, , Less than, , >, , Greater than, , <=, , Less than or Equal to, , >=, , Greater than or Equal to, , <>, , Not equal to, , Between, , Between two values, , In, , In the list, , Null, , Is Null, , e.g Select ep_id, emp_name, Address from emp_dtls where salary > 25000, output:, , 26

Page 28 :
d) Set- Operators:, When we want to combine the result of 2 queries then the special type of operators are, used known as set operators., The set operators are used to join queries and retrieve the number of rows. The operators, are as follows:, , 1. Union, 2. Union all, 3. Intersect, 4. Minus, Consider 2 table first is emp_dtls defined earlier and dept_dtls as shown below:, Emp_id, , Dept_name, , Dept_no, , 101, , Production, , d-1, , 102, , Sales, , d-2, , 103, , Marketing, , d-3, , 104, , IT, , d-4, , 10, , Design, , d-5, , 1]Union Operator:, Union Operator selects the distinct values of the same attribute of different related tables., e.g, select Emp_id from Emp_dtls Union select Empid from dept_dtls;, , Emp_id, 101, 102, 103, 104, , 28

Page 29 :
106, 115, 118, , 2] Union All:, Union all operators combines all records from both the queries without avoiding duplicate, values., , E.g, Select emp_id from emp_dtls union all select empid from dept_dtls;, , Output:, , Emp_id, 101, 104, 102, 115, 118, 101, 102, 103, 104, 106, , 3] Intersect:, This set operator is used to find out the similar attribute values between two table for, same attributes., , E.g, 29

Page 30 :
Select Emp_id from Emp_dtls intersect select Emp_id from dept_dtls;, , Emp_id, 101, 104, 102, , 4] Minus:, Minus operator performs the substraction i.e the present in first table and not in other, table are displayed., , e.g Select Emp_id from Emp_dtls minus select Emp_id from dept _dtls;, output:, , Emp_id, 115, 118, , e) Range searching operator: Between:, If we want to search value between a particular range, between operator, The values are, searched from a range for e.g we can display the records between 3 and 6., , e.g, select Emp_id, dept_name from dept_dtls where deptno between 2 and 4., , Output:, , Emp_id, , Dept_name, , 30

Page 31 :
102, , Sales, , 103, , Marketing, , 104, , IT, , f) Pattern Matching Operator Like:, , Like operator is used for pattern matching operator . This operator mostly used with NOT, logical operator., It user to special character to match the pattern ? and % ., ? indicates any number of characters., , e.g, select emp_id, dept_name from dept_dtls where dept_name like ‘p%’;, , output:, , Emp_id, , Dept_name, , 101, , production, , 31