- Parent Category: Programming Assignments' Solutions
We Helped With This Database Programming Assignment: Have A Similar One?
|Subject||MySQL | MSSQL | ORACLE|
|More Info||Sql Help|
Short Assignment Requirements
These are some easy assignments in high school level.I don't know what is mySQL or whatever, just simple SQL, entry level
Question 1 (30) Flights (flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time, price: real) Aircraft (aid: integer, aname: string, cruisingrange: integer) Certified (eid: integer, aid: integer) Employees (eid: integer, ename: string, salary: integer) Note that the Employees relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the following queries in SQL. 1. Find the names of pilots certified for some Boeing aircraft. 2. For each pilot who is certified for more than three aircraft, find the eid and the maximum cruising range of the aircraft for which she or he is certified. 3. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.
Question 2 (10) Book (BID, Title, Author, Type, Copies) Student (SID, Name, Address, RentedBooks) Rental (BID, SID, Rent Date, ReturnDate) The primary keys are underlined. Book stores information about the books in a student club library. Each is given a book ID (BID), title, author, type (documentary, novel, etc.), and the number of remaining copies in the library. The attributes of Student are self-descriptive, except RentedBooks which indicates the number of books borrowed but not returned yet by the student. BID in Rental refers to the BID of Book. Similarly, SID in Rental refers to the SID of Student. Attributes Rent Date and ReturnDate store the date when a book is borrowed and the date when it must be returned, respectively. To find the name of the student who has booked every type of books.
Question 3 (60) Flowers (ProductId, Description, Price) Customers (CustomerId, CName, CAddress, Credit Card) Orders (OrderId, ProductId, CustomerId, Date, RName, RAddress) Mailed (OrderId, Mailing Date, TrackingNo) Subcontracted (OrderId, ShopName, ShopPhone) The shop offers a selection of different bouquets (bunches of flowers) described in the table Flowers, in which ProductId identifies the bouquet to be delivered. The table Customers contains information about customers. The table Orders contains information about the Date when the flowers should be delivered and recipients (to whom the flowers should be delivered). Flowers of each order are either sent by mail to recipient directly or given to a local flower shop for pickup, as described in tables Mailed and Subcontracted, respectively. 1. Print the recipient name and address of all orders delivered in Brooklyn city after 01/01/18. 2. Print the name of customers who have spent over $300 and the total amount of money they have spent. 3. Print the name of customers who prefer mailing their flowers directly rather than sending them to local flower shops (i.e., delivering flowers by post more frequently in the past). 4. Draw the ERD for the described database.