SQL questions Homework Sample

The true and false questions below require an explanation in addition to whether the answer is true or false. There are SQL statements with either syntax errors or logical mistakes, correct so that they do what is intended. What are the relations between the entities in the examples below, and what cardinality. For more SQL programming assignment help contact us for details.

Solution:

Ques #1.
Circle or highlight the appropriate answer. GIVE REASONS or EXPLANATIONS for correct answers and if the answer is false explain why and then indicate the correct answer: It is NOT a simple T/F question each question requires T/F explanation Do not run them in ORACLE.
(12 points)

T F 1. Given: STUDENT (sid, name, address, age, GPA, salary)

Following SQL statement is syntactically correct and will provide student ID and the average age of each student.

SELECT SID, avg(age)
FROM STUDENT;

The statement is syntactically incorrect because there is no grouping. Also, it makes no sense to get the average age of a student of a single database entry.

T F 2. an m:n relationship is defined as (Chen’s notation)

5 : 2
STUDENT <——> CLASS implies a student can take exactly 5 classes (it is like m:n, except m and n have values)

it implies a student can take exactly 2 classes and each class has exactly 5 studients

T F 3. An error : column ambiguously defined occurs when column name does not exist in the table. See example below:

SQL> select mem_name, mem_no, destination
from member m, itenerary i
where m.mem_no = i.mem_no; 2 3
select mem_name, mem_no,destination
*
ERROR at line 1:
ORA-00918: column ambiguously defined

The error column ambiguously defined occurs when there are multiple columns which have the same name in the inner query

T F 4.
Following error happens because Primary key is NOT defined properly

SQL> alter table sales_fact add primary key(product,quarter,state);
alter table sales_fact add primary key(product,quarter,state)
*
ERROR at line 1:
ORA-02260: table can have only one primary key

The error happens because Primary key was already defined in the first command and it can’t be defined twice or changed without removing the first primary key.

T F 5. Given the assumption that a VENDOR can supply many products and a PRODUCT can be supplied by only one vendor, an ERD would look like

PRODUCT <——————> VENDOR

It’s lacking the cardinality constraint
N 1
PRODUCT <——————> VENDOR

T F 6. Given: ENROLLMENT (SID, CID, GPA)

ENROLMENT will have two primary keys (SID) and (CID) since a student can take many classes and a class can be taken by many students

This is a many to many relationship between students and classes which is represented by a separate table
Ques#2:
(3 points)

Identify and EXPLAIN the errors (syntax or logical) in the following SQL codes and provide correct code:

GIVEN STUDENT (sname, age, classification) note: classification is student’s classification (i.e, undergraduate, graduate etc..)

Select sname
FROM STUDENT
Where lower(classification)=’Graduate’;

This is a logical error as the sql lower function converts all the characters in a string into lowercase. Which will never match ‘Graduate’ because the first letter is an uppercase letter.

Correct Code:

Select sname
FROM STUDENT
Where lower(classification)=’graduate’;

Select age, classification, count(*)
From STUDENT
Group by age;

This is a syntax error “ORA-00979: not a GROUP BY expression” as classification is used in the selection and not provided in the group by

Correct Code:

Select age, classification, count(*)
From STUDENT
Group by age, classification;

This will put all student with the same age and classification in the same group and return the age, classification, and students count of each group.
Ques#3: Given the following relations, Answer the following:
(3 points)
PLANE

PILOT

⦁ Assume a PILOT can fly only one type of PLANE, i.e., PILOT Mary can only fly BOEING 777 but a PLANE type can be flown by different PILOTs

⦁ What ‘s the nature of relationship (1:1 or 1:m or m:n) between PILOT and PLANE, Give reasons

The relationship is m:1 as a pilot can fly 1 plane type but the plane type can be flown by any number of pilots

⦁ Assume a PILOT can fly at the most THREE different types of PLANE, i.e., PILOT Moore can only BOEING 777, BOEING 767 and BOEING 747 but a PLANE type can be flown by many PILOTs

What ‘s the nature of relationship (1:1 or 1:m or m:n) , Give reasons (use actual numbers where applicable)

The relationship is m:n but it has a min, max constraint for the pilot. As a pilot can fly at most 3 plane type but he doesn’t have to fly exactly three and plane type can be flown by any number of pilots

Ques#4. Given the following relations between AUTHOR and BOOK

AUTHOR (author_id, name, specialty, phone_numb)

author_id author_name specialty phone_number

123 Smith,R DBA 111-1111
124 Smith, L Programmer 223-6786
215 Patty Programmer 223-6786
218 Lauren DBA 223-9087
222 Smith, P DBA 211-9000
321 Sammi 120-6754

BOOK (BOOK_ISBN, Author_ID, Date_book_published)

BOOK_ISBN Author_ID Date_book_published

