Figure 1: DB Browser for SQL Lite
- When dealing with SQL, you will want a tool to write statements and send them to a database for results. Numerous programs grasp SQL, often embedding statements in code. Instead of diving into complex setups, let's use a hassle-free app available on Windows, Mac, and Linux — DB Browser for SQLite.
Figure 2: SQL Lite
-
Before diving into the language for chatting with databases, let's get what a database is.
-
At its core, it is a stash of info. Picture this: a list of folks, where they reside, and their go-to color.
-
Here, we have got three nuggets – a name, a city, and a hue. Now, in a database, these bits of info get sorted into columns, and each bunch of info lands in rows.
Figure 5: Database Explanation
- The blueprint for all this – how fields, tables, and links are set – that is the schema. Now, we will not get into making these setups in this course.
Figure 6: Card Representing an Individual
-
SQL, short for Structured Query Language, is a tool for handling and defining data in databases.
-
It came into play in 1974 and became a standard in 1986.
-
It offers a standardized way to interact with databases, asking specific questions or making structured queries that databases can understand.
Figure 8: Pronunciation of SQL
-
SQL simplifies this process, letting us pose questions that databases grasp.
-
No genius is required. SQL's become a powerhouse in data thinking, integrated into many database products, each with its quirks.
Figure 9: SQL Statements
-
A statement, the heart of SQL, is what you write to get info or tweak a database. It is made up of clauses, the building blocks.
-
Statements can do a lot: ask questions, modify data, or even create, tweak, or nix tables.
-
When it is tweaking existing tables, that is SQL as a DML (data manipulation language), working the CRUD operations (create, read, update, delete).
-
CRUD is your go-to term for database data gymnastics.
Figure 11: SQL Statements - Field and Table names
Figure 13: SELECT Statement
-
The fundamental way to fetch information in SQL is by using a select statement.
-
Just kick it off with the select keyword, indicating that we want the database to pick out specific information and hand it back to us.
-
You can even ask for non-database text by tossing in some quotes and a semicolon.
-
To understand the database content, you can click on "browse data" and choose a specific table, like 'people'.
-
Each field in the table can be used to request information.
-
So, to retrieve all the first names from the 'people' table, it is as simple as writing "SELECT first_name FROM people;".
Figure 15: Execute SQL
Figure 16: The Art Of Refining Our Queries
-
When we are craving specific info from the database, the WHERE keyword becomes our trusty sidekick.
-
It helps us set conditions within a SELECT statement. Let's say you want records only for Californians; toss in a WHERE clause with the condition 'state_code=CA'.
-
Beware of case sensitivity, though. 'CA' and 'ca' are different in the database. So, make sure your caps match.
Figure 18: Results of WHERE Statement
-
You can spice it up by exploring different fields. For instance, to find folks who opted for a shirt instead of a hat, switch your WHERE condition to 'shirt_or_hat=shirt'.
-
Remember, the order of your clauses matters. If you jumble them, the database gets confused. Keep practicing and use white space wisely.
Figure 19: Order of Clauses
Figure 20: Statement Criteria
-
Let's add a dash of logic to our queries! The AND logical operator becomes our buddy.
-
Want the names of Californians who wanted a shirt? Easy-peasy: "SELECT first_name, last_name FROM people WHERE state_code=CA AND shirt_or_hat=shirt.
-
" You can string multiple conditions with AND or even use OR to get results that meet one of two conditions.
-
Just remember those parentheses; they are your guiding light in complex queries.
-
Sometimes, we do not want an exact match; we want to be a bit fuzzy. Enter the LIKE operator.
-
It allows us to look for values that match part of a field. Need all states starting with 'C'? Instead of a long OR statement, try 'state_code LIKE 'C%''.
- When the database serves up data, it might be all over the place. Here is where ORDER BY comes to the rescue. Stick it at the end of your query and pick a field for sorting.
Figure 26: ASC or DESC
Figure 28: DISTINCT Function
Figure 29: Explore The Power Of SQL
-
Alright, let's dive into SQL data types. Now, in a database, fields are like containers holding specific kinds of data.
-
You've got names, dates, ID numbers, or even the points scored in a competition. Now, databases like us to declare upfront what kind of data each field will hold when we create a table.
-
This is where data types come into play.
Figure 31: SQL Data Types
-
SQL has a few categories to be aware of binary for ones and zeros, dates and times for temporal info, numbers for numeric values, and text for characters.
-
Now, within each of these, there are specific types tailored for different purposes. Remember, not every type is supported in every SQL implementation, so always check your documentation.
-
Now, let's talk SQL math. We've got various operators to play with – addition, subtraction, multiplication, division, and even modulo.
-
Stick to integers unless you want unexpected results. Logical comparison operators, like greater than, less than, and equal to, can help filter results.
Figure 32: Math in SQL
Databases also provide nifty math functions – check your software's documentation for the full list. Let's do a quick demo. If you want to add 4 and 2. Run that query, and bam, 6!
Figure 33: Division
Figure 35: ORDER BY
Flip the comparison to find those with scores of 70 or less. Now, let's dig into aggregate functions. Want the max and min quiz scores? Easy. Want the total points earned? Use the sum function.
Figure 36: GROUP BY
- Now, let's talk about transforming SQL data – because sometimes, you have to mold it to fit your needs. Ever want to change the case of a string? Well, there is LOWER for lowercase and UPPER for uppercase.
Figure 39: Lower and Upper Case
Now, for those times when you need data to wear a different hat, there is CAST.
Figure 42: CAST
- It lets you interpret one data type as another – useful when you cannot tweak the database schema. Oh, and keep in mind, sorting can behave differently based on how you treat data types.
- Alright, let's wrap up with a quick chat about aliases. You know, the names of the fields we get back in our queries – sometimes, they could use a makeover. If you want to give them a friendlier name, throw in the AS keyword.
- Like, check this out: instead of having UPPER(last_name) as a column header, let's call it 'surname'. And for first_name, how about 'firstname' without the underscore?
Figure 45: Adding Data to Tables
Carol's last name has been forgotten. SQL does not like that, it has to have some data.
- Alright, let's get into the groove of modifying data. We use the update keyword for this.
Figure 50: Modifying Table Data
Figure 52: Carlos Morrison
-
Alright, time to clean house. The delete keyword is your go-to for kicking records out of a table.
-
Like update, you gotta tell the database where to drop the bomb, and it is smart to throw in a condition to keep things targeted.
-
You do not wanna wipe out the entire guest list accidentally.
Figure 55: Removing Table Data
Figure 56: Delete From People
-
A widely used document database, MongoDB, is recognized for its robustness and user-friendliness.
-
The course aims to provide developers with essential MongoDB knowledge, including installation, database deployment setup, fundamentals like the document model, database structure, and MQL.
-
What is MongoDB?
-
In 2009, MongoDB gained popularity among web developers for its user-friendly approach, enabling them to work with data in a format consistent with their applications — documents.
-
Its native drivers seamlessly integrated data with code.
-
In this course, we will explore MongoDB's workings and distinctions from traditional relational databases.
-
At a broad level, databases organize data, and more intricate databases adhere to specific design and data modeling standards.
Figure 2: Relational (SQL)
Figure 4: Structured Query Language
Figure 5: Joining Related Data
MongoDB's flexible document store is a key feature, where data is stored in documents rather than tables.
Creating a document
-
Documents in MongoDB are essentially field-value pairs stored in BSON, a JSON-like format.
-
The process involves creating documents and saving them to a database.
-
While demonstrating this in the course using MongoDB shell on a Mac, it's emphasized that the same commands are applicable in Codespaces.
Figure 9: MongoDB JSON file
-
The process of inserting documents into the database is explained using the Mongo shell command.
-
JavaScript can be written in this shell, and the importance of connecting to the correct database is highlighted.
Figure 10: Creating a document
Figure 11: Formatting a document
-
The find command in MongoDB, similar to a SELECT statement in SQL, is fundamental for querying recipe documents.
-
It requires a query document as its first parameter; an empty query document retrieves all documents by default.
Figure 13: Using .find with parameters
- Numeric range searches, greater than or less than queries, and other advanced features will be explored in the next section.
-
MongoDB documents provide a versatile solution for storing diverse data in various formats, offering flexibility not commonly found in traditional relational databases.
-
While traditional databases might rely on text or number fields, MongoDB enables us to handle more complex structures, such as a recipe, without the need for extensive refactoring.
Figure 14: Entering Data into document
Figure 15: Using insert
Collections
-
The flexibility inherent in MongoDB's document model offers numerous advantages for developers, but maintaining organization becomes challenging over time.
-
Collections play a pivotal role in addressing this challenge.
Figure 18: Recipe - User - Blog Post
Figure 19: Collections and Databases
-
In our queries, we have been using collections consistently, like in db.recipes.find to retrieve documents.
-
Essential commands include show dbs to display all databases and show collections to list collections within the current database.
-
You can identify the current database using db.getName. Both databases and collections are created dynamically as you insert data.
Figure 20: Collections
Figure 22: Validating Collections
This comprehensive understanding of MongoDB collections sets the stage for more advanced topics later in the course. The upcoming challenge will allow us to apply our knowledge effectively.
MongoDB provides various operators to filter fields, such as strings, numbers, arrays, objects, or subdocuments. These operators are essential for conditionally filtering results in queries.
-
For instance, instead of greater than, use $gt, and for less than, use $lt. To illustrate, consider a scenario where we want to find recipes with a cook time of 30 minutes or less.
-
The query would be cook_time: { $lte: 30 } }. Additionally, combining conditions, like cook time and prep time, is done with a comma for an "and" query.
Figure 28: Using less than operator
-
Navigating object fields, such as ingredients, involves dot notation.
-
To find recipes with eggs, the query is: { "ingredients.name": "egg" }. However, avoid expecting an exact match when using dot notation, and remember to use it unless an exact match is intended.
-
For more operators, refer to the MongoDB documentation. Importantly, exploring different operators empowers developers to craft richer queries.
-
Now, transitioning to document updates and deletions, we will delve into those aspects in the next discussion.
-
In data management, changes are inevitable, whether due to schema adjustments, user profile updates, or corrections like fixing a spelling error in a recipe.
-
For this tutorial, we will utilize the "examples" collection to avoid altering our recipe data.
-
You can refer to the "examples.js" file for instructions on importing this collection.
Figure 31: Updating Documents
Figure 36: Eventual Consistency
-
In the context of a "likes_count" field in a recipe, the inc operator effectively increments the count, demonstrating atomic operations and avoiding inconsistencies in simultaneous likes.
-
It's a powerful technique for maintaining data accuracy.
Figure 37: Working with Arrays
-
Upon running find, observe the added ID 60. To remove it, replace push with pull in the update operation.
-
The document will be updated, and the ID will disappear from the likes array. Note that the added element could be of any valid type: integer, string, object, or even another array.
-
We have two primary deletion options: deleteOne and deleteMany. Both methods require a filter document, akin to the find command in update operations.
-
With deleteOne, the first matching document in the collection is deleted, while deleteMany removes all documents that match the specified filter.
Figure 40: Using deleteOne
- Users can assign query results to variables, as demonstrated by querying a recipe collection, formatting the output into a table, and leveraging JavaScript functionalities like console.table.
Figure 43: Exercise Image Pending
-
So far, we have focused on direct interactions with MongoDB, but let's now explore creating applications with it.
-
Fortunately, there are drivers and libraries for various languages and frameworks.
Figure 56: MongoDb & Python
-
In the Python shell, import PyMongo, set up the client using localhost and the default port, and define a variable, 'db,' assigned to the 'cooker' database.
-
Confirm the connection by checking 'db.name,' which should return 'cooker.
Figure 58: Sorting with Python
-
Additional commands like sorting and limiting can be demonstrated using '.sort()' and '.limit()'.
-
For instance, sort by title in ascending order and limit results to three documents.
- So far, we have been directly interacting with MongoDB, which is excellent, but what about building actual applications? There are official MongoDB drivers for various frameworks and libraries, and today, we will explore using Node.js.
Figure 59: Add MongoDB as a Dependency
Figure 62: Node Sorting
-
Moving on to other examples, we ran a find one command, and Node.js formatted the result nicely. Similarly, we can use the find command to list multiple recipes.
-
In example 4, we add a sort to order by title and limit to three documents.
- In MongoDB, we have the capability to store entire files, not just documents, through a feature called GridFS.
- This technology breaks down file contents into chunks, each stored in its own document, while a parent document holds information about the file.
- We have extensive flexibility in configuring MongoDB startup options through command-line settings, such as specifying data file location with '--dbpath.' However, MongoDB's configuration files provide a more convenient and secure way to manage these settings.
Figure 65: Config file options
Figure 67: Replica Sets
-
In this course, we have primarily utilized a standalone database for convenience in initial learning, but employing such a setup in a production environment is discouraged due to potential issues like server crashes or unavailability leading to downtime.
-
Directories have been created for each replica set member's data files and initiated MongoDB in three terminals, each running on a different port.
-
We have initialized the replica set by logging into the primary node, defining a configuration with member details, and executing rs.initiate().
-
In this course, we will not cover the entire process of setting up sharding, but it is crucial to grasp the fundamentals.
-
Sharding involves distributing or partitioning data across multiple servers, offering performance and infrastructure benefits compared to scaling up with larger servers.
Figure 74: Basic Sharding Setup
-
MongoS determines where data resides, retrieves it from the correct server, and merges it seamlessly, simplifying the process for the client.
-
Importantly, this happens transparently, requiring no special handling on the application side.
Figure 76: How is Your Data Sharded?
-
Securing databases is crucial, and while safeguarding them behind a firewall or employing network-based security measures is beneficial, it's imperative to implement authentication and authorization.
-
Authentication, akin to logging into an account, involves MongoDB usernames, passwords, or keys for access—a bit like having a badge for an office building.
-
Authorization, on the other hand, determines specific privileges even after authentication.
-
MongoDB uses role-based access control, assigning roles and privileges to users.
-
Backing up your databases is crucial, and testing your restore plans is equally important.
-
MongoDB provides various methods for backups, and we'll discuss two of them: copying data files and utilizing mongodump and mongorestore programs.
-
The first method involves copying data files, a relatively straightforward yet manual approach.
-
Navigate to the MongoDB DB path directory, where all the MongoDB files are located. Before proceeding with the backup, stop any write operations to the disk.
-
Log into MongoDB on the shell and execute the command 'DB.fsycnLock' to lock the database against writes temporarily.