rdbms solved assignment

• ▼MySQL Exercises
• Introduction
• ▼DML and DDL
• Create Table statement
• Insert Into statement
• Update Table statement
• Alter Table statement
• ▼Exercises on HR Database
• Basic SELECT statement
• Restricting and Sorting Data
• MySQL Aggregate Functions
• ▼Exercises on Northwind Database
• Products Table

MySQL Exercises, Practice, Solution

What is mysql.

MySQL is the world's most widely used open-source relational database management system (RDBMS), enabling the cost-effective delivery of reliable, high-performance and scalable Web-based and embedded database applications. It is widely-used as the database component of LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack.

The best way we learn anything is by practice and exercise questions. We have started this section for those (beginner to intermediate) who are familiar with SQL and MySQL . Hope, these exercises help you to improve your MySQL query skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

Exercises on Data Manipulation Language (DML) & Data Definition Language (DDL)

• MySQL Create Table statement [20 Exercises]
• MySQL Insert Into statement [14 Exercises]
• MySQL Update Table statement [9 Exercises]
• MySQL Alter Table statement [15 Exercises]

Exercises on HR Database

• MySQL Basic SELECT statement [19 Exercises]
• MySQL Restricting and Sorting Data [11 Exercises with Solutions]
• MySQL Aggregate Functions and Group by [14 Exercises with Solutions]
• MySQL Subqueries [22 Exercises with Solution ]
• MySQL JOINS [13 Exercises with Solution]
• MySQL Date Time [21 Exercises with Solution]
• MySQL String Functions [17 Exercises with Solution]

Exercises on Northwind Database

• Exercises on Products Table [ 10 Exercises]

More to Come !

Structure of 'hr' database :

You may download the structure and data of the database used here

Click here to get Oracle Hr Database

Structure of 'northwind' database:

Click here to get Northwind sample databases for Microsoft SQL Server.

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics

Tutorialwing

DBMS Relational Algebra Examples With Solutions

Table of content.

• Getting Started
• Select Operation
• Project Operation
• Union Operation
• Set Difference Operation
• Cartesian Product Operation
• Rename Operation

Relational algebra is procedural query language used to query the database in various ways. In other words, Relational Algebra is a formal language for the relational mode. A data model must also include a set of operations to manipulate, retrieve the data in the database, in addition to defining the database structure and constructs.

The relational algebra operation enables a user to specify basic retrieval requests for data from the database.

The results of retrieval are a new relation, which may have been formed from one or more relations. Sequences of relational algebra operators form a relational algebra expression, whose result is a new relation that represents the result of a database query (retrieval query).

Relational algebra provides the foundation of relational model operation and it’s used as the basic for implementing and optimising queries in RDBMS.

Fundamental Operations –

Fundamental operations on relational algebra are as below –

• Select operation
• Project operation
• Union operation
• Set difference operation
• Cartesian product operation
• Rename operation

The project, rename and select operations are called unary operations because they operate on one relation. Operations such as Union, Set Difference and Cartesian product operate on two relations. Therefore, they are called binary operations.

Note – We are going to use below relation tables to show different dbms relational algebra examples.

Player relation

Deposit relation

Borrower relation

R-Schema(id, name)

R – relation.

S-Schema(id, name)

S – relation.

(1) Select Operation (σ)

The select operation selects the tuples (rows) that satisfy the given predicate (condition). The lower case Greek letter Sigma (σ) is used to represent the select operation.

The predicate appears as a subscript to σ and argument relation is given in parenthesis following σ. Predicates can be defined using the operators =, !=, , >= etc. and they may be connected by using the connectives.

Notation of Selection Operation

Where, σ is predicate, r stands for relation (name of the table). p is the prepositional logic.

Exercises –

Question A. Find all tuples from player relation for which country is India.

Result –

Question B. Select all the tuples for which runs are greater than or equal to 15000.

Question C . Select all the players whose runs are greater than or equal to 6000 and age is less than 25

(2) Project Operation (π)

Projection of a relation P (P-Schema) on the set of attributes Y is the projection of each tuple of the relation P on the set of attributes Y.

The projection operation is a unary operation and it returns its argument relation with certain attributes left out. It is denoted by a Greek letter pi (π). The attributes, which appear in the result, are listed as a subscript to π.

Notation of Project Operation

Where, A1, A2, An are attribute name of the relation r.

a. List all the countries in Player relation.

b. List all the team ids and countries in Player Relation

(3) Union Operation ( ∪ )

Compatible relations: Two relations R and S are said to be compatible relations if they satisfy following two conditions –

• The relations R and S are of same entity i.e. the number of attributes are same.
• The domains of the ith attribute of R and ith attribute of S must be same for all i.

The union of R and S is set theoretic union of R and S, if R and S are compatible relations.

It is denoted by ∪, the resultant relation P(P=R ∪ S) has tuples drawn from R and S such that a tuple in P is either in R or S or in both of them.

Notation of Union Operation

Where, R and S are relations.

Example – 1: P = R ∪ S is given by relation

(4) Set Difference Operation (-)

The set difference operation removes common tuples from the first relation. It is denoted by ‘-‘ sign. The expression R-S results in a relation containing those tuples in R but not in S. For set difference operation, relations must be compatible relations.

Notation of Set Difference Operation

Example –.

A. P = R – S is given by

A. Find all the customers having an account but not the loan.

B. Find all the customers having a load but not the account.

(5) Cartesian Product Operation ( X )

Cartesian product of two relations is the concatenation of tuples belonging to the two relations. It is denoted by ‘x’ sign. If R and S are two relations, (R X S) results in a new relation P, which contains all possible combination of tuples in R and S. For Cartesian product operation, compatible relations are not required.

Notation of Cartesian Product Operation

Where, P, R and S are relations.

X represents concatenations. The degree/arity of the resultant relation is given by

|P|=|R|=|S|

Employee-Schema = { Emp-id, Name }

Project-Schema = { Proj-name }

Find R = Employee X Project

Solution –

R-Schema = {Emp-id, Name, Proj-name}

If the attribute name is same in both argument relations, then that is distinguished by attaching the name of the relation from which the attribute originally came.

Given Customer schema = {cust-id, name} Customer

Employees Schema = {emp-id, name} Employee

Find R = Customer X Employee

Customer X Employee

6.Rename Operation

This is a unary operation. Any relational algebra expression returns a new relation, but this relation is not having a name associated with it. Using Rename operation, we can rename such result relations or if we want to change the name of a given relation, it can be changed using rename operation.

It is denoted by rho (ρ)

Notation of Rename Operation

ρ(NewName, OldName)

NewName – New name of the relation. OldName – Old name of the relation.

Question – Rename Player relation to PlayerList.

Solution – ρ(PlayerList, Player).

Question 1. Rename Customer relation to CustomerList.

Thus, we have gone through different dbms relational algebra examples.

Recommended Posts

" title=" ">.

You must be logged in to post a comment.

MySQL Tutorial

Mysql database, mysql references, mysql examples, mysql rdbms, what is rdbms.

RDBMS stands for Relational Database Management System.

RDBMS is a program used to maintain a relational database.

RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.

RDBMS uses SQL queries to access the data in the database.

What is a Database Table?

A table is a collection of related data entries, and it consists of columns and rows.

A column holds specific information about every record in the table.

A record (or row) is each individual entry that exists in a table.

Look at a selection from the Northwind "Customers" table:

The columns in the "Customers" table above are: CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. The table has 5 records (rows).

Advertisement

What is a Relational Database?

A relational database defines database relationships in the form of tables. The tables are related to each other - based on data common to each.

Look at the following three tables "Customers", "Orders", and "Shippers" from the Northwind database:

Customers Table

The relationship between the "Customers" table and the "Orders" table is the CustomerID column:

Orders Table

The relationship between the "Orders" table and the "Shippers" table is the ShipperID column:

Shippers Table

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

BCA Programming Notes

This is blog is useful for the students of BCA and BCS .

• Chapter 4:-SQL
• C++ Slip Program
• C++ Slip 2019 pattern
• Advance PHP
• Extra Program
• Advance Java (2019 pattern)
• Core Java-2019
• Advance Java
• Question Paper
• Assignments
• Java Programming
• Data Analysis
• HSC Practical
• Digital Marketing Notes
• Python Notes
• Angular Js Notes
• PHP(2019)Notes
• Advance PHP Notes
• Rdbms Notes
• Advance Java Notes
• DBMS Assignment
• PHP Assignment
• Python Assignment
• Core Assignment
• Manual Testing
• ERD and Project Design
• Project Report
• HTML/CSS/JavaScript
• Personal Blog
• Business Blog
• C Programming
• DBMS Interview Question

