Database Management System

 

Unit - I

Overview Of Database Management Systems

1. What is a Database?

A. A database is a collection of information that is organized so that it can be easily accessed, managed and updated. Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information. Data gets updated, expanded and deleted as new information is added.

To find out what database is, we have to start from data, which is the basic building block of any DBMS.

Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1, ABC, 19 etc).

Record: Collection of related data items, e.g. in the above example the three data items had no meaning. But if we organize them in the following way, then they collectively represent meaningful information.

Table or Relation: Collection of related records.

Roll	Name	Age
1	ABC	19
2	DEF	22
3	XYZ	28

The columns of this relation are called Fields, Attributes or Domains. The rows are called Tuples or Records.

2. Discuss about Problems with File System Data Management? (Or)

What are the Disadvantages of Traditional File processing system? Explain.

A. FILE ORIENTED APPROACH: The earliest business computer systems were used to process business records and produce information. They were generally faster and more accurate than equivalent manual systems. These systems stored groups of records in separate files, and so they were called file processing systems.

1. File system is a collection of data. Any management with the file system, user has to write the procedures

2. File system gives the details of the data representation and Storage of data.

3. In File system storing and retrieving of data cannot be done efficiently.

4. Concurrent access to the data in the file system has many problems like a Reading the file while other deleting some information, updating some information

5. File system doesn’t provide crash recovery mechanism.

6. Protecting a file under file system is very difficult.

The traditional file processing system data management contains the following limitations.

1. Structural and Data Dependence: The application programs that access to data file is dependent on its structure. Whenever any change is made to a data file, then all the application programs that access the data file must also be refined. The changes in the characteristics of data such as changing data type of a field, changing the memory capacity of a field cause changes in all the programs that access the file.

2. Field Definitions and Naming Conventions: While designing a data file, it is important to design every field name of a record. These field names describe the characteristics of actual data values. For example the field name “cust_id” describes the value of a Customer Number.

Ex:       Emp_ID            :         Employee ID

Cust_City          :         Customer City

Stud_Course     :         Student Course

3. Data Redundancy: The file system data management system forces the storage of same basic data in different locations. As application programs are often developed independently, there is more chance of duplicated data. This unnecessarily repeated data is called “Data Redundancy”. The data stored in different locations need to be updated consistently. Otherwise it leads to the problem of data inconsistency.

4. Data Inconsistency: Data inconsistency occurs when redundant data that is stored with different, conflicting versions of same data in different places. If we reduce the no.of repetitions of a data value it means we are developing data consistency.

5. Excessive Program Maintenance: The above discussed factors create heavy program maintenance. Structural data dependency leads to frequent changes in application programs as every data change needs change in application program.

6. Limited Data Sharing: As in traditional File processing approach, each application has its own private files users have little opportunity to share the data outside of their own applications.

3. Explain: Data, Information, Database and Database Management Systems.

A. DATA: Data is referred to known facts that could be recorded on computer media. Data consists of facts such as text, graphics, images, audio, video etc. that have meaning in the user’s environment.

INFORMATION: We can define the information as data that has been processed in such way that it can increase the knowledge of the person who uses it. By adding additional data items and providing some structure, we can acquire the information in from the raw-data.

DATABASE: Database is a repository of data. We can define a database as a well-organized collection of logically related data. A database may be of any size and complexity. As database is a well organized, the data are structured so as to be easily stored, manipulated and retrieved by users. As related, the data describes the domain of interest to a group of users and that the users can use the data to answer the questions concerning that domain.

DBMS: A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

	Data	Information
Definition (Oxford Dictionaries)	Facts and statistics collected together for reference or analysis	Facts provided or learned about something or someone Data as processed, stored, or transmitted by a computer
Refers to	Raw Data	Analyzed Data
Description	Qualitative Or Quantitative Variables that can be used to make ideas or conclusions	A group of data which carries news and meaning
In the form of	Numbers, letters, or a set of characters.	Ideas and inferences
Collected via	Measurements, experiments, etc.	Linking data and making inferences
Represented in	A structure, such as tabular data, data tree, a data graph, etc.	Language, ideas, and thoughts based on the data
Analysis	Not analyzed	Always analyzed
Meaning	Carries no specific meaning	Carries meaning that has been assigned by interpreting data
Interrelation	Information that is collected	Data that has been processed

 

4. What are various objectives of Database Management System?

A. DBMS: A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

·         Mass Storage: DBMS can store a lot of data in it. So for all the big firms, DBMS is really ideal technology to use. It can store thousands of records in it and one can fetch all that data whenever it is needed.

·         Removes Duplicity: If you have lots of data then data duplicity will occur for sure at any instance. DBMS guarantee it that there will be no data duplicity among all the records. While storing new records, DBMS makes sure that same data was not inserted before.

·         Multiple Users Access: No one handles the whole database alone. There are lots of users who are able to access database. So this situation may happen that two or more users are accessing database. They can change whatever they want, at that time DBMS makes it sure that they can work concurrently.

·         Data Protection: Information such as bank details, employee’s salary details and sale purchase details should always be kept secured. Also all the companies need their data secured from unauthorized use. DBMS gives a master level security to their data. No one can alter or modify the information without the privilege of using that data.

·         Data Backup and recovery: Sometimes database failure occurs so there is no option like one can say that all the data has been lost. There should be a backup of database so that on database failure it can be recovered. DBMS has the ability to backup and recover all the data in database.

·         Everyone can work on DBMS: There is no need to be a master of programming language if you want to work on DBMS. Any accountant who is having less technical knowledge can work on DBMS. All the definitions and descriptions are given in it so that even a non-technical background person can work on it.

·         Integrity: Integrity means your data is authentic and consistent. DBMS has various validity checks that make your data completely accurate and consistence.

·         Platform Independent: One can run DBMS at any platform. No particular platform is required to work on database management system.

5. Explain classification of DBMS. (Or) Explain various types of databases.

A. Databases can be classified into different types according to the number of users, location of the database and type of use. Databases range from those single user database systems to thousands of user’s database systems. A single user database supports only one user whereas a multi user database system supports thousands of users. Based on the constraints such as number of users, location of database and type of use, we can categorize databases into the following types.

1. Desktop database
2. Workgroup database
3. Enterprise database
4. Centralized database
5. Distributed database
6. Operational database

7.      Data warehouse

1. Desktop Database:-The database that supports only one user is called as “Desktop Database”. Databases that run on electronic devices like Mobile phones, Personal Digital Assistants (PDAs), Personal computers are called as Desktop databases. These databases are used by atmost and atleast one person.

2. Workgroup Database:-The databases that support relatively small number of users are called as workgroup databases. Workgroup databases are used by multiple users with a small number of persons who are associated within a department. These databases can be constructed by using a Local Area Network (LAN).

3. Enterprise Database:-The database that supports relatively more number of users is called as Enterprise Database. Enterprise databases are used by multiple users within an entire organization. These type of databases can be constructed by using a Local Area Network (LAN) or Metro Politian area Network(MAN).

4. Centralized Database:-The database that is supports data located at a single site is called as centralized database.

5. Distributed Databases:-The databases that support data that is distributed across several different sites are called as distributed databases.

6. Operational Database:-The databases that are designed basically to support day-to-day operations of

an organization is called as “Operational Databases”. They are also called as Transactional Databases or Production Databases.

7. Data warehouse:-Data warehouses basically focus on storing data used to generate information that is needed to make strategic decisions. They are used in Decision Support Systems.

6. What are the advantages of Database Approach.

A. Database is a shared, integrated computer structure that stores collection of user data and metadata. User data includes raw facts that could be recorded on computer media. Metadata includes data about data through which user data is integrated and managed. The database management system has promising potential advantages, which are explained below:

Advantages of Database Approach:

1.      Improved Data Sharing: The database approach provides End-users to have better access to more data and better-managed data. This access makes possible for end users to respond quickly for changes in their environment.

2.      Improved Data Security: When multiple users have the access to data, securing data is more critical. To provide security for data, we spend considerable amount of time, effort and money. DBMS provides better facilities for data privacy and security policies.

3.      Better Data Integration: As DBMS provides the advantage of data sharing, users can have wider access to well-managed data that provides integrated view of the organization’s data operations.

4.      Minimized Data Inconsistency: Data Inconsistency exists when different copies of the same data appear in different places. As we eliminate/reduce the redundancy of data, we improve the consistency of data. The probability of data inconsistency is reduced in a properly designed database.

5.      Improved Data Access: DBMS makes it possible to produce quick answers to the queries posed by end users.

6.      Improved Decision Making: Better managed data and improved data access make it possible to generate better quality of information through which decisions are made.

7.      Increased End-User Productivity: The data combined with the tools that transform data into usable information gives advantage to the End-users to make quick, informed decision that can make the difference between success and failure.

8.      Program-Data Independence: The separation of data descriptions (Metadata) from the application programs is called “Data Independence”. Data descriptions are stored in a central location called “Repository”. This results the flexibility to change the data without changing the application programs that process the data.

9.      Minimal data redundancy: Data files are integrated into a single, logical structure. Each primary fact is recorded in only one place in the database. Database approach allows the designer to carefully control the type and amount of redundancy.

10.  Reduced Program Maintenance: The data that is stored in the database need not be changed frequently. As in a database approach, data is more independent of the application programs we can accommodate these changes very easily.

7. Explain Database System Environment (or) what are the main components of DBMS?

A. Components of DBMS: The database system is an organization of various components that define and control the storage, management and use of data within a database environment. Generally the database system is a composition of hardware, software, people, procedures and data.

1. Hardware: All the physical devices of a system are collectively called as system’s hardware.

Ex : Micro Computers, Work Stations, Storage Devices, Printers etc.

2. Software: A collection of programs is called as “Software”. In database system the commercial software DBMS is treated as software. To make it ready the database system needs an operating system, DBMS software and Application program.

Operating system software manages all the hardware components and makes other software to run on the computer. Ex: Microsoft Windows, Unix

DBMS software manages the storage, manipulation and use of data within the database system. Ex: Microsoft SQL server, Oracle, MySQL, DB2

Application programs are used to access and manipulate data that is resided in the database system.

3. People:  In a database system there are many people who are collaborated with various tasks.

®    System administrators

®    Database administrators

®    Database designers

®    System analysts and programmers

®    End users

4. Procedures:  Procedures are the instructions and rules that control the design and issued of the database system. Procedures enforce the standards by which the business is conducted within the organization.

5. Data: Data is a collection of raw facts stored in the database. Based on this raw material the information is generated.

8. Discuss about the Evaluation of Data Models. (or) Types of Data Models.

A. The need of better management introduced several data models to resolve the problems associated with File Processing system. A data model is a collection of conceptual tools for describing data, relationships, semantics and consistency constraints. There are the following data models to represent the structure of a database.

1. Hierarchical Model: It was developed to manage large amounts of data for complex projects. In this model, the basic logical structure is represented by a top-down tree structure. This structure contains several levels where each level contains several record types in file system.

 

 

 

 

 

 

Advantages: The different advantages of Hierarchical Architecture are as follows:

1. Database Security
2. Efficiency dealing with a large database

Disadvantages: The different disadvantages of Hierarchical Architecture are as follows:

1. Complex Implementation
2. Difficult to manage

2. Network Model: It was created to represent complex data relationships more effectively. It was created to improve database performance and to set a database standard. In this Model, the user views the database as a collection of records in 1:M relationships. It allows a record to have more than one parent. In Network Model a relationship is composed of atleast two record types. Example:                                 

Advantages: The Network model has the following advantages:

1. Data Access flexibility
2. Promotes database integrity

Disadvantages: The network model has following disadvantages:

1. System complexity
2. Lack of structural independence

 

 

 

 

 

 

 

 

 

3. Relational Model: It uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns and each column has a unique name. This  model is implemented through a very sophisticated Relational Database Management System (RDBMS).

Advantages: The different advantages of Relational model are as follows:
1.    Easier database design, implementation, management, and use.
2.    Powerful database management system.
Disadvantages: The different disadvantages of Relational model are as follows:
1.    Substantial hardware and system software overhead
2.    Possibility of poor design and implementation

 

 

 

 

 

4. Entity Relational Model: It consists of a collection of objects called entities and relationships among those entities. ER models are normally represented by using graphical representation called ER diagrams to model database components.

            An Entity is anything like a Person, Place, Object, Event or Concept in the user environment about which data to be collected and stored. An entity represents a particular type of object in the real world. Each entity in an organization is unique and distinct.

Example:        Person : Employee, Student   Place : Store, State  Object :  Building, Chair

            An Attribute is a property or characteristic of a entity. Each entity has a set of attributes associated with it.

Example:   Student: Stud_ID, Stud_Name, Address, Phone

A Relationship is an association among the instances of one or more entities.

                            

Advantages: The advantages of ER model are as follows:

1. Visual representation.
2. Effective communication tool

Disadvantages: The disadvantages of ER model are as follows:

1. Limited constraint representation
2. Limited relationship representation

5. Object-Oriented Model: In this model both data and their relationships are contained in a single structure called object. Object-Oriented data model is the base for Object-Oriented Database Management System (OODBMS). An object includes information about relationships between various facts within the object and information about its relationships with other objects.

Advantages: Advantages of OO database model:

1. Database integrity
2. Both structural and data independence.

Disadvantages: Disadvantages of OO Database model:

1. Lack of OODM standards
2. Complex navigation data access

 

9. Write about ANSI – SPARC data model. (Or) Explain three level ANSI – SPARC Architecture.

A.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The model provides the basis for understanding some of the functionality of a DBMS. In 1975 American National Standards Institution (ANSI), Standards Planning and Requirement Committee (SPARC) recognized the need for three level architecture.

