|
AskYourself posted:You might be right For a simple case I compared the execution plans of SELECT * vs SELECT 1 and they're identical.
|
# ? Nov 22, 2017 21:31 |
|
|
# ? Jun 4, 2024 19:36 |
|
I never thought that I would miss SQL stuff until we started using Firebase Don’t get me wrong, it can be nice, but it’s just so foreign compared to SQL At least it does all the serialization of POJOs for me I guess
|
# ? Nov 27, 2017 21:14 |
|
Is there an easy way to mass rename tables and columns without losing data?
|
# ? Nov 28, 2017 13:27 |
|
Jose posted:Is there an easy way to mass rename tables and columns without losing data? I've never seen mass ways of doing this, but in SQL Server sp_rename is a thing. What is it you're really trying to do?
|
# ? Nov 28, 2017 14:17 |
|
Jose posted:What server role do I need in order to be able to use the SQL server import export wizard? I'm transferring tables across multiple databases/servers and I'm feeling lazy enough to not give myself data writer/reader on them all but don't want to leave the account with sysadmin I have a similar but perhaps stupider question: what is the best way to export a table including all the mssql rules (must be unique, primary key, data types etc.) I have a test database and I want to make sure a table I create for an app update is working properly there before I add to the production database. But export options-- at least the human readable plain text ones-- seem to drop this information. There must be a very very simple and common way to do this, no?
|
# ? Nov 28, 2017 21:17 |
|
mr_package posted:I have a similar but perhaps stupider question: what is the best way to export a table including all the mssql rules (must be unique, primary key, data types etc.) I have a test database and I want to make sure a table I create for an app update is working properly there before I add to the production database. But export options-- at least the human readable plain text ones-- seem to drop this information. There must be a very very simple and common way to do this, no? To make sure I understand properly, by "export" you mean just the table structure and not the data contained within it, correct? If so, yeah that's super easy in the SSMS: gives you etc etc
|
# ? Nov 28, 2017 21:28 |
|
YES that looks much much better; I was exporting to csv, txt, whatever human-readable export options I could find in SMSS and they were of course omitting all that stuff. HA HA HA I'm in charge of the database that the entire company runs on.
|
# ? Nov 29, 2017 00:34 |
|
Two very basic questions coming your way... I have a Field in a table where the field is 'Firstname, Lastname', so it looks like: 'John, Smith' 'Jane, Doe' How do I go about sorting this field by last name in alphabetical order, so it displays like: 'Doe, Jane' 'Smith, John' Also, is there a way to add text into a field's data? For example, I have a field that lists all of the hire dates for employees that displays as: '11/11/1996' '02/13/2008' But I would like each individual entry to display as: 'Hired in 1996' 'Hired in 2008' removing the mm/dd and only keeping the year. Any ideas?
|
# ? Nov 29, 2017 14:36 |
nwiniwn posted:Two very basic questions coming your way... In MySQL: SQL code:
Shy fucked around with this message at 16:07 on Nov 29, 2017 |
|
# ? Nov 29, 2017 14:55 |
|
nwiniwn posted:Two very basic questions coming your way... LastName, FirstName in MSSQL: code:
code:
|
# ? Nov 29, 2017 15:07 |
|
kumba posted:LastName, FirstName in MSSQL: Thanks for the help-I'm using Oracle. That function for hired in year says it's missing a right parenthesis.
|
# ? Nov 29, 2017 15:34 |
|
nwiniwn posted:Thanks for the help-I'm using Oracle. That function for hired in year says it's missing a right parenthesis. Oracle SQL apparently uses double bar instead of + for string concatenation, so try this: code:
|
# ? Nov 29, 2017 15:42 |
|
nwiniwn posted:Two very basic questions coming your way... code:
code:
|
# ? Nov 29, 2017 15:44 |
|
Jethro posted:
This worked-thanks! edit: however, the first code is only returning the info in the same format it began 'Firstname, Lastname', not 'Lastname, Firstname' like I was trying for: code:
nwin fucked around with this message at 16:06 on Nov 29, 2017 |
# ? Nov 29, 2017 15:56 |
|
help me normalize this mess (postgres 9.6, but could pretty easily be 10 if it matters)code:
totally ok with exclude or check constraints, altho i'm not sure they work here edit: i solved this, check it: code:
the talent deficit fucked around with this message at 04:49 on Nov 30, 2017 |
# ? Nov 30, 2017 01:55 |
|
edit
Busy Bee fucked around with this message at 14:24 on Dec 1, 2017 |
# ? Nov 30, 2017 04:46 |
I'm also boning up on SQL in preperation for an interview. They sent me a packet of questions that I totally loving bombed but for some reason they still want to talk to me. One question was take a select query and spot the problem. I stated what the query was supposed to do (get a list of people who did not win a game), but not what was wrong. I threw it up in MS Access to see what happened and it returns nothing unless I take the subquery SELECT heat_id FROM players and add WHERE IS NOT NULL to the end, which causes it to suddenly put out the data I wanted. There is a game where the heat_id is null. What gives? I would figure the subquery would just pull what it can and ignore the null, it certainly does when you isolate it. Put it in a subquery and all of a sudden the null brings the whole thing to a screeching halt. The full query was SELECT id from players WHERE id not in (select heat_id from players) This doesn't return anything unless I stick where is not null to the end of the subquery.
|
|
# ? Nov 30, 2017 07:55 |
|
skooma512 posted:I'm also boning up on SQL in preperation for an interview. They sent me a packet of questions that I totally loving bombed but for some reason they still want to talk to me. x NOT IN (NULL) evaluates to UNKNOWN per standard SQL. x NOT IN (x, NULL) evaluates to FALSE per standard SQL. All possible values of x give a result of either UNKNOWN or FALSE if there are NULLs in the IN-list, so you'll never select anything.
|
# ? Nov 30, 2017 11:18 |
|
Alright, new question...in my table "employees", I have names given as: employee_name = 'Firstname, Lastname' ex: John, Smith Jackie, Doe Morgan, James I want to get a count of each person's first letter of their last name, so if I had 3 people whose last name started with S, 4 started with T, and 2 started with A, it would return this: A: 2 S: 4 T: 3 This is the code I have so far...but it's returning everything by the first letter of the first name...since people have different amounts of characters in their first name, I'm not sure how to move it so it just shifts to the second word in the field, after the comma...any ideas? code:
|
# ? Nov 30, 2017 14:21 |
|
Take a look at CHARINDEX to hone in on the comma.
|
# ? Nov 30, 2017 15:27 |
|
nwiniwn posted:Alright, new question...in my table "employees", I have names given as: This makes me sad. Send a sternly worded letter demanding that they stop breaking zeroeth normal form with this poo poo. One piece of data per column dammit.
|
# ? Nov 30, 2017 16:06 |
|
his questions read like homework problems so i'm assuming these are not real world examples because holy gently caress if that's a real thing whoever designed it should be shot
|
# ? Nov 30, 2017 16:10 |
|
Yeah it’s for a 6-week MBA course on Databases, where the first two were spent on Access and the last 4 is SQL. No guidance was given on this so I’m trying to google whatever I can to figure things out. edit: I think I've got it so it will sort by the second word...would this be correct? code:
nwin fucked around with this message at 16:49 on Nov 30, 2017 |
# ? Nov 30, 2017 16:39 |
|
but hopefully they are using this as a "teachable moment" kind of thing and saying "this is the kind of problem you get if you don't design your database properly"?
|
# ? Nov 30, 2017 17:38 |
|
Hammerite posted:but hopefully they are using this as a "teachable moment" kind of thing and saying "this is the kind of problem you get if you don't design your database properly"? haha. no...at least not yet.
|
# ? Nov 30, 2017 17:41 |
|
Solve every problem by adding two computed columns 'firstname' and 'lastname' and then doing proper joins over those. Or for more passive-aggressiveness, copy the data over to a normalized table, DROP the old table, then proceed to solve the problem.
|
# ? Nov 30, 2017 18:19 |
Hammerite posted:x NOT IN (NULL) evaluates to UNKNOWN per standard SQL. Thanks! That makes sense.
|
|
# ? Nov 30, 2017 19:07 |
|
NihilCredo posted:Solve every problem by adding two computed columns 'firstname' and 'lastname' and then doing proper joins over those. It's so beautiful. I love this.
|
# ? Nov 30, 2017 21:30 |
|
I currently have a problem where I am trying to multiply an integer by the number of non-null values across a few other columns. However, the columns being checked hold letters rather than integers. In this example I'd basically want my query to return a column that would say 220 220 110 220 160
|
# ? Dec 1, 2017 20:50 |
|
Kwilty posted:I currently have a problem where I am trying to multiply an integer by the number of non-null values across a few other columns. However, the columns being checked hold letters rather than integers. code:
|
# ? Dec 1, 2017 21:02 |
|
kumba posted:
holy poo poo that there is some good thinkin. e: I told myself I was overthinking this too.
|
# ? Dec 1, 2017 21:19 |
|
sometimes all it needs is a second pair of eyes
|
# ? Dec 1, 2017 21:26 |
|
kumba posted:
So sadly the database we use for some reason has a mix of blanks and null values for these fields, and it appears to be messing with the calculation.
|
# ? Dec 1, 2017 23:07 |
|
Kwilty posted:So sadly the database we use for some reason has a mix of blanks and null values for these fields, and it appears to be messing with the calculation. well that's stupid as hell but should be easy enough to fix code:
|
# ? Dec 1, 2017 23:14 |
|
Basic GROUP BY question. Why is it that when there are multiple columns involved that you have to include both columns when doing the GROUP BY statement. For example http://www.dofactory.com/sql/having SELECT AVG(TotalAmount), FirstName, LastName FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id GROUP BY FirstName, LastName HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200 Why can't I just say "GROUP BY FirstName"? If I do that it brings an error and I have to include LastName as well. What is the reasoning behind that?
|
# ? Dec 2, 2017 07:22 |
|
"When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column." ... group by firstname,lastname; distinguishes between "John Doe" and "John Deer". select sum(amount),firstname group by firstname; accumulates their amounts into the same row. (If you select sum(a),b,c group by b; it's like saying you want a join on field c, but you haven't specified how that join is to occur, so it gets confused.)
|
# ? Dec 2, 2017 07:45 |
|
Because you're telling the DBMS which information from individual rows is relevant and which is to be summarized by the aggregate function. Try to forget the "simple" idea that the GROUP BY clause just automatically gets all the non-aggregate stuff from the SELECT clause, as if that was just syntax bookkeeping SQL makes you do for no good reason. Turn your thinking around instead: always start by considering what kinds of information you want to keep significant. Do you want an average just over the first name of your customers, like the average amount a "Jason" or "Melinda" has, or do you want the average for every unique combination of first and last name, as in separate values for a "Jason Alberich" vs a "Jason Smith" (but still aggregating over every "Jason Smith", if there are multiples)? This will determine your GROUP BY clause, and once you have your GROUP BY clause figured out, then you know what is available to you in the SELECT clause. Remember that GROUP BY aggregates data. You can't SELECT columns that aren't in the clause because those columns have been discarded in the process of producing those aggregate values. There are no individual rows of the base table "hidden" here, because you're not dealing with the base table, you're dealing with the aggregation you ordered the DBMS to create from it. The columns in the GROUP BY clause are all that's left because that's what you told the DBMS is significant, everything else got folded up. If you're looking for a solution that lets you work with individual rows and compute aggregate values to just appear as another column - like, say, you want the full record for Joey Dudeguy from Squirreltown, but want a pseudo-column tacked on that contains the average spending for all people from Squirreltown - you're going to want to look into analytic (or "window") functions.
|
# ? Dec 2, 2017 07:48 |
|
For me, one of the most important steps to truly grokking SQL (and it's a language that flies once you grok it) was learning the order in which a query is executed. This is for SQL Server but I think most databases will be the same:
Memorize that list (well, maybe not the WITH CUBE / ROLLUP, whatever it is). Once you do that, a lot of questions and 'why doesn't this query work??' moments suddenly evaporate. It's obvious why you can use your custom named columns in some parts of the query but not others. It's obvious what happens when you use DISTINCT(SUM()) and GROUP BY in the same query. It's obvious why putting a condition in the JOIN or in the WHERE is equivalent. Et cetera.
|
# ? Dec 2, 2017 10:37 |
|
Thank you for the clarification. I moved onto the ANY and ALL operators and I'm having a hard time wrapping my head around it while I prepare for this analyst interview. Here is the resource I am using: https://www.w3schools.com/sql/sql_any_all.asp I don't understand the use of the ALL operator, especially with a data set. I would assume its not that common for a column of data to ALL be the same where it returns TRUE? None of the videos on YouTube all the tutorials really explains it well in using ANY / ALL operators. In addition, why would someone want to use a subquery rather than using JOIN. Are they not similar? Busy Bee fucked around with this message at 03:44 on Dec 3, 2017 |
# ? Dec 3, 2017 03:21 |
|
|
# ? Jun 4, 2024 19:36 |
|
That depends on how you define the condition. Example:code:
Similarly, subqueries aren't always the solution, but they have their uses. Example: code:
code:
|
# ? Dec 3, 2017 07:33 |