User Panel
User Panel
User Panel
|
Posted: 11/29/2016 11:57:55 PM EDT
I am struggling with some homework and could use some help please.
|
|
|
|
Posted: 11/30/2016 12:00:11 AM EDT
[#1]
Yes, it's what I do for a living.
But if I do your homework you'll never learn. So I'll give you hints. So ask your questions. |
|
|
|
Posted: 11/30/2016 12:00:45 AM EDT
[#2]
Gotta post your question and maybe which database you are using. Theres a tech forum that this kinda stuff usually goes in but its cool here too.
|
|
|
|
Posted: 11/30/2016 12:01:49 AM EDT
[#3]
All I know about SQL is what I learned from xkcd.
|
|
|
|
Posted: 11/30/2016 12:03:42 AM EDT
[#4]
Sure. Which dbms?
|
|
|
|
Posted: 11/30/2016 12:04:49 AM EDT
[#5]
SELECT * from TABLE
all you need to know. 
|
|
|
|
Posted: 11/30/2016 12:16:53 AM EDT
[#7]
Quote HistoryQuoted: This isn't my major just a required IS database class so we are learning using Microsoft Access. I have to do a parameter query where I Report the least expensive copy of a book whose title is entered. I have a table for book and one for book_copy I believe I need to do a join for them and then do the parameter query. http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png This is what I have so far Select* from Book where Title = [Enter book title]; Well, for starters, you don't have to select *. You only need the title and the asking price, right? |
|
|
|
Posted: 11/30/2016 12:18:03 AM EDT
[#8]
Quote HistoryQuoted: Well, for starters, you don't have to select *. You only need the title and the asking price, right? Quote HistoryQuoted: Quoted: This isn't my major just a required IS database class so we are learning using Microsoft Access. I have to do a parameter query where I Report the least expensive copy of a book whose title is entered. I have a table for book and one for book_copy I believe I need to do a join for them and then do the parameter query. http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png This is what I have so far Select* from Book where Title = [Enter book title]; Well, for starters, you don't have to select *. You only need the title and the asking price, right? |
|
|
|
Posted: 11/30/2016 12:19:41 AM EDT
[#9]
Book has the title.
Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. |
|
|
|
Posted: 11/30/2016 12:20:04 AM EDT
[#10]
Quote HistoryQuoted:
This isn't my major just a required IS database class so we are learning using Microsoft Access. I have to do a parameter query where I Report the least expensive copy of a book whose title is entered. I have a table for book and one for book_copy I believe I need to do a join for them and then do the parameter query. <a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png</a> This is what I have so far Select* from Book where Title = [Enter book title]; You have a unique identifier. . . can't you just do a v-lookup and phone it in
|
|
|
|
Posted: 11/30/2016 12:21:04 AM EDT
[#11]
Quote HistoryQuoted: Yes. I need to display the minimum price as well. So would that be a min() type of statement? Quote HistoryQuoted: Quoted: Quoted: This isn't my major just a required IS database class so we are learning using Microsoft Access. I have to do a parameter query where I Report the least expensive copy of a book whose title is entered. I have a table for book and one for book_copy I believe I need to do a join for them and then do the parameter query. http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.11.45%20PM_zpskz1ls6zw.png This is what I have so far Select* from Book where Title = [Enter book title]; Well, for starters, you don't have to select *. You only need the title and the asking price, right? |
|
|
|
Posted: 11/30/2016 12:21:42 AM EDT
[#12]
|
|
|
|
Posted: 11/30/2016 12:23:57 AM EDT
[#13]
Router# configure terminal
or config t for short... Wait, Wrong system. LOL |
|
|
|
Posted: 11/30/2016 12:24:35 AM EDT
[#14]
|
|
|
|
Posted: 11/30/2016 12:24:43 AM EDT
[#15]
Quote HistoryQuoted: Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. |
|
|
|
Posted: 11/30/2016 12:25:12 AM EDT
[#16]
select price,Title from Book order by price asc limit 1;
|
|
|
|
Posted: 11/30/2016 12:25:17 AM EDT
[#17]
Quote HistoryQuoted: Yeah I got that. I keep getting an error about book.mdb not being able to be found Quote HistoryQuoted: Quoted: Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. |
|
|
|
Posted: 11/30/2016 12:26:06 AM EDT
[#18]
Quote HistoryQuoted: select price,title from table order by price asc limit 1; 2. I would fire you if I saw that. 3. You are wrong. |
|
|
|
Posted: 11/30/2016 12:26:51 AM EDT
[#19]
Quote HistoryQuoted:
1. It's better to answer someone's questions than just give them the answer..... 2. I would fire you if I saw that. 3. You are wrong. Quote HistoryQuoted:
Quoted:
select price,title from table order by price asc limit 1; 2. I would fire you if I saw that. 3. You are wrong. I wouldnt work for you with an attitude like that so we're ok. 
|
|
|
|
Posted: 11/30/2016 12:28:24 AM EDT
[#20]
Quote HistoryQuoted: Paste your join statement. Quote HistoryQuoted: Quoted: Quoted: Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN.  |
|
|
|
Posted: 11/30/2016 12:28:38 AM EDT
[#21]
Quote HistoryQuoted:
select price,Title from Book order by price asc limit 1; ^this |
|
|
|
Posted: 11/30/2016 12:30:11 AM EDT
[#22]
Quote HistoryQuote HistoryQuoted: Quoted: Quoted: Quoted: Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. SELECT `Book`.`Title`, `Book_Copy`.`AskingPrice` FROM `Book`, `Book_Copy` WHERE `Book`.`ISBN` = `Book_Copy`.`ISBN` AND `Book`.`Title` = 'The Title' ORDER BY `Book_Copy`.`AskingPrice` ASC LIMIT 1; |
|
|
|
Posted: 11/30/2016 12:30:30 AM EDT
[#23]
Quoted:
I am struggling with some homework and could use some help please. SELECT * FROM rtfm WHERE Help = 'RTFM' That should get you started |
|
|
|
Posted: 11/30/2016 12:32:11 AM EDT
[#24]
Quote HistoryQuoted:
Paste your join statement. Quote HistoryQuoted:
Quoted:
Quoted:
Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. This. Also Access has a particularly fucked variant of SQL, and I have a hard time remembering what does and doesn't work in it. IIRC, joins may require some careful parenthesis to work right. There's a query builder in it that can help you get it right. |
|
|
|
Posted: 11/30/2016 12:32:20 AM EDT
[#25]
Quote HistoryQuoted:
^this Quote HistoryQuoted:
Quoted:
select price,Title from Book order by price asc limit 1; ^this 
|
|
|
|
Posted: 11/30/2016 12:32:28 AM EDT
[#26]
Quote HistoryQuoted:
select price,Title from Book order by price asc limit 1; Didn't read the schema. Didn't read the question. Uses MySQL. Lemme guess, developer? |
|
|
|
Posted: 11/30/2016 12:32:35 AM EDT
[#27]
SELECT
b.Title, b.ISBN, MIN(c.AskingPrice) AskingPrice FROM Book b JOIN Book_Copy c ON b.ISBN = c.ISBN WHERE b.Title = [Enter book title] GROUP BY b.Title, b.ISBN; (eta. forgot the GROUP BY...) |
|
|
|
Posted: 11/30/2016 12:34:12 AM EDT
[#28]
Quote HistoryQuoted:
All I know about SQL is what I learned from xkcd. http://imgs.xkcd.com/comics/exploits_of_a_mom.png that's funny as fuck |
|
|
|
Posted: 11/30/2016 12:35:39 AM EDT
[#29]
Quote HistoryQuoted:
SELECT b.Title, b.ISBN, MIN(c.AskingPrice) AskingPrice FROM Book b JOIN Book_Copy c ON b.ISBN = c.ISBN WHERE b.Title = [Enter book title] GROUP BY b.Title, b.ISBN; (eta. forgot the GROUP BY...) No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly. |
|
|
|
Posted: 11/30/2016 12:36:24 AM EDT
[#30]
Quote HistoryQuote HistoryQuoted: Quoted: Quoted: Quoted: Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. also you are missing your join statement. For example: Select * from table1 as t1 (nolock) inner join table2 as t2 on t1.field1 = t2.field1 where etc etc You can also left join, right join and outer join. |
|
|
|
Posted: 11/30/2016 12:37:10 AM EDT
[#31]
Quote HistoryQuote HistoryQuoted:
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1; ^this http://i.imgur.com/bLutSUl.jpg ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need. |
|
|
|
Posted: 11/30/2016 12:37:52 AM EDT
[#32]
Quote HistoryQuoted:
I think that is mySQL, I am not familar with it and work with the enterprise edition, but I think it thinks book is a database instead of a table name. For example a call outside of a database query analyzer is database.dbo.table. I think you need to call as "datebase name".book.isbn. I will let someone more familiar with mySQL chime in. also you are missing your join statement. For example: Select * from table1 as t1 (nolock) inner join table2 as t2 on t1.field1 = t2.field1 where etc etc You can also left join, right join and outer join. Quote HistoryQuoted:
Quoted:
Quoted:
Quoted:
Quoted:
Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. also you are missing your join statement. For example: Select * from table1 as t1 (nolock) inner join table2 as t2 on t1.field1 = t2.field1 where etc etc You can also left join, right join and outer join. That will send OP into a coma, you know that, right?
|
|
|
|
Posted: 11/30/2016 12:39:53 AM EDT
[#34]
Quote HistoryQuoted:
My teacher had us learning joins like this <a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a> Quote HistoryQuoted:
Quoted:
Quoted:
SELECT b.Title, b.ISBN, MIN(c.AskingPrice) AskingPrice FROM Book b JOIN Book_Copy c ON b.ISBN = c.ISBN WHERE b.Title = [Enter book title] GROUP BY b.Title, b.ISBN; (eta. forgot the GROUP BY...) No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly. <a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a> That's the gay way. |
|
|
|
Posted: 11/30/2016 12:40:47 AM EDT
[#35]
Quote HistoryQuoted: No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly. Quote HistoryQuoted: Quoted: SELECT b.Title, b.ISBN, MIN(c.AskingPrice) AskingPrice FROM Book b JOIN Book_Copy c ON b.ISBN = c.ISBN WHERE b.Title = [Enter book title] GROUP BY b.Title, b.ISBN; (eta. forgot the GROUP BY...) No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly.  . |
|
|
|
Posted: 11/30/2016 12:41:21 AM EDT
[#36]
Quote HistoryQuoted:
<a href="http://s1201.photobucket.com/user/sabroso1/media/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/7047deff-c197-469c-9b52-e5f1ed0c4cc0_zpsc7ltgwhs.png</a> Quote HistoryQuoted:
Quoted:
Quoted:
Quoted:
Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. try from book, book_copy |
|
|
|
Posted: 11/30/2016 12:41:52 AM EDT
[#37]
Select b.isbn, c.price From book b, book_copy c Where B.title = 'UserEntered Name' And B.isbn = c.Isbn And c.Price = ( select min(price) from book_copy d where d.isbn = b.isbn) Get the isbn from book. Join it up with all of the cook_copy rows Identify the lowest cost book-copy row with a sun select use that value for the seco d joi . Done |
|
|
|
Posted: 11/30/2016 12:45:34 AM EDT
[#38]
Quote HistoryQuoted: Select b.isbn, c.price From book b, book_copy c Where B.title = 'UserEntered Name' And B.isbn = c.Isbn And c.Price = ( select min(price) from book_copy d where d.isbn = b.isbn) Get the isbn from book. Join it up with all of the cook_copy rows Identify the lowest cost book-copy row with a sun select use that value for the seco d joi . Done |
|
|
|
Posted: 11/30/2016 12:46:48 AM EDT
[#39]
SELECT TOP 1
b.ISBN,b.Title,c.AskingPrice FROM Book b INNER JOIN Book_Copy c ON c.ISBN = b.ISBN WHERE b.Title = @EnteredBookTitle ORDER BY c.AskingPrice ASC |
|
|
|
Posted: 11/30/2016 12:47:42 AM EDT
[#40]
SELECT 'Hello World';
|
|
|
|
Posted: 11/30/2016 12:50:07 AM EDT
[#41]
Quote HistoryQuoted: I'm kinda trying to keep the syntax the same as the way she's been teaching it. We haven't learned the b.xxx c.xxx type stuff Quote HistoryQuoted: Quoted: Select b.isbn, c.price From book b, book_copy c Where B.title = 'UserEntered Name' And B.isbn = c.Isbn And c.Price = ( select min(price) from book_copy d where d.isbn = b.isbn) Get the isbn from book. Join it up with all of the cook_copy rows Identify the lowest cost book-copy row with a sun select use that value for the seco d joi . Done |
|
|
|
Posted: 11/30/2016 12:50:26 AM EDT
[#42]
Anyone have any good cliffsnotes guides for beginners in SQL? I have about 50 gigs of data that would be much easier to work with if I learned how to use database software.
|
|
|
|
Posted: 11/30/2016 12:51:03 AM EDT
[#43]
Quote HistoryQuoted:
My teacher had us learning joins like this <a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a> Quote HistoryQuoted:
Quoted:
Quoted:
SELECT b.Title, b.ISBN, MIN(c.AskingPrice) AskingPrice FROM Book b JOIN Book_Copy c ON b.ISBN = c.ISBN WHERE b.Title = [Enter book title] GROUP BY b.Title, b.ISBN; (eta. forgot the GROUP BY...) No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly. <a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a> Yeah, that's awful if you're doing anything non-trivial. Or even if you aren't. But classes being what they are, suppose you'll have to do it that way for now. |
|
|
|
Posted: 11/30/2016 12:53:20 AM EDT
[#44]
Quote HistoryQuoted:
try from book, book_copy Quote HistoryQuoted:
Quoted:
Quoted:
Quoted:
Quoted:
Book has the title. Book_copy has the price. So you know you need to join those. TO join - you need to have a unique ID shared by each table - which in this case is ISBN. try from book, book_copy But yeah, this. The FROM clause should only have tables, not fields. |
|
|
|
Posted: 11/30/2016 12:54:20 AM EDT
[#45]
Quote HistoryQuoted: Yeah, that's awful if you're doing anything non-trivial. Or even if you aren't. But classes being what they are, suppose you'll have to do it that way for now. Quote HistoryQuoted: Quoted: Quoted: Quoted: SELECT b.Title, b.ISBN, MIN(c.AskingPrice) AskingPrice FROM Book b JOIN Book_Copy c ON b.ISBN = c.ISBN WHERE b.Title = [Enter book title] GROUP BY b.Title, b.ISBN; (eta. forgot the GROUP BY...) No need to return ISBN in the result set, and you should declare the inout item as a variable at the beginning so the query does not get edited directly. <a href="http://s1201.photobucket.com/user/sabroso1/media/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png.html" target="_blank">http://i1201.photobucket.com/albums/bb358/sabroso1/Screen%20Shot%202016-11-29%20at%2011.36.57%20PM_zpsachzbztg.png</a> Yeah, that's awful if you're doing anything non-trivial. Or even if you aren't. But classes being what they are, suppose you'll have to do it that way for now. |
|
|
|
Posted: 11/30/2016 12:56:46 AM EDT
[#46]
Quote HistoryQuoted: But yeah, this. The FROM clause should only have tables, not fields. Quote HistoryQuoted: Quoted: try from book, book_copy But yeah, this. The FROM clause should only have tables, not fields. |
|
|
|
Posted: 11/30/2016 1:02:39 AM EDT
[#47]
Quote HistoryQuoted:
ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need. Quote HistoryQuoted:
Quoted:
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1; ^this http://i.imgur.com/bLutSUl.jpg ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need. You're a Solution Architect too? Clearly, your data is replicated and duplicated in too many places. Just insert the price into the table with the ISBN and book name and drop the other table. Then delete all the duplicate records which don't have the lowest price. |
|
|
|
Posted: 11/30/2016 1:04:33 AM EDT
[#48]
Quote HistoryQuoted: You're a Solution Architect too? Clearly, your data is replicated and duplicated in too many places. Just insert the price into the table with the ISBN and book name and drop the other table. Then delete all the duplicate records which don't have the lowest price. Quote HistoryQuoted: Quoted: Quoted: Quoted: Quoted: select price,Title from Book order by price asc limit 1; ^this http://i.imgur.com/bLutSUl.jpg ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need. You're a Solution Architect too? Clearly, your data is replicated and duplicated in too many places. Just insert the price into the table with the ISBN and book name and drop the other table. Then delete all the duplicate records which don't have the lowest price. |
|
|
|
Posted: 11/30/2016 1:10:09 AM EDT
[#49]
Quote HistoryQuoted:
You're a Solution Architect too? Clearly, your data is replicated and duplicated in too many places. Just insert the price into the table with the ISBN and book name and drop the other table. Then delete all the duplicate records which don't have the lowest price. Quote HistoryQuoted:
Quoted:
Quoted:
Quoted:
Quoted:
select price,Title from Book order by price asc limit 1; ^this http://i.imgur.com/bLutSUl.jpg ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need. You're a Solution Architect too? Clearly, your data is replicated and duplicated in too many places. Just insert the price into the table with the ISBN and book name and drop the other table. Then delete all the duplicate records which don't have the lowest price. 
Schema changes are unsupported. You must be new here? |
|
|
|
Posted: 11/30/2016 1:16:35 AM EDT
[#50]
Quote HistoryQuoted:
Schema changes are unsupported. You must be new here? Quote HistoryQuoted:
Quoted:
Quoted:
ha, just looked at the screenshot. yeah you would need to generate a query relating the primary key. I would say fuck it and generate a new view linking the surrogates and then filtering the information i need. You're a Solution Architect too? Clearly, your data is replicated and duplicated in too many places. Just insert the price into the table with the ISBN and book name and drop the other table. Then delete all the duplicate records which don't have the lowest price.
Schema changes are unsupported. You must be new here? Yeah, you have 30 years of jankey code and "just get it working" fixes to support! |
|
|
Win a FREE Membership!
Sign up for the ARFCOM weekly newsletter and be entered to win a free ARFCOM membership. One new winner* is announced every week!
You will receive an email every Friday morning featuring the latest chatter from the hottest topics, breaking news surrounding legislation, as well as exclusive deals only available to ARFCOM email subscribers.
AR15.COM is the world's largest firearm community and is a gathering place for firearm enthusiasts of all types.
From hunters and military members, to competition shooters and general firearm enthusiasts, we welcome anyone who values and respects the way of the firearm.
Subscribe to our monthly Newsletter to receive firearm news, product discounts from your favorite Industry Partners, and more.
Copyright © 1996-2024 AR15.COM LLC. All Rights Reserved.
Any use of this content without express written consent is prohibited.
AR15.Com reserves the right to overwrite or replace any affiliate, commercial, or monetizable links, posted by users, with our own.