There are following three levels or layers of DBMS architecture:

1. External Level                                 2. Conceptual Level                            3. Internal Level

1. External Level or Subschema: - External Level is described by a schema i.e. it consists of definition of logical records and relationship in the external view. It also contains the method of deriving the objects in the external view from the objects in the conceptual view.

2. Conceptual Level or Conceptual Schema: - Conceptual Level represents the entire database. Conceptual schema describes the records and relationship included in the Conceptual view. It also contains the method of deriving the objects in the conceptual view from the objects in the internal view.

3. Internal Level or Physical Schema: - Internal level indicates hoe the data will be stored and described the data structures and access method to be used by the database. It contains the definition of stored record and method of representing the data fields and access aid used.

A mapping between external and conceptual views gives the correspondence among the records and relationship of the conceptual and external view. The external view is the abstraction of conceptual view which in turns is the abstraction of internal view. It describes the contents of the database as perceived by the user or application program of that view.

10. Explain costs and risks of database approach

A. The database approach emphasizes data integration and sharing across organizations. As with any business decision, the database approach entails some additional costs and risks that must be recognized and managed when implementing this approach. Mentionable some costs & risks of database approach are as follows:

1. New Specialized Personnel: Frequently, organizations that adopt the database approach need to hire or train individuals to design & implement databases, provide database administration services and manage a staff of new people, further, because of the rapid changes in technology these new people will have to be retrained or upgraded on a regular basis.

2. Installation & Management Costs and complexity: A multi-user database management system is a large and complex suite of software that has a high initial cost, requires a staff of trained personnel to install and operate, and also has substantial annual maintenance & support costs. Installing such a system may also require upgrades to the hardware and data communications system in the organization.

3. Conversion Costs: The cost of converting the traditional file processing systems to modern database technology: measured in terms of money, time, and organizational commitment.

4. Need for explicit Backup & Recovery: A shared corporate database must be accurate and available at all times. These require that comprehensive procedures be developed and used for providing backup copies of data and for restoring a database when damage occurs.

5. Organizational Conflict: A shared database requires a consensus on data definitions and ownership as well as responsibilities for accurate data maintenance. Experience has shown that conflicts on data definitions, data formats, and coding, rights to update shared data are frequent and often difficult to resolve.

 

 

 

 

 

 

 

 

 

 

 

UNIT -II

ENTITY – RELATIONSHIP MODEL

1. Explain advantages of ER Model.

A. E-R model: An E – R Model (or) ERD (or) Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system. ER Diagrams are most often used to design or debug relational databases in the fields of software engineering, business information systems, education and research.

·         Conceptually it is very simple: Making the ER Diagram is a very easy process. You just know about the notations about various types of entities, their attributes and the relationships between these entities.

·         Better visual representation: The E-R model gives graphical and  diagrammatical representation of various entities, their attributes and relationships between entities. So, It helps in the clear understanding of the data structure and in minimizing redundancy and other problems.

·         Effective Communication Tool: It is an effective communication tool among users, domain experts and database designers.

·         It is highly integrated with relational model, so converting ER Diagrams to tables is very simple.

·         Easy conversion to any Data Model: Conversion of ER Diagram to any other data model like network model, hierarchical model and the relational model is very easy.

2. Explain the constructs of ER Model (Or) Explain types of entities, attributes          (Or) Explain basic notations of ER Model. (Or) Explain building blocks of ERD.

A. E-R model: Same as above Definition

Entity:  An Entity is an object that has a definite meaning in the user’s environment about which the organization wishes to maintain data. An entity refers to the entity set and not to single occurrence that is it represents a table but not a single row.

            Example:        EMPLOYEE, STUDENT, BUILDING

            An entity may be a strong entity or a weak entity. The entity, which has a key/identifier, is called strong entity where as the entity, which does not have any key/identifier is called a weak entity.

An associative entity is a term used in relational and entity–relationship theory. A relational database requires the implementation of a base relation (or base table) to resolve many-to-many relationships.

Notation:

 

Example:

Attributes: An Attribute is a property or characteristic of a Entity type. Each entity type has a set of attributes associated with it. Based on the importance and behaviour, attributes are divided into the following types. Attributes have a domain where a domain is a set of permitted values for a given attribute.

1. Required Attributes: A required attribute is an attribute that must have a value for each entity occurrence of an entity set. So a required attribute of an entity set should not be left empty.

2. Optional Attribute: An optional attribute is an attribute that may or may not have value for each occurrence of an entity set. So an optional attribute can be left empty also.

3. Identifier:   An identifier is an attribute or set of attributes that uniquely identifies each instance of an entity set. The attributes that are part of an identifier are underlined in ER Diagrams. When the identifier contains only one key attribute, then it is called simple identifier otherwise it is called composite identifier.

4. Composite Attribute: The attribute that can be sub-divided to yield additional attributes is called as a “Composite Attribute”.

5. Simple Attribute: The attribute that cannot be sub-divided to yield additional attributes is called as a “Simple Attribute”.

6. Single-Valued Attribute: A single-valued attribute can have only one value for a given entity instance.

7. Multi-Valued Attribute:   A multi-valued attribute can have more than one value for a given entity instance.

8. Stored Attribute: A stored attribute is an attribute about which the designer wants to store data in the database.

9. Derived Attribute: A derived attribute is an attribute whose value can be calculated from other stored attributes.

Notation:

Example:

Relationships:

            A relationship is a meaningful association between entities. Each relationship is identified by a name that describes the relationship. The entities that participate in a relationship are called as participants.

            The relationship between two entities might be a strong relationship or a weak relationship. The relationship between two strong entities is called as strong relationship or identifying relationship and the relationship between two weak entities or one strong and one weak entity is called as weak relationship or non-identifying relationship.

Notation:

Example:

3. What is Relationship Participation? Explain Relationship Degree in detail.

A. The participation in an entity relationship is either optional or mandatory. Optional participation of a relationship means that the occurrence of one entity does not require a corresponding entity occurrence in that particular relationship. Mandatory participation of a relationship means that the occurrence of one entity requires a corresponding entity occurrence in that particular relationship.

Example:

            If there are two entities PROFESSOR and CLASS, some Professors who conduct research without teaching any class, but each class must be conducted by a professor. Hence the entity CLASS is optional for the entity PROFESSOR in the relation “PROFESSOR teaches CLASS”, the entity PROFESSOR is mandatory for the entity CLASS in the same relation.

Relationship Degree:

            The degree of a relationship indicates that the number of entities that participate in that relationship. In entity relationship modeling, we can see the following types of relationships based on the number of entities that participate in that relationship.

1. Unary Relationship:

            When there is an association is maintained with only one entity in a relationship, then it is called “Unary Relationship.”

Example :

2. Binary Relationship:

            When there is an association is maintained between two entities in a relationship, then it is called “Binary Relationship.” Majority of the relationships are Binary relationships.

Example:

3. Ternary Relationship:

            When there is an association is maintained among three entities in a relationship, then it is called “Ternary Relationship.”

Example:

Cardinality Constraints:

            Cardinality constraint specifies the number of instances of one entity that can be associated with each instance of another entity.

a) Minimum Cardinality:

            The minimum cardinality of a relationship is the minimum number of instances of one entity may be associated with each instance of another entity.

b) Maximum Entity:

            The maximum cardinality of a relationship is the maximum number of instances of one entity may be associated with each instance of another entity.

Notations:

            The following notations are used to represent both optional and mandatory participations with various relationship cardinalities.

4. Explain the Relationship classification. (Or) Write different types of relationships.

A. A Relationship is an association among the instances of one or more entities. Data models use three types of relationships as follows:

1.      One-To-Many Relationship

2.      Many-To-Many Relationship

3.      One-To-One Relationship

1. One-To-Many Relationship (1:M):

            When an entity instance of one entity is related to more than one entity instances of another entity it is called One-To-Many relationship. This relationship is noted as 1:M in data modeling.

Example:

            A CUSTOMER may place any number of ORDERs; an ORDER must be placed by one CUSTOMER.

2. Many-To-Many Relationship (M:M):

                        When multiple entity instances of one entity are related to multiple entity instances of another entity it is called Many-To-Many relationship. This relationship is noted as M:N in data modeling.

Example :

            An EMPLOYEE can learn many COURSEs, a COURSE can be learned by many EMPLOYEEs.

3. One-To-One Relationship (1:1):

            When an entity instance of one entity is related to exactly one entity instances of another entity it is called One-To-One relationship. This relationship is noted as 1:1 in data modeling.

Example :

            An EMPLOYEE manages exactly on STORE, one STORE is exactly managed by one EMPLOYEE.

5. Explain Generalization and Specialization, Aggregation and composition.

A. Generalization: In our Employee example, we have seen different types of employees like Engineer, Accountant, Salesperson, Clerk etc. Similarly each employee belongs to different departments. We can represent it in an ER diagram as below. When you see this diagram for the first time, you will not understand it quickly. One will take time to understand it or he might misunderstand some requirement.

What if we group all the sub departments into one department and different employees into one employee? However sub departments and different employee types have same features in their own domain. So if we merge the child entities into their parent, it makes the diagram simpler, hence easy to understand. This method of merging the branches into one is called generalization. We can see the generalized structure of requirement to understand it quickly.  So above ER diagram will be changed to as below:

Generalization is the bottom up approach which helps to design the requirement at high level. Thus making one to understand quickly.

Specialization: It is opposite approach of generalization. Here, each entity is further divided into sub levels to understand it deeper. In the above example, Department entity is further divided into sub departments to understand how they are scattered. This method of representation helps the developer to code correctly and quickly. It is a top down approach of breaking higher level entity to low level entity. Once the entities are understood at higher level, it makes easy to understand the requirement at low level.

One more example of specialization would be Person. We can further divide person as STUDENT, TEACHER, ENGINEER, SOLDIER etc. (Merging STUDENT, TEACHER, ENGINEER etc into PERSON is an example of generalization).

Aggregation: Look at below ER diagram of STUDENT, COURSE and SUBJECTS. Student attends the Course, and he has some subjects to study. At the same time, Course offers some subjects. Here a relation is defined on a relation. But ER diagram does not entertain such a relation. It supports mapping between entities, not between relations.

If we look at STUDENT and COURSE from SUBJECT’s point of view, it does not differentiate both of them. It offers it’s subject to both of them. So what can we do here is, merge STUDENT and COURSE as one entity. This process of merging is called aggregation. It is completely different from generalization. In generalization, we merge entities of same domain into one entity. In this case we merge related entities into one entity.

Composition: We should be more specific and use the composition link in cases where in addition to the part-of relationship between ClassA and ClassB - there’s a strong lifecycle dependency between the two, meaning that when ClassA is deleted then ClassB is also deleted as a result

The composition link shows that a class (container, whole) has exclusive ownership over other class/s (parts), meaning that the container object and its parts constitute a parent-child/s relationship.

5. Explain how to reduce the ERD into tables.

A. There are various steps involved in converting it into tables and columns. Each type of entity, attribute and relationship in the diagram takes their own depiction here. Consider the ER diagram below and will see how it is converted into tables, columns and mappings.

The basic rule for converting the ER diagrams into tables is:

v  Convert all the Entities in the diagram to tables: All the entities represented in the rectangular box in the ER diagram become independent tables in the database. In the below diagram, STUDENT, COURSE, LECTURER and SUBJECTS forms individual tables.

v  All single valued attributes of an entity is converted to a column of the table: All the attributes, whose value at any instance of time is unique, are considered as columns of that table. In the STUDENT Entity, STUDENT_ID, STUDENT_NAME form the columns of STUDENT table. Similarly, LECTURER_ID, LECTURER_NAME form the columns of LECTURER table. And so on.

v  Key attribute in the ER diagram becomes the Primary key of the table: In diagram above, STUDENT_ID, LECTURER_ID, COURSE_ID and SUB_ID are the key attributes of the entities. Hence we consider them as the primary keys of respective table.

v  Declare the foreign key column, if applicable: In the diagram, attribute COURSE_ID in the STUDENT entity is from COURSE entity. Hence add COURSE_ID in the STUDENT table and assign it foreign key constraint. COURSE_ID and SUBJECT_ID in LECTURER table forms the foreign key column. Hence by declaring the foreign key constraints, mapping between the tables are established.

v  Any multi-valued attributes are converted into new table: A hobby in the Student table is a multi-valued attribute. Any student can have any number of hobbies. So we cannot represent multiple values in a single column of STUDENT table. We need to store it separately, so that we can store any number of hobbies, adding/ removing / deleting hobbies should not create any redundancy or anomalies in the system. Hence we create a separate table STUD_HOBBY with STUDENT_ID and HOBBY as its columns. We create a composite key using both the columns.

v  Any composite attributes are merged into same table as different columns: In the diagram above, Student Address is a composite attribute. It has Door#, Street, City, State and Pin. These attributes are merged into STUDENT table as individual columns.

v  One can ignore derived attribute, since it can be calculated at any time: In the STUDENT table, Age can be derived at any point of time by calculating the difference between DateOfBirth and current date. Hence we need not create a column for this attribute. It reduces the duplicity in the database.

These are the very basic rules of converting ER diagram into tables and columns, and assigning the mapping between the tables. Table structure at this would be as below:

 

6. Explain constraint specification techniques in EER Model.

A. The model that has resulted from adding more semantic constructs to the original ER model is called as Extended Entity Relationship Model or Enhanced Entity Relationship Model.

The most important new modeling construct incorporated in the EER model is Supertype/Subtype relationships. This facility allows us to model a general entity type called “Super Type” and then sub divide it into several specialized entity types called “Sub types”.

1.   SubType Discriminator: A subtype discriminator is the attribute in the supertype entity that determines to which subtype the supertype occurrence is related. It determines that into which of the subtypes an entity instance should be inserted.

In the below EER Diagram, the subtype discriminator Emp_Type determines the targeted subtype to which an instance should be related. If the value of the discriminator is ‘P’ then the instance is related to the subtype entity PILOT, if it is ‘M’ then the instance is related to the subtype entity MECHANIC, if it is ‘A’ then the instance is related to the subtype entity ACCOUNTANT.

Constraints allow us to capture some of the important business rules that apply to the relationships. The two most important types of constraints are Disjointness and Completeness constraints.

2.     Disjointness Constraints: An entity supertype can have disjoint or overlapping entity subtypes. Disjoint constraint address that whether an instance of a supertype may simultaneously be a member of two or more subtypes. The disjointness constraint has two possible rules.

a.              Disjointness Rule                                                        b.  Overlapping Rule

a)  Disjointness Rule: Disjoint subtypes also known as non-overlapping subtypes. Each entity instance of the supertype can appear in only one of the subtypes. It specifies that if an entity instance of the supertype is a member of one subtype, it cannot simultaneously be a member of any other subtype in the hierarchy.

b)

Overlapping Rule: The overlap rule specifies that an entity instance can simultaneously be a member of two or more subtypes. Each entity instance of the supertype may appear in more than one subtype. Example:

3.   Completeness Constraints: The completeness constraint specifies whether each supertype entity occurrence must also be a member of atleast one subtype. Completeness constraint may be withering partial or total.

a)        

Partial Completeness: It means that not every supertype occurrence is a member of a subtype. There may be some supertype occurrences that are not members of any of its subtypes.

b)           Total Completeness: Total completeness constraint means that every supertype entity occurrence must be a member of atleast one of its subtypes.

Notation:

Specialization: Specialization is the top-down process of defining low-level and more specific subtypes of the supertype and forming supertype/subtype relationships. Each subtype is formed based on some distinguishing characteristics such as attributes specific to the subtype.

 

Generalization: Generalization is the bottom-up process of defining high-level and more  generic supertype entity from lower-level subtype entities.

 

 

7. Explain multiple inheritances.

A. Some objects may have similar but not identical attributes and methods. If there is a large degree of similarity, it would be useful to be able to share the common properties.

Inheritance allows one class to be defined as a special case of a more general class. These special classes are known as subclasses and the more general cases are known as super classes. There are several forms of inheritance:

• Single Inheritance
• Multiple Inheritance
• Repeated Inheritance
• Selective Inheritance

 

Single Inheritance: Single Inheritance refers to the fact that the subclasses inherit from no more than one super class.

 

Multiple Inheritance: Multiple Inheritance refers to the fact that the subclasses inherit from more than one super class.

1. Repeated Inheritance: It is a special case of multiple inheritance in which the super class inherit from a common super class. The inheritance must ensure that the subclass does not inherit properties from super class twice.

2. Selective Inheritance: This type of inheritance allows subclass to inherit limited properties from the super class.

 

UNIT- III Relational Model

1. Explain E F Codd’s Relationship Database Rules.

A. E.F Codd’s defined 12 important relational database rules that make a database system as relational database system.  He defined these rules to standardize the definition of relational database system. Any database system that obeys/follows all these rules can be called as Relational Database System. Rules:

1.  Information: The information in a relational database must be presented logically in the form of rows and columns in the tables.

2.  Guaranteed Access: Every data value in a table must be accessible through the combination of table name and the column.

3.  Handling Nulls: The null values must be treated in a systematic way independent of its datatype. A Null value is different from a white space or blank.

4.  Usage of Metadata: The metadata must be stored and managed as ordinary data and it must be accessible through the same tools that are used to access actual data.

5.  Comprehensive Sub-Language:             RDB system must support a common and well-defined language for data definition, retrieval, manipulation, and data integrity and transaction mgmt.

6.  View Updating: The views must be designed as updatable views.

7.  High Level Insert, Update and Delete: This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a table.

8.  Physical Data Independence: Application Programs or Users are isolated from the physical storage structure of the database. They must be allowed to access data even the physical storage hardware is changed.

9.  Logical Data Independence: Application Programs or Users are isolated from the logical structure of the database. They must be allowed to access data even the structure of the tables is changed.

10.  Integrity Independence: All relational integrity constraints must be definable in the relational language and stored in the system catalog but not at application level.

11.  Distribution Independence: The end-users and application programs are completely unaware of where the actual database is located.

12.  Non-Subversion: If the system supports low-level data access, there must be no chance to modify the structure of the tables.

 

2. Explain Concepts of relational data model

A. Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple and it has all the properties and capabilities required to process data with storage efficiency.

Ø  Tables: In relational data model, relations are saved in the format of Tables. This format stores the relation among entities. A table has rows and columns, where rows represents records and columns represent the attributes.

Ø  Tuple: A single row of a table, which contains a single record for that relation is called.

Ø  Relation instance: A finite set of tuples in the relational database system represents relation instance. Relation instances do not have duplicate tuples.

Ø  Relation schema: A relation schema describes the relation name (table name), attributes, and their names.

Ø  Relation key: Each row has one or more attributes, known as relation key, which can identify the row in the relation (table) uniquely.

Ø  Attribute domain: Every attribute has some pre-defined value scope, known as attribute domain.

3. Explain about various Keys and their characteristics that are used to design a database

A. In relational data model, the understanding and usage of keys is very important. Keys are used to identify individual records of table uniquely. They are also used to represent relationships between tables and maintain integrity of data.

Key:    A Key is an attribute or combination of attributes that identifies other attributes uniquely.

Example:If the relation EMPLOYEE contains attributes such as Emp_ID, Emp_Name, Emp_Desi, Emp_Dept, Emp_Salary, then the attribute Emp_ID can serve as a key. Hence the attribute Emp_ID can identify all the remaining attributes of an individual Employee uniquely.

Simple Key:   The Key which consist only one attribute is called as “Simple Key”.

Composite Key:The Key which consist multiple attributes is called as “Composite Key”.

Super Key:     A Super Key is any Key that uniquely identifies the rows of a table uniquely.

Example:        Emp_Id

                        Emp_ID, Emp_Name

                        Emp_ID, Emp_Name, Emp_Dept

Note: Any super set of a Key can be called as a Super Key

Candidate Key:A Candidate Key is a Key/Super Key which consist the attributes that are sufficient for unique identification. A relation can have any number of candidate keys. It should not include any unnecessary attributes to identify the rows of a table uniquely. A Candidate Key must satisfy the following properties.

The Super Key Emp_ID, Emp_Name can be converted as Candidate Key by eliminating Emp_Name from the set, because the attribute Emp_ID itself is sufficient to identify the individual rows of Employee table.

Primary Key:A Primary Key is a Key that is selected from a list of Candidate Keys. It is the

choice of database designer to choose an appropriate candidate key as primary key. Primary

Key is used to build relationship among several entities in the relational data model.

Foreign Key:  A Foreign Key is an attribute or set of attributes whose value matches the Primary Key value in the related table in the same database.

 

4. Explain relation integrity?

Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Relational Integrity Constraints. There are three main integrity constraints:

1.      Key Constraints: There must be at least one minimal subset of attributes in the relation, which can identify a tuple uniquely. This minimal subset of attributes is called key for that relation. If there are more than one such minimal subsets, these are called candidate keys.

Key constraints force that:

Ø  In a relation with a key attribute, no two tuples can have identical values for key attributes.

Ø  A key attribute cannot have NULL values.

Ø  Key constraints are also referred to as Entity Constraints.

2.      Domain Constraints: Attributes have specific values in real-world scenario. For example, age can only be a positive integer. The same constraints have been tried to employ on the attributes of a relation. Every attribute is bound to have a specific range of values. For example, age cannot be less than zero and telephone numbers cannot contain a digit outside 0-9.

3.      Referential integrity Constraints: Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key attribute of a relation that can be referred in other relation.

Referential integrity constraint states that if a relation refers to a key attribute of a different or same relation, then that key element must exist.

 

5. Explain relational algebra?

A.Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An operator can be either unary or binary. They accept relations as their input and yield relations as their output. Relational algebra is performed recursively on a relation and intermediate results are also considered relations.

The fundamental operations of relational algebra are as follows:

1.      Select                                      2.  Project

3.   Union                                      4.  Set different

      5.   Cartesian product6.Rename

1. Select Operation (σ): It selects tuples that satisfy the given predicate from a relation.

        Notation: σ_p(r)

Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula which may use connectors like and, or, and not. These terms may use relational operators like − =, ≠, ≥, < ,  >,  ≤.

Example1:  σ_{subject = "database"}(Books)

Output: Selects tuples from books where subject is 'database'.

Exanple2:σsubject = "database" and price = "450"(Books)

Output: Selects tuples from books where subject is 'database' and 'price' is 450.

2. Project Operation (∏):It projects column(s) that satisfy a given predicate.

Notation: ∏_{A1, A2, An} (r)

Where A₁, A₂ , A_n are attribute names of relation r.Duplicate rows are automatically eliminated, as relation is a set.

Example: ∏_{subject, author} (Books)

Selects and projects columns named as subject and author from the relation Books.

3. Union Operation (∪):It performs binary union between two given relations and is defined as: r ∪ s = { t | t ∈ r or t ∈ s}

          Notation:   r U s

Where r and s are either database relations or relation result set (temporary relation).

For a union operation to be valid, the following conditions must hold:

Ø  r, and s must have the same number of attributes.

Ø  Attribute domains must be compatible.

Ø  Duplicate tuples are automatically eliminated.

Example:∏ author(Books) ∪ ∏ author(Articles)

Projects the names of the authors who have either written a book or an article or both.

4.      Set Difference (−): The result of set difference operation is tuples, which are present in one relation but are not in the second relation.

          Notation − r − s

Finds all the tuples that are present in r but not in s.

Example:∏ author (Books) − ∏ author (Articles)

Output: Provides the name of authors who have written books but not articles.

5.      Cartesian Product (Χ): Combines information of two different relations into one.

          Notation − r Χ s

Where r and s are relations and their output will be defined as:

r Χ s = { q t | q ∈ r and t ∈ s}

Example:   σauthor = 'tutorialspoint'(Books Χ Articles)

Yields a relation, which shows all the books and articles written by tutorialspoint.

6.      Rename Operation (ρ): The results of relational algebra are also relations but without any name. The rename operation allows us to rename the output relation. 'rename' operation is denoted with small Greek letter rho ρ.

          Notation − ρ _x (E)

Where the result of expression E is saved with name of x.

Additional operations are −

Ø  Set intersection

Ø  Assignment

Ø  Natural join

Advantages of Relational Algebra:

v  The relational algebra has solid mathematical background.

v  The mathematical background of relational algebra is the basis of many interesting developments and theorems.

v  If we have two expressions for the same operation and if the expressions are proved to be equivalent, then a query optimizer can automatically substitute the more efficient form.

Limitations Of Relational Algebra:

Although relational algebra seems powerful enough for mostpractical purposes, there are somesimple and natural operators on relations which cannot be expressed by relational algebra.Thetransitive closure of a binary relation is one of them.

 

 

 

6. Explain Relational Algebra Operations?

A.    

7. Explain relational calculus?

A. In contrast to Relational Algebra, Relational Calculus is a non-procedural query language, that is, it tells what to do but never explains how to do it.

Relational calculus exists in Two forms:

1. Tuple Relational Calculus (TRC):  Filtering variable ranges over tuples

          Notation − {T | Condition}

Returns all tuples T that satisfies a condition.

Example: { T.name |  Author(T) AND T.article = 'database' }

Output: Returns tuples with 'name' from Author who has written article on 'database'.

TRC can be quantified. We can use Existential (∃) and Universal Quantifiers (∀).

Example: { R| ∃T   ∈ Authors(T.article='database' AND R.name=T.name)}

Output:  The above query will yield the same result as the previous one.

2. Domain Relational Calculus (DRC): In DRC, the filtering variable uses the domain of attributes instead of entire tuple values (as done in TRC, mentioned above).

          Notation: { a₁, a₂, a₃, ..., a_n | P (a₁, a₂, a₃, ... ,a_n)}

Where a1, a2 are attributes and P stands for formulae built by inner attributes.

Example: {< article, page, subject > |  ∈ TutorialsPoint ∧ subject = 'database'}

Output: Yields Article, Page, and Subject from the relation TutorialsPoint, where subject is database.

Just like TRC, DRC can also be written using existential and universal quantifiers. DRC also involves relational operators.The expression power of Tuple Relation Calculus and Domain Relation Calculus is equivalent to Relational Algebra.

8. Explain Normalization? And its types.

A. Normalization:

v  Normalization is the process of organizing the data in the database.

v  Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.

v  Normalization divides the larger table into the smaller table and links them using relationship.

v  The normal form is used to reduce redundancy from the database table.

Types of Normal Forms

There are the four types of normal forms:

Normal Form	Description
1NF	A relation is in 1NF if it contains an atomic value.
2NF	A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.
3NF	A relation will be in 3NF if it is in 2NF and no transition dependency exists.
4NF	A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued dependency.
5NF	A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.

1. First Normal Form (1NF)

v  A relation will be 1NF if it contains an atomic value.

v  It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute.

v  First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.

EMPLOYEE table:

EMP_ID	EMP_NAME	EMP_PHONE	EMP_STATE
14	John	7272826385, 9064738238	UP
20	Harry	8574783832	Bihar
12	Sam	7390372389, 8589830302	Punjab

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID	EMP_NAME	EMP_PHONE	EMP_STATE
14	John	7272826385	UP
14	John	9064738238	UP
20	Harry	8574783832	Bihar
12	Sam	7390372389	Punjab
12	Sam	8589830302	Punjab

