MYSQL Query question
 

Ok, for class I have to do some queries and I am totally lost here. Here's the tables:

Jobs: contains
jnum, jname, numworkers, city
I can only perform UPDATE on the "city" column on this table.

Parts: contains
pnum, pname, color, weight, city
I can only perform UPDATE on this table.

Shipments: contains
snum, pnum, jnum, quantity
I can only perform SELECT on this table.

Suppliers: contains
snum, sname, status, city
I can only perform INSERT on this table.

How would I go about doing these queries with those restrictions?

1-Insert into parts the row: P60, flange, silver, 8, Los Angeles.
2-Insert into shipments the row S5, P8, J5, 40.
3-Update the name of the part identified by key value P33 with the new name of gear.
4-Update the city value of the job identified by key value J3 with Dallas.
5-List all of the parts which are shipped in a quantity greater than 125.

Have someone write you a script to do it :thumbsup

Regards,

Lee

See, I'm kinda new to SQL, so I don't really know how to get around the restrictions of not being able to select, when they want you to select basically.

I would be pleased if you guys could just solve one or give me some tips to get me started. Does it involve joins?

Short answer: You can't.

If you only have SELECT on a table, you can't INSERT into it, no matter how much you may want to. If you have UPDATE, then the best you can do is kabosh another record, you can't insert new things.

You need more permissions, or create your own tables for your updates. :2 cents:

I'm also curious as to why you have all these restrictions if you're obviously supposed to be creating orders and doing other administrative functions.

These are pretty much nonsense. You can't insert a row into a table using only an update. You sure there isn't already some data in there, or something else you're missing in the assignment?

Hmmm...interesting...Here's the full assignment if you want to read it...maybe I passed over something very important? btw: The tables are already populated with data.

Objectives: To create, populate, and query a MySQL database.

Description: This assignment involves the creation, population, and querying a MySQL database. By now you should have downloaded and set-up MySQL on your system and be somewhat familiar with the MySQL server and MySQL environment. This assignment is important because it will create and populate the database that you will use in the next programming assignment when you will use JDBC to remotely connect to the database (back-end) using a Java application front-end. We will also use this same database in other future programming assignments utilizing servlets and JSPs (Java Server Pages). There is no programming per se in this assignment, but you will construct several different queries to pose to the database once it is created and populated.

Once you have created and populated the database, register a new user named client (you can decide whether or not to assign them a password) and assign to this user the following permissions:
Grant table level selection on shipments relation.
Grant table level insertion on suppliers relation.
Grant table level update on parts relation.
Grant column level update on attribute city in jobs relation.


As the root user execute the following queries:
List the snum and sname for every supplier who ships a part whose color is black to any job located in Orlando. (This is the SQL query from the mid-term exam.)
List the pnum and pname for all of the parts which are not shipped to any job.
List the jnum and jname for those jobs which receive shipments of parts from only supplier with snum = ?S1?.
List the snum, sname, and pnum for those suppliers who ship the same part to every job.
List the snum, and sname for those suppliers who ship both blue and red parts to some job.

As the client user execute the following statements:
Insert into parts the row: P60, flange, silver, 8, Los Angeles.
Insert into shipments the row S5, P8, J5, 40.
Update the name of the part identified by key value P33 with the new name of gear.
Update the city value of the job identified by key value J3 with Dallas.
List all of the parts which are shipped in a quantity greater than 125.

Input Specification: Create your database using the script named dbscript.sql which you can download from the assignments page.

Output Specification: Provide screen shots from the MySQL environment which clearly show the complete query expression and results for each of the queries listed above.

Didn't ask you to perform the actions with the new user you created.

:glugglug

Hrm, that looks screwed up.

If you only have UPDATE privilege, you cannot do any inserts. You could try REPLACE INTO which will look for a matching row (on primary key) and if none is found it will do an insert, but I'm not sure that will work anyways (because you still have no insert privilege).

However even worse than that, is this one:

Grant table level selection on shipments relation.
Insert into shipments the row S5, P8, J5, 40.

If you only have select privilege, then you can definately not insert data.

What MySQL version are you allowed to work with? Maybe you can do something with stored procedures, triggers, cascades, etc...

Uhm, yes it does:

"As the client user execute the following statements:" - right before it tells you the statements to execute :)

I'm an idiot... read more post less.

Let me look over the post again.

ive learned this stuff as well with the thing i do.. only difference is that i learned it because it was convenient and made me $$$$... school is such a waste of time.

hah...this is reassuring...so is everyone basically saying my homework is impossible? :)

Yup, you are screwed and getting an "F" :(

school is gay

you better believe it

lol, do your own homework lazy :winkwink:

yeah, umm...thanks, but i'm not asking you guys to do my homework. i would just like a hint on how to get started on these tricky problems.