GTU PL/SQL PROGRAMS

Question 1  [ SHOW SOLUTION ]
Customer Item schema queries which fall in all the categories mentioned above.
CUST(Custno, cname, state, phone)
ITEM(itemno, Itemname, Itemprice, Qty_hand)
INVOICE(Invno, invDate, Custno)
INVITEM(Invno, Itemno, Qty)


Question 2  [ SHOW SOLUTION ]
STUDENT(rollno,name,class,birthdate)
COURSE(courseno, coursename, max_marks, pass_marks)
SC(rollno,courseno,marks)


Question 3  [ SHOW SOLUTION ]
HOSTEL (H#, hname, haddr, total_capacity, warden)
ROOM (h#, r#, rtype, location, no_of_students, status)
CHARGES (h#, rtype, charges)
STUDENT (sid, sname, saddr, faculty, dept, class, h#, r#)
FEES (sid, fdate, famount)


Question 4  [ SHOW SOLUTION ]
Screen(screen_id,location ,seating_cap)
Movie(movie_id,movie_name,date_of_release)
Current(screen_id,movie_id,date_of_arrival,date_of_closure)
Value of screen_id must start with letters ‘S’.
Attribute location can be any one of ‘ FF’, ‘SF’, or ‘TF’.
Date_of_arrival must be less than date_of_closure.


Question 5  [ SHOW SOLUTION ]
DISTRIBUTOR (DNO, DNAME, DADDRESS, DPHONE)
ITEM (ITEMNO, ITEMNAME, COLOUR, WEIGHT)
DIST_ITEM (DNO, ITEMNO, QTY)


Question 6  [ SHOW SOLUTION ]
WORKER (worker_id, name, wage_per_hour, specialised_in, manager_id)
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)


Question 7  [ SHOW SOLUTION ]
PUBLISHER(publ_id, publ_name, contact_person, contact_addr, contact_phone)
CATEGORY(cat_id, cat_details, max_books, duration)
BOOK_MASTER(book_id, bname, isbn_no, total_copies, publ_id)
MEMBER(member_id, mname, cat_id, mem_ship_dt)


Question 8  [ SHOW SOLUTION ]
APPLICANT (aid, aname, addr, abirth_dt)
ENTRANCE_TEST (etid, etname, max_score, cut_score)
ETEST_CENTRE (etcid, location, incharge, capacity)
ETEST_DETAILS (aid, etid, etcid, etest_dt, score)

ISSUE(ISSUE_id, member_id, book_id, issu_ret, issue_ret_dt)