Databases | SQL | Oracle | MS Access Answers

Which of the query below list the most expensive service received by each patient?

A. SELECT service, max(cost)
FROM services
GROUP BY service

B. SELECT patientID, max(cost)
FROM services
GROUP by service

C. SELECT patientID, service
FROM services
where cost =
(SELECT max(s.cost) as mc
FROM services s WHERE s.patientID = services.patientID)

D. SELECT patientID, service
FROM services
GROUP BY patientID
HAVING cost = mac(cost)

What does this query mean?

SELECT admissionID, sum(cost)
FROM services
WHERE admissionID > 100 and cost > 50
GROUP BY admissionID
HAVING sum(cost) > 200

A. Admissions with ID greater than 100, cost greater than 50 per service, and total cost per admission greater than 200

B. First 100 admissions with cost greater than 50 and total cost per patient greater than 200

C. Admissions with ID greater than 100, cost greater than 50 per admission, and total cost per patient greater than 200

D. Admissions with ID greater than 100, and total cost higher than 200 for services with individual costs greater than 50

Assign your last name as the value of the text variable in the executable section of the block. Include statements in the block to display each variable's value onscreen.

1: Produce an unduplicated list of all product IDs for all Products that have been sold. Sort the List.
2: Show the basket ID, Product ID, Product name, and description for all items. (Do it two ways-one with an ANSI join and one with a traditional Join.)
3: Modify all queries in step 2 to include the customer's last name.
4: Display all orders (basket ID, Shopper ID, and date ordered) placed in February 2012. The date should be displayed in this format: February 12, 2012.
5: Display the total quantity sold by the product ID.
6: Modify the query in step 5 to show only products that have sold less than a quantity of 3.
7: List all active coffee products (product ID, name, and price) for all coffee items priced above the overall average of coffee items.
8: Create a table named CONTACTS that includes the following columns.

Use SQL commands to create the tables and attributes. Make sure that you assign the appropriate datatypes such as NUM, CHAR, etc.

After identifying the entities, attributes, and relationships among the entities of the application, you
need to start with the design of the database. For this, you need to perform the following tasks:
1. Draw an E/R diagram to demonstrate the relationships between the various entities of the
application. [8 Marks]
2. Map the E/R diagram to tables that you need create in order to accomplish the requirements of
the application. [3 Marks]
3. Identify the attributes that can be used as primary keys for the corresponding tables in the
relational database design. [2 Marks]
4. Identify the various schemas that need to be created while designing the database. [2 Marks]

. An insurance database has the tables given below. The data types are also specfified.

Table name : owner_details
Column name Format
Owner_id Char(4)
Name Varchar(25)
Address Varchar(40)

Table name : car
Column name Format
Reg_no Char(10)
Model Varchar(15)
Year Number(4)

Table name : accident
Column name Format
Report_no Number(5)
Acc_date Date
Location Varchar(20)

Table name : cars_owners
Column name Format
Owner_id Char(4)
Reg_no Char(10)

Table name : cars_in_accident
Column name Format
Owner_id Char(4)
Reg_no Char(10)
Report_no Number(5)
Cost_of_damage Number(5)


a) Create the above table with proper primary and foreign keys.
b) Enter at least 5 tuples for each relation
c) Update the cost of damage for a particular car involved in an accident with report
number 25 to 30000.
d) Add a new car to the database
e) Add a new accident to the database
f) List the car owners whose cars were involved in accidents in the year 2006
g) Find the number of

A software company has the following information about its clients and the projects given by them:

Clients (client number, client name, address)
Projects(project number, project name, total investment, no. of people working on the project)
Client project(client number, project number, date of start of project)

Create a database with the tables given above. Give suitable field names, keys and validation checks. Create data entry forms and enter at least 10 rows.

Run the following queries on the above database:
a) List clients who have assigned more than 2 projects to the software company.
b) List clients in order of decreasing total project investment.
c) Show the details of the project with the maximum number of people working on the it.

An SQL query to calculate the average amount of invoices for each customer, sorted in ascending order.

For the follow tables:
create table customer(
cid integer, --- customer ID
cname varchar(50), --- customer name
primary key (cid));

create table product(
pid integer, --- product ID
pname varchar(50), ---- Product name
price number, --- price of product
primary key (pid));


create table orders(
oid integer, --- order ID
cid integer,--- customer ID
primary key (oid),
foreign key (cid) references customer(cid));

create table order_line(
oid integer, --- order ID
pid integer, --- product ID
quantity integer, --- quantity of product in the order
primary key (oid, pid),
foreign key (oid) references orders(oid),
foreign key (pid) references product(pid));


Write a PL/SQL procedure to print out the names of customers who have bought a certain product. The product name is the input parameter