Is the native ArcGIS database capable of doing generalized data manipulation?

Question

I'm doing political analysis across redistricting cycles, and need to do a lot of data manipulation. I need to split up precincts, do updates, joins, calculations, etc... I'll need a method that is able to handle a table of 500k census blocks. I just bought the home version of ArcGIS.

Should I try to store the data and do the manipulation using the internal .gdb file? That seems the easiest, but I have no idea if it's possible to do SQL-like updates and manipulations. I haven't been able to find the answer in the docs.

I'll need to do:

update table set this = that
create table as (select * from table1, table2 where someclause)

If ArcGIS is not capable of this, what do most people do instead?

Answer 1

Instead of diving head in without any prior experience with GIS, you should really start to do some basic preparatory learning via tutorials, and familiarize yourself with the "GIS way" of doing things.

It seems you come from a more "database" / SQL type background, ArcGIS, and most other GISs actually hide some of the complexities of SQL. E.g. you generally do not write SQL to update some field, but use geoprocessing tools from the ModelBuilder toolbox instead. For a simple field update, you can use the Field Calculator.

For selections, you can use SQL, e.g. through a Select By Attributes or Definition Query, where you generally only supply the WHERE clause. For joins, use the Add Join tool, or the interface tools for that (right click a layer and choose Join and Relates).

I definitely recommend you to use File Geodatabase over the still ubiquitous shapefiles. Shapefiles have some serious limitations, that will bite you later one (e.g. max 2GB size, short max field names etc.).