Slip 1   Client (client_no, client_name, address, birthdate) Policy_info (policy_no, desc, maturity_amt, prem_amt, date) Relation between Client and Policy_info is Many to Many Constraint: Primary key, prem_amt and maturity_amt should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will return total maturity amount of policies of a particular client. 2)Write a cursor which will display policy date wiseclient details. Slip 2 Consider the following Item_Supplier database         Item (itemno, itemname ) Supplier (supplier_No , supplier_name, address, city ) Relationship between Item and Supplier is many-to-many with descriptive attribute rate and quantity Constraints: itemno ,supplier_No primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write function to print the total number of suppliers of a particular item 2)Write a trigger which will fire before insert orupdate on rate and quantity less than or equal to zero. (Raise user definedexception and give appropriate message) Slip 3 Consider the following entities and their relationship.            Newspaper (name,language , publisher , cost ) Cities (pincode , city, state) Relationship between   Newspaper and Cities is many-to-many with descriptive attribute daily required Constraints: name and pincode primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message). 2)Write a procedure to calculate city wise totalcost of each newspaper Slip 4 Consider the following entities and their relationships.          Client(client_no, client_name, address, birthdate) Policy_info (policy_no, desc, maturity_amt, prem_amt, date) Relation between Client and Policy_info is Many to Many Constraint: Primary key, prem_amt and maturity_amt should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure which will display all policy details having premium amount less than 5000. 2)Write a trigger which will fire before insert orupdate on policy_info having maturity amount less than premium amount. (Raiseuser defined exception and give appropriate message) Slip 5 Consider the following entities and their relationships.          Library(Lno, Lname, Location, Librarian, no_of_books) Book(Bid, Bname, Author_Name, Price, publication) Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept publication name from user and display total price of books of that publication. 2)Write a cursor which will display library wisebook details.(Use Parameterized Cursor)       Slip 6 Consider the following entities and their relationships.          Employee (emp_id, emp_name, address) Investment (inv_no, inv_name, inv_date, inv_amount) Relation between Employee and Investment is One to Many. Constraint: Primary key, inv_amount should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure which will display details of employees invested amount in “Mutual Fund” 2)Write a cursor which will display date wiseinvestment details. Slip 7 Consider the following entities and their relationships. Bill (billno, day, tableno, total) Menu (dish_no, dish_desc, price) The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display menu details having price between 200 to 500 which were order on ‘Saturday’ . 2)Write a trigger which will fire before insert orupdate on Menu having price less than or equal to zero. (Raise user definedexception and give appropriate message) Slip 8 Consider the following entities and their relationships.          Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt) Customer (cust_no, cust_name, mobile_no) Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept plan number from user and display all the details of the selected plan 2)Write a cursor which will display customer wiseplan details.(Use Parameterized Cursor) Slip 9 Consider the following entities and their relationships.          Project (pno, pname, start_date, budget, status) Department (dno, dname, HOD, loc) The relationship between Project and Department is Many to One.   Constraint: Primary key. Project Status Constraints:C – Completed,P - Progressive, I – Incomplete Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which accept department name and display total number of projects whose status is “p”(progressive). 2)Write a cursor which will display status wiseproject details of each department. Slip 10 Consider the following entities and their relationships.          Gym (Name, city, charges, scheme) Member (ID, Name, phoneNo, address) Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept member id and scheme from user and display charges paid by that member. 2)Write a trigger which will fire before insert orupdate on Gym having charges less than 1000. (Raise user defined exception and give appropriate message)     Slip 11 Consider the following entities and their relationships.          Student (rollno, sname, class, timetable) Lab (LabNo, LabName, capacity, equipment) Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept Lab number from user and display total number of student allocated in that lab. 2)Write a cursor which will display lab wise studentdetails. Slip 12 Consider the following entities and their relationships.          Wholesaler (w_no, w_name, address, city) Product (product_no, product_name, rate) Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute. Constraint: Primary key, rate should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept wholesaler name from user and will display total number of items supplied by him. 2)Write a trigger which will fire before insert orupdate on product having rate less than or equal to zero (Raise user definedexception and give appropriate message) Slip 13 Consider the following entities and their relationships.          Country (CId, CName , no_of_states, area, location, population) Citizen( Id, Name, mother_toung, state_name) Relation between Country and Citizen is one to many. Constraint: Primary key, area should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will display name of the country having minimum population. 2)Write a cursor which will display county wisecitizen details. Slip 14 Consider the following entities and their relationships.          College (code, college_name, address) Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg) Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure which will accept teacher name from user and display his/her college details. 2)Write a trigger which will fire before insert orupdate on Teacher having salary less than or equal to zero (Raise user definedexception and give appropriate message) Slip 15 Consider the following entities and their relationships.          Driver(driver_id, driver_name, address) Car(license_no, model, year) Relation between Driver and Car is Many to Many with date and time as descriptive attribute. Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will display the total number of person who are using “Swift” car 2)Write a trigger which will fire before insert orupdate on year. If year value is more than current year. (Raise user definedexception and give appropriate message) Slip 16 Consider the following entities and their relationships.          Game (game_name, no_of_players, coach_name) Player (pid, pname, address, club_name) Relation between Game and Player is Many to Many. Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure which will display games details having number of players more than 5. 2)Write a trigger which will fire before insert orupdate on Game having no_of_players less than or equal to zero. (Raise userdefined exception and give appropriate message) Slip 17 Consider the following Item_Supplier database         Company (name , address , city , phone , share_value) Person (pname ,pcity )   Relationship between Company and Person is M to M relationship with descriptive attribute No_of_shares iConstraints: name,pname primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a trigger before insert or update on No_of_shares field should not be zero.(Raise   user       defined exception             and       give      appropriate       message) 2)Write a function to display total no_of_shares ofa specific person. Slip 18 Consider the following entities and their relationship.            Student (s_reg_no, s_name, s_class) Competition (comp_no, comp_name, comp_type)   Relationship between Student and Competition is many-to-many with descriptive attribute rank and year.   Constraints: primary key, foreign key, primary key for third table(s_reg_no, comp_no, year),s_name and comp_name should not be null,comp_type can be sports or academic. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year. 2)Write a cursor which will display year wisedetails of competitions. (Use parameterized cursor) Slip19 Consider the following entities and their relationships.          Game(game_name, no_of_players, coach_name) Player (pid, pname, address, club_name)   Relation between Game and Player is Many to Many.   Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will return total number of football players of “Sports Club”. 2)Write a cursor which will display club wisedetails of players. Slip 20 Consider the following entities and their relationships.          Driver (driver_id, driver_name, address) Car (license_no, model, year) Relation between Driver and Car is Many to Many with date and time as descriptive attribute. Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display car details used on specific day. 2)Write a cursor which will display driver wise cardetails in the year 2018. Slip 21 Consider the following entities and their relationships.          College(code, college_name, address) Teacher(teacher_id, teacher_name, Qualification, specialization, salary, Desg) Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will accept college name from user and display total number of “Ph.D” qualified teachers. 2)Write a cursor which will display college wiseteacher details. Slip22 Consider the following entities and their relationships.          Country (CId, CName , no_of_states, area, location, population) Citizen( Id, Name, mother_toung, state_name) Relation between Country and Citizen is one to many. Constraint: Primary key, area should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a   procedure   to   display name   of    citizens   having mother   toung   “Marathi   “ and            from “India”; 2)Write a trigger which will fire before insert orupdate on country  having no_of_state lessthan equal to zero. (Raise user defined exception and give appropriate message) Slip 23 Consider the following entities and their relationships.          Wholesaler (w_no, w_name, address, city) Product (product_no, product_name, rate) Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute. Constraint: Primary key, rate should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure which will display details of products supplied by “Mr. Patil” 2)Write a cursor which will display wholesaler wiseproduct details.(Use Parameterized cursor) Slip 24 Consider the following entities and their relationships.          Student (rollno, sname, class, timetable) Lab (LabNo, LabName, capacity, equipment) Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display details of students which perform practical sessions in a given Lab. 2)Write a trigger which will fire before delete on Lab (Raise user defined exception and give appropriate message) Slip 25 Consider the following entities and their relationships.          Gym (Name, city, charges, scheme) Member (ID, Name, phoneNo, address) Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display member details of gym located at “Pimpri’” 2)Write a cursor which will display gym wise member details.(Use Parametrized Cursor) Slip 26 Consider the following entities and their relationships.          Project (pno, pname, start_date, budget, status)   Department (dno, dname, HOD, loc) The relationship between Project and Department is Many to One. Constraint: Primary key.Project Status Constraints:      C – Completed,P - Progressive, I – Incomplete Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display the name of HOD who has completed maximum project. 2)Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message) Slip 27 Consider the following entities and their relationships.          Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt) Customer (cust_no, cust_name, mobile_no) Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display the plan having minimum response. 2)Write a trigger which will fire before insert orupdate on mobile number having length less than or greater than10. (Raise userdefined exception and give appropriate message) Slip 28 Consider the following entities and their relationships.          Bill (billno, day, tableno, total) Menu (dish_no, dish_desc, price) The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which accept a table number and display total amount of bill for a specific table 2)Write a cursor which will display table wise menudetails. Slip 29 Consider the following entities and their relationships.          Employee (emp_id, emp_name, address) Investment (inv_no, inv_name, inv_date, inv_amount) Relation between Employee and Investment is One to Many. Constraint: Primary key, inv_amount should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a function which will return total investment amount of a particular client. 2)Write a trigger which will fire before insert orupdate on Investment having investment amount less than 50000. (Raise userdefined exception and give appropriate message) Slip 30   Consider the following entities and their relationships.         Library(Lno, Lname, Location, Librarian, no_of_books) Book(Bid, Bname, Author_Name, Price, publication) Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1)Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”. 2)Write a trigger which will fire before insert orupdate on book having price less than or equal to zero. (Raise user definedexception and give appropriate message)

Social Profiles

• Blog Archives

Popular Posts

• PHP-SEM3   1]Write a PHP script for the following: Design a form to accept a string. Write a function to count the total number of vowels (a,e,i,o,u)...
• AngularJS   1)Write an AngularJS script to display list of games stored in an array on click of button using ng-click. And also   Demonstrate ng-init,...
• RDS-1 Slip 1   Client (client_no, client_name, address, birthdate) Policy_info (policy_no, desc, maturity_amt, prem_amt, date) Relati...
• WT-1 Slip1 Q1. Write a JavaScript program to calculate the volume of a sphere.                                        <html> ...
• cpp-2019-slip1A   #include<iostream.h> #include<conio.h> class max { public:   inline int maximum(int a,int b) { return a>b?a...
• Web Technology (1)

Total Pageviews

One stop guide to computer science students for solved questions, Notes, tutorials, solved exercises, online quizzes, MCQs and more on DBMS, Advanced DBMS, Data Structures, Operating Systems, Machine learning, Natural Language Processing etc.

TOPICS (Click to Navigate)

• Advanced Database Concepts
• Data structures, Operating Systems
• Natural Language Processing
• Quiz Questions and Answers
• DBMS, ADBMS Question Bank
• RDBMS Exam and Interview Questions
• Parallel Databases
• ADBMS Quizzes
• Advanced DBMS Concepts
• Distributed Databases
• Modern Databases - Special Purpose Databases
• Object Based Database Systems

Monday, April 27, 2020

Relational algebra in database management systems solved exercise, relational algebra – solved exercise.

Related links:

• Go to DBMS - solved exercises page 
• Go to DBMS - Multiple Choice Questions (MCQs) page

solved exercises in dbms

Solved exercises in relational algebra, solved exercises in sql, sql and relational algebra short answers, sql and relational algebra short exercises, relational algebra exercises with answers explained, 6 comments:.

Is that last RA query wrong ? .. I think you forgot to filter upon the Jet agency

Thanks. Corrected

OMG THANK YOU SO MUCH

nyc presentation