1230987 123 1/12/2018
45665478 218 1/12/2018
1230987 124 1/12/2018
1230987 215 1/12/2018
22245678 215 2/3/2016
221345890 321 3/4/2016

a. Give primary key(s) of each table and state any assumptions made
(2 points)

AUTHOR
The primary key is: author_id
Assuming it’s unique through the entire table

BOOK
The primary key is (BOOK_ISBN, Author_ID)
Assuming BOOK_ISBN is unique per book and each book can be written by multiple authors

B what is the nature of association (1:1, 1:m or m:n) between the entities AUTHOR and BOOK? (Give reasons)
(2 points)
The relationship between Book and Author is m:n as in the table book the BOOK_ISBN 1230987 was repeated which means it was written by more than 1 author and the author_id 215 was repeated which means he wrote more than 1 book.
Q#5. BeachrUS.com is trying to create a database and check their annual sales. They have hired you to study their system. Following question refers to BeachrUS.com.

They have provided you with sample data for three tables (PROPERTY,RENTER,LEASE) for the 2012-2017(partial) fiscal years (see Appendix A).

PROPERTY (P_NO, P_address, type, rent, prop_owner_No)

P_No is the property ID
P_address is property address
Type is property type, i.e, house,flat,townhouse (currently only three types are allowed)
Rent is the listed rent for that property
Prop_owner_no is the ID of property owner

RENTER (renter_no, renter_name, renter_address, preference_type, max_affordable rent, title)

Renter_no is the ID of renter
Renter_name is renter’s name
Renter_address is the address of renter
preference_type type of property renter likes to rent
max_affordable_rent is the maximum amount this renter can afford for each lease
Title is their title (Mr, Ms or Mrs)

LEASE (L_NO,P_No,Renter_no,rent_paid,payment,start_date,finish_date)

L_NO is the lease ID
P_NO is the property ID
Renter_no is the ID of renter
Rent_paid is the actual rent paid for that property
Payment is the type of payment (only cask, Chk, VISA or MC are allowed)
Start_date is the date rental started
Finish_date is the date rental finished

Create table user_tablename as select attributes from tablename;
(see page 288)

Or you can create your own tables rent, lease and property with data provided

Part A:

Ques a
Provide the PK and FK of each of the three tables (add more column if needed)
(4 points)

PROPERTY RENTER LEASE
PK P_NO Renter_no L_NO
FK P_NO
FK Renter_no
FK
FK

Ques b

Give the SQL statements for creating tables IN ORACLE for property AND Lease TABLES ONLY. Assume renter table is already created.
(4 points)

create table PROPERTY (P_NO char(10) primary key, P_address char(200), Type char(9), Rent number, Prop_owner_no char(10));

create table LEASE (L_NO char(10) primary key, P_NO char(10), Renter_no char(10), Rent_paid number, Payment char(4), Start_date date, Finish_date date,
constraint FK_Table_1 foreign key(P_NO) REFERENCES PROPERTY(P_NO),
constraint FK_Table_2 foreign key(Renter_no) REFERENCES RENTER(Renter_no));

Q1 – Q15 (except Q1) must be answered in SINGLE SQL statement.

For all of the following questions construct the query in SQL, run them in ORACLE.

Include the SQL and the resulting output from ORACLE for each part listed below.

If you are not able to create or run the query write the query in sql on paper (without oracle output for some partial credit)

(30 points)

Part B:

⦁ Give the structure of RENTER, PROPERTY and LEASE tables in separate SQL query

⦁ Give the renter names who rented properties.

⦁ Give the property no of property that charge more than $600 in rent.

⦁ How many times each renter (give the renter’s no) has leased properties?

⦁ How many townhouses are in the market? Give their count.

⦁ Give the renter_no of renters who have rented more than once.

⦁ What is the least and most expensive rent of a given property type? Note there are three property types(flat, house, townhouse)

⦁ Give the names of renters who have at least 2 t’s t in their name

⦁ Give the name and total amount spent by each renter (only include renters that have rented).

⦁ Give the renter numbers, payment type, property number, start and end rental date of renters that did not pay by VISA (do not include renters that have not yet paid)

⦁ Give the average of profit for each property type. Only include properties that had profits.

Hint: profit will be (rent_paid – rent)

Your output should look like:

P_NO Average profit
—- ——————-
PD12 180
PD14 100
PG21 93.3333333

Q 12. Give the total rent paid for each property.

Q 13. Ms Keeelly wants to get the names of renters who paid more for a rental than they could afford. Write the query and give its output.

Q 14. Beah rUS is contacted by EEOC about its renting practices. BeachRUs needs to provide a count of female renters they had in the past.

Q 15. Give a count of renter whose names start with an M

Q 16 through Q 18 may require more than one SQL statements. Just write SQL statements, you will not be able to actually execute them since you only have read permission.

( 6 Points)

