MYSQL Query question - GoFuckYourself.com

QualityMpegs · 03-06-2005, 09:42 PM

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.

European Lee · 03-06-2005, 09:44 PM

Quote:

Originally Posted by QualityMpegs

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

Have someone write you a script to do it

Regards,

Lee

QualityMpegs · 03-06-2005, 09:47 PM

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.

QualityMpegs · 03-06-2005, 09:56 PM

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

rickholio · 03-06-2005, 10:14 PM

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.

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.

willow · 03-06-2005, 10:16 PM

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?

QualityMpegs · 03-06-2005, 10:51 PM

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.

broke · 03-06-2005, 11:14 PM

Quote:

Originally Posted by QualityMpegs

As the root user execute the following queries:

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

Varius · 03-06-2005, 11:17 PM

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...

Varius · 03-06-2005, 11:18 PM

Quote:

Originally Posted by broke

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

Uhm, yes it does:

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

broke · 03-06-2005, 11:20 PM

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

Let me look over the post again.

mrthumbs · 03-06-2005, 11:22 PM

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.

QualityMpegs · 03-07-2005, 05:53 AM

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

DateDoc · 03-07-2005, 05:58 AM

Quote:

Originally Posted by QualityMpegs

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

Yup, you are screwed and getting an "F"

ytcracker · 03-07-2005, 06:04 AM

school is gay

QualityMpegs · 03-07-2005, 06:05 AM

Quote:

Originally Posted by ytcracker

school is gay

you better believe it

blazin · 03-07-2005, 06:23 AM

lol, do your own homework lazy

QualityMpegs · 03-07-2005, 06:30 AM

Quote:

Originally Posted by blazin

lol, do your own homework lazy

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.

03-06-2005, 09:42 PM	#1
QualityMpegs Confirmed User Join Date: Feb 2003 Location: Orlando, Florida Posts: 3,521	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.

03-06-2005, 10:14 PM	#5
rickholio Confirmed User Industry Role: Join Date: Jan 2004 Location: Nor'easterland Posts: 1,914	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. 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. Last edited by rickholio; 03-06-2005 at 10:15 PM..

03-06-2005, 10:51 PM	#7
QualityMpegs Confirmed User Join Date: Feb 2003 Location: Orlando, Florida Posts: 3,521	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. Last edited by QualityMpegs; 03-06-2005 at 10:52 PM..

03-06-2005, 11:17 PM	#9
Varius Confirmed User Industry Role: Join Date: Jun 2004 Location: New York, NY Posts: 6,890	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... __________________ Skype variuscr - Email varius AT gmail

03-07-2005, 06:04 AM	#15
ytcracker stc is the greatest Join Date: Dec 2002 Location: rip sean murray Posts: 12,403	school is gay __________________ www.ytcracker.com \| www.digitalgangster.com i love you

03-06-2005, 09:47 PM	#3
QualityMpegs Confirmed User Join Date: Feb 2003 Location: Orlando, Florida Posts: 3,521	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.

03-06-2005, 09:56 PM	#4
QualityMpegs Confirmed User Join Date: Feb 2003 Location: Orlando, Florida Posts: 3,521	I would be pleased if you guys could just solve one or give me some tips to get me started. Does it involve joins?

03-06-2005, 10:16 PM	#6
willow Confirmed User Join Date: Dec 2001 Location: NY, NY, USA Posts: 131	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?

03-06-2005, 11:20 PM	#11
broke Confirmed User Join Date: Aug 2003 Location: Someplace Windy Posts: 4,501	I'm an idiot... read more post less. Let me look over the post again.

03-06-2005, 11:22 PM	#12
mrthumbs salad tossing sig guy Join Date: Apr 2002 Location: mrthumbs*gmail.com Posts: 11,702	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.

03-07-2005, 05:53 AM	#13
QualityMpegs Confirmed User Join Date: Feb 2003 Location: Orlando, Florida Posts: 3,521	hah...this is reassuring...so is everyone basically saying my homework is impossible?

03-07-2005, 06:23 AM	#17
blazin Confirmed User Join Date: Aug 2002 Posts: 2,781	lol, do your own homework lazy __________________ I don't endorse a god damn thing......