2. Second Normal Form (2NF)

v  In the 2NF, relational must be in 1NF.

v  In the second normal form, all non-key attributes are fully functional dependent on the primary key

Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can teach more than one subject.

TEACHER table

TEACHER_ID	SUBJECT	TEACHER_AGE
25	Chemistry	30
25	Biology	30
47	English	35
83	Math	38
83	Computer	38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a candidate key. That's why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID	TEACHER_AGE
25	30
47	35
83	38

TEACHER_SUBJECT table:

TEACHER_ID	SUBJECT
25	Chemistry
25	Biology
47	English
83	Math
83	Computer

3. Third Normal Form (3NF)

v  A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.

v  3NF is used to reduce the data duplication. It is also used to achieve the data integrity.

v  If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.

A relation is in third normal form if it holds atleast one of the following conditions for every non-trivial function dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

Example: EMPLOYEE_DETAIL table:

EMP_ID	EMP_NAME	EMP_ZIP	EMP_STATE	EMP_CITY
222	Harry	201010	UP	Noida
333	Stephan	02228	US	Boston
444	Lan	60007	US	Chicago
555	Katharine	06389	UK	Norwich
666	John	462007	MP	Bhopal

Super key in the table above:

1.      {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so onCandidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID). It violates the rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.

EMPLOYEE table:

EMP_ID	EMP_NAME	EMP_ZIP
222	Harry	201010
333	Stephan	02228
444	Lan	60007
555	Katharine	06389
666	John	462007

EMPLOYEE_ZIP table:

EMP_ZIP	EMP_STATE	EMP_CITY
201010	UP	Noida
02228	US	Boston
60007	US	Chicago
06389	UK	Norwich
462007	MP	Bhopal

 

UNIT- IV

Structure Query Language

 

1. What are the features of SQL?

A. SQL is both an easy-to-understand language and a comprehensive tool for managing data. Here are some of the major features of SQL and the market forces that have made it successful:

Ø  Vendor Independence: A SQL-based database and the programs that use it can be moved from one DBMS to another vendor's DBMS with minimal conversion effort and little retraining of personnel.

Ø  SQL Standards: In 1986, the American National Standards Institute (ANSI) and the International Standards Organization (ISO) published the first official standard for SQL which was expanded in 1989, 1992 and 1999. The evolving standards serve as an official stamp of approval for SQL and have speeded its market acceptance.

Ø  Portability across Computer Systems: SQL databases run on various computer systems, ranging from mainframes to stand-alone computers. SQL-based applications that begin on single-user or departmental server systems can be moved to larger server systems as they grow.

Ø  Relational Foundation: We already know that SQL is a language for relational databases. The relational database model and row/column structure make SQL simple and easy to understand. The relational model also has a strong theoretical foundation that has guided the evolution and implementation of relational databases.

Ø  Programmatic Database Access: SQL is also a database language used by programmers to write applications that access a database. The same SQL statements are used for both interactive and programmatic access, so the database access parts of a program can be tested first with interactive SQL and then embedded into the program.

 

2.  Explain Components or Elements of SQL

A.    There are three components of SQL –

DDL (Data Definition Language): It is used for creation and deletion of the database. It provides a set of statements to create/define a database, modify its structure after it has been created, and destroy it after it is no longer needed.

v  CREATE TABLE tablename to create a table in the database

v  DROP TABLE tablename to remove a table from the database

v  ALTER TABLE tablename to add or remove columns from a table in the database

DML (Data Manipulation Language): It is used for manipulating the database. Manipulation means insert, modify, delete or query the data in the database. It enables users to access or manipulate the data as organised by appropriate data model.

v  SELECT to select rows of data from a table

v  INSERT to insert rows of data into a table

v  UPDATE to change rows of data in a table

v  DELETE to remove rows of data from a table

DCL (Data Control Language): It is used to grant and revoke authorization for database access, auditing the database use and dealing with transactions. It specifies how to restrict a user/se of users to access only certain parts of data, perform only certain types of queries.

v  GRANT to grant a privilege to a user

v  REVOKE to revoke (remove) a privilege from a user

v  TCL (Transaction Control Language): It is used to manage the changes made by DML

statements. It specifies how transactions can be started / stopped, how a set of concurrently executing transactions can be managed.

 

 

 

 

 

 

 

 

 

 

 

 

 

3.  Write about Data Manipulation Language Commands available inSQL.

Data Manipulation Language commands are used for data manipulation operations such as insertion, updations or deletions. Commands:

1.       INSERT                      2.SELECT                  3.UPDATE                 4.DELETE

1. INSERT: INSERT command is used to add new data values to the database. While giving data values each value must be separated by a comma symbol. The data values like char, varchar, varchar2, date, long etc must be enclosed within a pair of single quote symbols.

Syntax: INSERT INTO <Table Name>VALUES(List of data values order of structure defined);

(or)

INSERT INTO <Table Name> (List of columns for which data are available)VALUES (List of values in the order of column list specified);

Example:  Insert Into Student Values(100,'AnilKumar',19,'M','B.Sc','06-Sep-2010');

(or)

Insert Into Student(Sid, SName, Gender) Values (101,'Rajesh','M');

(or)

Insert Into Student values(&Sid, ‘&SName’, ’&Gender’);

2.  SELECT:SELECT command is used to retrieve/read the existing data values from the database. The statements that are built by using SELECT command are called as ‘Queries’.

Syntax: SELECT <ColumnList>               FROM <TableList>;

Example: Select * from Product;

Select Sid, Sname, DOB from Student;

Select EName from Employee Where Dept='Marketing'; Select Distinct Pname from Product;

Select sname from Student Order By snameDesc;

3. UPDATE: UPDATE command is used to update/modify the data values of a table. By using  a single update command we can update multiple records. Updations on a table can be done based on a condition or even without any condition. While performing update operations, we can specify a condition based on which we want to update the rows of the table.

Syntax: UPDATE <Table Name> SET Col1=Val1, Col2=Val2, . . . .WHERE <Condition>;

Example: 1. Update Employee Set Desi='Clerk' Where EID=102;

2.      Update Employee Set Salary=Salary+(salary*10)/100 Where Desi='Manager';

4. DELETE: DELETE command is used to delete/erase the data values (records) of a table. By using a single DELETE command, we can delete multiple records. Deletions on a table can be done based on a condition or even without any condition.

Syntax: DELETE FROM <Table Name> WHERE <Condition>;

Example: 1. Delete From Student Where Result=’Fail’;

                      Delete From Employee Where Designation='Clerk';

4.  Write about Data Definition Language Commands available inSQL

SQL includes commands to create database objects such as tables, indexes, views etc. These commands are used to create a database object, modify the structure of an existing object, and also to remove the object from the database. Commands:

1.       CREATE                    2.ALTER                    3.DROP                      4.TRUNCATE

1.  CREATE: It is used to define the structure of an object such as table, index, view etc. in the database.         Syntax: CREATE<Object Type><Object Name>(Object Definition);

Eg: CREATE TABLE Student (Sid Number(3), SName Varchar2(20), Gender Char, Course Varchar2(10), DOB Date);

2.  ALTER: It is used to modify the structure of an existing object in the database. This modific-

-ation may be changing the data type, constraints of the properties or adding new properties to the existing objects.

Syntax: ALTER TABLE <Table Name> ADD/MODIFY/DROP (Col Definition, Col

Definition …);

Eg:      ALTER TABLE student MODIFY (snamevarchar2(25)); ALTER TABLE student ADD (course char(10));

ALTER TABLE student DROP(course);

3.  DROP: It is used to remove or erase an object from the database including data and structure of the object. Syntax: DROP <Object Type>< Object Name>;

Eg:              DROP TABLE Employee;

DROP VIEW Stud_View;

4.  TRUNCATE: It is used to delete all the rows of a table by leaving its structure.

Syntax: TRUNCATE TABLE <Table Name>;

Eg:              TRUNCATE TABLE Student;

TRUNCATE TABLE Employee;

5.  Write about Data Control Language commands available in SQL

The Data Control Language (DCL) component of the SQL language is used to create privileges to allow users access to, and manipulation of, the database. There are two main commands:

Ø  GRANT to grant a privilege to a user

Ø  REVOKE to revoke (remove) a privilege from a user

1.  GRANT command: In order to do anything within an Oracle database you must be given the appropriate privileges. Oracle operates a closed system in that you cannot perform any action at all unless you have been authorized to do so. This includes logging onto the database, creating tables, views, indexes and synonyms, manipulating data (ie select, insert, update and delete) in tables created by other users, etc. The SQL command to grant a privilege on a table is:

Syntax: GRANT SELECT, INSERT, UPDATE, DELETE ON table name TO username;

Eg: GRANT SELECT ON employee TO hn23;

GRANT SELECT, UPDATE, DELETE ON employee TO hn44;

2.  REVOKE command: The SQL command to revoke a privilege on a table is:

Syntax: REVOKE SELECT, INSERT, UPDATE, DELETE ON table name FROM username;

Eg: REVOKE SELECT ON employee FROM hn23; REVOKE SELECT, UPDATE, DELETE FROM hn44;

6. Explain different data types in SQL?

A. Each DBMS supports various data types to define the domain of the columns that makes a table. Data types help the designers to choose an appropriate data type for columns depending on their requirements. These data types support various kinds of data values such as Numeric, Character, Date etc.

 

Oracle supports the following data types to design the database objects with various domains:

1.            Char: Char data type is used when the user wants to store a fixed-length of characters. It can hold alphanumeric values in the range of 1 Byte to 2000Bytes.

2.             Varchar/Varchar2: Both Varchar and Varchar2 data types are used when the user wants to store variable-length of character values. These two data types can hold data values of type alphanumeric.

3.              Long: Long data type is used to store variable-length of alphanumeric values upto2GB of size. But only one column of a table can have the data type long.

4.              Date: Date data type is used to store data values related to date. The format of date is ‘dd- mon-yy’.

5.          TimeStamp: TimeStamp data type is used to store data values related to date and time.

6.           Number: Number data type is used to store only numerical values in the form of both integers and real values. The values of these columns may be either positive or negative.

7.          Raw: Raw data type is used to store binary data with a maximum size of 200Bytes.

8. LongRaw: LongRaw data type is used store raw large binary data values.

9. Blob, Clob: These types are used to store large binary and character data values. 10. Bfile: Bfile data type is used to store external files.

7.      Discuss about Select Queries with example.

A. SELECT Statement: SELECT command is used to retrieve/read the existing data values from the database. The statements that are built by using SELECT command are called as ‘Queries’.

Syntax: SELECT <Column List> FROM <Table List>;

Example: Select * from Product;

Select Sid, Sname, DOB from Student;

1)   Selecting rows with condition: We can read partial table contents by placing restrictions on the rows to be included in the output. This can be done by using the WHERE clause to add conditional restrictions to the SELECT statement.

Syntax: SELECT <Column List> FROM <Table List> WHERE <Condition>;

Example: Select EName from Employee Where Dept='Marketing';

Select Sid, SName, Average from Student where Average>=75;

2)   Selecting Unique Values: While reading values, we can also read only unique values of a column. For this we use DISTINCT clause.

Syntax: SELECT DISTINCT <Column Name> FROM <Table Name>;

Example: Select Distinct Pname from Product; Select Distinct Course from Student;

3)    Selecting Ordered Data: When we read data values, they can also be ordered in either ascending or descending order.

Syntax: SELECT <Column List> FROM <Table List> ORDER BY <Column Name>

ASC/DESC;

Examples: Select sname from Student Order By sname Asc; Select sname from Student Order By sname Desc;

Select Eid, Ename From Employee Order By Salary Desc;

4)   Selecting Computed Columns and Column Aliases: While retrieving multiple columns of a table we can also perform computations on those columns and we can name that result as a column alias.

Example: Select Pid,PName, Price*QOH from Product;  Select Pid, PName, Price*QOH As Cost from Product;

Select sid, sname, maths+physics+cs As Total from Student_Results;

 

8.   Discuss about joining database tables. (OR) Discuss about SQL join operators.

The ability to combine tables on common attributes is the most important distinction between a relational database and other databases. A join operation is performed when data are retrieved from more than one table at a time.

The relational join operation merges rows from two tables and returns the rows with one of the following conditions:

Ø  Having common values in common columns

Ø  Meet a given join condition

Ø  Having no matching values

Types of Joins:There are following types ofjoins:

•    Cartesian Product or CrossJoin

•    Equi Join or EqualityJoin

•    Non-equijoin or Non-equalityJoin

•    OuterJoin

•    Left OuterJoin

•     Right OuterJoin

•    SelfJoin

1.   Cartesian Product or Cross Join: A cross join performs a relational product of two tables called Cartesian Product. It returns MxNrow as result where M is the number of rows in first table and N is the number of rows in the secondtable.

Syntax: SELECT < Column List> FROM <Table 1> CROSS JOIN <Table 2>;

Example: Select * From Product Cross Join Vendor;

2.   Equi Join or Equality Join: If any other operator instead of equality operator (=), it is an equality join, also known as an inner join or simple join or anequijoin.

Syntax: SELECT <Column List> FROM <Table1>,<Table2> where <Table 1>.column1=

<Table 2>.column1;

Example: Select * from Emp, Dept where Emp.Deptno=Dept.Deptno;

3.   Non-equijoin or Non-equality Join: If any other operator instead of equality operator (=) is used to join the tables in the query, it is non-equalityjoin.

Example: Select E.Ename, E.Sal, S.Grade from Emp E, SalGrade S where E.sal BETWEEN

S.Lowsal AND S.Hisal;

