CS 3743 University of Texas at San Antonio SQL Database Computer Coding Task

User Generated

Oevna1030_

Computer Science

CS 3743

University of Texas at San Antonio

CS

Description

Unformatted Attachment Preview

CS 3743 HOMEWORK #6 SQL DUE: SEE BLACKBOARD Customer(custNr, name, baseLoc,birthDt, gender) Property (propId, locDesc, state, propType, ra ng) Rental(custNr, propId, startDt, totalCost) To make it easier for the grader, use the following tuple variables: Customer – c, Property – p, and Rental – r. There may be cases where you need mul ple tuple variables for the same rela on; please use something meaningful (e.g., r444). I am showing sample data for your bene t; however, you don't have to do anything with it. Property Table +----------+---------------------+-------+----------+------| propId | locDesc | state | propType | rating +----------+---------------------+-------+----------+------| BEANSBD | Friole Heaven | TX | T | 74 | FIREHHH | Forest View | CA | T | 61 | HOMEJJJ | Home Alone | CO | T | 73 | MTNDDD | Mountain View | CO | T | 79 | NYCCC | Times Square | NY | T | 96 | OILAAA | Oil Slick | TX | T | 68 | QUAKELLL | Rock N Roll | CA | M | 85 | RVBBB | Water Logged Camper | FL | M | 99 | SAEEE | River Shores | TX | T | 91 | SNOWFFF | Snowcap Ridge | CO | T | 100 | SNOWNNN | Snowy View | CO | T | 84 | STARIII | Star View | CO | C | 72 | TENTGGG | Kamp Komfort | CO | C | NULL | VACAYKKK | Griswold Paradise | CO | T | 67 | WESTMMM | Desert View | TX | C | 95 +----------+---------------------+-------+----------+------- Customer Table + | | | | | | | | | | | | | | | + | + + | | | | | | | | | | | | | | | | | | + | + ti ti fi ti +--------+---------------+---------+------------+------| custNr | name | baseLoc | birthDt | gender +--------+---------------+---------+------------+------| 111 | Ava Kashun | TX | 2000-04-01 | M | 222 | Cam Aruh | CA | 1990-09-11 | M | 333 | Pete Moss | FL | 1992-03-03 | M | 444 | Bill Board | CA | 1985-04-04 | M | 480 | Anita Vacay | TX | 1975-06-01 | F | 555 | Perry Scope | OK | 1927-04-15 | M | 601 | E Race | OK | 1986-10-01 | M | 666 | Tom E Gunn | NULL | NULL | M | 755 | Ray Mee | CA | 1965-04-01 | M | 777 | Bob Wire | OK | 1973-07-07 | M | 855 | T Doh | CA | 1975-04-15 | F | 888 | Penny Lane | CA | 1990-08-08 | F | 890 | Anita Break | CA | 1992-08-09 | F | 901 | Peg Board | NY | 1987-04-04 | F | 902 | Al B Tross | CA | 1957-07-12 | M | 903 | B B Gunn | NULL | NULL | F | 904 | Sally Mander | NY | NULL | F | 999 | Marcus Absent | NY | 1999-09-09 | M +--------+---------------+---------+------------+------- Rental Table: +--------+----------+------------+----------| custNr | propId | startDt | totalCost +--------+----------+------------+----------| 111 | RVBBB | 2019-09-09 | 60 | 111 | SAEEE | 2019-09-09 | 200 | 111 | STARIII | 2019-09-09 | 200 | 222 | NYCCC | 2019-11-11 | 200 | 222 | OILAAA | 2019-09-10 | 80 | 222 | RVBBB | 2019-09-09 | 60 | 222 | SAEEE | 2019-11-20 | 200 | 222 | VACAYKKK | 2019-11-11 | 50 | 333 | SAEEE | 2019-11-07 | 200 | 444 | MTNDDD | 2019-10-10 | 200 | 444 | QUAKELLL | 2019-11-15 | 200 | 444 | SAEEE | 2019-10-11 | 200 | 444 | SNOWNNN | 2019-11-14 | 200 | 444 | STARIII | 2019-10-13 | 800 | 444 | VACAYKKK | 2019-11-16 | 50 | 444 | WESTMMM | 2019-10-12 | 50 | 480 | OILAAA | 2019-11-11 | 80 | 480 | SAEEE | 2019-11-11 | 200 | 555 | BEANSBD | 2019-09-09 | 100 | 555 | MTNDDD | 2019-11-05 | 200 | 555 | NYCCC | 2019-10-01 | 200 | 555 | QUAKELLL | 2019-12-25 | 200 | 555 | RVBBB | 2019-12-04 | 200 | 555 | STARIII | 2019-12-04 | 600 | 555 | TENTGGG | 2019-10-01 | 80 | 555 | WESTMMM | 2019-11-11 | 200 | 666 | VACAYKKK | 2019-09-09 | 50 | 755 | RVBBB | 2019-11-01 | 60 + | | | | | | | | | | | | | | | | | | | | | | | | | | + | + | | | | | | | | | | | | | | | | | | | | | | | | | | | | + | + PROBLEMS START ON NEXT PAGE +--------+----------+------------+----------| custNr | propId | startDt | totalCost +--------+----------+------------+----------| 777 | SNOWFFF | 2019-11-02 | 200 | 777 | TENTGGG | 2019-11-03 | 50 | 777 | WESTMMM | 2019-11-04 | 80 | 855 | BEANSBD | 2019-11-01 | 60 | 855 | NYCCC | 2019-11-13 | 200 | 888 | HOMEJJJ | 2019-12-03 | 200 | 888 | RVBBB | 2019-12-01 | 200 | 888 | STARIII | 2019-12-02 | 200 | 890 | HOMEJJJ | 2019-11-13 | 200 | 890 | RVBBB | 2019-11-10 | 200 | 890 | STARIII | 2019-11-12 | 600 | 890 | WESTMMM | 2019-11-11 | 200 | 901 | MTNDDD | 2019-11-15 | 300 | 901 | QUAKELLL | 2019-11-17 | 200 | 901 | RVBBB | 2019-11-14 | 200 | 901 | SNOWNNN | 2019-11-16 | 200 | 902 | MTNDDD | 2019-11-18 | 600 | 903 | MTNDDD | 2019-11-19 | 600 | 904 | MTNDDD | 2019-11-29 | 600 | 904 | STARIII | 2019-11-22 | 400 | 999 | MTNDDD | 2019-11-24 | 800 | 999 | QUAKELLL | 2019-12-24 | 60 | 999 | RVBBB | 2019-11-23 | 200 | 999 | SNOWNNN | 2019-11-01 | 200 | 999 | STARIII | 2019-10-01 | 500 | 999 | WESTMMM | 2019-11-25 | 200 +--------+----------+------------+----------- Please show an appropriate SQL SELECT statement to answer each of the following. 1. (2.5 pts) Find the propId, locDesc, and state for proper es with a propType equal to "T" (i.e., tradi onal). 2. (2.5 pts) Find the propId and startDt for proper es which the customer named Ava Kashun has a rental. 3. (5 pts) Find the property ID and locDesc of proper es in TX (state) which have a start date a er "2019-11-03" for the customer named "Perry Scope". 4. (10 pts) Find the customer numbers and names of customers who have a rental start a er "2019-11-04" for a property which customer #444 also had a rental. Do not include #444 in your result. Hint: you will have to use one of the rela ons twice. 5. (10 pts) Find the customer number and names of customers who have rented all of the proper es having a propType of "C" (Camping). 6. (15 pts) Find the custNr, name, gender, and birth date of customers who only rent proper es with a state equal to "CO". State whether your answer includes customers that don't have any associated rentals. 7. (15 pts) Find the propId and locDesc for proper es on which only customer #777 has a rental. State whether your answer includes proper es that don't have any associated rentals. 8. (15 pts) Find the customer numbers and names of customers who have rented all the proper es which Penny Lane has rented. State whether you included Penny Lane in your answer. 9. (15 pts) Find the customer number, names, and birth date for customers who never rented a property with a state equal to "CO". State whether your answer includes customers that don't have any associated rentals. ti ti ti ti ti ft ti ft ti ti ti ti ti ti 10. (10 pts) Show the custNr and count of the number of proper es rented for each customer who rented at least 3 proper es.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.

SQL Assignment
Name Omitted
20th November 2021

1. Find the propId, locDesc, and state for properties with a propType equal to “T”
(i.e., traditional)
SELECT propId, locDesc, state FROM property WHERE propType='T';
Result set

2. Find the propId and startDt for properties which the customer named Ava Kashun
has a rental
SELECT p.propId, startDt FROM property p INNER JOIN rental r ON p.propId=r.propId
INNER JOIN customer c ON c.custNr= r.custNr WHERE c.name='Ava Kashun';
Result set

3. Find the property Id and locDesc of properties in TX (state) which have a start date
after “2019-11-03” for the customer named “Perry Scope”.
SELECT p.propId, locDesc FROM property p INNER JOIN rental r ON p.propId=r.propId
INNER JOIN cu...


Anonymous
Just what I needed…Fantastic!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags