1. Assume the following table- Degree (degcode, name, subject) Candidate (seatno, degcode, name, semester, month, year, result) Marks (seatno, degcode, semester, month, year, papcode, marks) [degcode degree code, name name of the degree (Eg. BSc.), subject subject of the course (Eg. DBMS), papcode paper code (Eg. CSE-3105)] Solve the following queries using SQL; i. The name, subject and number of candidates for all degrees in which there are less than 5 candidates. ii. The names of all the candidate who have got highest total marks in BSc. DBMS. iii. All the degree codes which are there in the candidate table but not present in degree table in the order of degcode. iv. The name of all the candidates who have got less than 40 marks in exactly 2 subjects 2. Patient(ID, FirstName, SurName, Admission_Date, Dr_ID,Ward_No) Ailment(Ailment_ID, Name,Patient_ID) Ward(Ward_No, Ward_Name) Doctor(ID,SurName,First_Name,No_of_Patient, Ailment_ID) For the schema above, Write SQL to implement the following queries: i. List the ward number and ward name of the wards which have the most patients. ii. Show the Doctor's name who have the third highest no of patient. 3. Given the following relational schema, write queries in SQL to answer the following queries Customer (cid, cname, address, city, state) Product (pid, pname, price, inventory) 2 Shipment(sid, cid, shipdate) Shipped Product(sid, pid, amount) i. Return the list of customers (no duplicates) that have never received a shipment. ii. Return product names and total amount shipped (price*amount) for products shipping over $1000. 4. employee (ID, person name, street, city) works (ID, company_name, salary) company (company name, city) manages ( ID, manager id) Consider the above relational database, where the primary keys are underlined. Give an expression in SQL for each of the following queries. i. Find the ID, name, and city of residence of each employee who works for First Bank Corporation. ii. Find the ID, name, and city of residence of each employee who works for First Bank Corporation and earns more than $10000. iii. Find the ID of each employee who does not work for First Bank Corporation. 5. Consider the following relation- Employee (Employee-Name, Company-Name, Salary) Write SQL for the following: i. Find the total salary of each company. ii. Find the employee name who is getting lowest salary. 6. Considering the relational schema given below. Write the following queries in relational algebra: Loan(loan_number, branch_name, amount) Borrower(customer_name, loan_number) Account(account_number, branch_name, balance) i. Find the names of all customers who have a loan at the Bashundhora. ii. Find the largest account balance in the bank. iii. Find the names of all customers who have either an account or a loan or both. 7. branch(branch name, branch city, assets) customer (ID, customer name, customer street, customer city) loan (loan number, branch name, amount) borrower (ID, loan number) account (account number, branch name, balance ) depositor (ID, account number) Consider the bank database above, where the primary keys are underlined. Construct the following SQL queries for this relational database. 3 i. Find each customer who has an account at every branch located in Brooklyn. ii. Find the total sum of all loan amounts in the bank. iii. Find the names of all branches that have assets greater than those of at least one branch located in Brooklyn. 8. What is an E-R model? What are the various symbols used to draw an E-R diagram? Explain with the help of an example how weak entity sets are represented in an E-R diagram. 9. A car-rental company maintains a vehicle database for all vehicles in its current fleet. For all vehicles, it includes the vehicle identification number, license number, manufacturer, model, date of purchase, and color. Special data are included for certain types of vehicles: Trucks: cargo capacity Sports cars: horsepower, renter age requirement Vans: number of passengers Off-road vehicles: ground clearance, drivetrain (four- or two-wheel drive) Construct an ER diagram for the car rental company database. 10. Design a database for an automobile company to provide to its dealers to assist them in maintaining customer records and dealer inventory and to assist sales staff in ordering cars. Each vehicle is identified by a vehicle identification number (VIN). Each individual vehicle is a particular model of a particular brand offered by the company (e.g., the XF is a model of the car brand Jaguar of Tata Motors). Each model can be offered with a variety of options, but an individual car may have only some (or none) of the available options. The database needs to store information about models, brands, and options, as well as information about individual dealers, customers, and cars. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints.