Module 4 - Linux and SQL (4) - Google Cybersecurity Certificate

Syntax

The rules that determine what is correctly structured in a computing language.

Accessing SQL from Linux

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.

Comparison Operators

< Less than > Greater to = Equal to <= Less than or equal to >= Greater than or equal to <> Not equal to 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: SELECT firstname, lastname, birthdate FROM employees WHERE birthdate > '1970-01-01'; 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';

Foreign Key

A column in a table that is a primary key in another table.

Primary Key

A column where every row has a unique entry.

SQL (Structured Query Language)

A programming language used to create, interact with, and request information from a database.

Log

A record of events that occur within an organization's systems.

Query

A request for data from a database table or a combination of tables.

Wildcards

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. '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

Relational Database

A structured database containing tables that are related to each other.

Operator

A symbol or keyword that represents an operation.

ORDER BY

An important keyword for organizing the data you extract from a table. ORDER BY sequences the records returned by a query based on a specified column or columns. This can be in either ascending or descending order. To use the ORDER BY keyword, write it at the end of the query and specify a column to base the sort on. In this example, SQL will return the customerid, city, and country columns from the customers table, and the records will be sequenced by the city column: "SELECT customerid, city, country FROM customers ORDER BY city;" You can also use the ORDER BY with the DESC keyword to sort in descending order. "SELECT customerid, city, country FROM customers ORDER BY city DESC;"

BETWEEN

An operator that filters for numbers or dates within a range.

Database

An organized collection of information or data.

String Data

Data consisting of an ordered sequence of characters.

Numeric Data

Data consisting of numbers.

Date and Time Data

Data representing a date and or time

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. 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. Examples listed below: SELECT COUNT(firstname) FROM customers; SELECT COUNT(firstname) FROM customers WHERE country = 'USA';

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: "SELECT firstname, lastname, title, email FROM employees WHERE title = 'IT Staff';" 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.

SELECT

Indicates which columns to return. For example, you can return the customerid column from the Chinook database with "SELECT customerid" You can also select multiple columns by separating them with a comma. For example, if you want to return both the customerid and city columns, you should write "SELECT customerid, city". If you want to return all columns in a table, you can follow the SELECT keyword with an asterisk (*). The first line in the query will be "SELECT *".

FROM

Indicates which table to query. To use the FROM keyword, you should write it after the SELECT keyword, often on a new line, and follow it with the name of the table you're querying. If you want to return all columns from the customers table, you can write: "SELECT * FROM customers;" When you want to end the query here, you put a semicolon (;) at the end to tell SQL that this is the entire query. Note: Line breaks are not necessary in SQL queries, but are often used to make the query easier to understand. If you prefer, you can also write the previous query on one line as "SELECT * FROM customers;"

NOT

Negates a condition. For example, if a cybersecurity issue doesn't affect customers in the USA but might affect those in other countries, you can return all customers who are not in the USA. This would be more efficient than creating individual conditions for all of the other countries. To use the NOT operator for this task, write the following query and place NOT directly after WHERE: SELECT firstname, lastname, email, country FROM customers WHERE NOT country = 'USA';

AVG

Returns a single number that represents the average of the numerical data in a column.

COUNT

Returns a single number that represents the number rows retuned from your query.

SUM

Returns a single number that represents the sum of the numerical data in a column.

LEFT JOIN

Returns all of records of the first table, but only returns rows of the second table that match on a specified column. 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 JOIN

Returns all of the records of the second table, but only returns rows from the first table that match on a specified column. 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 JOIN

Returns all records from both tables. 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.

INNER JOIN

Returns rows matching on a specified column that exists in more than one table. 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; 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;

FIltering

Selecting data that match a certain condition.

AND

Specifies that both conditions must be met simultaneously. As an example, a cybersecurity concern might affect only those customer accounts that meet both the condition of being handled by a support representative with an ID of 5 and the condition of being located in the USA. To find the names and emails of those specific customers, you should place the two conditions on either side of the AND operator in the WHERE clause: SELECT firstname, lastname, email, country, supportrepid FROM customers WHERE supportrepid = 5 AND country = 'USA';

OR

Specifies that either condition can be met. For example, if you are responsible for finding all customers who are either in the USA or Canada so that you can communicate information about a security update, you can use an OR operator to find all the needed records. As the following query demonstrates, you should place the two conditions on either side of the OR operator in the WHERE clause: SELECT firstname, lastname, email, country FROM customers WHERE country = 'Canada' OR country = 'USA';

LIKE

Used with WHERE to search for a pattern in a column. To apply wildcards to the filter, you need to use the LIKE operator instead of an equals sign (=). 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: "SELECT lastname, firstname, title, email FROM employees WHERE title LIKE 'IT%'; " 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: SELECT firstname,lastname, state, country FROM customers WHERE state LIKE 'N_';

Sorting Based on Multiple Columns

You can also choose multiple columns to order by. For example, you might first choose the country and then the city column. SQL then sorts the output by country, and for rows with the same country, it sorts them based on city. You can run this to explore how SQL displays this: "SELECT customerid, city, country FROM customers ORDER BY country, city;"