# SQL and Databases # Welcome to week 4; SQL and Databases

---

# New Page # Introduction to databases

# SQL filtering versus Linux filtering Previously, you explored the Linux commands that allow you to filter for specific information contained within files or directories. And, more recently, you examined how SQL helps you efficiently filter for the information you need. In this reading, you'll explore differences between the two tools as they relate to filtering. You'll also learn that one way to access SQL is through the Linux command line. ## Accessing SQL There are many interfaces for accessing SQL and many different versions of SQL. One way to access SQL is through the Linux command line. To access SQL from Linux, you need to type in a command for the version of SQL that you want to use. For example, if you want to access SQLite, you can enter the command sqlite3 in the command line. After this, any commands typed in the command line will be directed to SQL instead of Linux commands. ## Differences between Linux and SQL filtering Although both Linux and SQL allow you to filter through data, there are some differences that affect which one you should choose. ### **Structure** SQL offers a lot more structure than Linux, which is more free-form and not as tidy. For example, if you wanted to access a log of employee log-in attempts, SQL would have each record separated into columns. Linux would print the data as a line of text without this organization. As a result, selecting a specific column to analyze would be easier and more efficient in SQL. In terms of structure, SQL provides results that are more easily readable and that can be adjusted more quickly than when using Linux. ### **Joining tables** Some security-related decisions require information from different tables. SQL allows the analyst to join multiple tables together when returning data. Linux doesn’t have that same functionality; it doesn’t allow data to be connected to other information on your computer. This is more restrictive for an analyst going through security logs. ### **Best uses** As a security analyst, it’s important to understand when you can use which tool. Although SQL has a more organized structure and allows you to join tables, this doesn’t mean that there aren’t situations that would require you to filter data in Linux. A lot of data used in cybersecurity will be stored in a database format that works with SQL. However, other logs might be in a format that is not compatible with SQL. For instance, if the data is stored in a text file, you cannot search through it with SQL. In those cases, it is useful to know how to filter in Linux. ## Key takeaways To work with SQL, you can access it from multiple different interfaces, such as the Linux command line. Both SQL and Linux allow you to filter for specific data, but SQL offers the advantages of structuring the data and allowing you to join data from multiple tables. # Adedayo: SQL in cybersecurity

# Basic queries

# Query a database Previously, you explored how SQL is an important tool in the world of cybersecurity and is essential when querying databases. You examined a few basic SQL queries and keywords used to extract needed information from a database. In this reading, you’ll review those basic SQL queries and learn a new keyword that will help you organize your output. You'll also learn about the Chinook database, which this course uses for queries in readings and quizzes. ## Basic SQL query There are two essential keywords in any SQL query: SELECT and FROM. You will use these keywords every time you want to query a SQL database. Using them together helps SQL identify what data you need from a database and the table you are returning it from. The video demonstrated this SQL query: SELECT employee\_id, device\_id FROM employees; In readings and quizzes, this course uses a sample database called the Chinook database to run queries. The Chinook database includes data that might be created at a digital media company. A security analyst employed by this company might need to query this data. For example, the database contains eleven tables, including an employees table, a customers table, and an invoices table. These tables include data such as names and addresses. As an example, you can run this query to return data from the customers table of the Chinook database: SELECT customerid, city, country FROM customers; ``` +------------+---------------------+----------------+ | CustomerId | City | Country | +------------+---------------------+----------------+ | 1 | São José dos Campos | Brazil | | 2 | Stuttgart | Germany | | 3 | Montréal | Canada | | 4 | Oslo | Norway | | 5 | Prague | Czech Republic | | 6 | Prague | Czech Republic | | 7 | Vienne | Austria | | 8 | Brussels | Belgium | | 9 | Copenhagen | Denmark | | 10 | São Paulo | Brazil | | 11 | São Paulo | Brazil | | 12 | Rio de Janeiro | Brazil | | 13 | Brasília | Brazil | | 14 | Edmonton | Canada | | 15 | Vancouver | Canada | | 16 | Mountain View | USA | | 17 | Redmond | USA | | 18 | New York | USA | | 19 | Cupertino | USA | | 20 | Mountain View | USA | | 21 | Reno | USA | | 22 | Orlando | USA | | 23 | Boston | USA | | 24 | Chicago | USA | | 25 | Madison | USA | +------------+---------------------+----------------+ (Output limit exceeded, 25 of 59 total rows shown) ```

