What are
/r/SQL's
favorite Products & Services?
From 3.5 billion Reddit comments
The most popular Products mentioned in /r/SQL:
The most popular Services mentioned in /r/SQL:
SQLZOO
HackerRank
PostgreSQL
CodeProject
Microsoft SQL Server
DBeaver
SQL Server Management Studio
Stack Overflow
DataGrip
SoloLearn
SQLite
Amazon Web Services
Codewars
LeetCode
SlideShare
The most popular Android Apps mentioned in /r/SQL:
The most popular reviews in /r/SQL:
You install a server. Go here and click "download now" on Developer edition.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
After installation you can connect to localhost, which is your local computer.
Yes, you can use SQL Server 2017 Developer. It's free to use for learning. They say:
"SQL Server 2017 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment."
Download here.
f.lux to take the blue out of the screen if I'm using SSMS, etc.
VS Code in Solorized Light or Dark mode.
The newer Azure Data Studio (a spin off of VS Code just for SQL Server) in Solorized Light or Dark mode.
Both of the latter allow you to zoom the whole interface making text large.
This book was passed around my first jobs office to us interns as we scrambled to learn all we could to get promoted to a junior position... it kick started my career.
I went from data analysis in financial services to data analysis in marketing at a different organization. While the comment about Excel has merit for some tasks, I still use SQL for the vast bulk of my work.
In particular, I'm using it to do response rate and lift calculations, analysis in identifying target audiences, and various forms of exploratory data analysis.
When you get into performing tasks like basket analysis and affinity groupings, Excel will simply not be able to handle the million+ (100Million+) rows. However, in many situations, using SQL to generate some high-level aggregates that you then put in Excel for nimble ad-hoc analysis can be a great combination.
If you want a good starting point for what you can do with SQL for marketing, I would recommend Data Analysis Using SQL and Excel. It won't teach you the language, but it will show you the basics of what's possible, and how to implement various solutions once you have a solid, foundational understanding.
As for another language, I would recommend picking up R. It's very useful, free, and has great learning resources available. Johns Hopkins offers a free 4 week intro on Coursera.
Get a data modeling tool, most have a way of connecting to a DB and reverse engineering it into a physical data model (showing tables, columns, table relationships, etc.). Here a couple of free ones which support all DBs and have reverse engineering capabilities:
Are you asking for a tool for querying and using the PostgresSQL database? If so I prefer DBeaver on Mac. It’s free and open source. https://dbeaver.io
Also there is pgAdmin, which is kind of an “official” one. In my opinion it’s clunky not as option packed as DBeaver.
Edit: Azure Data Studio works on Mac too. Again, not as good as DBeaver, IMO. Azure’s community support could eventually bring it towards to top of the list though.
I think this is a great beginners / fundamentals book. It will give you an in depth focus on the basics:
​
You're concatenating strings, including user input, to create your SQL statements. This is the fast track to a p0wn3d website.
You need to be using prepared statements (linked to the PHP docs only because that's what you're running) to interact with your database (BTW, specifying your database is requested in the sidebar). This will eliminate about 99.99% of your SQL injection risk. It will handle your punctuation properly and any other string input as well. Oh, and your database will thank you too (at least if it's SQL Server, can't speak to the others) because it'll be able to better create and cache query plans.
If your setup is such that prepared statements aren't possible, fix it so that they are.
DO NOT CREATE SQL STATEMENTS FROM USER INPUT WITH STRING CONCATENATION. EVER.
Take your website offline (or at least this feature of it) until you have this fixed.
I wouldn't be the best person to provide career advice, but wanted to respond to this point:
>It is really my first experience doing any sort of coding (if SQL counts).
SQL is absolutely coding. While the most common kinds of programming follow the imperative paradigm (e.g. procedural and object-oriented code), SQL is one of several types of programming in the other major paradigm family--declarative programming.
Imperative programming focuses on giving the computer a set of explicit directions that will lead the computer to the desired result. Declarative programming instead focuses on giving the computer a detailed technical description of the desired result and leaving the actual mechanism of how to find upon that desired result up to the computer.
The fact that SQL is declarative rather than imperative doesn't mean it isn't coding. Often, complex manipulation of data sets can be done much more efficiently by an RDBMS via SQL than would be possible via an imperative language... The imperative language usually takes longer to write the code to do it and will often be computationally inefficient at doing it as the RDBMS is already heavily optimized for doing the vectorized arithmetic.
The following article is a really valuable piece I read last year that I strongly advise looking into if you're not sure if SQL should be considered "coding":
https://dev.to/geshan/you-can-do-it-in-sql-stop-writing-extra-code-for-it-lok
Properly used, SQL can replace a lot of completely unnecessary imperative code when working on data sets and will do so much more efficiently than anything a non-genius coder is likely ever to write.
It really depends how far along you are.
I'd suggest Hacker Rank. They have great tutorials, and exercises for you to practice with. If they're too tough, there are discussions where people will walk you throw the example. Great self paced learning.
Once you get a firm hold on the basics, I think you should practice yourself. Download data from Kaggle or look online, and pull the data you need. You'll have to find a way to hook up to a database with a MSSQL or MySQL client.
It's hard to beat SQLZOO. The tutorials give you example SQL statements and then ask to to modify the SQL to achieve a slightly different result. It lets you try out SQL without having to install a database server. You can select from several different popular database engines as well.
SQL Server Management Studio + SQL Server Developer Edition. They're free.
https://www.theodinproject.com/courses/databases See this from odin project, after each lesson you get pracitcal sql examples. Bthw one of the webistes you can leverage your sql skills is called 'Sql zoo' search for it in google :)
If you want to learn SQL, I'd suggest you to use PostgreSQL. It's free, open source and very close to match ANSI SQL. I can't give you any resource though, beside the doc
frankly first I would ask the execs what ERP they want to switch to
because from there you can insert directly into the database if its an on prem ERP(that can be a monumental task that you can find consultants to do) or the ERP probably accepts flat file import
some ERP vendors offer quickbooks database "upsize" wizards but they only work on the actual quickbooks database and probably can't handle 10 years of data
if they just want to report on it you might be better off finding a data warehousing tool
Like /u/DharmaPolice and /u/alinroc said, a pre-built SAAS solution may be more beneficial. Have you tried asking the folks over at /r/smallbusiness or /r/Entrepreneur how they do inventory management?
I saw odoo suggested somewhere and I found their pricing page [it's doing some weird geolocation locking to me, I'm not in the US].
https://msdn.microsoft.com/en-us/library/ms181091.aspx
You can find out EXACTLY what is causing it!
Also:
SELECT session_id, num_writes, st.text AS statement_text FROM sys.dm_exec_connections AS ec CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st ORDER BY num_writes DESC
http://use-the-index-luke.com/
You should install and run a local database. Note that different backends have slightly different dialects; select * from foo as bar in mysql vs select * from foo bar in postgres, and other small changes like that.
Probably install MySQL or Postgres.
My 2 recommendations are
- sqlzoo
- the Coursera db course (just the SQL homeworks). If you can get through those two homework assignments you can do pretty much anything in SQL.
you can start with SSRS which is Sql Server Reporting Services this comes as a standard with MS SQL and gives you an option to install when you are installing MS SQL, if its 2005 or later. You can also connect Access to SSRS.
Refer to this link its a good starting guide http://www.codeproject.com/Articles/10377/Integrating-Microsoft-Access-DB-SQL-Reporting-Serv
If all you're doing is "limited to creating new tables and running queries", then you have a few options. You can download the developer edition of SQL Server and SSMS for free.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
If you don't want to install MSSQL on your local machine, you can always download a developer virtual machine from Microsoft and install MSSQL on that. I don't have Windows 8 or higher, so I can't install the latest versions because of min OS requirements. But, my machine will still run VM's well enough. So, I'm using these VM's to do some training.
I think you would find HackerRank intersting. It has over 50+ challenges starting from basic selects to advanced joins. In each challenge you will get a new problem. Once you run/submit your code you will get a feedback from the system whether you did good or not. All you need is an account (free).
Have you considered sqlite? You won't need to install a client/server to get rocking with it.
See if it will work for you: http://www.sqlite.org/whentouse.html
Note, you can't do SP directly with sqlite: http://stackoverflow.com/questions/3335162/creating-stored-procedure-and-sqlite
To me, installing the SQLite ODBC driver and querying from desktop software (Access, Excel Power Query, MSQuery) would be the easiest way.
This will allow you to choose only the columns you want in the query, as well as filter by criteria.
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
Installing this will allow you to query your SQLite via ODBC using desktop software.
Isolation levels are what you need to read up on. Default is READ COMMITTED so that would mean in your scenario you would only be seeing stuff that was committed prior to your query execution.
> I knew I was going to feel retarded while facing the solution to my problem (given or found) and hey I was right.
Buddy, this is a problem.
You shouldn't feel bad learning things. You're new. It's normal to learn things.
This should feel good, not bad. You didn't fail! You went out, you got help, and you grew.
Keep doing what you're doing. You're doing it well.
.
> I'll do my best to improve my skills from here !
When you're on a little bit more solid ground, I recommend you consider reading the webpage use the index luke, by Markus Winand, and material by a guy named Bill Karwin, especially a slideshow he made about one of his books, both called SQL Antipatterns Strike Back.
I feel like you probably need a month or so of practice before you're ready for those works, but
The big one is practice, buddy.
Have fun
You've asked a rather open ended question on a subreddit for more SQL programming sorts of things.
But anyway, I'll still give you a basic runthrough:
How the database is set up is determined by what host you're on. It goes from easy (such as with Bluehost here: https://my.bluehost.com/cgi/help/6 ) to difficult, such as with Amazon AWS here: https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-centos-6
I am going to recommend that what you do is search for whomever you chose as your host along with "Mysql." You'll probably pick up what you need to do!
One last thing of note for you! You're getting into a world of tons and tons of options, and they do change pretty quickly, so it's quite exciting. But if you want to get started with a basic understanding of everything, I recommend you look up what a "LAMP stack" is. It's sorta the underpinning of a lot of web sites out there, and if you really learn this stuff you can make a lot of money and have a pretty fun job!
Take care,
-Chris
An ORDER BY is irrelevant on an update: you're updating all records that match the criteria in your WHERE clause. A database transaction is (well, should be) an all-or-nothing thing: when your UPDATE successfully completes executing, all relevant records will be updated, and the order of their updates really just doesn't matter.
ORDERing data really only matters when you're reading it (SELECTing) out of the system.
Also, you can update multiple fields at once, rather than having to run 3 separate update statements:
UPDATE dimDate SET year = datepart(year, [dbo].[dimDate].[SupplierDate]) ,MonthName = datename(month, [dbo].[dimDate].[SupplierDate]) ,Day = datepart(day, [dbo].[dimDate].[SupplierDate])
EDIT: Give this a read, and do some more research on your own about set based vs procedural thinking. With SQL, you want to be thinking in sets - when that sort of logic clicks for you, you'll turn into a SQL wizard almost overnight :)
EDIT x2: Another link for you
SQL is easy to learn, hard to master... like most things.
You might look at trying problems on leetcode.com that have solutions provided to try and figure out where you are messing up.
Ok so you want the advantage of SQL but aren't allowed to use a local instance of SQL Server? If you are allowed direct read access to the server may I recommend Database .Net It's basically SSMS but you can connect to many more datasources with ease (Such as Excel or Access) whilst still using SQL. It also requires no instance, it's an exe with no install.
So you could create an ETL query to pull the data from your production databases into a local Access database, or straight to an Excel report, then just run that daily through Database .NET?
No.
Even systems that did not support SQL in the beginning are now starting to support it.
E.g. recently Elasticsearch: https://www.elastic.co/blog/elasticsearch-6-3-0-released
Further, SQL is not limited to the relational model anymore. SQL-92 was the last standard the embraced the purely relational model. SQL:1999 added non-relational concepts such as arrays, compound types and the like. For the avoidance of doubt: this was really added to the SQL language itself—not an procedural extension like PL/SQL.
SQL is a standard that is implemented by lots of different database systems. What are you trying to do with SQL? If you're just trying to play around, you might be able to get away with SQLFiddle without installing anything. If you're interested in setting up a simple SQL server and building a web page, I'd recommend starting with MySQL via WAMP
> get my database like up to a server
You'll need a server, some skills in ssh and ftp/sftp and your scripts.
> maybe connect it to a website
Are you looking at wordpress or hand-rolling your own? You'll need some skills in php probably for both. If you just want to connect to a remote database and run queries then for MySQL there is phpadmin
Edit: there are other languages out there
Edit2: Just noticed that you want to use SQL Server
I'm wondering what's new in SQL though? I haven't used it day to day since August 2016, but still do the basics occasionally in my current role.
Here's the Amazon link to checkout the review scores: SQL Cookbook: Query Solutions and Techniques for Database Developers (Cookbooks (O'Reilly)) https://www.amazon.com/dp/0596009763/ref=cm_sw_r_cp_apa_i_iJnuCbBS4T84X
Mostly positive and FWIW, the Kindle version is $17.27.
I was already considering buying the bundle, now I'm leaning even more towards purchasing.
There are a lot of ways to accomplish this. I've had a hard time resolving what you're looking for by using a set based query and typically resort to correlated subquery. The correlated subquery is a subquery that references the primary query and joins on it. The result is the subquery is executed and analyzed for each row in the main query.
Here's an example of one.
https://stackoverflow.com/questions/879111/t-sql-subquery-maxdate-and-joins
There's also other methods but I feel correlated subquery is my favorite minus figuring out a good way to execute in a set.
https://www.jetbrains.com/datagrip/
​
That one looks pretty and does things. Also has themes so you can make it look whatever colors you prefer.
Reddit search isn't doing SQL Database Queries:
It uses a search engine called Cloudsearch on AWS:
A Search Engine is a very different technology.
This scares me a lot. Most devs who work under HIPPA scope get paid big, because the liability could be big, and the regulatory requirements could be big. The fact that you are going solo on your first major project which has regulatory scope at all (HIPPA, PCI, Sarbanes-Oxley...) isn't usually recommended. Most DBs have a method for backing up schema only; such as for postgres (SQL server I expect has something similar):
pg_dump -s -d mydb > mydb.sql This can then be recreated on another box with
createdb ... && pg_restore ...
DO NOT take in scope data home!
From here you can get stakeholders to help you generate test data. This would also be a great time to have them help you understand corner-cases. Having a corner-case conversation is annoying but will save you a lot of time in the long run compared to running into them one by one on real data. That being said, I don't think anyone here would recommend a green dev working on anything in regulatory scope un-supervised.
EDIT: after a quick google it looks like it isn't as easy as I expected with SQL server. Check out this and this. From two minutes of reading the scrip generator option should be good enough though you may have to play with a third party option (its Microsoft, why would I think it would be as easy as open source :).
Again be careful, even if you dont pull data, the code you write will put liability on your head. And the liability for medical information fucking high.
> practice with the real data
For what it's worth... practice with real data isn't a thing.
You should take a run at https://www.hackerrank.com/. It's SQL questions range from easy to very tough. The queries are not particularly real, but only because real data is just so messy. The underlying query techniques needed to answer the questions are 100% transferable to real data.
Check out this guy for java tutorials. Once you know a bit of that, a quick google with tell you how to connect your newly created java interface with a SQLServer backend.
Do you have any experience programming?
This is a good course: https://www.coursera.org/course/db
There's also this learn by doing approach but it is (very) incomplete and possibly abandoned. Might be an easy way to get your feet wet.
I took the first iteration of it, and even as someone with a decade of experience, I still learned a lot of the more academic/formal theory behind relational DBs, denormalization, and features I just never bothered to poke at due to being stuck in MySQL. I felt really luke-warm about the XML database stuff, but ended up dealing with Tamino and eXist-db by pure coincidence 6 months after, so you never know. I've taken half a dozen coursera classes (this was one of the three pilot courses for what became coursera), and this is still my favorite.
MSSQL comes with SQL Reporting Servicea built in. You can really do a lot in a short amount of time with Report Builder and just leveraging the wizard.
If you want more dashboarding / visualization capabilities that are more interactive in nature, you could use Grafana.
If you can't install anything in your computer, try http://sqlzoo.net/ You can select your database engine via the drop down in the top right.
Also http://sqlfiddle.com/ If you're just looking for a Sandbox
You start off with SQL, plain and simple. Technically you'd call it SQL 92, but the standard itself is a) quite big and b) not implemented fully by any major RDBMS.
Nonetheless, SELECT Column FROM Table looks the same in every SQL variant out there. Once you get to the functions to do some more complex stuff, there will be differences, but rather minor (e.g. current date in SQL Server is GETDATE(), in MySQL and Postgres it'd be NOW(), and in Oracle CURRENT_DATE). Some functions will differ significantly (adding dates comes to mind), some will be missing (GROUP_CONCAT() in MySQL vs nothing in T-SQL). Window functions also differ by quite a lot.
Generally though, if you know one SQL variant learning what the equivalent function in another one is just a simple google query away, the query logic remains basically the same. Unless you plan venturing into the world of database development, using a procedural language like PL/SQL (Oracle) or T-SQL (SQL Server), you need not to worry about it. The most important thing about learning SQL is not in syntax, which is rather trivial, but the fact you're working with sets of data, which a lot of people have trouble with, especially those with background in procedural programming.
I recommend http://sqlzoo.net to get started, you get exercises you need to complete, quizzes, what not. It walks you through all basic SQL features.
Wait wait wait, before you buy anything, do SQLzoo. It's can be a little confusing, but if you can get through all the material, you got it. Six weeks is fine, you're good. Also, it might work out well if you get yourself a nice simple GUI for whatever provider you'll be using so you can easily practice queries.
I'd also like to second the idea that the hardest part of learning SQL is understanding how to structure a relational DB. What helped me most was doing a ton of reading about concepts and using visual diagramming tools so I could easily conceptualize relationships.
Good Luck!
Here are a few resources I like:
- SQLZoo. A series of exercises to learn SQL, starting with SELECT and ending with JOIN. This holds your hand.
- PostgreSQL Exercises. It has the tables set up and asks you to retrieve data. You need to look through the docs to figure out how to do it. Less handholding than SQLZoo, but it will give you the answers if you get stuck.
- If you like learning from books, Head First SQL is what I started with, and I thought it was great.
Hope that helps.
What environment do you have access to? There are enough small differences between SQL variants for this to influence / restrict your decision regarding tutorials. Unless there's a reason to switch, you're probably best of sticking with whatever platform they use at your job.
- SqlZoo has already been mentioned - Link
- I've not looked at W3Schools SQL tutorials., but I like their web stuff since they assume you know absolutely bugger all. Link
Otherwise go for one specialising in your platform. The fundamentals of SQL are very simple - deceptively so in fact. If you're looking to fully grok SQL then it might be worth starting a little database project of your own - e.g. to catalogue your DVD collection or something. Most SQL books will have a sample database to work through and of course you should work through that first, but you may find it only clicks when you're working with information you properly understand/own. The sample databases which come with some SQL platforms (e.g. Microsoft's AdventureWorks) are a bit too complex / unwieldy for a beginner to grasp.
Everyone learns in their own way of course, but personally, the most I learnt was from the monumental fuck ups I managed to achieve on my own.
Final advice: Stay clear of MS Access if you want to understand SQL.
Get a good editor. I used SQL Navigator back in the day, but I think more people use Toad. I haven't done PL/SQL for several years now, but I actually miss it. We migrated all of our database over to SQL Server. And while I like SQL Server as well, there are things I miss in PL/SQL. Watch some quick tutorials on Youtube before you hit your class. While PL/SQL and TSQL are similar, language differences aren't the only thing you'll see. The structure is different as well (PL/SQL has a header and body, among other things). And if you get stuck, just search Google "SELECT TOP TSQL vs. Oracle", for example to see the differences. Now I'm missing my Oracle days. I feel so.. conflicted.
Depending on how large your dataset is, you may want to move the rating records into a temp table rather than performing a subquery. Try it out and see if the performance is any better.
Also, below is a link to an awesome visual diagram explaining different join types.
http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
Usually SSMS, but for some work it is nice to have SquirrelSQL around. For example to create insert statements based on result sets, browsing table data sets very quickly, etc.
> If I were doing MS I'd probably download the 180 day trial of MS SQL server and run that inside a virtual environment. https://www.microsoft.com/en-us/sql-server/sql-server-downloads
SQL Server offers the development version which is essentially enterprise for free. All you need is to download it and not use it for production. They have also updated their adventureworks database to world wide importers.
http://RegEx101.com is a great site to use to explain what's going on with a RegEx. https://regex101.com/r/SJZ81Y/1
^ asserts position at start of the string Non-capturing group (?:[^\/]\/){7} {7} Quantifier — Matches exactly 7 times Match a single character not present in the list below [^\/] * Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy) \/ matches the character / literally (case sensitive) \/ matches the character / literally (case sensitive) 1st Capturing Group ([^\/]+) Match a single character not present in the list below [^\/]+ + Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy) \/ matches the character / literally (case sensitive) .* matches any character (except for line terminators) * Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy) $ asserts position at the end of the string, or before the line terminator right at the end of the string (if any)
You can do relatively simple statistical analysis in SQL. It's probably more common to store the data in a SQL database, and use SQL to select data for processing by statistical software like R.
I'm not sure if this is what you're after, but check out mysql workbench. You still need to know a fair amout of SQL CLI but mysqlworkbench will autocomplete some things such as tablenames and warn you if your syntax is incorrect: https://www.mysql.com/products/workbench/
It's definitely not comprehensive but I wanted to be sure you were aware of it.
Which DB are you using?
If it's MySQL, then http://www.heidisql.com/ is really great. Very easy to use, and doesn't get in your way like I've found many others to.
I'm also using it with Postgres, but it only partially supports it at this stage. It also support MS SQL, but I've never tried that.
5 million rows isn't a lot or rows and is fine with SQL Lite's limits. I'm an MS-SQL guy, but I know that with some clever use of indexes, you can get some real performance gains.
It may be an idea if your WHERE clause targets a substring of a specific field regularly (for example the first three chars of the servers name), then create an additional field containing only those substrings from the original field and then create an index on that new field should increase performance. Wildcard table scans are always be poor performers.
I don't know the performance constraints of SQL Lite, but it may be worth spending some time seeing if the same DB performs better in another DBMS if it's really bogging down for you.
Finding a Wednesday is pretty easy... use extract(dow from DT) where DT is your date column. extract(dow...) returns an integer between 0 (Sunday) and 6 (Saturday), so you're looking for where that returns 3.
"in the last 60 days" is just current_date - DT <= 60.
"last Wednesday" depends on exactly what you mean. If today is Thursday, is yesterday "last Wednesday", or is 8 days ago? I'd probably use extract again, selecting where extract(week from DT) = extract(week from current_date) or throwing on a minus 1 at the end of that for last week.
It's the same question as asking "What's the difference between Oracle and SQL?"
SQL is a language. PostgreSQL is an implementation of that language. PostgreSQL implements the ANSI-SQL:2008 standard and also has it's own procedural language PL/pgSQL which is very similar to Oracle's PL/SQL.
For more information on PostgreSQL see http://www.postgresql.org/about/
That's a very implementation specific problem.
For example, I have a hard time imaging anything around today will still be around when Postgres timestamps would run into an issue: http://www.postgresql.org/docs/9.1/static/datatype-datetime.html
I just started doing some self study courses (a couple weeks in) and I've been using SQLiteStudio, which allows you to Read/Create SQLite tables. (https://sqlitestudio.pl/) but if you want something more robust I've also been using DBeaver (https://dbeaver.io/). It can access a whole host of different RDBMS.
> How advanced of stats do I need to know? I took ... a stats 101 course.
Heh, certainly more than an intro course. That's all I took and I got by but this was a decade ago and things like R or "big data" really weren't common things. But being able to tease out what metrics or numbers they are looking for and correcting any misunderstandings (here's why you can't average a series of averages, no you can't sum the dollar amount of each monthly balance to get one big number, et al.) the client has about the calculation and interpretation of the numbers you provide.
A data analyst should also focus on learning the domain/business processes in and out and being able translate that to efficient code. Learning the domain will help you understand what questions they're attempting to answer based on the number they ask for. Knowing how to write performant code that is easily translatable to enable future updates and modifications less painless and quicker. Knowing how to write performant code requires a knowledge of storage structures and execution plans. A more advanced data analyst with things like "senior" in front of their name should start to become aware of processes that flow across objects like functions and stored procedures and will seek to identify a pattern that will help in building out the predictability of the code base or framework they look to build.
Presentation of the data. Do yourself a favor and go read Edward Tufte's classic The Visual Display of Quantitative Information. Learn when to present which information in which format or style for maximum effectiveness.
Under the Cons for Redshift -
> It could also get very expensive considering the fact that Amazon bills you for storage space as well as server requirements (CPU, RAM etc.).
Not sure what this is supposed to mean, since it seems to imply that billing is separate between compute and storage, but all current node types in Redshift include both. Granted it is pretty costly, but the wording here is sort of odd (unless I'm missing something here).
& is an illegal character in XML
Check this thread:
https://stackoverflow.com/questions/730133/invalid-characters-in-xml
and this one
Hi
You can absolutely go the c# route. You really only need to write a small service project to read the comport and spit out raw text files.
Edit: IIRC you can even do this in a simple shell script. Edit2: a link http://superuser.com/questions/129447/how-can-i-pipe-the-output-of-the-serial-port-to-a-file-in-windows
You can of course use basic SQL objects to connect and make basic inserts.
Text can be read into SQL server and further processed from there.
You can decode the strings using native t-sql string functions, the BCP format writer, or my personal favourite using c# CLR stored procedures and table functions (sounds hard but see their default example script in the MSDN page for CLR). It is really easy to get powerful string processing this way, benefiting from c# string libraries with nice functions like regex.
So this is to make you aware of an alternative which is heavier in the SQL implementation and lighter in the c#.
The other way around, there is Linq, Entity framework etc. They all make light work of interfacing with SQL Server.
oh, there's a postgres extension which does what you can already do with a string to no actual benefit, which does a terrible job compared to closure tables
i didn't know that, because i've been using closure tables since long before this completely unnecessary extension was created
i appear to be in error: postgres has created an entirely unnecessary and counterproductive datatype to encode a fundamentally deeply bad approach into their database directly
why does this even exist? what benefit does it give you over a string? is this just a place to stuff operators that would be useful on strings but they decided to hard-limit to an unnecessary niche datatype?
it looks like they made a whole datatype for trees-as-strings and still didn't give it referential integrity 🤣
i openly admit: i don't get what this is for, at all
Even though I agree with you concerning the NATURAL JOIN, it should be explained why using it is "begging for trouble". :)
See for example here: https://stackoverflow.com/questions/2707950/is-using-natural-join-or-implicit-column-names-not-a-good-practice-when-writing
You're in a for a somewhat fun ride; on 'paper' it's simple, but there is a lot of gotchas.
Method 1: Generic Solution
- Switch the Wordpress DB/PHP config to use MSSQL Libraries
- Go through all the files that contain SQL Queries and verify they are SQL/T-SQL compliant.
Method 2: Useful for Read-Only Solutions
- Create another instance of MySQL that does regular pulls from the SQL Server instance.
Use MERGE
Inserting, Updating, and Deleting Data by Using MERGE
http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
MERGE (Transact-SQL)
http://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
Postgres runs easily on very low-end hardware, why don't you just install it on your computer?
ElephantSQL has a free plan which is limited to 20MB. And Heroku also has a free plan.
actually, you can take that exact course right now on coursera.org, which is what im doing. jennifer is a great instructor and im learning very efficiently. i made a reddit post about this the other day over in r/learnprogramming. heres that post:
http://www.reddit.com/r/learnprogramming/comments/25seee/best_free_online_resource_to_learn_sql/
and heres the link to the current stanford class on coursera (its self-study with no start or end date):
https://www.coursera.org/course/db
SQL is easy and the most important thing is to understand the logic and process of how databases work than to know the actual SQL language/syntax... the latter is much easier than the former. good luck!
Well, that seems rather arbitrary. After is even more arbitrary. Do you have another timestamp to corroborate this one?
What I find annoying about SQL content online is that it tries to pretend its a programming language, which it kind of is but definitely isn't. For any basic Excel user SQL is completely intuitive but the content tries to get you bogged down on all this bullshit terminology that really is irrelevant. Joins are a great example here... again, to anyone who knows what a vlookup is they're stupid simple, so as soon as you show someone how to use them like a vlookup (which is usually in the form of a left or inner) then it's super easy to show them the Venn that covers all the different options that you can't do with Excel. The response should be, "wow that's awesome," not, "what the fuck are you talking about?"
You might want to provide sample queries where you join a few tables all together and then demonstrate how you can modify the template to produce hugely different results based on the select/where. I mean get them using it and then tell what the word "schema" means. IMO SQLFiddle is a poor tool but if you're going to use it them just tell them "schema" is a complicated word for "format" or "structure" and to ignore it. Give them specific example how to import the schema, but don't bother explaining what it is because at this level they don't care. On another level they know what schema is but bringing it up breaks the simple narrative that you're trying to accomplish which is very simply: hey dumbasses, you can use this select/from/where combination to get data way faster than using Excel, and all the things you can do in Excel you can do here just as simply for the most part... and PS, you're supposed to use this to cut up the data first and then export it to Excel to visualize. Once that core concept is ingrained they "get it" and you can explain all sorts of advanced concepts.
you can't just say where not in <tablename>
IN requires an actual list so either a subquery; which your SQL version doesn't support and therefore should be replaced by something else. If not you are going to have a bad time any time you need to do anything other than a generic select.
If you insist on continuing with this insanity though, learn to use JOINS
http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
Procedures are reusable bits of code that can take an input and can also provide output based on the input. Simple example is mathematical procedures... calculating discounts, special pricing, etc... for examples let's pretend you need a function to square a number. So you write a procedure that takes an INput (x) and provides an OUTput (x * x).
No idea what flavor of SQL you're talking about... so I'll just assume Oracle PL\SQL:
PROCEDURE squareNum(x IN OUT number) IS BEGIN x := x * x; END;
Now that you have your procedure, you can use it in your code. Declaring a variable a and calling squareNum(a) to run the procedure on the number a and return the output.
DECLARE a number;
BEGIN a:= 23; squareNum(a); dbms_output.put_line(' Square of (23): ' || a); END; /
http://www.tutorialspoint.com/plsql/plsql_procedures.htm
If you've ever done functions in Algebra in school or any programming class, it's the same concept.
Enjoy your SQL injection hacks!
This problem, as well as the security vulnerability, would be prevented if you used prepared statements instead of string concatenation.
Welcome. If you want to explore just download SSDT for whichever version of SQL server you use. You can do the development right on your PC and you don’t have to deploy the package out, you can just run it on your machine.
SSDT comes with a shell version of Visual Studio, and you don’t need a VS license.
https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
use plural name for tables, like licenses †
use specific names for columns, like license_id
don't prefix all column names with license_, just the primary key and foreign key columns
for cross-database brand compatibility, use snake_case for names
† Joe Celko, who helps write the SQL standard, suggests using plurals, so you should too https://www.safaribooksonline.com/library/view/joe-celkos-sql/9780128007617/B9780128007617000012.xhtml
PostgreSQL is far more well-engineered, full-featured, and standards-compliant. For years, MySQL was terribly engineered. It would silently delete data is certain situations. My favorite MySQL gotcha is that if you issue a GRANT statement with a typo in the user's name instead giving a "user does not exist" error it simply creates a new user with the misspelled name.
I mean, it was pure shit. The only saving grace is that it was super-easy to get up and running, so a whole generation of web technologies in the early 2000s defaulted to using it (i.e. Wordpress).
The current technical story with MySQL is a little better, but it is backed by Oracle. So you still have to beware.
This comparison is a decent one: https://hackr.io/blog/postgresql-vs-mysql
https://www.sololearn.com/Course/SQL/
​
Enjoy - Had everything i needed to start a new job, knew nothing before this course and taught me the required fundamentals
https://www.virtualbox.org/wiki/VirtualBox
Get VirtualBox, which is a Virtual Machine from which you can install another OS, like windows (completely isolated from your computer)......it's a program that runs a virtual computer system
Step 1. Rob a bank
Step 2. Buy aqua studio with the money you stole
http://www.aquafold.com/aquadatastudio.html
Step 3. write/execute every kind of SQL anywhere on any kind of OS
Step 4. profit
SQLZOO has some good practice problems that I think were helpful when I was brushing up on SQL. Looking at your list of things you know, though, I'd say the next logical step would be to learn aggregate functions (notably SUM), and the related GROUP BY and HAVING clauses. As a data analyst, my boss will randomly ask me, "What is the cost impact of _____" several times a day and literally all of those questions are answered by aggregate functions (and 99% of the time it is SUM).
w3schools should really just be used like a dictionary in that you dont use it to learn how to speak English.
I used http://sqlzoo.net/ a few years ago and I picked up enough to be dangerous in an interview (which I landed).
You're welcome man. Depending on your level, these reading websites can give you just as much, if not more, than what these books have to offer. To me I learn a lot better with actual examples and reading books only help me with high level "best practices". To actually learn SQL you need to do a lot of SQL. The reason I prefer sites like these is they actually give you true life examples.
The first question I really have for you is, what are you going to be doing that you need to learn SQL? If you're doing a helpdesk job that needs some sql knowledge then maybe you should focus on installing, resolving issues as detached databases, recovering data, etc. Doing stuff like this you only need a really basic level of SQL knowledge but a better understanding of the administration. If you're wanting to be a BA then you can learn basic SQL queries then supplement excel to really deliver good reports.
If you can give me a general idea of what you're doing then I can point in you in a general direction. I listed two sites below to give you a better into to SQL explaining select statements.
Oracle Data Modeller can reverse engineer and do this. It's free and although written by Oracle, does work on other RDBMS platforms (SQL Server, MySQL, etc).
Looks like dbeaver is using jdbc csv driver which is using the H2 engine
> H2 database as an SQL engine and supports the most part of ANSI/ISO SQL grammar like SELECT, INSERT, UPDATE, DELETE and CREATE statements.
Oh, my apologies. I use MS SQL not MySql.
It looks like MySql uses something called CrossTab instead of PIVOT. Here's an article I found on it.
http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
I don't know much about MySql though. Sorry
This article proposes several ways of doing this.
http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server
I caution against doing it in SQL because of RBAR (row by agonizing row). Instead, if there is a presentation layer, I would do this calculation here.
Very rarely in my experience does a Running Total have any real value because the ORDER of the data becomes very important. Rather, it is often the case that the end-user wants a Group Total for some subgrouping.
Could you provide some background on what your project is attempting to accomplish? That would help with a database selection.
SQLLite is a great place to start. It's very light weight, is mostly SQL standard compliant, and is easy to include with your project if you are going to distribute it with others.
For a more robust, but server/site specific, I'd recommend PostgreSQL. It's very feature rich, relatively easy to install on any platform (Windows, Mac, Linux), and open source so it's free to download and use.
Given that you are using VS Express, I'm assuming you'll either be writing C# or VB.NET projects. I found a CodeProject walk through around using PostgreSQL within a C# application.
http://www.codeproject.com/Articles/30989/Using-PostgreSQL-in-your-C-NET-application-An-intr
If you're getting JSON, I'd encourage looking at ways of using the data as-is. As deewd mentioned, you're going to either need a table to cache the data, or use a temp table to hold the data for your calculations. Depending on the size of the data retrieved, there could be a lot of overhead.
Especially in automating this, it seems like you'd want to skip over a SQL dump all together.
Highcharts is a good example of a tool you can use to visualize data directly from JSON. Almost all of the highcharts examples are using json responses, and they just need a bit of javascript and html knowledge to put together. There is a wide variety of js based charting tools that would readily take JSON data and put it into a table or chart, while also doing averages, sums, and other operations.
Looking at the elements view on Chrome it looks like some flavor of Highcharts. Lots of div tags referencing the name and their site looks to have an example chart similar to the netmarketshare.com one.
Checkout SQL Server Machine Learning Service
ML obviously has a pipeline, and the data is usually stored in a SQL server. Python and R are great for ML and forecasting/modeling.
Also check out Jupyter Notebooks. Azure Data Studio (effectively VS Code but an RDMS) has that built in as well with really cool pipelines you can build out, and your choice of language.
stratascratch.com They have datasets pre-loaded with questions and answers you can practice with. They source their questions from technical interviews from companies so I found it helpful to use for interview practice. Otherwise, Datacamp.com is cool for very specific niches.
I spend 90% of my day in SSMS, but when I need to do a quick CSV file import or run something against another DBMS, I just use Navicat's tools. They're not as good as SSMS, of course, but the cross-DB option and import tools are handy to have around.
Link: http://www.navicat.com/products/navicat-premium
I love Jetbrain's PHPStorm IDE, but I don't know that I'll be drinking the cool-aid with their SQL IDE. And yeah, the fact that it's written in Java does kinda suck.
So, that is a weird issue.. Sometimes, you could have broken permissions somewhere or it could be trying to access another file that it doesn't have permissions to for whatever reason.
Is your pc connected to a domain, is it a possibility that you installed it as a local user and logged in as a domain user? To check this you can always type whoami in cmd.
If you didn't change the default location of the database and log drives they should be located in or around : C:\Program Files\Microsoft SQL Server\MSSQLxx.MyInstance\ (Might say something other than Microsoft SQL Server, and the xx in MSSQL"xx" will be different depending on your installed version. If you didn't change the instance name that should be called something along the lines of SQLExpress from what I've seen on servers in the past. Have you tried event viewer? - That may give you more insight into what is going on.
So you should be okay reinstalling SSMS, it is just the program to access SQL. They are completely separate instances. You could also just download SSMS by itself and try the new version. After SQLServer 2016, they don't even include it in the installation anymore you gotta download it. I believe its 17.x.x at the moment.
I use it on most of my server and it has worked flawlessly.
edit: a bunch of stuff.
Start with SQL Server Express it's a free version of SQL Server that (assuming you have a fairly new laptop or desktop) you can install and run locally. You'll also need SQL Server Management Studio to manage and query the database.
Let us know if you have any questions! Good Luck!
PS: Join us over at /r/SQLServer/ for more specific stuff pertaining to Microsoft SQL Server.
I'm guessing you installed SSMS which is the application you need to manage SQL Server. You still have to install the actual database engine. SQL Server Developer edition is free to use on the condition it's not used for a production workload.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
And the installation guide to explain which components to install: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server?view=sql-server-ver15
--Welcome to the club.
I work mostly with SQL Server, so some of this comes from that perspective.
Download SQL Server Developer Edition and use the Stack Overflow Database to continue learning after you leave your current job.
As for "how much do I need to know", that depends on what you're trying to claim you know and what job you're trying to apply for. We are currently doing interviews for developers and if someone says they know SQL, the first questions around that are to try to figure out A) whether or not they've actually used it, and B) how much they actually know.
Having a good understanding of the various types of joins is important. Understanding what a cartesian product is can be beneficial. Do you know aggregates? How about subqueries and CTEs?
Do you have a local SQL User Group? If so, start going to meetings. See if there's a SQL Saturday happening anywhere accessible to you in the near future - it's a great way to get a day of free training on SQL Server.