Imagine you have the following tables: Person (PID, PName, PRanking, PAge) Car (

Question

Imagine you have the following tables: Person (PID, PName, PRanking, PAge) Car (CID, CName, CColor) Rental (PID, CID, Date)

Write SQL statements to:

Find all people who have rented the car with CID = 15.

Find people who have rented at least one car.

Find the average age of all people who are eligible to drive (age >= 16).

Find all people whose name begins with ‘s’, ends with ‘t’ and contains at least 5 chars.

Find all people who have rented either a green or a blue car. Find all people who haven’t rented a red car.

Find all people who are older than the person with PID = 3

Find the youngest person for each ranking level.

Find the youngest person for each ranking level that has at least 2 such people. For each car, find the number of reservations.

Write a stored procedure that resets the ranking of each person by the number of car rentals [Ranking 0 (#rentals <10), Ranking 1 (10 <= #rentals < 20)] and returns the new rankings.

12. Write a procedure to remove duplicates from a table containing a million rows using batching

13. Imagine a tree structure that has the following properties:

a) Single root node

b) Each node may have multiple children nodes

c) Nodes may not be deleted

d) All non-root nodes have a property max children nodes - new nodes are defaulted to a fixed default value

e) The max children nodes property may be changed at any time - if the new value is less than the current number of children nodes, the children are distributed up the chain respecting the property for all nodes along the way.

If the property is set to zero, the node is made a direct child of the root node

f) When adding a new node, it is added to a node as close to the root's level as possible ordered by percentage quota of children filled ascending. If all nodes have met their quota, it is placed directly under the root node.

Create tables and indexes to store this information and procedures to perform the following operations as efficiently as possible: i. Add a node to a tree ii. Change the max children node property of a node

Explanation / Answer

SQL statements :

Find all people who have rented the car with CID = 15.

select distinct A.PName from Persons A, Car B, Rental C where B.CID = C,CID and C.PID = A.PID and B.CID = 15;

---------

Find people who have rented at least one car.

everyone whose pid is in rental table would have renteed the car atleast once, so just match the two pid's.

select distinct A.PName from Person A, Rental B where A.PID = B.PID;

-------

Find the average age of all people who are eligible to drive (age >= 16).

select avg(PAge) from Person where PAge >= 16;

-------

Find all people whose name begins with ‘s’, ends with ‘t’ and contains at least 5 chars.

select PName from Person where PName LIKE 's_%_%_%t';

explaination - % operator would match atleast 1 character, while _ matches exactly 1 character, so it starts and ends with s and t while 3 _ indicate 3 characters ,so total character length becomes 5.

----------------

Find all people who have rented either a green or a blue car.

select distinct A.PName from Person A, Car B, Rental C where B.CID = C.CID and A.PID = B.PID and B.CColor='green' or B.CColor = 'blue';

----------

Find all people who are older than the person with PID = 3.

select distinct A.PName from Perosn A, Person B where A.PAge > B.PAge and B.PID=3;

----------

Find the youngest person for each ranking level.

select distinct PName, MIN(PAge) from Person GROUP BY PRanking;

explaination : group by would group all the persons with similar ranking together, and then min() function would display the min PAge amongst those person, i.e. the youngest of the lot.

--

do the next sql statement similarly.

------------------------------------------------------------------------------------------------------------------

for the final question, you would want to have a table with all the nodes, and then a nodeToChildren table that your node table will have a one to many relationship with. nodeToChildren will have the parentId, which is a FK back to the node table, and a childID. Nodes will have multiple children, so they'll have multiple entries in nodeToChildren, if that all makes sense. You could have maxChildren be a field in the node table.

To add a node your stored procedure will want to calc the best parent to insert a node under with a SELECT .. GROUP BY statement, then insert it into the node table. However, you will need an if statement to insert it to the root if your SELECT statement didn't return any results (quota for all is filled).

Changing the max children is trickier, but probably not that bad. Just have a while loop that queries, in order, all the parents of the nodes you need to move and their quota, and then making inserts and deletes to the nodeToChildren table to represent moving the nodes around the tree.

-------------

thank you

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.