DBMS Practical Assignment
Assignment -10
Doctor_master(doctid,doctname,address,specialization)
Patient_master(patid,doctid,patname,DOB,address,phoneno)
Doctid and patid should be declared as primary key while creating table.
Create appropriate relationship between tables. “Specialization” field must not be null.
Queries:
1. Display patients treated under the doctor “Mr.Mehta”.
2. Remove the patient of doctor “Mr.X” with specialization “Surgeon”.
3. Find out patients whose age is greater than 15 years.
4. Display the doctors whose name contains “D” and last charcter is “A”.
5. Display total patients treated by each doctor.
6. Display patients who born before year 2001.
7. Removes patients whose phone number ends with digits 7 or 9.
8. Add new column “treatment_date” in table patients.
9. Display patients who have taken treatment in current month.
10. Display doctor who have treated maximum patients.
Assignment -11
Vehicle(vid,name,color,price,company)
Customer(cid,name,address,phoneno,email)
Sell(vid,cid,selldate,amount)
Constraints:
vid and cid must be declared as primary key while creating table.the colour value must be
“red”,”black” and “white”.
Create appropriate relationship between tables.
Queries:
1. Add new column tax in vehicle table.
2. Display the vehicles which are not sold in last one year.
3. Display vehicles with maximum profit.
4. Display vehicle having highest sell.
5. Display total amount of sale of each vehicle.
6. Display total number of vehicle of same company.
7. Display vehicle details purchased in current month.
8. Display vehicles with “red” colour.
9. Display vehicle details whose name starts with “H”.
10.Count total vehicle sale in current month whose company name contain minimum 10
charaters.
Assignment -12
Product(pid,pname,ptype,price,qty)
Salesman(sid,name,area,DOJ,target)
Sales(pid,sid,saledate,qty)
Constraints:
pid and sid must be declared as primary key while creating table.Create appropriate relationship
between tables.
Qty field must not null and default value should be 15.
Queries:
1. Display total qty sold of product “xyz”.
2. Count total products sale by each salesman.
3. Display salesman who sold more products than given target.
4. Display products which are not sold in last one year.
5. Display salesman who have joined in current month.
6. Count total products of each type.
7. Display salesman whose name contain more than 5 charcters and joined in current year.
8. Display average sale of each salesman.
9. Remove the column area from salesman table.
10. Incrase the target of employee by 50 who have more than five year of experience.
The Ravi Tutorials is an FREE Website or Blog which has been created to provide simple and easy online education to the student who are interested to study different Technology related to computer science.