The above example creates a non-equijoin to evaluate an employee’s grade. The Salary must be between any pair of the low and high salary ranges.

4.  Outer Join: An outer join returns the rows with matching values and also unmatchedvalues. There are three types of outerjoinoperators:1. LEFT 2.RIGHT

1.  LEFT Join: LEFT outer join returns the rows matching the join condition andthe rows in the left side table with unmatched values in the right sidetable.

Syntax: SELECT <Columns> FROM <Table1> LEFT JOIN <Table2> ON <Condition>;

Example: Select p.Pcode, p.PName, p.Price, v.VCode, v.VName From Product p Left Join

Vendor v On p.vcode=v.vcode;

2.  RIGHT Join: RIGHT outer join returns the rows matching the join condition andthe rows in the right side table with unmatched values in the left sidetable.

Syntax: SELECT <Column List> FROM <Table1> RIGHT JOIN <Table2> ON <Condition>;

Example: Select p.Pcode, p.PName, p.Price, v.VCode, v.VName From Product p Right Join

Vendor v Onp.vcode=v.vcode;

5.   Self Join: It is a Join of a Table to itself. The same table appears twice in the from clause and is followed by table aliases. The table aliases must qualify the column names in the join condition.

Syntax: SELECT Columns FROM Table T1, Table T2 where T1.Column1=T2.Column2;

Example: Select E1.EName “Employee Name”, E2.EName “Managers Name” from Emp E1,

Emp E2 where E1.Mgr=E2.Empno;

 

9.   Discuss about sub queries, nested queries and correlated subqueries.

SQL also provides the subquery technique which involves placing an inner query within WHERE or HAVING clause of another query called Outer query. The inner query provides values for the search condition of the outer query, such queries are referred to as subqueries or Nested sub queries and may be nested multiple times. In this technique, the parent query always returns the output depending upon the results of the inner query.

Sometimes either the joining or the subquery technique may be used to accomplish the same result. The joining technique is useful when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested.

A subquery that returns only one row is called as “Single Row Subquery”. A subquery that returns more than one row is called as “Multiple-row Subquery”.

The inner query always must be enclosed in braces. Sub query will be evaluated first followed by main query. All the inner and outer queries will be nested using comparison operators. Operators:

Ø  Single row operators: The following operators are used with single row sub queries.

< , > , < = , > = , ! =

Ø  Multiple row operators: The following operators are used with multiple row subqueries.

IN , ANY, ALL, SOME

Ø  WHERE Sub queries: The most common type of sub query uses an inner SELECT sub query on the right side of a WHERE comparison expression. These queries work on the sub queries that returns only one row. The comparison operators <, >, =, >=, <=, <> are used for building single row sub queries.

Eg: select sname,cs from student where sid = (select sid from student where cs=(select max(cs) from student));

Ø  Multiple Row Sub Queries: Multiple row sub queries work on the sub queries that may return more than one row as result. The operators IN, ANY, ALL are used for working with multiple row sub queries.

Eg: select sname from student where sid in (select sid from student where cs>maths and cs>physics);

Ø  Extracting Data from Multiple Table: While working with subqueries, we can also extract data values from multiple tables.

Eg: select dname from dept where deptno=(select dept_no from employee where ename='Lavanya');

Ø  ANY Operator: We use the ANY operator in a WHERE clause to compare a value with any of the values in a list. ANY evaluates to true if the result of an inner query contains atleast one row that satisfies the condition. We must place an =, <>, <, >, <=, or >= operator beforeANY.

Eg: SELECT * FROM employee WHERE salary > ANY (2000, 3000, 4000);

Ø  ALL Operator: We use the ALL operator in a WHERE clause to compare a value with all of the values in a list. ALL evaluates to true when each value of inner query satisfies the condition. We must place an =, <>, <, >, <=, or >= operator beforeALL.

Eg: SELECT * FROM employee WHERE salary > ALL (2000, 3000, 4000);

Ø  SOME Operator:SOME operator compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

Eg: Select * From Employee Where salary=some (2750, 3000, 4000);

Ø  Correlated Sub Queries: In normal subqueries the queries will be executed in an orderly fashion that is one after another. In contrast, a correlated subquery is a subquery that  executes once for each row in the outer query

10. Explain Group or aggregate functions

A.    It produce a single value for an entire group ortable.

1.  COUNT(All Columns): It determines the number ofrows.

Example: Select COUNT(*) From emp;

2.  SUM(All Columns): It returns the sum of values for the selected list ofcolumns.

Example: Select SUM(sal) From emp;

3.  MAX(column name):It returns the maximum value of the selected list ofitems.

Example: Select MAX(sal) From emp;

4.  MIN(column name): It returns the minimum value of the selected list ofitems.

Example: Select MIN(sal) From emp;

5.  AVG(column name): It returns the average of columnvalues.

Example: Select AVG(sal) From emp;

11. Discuss about Relational Set Operators with examples?

A. Set operations are used to combine two or more sets to create new set where each set can be understood as a relation. In relational database terms, we can use the words ‘sets’, ’relations’ and ‘tables’ interchangeably. SQL enables us to handle the requirements of combining two or more sets by using set operations like UNION, INTERSECT and MINUS.

 SQL statements containing these set operators are called as “Compound Queries” and each SELECT statement in a compound query is referred as “Conponent Query”. A set operator can combine two SELECT statements into a compound query when they satisfy the following conditions.

1.                  The result set of both the queries must have the same number of columns

2.                  The datatype of each column in the second result set must match to the datatype of its corresponding column in the first result set.

1. UNION: The UNION operator combines rows from two or more queries without including duplicate rows. It merges the result of two or more queries into a single set of values.

Syntax : <Select Query>   UNION    <Select Query>;

Query :       

Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer1 

UNION Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer2;

2. UNION ALL: The UNION ALL operator combines rows from two or more queries by including even duplicate rows from the sets.

Syntax : <Select Query>    UNION   ALL   <Select Query>;

Query :

Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer1  UNION ALL Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer2;

3. INTERSECT: The INTERSECT operator combines rows from two queries returning only the rows that appear in both the sets.

Syntax : <Select Query>    INTERSECT    <Select Query>;

Query :

Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer1 

INTERSECT Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer2;

4. MINUS: The MINUS operator combines rows from two queries returning only the rows that appear in the first set but not in the second set.

Syntax : <Select Query>    MINUS    <Select Query>;

Query :

Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer1 

MINUS Select Cust_Code, Cust_Name, Cust_FName, Cust_Address, Balance From Customer2;

12. Explain Views in SQL?

A.    Views in SQL:

v  Views in SQL are considered as a virtual table. A view also contains rows and columns.

v  To create the view, we can select the fields from one or more tables present in the database.

v  A view can either have specific rows based on certain condition or all the rows of a table.

Student_Detail

STU_ID	NAME	ADDRESS
1	Stephan	Delhi
2	Kathrin	Noida
3	David	Ghaziabad
4	Alina	Gurugram

Student_Marks

STU_ID	NAME	MARKS	AGE
1	Stephan	97	19
2	Kathrin	86	21
3	David	74	18
4	Alina	90	20
5	John	96	18

1. Creating view: A view can be created using the CREATE VIEW statement. We can create a view from a single table or multiple tables.

Syntax: CREATE VIEW view_name AS SELECT column1, column2.....  FROM table_name  

                  WHERE condition;  

2. Creating View from a single table: In this example, we create a View named DetailsView from the table Student_Detail.

Query: CREATE VIEW DetailsView AS  SELECT NAME, ADDRESS  FROM Student_Details  

                WHERE STU_ID < 4;  

Just like table query, we can query the view to view the data.

             SELECT * FROM DetailsView;  

Output:

NAME	ADDRESS
Stephan	Delhi
Kathrin	Noida
David	Ghaziabad

3. Creating View from multiple tables: View from multiple tables can be created by simply include multiple tables in the SELECT statement. In the given example, a view is created named MarksView from two tables Student_Detail and Student_Marks.

Query: CREATE VIEW MarksView AS SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS FROM Student_Detail, Student_Mark 

             WHERE Student_Detail.NAME = Student_Marks.NAME;  

To display data of View MarksView:

SELECT * FROM MarksView;  

NAME	ADDRESS	MARKS
Stephan	Delhi	97
Kathrin	Noida	86
David	Ghaziabad	74
Alina	Gurugram	90

4. Deleting View: A view can be deleted using the Drop View statement.

Syntax: DROP VIEW view_name;  

Example: If we want to delete the View MarksView, we can do this as:

                 DROP VIEW MarksView;  

Unit-5  PL/SQL

 

1.      Discuss about structure of PL/SQL (Procedural SQL)? (Or)Explain different sections of PL/SQL program.(Or) Explain steps to create a PL/SQL

PL/SQL Procedural Language/Structured Query Language. PL/SQL extends SQL by adding extra flavors like variables, conditional statements, looping structures, cursor management, exception handling statements etc. PL/SQL combines the flexibility of SQL with the power of programming language.

Structure of PL/SQL Block:

Declare

            <Declarative Statements >

Begin

         <Executable Statements >

Exception

            < Exception Handling Statements >

End;

1) Declare Section: Declare section is used to place all the declarative statements that declare the variables, cursors, exceptions etc.

2) Begin Section: Begin section is used to place all the executable statements that process the data of the program.

3) Exception Section: Exception handling section is used to place all the exception handling statements of the program.

4) End Section: Each procedure block must be ter

minated by the keyword END.          

Note:

1.        The sections Begin and End are mandatory but the sections Declare and Exception are optional.

2.        Before using any object such as variable, cursor, exception etc. they must be declared.

3.        Each executable statement in a PL/SQL block must be terminated by a semi-colon symbol.

Program to initialize two variables of type Number and calculate their sum.

Declare

         a Number := 100;

         b Number := 200;

         c Number;

Begin

         c := a+b;

         dbms_output.put_line('Sum of a,b is : '||c);

End;

2. What are the elements of PL/SQL

A. The following are some of the elements of PL/SQL Language:

1.      Assignment Statement: An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target.

2.      Block Declaration: The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required.

3.      CASE Statement: The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE.

4.      CLOSE Statement: The CLOSE statement indicates that you are finished fetching from a cursor or cursor variable, and that the resources held by the cursor can be reused.

5.      Collection Definition: A collection is an ordered group of elements, all of the same type. For example, the grades for a class of students. Each element has a unique subscript that determines its position in the collection. PL/SQL offers three kinds of collections: associative arrays, nested tables, and arrays.

6.      Collection Methods: A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections.

7.      COMMIT Statement: The COMMIT statement makes permanent any changes made to the database during the current transaction. A commit also makes the changes visible to other users. The SQL COMMIT statement can be embedded as static SQL in PL/SQL.

8.      Constant and Variable Declaration: You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage for a value, specify its datatype, and specify a name that you can reference. Declarations can also assign an initial value and impose the NOT NULL constraint.

9.      DELETE Statement: The DELETE statement removes entire rows of data from a specified table or view.

10.  Exception Definition: An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

 

3. What are the data types used in PL/SQL

A. The PL/SQL variables, constants and parameters must have a valid data type, which specifies a storage format, constraints, and a valid range of values.

·                  PL/SQL Scalar Data Types and Subtypes

PL/SQL Scalar Data Types and Subtypes come under the following categories −

S.No	Date Type & Description
1	Numeric- Numeric values on which arithmetic operations are performed.
2	Character- Alphanumeric values that represent single characters or strings of characters.
3	Boolean- Logical values on which logical operations are performed.
4	Datetime- Dates and times.

·                  PL/SQL Numeric Data Types and Subtypes

Following table lists out the PL/SQL pre-defined numeric data types and their sub-types −

S.No	Data Type & Description
1	PLS_INTEGER- Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
2	BINARY_INTEGER- Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
3	BINARY_FLOAT- Single-precision IEEE 754-format floating-point number
4	BINARY_DOUBLE- Double-precision IEEE 754-format floating-point number
5	NUMBER(prec, scale)- Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0
6	DEC(prec, scale)- ANSI specific fixed-point type with maximum precision of 38 decimal digits
7	DECIMAL(prec, scale)- IBM specific fixed-point type with maximum precision of 38 decimal digits
8	NUMERIC(pre, secale)- Floating type with maximum precision of 38 decimal digits
9	DOUBLE PRECISION- ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
10	FLOAT- ANSI and IBM specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
11	INT- ANSI specific integer type with maximum precision of 38 decimal digits
12	INTEGER- ANSI and IBM specific integer type with maximum precision of 38 decimal digits
13	SMALLINT- ANSI and IBM specific integer type with maximum precision of 38 decimal digits
14	REAL- Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)

·                  PL/SQL Character Data Types and Subtypes

Following is the detail of PL/SQL pre-defined character data types and their sub-types −

S.No	Data Type & Description
1	CHAR- Fixed-length character string with maximum size of 32,767 bytes
2	VARCHAR2- Variable-length character string with maximum size of 32,767 bytes
3	RAW- Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL
4	NCHAR- Fixed-length national character string with maximum size of 32,767 bytes
5	NVARCHAR2- Variable-length national character string with maximum size of 32,767 bytes
6	LONG- Variable-length character string with maximum size of 32,760 bytes
7	LONG RAW- Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL
8	ROWID- Physical row identifier, the address of a row in an ordinary table
9	UROWID- Universal row identifier (physical, logical, or foreign row identifier)

·                  PL/SQL Boolean Data Types

The BOOLEAN data type stores logical values that are used in logical operations. The logical values are the Boolean values TRUE and FALSE and the value NULL.

·                  PL/SQL Large Object (LOB) Data Types

Large Object (LOB) data types refer to large data items such as text, graphic images, video clips, and sound waveforms. LOB data types allow efficient, random, piecewise access to this data. Following are the predefined PL/SQL LOB data types −

