2013 pattern slips
Slip1
Consider the following Entities and Relationships
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.
Slip2
Consider the following Entities and Relationships
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.
•Displaydepartment wise employee list.
Slip3
Consider the following Entities and Relationships
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,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 .
•Displayall project working under 'Mr.Desai'.
Slip4
Consider the following Entities and Relationships
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.
•Findnames of guest with maximum room charges.
Slip5
Consider the following Entities and Relationships
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.
•Displaybook names having price between 100 and 200 and published in the year 2013.
Slip6
Consider the following Entities and Relationships
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.
Slip7
Consider the following Entities and Relationships
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.
•Displayshiftwise employee details.
Slip8
Consider the following Entities and Relationships
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.
•Displaytotal number of bills collected from each table on 01/12/2013.
Slip9
Consider the following Entities and Relationships
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'
Slip10
Consider the following Entities and Relationships
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.
•Displaydetails of the boats which is sailed maximum times on Sundays.
Slip11
Consider the following Entities and Relationships
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 itscost.
Slip12
Consider the following Entities and Relationships
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 ofCrocin.
Slip13
Consider the following Entities and Relationships
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.
Slip14
Consider the following Entities and Relationships
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 Mumbai.
•Display loan details in descending order of their amount.
•Display all branches located in Mumbai, Pune and Nasik.
Slip15
Consider the following Entities and Relationships
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.
•Updatebudget of a project done by employees of Computer Department by 15%.
Slip16
Consider the following Entities and Relationships
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 branchwhose city starts from 'A'.
Slip17
Consider the following Entities and Relationships
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.
•Displaythe donor information with blood group whose city name contains “sh” in it.
Slip18
Consider the following Entities and Relationships
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’.
•Findthe bust having maximum capacity from ‘Nigadi’ to 'Kothrud'..
Slip19
Consider the following Entities and Relationships
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.
Slip20
Consider the following Entities and Relationships
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.
Slip21
Consider the following Entities and Relationships
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’.
•Displaypublisher wise book details.
Slip22
Consider the following Entities and Relationships
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.
Slip23
Consider the following Entities and 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.
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.
Slip24
Consider the following Entities and 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 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.
Slip25
Consider the following Entities and Relationships
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”.
•Whichparty has maximum politicians
Slip26
Consider the following Entities and 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 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.
•Displaycoach wise player details.
Slip27
Consider the following Entities and Relationships
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.
Slip28
Consider the following Entities and 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 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’.
•Decrementrate of all products by 5% supplied by wholesaler from 'Pune ' city.
Slip29
Consider the following Entities and 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 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.
Slip30
Consider the following Entities and Relationships
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”.
