Answer Happy

Example databases Library Customer (name, phone, email, libraryCardNumber) LibraryItem (ID, loanPeriod) Book (ID, title, author, language, year) CD (ID, title, artist, year, length) DVD (ID, title, year, length) Loan (itemID, libraryCardNumber, startDate, endDate, returned). The library database consists of six relations. The relation LibraryCustomer contains the information about library customers. Each customer is identified using the attribute libraryCardNumber. The relation Item contains all possible loanable items and tells the loan period for the item. Loanable items can be books, CDs, or DVDs and each have their own relation, Book, CD, and DVD, respectively. The ID in Item matches one ID in Book, CD, or DVD. The relation Loaned contains the past and current information about loaned items. The attribute returned is O if the item is not yet returned and 1 if it is. Customers (email, name, address, birthday) Product (prodID, description, price, weight, type) Orders (orderID, customer, date, payment) OrderContent (orderID, product, amount). This is the schema for the online shop. The relation Customers contains the information about customers, each tuple with a unique email. The relation Product contains the product information. The attribute type can be "food”, ”hygiene", "hobby", or "kitchen". The relation Orders keeps track of the orders of the customers, with a unique orderID. The attribute customer contains the email of the customer making the order. The date of the order is in the format "YYYY-MM-DD”. The attribute payment is "credit”, "paypal", or "mobilepay". The relation OrderContents tells which products belong to which order, and the amount of the products in the order. Doctor (id, name, email) Patient (ssNo, name, birthday, heights, weight, phone, vaccine) Appointment (patient, doctor, date, description). These are the relations in the hospital database. The relation Patient contains the information about patients. The attribute vaccine takes the value 1 if the patient has had the corona vaccine and 0 otherwise. The relation Doctor contains the working ID, name, and email of a doctor. The relation Appointment stores the information about appointments between doctors and patients. It is assumed that a patient can meet a specific doctor only once a day. The attribute description in Appointment can take values "checkup”, 'acute”, "treatment", and "vaccine”, describing on the nature of the appointment.
SQL queries 8. (1p.) Consider the library database. Write the following query in SQL: find the names and emails of all the library customers. 9. (1p.) Consider the library database. Write the following query in SQL: find the title and author of all books released after the year 2000. (Please note that this is similar to Exercise 1. How could the relational algebra expression be "translated" into SQL?) 10. (1p.) Consider the library database. Write the following query in SQL: find the name, phone and email of the owner of the library card number 842-853-8174. 11. (1p.) Consider the library database. Write the following query in SQL: find the books (all fields) that have "Harry Potter" in their title and are released after the year 2006. 12. (1p.) Consider the library database. Write the following query in SQL: find the name, phone number, and email of customers that have unreturned loans. (Please note that this is similar to Exercise 2. How could the relational algebra expression be "translated” into SQL?) 13. (1p.) Consider the library database. Write the following query in SQL: find the names of library customers who have ever loaned any book written by Leo Tolstoy. 14. (1p.) Consider the library database. Write the following query in SQL: find IDs and titles of all the loanable items with loan period over 14 days. 15. (1p.) Consider the online store database. Write the following query in SQL: find the IDs of orders containing some food item or some hobby item. 16. (1p.) Consider the online store database. Write the following query in SQL: find the IDs of orders containing some food item and some hygiene item. 17. (1p.) Consider the online store database. Write the following query in SQL: find all the descriptions of the products the user with the email [email protected] has ever ordered, ordered alphabetically. List each item only once. 18. (2 p.) Consider the online store database. Write the following query in SQL: find the emails and names of customers who have made at least two orders with different different payment methods. List each customer only once.