Data Type	Description	Size
BFILE	Used to store large binary objects in operating system files outside the database.	System-dependent. Cannot exceed 4 gigabytes (GB).
BLOB	Used to store large binary objects in the database.	8 to 128 terabytes (TB)
CLOB	Used to store large blocks of character data in the database.	8 to 128 TB
NCLOB	Used to store large blocks of NCHAR data in the database.	8 to 128 TB

 

4. Explain control structures in PL/SQL?

A. In addition to SQL commands, PL/SQL can also process data using flow control statements. The flow control statements can be classified as the following categories:

1.              Conditional Control Statements

2.              Iteration Control Statements

3.              Sequential Control Statements

Conditional Control Statements: PL/SQL IF statement check condition and transfer the execution flow on that matched block depending on a condition. IF statement execute or skip a sequence of one or more statements. PL/SQL IF statement four different type,

a.  IF THEN Statement: IF THEN Statement write in following syntax format:

IF ( condition ) THEN statement

ENDIF

Example:

DECLARE

no INTEGER(2)= 14;

BEGIN

   IF ( no = 14 ) THEN

      DBMS_OUTPUT.PUT_LINE('conditiontrue');

END IF;

END;

/

b. IF THEN ELSE Statement: IF THEN ELSE Statement write in following syntax format:

IF ( condition ) THEN

statement;

ELSE

         statement;

END IF;

Example:

                                  DECLARE

          no INTEGER(2) := 14;

   BEGIN

   IF ( no = 11 ) THEN

      DBMS_OUTPUT.PUT_LINE(no || ' is same');

   ELSE

      DBMS_OUTPUT.PUT_LINE(no || ' is not same');

   END IF;

END;

/

b.   IF THEN ELSIF Statement: IF THEN ELSIF Statement write in following syntax format:

IF (condition-1 ) THEN statement-1;

ELSIF (condition-2 ) THEN statement-2;

ELSIF( condition-3 ) THEN statement-3;

ELSE statement;

END IF;

EXAMPLE:

 

DECLARE

           result CHAR(20):= 'second';

           BEGIN

   IF ( result = 'distinction' ) THEN

      DBMS_OUTPUT.PUT_LINE('First Class with Distinction');

   ELSIF ( result = 'first' ) THEN

      DBMS_OUTPUT.PUT_LINE('First Class');

   ELSIF ( result = 'second' ) THEN

      DBMS_OUTPUT.PUT_LINE('Second Class');

   ELSIF ( result = 'third' ) THEN

      DBMS_OUTPUT.PUT_LINE('Third Class');

   ELSE

      DBMS_OUTPUT.PUT_LINE('Fail');

   END IF;

END;

/

1.       PL/SQL Loop - Basic Loop, FOR Loop, WHILE Loop: PL/SQL Loop Basic Loop, FOR Loop, WHILE Loop repeat a number of block statements in your PL/SQL program. Loop use when we have a block of statements for required to repeatedly certain number of times. PL/SQL loop statements 3 different forms:

a.  Basic LOOP: Basic LOOP write in following syntax format:

[ label_name ] LOOP

statement(s);

END LOOP [ label_name ];

Example:

DECLARE

          no NUMBER := 5;

              BEGIN

             LOOP

                            DBMS_OUTPUT.PUT_LINE ('Inside value: no = ' ||no);

             no:= no-1;

             IF no = 0THEN

                            EXIT;

             END IF;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Outside loop end');END;

/

b. WHILE LOOP: WHILE LOOP write in following syntax format: [ label_name ] WHILE condition LOOP

statement(s);

END LOOP [ label_name ];

Example:

DECLARE

               i NUMBER := 0;

            BEGIN

             WHILE i < 10

  LOOP

    DBMS_OUTPUT.PUT_LINE(i);

             i := i +1;

END LOOP;

END;

/

2. FOR LOOP: FOR LOOP write in following syntax format:

[ label_name ] FOR current_value IN [ REVERSE ] lower_value..upper_value LOOP

statement(s);

END LOOP [ label_name ];

Example:

BEGIN

             FOR no IN 1 .. 5LOOP

             DBMS_OUTPUT.PUT_LINE('Iteration : ' ||no);

             END LOOP;END;

/

5. Explain procedure concept in PL/SQL?

A.  PL/SQL procedures create using CREATE PROCEDURE statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value. When you create a function or procedure, you have to define IN/OUT/INOUT parameters.

1.              IN: IN parameter referring to the procedure or function and allow to overwritten the value of parameter.

2.              OUT: OUT parameter referring to the procedure or function and allow to overwritten the value of parameter.

3.              INOUT : Both INOUT parameter referring to the procedure or function to pass both IN OUT parameter, modify/update by the function or procedure and also get returned.

PL/SQL Procedure Syntax:

CREATE [OR REPLACE] PROCEDURE [SCHEMA..]procedure_name

        [ (parameter [,parameter])]

    IS

        [declaration_section

                               Variable declarations

Constant declarations;

        ]

             BEGIN

                 [executable_section

                                PL/SQL execute/sub program body

                ]

             [EXCEPTION]

            [exception_section

                                PL/SQL Exception block

            ]

             END[procedure_name];

    /

Create PROCEDURE: In this example passing IN parameter (no) and inside procedure SELECT ... INTO statement to get the employee information.

CREATE or REPLACE PROCEDURE pro1(no in number,temp out emp1%rowtype) IS

BEGIN

SELECT * INTO temp FROM emp1 WHERE eno =no; END;

/

               7. Explain FUNCTIONS concept in PL/SQL?

A. PL/SQL functions block create using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value.

When you create a function or procedure, you have to define IN/OUT/INOUT parameters.

 

1.              IN: IN parameter referring to the procedure or function and allow to overwritten the value of

parameter.

2.              OUT: OUT parameter referring to the procedure or function and allow to overwritten the value of parameter.

3.              IN OUT: Both IN OUT parameter referring to the procedure or function to pass both IN OUT parameter, modify/update by the function or procedure and also get returned.

PL/SQL Functions Syntax

CREATE [OR REPLACE] FUNCTION [SCHEMA..]function_name

[(parameter,parameter)} RETURN return_datatype

IS | AS

[declaration_section

                  variable declarations;

                  constant declarations;

]

BEGIN

[executable_section PL/SQL execute/subprogram body]

[EXCEPTION]

[exception_section PL/SQL Exception block]

END [function_name];

/

EXAMPLE:

CREATE or REPLACE FUNCTION fun1(no in number) RETURN varchar2 IS

name varchar2(20); BEGIN

select ename into name from emp1 where eno = no; return name;

END;

/

8. Explain PACKAGES concept in PL/SQL?

A. PL/SQL Packages is schema object and collection of related data type (variables, constants), cursors, procedures, functions are defining within a single context. Package are device into two part,

1.       PackageSpecification                                  2. PackageBody

Package specification block you can define variables, constants, exceptions and package body you can create procedure, function, subprogram.

PL/SQL Package Advantages:

1.              You can create package to store all related functions and procedures are grouped together into single unit calledpackages.

2.              Package are reliable to granting aprivileges.

3.              All function and procedure within a package can share variable amongthem.

4.              Package are support overloading to overload functions andprocedures.

5.              Package are improve the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't required to calling physicallyI/O.

6.              Package arereduce the traffic because all block execute all atonce.

PL/SQL Package Example:

PL/SQL Package example step by step explain to you, you are create your own package using this reference example. We have emp1 table having employee information,

 

 

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Forbs ross	Web Developer	45k
2	marks jems	Program Developer	38k
3	Saulin	Program Developer	34k
4	ZeniaSroll	Web Developer	42k

Package Specification Code: Create Package specification code for defining procedure, function IN or OUT parameter and execute package specification program.

CREATE or REPLACE PACKAGE pkg1 IS | AS

PROCEDURE pro1(noin number, name out varchar2);

FUNCTION fun1(noin number) RETURN varchar2;

END;

/

Package Body Code: Create Package body code for implementing procedure or function that are defined package specification. Once you implement execute this program.

CREATE or REPLACE PACKAGE BODY pkg1 IS

PROCEDURE pro1(no in number,info our varchar2) IS

BEGIN

SELECT * INTO temp FROM emp1 WHERE eno= no;

              END;

 

FUNCTION fun1(no in number) return varchar2 IS

name varchar2(20); BEGIN

                      SELECT enameINTO name FROM emp1 WHERE eno= no; RETURN name;

             END;

END;

/

Pl/SQL Program calling Package: Now we have a one package pkg1, to call package defined function, procedures also pass the parameter and get the return result.

pkg_prg.sql:

DECLARE

nonumber := &no; name

varchar2(20);

BEGIN

