In SQL, you can join three tables or more by adding another join after the first one.
You can also run nested joins by specifying one join as the join condition for another.
Continue readingGet the Underlying Columns of a View Based on its Result Set
One of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.
Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.
Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.
But what if you do want to check a view for its underlying tables and columns?
Continue readingAdd a Column to a Table in SQL
In SQL, you can use the ALTER TABLE statement to add one or more columns to an existing table.
To do this, use the ADD clause, followed by the column definition. Separate multiple columns with a comma.
Only specify ADD once (i.e. there’s no need to specify it for each column).
Override the Query Optimizer for your T-SQL Joins with FORCEPLAN
The SET FORCEPLAN statement overrides the logic used by the SQL Server query optimizer to process a T-SQL SELECT statement.
More specifically, when FORCEPLAN is set to ON, the query optimizer processes a join in the same order as the tables appear in the FROM clause of a query.
This also forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.
Continue readingRename a Field in a MongoDB Collection
In MongoDB, you can rename a field when updating documents in a collection.
To rename a field, call the $rename operator with the current name of the field and the new name. This renames the field in all matching documents that have a field with that name.
Import CSV Data as an Array in MongoDB using mongoimport
MongoDB’s import utility – mongoimport – introduced a new parameter that allows you to import CSV data as an array.
The --useArrayIndexFields parameter interprets natural numbers in fields as array indexes when importing CSV or TSV files.
2 Ways to Replace a Substring in MongoDB
MongoDB 4.4 introduced nine new aggregation pipeline operators, including two new operators for finding and replacing a substring.
The two new operators that allow you to find and replace a substring are the $replaceOne and $replaceAll operators.
About the GO Command in SQL Server
If you’ve used T-SQL you will probably be familiar with the GO command. This command is often placed at the end of a batch of statements.
Extract the Week Number from a Date in SQL Server (T-SQL)
You can use the T-SQL function DATEPART() to return the week number from a date in SQL Server.
By “week number” I mean the week’s number within the year of the specified date.
Continue readingReturn the ISO Week Number from a Date in SQL Server (T-SQL)
If you need to extract the ISO week number from a date in SQL Server, you can use the iso_week argument when calling the DATEPART() function. You can alternatively use the isowk or isoww arguments to do the same thing.
By “ISO week”, I’m referring to the ISO 8601 date and time standard.
Continue reading