Multiple SQL Questions

Database Questions Answering various questions from writing SQL queries, drawing Entity–relationship model (ERR) diagrams, schema design, multiple SQL diagram design patterns, answering various database questions in business English and create simple demonstration applications to showcase database knowledge. A strong knowledge in English and SQL is required. High quality independent work is required. 16 questions in various formats listed above. Some questions have multiple sections. Around 60 questions total. A full list of questions will be sent to the winning bid. Some sample questions: 1. Use Oracle Forms, or write a program (using PL/SQL and/or Java), to display the following report for a given patient (the user will only enter the patient number). Patient Number: _______________ Patient Name: ______________________ Care Centre Name: ___________________ Name of Nurse-in-Charge: ___________________ Treatment ID Treatment Name Physician ID Date ___________ ___________ ___________ ___________ . . . . . . . . . . . . 2. Consider the concurrent execution of the following transactions. List two problems that may occur from the concurrent execution of these two transactions. 3. Consider the following three relations: TRAVEL_AGENT (name, age, salary)

CUSTOMER (name, departure_city, destination, journey_class)

TRANSACTION (number, cust_name, travel_agent_name, amount_paid) Write SQL statements to answer the following questions. Compute the number of different customers who have a transaction. Display the name of the oldest travel agent. List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1 000. Display the names and ages of the travel agents who have arranged journeys for customer “John Smith”, in descending order of age (use a subquery). Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery). 4. Consider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-Insurance. Member(member_ID, Name, Address). On average, there are two members for each policy and item covered by the policy. InsuredItem(Item_ID, Description, Coverage_Type). There is an average of ten covered items per policy. CoopOffice(Office_ID, Address, Manager_name). Each policy is registered and managed by only one office of the cooperative. Policy(Policy_ID, Type). The company has approximately one million policies at the present time. Approximately five percent of these policies experience some change each month. Period(Date_Key, Fiscal_Period). The length of the fiscal period is one month. The decision system reports are supposed to be based on five years of data. Claim(Claim_ID, Claim_Description, Claim_Type). The facts to be recorded for each combination of these dimensions are: Policy_Premium, Deductible, and Monthly_Claim_Total. 1. Design a star schema for this problem. 2. Using the assumptions stated above, estimate the number of rows in the fact table. 3. Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per field.