**Standard SQL:**

For databases that support the ANSI SQL standard and have the `INFORMATION_SCHEMA` views available, you can use the following query:

```sql SELECT table_name, column_name FROM information_schema.columns; ```

you can append if you want to specify where

```sql WHERE table_schema = 'your_database_name'; ```

if you want **Database-specific Queries:** If you are working with a specific database system and the standard SQL approach doesn't work, you can try the following methods:

**MySQL/MariaDB:** ```mysql SELECT table_name, column_name FROM information_schema.columns; ```

**or**

```mysql SHOW TABLES; DESCRIBE table_name; ```

**PostgreSQL:**

```sql SELECT table_name, column_name FROM information_schema.columns; ```

**SQLite:**

```sqlite SELECT name AS table_name, sql AS column_definition FROM sqlite_master WHERE type = 'table'; ```

You would run this SQLite command when you want to list all the tables in your SQLite database along with their SQL schema.

SQLite keeps a system table, `sqlite_master`, where it stores metadata about the database. Each row of `sqlite_master` represents an object (table, index, etc.) in the database. The columns are:

- type: the type of the database object, such as 'table' or 'index'. - name: the name of the object. - tbl\_name: the name of the table to which the object is associated. For a table, it's the same as `name`. - rootpage: the page number in the database file where the root B-tree page for the object is stored. - sql: the SQL statement that created the object.

This command specifies `type = 'table'` in the `WHERE` clause, so it only selects tables, not other types of objects like indices. For each table, it selects the name (renamed as `table_name` for clarity) and the SQL statement that created the table (as `column_definition`). So this command is useful when you need to know the structure of all tables in your SQLite database, such as the table names and their corresponding column definitions. It's a handy tool for exploring a database when you don't have the schema in front of you or when you've inherited a database and need to understand its structure. # Basic filters on SQL queries

# The WHERE clause and basic operators Previously, you focused on how to refine your SQL queries by using the WHERE clause to filter results. In this reading, you’ll further explore how to use the WHERE clause, the LIKE operator and the percentage sign (%) wildcard. You’ll also be introduced to the underscore (\_), another wildcard that can help you filter queries. ## How filtering helps As a security analyst, you'll often be responsible for working with very large and complicated security logs. To find the information you need, you'll often need to use SQL to filter the logs. In a cybersecurity context, you might use filters to find the login attempts of a specific user or all login attempts made at the time of a security issue. As another example, you might filter to find the devices that are running a specific version of an application. ## WHERE To create a filter in SQL, you need to use the keyword WHERE. WHERE indicates the condition for a filter. If you needed to email employees with a title of IT Staff, you might use a query like the one in the following example. You can run this example to examine what it returns: --- ```sql SELECT firstname, lastname, title, email FROM employees WHERE title = 'IT Staff'; ``` --- ``` +-----------+----------+----------+------------------------+ | FirstName | LastName | Title | Email | +-----------+----------+----------+------------------------+ | Robert | King | IT Staff | robert@chinookcorp.com | | Laura | Callahan | IT Staff | laura@chinookcorp.com | +-----------+----------+----------+------------------------+ ``` --- Rather than returning all records in the employees table, this WHERE clause instructs SQL to return only those that contain 'IT Staff' in the title column. It uses the equals sign (=) operator to set this condition. **Note:** You should place the semicolon (;) where the query ends. When you add a filter to a basic query, the semicolon is after the filter. ## Filtering for patterns You can also filter based on a pattern. For example, you can identify entries that start or end with a certain character or characters. Filtering for a pattern requires incorporating two more elements into your WHERE clause: - a wildcard - the LIKE operator ### **Wildcards** A **wildcard** is a special character that can be substituted with any other character. Two of the most useful wildcards are the percentage sign (%) and the underscore (\_): - The percentage sign substitutes for any number of other characters. - The underscore symbol only substitutes for one other character. These wildcards can be placed after a string, before a string, or in both locations depending on the pattern you’re filtering for. The following table includes these wildcards applied to the string 'a' and examples of what each pattern would return.

