Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market).
|
|
# ? Aug 26, 2009 00:43 |
|
|
# ? Jun 8, 2024 06:19 |
|
huge sesh posted:Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market). http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905/ I'm only halfway kidding, I looked at the preview and it looks like a decent intro to the relational model and SQL. Plus you could review it for us!
|
# ? Aug 26, 2009 01:27 |
|
quote:Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market). SELECT, INSERT, UPDATE, and DELETE (ie "SELECT TSQL", "INSERT TSQL TUTORIAL", etc) Those give you a nice foundation for manipulating the data, then you can get into the commands to actually build Tables, etc if so inclined. (ALTER, CREATE, etc) Markoff Chaney posted:http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905/
|
# ? Aug 26, 2009 01:33 |
|
Markoff Chaney posted:http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905/ Actually that book has gotten a few recommendations from the Recommend me some database books thread (which huge sesh should probably take a look at.) I've been meaning to pick it up myself.
|
# ? Aug 26, 2009 04:01 |
|
huge sesh posted:Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market). I've found http://techonthenet.com/ to be a good desktop-reference type place. The have a basic explanation of the syntax and examples, and if you end up somewhere that works with Oracle PL/SQL, this is a great place to find all the oddities of their implementation of SQL.
|
# ? Aug 26, 2009 14:41 |
|
Before I started a job involving 80% SQL I went to http://sqlcourse.com/ and http://sqlcourse2.com/ which did a decent job. Plus they have a test database you can play with.
|
# ? Aug 26, 2009 15:14 |
|
It's hard to see because of how far I scaled the image down, but what I have here is a list of part numbers. Each part has one of various reasons why it's been scrapped. This table keeps track of how many of each part have been scrapped for each reason. How do I manage to do something like this? I'm starting with a table of Scrap and there is a row for each item that was scrapped. It has infor such as part number (varchar), quantity (int), and reason scrapped (varchar) I would like each part number on it's own row, with each reason listed in the columns that follow it. We might have a part where 5 parts were contaminated, but on 10 parts the thickness was wrong. Here's what I am getting now: Basically I want to have a Scrap_Reason heading, and a count of each reason. Bob Morales fucked around with this message at 20:38 on Aug 31, 2009 |
# ? Aug 31, 2009 20:34 |
|
This should give you each unique part number on it's own row unless there's more than one scrap reason, then it'll give you the quantity and extended cost sum for each scrap reason:code:
code:
|
# ? Aug 31, 2009 21:14 |
|
If I understand you right, you want something like thiscode:
code:
|
# ? Aug 31, 2009 21:23 |
|
What I was looking for was more like:code:
|
# ? Sep 1, 2009 00:34 |
|
Bob Morales posted:What I was looking for was more like: If you are using SQLServer 2008 you could use the nifty PIVOT option, from the top of my head something along these lines. code:
|
# ? Sep 1, 2009 07:43 |
|
Hello all! I have small question that keeps bothering me. I've got a table called "favoritables", which stores a bunch of items people can add to their "favorites". Each has a unique id and various other info. I've also got a "favorites" table, which is just a table storing people's favorites, it has a couple of rows, the important ones being favoritable_id and user_id. My question is this: How would I, with a single query, fetch all "favoritables" with an additional field which contains the amount of "favorites" attached to a "favoritable" id? Example: 4 people have "a" in their favorites, and 2 have "b". I want something like this code:
code:
|
# ? Sep 1, 2009 14:34 |
|
Edit: ^^ for you - wait use Knightmare's, I forgot the group by. code:
ether posted:If you are using SQLServer 2008 you could use the nifty PIVOT option, from the top of my head something along these lines. Sub Par fucked around with this message at 15:16 on Sep 1, 2009 |
# ? Sep 1, 2009 15:10 |
|
Sir Davey posted:Thank you for your help! I can't get my head around this, and if I don't get any replies soon I'll just fetch all favoritables, and for each of those I'll do a SELECT COUNT(*) FROM favorites WHERE favoritable_id=## which would be horribly unoptimized! Your table names seem very confusing, I'm guessing you want something like this: code:
|
# ? Sep 1, 2009 15:15 |
|
Knightmare posted:Your table names seem very confusing, I'm guessing you want something like this: Knightmare posted:
Thanks a bunch, this works wonders! Performance-wise it isn't great but it's much better than what I originally came up with
|
# ? Sep 1, 2009 15:27 |
|
I have a SQL database with tables derived from CSV flat files an I need a better way of doing what I am doing. Here's how it currently works: - Data from a partner is exported from a database and dropped as a flat file into a directory called mmddyyyy on a sftp server - Each day, a script pulls any new data files down from the server to a file server with a matching directory structure Note: These files are technically "daily" files but some days it gets skipped due to database errors on their side and other days the job is rerun to backfill the gaps. The end result is that I have to parse all of the daily directories all the time to see if any files have appeared "in the past." - the tables on the local database are cleared - a DOS batch file then parses all of the daily directories and runs a BULK INSERT against each of the flat files, importing them into the database The problem with this is that the database is hammered every day as it processes the thousands of flat files and imports millions of records. The batch job takes about two hours to run these days and everything else dies while it happens. I drop the table and do a bulk insert because if I don't drop the table, I get duplicate records. Surely there is a better way of doing this, but you know, rookie.
|
# ? Sep 1, 2009 17:31 |
|
Sounds... financial. Am I close? I'm no DBA, but have you considered turning of indexing and logging for that particular table/database?
|
# ? Sep 1, 2009 18:05 |
|
Triple Tech posted:Sounds... financial. Am I close? Bingo. I work for an online broker. Indexing and logging are already turned off.
|
# ? Sep 1, 2009 19:20 |
|
Agrikk posted:Bingo. I work for an online broker. What is the nature of the duplicate records? Are the files you're importing cumulative (i.e. 9/1/2009 has all 8/31/2009 data plus all the stuff that happened in-between), or are they supposed to build on each other (and if so, why duplication)? Can you do anything with a primary key and insert only unique rows rather than dropping the table every day and doing a bulk insert of millions of rows?
|
# ? Sep 1, 2009 19:34 |
|
Sub Par posted:What is the nature of the duplicate records? Are the files you're importing cumulative (i.e. 9/1/2009 has all 8/31/2009 data plus all the stuff that happened in-between), or are they supposed to build on each other (and if so, why duplication)? The file I am importing are cumulative in some cases and not in others. I have full control over my local database so I can do whatever I want to it to make it more efficient. Can you tell me more about how I'd use primary keys and how to insert only unique rows?
|
# ? Sep 1, 2009 21:16 |
|
Agrikk posted:The file I am importing are cumulative in some cases and not in others. I have full control over my local database so I can do whatever I want to it to make it more efficient. Can you tell me more about how I'd use primary keys and how to insert only unique rows? Does the flat file have unique identifiers or do you assign them a unique number when importing? If they come with a primary key and you use whatever they have it should be easier.
|
# ? Sep 1, 2009 21:51 |
|
Knightmare posted:Does the flat file have unique identifiers or do you assign them a unique number when importing? If they come with a primary key and you use whatever they have it should be easier. The flat file has a name with a date string in it. The contents of the files vary, but generally include a customer code or an account ID or a trasaction ID or some combination of all three. They do not come with a primary key since they are extracts from a report derived from multiple tables on the far end.
|
# ? Sep 1, 2009 21:56 |
|
I have written a stored procedure that, when passed the ID number of a user, calculates that user's rating (a number that's meant to reflect how good a player that user is, based on how many games he has played and how many he has won) and updates the user's row with the rating. So when I send MySQL the query "CALL CalculateRating(12345)" I am getting it to do something of the form "UPDATE User SET Rating = (result of some subquery) WHERE UserID = 12345". What I would like to know is, can I write a single query that will do this for each of my users at once? At the moment I have a query "SELECT UserID FROM User", and I loop through the results in PHP and issue a bunch of queries that say "CALL CalculateRating($userid)". Is there SQL syntax to say, "for each UserID in such and such a results set, CALL CalculateRating(UserID)"? Or could I go even better: could I write a single query that updates each of the rows of User with the value calculated for that user from a subquery involving a "GROUP BY UserID" clause?
|
# ? Sep 2, 2009 00:08 |
|
Hammerite posted:I have written a stored procedure that, when passed the ID number of a user, calculates that user's rating (a number that's meant to reflect how good a player that user is, based on how many games he has played and how many he has won) and updates the user's row with the rating. So when I send MySQL the query "CALL CalculateRating(12345)" I am getting it to do something of the form "UPDATE User SET Rating = (result of some subquery) WHERE UserID = 12345". Something like: UPDATE users u SET rating = (SELECT SUM(s.wins)/SUM(s.games_played) FROM stats s WHERE s.user_fk = u.id); (change the select to how your actual formula works naturally)
|
# ? Sep 2, 2009 01:11 |
|
Carthag posted:Something like: Thanks for this. I didn't realise that it was legal to refer in a subquery to a table that is only part of the outside query. One thing I wonder: Is MySQL doing this "sensibly" (meaning: it creates the result set you call s only once), or is it doing it the dumb way (creating s over again for each row in u)? I would hope it's doing it properly, as opposed to doing thousands* of redundant calculations, but I've no idea how I'd check. *if not millions - there are close to a thousand rows in User, so if it creates s r times, where r is the number of rows in User, then I guess it is doing r*(r-1) too many calculations. Hammerite fucked around with this message at 15:54 on Sep 2, 2009 |
# ? Sep 2, 2009 15:49 |
|
Hammerite posted:Thanks for this. I didn't realise that it was legal to refer in a subquery to a table that is only part of the outside query. For the result set to be only calculated once, you would have to select all rows and group, then loop through those rows and update. But that's not what this kind of bulk update statement does.
|
# ? Sep 2, 2009 20:31 |
|
MoNsTeR posted:But that result set is different for every row in "u" It isn't the way I've written it. My s gets the necessary information to return each user's rating, and uses a GROUP BY clause. So s will be the same every time. Full disclosure - my query looks like this: code:
So my s (called SubqueryA) does not need to be recalculated every time - are you telling me that it will be?
|
# ? Sep 2, 2009 23:01 |
|
Yes it will be. When you issue a statement of the form update <table> set <column> = <select statement> That select statement will be run for each row in <table>. So generally when you write this kind of statement, your subquery should be correlated to the rows in the table being updated. If it's significantly slower to do this than to calculate the results for all rows in one shot, then you should do that, and loop through the results issuing an update for each row, rather than trying to issue a single update.
|
# ? Sep 2, 2009 23:18 |
|
MoNsTeR posted:Yes it will be. When you issue a statement of the form Thanks. Is there a way to do this using a stored procedure? As in: can I write a stored procedure that calculates my SubqueryA and then loops through it as you suggest - or do I need to go back to looping through a result set in PHP? EDIT: Never mind, I have found out how to do this using MySQL cursors. Hammerite fucked around with this message at 01:01 on Sep 3, 2009 |
# ? Sep 2, 2009 23:28 |
|
Using OCI8 and ADOdb. This is an odd one. The loop goes along happily until it hits December 2008, then the program just exits -- no happy message, no error message, nothing in the logs.php:<? $days=365; // days to go back $chunksize=10; // process this many days at a time $pniq2= /* a stored query, details to follow */ ; $datetime=new DateTime(); $today=$datetime->format('Ymd'); $datetime->modify('-'.$days.' days'); $dateuntil=$datetime->format('Ymd'); while($dateuntil!=$today){ $extract_date=$dateuntil; $datetime->modify('+'.$chunksize.' days'); $dateuntil=$datetime->format('Ymd'); if($dateuntil>$today) $dateuntil=$today; $rs=$db->Execute($pniq2, array('extractdate'=>$extract_date,'untildate'=>$dateuntil)); echo('<p>I just made a query!</p>'); if(!$rs)die('<h1>Error: '.$db->ErrorMsg().'</h1>'); while($item=$rs->FetchRow()){ //stuff } $rs->Close(); } ?> code:
about 10 times, then code:
Edit Turns out it was out of memory after all, who knew somebody would dump in 40000 records in the space of a week. Now I have PHP's memory limit set to friggin' 256M, oh boy. Now, why doesn't it say out of memory? Tad Naff fucked around with this message at 00:39 on Sep 3, 2009 |
# ? Sep 2, 2009 23:48 |
|
I'm trying to learn SQL with IBM DB2 but I'm stuck trying to make some crazy-rear end queries. I want to find out, if all the giraffes combined weigh more than all the elephants combined, but it's proving to be a real nutcracker for a novice like me. I've drawn some diagrams of taking it step by step: 1. compiling a list of each animal and its weight. 2. summing each species combined weight. 3. removing the species that aren't giraffes or elephants. 4. Comparing which combined weight is larger. I've been able to make queries to sum up a single species' weight but I can't figure out how to make a list of it. Anyone have any good ideas? code:
|
# ? Sep 3, 2009 13:21 |
|
To sum up the weight of all animals in each species:code:
|
# ? Sep 3, 2009 16:04 |
|
I don't know if DB2 allows CASE, but here's what I would do:code:
|
# ? Sep 3, 2009 20:00 |
|
This doesn't go to answering your question in the slightest but it is an important design tip that merits mentioning. "fodder" on "species" should be "fodder_id", "type_id" on "spec_animal" should be "species_id", and "id" on "food_storage" should be "fodder_id". This may seem trivial but when you're reading a query you wrote last year against tables someone else created the year before that, it makes orders-of-magnitude difference in the readability and intelligibility of a query. (To take it one step further, "id" on "species" should also be "species_id", "id" on "spec_animal" should be "spec_animal_id", etc. This is my preference though and I can see the argument for always using "id".)
|
# ? Sep 3, 2009 20:58 |
|
Kind of a silly question . . . I have a (fairly small, fairly simple) database set up using Postgres. Eight tables total. Can anyone recommend DB visualization software? Something that visualizes schema/dependencies/et cetera . . .
|
# ? Sep 4, 2009 04:13 |
|
Boz0r posted:I'm trying to learn SQL with IBM DB2 but I'm stuck trying to make some crazy-rear end queries. I want to find out, if all the giraffes combined weigh more than all the elephants combined, but it's proving to be a real nutcracker for a novice like me. Not at all familiar with DB2 but I would approach it something like this, although I must say that mixing conditional aggregates and group by's are very dependant on what order the database evaluates them. code:
|
# ? Sep 4, 2009 08:47 |
|
I've been getting deadlocks issues on our MSSQL database for the past few days. I ran SQL profiler and extracted the deadlock graph and found out that the following query has a Shared intent update (SIU) lock mode.code:
|
# ? Sep 4, 2009 11:24 |
|
I'm using MySQL with InnoDB tables and am trying to avoid some potential concurrency issues and I'm not sure which of the two ways I've come up with is the best way to do it (or if another better solution exists). Let's say I've got an auction database with two tables, auctions (items up for auctions), and bids (history of bids on auction items). I want to make sure that everything is ok if two bids are placed at the same time on the same auction. Let's say a bid of 30 is being placed on auction with id=1. I need to make sure the bid is valid (the auction price isn't already at 30 or above) and then place the bid. Lines starting with # are pseudocode for application logic. Approach 1: code:
code:
edit: I have doubts about Approach 1: is the read on the price column for the WHERE clause and the update to the price column one atomic instruction? Or can another connection update the value of the price after the price is read for the WHERE clause but before the update happens? edit2: I'm now pretty convinced that Approach 2 is definitely what I should be doing - but I still want to make sure I'm not missing anything. supster fucked around with this message at 20:16 on Sep 4, 2009 |
# ? Sep 4, 2009 20:11 |
|
Both are doing what you want, which is essentially 1. lock the auction row 2. check that the new price is valid 3. if so, then set the new price and insert the bid 4. commit, thereby releasing the lock The where clause of the update is part of the same transaction so there is no chance you will get dirty data (unless your isolation level is set to Read Uncommitted or something crazy like that). Personally I like #1 for its simplicity but #2 has additional clarity. It's a toss-up.
|
# ? Sep 4, 2009 22:05 |
|
|
# ? Jun 8, 2024 06:19 |
|
nevermind...
nbv4 fucked around with this message at 02:49 on Sep 5, 2009 |
# ? Sep 4, 2009 22:21 |