I've been on this assignment for school for a couple weeks now (yeah I'm a bit slow).
This is my last query that I cannot get to work (No rows selected is what it gives me)
/*8. Provide a distinct alphabetic list of students (first and last name and phone number) from Flushing, NY who enrolled prior to 10:20am on February 2, 2007.
*/
SELECT s.first_name, s.last_name, s.phone
FROM student s, zipcode z
WHERE s.zip = z.zip AND z.city= 'FLUSHING' AND z.state = 'NY'
AND s.registration_date < '2007-02-02 10:20:00 YYYY-MM-DD HH:MI'
ORDER BY s.first_name, s.last_name DESC;
Any ideas what I've done wrong here? Thanks for your time and help everyone.
On Microsoft SQL I would change
to
AND s.registration_date<CAST('2007-02-02 10:20:00' AS DATETIME)
Thanks for the help. Sadly no cigar.
To make the query read a bit easier - change the join:
FROM student s
INNER JOIN zipcode z ON z.zip = s.zip
To eliminate data issues - start with this in the WHERE clause:
WHERE z.state = 'NY'
Include additional columns so you can see the results...
And finally - reread the requirements and adjust the above query to identify enrollment vs registration. Also - look at the specific data types for each column to verify you can answer the question that is being asked.
Finally - in Oracle you would use the TO_DATE function to convert a string to a date/time.