Pattern	Results that could be returned
`'a%'`	`apple123, art, a`
`'a\_'`	`as, an, a7`
`'a\_\_'`	`ant, add, a1c`
`'%a'`	`pizza, Z6ra, a`
`'\_a'`	`ma, 1a, Ha`
`'%a%'`	`Again, back, a`
`'\_a\_'`	`Car, ban, ea7`

### **LIKE** To apply wildcards to the filter, you need to use the LIKE operator instead of an equals sign (=). LIKE is used with WHERE to search for a pattern in a column. For instance, if you want to email employees with a title of either 'IT Staff' or 'IT Manager', you can use LIKE operator combined with the % wildcard: --- ```sql SELECT lastname, firstname, title, email FROM employees WHERE title LIKE 'IT%'; ``` --- ``` +----------+-----------+------------+-------------------------+ | LastName | FirstName | Title | Email | +----------+-----------+------------+-------------------------+ | Mitchell | Michael | IT Manager | michael@chinookcorp.com | | King | Robert | IT Staff | robert@chinookcorp.com | | Callahan | Laura | IT Staff | laura@chinookcorp.com | +----------+-----------+------------+-------------------------+ ``` --- This query returns all records with values in the title column that start with the pattern of 'IT'. This means both 'IT Staff' and 'IT Manager' are returned. As another example, if you want to search through the invoices table to find all customers located in states with an abbreviation of 'NY', 'NV', 'NS' or 'NT', you can use the 'N\_' pattern on the state column: --- ```sql SELECT firstname,lastname, state, country FROM customers WHERE state LIKE 'N_'; ``` --- ``` +-----------+----------+-------+---------+ | FirstName | LastName | State | Country | +-----------+----------+-------+---------+ | Michelle | Brooks | NY | USA | | Kathy | Chase | NV | USA | | Martha | Silk | NS | Canada | | Ellie | Sullivan | NT | Canada | +-----------+----------+-------+---------+ ``` --- This returns all the records with state abbreviations that follow this pattern. ## Key takeaways Filters are important when refining what your query returns. WHERE is an essential keyword for adding a filter to your query. You can also filter for patterns by combining the LIKE operator with the percentage sign (%) and the underscore (\_) wildcards. # Filter dates and numbers

# Operators for filtering dates and numbers Previously, you examined operators like less than (<) or greater than (>) and explored how they can be used in filtering numeric and date and time data types. This reading summarizes what you learned and provides new examples of using operators in filters. ## Numbers, dates, and times in cybersecurity Security analysts work with more than just **string data**, or data consisting of an ordered sequence of characters. They also frequently work with **numeric data**, or data consisting of numbers. A few examples of numeric data that you might encounter in your work as a security analyst include: You'll also encounter **date and time data**, or data representing a date and/or time. As a first example, logs will generally timestamp every record. Other time and date data might include: ## Comparison operators In SQL, filtering numeric and date and time data often involves operators. You can use the following operators in your filters to make sure you return only the rows you need:

operator	use
`<`	less than
`>`	greater than
`=`	equal to
`<=`	less than or equal to
`>=`	greater than or equal to
`<>`	not equal to