in question (i) the arity for set difference must be same arity(agency ⨝ (Π aid (agency)) is not equal to arity(Π aid (σ pid = 123 (booking)))

You are correct. But you missed the set of parenthesis. Please check the RA expression once again. Thanks.

Featured Content

Multiple choice questions in natural language processing home.

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

• Relational algebra in database management systems solved exercise Relational algebra in database management systems solved exercise Relational algebra – solved exercise Question: Consider the fo...
• Machine Learning Multiple Choice Questions and Answers Home Top 5 Machine Learning Quiz Questions with Answers explanation, Interview questions on machine learning, quiz questions for data scienti...
• Machine Learning Multiple Choice Questions and Answers 01 Top 5 Machine Learning Quiz Questions with Answers explanation, Interview questions on machine learning, quiz questions for data scientist...
• Normalization - Solved exercises Home Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
• Information Retrieval MCQs with Answers home page Information retrieval multiple choice questions with answers, IR MCQs with answers, solved information retrieval questions, information retr...

FYBBA(CA) Sem - II RDBMS Lab Assignment

  FYBBA(CA) PL-SQL LAB BOOK ALL ASSIGNMENT SOLUTIONS

Posted by Siddharth Sarode

You may like these posts, post a comment, social plugin, search this blog, most popular.

• DS assignment
• File handling
• Linked List
• Navbar html css

Footer Menu Widget

Contact form.

In order to continue enjoying our site, we ask that you confirm your identity as a human. Thank you very much for your cooperation.

• Online Degrees
• Find your New Career
• Join for Free

Introduction to Relational Databases (RDBMS)

This course is part of multiple programs. Learn more

This course is part of multiple programs

Taught in English

Some content may not be translated

Instructors: Rav Ahuja +1 more

Instructors

Instructor ratings

We asked all learners to give feedback on our instructors based on the quality of their teaching style.

Financial aid available

56,563 already enrolled

(536 reviews)

Recommended experience

Beginner level

Computer and IT literacy. Curiosity about how data is managed.

What you'll learn

Describe data, databases, relational databases, and cloud databases.

Describe information and data models, relational databases, and relational model concepts (including schemas and tables). 

Explain an Entity Relationship Diagram and design a relational database for a specific use case.

Develop a working knowledge of popular DBMSes including MySQL, PostgreSQL, and IBM DB2

Skills you'll gain

• Database (DB) Design
• Relational Database Management System (RDBMS)
• Database Architecture

Details to know

Add to your LinkedIn profile

13 assignments

See how employees at top companies are mastering in-demand skills

Build your subject-matter expertise

• Learn new concepts from industry experts
• Gain a foundational understanding of a subject or tool
• Develop job-relevant skills with hands-on projects
• Earn a shareable career certificate

Earn a career certificate

Add this credential to your LinkedIn profile, resume, or CV

Share it on social media and in your performance review

There are 4 modules in this course

Are you ready to dive into the world of data engineering? In this beginner level course, you will gain a solid understanding of how data is stored, processed, and accessed in relational databases (RDBMSes). You will work with different types of databases that are appropriate for various data processing requirements.

You will begin this course by being introduced to relational database concepts, as well as several industry standard relational databases, including IBM DB2, MySQL, and PostgreSQL. Next, you’ll utilize RDBMS tools used by professionals such as phpMyAdmin and pgAdmin for creating and maintaining relational databases. You will also use the command line and SQL statements to create and manage tables. This course incorporates hands-on, practical exercises to help you demonstrate your learning. You will work with real databases and explore real-world datasets. You will create database instances and populate them with tables and data. At the end of this course, you will complete a final assignment where you will apply your accumulated knowledge from this course and demonstrate that you have the skills to: design a database for a specific analytics requirement, normalize tables, create tables and views in the database, load and access data. No prior knowledge of databases or programming is required. Anyone can audit this course at no-charge. If you choose to take this course and earn the Coursera course certificate, you can also earn an IBM digital badge upon successful completion of the course.

Relational Database Concepts

In this module, you will first learn about the fundamental aspects of data structures and file formats, along with the differences between relational and non-relational databases. You’ll explore various types of data models and discuss fundamental concepts in database management. Additionally, you’ll explore Entity-Relationship Diagrams (ERD) along with their components and relationships. You will also gain expertise in diverse database topics. Finally, you’ll gain a clear understanding of Db2 and PostgreSQL.

What's included

14 videos 3 readings 4 assignments 4 plugins

14 videos • Total 86 minutes

• Course Introduction • 4 minutes • Preview module
• Review of Data Fundamentals • 7 minutes
• Information and Data Models • 6 minutes
• ERDs and Types of Relationships • 4 minutes
• Mapping Entities to Tables • 5 minutes
• Data Types • 5 minutes
• Relational Model Concepts • 6 minutes
• Database Architecture • 6 minutes
• Distributed Architecture and Clustered Databases • 4 minutes
• Database Usage Patterns • 6 minutes
• Introduction to Relational Database Offerings • 7 minutes
• Db2 • 10 minutes
• MySQL • 6 minutes
• PostgreSQL • 4 minutes

3 readings • Total 5 minutes

• Course Overview • 3 minutes
• Summary and Highlights • 1 minute

4 assignments • Total 50 minutes

• Graded Quiz: Fundamental Relational Database Concepts • 15 minutes
• Graded Quiz: Introducing Relational Database Products • 15 minutes
• Practice Quiz: Fundamental Relational Database Concepts • 10 minutes
• Practice Quiz: Introducing Relational Database Products • 10 minutes

4 plugins • Total 27 minutes

• Helpful Tips For Course Completion • 1 minute
• Hands-on Lab: Relational Model Concepts • 10 minutes
• Reading: Deep Dive into Advanced Relational Model Concepts • 1 minute
• Hands-on Lab: Advanced Relational Model Concepts • 15 minutes

Using Relational Databases

In this module, you’ll explore the types of SQL statements, like Data Definition Language (DDL) and Data Manipulation Language (DML). You’ll learn how to create, modify, and manage tables using DDL statements and understand data movement utilities for efficient data loading and management. Additionally, you’ll dive into key database objects such as schemas, primary keys, foreign keys, and indexes, gaining insights into their roles in data organization, integrity, and retrieval. You’ll also understand the importance of normalization for reducing redundancy and ensuring data consistency, while also understanding various constraints within the relational model to maintain data accuracy and reliability.

11 videos 2 readings 4 assignments 3 app items 4 plugins

11 videos • Total 51 minutes

• Types of SQL Statements (DDL vs. DML) • 2 minutes • Preview module
• Creating Tables • 5 minutes
• CREATE TABLE Statement • 3 minutes
• ALTER, DROP, and Truncate Tables • 3 minutes
• Data Movement Utilities • 6 minutes
• Loading Data • 3 minutes
• Database Objects and Hierarchy (Including Schemas) • 6 minutes
• Primary Keys and Foreign Keys • 3 minutes
• Overview of Indexes • 5 minutes
• Normalization • 6 minutes
• Relational Model Constraints - Advanced • 4 minutes

2 readings • Total 4 minutes

• Summary and Highlights • 2 minutes
• Graded Quiz: Creating Tables and Loading Data • 15 minutes
• Graded Quiz: Designing Keys, Indexes, and Constraints • 15 minutes
• Practice Quiz: Creating Tables and Loading Data • 10 minutes
• Practice Quiz: Designing Keys, Indexes, and Constraints • 10 minutes

3 app items • Total 95 minutes

• Hands-on Lab: Create Tables and Load Data in Datasette • 20 minutes
• Hands-on Lab: Normalization, Keys, and Constraints in Relational Database • 60 minutes
• (Optional) Obtain IBM Cloud Feature Code and Activate Trial Account • 15 minutes

4 plugins • Total 72 minutes

• Reading: Overview of Optional Lesson • 2 minutes
• (Optional) Hands-on Lab: Create Db2 service instance • 15 minutes
• (Optional)Hands-on Lab: Create Tables and Load Data in Db2 • 30 minutes
• (Optional) Hands-on Lab: Normalization, Keys, and Constraints in Relational Databases (with Db2) • 25 minutes

MySQL and PostgreSQL

In this module, you will learn about the fundamental aspects of MySQL and PostgreSQL and identify Relational Database Management System (RDBMS) tools. You will explore the process of creating databases and tables and the definition of keys, constraints, and connections in MySQL. Additionally, you will discover important processes in PostgreSQL using command line, pgAdmin, and views. Moreover, you will gain essential skills such as database loading techniques and insights into securing sensitive data and streamlining data retrieval.

7 videos 2 readings 4 assignments 6 app items

7 videos • Total 34 minutes

• Getting Started with MySQL • 7 minutes • Preview module
• Creating Databases and Tables in MySQL • 3 minutes
• Populating MySQL Databases and Tables • 5 minutes
• Using Keys and Constraints in MySQL • 3 minutes
• Getting Started with PostgreSQL • 7 minutes
• Creating Databases and Loading Data in PostgreSQL • 4 minutes
• Views • 3 minutes

2 readings • Total 3 minutes

• Graded Quiz: MySQL • 15 minutes
• Graded Quiz: PostgreSQL • 15 minutes
• Practice Quiz: MySQL • 10 minutes
• Practice Quiz: PostgreSQL • 10 minutes

6 app items • Total 115 minutes

• Hands-on Lab: Getting Started with MySQL Command Line • 20 minutes
• Hands-on Lab: Create Tables and Load Data in MySQL using phpMyAdmin • 20 minutes
• Hands-on Lab: Keys and Constraints in MySQL using phpMyAdmin • 20 minutes
• Hands-on Lab: Getting Started with PostgreSQL Command Line • 20 minutes
• Hands-on Lab: Create Tables and Load Data in PostgreSQL using pgAdmin • 20 minutes
• Hands-on Lab: Views in PostgreSQL • 15 minutes

Final Project and Assessment

In this module, you will navigate the database design process, refine your practical skills, and understand essential steps. You will discover the role of Entity Relationship Diagrams (ERDs) and get an opportunity to engage in a hands-on database design lab, where you will use your theoretical knowledge to create databases. As you progress, you will receive an optional final assignment and project submission stages. For those seeking an advanced challenge, a final project using Db2 is available. A glossary is available for quick reference to key terms used throughout the module.

1 video 2 readings 1 assignment 1 peer review 2 app items 3 plugins

1 video • Total 7 minutes

• Approach to Database Design (Including ERD) • 7 minutes • Preview module
• Congratulations and Next Steps • 2 minutes
• Thanks from the Course Team • 2 minutes

1 assignment • Total 45 minutes

• Final Exam • 45 minutes

1 peer review • Total 60 minutes

• Project Submission and Peer Review • 60 minutes

2 app items • Total 135 minutes

• Hands-on Lab: Database Design Using ERDs • 45 minutes
• Final Project: Database Design and Implementation • 90 minutes

3 plugins • Total 60 minutes

• Reading: Best Practices of RDBMS Design • 15 minutes
• Reading: Project Overview • 20 minutes
• Course Glossary • 25 minutes

IBM is the global leader in business transformation through an open hybrid cloud platform and AI, serving clients in more than 170 countries around the world. Today 47 of the Fortune 50 Companies rely on the IBM Cloud to run their business, and IBM Watson enterprise AI is hard at work in more than 30,000 engagements. IBM is also one of the world’s most vital corporate research organizations, with 28 consecutive years of patent leadership. Above all, guided by principles for trust and transparency and support for a more inclusive society, IBM is committed to being a responsible technology innovator and a force for good in the world. For more information about IBM visit: www.ibm.com

Recommended if you're interested in Data Management

Data Warehouse Fundamentals

Relational Database Administration (DBA)

Data Engineering Capstone Project

ETL and Data Pipelines with Shell, Airflow and Kafka

Why people choose coursera for their career.

Learner reviews

Showing 3 of 536

536 reviews

Reviewed on Mar 17, 2024

Great course that sums up all that it teaches at the end, unfortunately I encountered an error loading PostgreSQL and MySQL at the end on the cloud IBM platform so I did it in VisCode

Reviewed on Sep 28, 2021

Really great and gives a foundation of relational databases.

Reviewed on Jul 27, 2023

I am learning new technologies through coursera.I am so much satisfies

New to Data Management? Start here.

Open new doors with Coursera Plus

Unlimited access to 7,000+ world-class courses, hands-on projects, and job-ready certificate programs - all included in your subscription

Advance your career with an online degree

Earn a degree from world-class universities - 100% online

Join over 3,400 global companies that choose Coursera for Business

Upskill your employees to excel in the digital economy

Frequently asked questions

When will i have access to the lectures and assignments.

Access to lectures and assignments depends on your type of enrollment. If you take a course in audit mode, you will be able to see most course materials for free. To access graded assignments and to earn a Certificate, you will need to purchase the Certificate experience, during or after your audit. If you don't see the audit option:

The course may not offer an audit option. You can try a Free Trial instead, or apply for Financial Aid.

The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.

What will I get if I subscribe to this Certificate?

When you enroll in the course, you get access to all of the courses in the Certificate, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile. If you only want to read and view the course content, you can audit the course for free.

What is the refund policy?

If you subscribed, you get a 7-day free trial during which you can cancel at no penalty. After that, we don’t give refunds, but you can cancel your subscription at any time. See our full refund policy Opens in a new tab .

More questions

In this assignment you will be using the RDBMS provided by MySQL...

Answer & explanation, subscribe or share.

Unlock this answer and over 100 million course-specific resources

Related Textbook Solutions

Recently Asked Questions

• Q Why is knowing how to work with files important for automation? 1 point In order to create a function, it's necessary to Answered 51d ago
• Q Sticky sessions are stored on Group of answer choices Target Group Database server Web Application server Load Balancer Answered 51d ago
• Q 11.12 LAB: Dates Complete the code to implement the following operations: Complete read_date(): Read an input string rep Answered 51d ago
• Q Regarding the comparison of the use of response models and clone models for outside lists, which of the following is tru Answered 51d ago
• Q Which one of the below is NOT an Administrative query in server manager? a)List of installed services b) Event logs with Answered 51d ago
• Q Explain the program sequence by describing 3-5 courses experience in the recommended order of presentation Answered 51d ago
• Q You're the sole IT employee at your company. Your boss wants the company to migrate away from using the current, paid-fo Answered 51d ago
• Q Which statements are true about HTTPS and security protocols? Select all that apply. HTTPS can be secured with Secure So Answered 51d ago

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

pradeepsure/KeepNote-RDBMS-Assignment-boilerplate

Folders and files.

Repository files navigation

Seed code - boilerplate for rdbms - assignment, assignment step description.

Read the given set of questions and solve them by writing queries using MySQL

Problem Statement

Note management app (similar to Google Keep) is used to take notes, add notes into categories and set reminders for a note. Create the necessary DB schema (MySQL) including tables, relationships, triggers and add sample data into each table.

Expected Solution

Create the tables for Note, Category, Reminder, User, UserNote, NoteReminder and NoteCategory.

User table fields: user_id, user_name, user_added_date, user_password, user_mobile

Note table fields: note_id, note_title, note_content, note_status, note_creation_date

Category table fields : category_id, category_name, category_descr, category_creation_date, category_creator

Reminder table fields : reminder_id, reminder_name, reminder_descr, reminder_type, reminder_creation_date, reminder_creator

NoteCategory table fields : notecategory_id, note_id, category_id

Notereminder table fields : notereminder_id, note_id, reminder_id

usernote table fields : usernote_id, user_id, note_id

Insert the rows into the created tables (Note, Category, Reminder, User, UserNote, NoteReminder and NoteCategory).

Fetch the row from User table based on Id and Password.

Fetch all the rows from Note table based on the field note_creation_date.

Fetch all the Categories created after the particular Date.

Fetch all the Note ID from UserNote table for a given User.

Write Update query to modify particular Note for the given note Id.

Fetch all the Notes from the Note table by a particular User.

Fetch all the Notes from the Note table for a particular Category.

Fetch all the reminder details for a given note id.

Fetch the reminder details for a given reminder id.

Write a query to create a new Note from particular User (Use Note and UserNote tables - insert statement).

Write a query to create a new Note from particular User to particular Category(Use Note and NoteCategory tables - insert statement)

Write a query to set a reminder for a particular note (Use Reminder and NoteReminder tables - insert statement)

Write a query to delete particular Note added by a User(Note and UserNote tables - delete statement)

Write a query to delete particular Note from particular Category(Note and NoteCategory tables - delete statement)

Create a trigger to delete all matching records from UserNote, NoteReminder and NoteCategory table when : 1. A particular note is deleted from Note table (all the matching records from UserNote, NoteReminder and NoteCategory should be removed automatically) 2. A particular user is deleted from User table (all the matching notes should be removed automatically)

BBA CA and BCA Practical slips solution

Pune University BBA(CA)/BCA Software Practical Slips Solution C Programming, Rdbms , DBMS, WebTech subjects in syllabus

