(Using sql developer )Query Formulation Part 1 Problems (25%) 1. List the city,
ID: 3747370 • Letter: #
Question
(Using sql developer )Query Formulation Part 1 Problems (25%)
1. List the city, state, and zip codes in the customer table. Your output should not have duplicates. (5 points)
2. List the name, department, phone number, and email address of employees with a phone number beginning with “5-”. (5 points)
3. List all columns of the resource table with a rate between 10 and 15 including the end points 10 and 15. Sort the result by rate. (5 points)
4. List the event requests with a status of “Approved” or “Denied” and an authorized date in July or August 2018. Include the event number, authorization date, and status in the output. In Oracle, you surround a date constant with single quotes. Use the default date format as shown in textbook Chapter 4. Text constants in Oracle are case sensitive. *** Change to updating values in one table without need to reference the other table. (5 points)
5. List the location number and name of locations that are part of the “Basketball arena”. Your query must not use the facility number (“F101”) of the basketball arena in the WHERE clause. Pretend that the user knows only the facility name, not the facility number. Thus, the WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). Text constants in Oracle are case sensitive. *** Change to delete location rows without need to reference Faciity table. *** (5 points)
Query Formulation Part 2 Problems (50%)
6. For each event plan, list the plan number, count of the event plan lines, and sum of the number of resources assigned. For example, plan number “P100” has 4 lines and 7 resources assigned. You only need to consider event plans that have at least one line. (10 points) 7. For event requests, list the event number, event date (eventrequest.dateheld), and count of the event plans. Only include event requests in the result if the event request has more than one related event plan with a work date in December 2018. (10 points)
8. List the plan number, event number, work date, and activity of event plans meeting the following two conditions: (1) the work date is in December 2018 and (2) the event is held in the “Basketball arena”. Your query must not use the facility number (“F101”) of the basketball arena in the WHERE clause. Pretend that the user knows only the facility name, not the facility number. Text constants in Oracle are case sensitive. Thus, the WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). (10 points)
9. List the event number, event date, status, and estimated cost of events where there is an event plan managed by Mary Manager and the event is held in the basketball arena in the period October 1 to December 31, 2018. Your query must not use the facility number (“F101”) of the “Basketball arena” or the employee number (“E101”) of “Mary Manager” in the WHERE clause. Pretend that the user knows only the facility name and employee name, not the facility number and employee number. Thus, the WHERE clause should not have conditions involving the facility number or employee number compared to constant values. Text constants in Oracle are case sensitive. (10 points)
10. List the plan number, line number, resource name, number of resources (eventplanline.numberfld), location name, time start, and time end where the event is held at the “Basketball arena”, the event plan has activity of activity of “Operation”, and the event plan has a work date in the period October 1 to December 31, 2018. Your query must not use the facility number (“F101”) of the “Basketball arena” in the WHERE clause. Thus, the WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). Text constants in Oracle are case sensitive. (10 points)
Database Modification Problems (25%)
Modify the contents of your database according to the following problems. For each problem, paste your SQL statement in the word processing document that you began with Part 1.
1. Insert a new row into the Facility table with facility name “Swimming Pool”. (5 points)
2. Insert a new row in the Location table related to the Facility row in modification problem 1. The new row should have “Gate” for the location name. (5 points)
3. Insert a new row in the Location table related to the Facility row in modification problem 1. The new row should have “Locker Room” for the location name. (5 points)
4. Change the location name for all locations of the swimming pool facility. The new location name should be “Door”. You should use the facility number of the swimming pool in your UPDATE statement. (5 points)
5. Delete the swimming pool facility and all related locations. When deleting the locations, you should use the facility number of the swimming pool in your DELETE statement. (Hint: when deleting a parent and related rows in a child table, the RESTRICT action (default in Oracle SQL) requires deletion of the child rows in a separate statement before deleting the related parent row in a separate DELETE statement.) (5 points)
Explanation / Answer
SQL Developer Query Formulations:(first Four Questions solved)
1. select distinct city,state,zipcode
from customers;-- First solution using the distinct keyword
select city,state,zipcode from (select city,state,zipcode,count(*)
from customers
group by city,state,zipcode);--If there is restriction to use distinct keyword, then we can use the above query with group by clause and subquery to fetch those records. Here count is an aggregate function that gives the count of the records and group by is used to group the count based on the fields. Finally we do not want the count part in our result so we will write another select statement to fetch data from current select statement as a table.
2. select name,department,phone_number,email from employee e
where e.phone_number like '5-%';
here we are making use of like operator to match any phone number that starts with '5-'.Like operator has two wild card that can be used with it.
(%)--This represents zero, one or multiple characters.
(_)-- This represents a single character.
3.select * from resources
where rate between 10 and 15
order by rate (ASC|DESC)
Here we are using * keyword to fetch all the records from resource table and between operator is used to fetch values between two vaues(values included).
Order By clause is used to sort the values and ASC is used with order by clause to sort in ascending order and DESC is used to sort in Descending Order.
By default if we do not specify anything then it will take ASC.
4 select event_number,authorization_date,status from event
where status In ('Approved','Denied')
AND TO_CHAR(authorizarion_date,'MON-YY') IN ('AUG-18','JUL-18');
Here we are using multiple where conditions so they are being joined by and clause.
In operator is being used to fetch records with status as Approved or Denied while other records are rejected.
We now use TO_CHAR funtion on the authorization date to convert into a format as MON-YY which we can use to compare with the values provided in the question as JUL-18 and AUG-18.
We again use in operator to club these values.
---------------------------------------------------------------------------------------------------------------------------
NOTE: Just answered the first four parts as the question had multiple subparts.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.