**Note:** You can also use != as an alternative operator for not equal to. ### Incorporating operators into filters These comparison operators are used in the WHERE clause at the end of a query. The following query uses the > operator to filter the birthdate column. You can run this query to explore its output: --- ```sqlite SELECT firstname, lastname, birthdate FROM employees WHERE birthdate > '1970-01-01'; ``` --- ``` +-----------+----------+---------------------+ | FirstName | LastName | BirthDate | +-----------+----------+---------------------+ | Jane | Peacock | 1973-08-29 00:00:00 | | Michael | Mitchell | 1973-07-01 00:00:00 | | Robert | King | 1970-05-29 00:00:00 | +-----------+----------+---------------------+ ``` --- This query returns the first and last names of employees born after, but not on, '1970-01-01' (or January 1, 1970). If you were to use the >= operator instead, the results would also include results on exactly '1970-01-01'. In other words, the > operator is exclusive and the >= operator is inclusive. An **exclusive operator** is an operator that does not include the value of comparison. An **inclusive operator** is an operator that includes the value of comparison. ### **BETWEEN** Another operator used for numeric data as well as date and time data is the BETWEEN operator. BETWEEN filters for numbers or dates within a range. For example, if you want to find the first and last names of all employees hired between January 1, 2002 and January 1, 2003, you can use the BETWEEN operator as follows: --- ``` SELECT firstname, lastname, hiredate FROM employees WHERE hiredate BETWEEN '2002-01-01' AND '2003-01-01'; ``` --- ``` +-----------+----------+---------------------+ | FirstName | LastName | HireDate | +-----------+----------+---------------------+ | Andrew | Adams | 2002-08-14 00:00:00 | | Nancy | Edwards | 2002-05-01 00:00:00 | | Jane | Peacock | 2002-04-01 00:00:00 | +-----------+----------+---------------------+ ``` --- **Note:** The BETWEEN operator is inclusive. This means records with a hiredate of January 1, 2002 or January 1, 2003 are included in the results of the previous query. ## Key takeaways Operators are important when filtering numeric and date and time data. These include exclusive operators such as < and inclusive operators such as <=. The BETWEEN operator, another inclusive operator, helps you return the data you need within a range. # Filters with AND, OR, and NOT

# Types of joins

# Compare types of joins Previously, you explored SQL joins and how to use them to join data from multiple tables when these tables share a common column. You also examined how there are different types of joins, and each of them returns different rows from the tables being joined. In this reading, you'll review these concepts and more closely analyze the syntax needed for each type of join. ## Inner joins The first type of join that you might perform is an inner join. INNER JOIN returns rows matching on a specified column that exists in more than one table.

![Venn diagram with two circles labeled "left table" and "right table". The intersection is highlighted.](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/9y5ZKSySQTuS5RQ-MJLXrA_6b756cb30b9442c8ae576607a6ab3ff1_CS_R-080_Inner-joins.png?expiry=1689120000000&hmac=ztPJ29SOfpwVlt4cKmV6BcvIflzMNjIGbHMDxy37yOg)

It only returns the rows where there is a match, but like other types of joins, it returns all specified columns from all joined tables. For example, if the query joins two tables with SELECT \*, all columns in both of the tables are returned. **Note:** If a column exists in both of the tables, it is returned twice when SELECT \* is used. ### The syntax of an inner join To write a query using INNER JOIN, you can use the following syntax: SELECT \* FROM employees INNER JOIN machines ON employees.device\_id = machines.device\_id; SELECT thing1, thing2, thingX FROM table1 inner join table2 ON table1.commun\_colomn = table2.common\_colomn; You must specify the two tables to join by including the first or left table after FROM and the second or right table after INNER JOIN. After the name of the right table, use the ON keyword and the = operator to indicate the column you are joining the tables on. It's important that you specify both the table and column names in this portion of the join by placing a period (.) between the table and the column. In addition to selecting all columns, you can select only certain columns. For example, if you only want the join to return the username, operating\_system and device\_id columns, you can write this query: SELECT username, operating\_system, employees.device\_id FROM employees INNER JOIN machines ON employees.device\_id = machines.device\_id; it makes more sense for it to be all in one row for me so heres an explanation of each part how it worksSELECT thing\_1, thing\_2, thing\_X FROM table1 INNER JOIN table2 ON table1.common\_column = table2.common\_column; **Note**: In the example query, username and operating\_system only appear in one of the two tables, so they are written with just the column name. On the other hand, because device\_id appears in both tables, it's necessary to indicate which one to return by specifying both the table and column name (employees.device\_id). ## Outer joins Outer joins expand what is returned from a join. Each type of outer join returns all rows from either one table or both tables. ### Left joins When joining two tables, LEFT JOIN returns all the records of the first table, but only returns rows of the second table that match on a specified column.