• FYBCA SLIPS
• SYLLABI LABBOOK

 RDBMS  Practical Slips Solution

Q3 Consider the following entities and their relationships. [40]

 Employee (emp_id, emp_name, address)

 Investment (inv_no, inv_name, inv_date, inv_amount)

 Relation between Employee and Investment is One to Many. Constraint: 

Primary key, inv_amount should be > 0. Create a RDB in 3NF and 

write PL/SQL blocks in Oracle for the following:

Write a procedure which will display details of employees invested 

amount in “Mutual Fund”

1)Create table employee(eidint primary key,ename char(29),addr char(28));

2)Create table investment(inoint primary key,iname char(29),idatedate,iamtint

,eidint,constraintfk_employeeinvestmentforeign key(eid)references employee(eid));

SQL>desc employee

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EID                                       NOT NULL NUMBER(38)

 ENMAE                                              CHAR(25)

 ADDR                                               CHAR(27)

SQL>desc investment

 INO                                       NOT NULL NUMBER(38)

 INAME                                              CHAR(28)

 IDATE                                              DATE

 IAMT                                               NUMBER(38)

 EID                                                NUMBER(38)

SQL> select * from employee;

  EID ENMAE                     ADDR

---------- ------------------------- ---------------------------

       101 raghavpune

       103 aaravwagholi

       102 vijaymumbai

         1 raghavpune

SQL> select * from investment;

  INO INAME                        IDATE           IAMT        EID

---------- ---------------------------- --------- ---------- ----------

         1 rahul                        09-JAN-02    1200000        101

         2 archana                      02-MAR-05    1000000        102

         3 pooja                        04-MAR-09    9000000        103

         6 xyz                          26-NOV-16      27000        102

         8 xyz                          26-NOV-08      27000        102

        78 xyz                          26-NOV-22      27000        102

         7 xyz                          03-NOV-23      27000        102

        89 xyz                          09-NOV-23      27000        102

        34 mutual fund                  17-JAN-09     120000          1

        33 mutual fund                  17-JAN-09    1200000        101

10 rows selected.

SQL> create or replace procedure le as

2  cursor d is select employee.eid,enmae,addr,iname from employee,investment

3  whereemployee.eid=investment.eid

4  andinvestment.iname='mutual fund';

5  d1d%rowtype;

6  begin

7  open d;

8  loop

9  fetch d into d1;

10  exit when d%notfound;

dbms_output.put_line('output:'||d1.eid||''||d1.enmae||''||d1.add);

12  end loop;

13  close d;

14  end;

Procedure created.

SQL> execute le();

output:1raghav                   pune

output:101raghav                   pune

PL/SQL procedure successfully completed.

Write a cursor which will display date wise investment details.

SQL> declare

2  cursor y is select investment.ino,iname,idate,iamt,employee.eid

3  from investment,employee

4  where employee.eid=investment.eid

5  order by idate;

6  y1y%rowtype;

7  begin

8  open y;

9  loop

10  fetch y into y1;

11  exit when y%notfound;

12  dbms_output.put_line('output:'||y1.idate||''||y1.ino||''||y1.iname||''||y1.

iamt||''||y1.eid);

13  end loop;

14  close y;

15  end;

output:26-NOV-088  xyz                         27000 102

output:17-JAN-0933  mutual fund                 1200000 101

output:17-JAN-0934mutual fund                 120000 1

output:26-NOV-166xyz                         27000 102

output:26-NOV-2278xyz                         27000 102

output:03-NOV-237xyz                         27000  102

output:09-NOV-2389xyz                         27000 102

Q3 Consider the following entities and their relationships. [40] 

Bill (billno, day, tableno, total) 

Menu (dish_no, dish_desc, price) 

The relationship between Bill and Menu is Many to Many with quantity as 

descriptive attribute. Constraint: Primary key, price should be > 0. 

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a procedure to display menu details having price

 between 200 to 500 which were order on ‘Saturday’ .

SQL> create table bill(bnoint primary key,dayvarchar(27),tnoint,totalint);

Table created.

SQL>desc bill

 BNO                                       NOT NULL NUMBER(38)

 DAY                                                VARCHAR2(27)

 TNO                                                NUMBER(38)

 TOTAL                                              NUMBER(38)

SQL> insert into bill values(1,'monday',23,123);

1 row created.

SQL> insert into bill values(2,'saturday',23,234);

SQL> insert into bill values(3,'saturday',21,45);

SQL> select * from bill;

       BNO DAY                                TNO      TOTAL

---------- --------------------------- ---------- ----------

         1 monday                              23        123

         2 saturday                            23        234

         3 saturday                            21         45

SQL> create table menu(dnoint primary key,d_descvarchar(29),price int);

SQL>desc menu

 DNO                                       NOT NULL NUMBER(38)

 D_DESC                                             VARCHAR2(29)

 PRICE                                              NUMBER(38)

SQL> insert into menu values(11,'asd',234);

SQL> insert into menu values(12,'fsd',659);

SQL> insert into menu values(13,'jho',467);

SQL> select * from menu;

DNO D_DESC                             PRICE

---------- ----------------------------- ----------

        11 asd                                  234

        12 fsd                                  659

        13 jho                                  467

SQL> create table bm(bnoint references bill(bno),dnoint references menu(dno));

SQL>descbm

 BNO                                                NUMBER(38)

 DNO                                                NUMBER(38)

SQL> insert into bmvalues(1,11);

SQL> insert into bmvalues(1,12);

SQL> insert into bmvalues(2,13);

SQL> insert into bmvalues(2,12);

SQL> insert into bmvalues(3,12);

SQL> insert into bmvalues(3,13);

SQL> select * from bm;

       BNO        DNO

---------- ----------

         1         11

         1         12

         2         13

         2         12

         3         12

         3         13

6 rows selected.

SQL> create or replace procedure yu as

2  cursor f is select menu.dno,d_desc,price from bill,menu

3  where price between 200 and 500

4  and day='saturday'

5  andbill.bno=bm.bno

6  andmenu.dno=bm.dno;

7  f1f%rowtype;

8  begin

9  open f;

10  loop

11  fetch f into f1;

12  exit when f%notfound;

13  dbms_output.put_line('output:'||f1.dno||''||f1.d_desc||

14  end loop;

15  close f;

16  end;

 SQL> execute yu();

output:13jho467

2) Write a trigger which will fire before insert or update on Menu

 having price less than or equal to zero. (Raise user defined exception 

and give appropriate message)

SQL> create or replace trigger nj

2  before insert or update

3  on menu

4  for each row

5  begin

6  if(:new.price<=0) then

7  raise_application_error(-20004,'enter more than 0');

8  end if;

9  end;

Trigger created.

SQL> insert into menu values(23,'dde',23);

SQL> insert into menu values(87,'dde',-0);

insert into menu values(87,'dde',-0)

            *

ERROR at line 1:

ORA-20004: enter more than 0

ORA-06512: at "SYSTEM.NJ", line 3

ORA-04088: error during execution of trigger 'SYSTEM.NJ'

Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt) 

Customer (cust_no, cust_name, mobile_no) 

Relation between Plan and Customer is One to Many. Constraint: 

Primary key, fix_amt should be greater than 0. Create a RDB in 3NF and 

write PL/SQL blocks in Oracle for the following: 

Write a function which will accept plan number from user and 

display all the details of the selected plan 

Soluatiion:-

SQL> create table plan11(pnoint primary key,pname varchar2(24),nooffreecallsint,

freecalltimetimestamp,famtint check(famt>0));

SQL> create table cust11(cnoint primary key,cname varchar2(28),mnoint,pnoint,

constraint fk_plan11cust11 foreign key(pno)references plan11(pno));

SQL> insert into plan11values(11,'ddsd',12,'09/jan/07 12:09:09',1200);

SQL> insert into plan11values(12,'ytti',22,'02/feb/03

11:05:07',1300);

SQL>  insert into plan11 values(13,'kuio',23,'01/mar/0211:02:03',1400);

SQL> select * from plan11;

PNO    PNAME            NOOFFREECALLSFREECALLTIMEFAMT

11 ddsd                                1209-JAN-07 12.09.09.000000 Pm       1200

12 ytti                                2202-FEB-03 11.05.07.000000 AM 1300

13 kuio                                2301-MAR-02 11.02.03.000000 Am  1400

SQL>  insert into cust11 values(1,'priti',1223223232,11);

SQL> insert into cust11 values(2,'shamal',567576687,12);

SQL> insert into cust11 values(3,'raghav',576786878,13);

SQL> select * from cust11;

CNO CNAME                               MNO        PNO

---------- ---------------------------- ---------- ----------

         1 priti                        1223223232         11

         2 shamal                        567576687         12

         3 raghav                        576786878         13

SQL> create or replace function yt(n in number)

  2   return varchar2 as

3  res varchar2(29);

4  begin

  5   select pname into res from plan0 where pno=n;

  6   return res;

7  end;

Function created.

Function calling

SQL> begin

2  dbms_output.put_line('output:'||yt(11));

3  end;

output:ddsd

2) Write a cursor which will display customer wise plan details.

(Use Parameterized Cursor)

 SQL> declare

2  n number;

  3   cursor c1(n number) is select plan11.pno,pname,famt from plan11, cust11

4  where plan11.pno=cust11.pno

5  and cust11.cno=n;

6  c c1%ROWTYPE;

  8   n:=&n;

  9   open c1(n);

11   fetch c1 into c;

 10   loop

12   exit when c1%NOTFOUND;

 13   dbms_output.put_line(c.pno||' '||c.pname||' '||c.famt);

 14   end loop;

 15   close c1;

Enter value for n: 1

old   8:  n:=&n;

new   8:  n:=1;

11 ddsd 1200

Project (pno, pname, start_date, budget, status) 

Department (dno, dname, HOD, loc) 

The relationship between Project and Department is Many to One. 

Constraint: Primary key. Project Status Constraints:

 C – Completed, P - Progressive, I – Incomplete Create a RDB in 3NF 

and write PL/SQL blocks in Oracle for the following:

Write a function which accept department name and display total 

number of projects whose status is “p”(progressive).

create table project(pnoint primary key,pname char(29),sdatedate,dudgetint,

status char(28)check(status in('c','i','p')));

SQL>desc project

PNO                                       NOT NULL NUMBER(38)

 PNAME                                              CHAR(29)

 SDATE                                              DATE

 DUDGET                                             NUMBER(38)

 STATUS                                             CHAR(28)

SQL> select * from project;

PNO PNAME              SDATE         DUDGET   STATUS

---------- -----------------------------        ---------       ---------          

1 abc                           09-JAN-20     200000                c

2 ass                           09-MAR-20      50000                i

3 hhs                           04-JAN-20     300000                p

12 xyz                           09-JAN-09     12000                 p

create table department(dnoint primary key,dname char(24),hod char(28),loc 

char(29),pnoint,constraintfk_projectdepartment foreign key(pno)references 

project(pno)); 

SQL>desc department;

DNO                                       NOT NULL NUMBER(38)

 DNAME                                              CHAR(24)

 HOD                                                CHAR(28)

 LOC                                                CHAR(29)

 PNO                                                NUMBER(38)

SQL> select * from department;

 DNO     DNAME                    HOD             LOC      PNO

---------- ------------------------ ------------- --------------              

11  computer science         mane                        pune            1

12  commercedespandepune             2        

13  computer science         kadampune            3   

23   slbdrpune            12

SQL> create or replace function fg(d in char)

  2  return number as

  3  bs number(10);

  4  begin

  5  select count(pname) into bs from project,department

  6  where project.pno=department.pno

  7  and dname=d

  8  and status='p';

  9  return bs;

 10  end;

 11  /

function calling:-

  2  dbms_output.put_line('output:'||fg('slb'));

  3  end;

  4  /

Write a cursor which will display status wise project details of each 

department .

  2     cursor s1 is

  3      select pno,pname,sdate,dudget,status from project

  4      where sdate='09/jan/2020';

  5      s s1%rowtype;

  6     begin

  7     open s1;

  8     loop

  9      fetch s1 into s;

 10        exit when s1%notfound;

 11      dbms_output.put_line(s.pno||''||s.pname||''||s.sdate||''||s.dudget||''|

|s.status);

 12     end loop;

 13      close s1;

 14      end;

 15     /

1abc                          09-JAN-20200000c

Gym (Name, city, charges, scheme) 

Member (ID, Name, phoneNo, address) 

Relation between Gym and member is one to many. Constraint: 

Primary Key, charges must be greater than 0. Create a RDB in 3NF and 

