SQL Help

**Aylee** · 2014-02-07 13:03

Originally Posted by octopus

dunno, but if you take that out the query should work (with the results out of order lol)

I did, added it to the end and it fixed it and made it in order. Thanks a ton. Gonna start working on Number 5 when i get back from training session with dog.

**Aylee** · 2014-02-07 18:15

Ok working on Number 4.

repost of question to make easier on people

Spoiler: show

select distinct count(*) num_properties, z.city, z.state
from property p, zip z
WHERE p.zipcode = z.zipcode
GROUP BY p.zipcode, z.city, z.state
ORDER BY num_properties
desc

Is this correct or is there a flaw in my logic? Also how do i make it stop showing results after row 15? Actually the question confuses the fuck out of me... did i anwser correctly even?

Does this count the owners in each city?

SELECT COUNT (DISTINCT oNo) AS Num_Owners, z.city, z.state
FROM property p, zip z
WHERE p.zipcode = z.zipcode
GROUP BY z.city, z.state
ORDER BY Num_Owners
DESC

**Aylee** · 2014-02-09 20:14

Edit:

Figured it all out! thx for the help!

**Aylee** · 2014-03-09 19:49

Ok new question. For Homework 3 we have to create our own tables and populate them with raw data that he gave us. Whats the best way to do it? Its a massive amount of information and I know there are short cuts but unsure of how exactly the quickest and easiest way to do it is

**Uzor** · 2014-03-09 20:02

With pure SQL code, or are you allowed to use a database manager like Microsoft Access? That would surely be the easiest way and fastest way.

**Aylee** · 2014-03-09 20:10

Originally Posted by Uzor

With pure SQL code, or are you allowed to use a database manager like Microsoft Access? That would surely be the easiest way and fastest way.

I can use what ever i assume. He provided all the data we need in a word document. I've been trying to import it with by copy pasting it into a excel document, saving it and hitting import. I can go to the import wizard and bring it up and it shows the information in the excel document laid out the way i want. Hit next and it ask for table name and wont let me progress. If i leave it blank it gives me a error saying "table name is not a valid oracle table name" if i type anything and it next it just sits there.

EDIT:

kind of getting it to work but keep getting a error saying

"
Insert failed for rows 1 through 6
ORA-02291: INTERGITY CONSTRAIT)
VOILATED - PARENT KEY NOT FOUND

**Uzor** · 2014-03-09 20:13

What's the data you need to insert? The easiest by far would probably be to use Microsoft Access or something similar. I know you can use relationships in Excel too, but it's a whole lot easier to manage primary keys, foreign keys and alternate keys in Access than it is in Excel. You can do it using DDL, but if you're allowed to use Microsoft Access that would be your best bet.

**Aylee** · 2014-03-09 20:20

Originally Posted by Uzor

What's the data you need to insert? The easiest by far would probably be to use Microsoft Access or something similar. I know you can use relationships in Excel too, but it's a whole lot easier to manage primary keys, foreign keys and alternate keys in Access than it is in Excel. You can do it using DDL, but if you're allowed to use Microsoft Access that would be your best bet.

Any advice on how to do it with access? I havent used access since high school lol so basically have forgotten everything i know about it. Excel way is working kinda... keeps error saying i voialte my parent key...

**Uzor** · 2014-03-09 20:24

It depends on the data.

Do you already have it written out in a UML datasheet, as a logical notation model, or as a database model? It all depends on the way the data he gave you is presented.

**Aylee** · 2014-03-09 20:28

Originally Posted by Uzor

It depends on the data.

Do you already have it written out in a UML datasheet, as a logical notation model, or as a database model? It all depends on the way the data he gave you is presented.

All the data is laid out in a ms word document in a 4 tables (one for each database table)

**Uzor** · 2014-03-09 20:33

Ok, so it's presented as a database model then. How much data is it, exactly? I missed the part where you said it's a massive amount of data...

**Aylee** · 2014-03-09 20:35

Each table has 3-7 collums and 6-200 rows

**Uzor** · 2014-03-09 20:41

Well, it's hard to judge without seeing it, but is any of the data overlapping?

Based on the error you get in your edited post, it seems like you're using the wrong primary key(s), or copy/pasting the tables in the wrong order, which would suggest a lot or some of the data uses foreign keys (which is probably what he's trying to teach you I'm guessing)

**Aylee** · 2014-03-09 20:44

Originally Posted by Uzor

Well, it's hard to judge without seeing it, but is any of the data overlapping?

