Introduction to Databases and SQL

Welcome to Unit 6. In this unit, you'll learn how your app can store data in a database locally on the device. Say you've been spending too much money, and want to make a personal budget app that gives you a budget and a place where you can log your expenses in it every time you buy something.

You may initially think to create a list as a variable in your app, and add each new transaction to it, but the data and variables isn't saved, or isn't persisted if your app gets killed. When the app restarts, the list of transactions will be empty and all of your transaction data will be lost. You may want to use a web server to store your data.

However, you won't be able to access your data if the device is offline or has a flaky internet connection. In this unit, you'll learn how to save user data locally on the device so it persists and can still be retrieved later even if the process running your app gets killed. This concept is called data persistence.

Saving data as a file on the device is one form of data persistence, but a better way to save structured data in your app is to use a database. Structured data means that there are predefined properties of each table. You can't change the predefined type and properties of the data.

Storing this data in a database makes it easier to access, modify, and search than a file, for example. You will learn the basics of interacting with a database in this video. A database is a structured collection of data.

You can store basic Kotlin types in a database. In this video, we will be storing strings and IDs. On Android, we use SQL Lite databases to store structured information within our app.

SQL Lite databases are made up of tables consisting of rows and columns. The rows and columns in a table are similar to those of a spreadsheet. Let's look at an example of a table in a SQL Lite database.

The following table is a collection of the national parks in the state of California in the United States. The table name should be descriptive of its contents, so it's called Park. Notice that, despite it containing multiple parks, the table's name is the singular form, Park, which is the SQL Lite convention.

Columns and tables are uniquely-named properties of data. In the case of our Park table, the columns are properties of national parks including ID, name, city, and acreage. Acreage is the size of land in acres.

In each table, there must be one column that is referred to as the primary key. The primary key must be unique to each row in the table, and in this example, the ID is the primary key. Rows are individual data entries.

In this case, the rows are individual national parks To communicate with the SQL Lite database, we use something called SQL which stands for Structured Query Language. SQL is how you read and manipulate data in a relational database. SQL is not a full programming language, but you can create statements made up of clauses that you use to read, filter, insert, and delete data.

In this video, we will cover the basic syntax of multiple SQL statements. The first statement we will cover is the Select statement that is used for reading rows from tables. Here's the format for a simple Select statement.

In this select statement, you just need to specify which column you want to read, and the table you want to read it from. A select statement starts with the SELECT keyword which means you want to read data. Note that it's a convention to capitalize the keywords in SQL.

Next, you specify the column names you want to read. Then, you add the FROM keyword. Then, you add the name of the table.

Here is the result of SELECT name FROM Park query. If you want to select more than one column, you specify them with commas in between columns. This selects the data from the specified columns from a specified table.

This statement shows the ID and name columns from the Park table. If you want to select all columns from a table, you can use a shorthand syntax and use an asterisk. This selects all columns from a table.

Let's learn about what else you can do with Select statements. If you are visiting San Francisco and wanted to know which Parks were there. You can manually go through the table row by row and check which parks are in the city of San Francisco.

However, with SQL, there is a more intuitive way to achieve this: using WHERE clauses. A WHERE clause lets you filter results based on one or more columns. This query will return only the parks where the city is San Francisco.

If you wanted to visit a park in San Francisco that is big enough, let's say, over 30 acres, you could use the WHERE clause in conjunction with an AND operator to filter for parks that are in San Francisco and also over 30 Acres. This narrows our result down to Golden Gate Park. If you're going to visit multiple cities, you may want to look to see which parks are in San Francisco or San Diego.

You can use the WHERE clause in conjunction with the OR operator to do this. This gives us all the parks in San Francisco and all in San Diego. If you already went to San Francisco and wanted to figure out which park to go to next.

You would want a list of parks not including San Francisco parks. You can use a WHERE clause with the NOT operator to do this. This gives us a list of all parks where the city is not San Francisco.

You can also sort data by a specific column using the ORDER BY clause. This statement orders the park data by acreage. By default, the results will be displayed in ascending order.

However, if you want them in descending order, you can add the DESC keyword after the column name. Now, you can see the order is based on acreage in descending order. You may not want to retrieve every single Row from the database and, instead, only retrieve a specific number of rows.

You can add a LIMIT clause and specify the maximum number of rows to return. Here's the park table with only five parks. So far, we've only covered reading data with SQL.

Now, let's go into modifying data in tables starting with insertion. Insert statements insert data into a table. This is an Insert statement that inserts the Lassen Volcanic park into the park table.

Let's break it down. You start with the INSERT keyword followed by an INTO keyword. You followed that by the name of the table you want to insert the data into.

Then add the VALUES keyword with the values you want to insert. The values must match the order in which the columns are defined for the table. When you re-query the table, you can see that the new entry has been added.

You've just added data using an Insert statement. But what do you do if you entered it incorrectly, or if data changes. This is when you can use the UPDATE keyword to update the contents of a table.

Here's an Update statement that updates the acreage at Lassen Volcanic park to a hundred thousand acres. Let's break it down. You start with the UPDATE keyword followed by the table name.

In this SET clause, you can set each column you want to change to its new value. You can update one, multiple, or all fields at once with an Update statement. Lastly, add a WHERE clause where you can specify what rows should be affected by this change.

Note that this will affect any row that matches the criteria meaning it could affect multiple rows. When you re-query the table, you can see that the row with Lassen Volcanic has been updated to having a hundred thousand acres. Next, let's discuss how you delete data from the table.

Here is a delete statement that deletes Lassen Volcanic Park from the table. Let's go through it step by step. You start with the delete FROM clause with the table you want to delete from followed by the WHERE clause where you can specify the criteria for what you want to delete, which could be multiple entries.

Now, you can see that Lassen Volcanic has been deleted. We just learned a lot. We learned that SQL Lite databases are made up of tables consisting of rows and columns, how to write SQL statements including select, insert, update, delete.

How to filter SQL statements using the WHERE clause and the AND, NOT, OR operators and more. Hopefully, this video gave you an overview and understanding on SQL Lite databases and how to read and manipulate data in them using SQL. In this unit, you'll practice your SQL skills and apply that knowledge to create apps that use SQL Lite databases Happy coding.