Write a function which will accept member id and scheme from user 

and display charges paid by that member. 

1.create table gym23(name varchar2(29) primary key,city varchar2(28),

charges int,scheme varchar2(29));

SQL>desc gym23

NAME                                      NOT NULL VARCHAR2(29)

 CITY                                               VARCHAR2(28)

 CHARGES                                            NUMBER(38)

 SCHEME                                             VARCHAR2(29)

insert into gym23 values('abc','pune',34000,'hty');

insert into gym23 values('pqr','pune',30000,'yhj');

insert into gym23 values('xyz','pune',90000,'yuhs');

SQL> select * from gym23;

NAME                          CITY                   CHARGES    SCHEME

abcpune                           34000            hty

pqrpune                           30000            yhj

xyzpune                            90000            yuhs

3 row selected

2.create table member9(id int primary key,mname varchar2(29),phnoint,

addr varchar2(28),name varchar2(29),constraint fk_gym23member9 

foreign key(name)references gym23(name));

SQL>desc member9

 ID                                        NOT NULL NUMBER(38)

 MNAME                                              VARCHAR2(29)

 PHNO                                               NUMBER(38)

 ADDR                                               VARCHAR2(28)

 NAME                                               VARCHAR2(29)

insert into member values(11,'raghav',7875657575,'wagholi','abc' );

insert into member values(12,'aarav',7565456478 ,'wagholi','pqr' );

insert into member values(13,'shamal  ',6565657668 ,'pune','xyz' );

SQL> select * from member9;

ID   MNAME                PHNO     ADDR     NAME

--------------------------------------------------------------

11    raghav                7875657575   wagholiabc

12    aarav                  7565456478    wagholipqr

13    shamal                6565657668   pune   xyz

SQL> create or replace function getprise (n IN number)

  3  res number(10);

  5  select charges into res

  6  from gym23,member9

  7  where id=n

  8   and gym23.name=member9.name;

  9  return res;

Function calling:-

  2  dbms_output.put_line('output:'||getprise(11));

output:34000

Write a trigger which will fire before insert or update on Gym having 

charges less than 1000. (Raise user defined exception and give appropriate 

SQL> create or replace trigger t11

  2   before insert or update

3  on gym23

6  if(:new.charges<1000) then

  7   raise_application_error(-20002,'ERROR::Charges should be greater than 1000');

  8   end if;

SQL> insert into gym23 values('sd','pune',2300,'wee');

SQL> insert into gym23 values('yw','pune',200,'wee');

insert into gym23 values('yw','pune',200,'wee')

ORA-20002: ERROR::Charges should be greater than 1000

ORA-06512: at "SYSTEM.T11", line 3

ORA-04088: error during execution of trigger 'SYSTEM.T11'

Student (rollno, sname, class, timetable)

 Lab (LabNo, LabName, capacity, equipment) 

Relation between Student and Lab is Many to One. Constraint:

 Primary Key, capacity should not be null. Create a RDB in 3NF 

Write a function which will accept Lab number from user and display 

total number of student allocated in that lab. 

SQL> Create table student2(rnoint primary key,sname char(29),class int,

timetabl eint);

SQL> Create table lab2(lnoint primary key,lname char(29),capacity int,equ 

cha( 23),rnoint,constraint fk_student2lab2 foreign key(rno)

references student2(rno));

SQL>desc student2;

 RNO                                       NOT NULL NUMBER(38)

 SNAME                                              CHAR(29)

 CLASS                                              NUMBER(38)

 TIMETABLE                                          NUMBER(38)

SQL> insert into student2 values(1,'raghav','12',10);

SQL> insert into student2 values(2,'shamal','11',10);

SQL> insert into student2 values(3,'aarav','13',12);

SQL> select * from student2;

RNO SNAME                              CLASS  TIMETABLE

---------- ----------------------------- ---------- ----------

         1 raghav                                12         10

         2 shamal                                11         10

         3 aarav                                 13         12

SQL>desc lab2

LNO                                       NOT NULL NUMBER(38)

 LNAME                                              CHAR(29)

 CAPACITY                                           NUMBER(38)

 EQU                                                CHAR(23)

 RNO                                                NUMBER(38)

SQL> insert into lab2 values(11,'slb',56,'computer',1);

SQL> insert into lab2 values(12,'vbb',79,'computer',2);

SQL> insert into lab2 values(13,'rlb',79,'computer',2);

SQL> select * from lab2;

LNO   LNAME        CAPACITY   EQU     RNO

------------  ---------------- ---------------- ---------  ----------

        11          slb                        56            computer   1

12        vbb                          79        computer        2

13        rlb                            79       computer         2

 SQL> create or replace function qw(v in number)

2  return number as

3  sd number;

5  select capacity into sd from lab2

6  wherelno=v;

7  returnsd;

8  end;

2  dbms_output.put_line('output'||qw(11));

2)Write a cursor which will display lab wise student details .

2  cursor g is select student2.rno,sname,class,timetable,lname

3  from student2,lab2

4  where student2.rno=lab2.rno

5  order by lname;

6  g1g%rowtype;

8  open g;

10  fetch g into g1;

11  exit when g%notfound;

12  dbms_output.put_line('output:'||g1.lname||''||g1.rno||''||g1.sname||''||g1.

class||''||g1.timetable);

14  close g;

output:rlb                          2shamal                       1110

output:slb                          1raghav                       1210

output:vbb                          2shamal                       1110

 Wholesaler (w_no, w_name, address, city) 

Product (product_no, product_name, rate) 

Relation between Wholesaler and Product is Many to Many with 

quantity as descriptive attribute. Constraint: Primary key, rate should be > 0. 

Write a function which will accept wholesaler name from user and 

will display total number of items supplied by him.

Solution:- 

   SQL> create table wholesaler(wnoint primary key,wname char(29),addr char(28)

,city char(28));

SQL>desc wholesaler;

 WNO                                       NOT NULL NUMBER(38)

 WNAME                                              CHAR(29)

 ADDR                                               CHAR(28)

 CITY                                               CHAR(28)

SQL> insert into wholesaler values(1,'raghav','wagholi','pune');

SQL> insert into wholesaler values(2,'aarav','thane','mumbai');

SQL> insert into wholesaler values(3,'vijay','thane','mumbai');

SQL>select  * from wholesaler;

       WNO WNAME                         ADDR        CITY

 1 raghavwagholipune

 2 aarav                         thane           mumbai

  3 vijay                         thane         mumbai

SQL> create table product(pnoint primary key,pname char(28),rate int);

SQL>desc product;

 PNO                                       NOT NULL NUMBER(38)

 PNAME                                              CHAR(28)

 RATE                                               NUMBER(38)

SQL> insert into product values(11,'pen',12);

SQL> insert into product values(12,'pencil',11);

row created.

SQL> insert into product values(13,'notebook',23);

SQL> select * from product;

 PNO PNAME                              RATE

---------- ---------------------------- ----------

        11 pen                                  12

        12 pencil                               11

        13 notebook                             23

SQL> create table wp( quantity int,wnoint references wholesaler(wno),pnoint re

ferences product(pno));

SQL>descwp;

QUANTITY                                           NUMBER(38)

 WNO                                                NUMBER(38)

SQL> insert into wpvalues(56,1,11);

SQL> insert into wpvalues(52,2,12);

SQL> insert into wpvalues(22,3,13);

SQL> select * from wp;

QUANTITY        WNO        PNO

---------- ---------- ----------

        56          1         11

        52          2         12

        22          3         13

SQL> create or replace function ni(s in number)

3  gh number;

5  select count(wp.pno) into gh from product,wholesaler,wp

6  wherewholesaler.wno=wp.wno

7  andproduct.pno=wp.pno

8  andwholesaler.wno=s;

9  returngh;

10  end;

2  dbms_output.put_line('output:'||ni(1));

Write a trigger which will fire before insert or update on product

 having rate less than or equal to zero (Raise user defined exception and give appropriate message)

SQL> create or replace trigger ee

3  on product

6  if(:new.rate<=0) then

7  raise_application_error(-20008,'enter more than 0');

SQL> insert into product values(101,'pen',123);

SQL> insert into product values(102,'pen',-123);

insert into product values(102,'pen',-123)

ORA-20008: enter more than 0

ORA-06512: at "SYSTEM.EE", line 3

ORA-04088: error during execution of trigger 'SYSTEM.EE'

Country (CId, CName ,no_of_states, area, location, population) 

Citizen( Id, Name, mother_toung, state_name)

 Relation between Country and Citizen is one to many. Constraint: 

Primary key, area should not be null. Create a RDB in 3NF and 

Write a function which will display name of the country having minimum 

SQL> Create table country2(cid int primary key,cname char(29),nos int,

area char(29),loc char(29),population int);

SQL> Create table citizen3(id int primary key,name char(29),mt char(29),

snamech ar(29),cidint,constraint fk_country2citizen3 foreign key(cid)

references country2(cid));

SQL>desc country2

 Name                                      Null?                 Type

---------------------------------------- -------- -------------------------

 CID                                       NOT NULL      NUMBER(38)

 CNAME                                              CHAR(29)

 NOS                                                NUMBER(38)

 AREA                                               CHAR(29)

 POPULATION                               NUMBER(38)

SQL> insert into country2 values(101,'india',34,'ert','gsuy',1230000);

SQL> insert into country2 values(102,'us',34,'ert','gsuy',2120000);

SQL> insert into country2 values(103,'china',23,'ert','gsuy',200000);

SQL> select* from country2;

       CID          CNAME              NOSAREA                          LOC                           POPULATION

----------------------------- ----------------------------- ---------------------------------------------------------

 101 india            34                 ertgsuy                             1230000

       102 us    34ertgsuy                             2120000

       103 china 23ertgsuy                              200000

SQL>desc citizen3

 ----------------------------------------- -------- -------------------------

 NAME                                               CHAR(29)

 MT                                                 CHAR(29)

 CID                                                NUMBER(38)

SQL> insert into citizen3 values(1,'raghav','marathi','maharashtra',101);

SQL> insert into citizen3 values(2,'shamal','marathi','maharashtra',102);

SQL> insert into citizen3 values(3,'aarav','marathi','maharashtra',102);

SQL> select * from citizen3;

        ID NAME                          MTSNAME                                CID

----------------------------- ----------

         1 raghavmarathimaharashtra                          101

2 shamal                Marathi                maharashtra                          102

 3 aaravmarathimaharashtra                          102  

SQL> create or replace function er

2  return char as

3  uy char(29);

5  selectcname into uy from country2

6  where population=(select min(population) from country2);

7  returnuy;

2  dbms_output.put_line('output:'||er());

output:china

Write a cursor which will display county wise citizen details.

2  cursor k is select cname,id,name,mt,sname

3  from country2,citizen3

4  where country2.cid=citizen3.cid

5  order by cname;

6  k1k%rowtype;

8  open k;

10  fetch k into k1;

11  exit when k%notfound;

12  dbms_output.put_line('output:'||k1.cname||''||k1.id||''||k1.name||''||k1.mt

||''||k1.sname);

14  close k;

output:india                        1raghav                     marathimaharashtra

output:us                           3aarav                        marathimaharashtra

output:us                           2shamal                       marathimaharashtra

 College (code, college_name, address)

 Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg) 

Relation between Teacher and College is Many to One. Constraint: Primary Key, 

qualification should not be null. Create a RDB in 3NF and write PL/SQL 

blocks in Oracle for the following:

Write a procedure which will accept teacher name from user and display his/her college details.

Create table college(code int primary key,cname char(29),addr char(29));

Table created

Create table teacher1(tidint primary key,tname char(29),qualification char(29),

specialisation char(29),desg char(29),code int,constraint fk_collegeteacher1 

foreign key(code)references college(code));

SQL>desc college

 CODE                                      NOT NULL NUMBER(38)

 ADDR                                               CHAR(29)

SQL>desc teacher1

 TID                                       NOT NULL NUMBER(38)

 TNAME                                              CHAR(20)

 QUALIFICATION                                      CHAR(20)

 SPECIALISATION                                     CHAR(28)

 SALARY                                             NUMBER(38)

 DESG                                               CHAR(29)

 CODE                                               NUMBER(38)

SQL> select * from college;

      CODE CNAME                         ADDR

---------- ----------------------------- ----------------------------

1 bjswagholi

         2 wadiyapune

         3 dpmmumbai

SQL> select * from teacher1;

  TID TNAME   QUALIFICATION   SPECIALISATION   SALARY   DESG   CODE

---------------------------- ---------- ----------------------------- -----------------------------------------------------

21    mane                 mcomhwe                          30000                  teaher          1

22    jadhav                   m                          ghy                             40000                teach             2

23  deshamukhbahs                            90000                teach               3

SQL> create or replace procedure vs(b in char) as

2  cursor f is select college.code,cname,addr from college,teacher1

3  wherecollege.code=teacher1.code

4  andtname=b;

5  f1f%rowtype;

6  dint;

7  vn char(29);

8  gh char(29);

9  begin

10  open f;

11  loop

12  fetch f into f1;

13  exit when f%notfound;

14  d:=f1.code;

15  vn:=f1.cname;

16  gh:=f1.addr;

17  dbms_output.put_line('output:'||d||''||vn||''||gh);

18  end loop;

19  close f;

20  end;

SQL> execute vs('mane');

output:1bjs                          wagholi

1)Write a trigger which will fire before insert or update on Teacher having 

salary less than or equal to zero (Raise user defined exception and give 

appropriate message)

SQL> create or replace trigger rt

3  on teacher1

6  if(:new.salary<=0) then

7  raise_application_error(-20003,'enter more than 0');

SQL> insert into teacher1 values(78,'bhagat','mca','bm',40000,'teach',3);

SQL> insert into teacher1 values(76,'kadam','mca','bm',-40000,'teach',3);

insert into teacher1 values(76,'kadam','mca','bm',-40000,'teach',3)

ORA-20003: enter more than 0

ORA-06512: at "SYSTEM.RT", line 3

ORA-04088: error during execution of trigger 'SYSTEM.RT'

Q3 Consider the following entities andtheirrelationships. [40]Driver (driver_id, driver_name,address)

Car (license_no, model, year) Relation between Driver and Car is Many to Many with date and time asdescriptive attribute.Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1)Write a function which will display the total number of person who are using “Swift”car

SQL> create table driver2(did int primary key,dname char(29),addr char(29));

SQL> create table car(lnoint primary key,model char(29),year int);

SQL> create table dc1( pdate date,did int references driver2(did),

lnoint references car(lno));

SQL>desc driver

 D_NO                                      NOT NULL NUMBER(38)

 D_NAME                                    NOT NULL VARCHAR2(10)

 LICENCE_NO                                         NUMBER(10)

 ADDR                                               VARCHAR2(10)

 D_AGE                                              NUMBER(10)

 SALARY                                             NUMBER(10)

SQL>desc driver2

 ---------------------------------------- -------- ----------------------------

 DID                                       NOT NULL NUMBER(38)

 DNAME                                              CHAR(29)

SQL> insert into driver2 values(1,'raghav','pune');

SQL> insert into driver2 values(2,'aarav','mumbai');

SQL> insert into driver2 values(3,'rohan','mumbai');

SQL> select * from driver2;

       DID DNAME                         ADDR

---------- ----------------------------- -----------------------------

         2 aaravmumbai

         3 rohanmumbai

SQL>desc car

 LNO                                       NOT NULL NUMBER(38)

 MODEL                                              CHAR(29)

 YEAR                                               NUMBER(38)

SQL> insert into car values(101,'swift',2001);

SQL> insert into car values(102,'swift',2003);

SQL> insert into car values(103,'seho',2003);

SQL> select * from car;

       LNO MODEL                               YEAR

       101 swift                               2001

       102 swift                               2003

       103 seho                                2003

SQL>desc dc

 DID                                                NUMBER(38)

 LNO                                                NUMBER(38)

SQL> insert into dc values(1,101);

SQL> insert into dc values(2,101);

SQL> insert into dc values(3,102);

SQL> insert into dc values(3,103);

SQL> select * from dc;

       DID        LNO

         1        101

         2        101

         3        102

         3        103

SQL> create or replace function gh

5  select count(model) into sd

6  from car

7  where model='swift';

8  returnsd;

FUNCTIN CALLING:

2  dbms_output.put_line('outpout:'||gh());

2)Write a trigger which will fire before insert or update on year. If year value is more than current year. (Raise user defined exception and give appropriatemessage)

 Game (game_name, no_of_players, coach_name) 

Player (pid, pname, address, club_name) 

Relation between Game and Player is Many to Many.

 Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a procedure which will display games details 

having number of players more than 5

SQL> create table game2(gname char(29) primary key,nop int,cname char(29));

SQL> create table player2(pid int primary key,pname char(29),addr char(29),cname

 char(27));

SQL> create table gp2(gname char(29) references game2(gname),pid int references

player2(pid));

SQL> desc game2

GNAME                                     NOT NULL CHAR(29)

 NOP                                                NUMBER(38)

SQL> insert into game2 values('cricket',12,'eui');

SQL> insert into game2 values('kabddi',7,'uye');

SQL> insert into game2 values('kho-kho',7,'jude');

SQL> select * from game2;

GNAME                                NOP CNAME

----------------------------- ---------- ----------------------------

cricket                               12 eui

kabddi                                 7 uye

kho-kho                                7 jude

SQL> desc player2

PID                                       NOT NULL NUMBER(38)

 CNAME                                              CHAR(27)

SQL> insert into player2 values(101,'rohit sharma','mumbai','bgg');

SQL> insert into player2 values(102,'pravin narval','patana','iur');

SQL> insert into player2 values(103,'huins','bihar','ius');

SQL> select * from  player2;

       PID PNAME                         ADDRCNAME

------------------------------------------------------------------------------

       101 rohit sharma                  mumbaibgg

 102 pravin narval                 patina          iur

      103                              huins                         biharius

SQL> desc gp2

 GNAME                                              CHAR(29)

 PID                                                NUMBER(38)

SQL> insert into gp2 values('cricket',101);

SQL> insert into gp2 values('kabddi',102);

SQL> insert into gp2 values('kho-kho',103);

SQL> select * from gp2;

GNAME                                PID

cricket                              101

kabddi                               102

kho-kho                              103

SQL> create or replace procedure io as

  2  cursor d is select gname,nop,cname

  3  from game2

  4  where nop>5;

  5  g char(29);

  6  n int;

  7  c char(29);

  8  d1 d%rowtype;

  9  begin

 10  open d;

 11  loop

 12  fetch d into d1;

 13  exit when d%notfound;

 14  g:=d1.gname;

 15  n:=d1.nop;

 16  c:=d1.cname;

 17  dbms_output.put_line('output:'||g||''||n||''||c);

 18  end loop;

 19  close d;

 20  end;

 21  /

SQL> execute io();

output:cricket                      12eui

output:kabddi                       7uye

output:kho-kho                      7jude

procedure successfully completed.

SQL> create or replace PROCEDURE ge as

  2  cursor c1 is select * from game2 where nop>5;

  3  c c1%ROWTYPE;

  5  open c1;

  6  loop

  7  fetch c1 into c;

  8  exit when c1%NOTFOUND;

  9  dbms_output.put_line(c.gname||' '||c.nop||' '||c.cname);

 10  end loop;

 11  close c1;

 12  end;

 13  /

SQL> execute ge();

cricket                       12 eui

kabddi                        7 uye

kho-kho                       7 jude

2) Write a trigger which will fire before insert or update on Game having no_of_players less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL> create or replace trigger hu

  2  before insert or update

  3  on game2

  4  for each row

  5  begin

  6  if(:new.nop<=0) then

  7  raise_application_error(-20008,'enter more than 0');

  8  end if;

  9  end;

 10  /

SQL> insert into game2 values('tenis',1,'jn');

SQL> insert into game2 values('basketball',-1,'jn');

insert into game2 values('basketball',-1,'jn')

ORA-06512: at "SYSTEM.HU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.HU'

Q3. Consider the following Item_Supplier database [40] 

Company (name , address , city , phone , share_value) 

Person (pname ,pcity )

 Relationship between Company and Person is M to M relationship with descriptive attribute No_of_shares i Constraints: name,pname primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following : 

Write a trigger before insert or update on No_of_shares field should not be zero.(Raise user defined exception and give appropriate message) 

SQL> create table company(name char(29) primary key,addr char(29),city char(29),

phone int,svalue int);

SQL> create  table person(pname char(29) primary key,pcity char(29));

SQL> create table pc(name char(29) references company(name),pname char(29) refer

ences person(pname));

SQL> desc company

NAME                                      NOT NULL CHAR(29)

 CITY                                               CHAR(29)

 PHONE                                              NUMBER(38)

 SVALUE                                             NUMBER(38)

SQL> insert into company values('tata','pune','pune',45457658,12100000);

SQL> insert into company values('bjaj','thane','mumbai',69870972,1200000);

SQL> insert into company values('finix','banglor','pune',68764497,7800000);

SQL> select * from company;

NAME                          ADDRCITY                     PHONE     SVALUE

----------------------------- ---------- ----------

tata                          punepune                            45457658   12100000

bjaj                          thane                  mumbai                          69870972    1200000

finix                         banglorpune                            68764497    7800000

SQL> desc person;

PNAME                                     NOT NULL CHAR(29)

 PCITY                                              CHAR(29)

SQL> insert into person values('raghav','pune');

SQL> insert into person values('aarav','mumbai');

SQL> insert into person values('shamal','osmanabad');

SQL> select * from person;

PNAME                         PCITY

----------------------------- -----------------------------

raghav                        pune

aarav                         mumbai

shamal                        osmanabad

SQL> desc pc;

NAME                                               CHAR(29)

SQL> insert into pc values('tata','raghav');

SQL> insert into pc values('bjaj','aarav');

SQL> insert into pc values('bjaj','shamal');

SQL> insert into pc values('finix','shamal');

SQL> select * from pc;

NAME                          PNAME

tata                          raghav

bjaj                          aarav

bjaj                          shamal

finix                         shamal

  SQL>  create or replace trigger te

  3   on pc

  4   for each row

  6  if(:new.nos<=0) then

  7  raise_application_error(-20001,'Shares must be greater than zero');

 SQL> insert into pc values('tata','raghav',65);

SQL> insert into pc values('bjaj','shamal',0);

insert into pc values('bjaj','shamal',0)

ORA-20001: Shares must be greater than zero

ORA-06512: at "SYSTEM.TE", line 3

ORA-04088: error during execution of trigger 'SYSTEM.TE'

Write a function to display total no_of_shares of a specific person.

SQL> create or replace function bl

  3  res number;

  5  select count(nos) into res from pc

  6  where pname='shamal';

  7  return res;

  8  end;

  9  /

FUNCTIN CALLING:-

  2  dbms_output.put_line('output:'||bl());

Q3. Consider the following entities and their relationship. [40] 

Student (s_reg_no, s_name, s_class) 

Competition (comp_no, comp_name, comp_type) 

Relationship between Student and Competition is many-to-many with descriptive attribute rank and year. Constraints: primary key, foreign key, primary key for third table(s_reg_no, comp_no, year),s_name and comp_name should not be null,comp_type can be sports or academic. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year.

SQL> create table student9(srno int primary key,sname char(29),sclass int);

SQL> create table competition(cno int primary key,cname char(29),ctype char(29));

SQL> create table sc(srno int references student9(srno),cno int references competition(cno));

SQL> desc student9

 SRNO                                      NOT NULL NUMBER(38)

 SCLASS                                             NUMBER(38)

SQL> insert into student9 values(101,'shamal',12);

SQL> insert into student9 values(102,'raghav',13);

SQL> insert into student9 values(103,'aarav',14);

SQL> select * from student9;

SRNO    SNAME                             SCLASS

       101 shamal                                12

       102 raghav                                13

       103 aarav                                 14

SQL> desc competition

 CNO                                       NOT NULL NUMBER(38)

 CTYPE                                              CHAR(29)

SQL> insert into competition values(1,'queis','ty');

SQL> insert into competition values(2,'speech','he');

SQL> insert into competition values(3,'running','t8');

SQL> select * from competition;

 CNO CNAME                         CTYPE

         1 queis                         ty

         2 speech                        he

         3 running                       t8

SQL> desc sc

 SRNO                                               NUMBER(38)

 CNO                                                NUMBER(38)

SQL> insert into sc values(101,1);

SQL> insert into sc values(101,2);

SQL> insert into sc values(102,2);

SQL> insert into sc values(103,3);

SQL> select * from sc;

      SRNO        CNO

       101          1

       101          2

       102          2

       103          3

SQL> create or replace function  kl(n in number)

  5  select count(cno) into res from sc where srno=n;

  6  return res;

  7  end;

  8  /

FUNCTION CALLING:-

  2  dbms_output.put_line('output:'||kl(101));

