Practice from Database Management System with detailed solutions and model answers from past Tribhuvan University exams.
Consider a database system with following schemes:
Restaurant(Rname, Rlocation, Rfund)
Cook(Cname, Cspeciality)
Worksat(Cname, Rname, Workinghrs, Shift)
Food(Fname, Cname, Catagory)
Now write SQL statements and relational algebra statements for following Queries:
a) Select the name and location of all restaurants.
b) Find the working hours of cook named "Sita".
c) Select name of the foods cooked by "Ramesh".
d) Use join to select name of restaurants where food of category "breakfast" is available.
e) Find the name of cooks who work as "KFC"
How Order By and Group BY Having Clause in SQL are different? Illustrate with suitable examples.
Given the schema as below, write relational algebra and SQL queries for following:Sailors(sid, sname, rating, age)Boats(bid, bname, colour)Reserves(sid, bid, reservedate)
a) Find name, rating and age of all the sailors.
b) Find the name and color of boats having color blue.
c) Find name of the boats reserved on date 2021/07/20.
d) Find the names of boats and sailors who have reserved boats having color green.
e) Use Left and Right Outer Join between Sailors and Reserves.
How different join operations can be performed? Explain with proper examples. Consider a database of a shipment company. Shipped items are received into the system at a single retail center. Shipped items make their way to their destination via one or more standard transportation events (i.e. flights, truck deliveries). The transportation events follow a regular schedule. Create an ER diagram to represent this information.
Consider the following tables: Book(Bookid, Title, Publisher, Published Date, Price)
Book_Authors (Bookid, Author name)
Borrower (Card No, Name, Address, Contact)
Bookissue (BookId, CardNo, IssuedDate, Due Date)
Write SQL query for the following:
a) Find the name of the books taken by Ramesh Sharma.
b) Find the Issued book name and the borrower's name who have Issued date before 15th May, 2023 and due date after 15th June, 2023.
c) Find the borrower's name who has taken the book written by Laxmi Prashad Devkota.
d) Find the number of books Issued on the date 25th April, 2023.
Consider the following employee database, where primary keys are underlined Supplier (supplier-id, supplier-name, city) Suppliers (Supplier-id, parts-id, quantity) Parts (part-id, par-name, color, weight)
a) Write a SQL query to create above table.
b) Find the name of all suppliers who has supplied parts with quantity more than100 and the name of all parts supplied by "RD Traders"
c) Find the number of parts supplied by "S02"
d) Find the name of suppliers located in city "KTM".