![Venn diagram with two circles labeled "left table" and "right table". The left circle and the intersection are highlighted.](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/GsYCwSiOSMmymUqPUAQJ5w_5beed7e470c546fca088a83dfd9465f1_CS_R-080_Left-joins.png?expiry=1689120000000&hmac=HHSM7i9uwK-2Oq05zEf06ori3MPZ8G-R5W8fyug1QZU)

The syntax for using LEFT JOIN is demonstrated in the following query: SELECT \* FROM employees LEFT JOIN machines ON employees.device\_id = machines.device\_id; As with all joins, you should specify the first or left table as the table that comes after FROM and the second or right table as the table that comes after LEFT JOIN. In the example query, because employees is the left table, all of its records are returned. Only records that match on the device\_id column are returned from the right table, machines. ### Right joins When joining two tables, RIGHT JOIN returns all of the records of the second table, but only returns rows from the first table that match on a specified column.

![Venn diagram with two circles labeled "left table" and "right table". The right circle and the intersection are highlighted.](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/YHXRMOLiQheppUjthmM5yQ_cfb18a8315e34357bd1299f7eefafcf1_CS_R-080_Right-joins.png?expiry=1689120000000&hmac=vl4SnuhTha_RePhUQ-sb5pfEVYjfYrnCmJpUc14hKDY)

The following query demonstrates the syntax for RIGHT JOIN: SELECT \* FROM employees RIGHT JOIN machines ON employees.device\_id = machines.device\_id; RIGHT JOIN has the same syntax as LEFT JOIN, with the only difference being the keyword RIGHT JOIN instructs SQL to produce different output. The query returns all records from machines, which is the second or right table. Only matching records are returned from employees, which is the first or left table. **Note:** You can use LEFT JOIN and RIGHT JOIN and return the exact same results if you use the tables in reverse order. The following RIGHT JOIN query returns the exact same result as the LEFT JOIN query demonstrated in the previous section: SELECT \* FROM machines RIGHT JOIN employees ON employees.device\_id = machines.device\_id; All that you have to do is switch the order of the tables that appear before and after the keyword used for the join, and you will have swapped the left and right tables. ### Full outer joins FULL OUTER JOIN returns all records from both tables. You can think of it as a way of completely merging two tables.

