SQL Help

[Aylee]

Taking database this semester and the teacher is horrible. Was wondering if you guys could help me a bit with some problems im having on the second homework.

See below for questions

[octopus]

I don't think you listed all the tables. Neither table has city/state/zip so how would we join?

Edit:
(Note - I use Oracle SQL at work so there might be some differences in syntax)
#9
SELECT fName, lName, position
FROM Employee
JOIN Department USING (deptNo)
WHERE deptName like '%IT%'
ORDER BY lName

#10
SELECT deptName, fName, lName, position
FROM Employee
JOIN Department USING (deptNo)
WHERE fName = 'James' AND lName = 'Adam'
ORDER BY deptName ASC, lName DESC

[Aylee]

Like i said this teach is HORRIBLE! This is what he gave us. I will look in my text book when i get home and see if he excluded some tables that should have been provided.

This is the homework set.

Spoiler: show

[octopus]

Yeah from that page I can't see how you can figure out the city. Maybe the address contains the zip code and you can look up the city in another table?

[Aylee]

I don't think you listed all the tables. Neither table has city/state/zip so how would we join?

#10
SELECT deptName, fName, lName, position
FROM Employee
JOIN Department USING (deptNo)
WHERE fName = 'James' AND lName = 'Adam'
ORDER BY deptName ASC, lName DESC

This only shows James Adam because of the where statement. I need people manged by him. But I think i can adapat it to work. TYVM!

[octopus]

This only shows James Adam because of the where statement. I need people manged by him. But I think i can adapat it to work. TYVM!

Edit: nvm that probably won't work

I don't know if this is the solution but if it were me, I would do this:

SELECT deptName, fName||' '|| lName Name, position
FROM Employee NATURAL JOIN Department
WHERE mgrEmpNo IN (SELECT empNO FROM Employee WHERE fName = 'James' AND lName = 'Adam')
ORDER BY deptName ASC, lName DESC

[Aylee]

Edit: nvm that probably won't work

yeah Trying to think of a way to derive MGR number from his empNo but coming up blank lol. TY for your help though. gonna keep working on it for the next 3 days. like 4 others from 1-7 anyway lol

[octopus]

Try my edit?

[Aylee]

Try my edit?

Worked perfect tyvm! Also for number 7 its all listed under address

Spoiler: show

So how do i go about splicing it and parsing all the data out of it or w/e?


If you don't mind can you check my answers for 1-7 also? I need a perfect score on this homework.

Databases:

Branch (bNo, Street, zipCode)
Client (cNo, fName, lName, phone, preftype, maxrent)
Department (deptNo, deptName, mgrempno)
Employee (empNo, lName, fName, sex, dob, address, deptno, position)
owner (Ono, fname, lname, street, zipcode, phone)
property ( pno, street, zipcode, type, room, rent, ono, sno, bno)
staff (sno, fname, lname, position, sex, dob, salary, bno)
viewing (cno, pno, viewdate, cmmt)
zip (city, stae, zipcode)


1)

Spoiler: show

2)

Spoiler: show

3)
I know there is a better way to do this one but best i could think of.

Spoiler: show

4)
Haven't finished yet


5)
haven't finished yet


6)
haven't finished yet


7)
completely lost


8)

Spoiler: show

9)

Spoiler: show

10)

Spoiler: show

[octopus]

For #7 do you have some kind of a function or table to look up city from zip code?

It looks like you're using SQL Developer so are you connected to Oracle?

[Aylee]

For #7 do you have some kind of a function or table to look up city from zip code?

It looks like you're using SQL Developer so are you connected to Oracle?

not to my knowledge

yes its SQL developer via oracle

[octopus]

Anyway, I feel like #7 is a harder question than the rest, so I think I'm missing something. The address field is split up by a comma, so you have to separate the street address and state/zip.

select substr(address,1,instr(address,',')-1) street, substr(address, instr(address,',')+2) statezip from employee

And after that you have to get the city somehow from the zip, and reform the address.

[octopus]

Databases:
Staff (sNo, fName, lName, position, sex, dob, salary, bno)
client (cno, fname, lname, phone, preftype, maxrent)
owner (ono, fname, lname, street, zipcode, phone)
zip (city, state, zipcode)
branch (bno, street, zipcode)
property (pno, street, zipcode, type, room, rent, ono, sno, bno)

Uh you do have a zip table lol

[Aylee]

Uh you do have a zip table lol

The zip table is for 1-6 though :/

[octopus]

If you can't use that table I don't see how you can solve the problem.

[Aylee]

If you can't use that table I don't see how you can solve the problem.

Alright i'll email some class mates and the professor and see what i come up with

any suggestions on 1-6?

[Corrderio]

Short in the dark, but any way you can export the data?

[Aylee]

Short in the dark, but any way you can export the data?

I think it's possible but no idea how tbh. This is one of those classes where you basically teach yourself. This is easly considered the hardest class in my course list. Already taken and failed once because this teacher sux

[Corrderio]

I'm not sure what program you're using, but there has to be a way to export the database as an .sql file.

[Aylee]

I'm not sure what program you're using, but there has to be a way to export the database as an .sql file.

I am using oracles sol developer