pkg1.pro1(no,info); dbms_output.put_line('ProcedureResult'); dbms_output.put_line(info.eno||' ‘||info.ename||' '|| info.edept||' '|| info.esalary||''||);

dbms_output.put_line('Function Result'); name := pkg1.fun1(no); dbms_output.put_line(name);

END;

/

PL/SQL Program Result:

Now execute the above created pkg_prg.sqlprogram to asking which user information you want to get, you put user id and give information.

SQL>@pkg_prgno number: &n=2

Procedure Result

2            marksjems      ProgramDeveloper     38K FunctionResult

marksjems

PL/SQL procedure successfully completed.

 

9. Explain Exception handling in PL/sql?

A. PL/SQL exceptions are predefined and raised automatically into oracle engine when any error occur during a program.

Each and every error has defined a unique number and message. When warning/error occur in program it's called an exception to contains information about the error.

In PL/SQL built in exceptions or you make user define exception. Examples of built-in type (internally) defined exceptions division by zero, out of memory. Some common built-in exceptions have predefined names such as ZERO_DIVIDE and STORAGE_ERROR. Normally when exception is fire, execution stops and control transfers to the exception- handling part of your PL/SQL block. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which are also raise predefined exceptions.

PL/SQL exceptions consist following three,

1.             ExceptionType

2.             ErrorCode

3.             ErrorMessage

Syntax:

DECLARE

declaration statement(s);

BEGIN

statement(s);

EXCEPTION

WHEN built-in_exception_name_1 THEN

User defined statement (action) will be taken; WHEN built-in_exception_name_2 THEN

User defined statement (action) will be taken;

END;

 

 

 

 

Example:

DECLARE

temp  enum%rowtype;

BEGIN

SELECT * INTO temp FROM enum WHERE eno=3;

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line("Table haven't data");

END;

/

8. Explain Database Trigger? What are the types of Trigger?

A database trigger is a stored procedure i.e. field when an insert, update or delete statement is executed against the associated table database triggers can be used for the following purpose.

1. To generate the data automatically.

2. To generate the complex security authorization.

3. To maintain duplicate tables.

Syntax:

CREATE OR REPLACE TRIGGER triggername [BEFORE OR AFTER] (INSERT OR UPDATE OR DELETE) ON tablename [FOR EACH ROW] [DELCARE]

[variable datatype:=initial value]

BEGIN

PL/SQL statements;

END;

The trigger timing:-BEFORE or AFTER. This timing indicates when the trigger’s PL/SQL code executes, in this case, before or after triggering statement is completed.

Event:-The statement that causes the trigger to execute (INSERT, UPDATE OR DELETE)

Triggering Level: It is 2 types.

a. Statement level trigger: It is assumed if you omit the FOR EACH ROW keywords. This type of trigger is executed once, before or after the triggering statement is completed. This is the default case.

b. Row level trigger: It requires FOR EACH ROW keywords. This type of trigger is executed once for each row affected by the triggering statement.

Triggering action: The PL/SQL code enclosed between the BEGIN and end keywords. Each statement inside the PL/SQL code must end with a semicolon.

CREATE TRIGGER t1 AFTER UPDATE OR DELETE ON TEMP FOR EACH ROW

DECLARE

TRANS VARCHAR2(20);

BEGIN TRANS:=TO_CHAR(SYSDATE,'DAY');

IF TRANS='SAT' THEN

RAISE_APPLICATION_ERROR(-20008, 'transactions are not possible on saturday'); END IF;

END; /

OUTPUT :

SQL> @P14.SQL;

Trigger created.

Types of Triggers:

1.              BEFORE Trigger: BEFORE trigger execute before the triggering DML statement (INSERT, UPDATE, DELETE) execute. Triggering SQL statement is may or may not execute, depending on the BEFORE trigger conditions block.

2.              AFTER Trigger: AFTER trigger execute after the triggering DML statement (INSERT, UPDATE, DELETE) executed. Triggering SQL statement is execute as soon as followed by the code of trigger before performing Database operation.

3.              ROW Trigger: ROW trigger fire for each and every record which are performing INSERT, UPDATE, DELETE from the database table. If row deleting is define as trigger event, when trigger file, deletes the five rows each times from the table.

4.              Statement Trigger: Statement trigger fire only once for each statement. If row deleting is define as trigger event, when trigger file, deletes the five rows at once from the table.

5.              Combination Trigger: Combination trigger are combination of two trigger type,

Ø  Before Statement Trigger: Trigger fire only once for each statement before the triggering DML statement.

Ø  Before Row Trigger: Trigger fire for each and every record before the triggering DML statement.

Ø  After Statement Trigger: Trigger fire only once for each statement after the triggering DML statement executing.

Ø  After Row Trigger: Trigger fire for each and every record after the triggering DML statement executing.

 

 

 

 

 

PRACTICAL NO: 1

1. Create a student table.  Columns are sno, sname course, medium and fee.

SQL> CREATE  TABLE STUDENT(SNO NUMBER(3) PRIMARY KEY, SNAME VARCHAR2(10),COURSE VARCHAR2(10), MEDIUM VARCHAR2(2), FEE NUMBER(7,2));

Table created.

2. Add address column to student table.

SQL> ALTER TABLE  STUDENT ADD(ADDRESS VARCHAR2(10));

Table altered.

3. Increase size of sname column and decrease the size of course column.

SQL> ALTER   TABLE STUDENT MODIFY(SNAME VARCHAR2(13), COURSE VARCHAR2(5));

Table altered.

4. Delete address column from student table.

SQL> ALTER TABLE STUDENT DROP COLUMN ADDRESS;

Table altered.

5. Display structure of a student table.

SQL> DESCRIBE STUDENT;

6. Delete a student table.

SQL> DROP TABLE STUDENT;

Table dropped.

PRACTICAL NO 2

1.Create customer table by using the following details:

Ccode- customer numbers can not exceed 1000, primary key

Cname- accepts upper case letters.

Address-default value is VJA.

SQL>CREATE TABLE CUSTOMER(CCODE NUMBER(5) PRIMARY KEY CHECK(CCODE<=1000), CNAME VARCHAR2(10) CHECK(CNAME=UPPER(CNAME)),

             ADDRESS VARCHAR2(10) DEFAULT 'VJA');

Table created.

2.Create product table by using the following details.

Pcode- product codes are 101, 102, 103, primary key

Pname- accepts lower case letters.

Price- default value is 0.00

SQL> CREATE TABLE PRODUCT(

PCODE NUMBER(5) PRIMARY KEY CHECK(PCODE IN(101,102,103)),

PNAME VARCHAR2(10) CHECK(PNAME=LOWER(PNAME)),

PRICE NUMBER(10,2) DEFAULT 0.00);

Table created.

3.Create invoice table by using the following details.

Iday-date default is current date.

Icode- unique

C'code-foreign key, reference table CUSTOMER

Pcode- foreign key, reference table PRODUCT

Qty- default value is 0

SQL> CREATE TABLE INVOICE(IDAY DATE DEFAULT SYSDATE,

  ICODE NUMBER(5) UNIQUE, CODE NUMBER(5) REFERENCES CUSTOMER(CCODE),

    PCODE NUMBER(5) REFERENCES PRODUCT(PCODE),

    QTY NUMBER(5) DEFAULT 0);

Table created.

4. Inserting the following details in the customer table.

Ccode	Cname	Address
1	MAHESH	Hyd
2	GANESH
3	RAJESH

SQL> INSERT INTO CUSTOMER VALUES(1,'MAHESH','HYD');

1 row created.

SQL> INSERT INTO CUSTOMER(CCODE, CNAME) VALUES (2,'GANESH');

1 row created.

SQL> INSERT INTO CUSTOMER(CCODE, CNAME) VALUES(3,'RAJESH');

1 row created.

5. Inserting the following details in the product table.

Pcode	Pname	Price
101	lux	30
102	rin	20
103	santoor	25

SQL> INSERT INTO PRODUCT VALUES(&PCODE, '&PNAME', &PRICE);

Enter value for pcode: 101

Enter value for pname: lux

Enter value for price: 30

old   1: INSERT INTO PRODUCT VALUES(&PCODE, '&PNAME', &PRICE)

new   1: INSERT INTO PRODUCT VALUES(101, 'lux', 30)

1 row created.

SQL> /

Enter value for pcode: 102

Enter value for pname: rin

Enter value for price: 20

old   1: INSERT INTO PRODUCT VALUES(&PCODE, '&PNAME', &PRICE)

new   1: INSERT INTO PRODUCT VALUES(102, 'rin', 20)

1 row created.

SQL> /

Enter value for pcode: 103

Enter value for pname: santoor

Enter value for price: 25

old   1: INSERT INTO PRODUCT VALUES(&PCODE, '&PNAME', &PRICE)

new   1: INSERT INTO PRODUCT VALUES(103, 'santoor', 25)

1 row created.

6. Inserting the following details in the invoice table.

Iday	Icode	Ccode	Pcode	Qty
01-01-2011	1	1	101	3
01-01-2011	2	1	102	3
01-01-2011	3	2	101	3
02-01-2011	4	2	101	3
02-01-2011	5	2	103	3

SQL> INSERT INTO INVOICE VALUES('&IDAY',&ICODE,&CCODE,&PCODE,&QTY);

Enter value for iday: 01-JAN-2011

Enter value for icode: 1

Enter value for ccode: 1

Enter value for pcode: 101

Enter value for qty: 3

old   1: INSERT INTO INVOICE VALUES('&IDAY',&ICODE,&CCODE,&PCODE,&QTY)

new   1: INSERT INTO INVOICE VALUES('01-JAN-2011',1,1,101,3)

1 row created.

SQL> /

Enter value for iday: 01-JAN-2011

Enter value for icode: 2

Enter value for ccode: 1

Enter value for pcode: 102

Enter value for qty: 3

old   1: INSERT INTO INVOICE VALUES('&IDAY',&ICODE,&CCODE,&PCODE,&QTY)

new   1: INSERT INTO INVOICE VALUES('01-JAN-2011',2,1,102,3)

1 row created.

SQL> /

Enter value for iday: 01-JAN-2011

Enter value for icode: 3

Enter value for ccode: 2

Enter value for pcode: 101

Enter value for qty: 3

old   1: INSERT INTO INVOICE VALUES('&IDAY',&ICODE,&CCODE,&PCODE,&QTY)

new   1: INSERT INTO INVOICE VALUES('01-JAN-2011',3,2,101,3)

1 row created.

SQL> /

Enter value for iday: 01-JAN-2011

Enter value for icode: 4

Enter value for ccode: 2

Enter value for pcode: 101

Enter value for qty: 3

old   1: INSERT INTO INVOICE VALUES('&IDAY',&ICODE,&CCODE,&PCODE,&QTY)

new   1: INSERT INTO INVOICE VALUES('01-JAN-2011',4,2,101,3)

1 row created.

SQL> /

Enter value for iday: 02-JAN-2011

Enter value for icode: 5

Enter value for ccode: 2

Enter value for pcode: 103

Enter value for qty: 3

old   1: INSERT INTO INVOICE VALUES('&IDAY',&ICODE,&CCODE,&PCODE,&QTY)

new   1: INSERT INTO INVOICE VALUES('02-JAN-2011',5,2,103,3)

1 row created.

PRACTICAL  NO 3

1. Create Inventory table by using the following details: code-primary key, des-unique

SQL> CREATE TABLE INVENTORY( CODE NUMBER(3) PRIMARY KEY, DES VARCHAR2(10) UNIQUE);

Table created.

2. Adding the following details into the product table.

Code	Des
101	Monitors
102	Printers
103	CPU
104	Keyboards
108	Mouses

SQL> INSERT INTO INVENTORY VALUES(101,'MONITORS');

1 row created.

SQL> INSERT INTO INVENTORY VALUES(102, 'PRINTERS');

1 row created.

SQL> INSERT INTO INVENTORY VALUES(103,'CPU');

1 row created.

SQL> INSERT INTO INVENTORY VALUES(104, 'KEYBOARDS');

1 row created.

SQL> INSERT INTO INVENTORY VALUES(108, 'MOUSES');

1 row created.

3.Adding null values into the inventory table.

Code	Desc
106	NULL
107	NULL

SQL> INSERT INTO INVENTORY VALUES(106,NULL);

1 row created.

SQL> INSERT INTO INVENTORY VALUES(107, NULL);

1 row created.

4.Updating the table by using the following details

Column	New value	Condition
Des	Modems	Monitors
Code	105	Mouse’s

SQL> UPDATE INVENTORY SET DES='MODEMS' WHERE DES='MONITORS';

1 row updated.

SQL> UPDATE INVENTORY SET CODE=105 WHERE DES='MOUSES';

1 row updated.

5. Saving table changes.

SQL> COMMIT;

Commit complete.

6. Delete all records from inventory table

SQL> DELETE FROM INVENTORY;

7 rows deleted.

 

PRACTICAL  NO 4

AIM:  Join operators and views

1. List the employee numbers, names, department numbers, department names by using join operator.

SQL> select empno, ename from emp UNION select deptno,dname from dept;

2. Display the different designations independent no:20 & 30 using union.

SQL> select distinct(job) from emp where deptno=20 UNION select distinct(job) from emp          

                                                                                                                       where deptno=30;

 

3. List out which are common to deptno 20 and 30 using INTERSECT.

SQL> select distinct(job) from emp where deptno=20 INTERSECT select distinct(job) from emp

                                                                                                           where deptno=30;

4. List the employee names those are not working in 20^th department by using MINUS.

SQL> select ename from emp MINUS select ename from emp where deptno=20;

 

5. List the names of the employees drawing the highest salary using SUB QUERIES.

SQL> select ename from emp where sal=(select max(sal) from emp);

6. List the name of the employee who claims the minimum salary using SUB QUERIES.

SQL>  select ename from emp where sal=(select min(sal) from emp);

7. Create a view which contains employee name and their manager name working in sales department.

SQL> CREATE VIEW SALES AS SELECT ENAME, MGR FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

View created.

PRACTICAL  NO 5

AIM:  Describe the SELECT statement.

1. Displays all  records and all columns.

SQL> SELECT * FROM EMP;

2. Displays empno and sal from emp table by using exists operator condition is who are working in deptno 10.

SQL> SELECT EMPNO, SAL FROM EMP WHERE EXISTS(SELECT * FROM DEPT    

                                                                                                             WHERE DEPTNO=10);

3. List all distinct jobs in all departments.

SQL> SELECT DISTINCT(JOB), DEPTNO FROM EMP;

4. List employee names in alphabetic order.

SQL> SELECT ENAME FROM EMP ORDER BY ENAME;

5. List employee no, sal, da(15% of sal).

SQL> SELECT EMPNO, SAL, SAL*(15/100) DA FROM EMP;

6. List employee no, deptno, dname and location.

SQL> SELECT E.EMPNO, D.DEPTNO, D.DNAME, D.LOC  FROM EMP E, DEPT D 

                                                                                              WHERE E.DEPTNO=D.DEPTNO;

7. List the mgr numbers by using group by clause.

SQL> SELECT MGR, COUNT(EMPNO) FROM EMP GROUP BY MGR;

8. List salaries in descending order.

SQL> SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;

9. Grouping the mgr numbers those numbers are above 7700.

SQL> SELECT MGR FROM EMP GROUP BY MGR HAVING (MGR>7700);

PRACTICAL NO 6

AIM:  Describe operators and wild cards.

Logical operators:

1. Displays empno, name and deptno  those employees who are working in deptno 10, 30.

SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO=10 OR DEPTNO=30;

2. Displays the empno’s list who are working in deptno 30 and working as a manager.

SQL> SELECT EMPNO FROM EMP WHERE DEPTNO=30 AND JOB='MANAGER';

3. Displays all employee names and jobs  except salesman list.

SQL> SELECT ENAME, JOB FROM EMP WHERE NOT JOB='SALESMAN';

Relational operators:

4. Displays employee names who are getting above 2000.

SQL> SELECT ENAME, SAL FROM EMP WHERE SAL>2000;

5. Displays employee numbers those numbers are below 7700.

SQL> SELECT EMPNO FROM EMP WHERE EMPNO<7700;

6. Displays president details.

SQL> SELECT * FROM EMP WHERE JOB='PRESIDENT';

Special operators:

7. Displays employee names and sal who are getting more than 2000 and below 3000.

SQL> SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

8. Displays employee names who are working in 10 and 20departments by using in operator.

SQL> SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN(10, 20);

9. Displays scott details by using like operator.

SQL> SELECT EMPNO, ENAME FROM EMP WHERE ENAME LIKE 'SCOTT';

10. Determine employee numbers who are not have mgr value.

SQL> SELECT EMPNO FROM EMP WHERE MGR IS NULL;

Wild cards:

11. Displays the employee names those names are starts with J;

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE 'J%';

12. Displays the employee names those names contains the following format.  S_ _T_

SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE 'S_ _T_';

PRACTICAL NO: 7

AIM:  Describe the aggregate functions.

1. Determine the count of employee who are NOT taking commission.

SQL> SELECT COUNT(ENAME) FROM EMP WHERE NOT(COMM IS NULL);

2. Displays no of employees are working in each department.

SQL> SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;

3. Determine highest salary who are working as manager post.

SQL> SELECT MAX(SAL) FROM EMP WHERE JOB='MANAGER';

4. Determine the lowest salary in the deptno 20.

SQL> SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20;

5. Displays total expenditure of the deptno 30 employees.

SQL> SELECT SUM(SAL) FROM EMP WHERE DEPTNO=30;

6. Average of the employee salaries.

SQL> SELECT AVG(SAL) FROM EMP;

PRACTICAL NO 8

AIM:  Describe the DATE functions.

1. Displays the current date.

SQL> SELECT SYSDATE FROM DUAL;

2. Displays the date which is the next Friday of the current date.

SQL> SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;

3. Displays the date which is the last day of the current month.

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;

4. List the employee names having an experience in months.

SQL> SELECT ENAME, MONTHS_BETWEEN(SYSDATE, HIREDATE) EXPERIENCEINMONTHS FROM EMP;

5. List the employee names having an experience more than  24 years.

SQL> SELECT ENAME, MONTHS_BETWEEN(SYSDATE, HIREDATE)/12 EXPERIENCE FROM EMP WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE)/12>24;

6. To convert the given characters into date data type. Eg: 01-JAN-2012

SQL> SELECT TO_DATE('01-JAN-2012') FROM DUAL;

7. Rounding the given year. 10-07-2012.

SQL> SELECT ROUND( TO_DATE('01-JUL-2012'), 'YYYY')  FROM DUAL;

