Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

Consider the following Entities and Relationships [30 Marks] Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
Relation between Customer and Loan is Many to Many
Constraint: Primary key, loan_amt should be > 0.
Create a Database in 3NF & write queries for following
. • Find details of all customers whose loan is greater than 10 lakhs.
• List all customers whose name starts with 'ba'.
• List names of all customers in descending order who has taken a loan in Nasik city.
• Display customer details having maximum loan amount.
• Calculate total of all loan amount

2)Consider the following Entities and Relationships [30 Marks] Department (dept_no, dept_name, location)
Employee (emp_no, emp_name, address, salary, designation)
Relation between Department and Employee is One to Many
Constraint: Primary key, salary should be > 0.
Create a Database in 3NF & write queries for following.
• Find total salary of all computer department employees.
• Find the name of department whose salary is above 10000.
• Count the number of employees in each department.
• Display the maximum salary of each department.
• Display department wise employee list.

3)Consider the following Entities and Relationships [30 Marks] Project (pno, pname, start_date, budget, status) Department (dno, dname, HOD) Relation between Project and Department is Many to One Constraint: Primary key. Project Status Constraints: C – completed, P-Progressive, I-Incomplete Create a Database in 3NF & write queries for following. • List the project name and department details worked in projects that are ‘Complete’. • Display total budget of each department. • Display incomplete project of each department • Find the names of departments that have budget greater than 50000 . • Display all project working under 'Mr.Desai'.

4)Consider the following Entities and Relationships [30 Marks] Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
Relation between Room and Guest is One to One.
Constraint: Primary key, no of days should be > 0.
Create a Database in 3NF & write queries for following.
• Display room details according to its rates in ascending order.
• Find the names of guest who has allocated room for more than 3 days.
• Find no. of AC rooms.
• Display total amount for NON-AC rooms.
• Find names of guest with maximum room charges.

5)Consider the following Entities and Relationships [30 Marks] Book (Book_no, title, author, price, year_published)
Customer (cid, cname, addr)
Relation between Book and Customer is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be >0.
Create a Database in 3NF & write queries for following.
• Display customer details from 'Mumbai'.
• Display author wise details of book.
• Display all customers who have purchased the books published in the year 2013.
• Display customer name that has purchased more than 3 books.
• Display book names having price between 100 and 200 and published in the year 2013.

Consider the following Entities and Relationships [30 Marks] Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
Relation between owner and Property is One to Many.
Constraint: Primary key, rate should be > 0
Create a Database in 3NF & write queries for following.
• Display area wise property details.
• Display property owned by 'Mr.Patil' having minimum rate.
• Display all properties with owner name that having highest rate of properties located in Chinchwad area.
• Display owner wise property detail.
• Display owner name having maximum no. of properties.

7)Consider the following Entities and Relationships [30 Marks] Employee (emp_no, name, skill, payrate)
Position (posting_no, skill)
Relation between Employee and Position is Many to Many with day and shift as descriptive attribute.
Constraint: Primary key, payrate should be > 0.
Create a Database in 3NF & write queries for following.
• Find the names and rate of pay all employees who allocated a duty.
• Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.
• Display a list of names of employees who have skill of chef and who has assigned a duty.
• Display emp_no and dates for all employees who are working on Tuesday and at least one other day.
• Display shiftwise employee details.

8)Consider the following Entities and Relationships [30 Marks] Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
Relation between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a Database in 3NF & write queries for following.
• Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.
• Find total amount collected by hotel on date 08/01/2013
• Count number of menus of billno 301.
• Display menu details having price between 100 and 500.
• Display total number of bills collected from each table on 01/12/2013.

9)Consider the following Entities and Relationships
[30 Marks] Musician (mno, mname, addr, phno)
Album (title, copy_right_date, format)
Relation between Musicians and Album is One to Many.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
• Display all albums composed by ‘A R Rehman’.
• Display musician details who have composed Audio album.
• Find all musicians who have composed maximum albums.
• Display musician wise album details.
• Display Musian details from 'Pune'

10)Consider the following Entities and Relationships [30 Marks] Sailor (sid, sname, age)
Boats (bid, bname, color)
Relation between Sailer and Boats is Many to Many with day as descriptive attribute.
Constraint: Primary key, age should be > 0.
Create a Database in 3NF & write queries for following.
• Display details of all boats sailed by sailor ‘Ram’.
• Display Sailor names working on blue boat.
• Count number of boats sailed by each sailor.
• Find the name of sailor who sailed the boat on both Tuesday & Friday.
• Display details of the boats which is sailed maximum times on Sundays

11)Consider the following Entities and Relationships [30 Marks] Supplier (sid, sname, addr)
Parts (pid, pname, pdesc)
Relation between Supplier and Parts is Many to Many with cost as descriptive attribute.
Constraint: Primary key, cost should be > 0.
Create a Database in 3NF & write queries for following.
• Display Supplier details from 'Mumbai' city.
• Update cost by 25 % for all parts supplied by supplier ‘Mr. Pawar’.
• Display all parts supplied by each supplier.
• Display details of parts which are supplied at maximum price by each supplier.
• Display all suppliers who supply part ‘wheel’ and also display its cost.

12)Consider the following Entities and Relationships [30 Marks] Medical_store (mno, mname, city, phno)
Drug (dno, dname, type, company, price)
Relation between Medical_store and Drug as Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a Database in 3NF & write queries for following.
• Update price of drug by 5 % of 'ABC' Company.
• Display names of all medical store where ‘Crocin’ is available.
• Count total number of drug of ‘SunPharma’ company in ‘Sai medical’ store.
• Delete all drugs supplied by ‘SunPharma‘Company in ‘Sai medical’ store.
• Display the details of medical store having maximum quantity of Crocin

13)Consider the following Entities and Relationships [30 Marks] Account (ano, branchname, balance)
Customer (cust_no, cust_name, street, city)
Relation between Account and Customer is Many to Many.
Constraint: Primary key, balance should be > 500.
Create a Database in 3NF & write queries for following.
• Display customer details with balance between 100000 and 200000.
• Display customers having more than two accounts in Chinchwad branch.
• Delete account whose balance is below the balance <500.
• Select names of all Customers whose street name include the substring “road” and whose city is ‘Mumbai’.
• Find number of depositor for each branch.

14)Consider the following Entities and Relationships [30 Marks] Branch (bname , bcity ,assets)
Loan (loan_no, amount)
Relation between Branch and Loan is One to Many.
Constraint: Primary key, amount and assets should be > 0.
Create a Database in 3NF & write queries for following.
• Display total loan amount given by DYP branch.
• Find total number of loans given by each branch.
• Find the name of branch that have maximum assets located in
• Display loan details in descending order of their amount.
• Display all branches located in Mumbai, Pune and Nasik.

15) Consider the following Entities and Relationships [30 Marks] Employee (eno, ename, deptname, salary)
Project (pno, name, budget)
Relation between Employee and Project is Many to Many.
Constraint: Primary key, salary should be > 0.
Create a Database in 3NF & write queries for following.
• List the name of employee and department having salary > 50000.
• List names of all employees who works with ‘Ramesh’ on same project.
• Find the names of employees who are working on project having budget greater than 30000.
• List name of department that have at least two projects under them.
• Update budget of a project done by employees of Computer Department by 15%.

16)Consider the following Entities and Relationships [30 Marks] Branch (bno, bname, bcity, assets)
Account (acc_no ,balance)
Relation between Branch and Account is One to Many.
Constraint: Primary key, balance and assets should be > 0.
Create a Database in 3NF & write queries for following.
• Find the maximum account balance of each branch.
• Find branches where average account balance is more than 30000.
• Find names of all branches that have assets value greater than that of each branch in ‘pune’.
• Decrease 3% balance on account whose balance is greater than 100000.
• Display details of branch whose city starts from 'A'.

17)Consider the following Entities and Relationships [30 Marks] Donor (donor_no, donor_name, city)
Blood_Donation(bid,blood_group,quantity,date_of_collection)
Relation between Donor and Blood_Donation is One to Many.
Constraint: Primary key, blood_group should not be null.
Create a Database in 3NF & write queries for following.
• Display total blood quantity collected on 25th December 2013.
• Display total blood donated by each donor.
• Display Donor details having blood group 'A+ve'.
• Display the donor who has donated blood more than two times.
• Display the donor information with blood group whose city name contains “sh” in it.

18) Consider the following Entities and Relationships [30 Marks] Bus (bus_no, capacity, depot_no)
Route (rout_no, source, destination, no_of_stations)
Relation between Bus and Route is Many to One.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
• Find out the route details on which buses whose capacity is 20 runs.
• Display number of stations from 'Chinchwad' to ' Katraj'.
• Display the route on which more than 3 buses runs.
• Display number of buses of route ‘Swargate’ to ‘Hadapsar’.
• Find the bust having maximum capacity from ‘Nigadi’ to 'Kothrud'..

19)Consider the following Entities and Relationships [30 Marks] Person (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Person and Car is Many to Many with date and time as
descriptive attribute.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
• Display details of all persons who are driving ‘Alto’ car.
• Count the number of cars driven by each driver.
• Display car details manufactured before year 2000.
• In which day ‘Mr. Ram’ drives maximum number of cars.
• Display total number of persons who drives car in each year.

20)Consider the following Entities and Relationships [30 Marks] Person (pno, person_name, birthdate, income)
Area (area_name, area_type)
Relation between Person and area is Many to One.
Constraint: Primary key, income should be > 0, area_type should be rural or urban.
Create a Database in 3NF & write queries for following.
• Display persons having income less than 1 lakhs in PCMC Area.
• Display population of each area.
• Display persons details from 'Urban' area.
• Display the details of area having population greater than that of in Pune.
• Display details of person from each area having minimum income