16. a new attribute property size is to be added to the database. Add it to appropriate table. Note for some properties property_size may not be available. Write statements in SQL to add this field.

17.BeachrUS.com has just hired Ms Keeelly as sales agent, They assigned her an oracle account ORDB1555
Mr. Lio (ORDB200) created all three tables in his account, he wants to allow Ms Kelley read access to
LEASE and RENTER tables. Assuming you are Mr. Lio, allow Ms Kelley this access.

Grant ALL PRIVILEGES on LEASE to ORDB1555;
Grant ALL PRIVILEGES on RENTER to ORDB1555;

18. Mr. Pally Neman, a new renter, (renter_no CR15) wants to lease property PL21 from June 2, 2010 to June 12, 2010, the negotiated rent is $800; assuming you are Ms Keeelly, please make this reservation (LEASE, lease no 10030) for him. Do not enter Pally Newman in renter table. If you run the query, you will get an error. Explain why this may happen.

It has given an error because the Renter_NO is a forign key in the LEASE table which points to the Renter_NO in the RENTER table, but Mr. Pally Neman (renter_no CR15) doesn’t exit in the RENTER table. So, we can’t add a LEASE pointing to him before adding him to the RENTER table

Part C:

Draw ERD for BeachrUS.com and justify relationships

(4 points)

Property to Lease is 1:m as a Lease involves 1 property, but a property can be leased multiple times.
Renter to Lease is 1:n as a Lease involves only 1 renter, but a renter can lease multiple properties.
==================
BONUS +1

What would be PK of LEASE if L_NO is removed. Try to identify from the attribute already in the table. Do not add any new attributes. List all assumptions.
Yes, L_NO can be removed. We can use the (Start_date, P_NO, Render_NO) as a primary key.

BONUS +2

Give the renter number of renter who paid the lowest rent/day?
(hint: number of days can be calculated by subtracting dates)

APPENDIX A: Sample DATA for BEACHrUS.com

PROPERTY

Property (P_NO, P_ADDRESS, TYPE , RENT, PROP_OWNER_NO)

P_NO P_ADDRESS TYPE RENT ($) PROP_OWNER_NO
PA14 16 Hohlead St House 650 CO46
PL94 6 Argyll St flat 400 CO87
PG4 6 Lawrence St flat 350 CO40
PG36 2 Manor Rd flat 375 CO93
PG21 18 dale Rd House 600 CO87
PG16 5 Novar Dr. Townhouse 450 CO93
PL21 22 Charles St TownHouse 800 CO93
PA90 45 Oliver St. House 700 CO12
PD12 331 TollyGate TownHouse 320 CO46
PD14 333 TollyGate TownHouse 350 CO93
PF14 3 Charles Street flat 150 CO12
PA01 32 Barry Street House 800 CO46

RENTER
(RENTER_NO, RENTER_NAME, RENTER_ADDRESS, PREFERNCE_TYPE , MAX_AFFORDABLE_RENT, TITLE)

Renter_NO Renter_name Renter_address Prefernce_type Max_Affordable_rent Title
CR76 John Kay 56 High st. flat 425 Mr
CR56 Aline Stewart 64 fern St. flat 350 Mrs
CR74 Mike Ritchie 18 tain St townhouse 750 Mr
CR62 Mary Treager 5 Tarbor St. house 600 Ms
CR11 Chenny House 12 calvert St. townhouse 400 Ms
CR45 Rathor Bin 33 Charles Street TownHouse 500 Mr
CR55 WeareWe 24 Charles Street House 1500 Mr
CR66 Gooles 59 Merritt Ave House 800 Ms
CR96 Ellaert 159 Merritt Ave House 800 Mr
CR12 Chow Lee 1420 Charles St Townhouse 300 Mrs

LEASE

LEASE (L_NO, P_NO,RENTER_NO, RENT_PAID, PAYMENT, START_DATE, FINISH_DATE)

L_No P_No Renter_no Rent_paid Payment Start_date Finish_date

10024 PA14 CR62 650 VISA 01-JUN-12 01-NOV-12
10075 PL94 CR76 400 CASH 01-JAN-12 01-AUG-12
10012 PG21 CR74 700 CHK 01-JUN-12 30-JUN-12
10022 PG21 CR62 680 MC 01-OCT-12 30-OCT-12
10023 PG4 CR76 350 MC 01-SEP-12 01-OCT-12
10028 PA14 CR62 450 CHK 01-JAN-12 01-JUL-12
10029 PG21 CR12 700 01-NOV-12 01-DEC-12
10030 PD12 CR96 450 VISA 01-JAN-13 01-FEB-13
10032 PD12 CR96 550 VISA 01-OCT-13 05-OCT-13
10033 PD14 CR12 450 MC 01-Jan-2017 05-Jan-2017
10038 PA14 CR12 550 MC 20-Jan-2017 25-Jan-2017