Write a cursor which will display year wise details of competitions. (Use parameterized cursor)

  2  cursor c1(n int) is select year,competition.cno,cname,ctype

  3  from student9,competition,sc

  4  where student9.srno=sc.srno

  5  and competition.cno=sc.cno

  6  and year=n

  7  order by year;

  8  c c1%ROWTYPE;

 10  open c1(&n);

 12  fetch c1 into c;

 13  exit when c1%notfound;

 14  dbms_output.put_line(c.year||' '||c.cno||' '||c.cname||' '||c.ctype);

 15  end loop;

 16  close c1;

 17  end;

 18  /

Enter value for n: 2018

old  10: open c1(&n);

new  10: open c1(2018);

2018 2 speech                        he

2018 3 running                       t8

Game (game_name, no_of_players, coach_name) 

Player (pid, pname, address, club_name) Relation between Game and Player is Many to Many. Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following : 

Write a function which will return total number of football players of “Sports Club”. 

SQL> create table game5(gname char(29) primary key,nop int,name char(29));

SQL> create table player5(pid int primary key,pname char(29),addr char(29),cname char(27));

SQL> create table gp5(gname char(29) references game5(gname),pid int references player5(pid));

SQL> desc game5

SQL> insert into game5 values('cricket',12,'ass');

SQL> insert into game5 values('fotball',7,'aj');

SQL> insert into game5 values('football',7,'aj');

SQL> insert into game5 values('kho-kho',7,'jo');

SQL> select * from game5;

GNAME                                NOP NAME

----------------------------- ---------- -----------------------------

cricket                               12 ass

fotball                                7 aj

football                               7 aj

kho-kho                                7 jo

SQL> desc player5;

 PID                                       NOT NULL NUMBER(38)

SQL> insert into player5 values(101,'rohit sharma','mumbai','sports club');

SQL> insert into player5 values(102,'virat','pune','game club');

SQL> insert into player5 values(103,'hardik','pune','ghf club');

SQL> select * from player5;

---------------------------

       101 rohit sharma                  mumbaisports club

102 virat                         punegame club

103 hardik                        puneghf club

SQL> desc gp5;

SQL> insert into gp5 values('football',101);

SQL> insert into gp5 values('cricket',102);

SQL> select * from gp5;

football                             101

cricket                              102

SQL> create or replace FUNCTION getnum

  2   return number as

  5  select sum(nop) into res from game5,player5,gp5

  6  where player5.pid=gp5.pid

  7  and game5.gname=gp5.gname

  8  and  gp5.gname='football'

  9  and cname='sports club';

 10  return res;

 11  end;

  2  dbms_output.put_line('output:'||getnum());