![Venn diagram with two circles labeled "left table" and "right table". Both circles are highlighted.](https://d3c33hcgiwev3.cloudfront.net/imageAssetProxy.v1/oRzF__GaTqSGMmUqXKbSrQ_92db9841a00244c2aa214e60bb07f1f1_CS_R-080_FULL-OUTER-JOIN.png?expiry=1689120000000&hmac=_mQrCfMdNvPXplXuBFDsTIGUpqmxB-HicarJwCeLsUw)

You can review the syntax for using FULL OUTER JOIN in the following query: SELECT \* FROM employees FULL OUTER JOIN machines ON employees.device\_id = machines.device\_id; The results of a FULL OUTER JOIN query include all records from both tables. Similar to INNER JOIN, the order of tables does not change the results of the query. ## Key takeaways When working in SQL, there are multiple ways to join tables. All joins return the records that match on a specified column. INNER JOIN will return only these records. Outer joins also return all other records from one or both of the tables. LEFT JOIN returns all records from the first or left table, RIGHT JOIN returns all records from the second or right table, and FULL OUTER JOIN returns all records from both tables. # Continuous learning in SQL You've explored a lot about SQL, including applying filters to SQL queries and joining multiple tables together in a query. There's still more that you can do with SQL. This reading will explore an example of something new you can add to your SQL toolbox: aggregate functions. You'll then focus on how you can continue learning about this and other SQL topics on your own. ## Aggregate functions In SQL, **aggregate functions** are functions that perform a calculation over multiple data points and return the result of the calculation. The actual data is not returned. There are various aggregate functions that perform different calculations:

- COUNT returns a single number that represents the number of rows returned from your query. - AVG returns a single number that represents the average of the numerical data in a column. - SUM returns a single number that represents the sum of the numerical data in a column.

### **Aggregate function syntax** To use an aggregate function, place the keyword for it after the SELECT keyword, and then in parentheses, indicate the column you want to perform the calculation on. For example, when working with the customers table, you can use aggregate functions to summarize important information about the table. If you want to find out how many customers there are in total, you can use the COUNT function on any column, and SQL will return the total number of records, excluding NULL values. You can run this query and explore its output: --- ``` SELECT COUNT(firstname) FROM customers; ``` --- ``` +------------------+ | COUNT(firstname) | +------------------+ | 59 | +------------------+ ``` --- The result is a table with one column titled COUNT(firstname) and one row that indicates the count. If you want to find the number of customers from a specific country, you can add a filter to your query: --- ``` SELECT COUNT(firstname) FROM customers WHERE country = 'USA'; ``` --- ``` +------------------+ | COUNT(firstname) | +------------------+ | 13 | +------------------+ ``` --- With this filter, the count is lower because it only includes the records where the country column contains a value of 'USA'. There are a lot of other aggregate functions in SQL. The syntax of placing them after SELECT is exactly the same as the COUNT function. ## Continuing to learn SQL SQL is a widely used querying language, with many more keywords and applications. You can continue to learn more about aggregate functions and other aspects of using SQL on your own. Most importantly, approach new tasks with curiosity and a willingness to find new ways to apply SQL to your work as a security analyst. Identify the data results that you need and try to use SQL to obtain these results. Fortunately, SQL is one of the most important tools for working with databases and analyzing data, so you'll find a lot of support in trying to learn SQL online. First, try searching for the concepts you've already learned and practiced to find resources that have accurate easy-to-follow explanations. When you identify these resources, you can use them to extend your knowledge. Continuing your practical experience with SQL is also important. You can also search for new databases that allow you to perform SQL queries using what you've learned. ## Key takeaways Aggregate functions like COUNT, SUM, and AVG allow you to work with SQL in new ways. There are many other additional aspects of SQL that could be useful to you as an analyst. By continuing to explore SQL on your own, you can expand the ways you can apply SQL in a cybersecurity context. # Wrap-up; Glossary terms from week 4

### **Terms and definitions from Course 4, Week 4** **Database**: An organized collection of information or data **Date and time data:** Data representing a date and/or time **Exclusive operator**: An operator that does not include the value of comparison **Filtering:** Selecting data that match a certain condition **Foreign key:** A column in a table that is a primary key in another table **Inclusive operator:** An operator that includes the value of comparison **Log:** A record of events that occur within an organization's systems **Numeric data:** Data consisting of numbers **Operator:** A symbol or keyword that represents an operation **Primary key:** A column where every row has a unique entry **Query:** A request for data from a database table or a combination of tables **Relational database:** A structured database containing tables that are related to each other **String data**: Data consisting of an ordered sequence of characters **SQL (Structured Query Language):** A programming language used to create, interact with, and request information from a database **Syntax:** The rules that determine what is correctly structured in a computing language **Wildcard**: A special character that can be substituted with any other character