21)Book (book_no, book_name, price) Publisher (pno, pname, city)
Relation between Book and Publisher is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a Database in 3NF & write queries for following.
• Display total quantity of each book .
• Display Publisher names from 'Pune'.
• Display all publisher publishing more than two books.
• Display publisher having average books price less than average books price of ‘BPV Publications’.
• Display publisher wise book details. .

22)Consider the following Entities and Relationships [30 Marks] Student (stud_reg_no, stud_name, class)
Competition (cno, cname, ctype)
Relation between Student and Competition is Many to Many
with rank and year as descriptive attribute.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
• Display students from class 'F.Y. BCA' and participated in 'E-Rangoli ' Competition.
• Find the number of student for programming competition.
• Display the names of first three winners of each competition.
• Display average number of students participating in each competition.
• Display total number of competition held in the year 2014.

23)Consider the following Entities and Relationships [30 Marks] 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.
Create a Database in 3NF & write queries for following.
• Display the plan having minimum response.
• Display customer details starting their mobile number with 98.
• Display the customer dfetails that are getting less number of free calls than that of the plan ‘Let’s Rock’.
• Delete the details of ‘John’ who has stopped ‘Go Max’ plan.
• Find the plan whose fixed amount is greater than 5000.

24)Consider the following Entities and Relationships [30 Marks] 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 Database in 3NF & write queries for following.
• Display employee details who have invested more than 100000.
• Display employee wise total investment amount.
• Display the employee names who invest on date 2nd Jan 2013.
• Display employee whose investment are more than 3.
• Find average investment of employees of Pune.

25) Consider the following Entities and Relationships [30 Marks] Politicians (pno, pname, telephone_no)
Party (party_code, party_name)
Relation between Politicians and Party is Many to One.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
• Display party names in ascending order.
• Find the party who is having less number of members than ‘Congress’ party.
• Display party wise politician name with details.
• Display the party name with the details of politicians whose name include “Rao”.
• Which party has maximum politicians

26)Consider the following Entities and Relationships [30 Marks] 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 Database in 3NF & write queries for following.
• Display players from ‘Delhi’.
• List all games which require more than 4 players.
• Find the total number of cricket players of 'sports club”.
• Display games having more number of players than that of football.
• Display coach wise player details.

27) Consider the following Entities and Relationships [30 Marks] Item (item_no, item_name, quantity)
Suppliers (sup_no, sup_name, address, city, phone_no)
Relation between Item and Supplier is Many to Many with rate and
discount as descriptive attribute.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
• Delete items having quantity less than 2.
• Display total number of suppliers who are supplying ‘Refrigerator’.
• Display all suppliers supplying ‘Washing Machine’ with minimum cost.
• Give supplier details who give maximum discount on each item.
• List suppliers supplying maximum number of item.

28)Consider the following Entities and Relationships [30 Marks] 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 Database in 3NF & write queries for following.
• Display wholesaler from 'Pune' city and supplying 'Monitor'.
• Display total number of wholesaler of each product.
• Display all wholesalers who are supplying ‘Keyboard’ with maximum price.
• Display total quantity of each product sold by ‘Mr. Khabia’.
• Decrement rate of all products by 5% supplied by wholesaler from 'Pune ' city.

29)Consider the following Entities and Relationships [30 Marks] 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 Database in 3NF & write queries for following.
• Display Policy details having maturity amount >500000.
• Find total number of policies purchased on 12th January 2013.
• Find clients who have more than 3 policies.
• Find all policies whose number of clients is same as that of policy ‘Jeevan Raksha’.
• Display policy wise client details.

30)Consider the following Entities and Relationships [30 Marks] Train (train_no, train_name, depart_time, arrival_time,source_stn, dest_stn)
Passenger (p_id,p_name,address,age, gender)
Relation between Train and Passenger is Many to Many with
seat_no, amount and date as descriptive attribute.
Constraint: Primary key, seat_no should not be null.
Create a Database in 3NF & write queries for following.
• Display passenger details having age>50
• Display total amount collected for “Kokan Express” on 5th January 2013.
• Find total number of passenger of “Pune to Mumbai” route.
• Cancel all the trains of 21st January 2013.
• Calculate total number of male passenger in “Shatabdi Express”.

FYBCA-II >> dbms - 30 Marks

Search

Project Categories

Recent Posts

Sign In