Based on the error you get in your edited post, it seems like you're using the wrong primary key(s), or copy/pasting the tables in the wrong order, which would suggest a lot or some of the data uses foreign keys (which is probably what he's trying to teach you I'm guessing)

I think i figured it out, I took the class last semester and remembered the way he laid it out is retarded. I have to create the tables, populate them, then add the constraints or it wont work lol. So droped them and tried to populate and worked fine

Ok i take that back i got 2/4 tables populated

**Uzor** · 2014-03-09 20:51

Well there you go, good you figured it out

Either way, you should consider exploring Microsoft Access or something similar if you're going to take the course, it's a really good program and gives you a much better overview of data, tables and relationships, and you can run SQL-queries from inside the program, too. Then again, our teachers might have recommended it for computer science since we get all 100+ microsoft products for free. Either way, I really like it, takes a little getting used to though

**Aylee** · 2014-03-09 21:02

Originally Posted by Uzor

Well there you go, good you figured it out

Either way, you should consider exploring Microsoft Access or something similar if you're going to take the course, it's a really good program and gives you a much better overview of data, tables and relationships, and you can run SQL-queries from inside the program, too. Then again, our teachers might have recommended it for computer science since we get all 100+ microsoft products for free. Either way, I really like it, takes a little getting used to though

I'll look into it thanks. Ok one error im getting is because my date is formatted wrong. In the input its formated like this "day-month-year" (15-jul-13) in the wizard its asking how i format it so how do i tell it?

nvm DD-MON-YY thx!

Ok new problem poped up lol. When i import this data. The last field is decimals (3.0, 3.5, ext) But when i try to import its saying

"Insert failed for rows 1 through 50
ora-00001: unique constraint voilated. If i ignore all errors it adds them but leaves out anything that isant a whole number (adds 3.0 but ignores 3.5 and doesnt add them)

**Uzor** · 2014-03-09 21:18

You already have that data somewhere else, and it's set to not allow duplicates. You either need to change it so that it's not set as a primary key, or if it's not a primary key, change it so that it allows duplicates (you might have it set to "index - no duplicates" rather than "index - allow duplicates", or "no")

**Aylee** · 2014-03-09 21:25

Originally Posted by Uzor

You already have that data somewhere else, and it's set to not allow duplicates. You either need to change it so that it's not set as a primary key, or if it's not a primary key, change it so that it allows duplicates (you might have it set to "index - no duplicates" rather than "index - allow duplicates", or "no")

I used:

create table worksOnBLF(
empNo number(6) not null,
projNo number(4) not null,
dateWorked date primary key,
hoursWorked number(3) not null);

Actually empNo + projNo + dateworked are suppose to be the primary key but i dont know how to code that :/ but at the moment date worked is the primary key

Raw Data for Works On

Spoiler: show

**Uzor** · 2014-03-09 21:36

That's why, because you only have date worked as primary key. Look at the first two:

585049 1336 22-Feb-2013 3.00
392899 1905 22-Feb-2013 8.00

Since you only have dateWorked as a primary key, it see that both of them have the same value, and will not allow you to enter them as it would create duplicate entries. If you use all three as a primary key (which you should; primary key is the least amount of information needed to extract a unique entry), it would look at:

585049 1336 22-Feb-2013 3.00
392899 1905 22-Feb-2013 8.0

and see that it's different entries, and will therefor allow you to input the data correctly.

**Aylee** · 2014-03-09 21:38

Originally Posted by Uzor

That's why, because you only have date worked as primary key. Look at the first two:

585049 1336 22-Feb-2013 3.00
392899 1905 22-Feb-2013 8.00

Since you only have dateWorked as a primary key, it see that both of them have the same value, and will not allow you to enter them as it would create duplicate entries. If you use all three as a primary key (which you should; primary key is the least amount of information needed to extract a unique entry), it would look at:

585049 1336 22-Feb-2013 3.00
392899 1905 22-Feb-2013 8.0

and see that it's different entries, and will therefor allow you to input the data correctly.

How do I code it to make all 3 the primary key?

create table worksOnBLF(
empNo number(6) PRIMARY KEY,
projNo number(4) PRIMARY KEY,
dateWorked date primary key,
hoursWorked number(3) not null);

fails me lol i suck horribly at database

also tried:

create table worksOnBLF(
empNo number(6) NOT NULL,
projNo number(4) NOT NULL,
dateWorked date,
hoursWorked number(3) not null,
primary key (empNo, projNo, dateWorked));

and it failed

Thread: SQL Help

Thread Tools

Similar Threads

SQL Database - Help studying for Test

SQL Help

SQL help

SQL help

Latest Threads

Up & Coming Threads

Hottest Threads