Show the well id, depth, start date, and total days (use the alias total_days) f

Question

Show the well id, depth, start date, and total days (use the alias total_days) for all jobs that have been completed (i.e. have an end date). Sort the results by start_date in ascending order then total days in descending order. Note: to get total days you will need to use to_days to convert each date to days. For example, use to_days(date_column) to convert the column date_column to days. (SQL)

Below is a picture of my tables.

employee * employee_id first-name last_name email (0) gender (0) ob A a employee_id a wellid water_sampleA - startdate end-date (o) # sample-id - ph (0) temperature (0) E street (o) E city (o) state (o) s conductivity (o) dissolved_oxygen (o) E zipcode (o) hire-date (0) date time (0) well_id method-id employee_id well # wellid s depth (o) 9 street (o) city (o) - state (o) zipcode (o) method A methodid description -

Explanation / Answer

The SQL query for the given problem is given below.

Table job and well are joined on common attributes 'well_id'. TO_DAYS function is used to convert date to days and subtracting end date to start end to get the total number of days. In where clause condition is given to check the ends date which are not NULL, means job has been completed. Order by clause is used to sort the result.

Query-

SELECT j.well_id, w.depth, j.start_date, (TO_DAYES(j.end_date) - TO_DAYS(j.start_date)) AS 'Total_Days'
FROM job AS j
INNER JOIN well AS w
ON w.well_id = j.well_id
WHERE j.end_date IS NOT NULL
ORDER BY j.start_date ASC, Total_Days DESC ;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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