Write a cursor which will display club wise details of players.

  2  cursor t is select cname,pid,pname,addr from player5

  3  order by cname;

  4  t1 t%rowtype;

  6  open t;

  7  loop

  8  fetch t into t1;

  9  exit when t%notfound;

 10  dbms_output.put_line('output:'||t1.cname||''||t1.pid||''||t1.pname||''||t1.

 11  end loop;

 12  close t;

 13  end;

 14  /

output:game club                  102     virat                        pune

output:ghf club                   103       hardik                       pune

output:sports club                101     rohit sharma             mumbai

Consider the following entities and their relationships. [40] 

Driver (driver_id, driver_name, address)

 Car (license_no, model, year) 

Relation between Driver and Car is Many to Many with date and time as descriptive attribute. Constraint: Primary key, driver_name should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Write a procedure to display car details used on specific day. 

create table car(lno int primary key,model char(29),year int);

SQL> desc driver2;

 ----------------------------------------- -------- -----------------

 DID DNAME                         ADDR

         1 raghav                        pune

         2 aarav                         mumbai

         3 rohan                         mumbai

SQL> desc car;

SQL> insert into car values(102,'swift',2018);

  LNO MODEL                               YEAR

       102 swift                               2018

SQL> create table dc1( pdate date,did int references driver2(did),lno int refere

nces car(lno));

SQL> desc dc1;

 PDATE                                              DATE

SQL> insert into dc1 values('09/jan/20',1,101);

SQL> insert into dc1 values('08/mar/20',3,102);

SQL> insert into dc1 values('02/mar/20',3,103);

SQL> select * from dc1;

PDATE            DID        LNO

--------- ---------- ----------

09-JAN-20          1        101

08-MAR-20          3        102

02-MAR-20          3        103

SQL> create or replace PROCEDURE js as

  2  cursor c1 is select car.lno,model,year from car,dc1

  3  where  car.lno=dc1.lno

  4  and pdate='08-mar-2020';

  5  d c1%ROWTYPE;

  6  begin

  7  open c1;

  8  loop

  9  fetch c1 into d;

 10  exit when c1%NOTFOUND;

 11  dbms_output.put_line(d.lno||' '||d.model||' '||d.year);

 12  end loop;

 13  close c1;

 14  end;

 16  /

SQL> execute js();

102 swift                         2018

Write a cursor which will display driver wise car details in the year 2018 .

  2  cursor d is select dname,car.lno,model,year from driver2,car,dc1

  3  where driver2.did=dc1.did

  4  and car.lno=dc1.lno

  5  and year=2018

  6  order by dname;

  7  d1 d%rowtype;

  8  begin

  9  open d;

 10  loop

 11  fetch d into d1;

 12  exit when d%notfound;

 13  dbms_output.put_line('output:'||d1.dname||''||d1.lno||''||d1.model||''||d1.

 14  end loop;

 15  close d;

 16  end;

 17  /

output:rohan                        102swift                        2018

College (code, college_name, address)

Relation between Teacher and College is Many to One. Constraint: Primary Key, qualification should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Write a function which will accept college name from user and display total number of “Ph.D” qualified teachers. 

SQL> Create table college1(code int primary key,cname char(29),addr char(29));

SQL> Create table teacher10(tid int primary key,tname char(29),qualification char(29),specialisation char(29),desg char(29),code int,constraint fk_college1teacher10 foreign key(code)references college1(code));

SQL> desc college1;

CODE                                      NOT NULL NUMBER(38)

SQL> insert into college1 values(101,'bjs','wagholi');

SQL> insert into college1 values(102,'modern','pune');

SQL> insert into college1 values(103,'wadiya','pune');

SQL> select * from college1;

  CODE CNAME                         ADDR

       101 bjs                           wagholi

       102 modern                        pune

       103 wadiya                        pune

SQL> desc teacher10;

 TNAME                                              CHAR(29)

 QUALIFICATION                                      CHAR(29)

 SPECIALISATION                                     CHAR(29)

SQL> insert into teacher10 values(1,'mane','ph.d','bm','hod',101);

SQL> insert into teacher10 values(2,'patil','ph.d','ob','principal',102);

SQL> insert into teacher10 values(3,'kadam','mca','io','teacher',103);

SQL> select * from teacher10;

TID       TNAME      QUALIFICATIONSPECIALISATION                DESG                                CODE

----------------------------- ----------------------------- ----------

  1                  mane            ph.dbm                            hod                                  101

 2         patil              ph.dob                            principal                            102

 3 kadam         mcaio                            teacher                              103

SQL> create or replace function bf(n in char)

  3  e  number;

  5  select count(tname) into e

  6  from teacher10,college1

  7  where college1.code=teacher10.code

  8  and qualification='ph.d'

  9  and cname=n;

 10  return e;

 12  /

  2  dbms_output.put_line('output:'||bf('bjs'));

Write a cursor which will display college wise teacher details.

  2  cursor v is select cname,teacher10.tid,tname,qualification,specialisation,desg

  3  from college1,teacher10

  4  where college1.code=teacher10.code

  5  order by cname;

  6  v1 v%rowtype;

  7  begin

  8  open v;

  9  loop

 10  fetch v into v1;

 11  exit when v%notfound;

 12  dbms_output.put_line('output:'||v1.cname||''||v1.tid||''||v1.tname||''||v1.

qualification||''||v1.specialisation||''||v1.desg);

 13  end loop;

 14  close v;

 15  end;

Output:bjs                          1mane                         ph.dbm                           hod

output:modern                       2patil                        ph.dob                           principal

output:wadiya                       3kadam                        mcaio                           teacher

Country (CId, CName , no_of_states, area, location, population)

 Citizen( Id, Name, mother_toung, state_name) 

Relation between Country and Citizen is one to many. Constraint: Primary key, area should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Write a procedure to display name of citizens having mother toung “Marathi “ and from “India”;

SQL> Create table country2(cidint primary key,cname char(29),nosint,area char

(29),loc char(29),population int);

SQL> Create table citizen3(id int primary key,name char(29),mt char(29),snamech

ar(29),cidint,constraint fk_country2citizen3 foreign key(cid)references country

 CID                                       NOT NULL NUMBER(38)

 POPULATION                                         NUMBER(38)

  CID CNAMENOS     AREA            LOC            POPULATION

       101 india 34 ert gsuy                              1230000

102               us                       34ertgsuy                             2120000

103 china 23ertgsuy                              200000

         1 raghavmarathi maharashtra                          10

 2 shamalmarathi maharashtra                          102

 3 aaravmarathi maharashtra                          102  

SQL> create or replace procedure vs as

  2  cursor g is select name from citizen3,country2

  3  where country2.cid=citizen3.cid

  4  and mt='marathi'

  5  and cname='india';

  6  g1 g%rowtype;

  8  open g;

 10  fetch g into g1;

 11  exit when g%notfound;

 12  dbms_output.put_line('output:'||g1.name);

 14  close g;

SQL> execute vs();

output:raghav

Write a trigger which will fire before insert or update on country having no_of_state less than equal to zero. (Raise user defined exception and give appropriate message)

SQL> create or replace trigger df

  3  on country2

  6  if(:new.nos<=0)  then

  7  raise_application_error(-20002,'enter more than 0');

SQL> insert into country2 values(108,'us',23,'dwe','eeree',1220000);

SQL> insert into country2 values(109,'uk',-9,'ha','jk',19000);

insert into country2 values(109,'uk',-9,'ha','jk',19000)

ORA-20002: enter more than 0

ORA-06512: at "SYSTEM.DF", line 3

ORA-04088: error during execution of trigger 'SYSTEM.DF'

Wholesaler (w_no, w_name, address, city)

 Product (product_no, product_name, rate)

 Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute. Constraint: Primary key, rate should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Write a procedure which will display details of products supplied by “Mr. Patil” 

   SQL> create table wholesaler(wnoint primary key,wname char(29),addr char(28),city char(28));

SQL> insert into wholesaler values(4,'mr.patil','pune','pune');

 1  raghav                    wagholi              pune

 2   aarav                         thane           mumbai

  3   vijay                         thane         mumbai

4   mr.patil                      pune          pune

SQL> insert into wp values(123,4,12);

  QUANTITY        WNO        PNO

       123          4         12

SQL> create or replace procedure bn as

  2  cursor c1 is select product.pno,pname,rate

  3  from wholesaler,product,wp

  4  where wholesaler.wname='mr.patil'

  5  and wholesaler.wno=wp.wno

  6  and product.pno=wp.pno;

  7  c c1%ROWTYPE;

  9  open c1;

 11  fetch c1 into c;

 12  exit when c1%NOTFOUND;

 13  dbms_output.put_line(c.pno||''||c.pname||''||c.rate);

 15  close c1;

SQL> execute bn();

12          pencil                      11

Write a cursor which will display wholesaler wise product details

(Use Parameterized cursor)

  2  cursor c1(n in char) is select wname,product.pno,pname,rate from wholesaler,product,wp

  3  where wholesaler.wno=wp.wno

  4   and product.pno=wp.pno

  5  and wholesaler.wname=n

  6   order by wname;

  9  open c1(&n);

 13  dbms_output.put_line(c.wname||' '||c.pno||' '||c.pname||' '||c.rate);

Enter value for n: 'vijay'

old   9: open c1(&n);

new   9: open c1('vijay');

vijay                         13     notebook                     23

Slip no-24 

Student (rollno, sname, class, timetable) 

Lab (LabNo, LabName, capacity, equipment) 

Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a procedure to display details of students which perform practical sessions in a given Lab.

SQL> Create table student2(rnoint primary key,sname char(29),class int,timetabl

SQL> Create table lab2(lnoint primary key,lname char(29),capacity int,equ char(

23),rnoint,constraint fk_student2lab2 foreign key(rno)references student2(rno));

SQL> create or replace procedure kj(n IN number) as

  2  cursor c1 is select student2.rno,sname,class,timetable

  3  from lab2,student2

  4  where student2.rno=lab2.rno

  5  and lab2.lno=n;

  6  c c1%rowtype;

  8   open c1;

  9   loop

 10   fetch c1 into c;

 11   exit when c1%NOTFOUND;

 12   dbms_output.put_line('output:'||c.rno||' '||c.sname||' '||c.class||''||c.t

 13   end loop;

 14   close c1;

SQL> execute kj(11);

output:1 raghav                        1210

2)Write a trigger which will fire before delete on Lab (Raise user defined exception and give appropriate message)

SQL> create or replace trigger kd

  2   before delete

  3  on lab2

  5  declare

  6   del_lab exception;

  8   raise del_lab;

  9  exception

 10   when del_lab then

 11   raise_application_error(-20001,'Record can not be deleted');

SQL> DELETE FROM lab2 WHERE equ='computer';

DELETE FROM lab2 WHERE equ='computer'

ORA-20001: Record can not be deleted

ORA-06512: at "SYSTEM.KD", line 7

ORA-04088: error during execution of trigger 'SYSTEM.KD'

 Gym (Name, city, charges, scheme)

 Member (ID, Name, phoneNo, address)

 Relation between Gym and member is one to many. Constraint: Primary Key, charges must be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a procedure to display member details of gym located at “Pimpri’” 

1.create table gym23(name varchar2(29) primary key,city varchar2(28),charges int,scheme varchar2(29));

2.create table member9(id int primary key,mname varchar2(29),phnoint,addr varchar2(28),name varchar2(29),constraint fk_gym23member9 foreign key(name)references gym23(name));

SQL> insert into member9 values(21,'shamal',7768886542,'pimpari','xyz');

21 shamal                  7768886542       pimpari     xyz

SQL> create or replace procedure hj as

  2  cursor c1 is select id,mname,phno,addr from gym23,member9

  3   where gym23.name=member9.name

  4  and  city='PIMPRI';

  5  res c1%ROWTYPE;

  7   open c1;

  8   loop

  9   fetch c1 into res;

 10   exit when c1%NOTFOUND;

 11   dbms_output.put_line(res.id||' '||res.mname||' '||res.phno||' '||res.addr);

 12   end loop;

 13   close c1;

 15  /

SQL> execute hj();

32 ANIKET 8605471492 WAGHOLI

Write a cursor which will display gym wise member details.(Use Parametrized Cursor)

  2  g_var gym23%rowtype;

  3  m_var member9%rowtype;

  4  cursor g (name char) is

  5  select * from gym23;

  6  cursor m is

  7  select * from member9;

  9  open g('abc');

 11  fetch g into g_var;

 12  exit when g%notfound;

 13  dbms_output.put_line(g_var.name);

 14  open m;

 15  loop

 16  fetch m into m_var;

 17  exit when m%notfound;

 18  dbms_output.put_line(m_var.id||''||m_var.mname||''||m_var.phno||''||m_var.a

 19  end loop;

 20  close m;

 21  end loop;

 22  close g;

 23  end;

 24  /

11raghav7875657575wagholi

12aarav7565456478wagholi

13shamal6565657668pune

Project (pno, pname, start_date, budget, status)

 Department (dno, dname, HOD, loc) 

The relationship between Project and Department is Many to One. Constraint: Primary key. Project Status Constraints: C – Completed, P - Progressive, I – Incomplete Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Write a procedure to display the name of HOD who has completed maximum project. 

create table project(pnoint primary key,pname char(29),sdatedate,dudgetint,status char(28)check(status in('c','i','p')));

create table department(dnoint primary key,dname char(24),hod char(28),loc char(29),pnoint,constraintfk_projectdepartment foreign key(pno)references project(pno)); 

12  commercedespande                pune             2        

13  computer science            kadam                     pune               3   

23   slbdr                       pune              12

SQL> create or replace procedure ef as

  2  cursor m is select department.hod from department,project

  3  where project.pno=department.pno

  4  and pname=(select max(pname) from  project);

  5  m1 m%rowtype;

  7  open m;

  9  fetch m into m1;

 10  exit when m%notfound;

 11  dbms_output.put_line('ouput:'||m1.hod);

 13  close m;

SQL> execute ef();

Write a trigger which will fire before insert or update on project having budget less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL> create or replace trigger dx

  3  on project

  6  if(:new.dudget<=0) then

  7  raise_application_error(-20006,'enter more than 0');

SQL> insert into project values(78,'ds','09/jan/20',1290000,'p');

SQL> insert into project values(77,'hgo','09/jan/20',-43500,'i');

insert into project values(77,'hgo','09/jan/20',-43500,'i')

ORA-20006: enter more than 0

ORA-06512: at "SYSTEM.DX", line 3

ORA-04088: error during execution of trigger 'SYSTEM.DX'

Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should be greater than 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a procedure to display the plan having minimum response.

SQL> create table plan11(pnoint primary key,pname varchar2(24),nooffreecallsint,freecalltimetimestamp,famtint check(famt>0));

SQL> create table cust11(cnoint primary key,cname varchar2(28),mnoint,pnoint,constraint fk_plan11cust11 foreign key(pno)references plan11(pno));

 PNO PNAME                    NOOFFREECALLSFREECALLTIME FAMT

-------------------------------------------------------------------------------------------------------

11 ddsd                                1209-JAN-07 12.09.09.000000 PM   1200

12 ytti                                2202-FEB-03 11.05.07.000000 AM1300

13 kuio                                2301-MAR-02 11.02.03.000000 AM1400

SQL> create or replace procedure yh as

  2  cursor k is select pname from plan11

  3  where  nooffreecalls=(select min(nooffreecalls) from plan11);

  4  k1 k%rowtype;

  6  open k;

  8  fetch k into k1;

  9  exit when k%notfound;

 10  dbms_output.put_line('output:'||k1.pname);

 12  close k;

SQL> execute yh();

Write a trigger which will fire before insert or update on mobile number having length less than or greater than10. (Raise user defined exception and give appropriate message) 

SQL> create or replace trigger mn

  3  on cust11

  6  if(length(:new.mno)<10 or length(:new.mno)>10) then

  7  raise_application_error(-20007,'enter must 10 number');

SQL> insert into cust11 values(89,'shamal',7768886542,11);

SQL> insert into cust11 values(90,'shamal',776888654,12);

insert into cust11 values(90,'shamal',776888654,12)

ORA-20007: enter must 10 number

ORA-06512: at "SYSTEM.MN", line 3

ORA-04088: error during execution of trigger 'SYSTEM.MN'

SQL> insert into cust11 values(91,'shamal',77688865423,13);

insert into cust11 values(91,'shamal',77688865423,13)

 Bill (billno, day, tableno, total)

Menu (dish_no, dish_desc, price)

 The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a function which accept a table number and display total amount of bill for a specific table 

SQL> select * from bill  ;

BNO DAY                                TNO      TOTAL

---------- --------------------------- ---------- -----------------------------------

         1 monday                              23       123

 DNO D_DESC                             PRICE

---------- ----------------------------- ------------------

SQL> create table bm(bno int references bill(bno),dno int references menu(dno));

SQL>desc  bm

SQL> insert into bm values(1,11);

SQL> insert into bm  values(1,12);

SQL> insert into bm values(2,13);

SQL> insert into bm values(3,12);

SQL> insert into bm values(3,13);

 BNO        DNO

 1         11

SQL> create or replace function df(n IN number)

  5  select total into res from bill

  6   where tno=n;

  2  dbms_output.put_line('output:'||df(21));

Write a cursor which will display table wise menu details.

  2  cursor z is select bill.tno,menu.dno,d_desc,price

  3  from bill,menu,bm

  4  where bill.bno=bm.bno

  5  and menu.dno=bm.dno

  6  order by tno;

  7  z1 z%rowtype;

  9  open z;

 11  fetch z into z1;

 12  exit when z%notfound;

 13  dbms_output.put_line(z1.tno||''||z1.dno||''||z1.d_desc||''||z1.price);

 15  close z;

21 12 fsd 659

21 13 jho 467

23 12 fsd 659

23 11 asd 234

23 13 jho 467

Employee (emp_id, emp_name, address) 

Investment (inv_no, inv_name, inv_date, inv_amount) 

Relation between Employee and Investment is One to Many. Constraint: Primary key, inv_amount should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

Write a function which will return total investment amount of a particular client.

2)Create table investment1(inoint primary key,iname char(29),idatedate,iamtint,eidint,constraintfk_employeeinvestment1 foreign key(eid)references employee(eid));

INO                                       NOT NULL NUMBER(38)

  EID ENMAE            ADDR

---------- ------------------------- --------------------------

101 raghavpune

103 aaravwagholi

102vijaymumbai

1raghavpune

SQL> create or replace function fr(xz in char)

  2     return number as

  3     sd number;

  4     begin

  5     select sum(investment.iamt) into sd

  6     from employee,investment

  7     where employee.eid=investment.eid

  8      and enmae=xz;

  9      return sd;

 10     end;

 11      /

  2  dbms_output.put_line('output:'||vc('raghav'));

output:7020000

Write a trigger which will fire before insert or update on Investment having investment amount less than 50000. (Raise user defined exception and give appropriate message)

SQL> create or replace trigger cx

  3  on investment

  6  if(:new.iamt<50000) then

  7  raise_application_error(-20005,'enter more than 50000');

SQL> insert into investment values(19,'hj','09/jan/09',12000000,101);

SQL> insert into investment values(167,'hj','09/jan/09',12000,102);

insert into investment values(167,'hj','09/jan/09',12000,102)

    *

ORA-20005: enter more than 50000

ORA-06512: at "SYSTEM.CX", line 3

ORA-04088: error during execution of trigger 'SYSTEM.CX'

Library(Lno, Lname, Location, Librarian, no_of_books) 

Book(Bid, Bname, Author_Name, Price, publication) 

Relation between Library and Book is one to many. Constraint: Primary key, Price should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Write a procedure to display names of book written by “Mr. Patil” and are from “DPU Library”.

SQL> desc library0;

 LNAME                                              CHAR(26)

 LOCATION                                           CHAR(27)

 LIBRARIAN                                          CHAR(25)

 NOB                                                NUMBER(38)

SQL> select * from library0;

  LNO LNAME                      LOCATIONLIBRARIAN                        NOB

------------------------- -----------------------------------------------------------------------

 1 dcmi                       punemane                            1000

 2 dipak                      mumbaijadhav                         13200

 3 divy                       wagholikadam                          13000

 4ganesh                     punebhagat                          1300

 5rani                       Mumbai      patil                           1800

9 dpu                        punehg                              1200

 12 dpu                        punehg                              1200

7 rows selected.

SQL> desc book0;

 BID                                       NOT NULL NUMBER(38)

 BNAME                                              CHAR(25)

 A_NAME                                             CHAR(28)

 PUBLICATION                                        CHAR(28)

SQL> select * from book0;

  BID BNAME                     A_NAME                            PRICEPUBLICATION                         LNO

---------------------------- ----------------------------------------------------------------------------------------------

 21 aai                       patil                               120ghu                                   1

  2   karwhar              pati                                120jds                                   2

 23            gh                      s.b.rathod                    230raghav1

34 life                      patil                               239nirali                                9 

67        dreams                patil                               239nirali                               12

45 hgjh                      hsgwi                                12whw  1

SQL> create or replace procedure sn as

  2  cursor i is select bname from book0,library0

  3  where library0.lno=book0.lno

  4  and a_name='patil'

  5  and lname='dpu';

  6  i1 i%rowtype;

  8  open i;

 10  fetch i into i1;

 11  exit when i%notfound;

 12  dbms_output.put_line('output:'||i1.bname);

 14  close i;

SQL> execute sn();

output:life

output:dreams

Write a trigger which will fire before insert or update on book having price less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL> create or replace trigger mk

  3  on book0

  6  if(:new.price<=0) then

  7  raise_application_error(-20009,'enter more than 0');

Trigger created

SQL> insert into book0 values(78,'fk','bhuj',470,'foyp',4);

SQL> insert into book0 values(56,'fk','bhuj',0,'fgf',5);

insert into book0 values(56,'fk','bhuj',0,'fgf',5)

ORA-20009: enter more than 0

ORA-06512: at "SYSTEM.MK", line 3

ORA-04088: error during execution of trigger 'SYSTEM.MK'

2 comments:

Could u provide slip 9 Q3 2nd question... Also are all the programs executed?

Check Slip no 9 solution. All programs are executing