8. Round the given month. 17-01-2012.

SQL> SELECT ROUND( TO_DATE('17-JAN-2012'),'MONTH') FROM DUAL;

 

9. Rounding the first day of the given week. Ex: 17-JAN-2012.

SQL> SELECT ROUND( TO_DATE('17-JAN-2012'),'WW') FROM DUAL;

10. To convert the given characters into the following format. Ex: 01-12-2011  dd/mm/yy

SQL> SELECT TO_DATE('01/12/2011','DD/MM/YY') FROM DUAL;

PRACTICAL NO: 9

AIM:  Describe the STRING functions.

1. Displays the 10^th department employee names in upper case.

SQL> SELECT UPPER(ENAME) FROM EMP WHERE DEPTNO=10;

2. Displays the 20^th department job names in lower case.

SELECT LOWER(JOB) FROM EMP WHERE DEPTNO=20;

3. Displays president name in initial capital letters.

SQL> SELECT INITCAP(ENAME) FROM EMP WHERE JOB='PRESIDENT';

4. Displays the first 3 letters of the department location.

SQL> SELECT SUBSTR(LOC, 1, 3) FROM DEPT;

5. Displays ascii code of A.

SQL> SELECT ASCII('A') FROM DUAL;

6. Displays the position of  string 1-‘com’ in string2-‘super computer’.

SQL> SELECT INSTR('SUPER COMPUTER', 'COM', 5) FROM DUAL;

7. Displays length of the department location.

SQL> SELECT LOC, LENGTH(LOC) FROM DEPT;

8. Give an example of ltrim.

SQL> SELECT '     SQL           ', LTRIM('       PL/SQL') FROM DUAL;

9. Give an example of trim and rtrim.

SQL> SELECT TRIM('     STRUCTURED      '),RTRIM('PROGRAMMING LANGUAGE          ') FROM DUAL;

10. Give an example of lpad and rpad.

SQL> SELECT LPAD('COMPUTER',10,'*') FROM DUAL;

SQL> SELECT RPAD('COMPUTER',20,'X') FROM DUAL;

11. Describe vsize function.

SQL> SELECT VSIZE('COMPUTER') FROM DUAL;

PRACTICAL NO 10

AIM:  Describe the NUMERIC functions.

1. Displays the absolute value of -25.

SQL> SELECT ABS(-25) FROM DUAL;

2. Calculate 5³

SQL> SELECT POWER(5, 3) FROM DUAL;

3. Rounding the 2digits of the 30.2345 value.

SQL> SELECT ROUND(30.2345, 2) FROM DUAL;

4. Displays square root value of 25.

SQL> SELECT SQRT(25) FROM DUAL;

5. Displays exponent value of the 5 value.

SQL> SELECT EXP(5) FROM DUAL;

6. Displays year, month, day of the current date.

SQL> SELECT EXTRACT(YEAR FROM SYSDATE) YEAR, EXTRACT(MONTH FROM SYSDATE) MONTH, EXTRACT(DAY FROM SYSDATE) DAY FROM DUAL;

7. Displays biggest value of 10,20,30

SQL> SELECT GREATEST(10,20,30) FROM DUAL;

8. Displays smallest value of 100.5,200.6,100.7

SQL> SELECT LEAST(100.5, 200.6, 100.7) FROM DUAL;

9. Displays modular value of 10,3.

SQL> SELECT MOD(10,3) FROM DUAL;

10. Describe the truncate function.

SQL> SELECT TRUNC(125.876, 1) OUTPUT1, TRUNC(125.876, -2) OUTPUT2 FROM DUAL;

11. Displays floor value of 25.89.

SQL> SELECT FLOOR(25.89) FROM DUAL;

12. Displays ceil values of 25.678 and 25.11

SQL>  SELECT CEIL(25.678) OUTPUT1, CEIL(25.11) OUTPUT2 FROM DUAL;

PL / SQL PRACTICALS

PRACTICAL NO 1

AIM:  Write a PL/SQL program to check the given number is strong or not.

DECLARE

            N NUMBER:=&N;

            R NUMBER;

            F NUMBER;

            S NUMBER;

            TEMP number;

BEGIN

            S:=0;

            TEMP:=N;

            WHILE N>0

            LOOP

                        R:=MOD(N,10);

                        F:=1;

                        WHILE R>0

                        LOOP

                                    F:=F*R;

                                    R:=R-1;

                        END LOOP;

                        S:=S+F;

                        N:=TRUNC(N/10);

            END LOOP;

            IF TEMP=S THEN

                        DBMS_OUTPUT.PUT_LINE('STRONG' );

            ELSE

                        DBMS_OUTPUT.PUT_LINE('NOT STRONG' || S);

            END IF;

END;

/

Output 1:

SQL> @P1.SQL;

Enter value for n: 145

old   2:        N NUMBER:=&N;

new   2:        N NUMBER:=145;

STRONG

PL/SQL procedure successfully completed.

Output 2:

SQL> /

Enter value for n: 123

old   2:        N NUMBER:=&N;

new   2:        N NUMBER:=123;

NOT STRONG9

PL/SQL procedure successfully completed.

PRACTICAL NO 2

AIM:  Write a PL/SQL program to check the given string is palindrome or not.

Declare

            S1 VARCHAR2(20):='&S1';

            S2 VARCHAR2(20);

            L NUMBER;

Begin

            S1:=TRIM(S1);

            L:=LENGTH(S1);

            WHILE(L>0)

            LOOP

                        S2:=S2||SUBSTR(S1,L,1);

                        L:=L-1;

            END LOOP;

            IF(S1=S2) THEN

            DBMS_OUTPUT.PUT_LINE('It is a Palindrome');

            ELSE

            DBMS_OUTPUT.PUT_LINE('It is  not a Palindrome'||S2);

            END IF;

END;

/

OUTPUT 1:

SQL> @P2.SQL;

Enter value for s1: TRIVENI

old   2:        S1 VARCHAR2(20):='&S1';

new   2:        S1 VARCHAR2(20):='TRIVENI';

It is  not a PalindromeINEVIRT

PL/SQL procedure successfully completed.

OUTPUT 2:

SQL> /

Enter value for s1: LIRIL

old   2:        S1 VARCHAR2(20):='&S1';

new   2:        S1 VARCHAR2(20):='LIRIL';

It is a Palindrome

PL/SQL procedure successfully completed.

PRACTICAL  NO 3

AIM:  Write a PL/SQL program to swap two numbers without using third variable.

Declare

            A number:=&a;

            B number:=&b;

Begin

            DBMS_OUTPUT.PUT_LINE('Before swaping');

            DBMS_OUTPUT.PUT_LINE('A=' ||A||  'B='||B);

            A:= A+B;

            B:=A-B;

            A:=A-B;

            DBMS_OUTPUT.PUT_LINE('After swaping');

            DBMS_OUTPUT.PUT_LINE('A value=' ||A|| 'B value' || B);

END;

/

OUTPUT 1:

SQL> @p3.sql;

Input truncated to 1 characters

Enter value for a: 23

old   2:        A number:=&a;

new   2:        A number:=23;

Enter value for b: 32

old   3:        B number:=&b;

new   3:        B number:=32;

Before swaping

A=23B=32

After swaping

A value=32B value23

PL/SQL procedure successfully completed.

OUTPUT 2:

SQL> /

Enter value for a: 10

old   2:        A number:=&a;

new   2:        A number:=10;

Enter value for b: 20

old   3:        B number:=&b;

new   3:        B number:=20;

Before swaping

A=10B=20

After swaping

A value=20B value10

PL/SQL procedure successfully completed.

PRACTICAL NO: 4

AIM:  Write a PL/SQL program to generate multiplication tables fro 2, 4, 6

Declare

            T number:=2;

            I number;

            N number:=&N;

Begin

            WHILE T<=4

            LOOP             I:=1;

                        WHILE I<=N

                        LOOP

                                    DBMS_OUTPUT.PUT_LINE(T || 'X' || I || '=' || (T*I));

                                    I:=I+1;

                        END LOOP;

                        T:=T+2;

            END LOOP;

END; /

OUTPUT 1:

SQL> @p4.sql;

Enter value for n: 10

old   4:        N number:=&N;

new   4:        N number:=10;

2X1=2

2X2=4

2X3=6

2X4=8

2X5=10

2X6=12

2X7=14

2X8=16

2X9=18

2X10=20

4X1=4

4X2=8

4X3=12

4X4=16

4X5=20

4X6=24

4X7=28

4X8=32

4X9=36

4X10=40

PRACTICAL NO 5

AIM:  Write a PL/SQL program to display sum of even numbers and sum of odd numbers in the given range.

DECLARE

            N NUMBER:=&N;

            ES NUMBER:=0;

            OS NUMBER:=0;

            I NUMBER:=1;

BEGIN

            WHILE I<=N

            LOOP

                        IF MOD(I,2) =0 THEN

                                    ES:=ES+I;

                        ELSE

                                    OS:=OS+I;

                        END IF;

                        I:=I+1;

            END LOOP;

            DBMS_OUTPUT.PUT_LINE('SUM OF EVEN NUMBERS=' || ES);

            DBMS_OUTPUT.PUT_LINE('SUM OF ODD NUMBERS=' || OS);

END;

/

OUTPUT 1:

 

SQL> @p5.sql;

Input truncated to 1 characters

Enter value for n: 10

old   2:        N NUMBER:=&N;

new   2:        N NUMBER:=10;

SUM OF EVEN NUMBERS=30

SUM OF ODD NUMBERS=25

PL/SQL procedure successfully completed.

OUTPUT  2:

SQL> /

Enter value for n: 5

old   2:        N NUMBER:=&N;

new   2:        N NUMBER:=5;

SUM OF EVEN NUMBERS=6

SUM OF ODD NUMBERS=9

PL/SQL procedure successfully completed.

PRACTICAL NO: 6

AIM:  Write a PL/SQL program to check the given number is palindrome or not.

DECLARE

            N NUMBER:=&N;

            RN NUMBER:=0;

            R NUMBER;

            TEMP NUMBER;

BEGIN

            TEMP:=N;

            WHILE N>0

            LOOP

                        R:=MOD(N,10);

                        RN:=(RN*10)+R;

                        N:=TRUNC(N/10);

            END LOOP;

            IF TEMP=RN THEN

                        DBMS_OUTPUT.PUT_LINE('PALINDROME');

            ELSE

                        DBMS_OUTPUT.PUT_LINE('NOT PALINDROME');

            END IF;

END;

/

OUTPUT 1:

SQL> @p6.sql;

Enter value for n: 123

old   2:        N NUMBER:=&N;

new   2:        N NUMBER:=123;

NOT PALINDROME

PL/SQL procedure successfully completed.

OUTPUT 2:

Input truncated to 2 characters

SQL> /

Enter value for n: 121

old   2:        N NUMBER:=&N;

new   2:        N NUMBER:=121;

PALINDROME

PL/SQL procedure successfully completed.             

TRIGGERS PRACTICAL NO:7

DESCRIPTION OF TRIGGERS:

A database trigger is a stored procedure i.e. field when an insert, update or delete statement is executed against the associated table database triggers can be used for the following purpose.

1.To generate the data automatically.

2.To generate the complex security authorization.

3.To maintain duplicate tables.

Syntax:

CREATE OR REPLACE TRIGGER  triggername [BEFORE OR AFTER]

(INSERT OR UPDATE  OR DELETE) ON tablename [FOR EACH ROW]

[DELCARE]

            [variable datatype:=initial value]

BEGIN

            PL/SQL statements;

            ----

END;

The trigger timing:-  BEFORE or AFTER.  This timing indicates when the trigger’s PL/SQL code execute, in this case, before or after triggering statement is completed.

Event:-  The statement that causes the trigger to execute (INSERT, UPDATE OR DELETE)

Triggering Level:-  It is 2 types.

a.Statement level trigger:-  It is assumed if you omit the FOR EACH ROW keywords.  This type of trigger is executed once, before or after the triggering statement is completed.  This is the default case.

b.Row level trigger:-  It requires  FOR EACH ROW keywords.  This type of trigger is executed once for each row affected by the triggering statement.

Triggering action:-  The PL/SQL code enclosed between the BEGIN and end keywords.  Each statement inside the PL/SQL code must end with a semicolon.

 

CREATE TRIGGER t1 AFTER UPDATE OR DELETE ON TEMP

FOR EACH ROW

DECLARE

TRANS VARCHAR2(20);

BEGIN

TRANS:=TO_CHAR(SYSDATE,'DAY');

IF TRANS='SAT' THEN

            RAISE_APPLICATION_ERROR(-20008, 'transactions are not possible on saturday');

END IF;

END; /

OUTPUT :

SQL> @P14.SQL;

Trigger created.

 

FUNCTIONS PRACTICAL NO: 8

AIM:  Write a function to calculate the total salary

SQL>ED P19.SQL;

CREATE OR REPLACE FUNCTION f1(a number)

RETURN NUMBER IS

b number;

c number;

tot number;

BEGIN

SELECT sal, comm INTO b, c FROM emp where empno=a;

tot:=b+c;

RETURN(tot);

END;

/

OUTPUT:

SQL> @P19.SQL;

Function created.

PRACTICAL NO: 9

AIM:  Write a PL/SQL program to call user defined function.

DECLARE

a number;

b number;

BEGIN

a:=&empoyeenumber;

b:=f1(a);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY:'||b);

END;

/

OUTPUT:

SQL> @P20.SQL;

Enter value for empoyeenumber: 7499

old   6: a:=&empoyeenumber;

new   6: a:=7499;

TOTAL SALARY:5701.18

PL/SQL procedure successfully completed.