SQL Terms

Like Operator

- It allows you to specify partial character strings in a "wildcard" sense. n SQL, the LIKE operator can be used in a WHERE clause to search for a pattern in a column. To accomplish this, you use something called a wildcard as a placeholder for some other values. There are two wildcards you can use with LIKE: The % wildcard will match zero, one, or many characters in text. For example, the following query matches companies like 'Data', 'DataC' 'DataCamp', 'DataMind', and so on: SELECT name FROM companies WHERE name LIKE 'Data%'; The _ wildcard will match a single character. For example, the following query matches companies like 'DataCamp', 'DataComp', and so on: SELECT name FROM companies WHERE name LIKE 'DataC_mp'; You can also use the NOT LIKE operator to find records that don't match the pattern you specify. Allows you to search for a pattern in a string

len function

LEN (text) - returns the length, in number of characters, of a block of text We can find the length of a text column (which means the number of characters, including spaces) using the LEN function.

Inner join

Most common type of join; includes rows in the query only when the joined field matches records in both tables. INNER JOIN only includes records in which the key is in both tables.

LEFT function

Returns the characters in a text string based on the number of characters you specify starting with the far-left character in the string. If we want to extract the number of characters from the beginning of a string, we use the LEFT function. The function syntax is LEFT, (open parenthesis), the column name, a comma, the number of characters you want to extract, then the closing parenthesis. LEFT(description, 20)

OR keyword

Rows that meet any of the conditions are selected. What if you want to select rows based on multiple conditions where some but not all of the conditions need to be met? For this, SQL has the OR operator. For example, the following returns all films released in either 1994 or 2000: SELECT title FROM films WHERE release_year = 1994 OR release_year = 2000;

AVG keyword

SELECT AVG(budget) FROM films; gives you the average value from the budget column of the films table.

MAX Keyword

Similarly, the MAX function returns the highest budget: SELECT SUM(budget) FROM films;

MIN Keyword

Similarly, the MIN function returns the lowest budget:

substring

A String method that returns a designated portion of a String. Sometimes we need to extract from the middle portion of a string, as opposed to from the left or right edges. That is a job for SUBSTRING. The syntax is SELECT SUBSTRING, open parenthesis, column name, the number of the character to start from, then the number of characters to extract, then closing the parenthesis. SUBSTRING(url, 12, 12) AS target_section

Query

A _________ is a request for data from a database table (or combination of tables). _____________ is an essential skill for a data scientist, since the data you need for your analyses will often live in databases.

Record

A collection of fields that appear as a row in a database or table Each row, or _____________, of a table contains information about a single entity. For example, in a table representing employees, each row represents a single person. Each column, or field, of a table contains a single attribute for all rows in the table.

Field

A single characteristic of data that appears in a table as a column Each column, or _______, of a table contains a single attribute for all rows in the table. For example, in a table representing employees, we might have a column containing first and last names for all employees.

AND Keyword

A word or phrase that describes a subject or category on which you can search. Often, you'll want to select data based on multiple conditions. You can build up your WHERE queries by combining multiple conditions with the AND keyword. SELECT title FROM films WHERE release_year > 1994 AND release_year < 2000;

Aggregate functions can be combined with the WHERE clause to gain further insights from your data.

Aggregate functions can be combined with the WHERE clause to gain further insights from your data. For example, to get the total budget of movies made in the year 2010 or later: SELECT SUM(budget) FROM films WHERE release_year >= 2010;

HAVING clause

Allows you to specify conditions on the rows for each group- in other words, which rows should be selected will be based on the conditions you specify needs to follow GROUP BY clause SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list" HAVING "condition"; In SQL, aggregate functions can't be used in WHERE clauses. For example, the following query is invalid: SELECT release_year FROM films GROUP BY release_year HAVING COUNT(title) > 10; This means that if you want to filter based on the result of an aggregate function, you need another way! That's where the HAVING clause comes in. For example, SELECT release_year FROM films GROUP BY release_year HAVING COUNT(title) > 10; shows only those years in which more than 10 films were released.

SELECT statement

An SQL command that instructs the Microsoft Access database engine to return information from the database as a set of records. In SQL, you can select data from a table using a ______ statement

Group By operator

In a query, an operator that divides the selected records into groups based on the values in the specified field. Now you know how to sort results! Often you'll need to aggregate results. For example, you might want to count the number of male and female employees in your company. Here, what you want is to group all the males together and count them, and group all the females together and count them. In SQL, GROUP BY allows you to group a result by one or more columns, like so: SELECT sex, count(*) FROM employees GROUP BY sex; Commonly, GROUP BY is used with aggregate functions like COUNT() or MAX(). Note that GROUP BY always goes after the FROM clause! As you've just seen, combining aggregate functions with GROUP BY can yield some powerful results! A word of warning: SQL will return an error if you try to SELECT a field that is not in your GROUP BY clause without using it to calculate some kind of value about the entire group. Note that you can combine GROUP BY with ORDER BY to group your results, calculate something about them, and then order your results. For example, SELECT sex, count(*) FROM employees GROUP BY sex ORDER BY count DESC; because there are more females at our company than males. Note also that ORDER BY always goes after GROUP BY. Let's try some exercises!

Aggregate functions used to perform basic arithmetic with symbols like +, -, *, and /.

In addition to using aggregate functions, you can perform basic arithmetic with symbols like +, -, *, and /. So, for example, this gives a result of 12: SELECT (4 * 3);

IN Keyword

Enter the IN operator! The IN operator allows you to specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions! Neat, right? SELECT name FROM kids WHERE age IN (2, 4, 6, 8, 10);

LIMIT Keyword

If you only want to return a certain number of results, you can use the __________ keyword to limit the number of rows returned:

DISTINCT Keyword

If your query returns multiple rows with identical content, what keyword can eliminate duplicate rows? Often your results will include many duplicate values. If you want to select all the unique values from a column, you can use the ________ keyword. SELECT DISTINCT language FROM films;

JOIN Keyword

There's one more concept we're going to introduce. You may have noticed that all your results so far have been from just one table, e.g. films or people. In the real world however, you will often want to query multiple tables. For example, what if you want to see the IMDB score for a particular movie? In this case, you'd want to get the ID of the movie from the films table and then use it to get IMDB information from the reviews table. In SQL, this concept is known as a join, and a basic join is shown in the editor to the right. The query in the editor gets the IMDB score for the film To Kill a Mockingbird! Cool right? As you can see, joins are incredibly useful and important to understand for anyone using SQL.

FROM keyword

Used to tell the database which table to retrieve the columns from.

TOP Function

We can use TOP Function to limit the number of rows returned. Specify the number of rows returned between the brackets TOP(10) You can also specify a percentage of rows return return using TOP and PERCENT. Where PERCENT is the percent of rows to return TOP(10) PERCENT Using SELECT with TOP is a good way to get a quick view of the contents of a table.

COUNT (*) Statement

What if you want to count the number of employees in your employees table? The _____ statement lets you do this by returning the number of rows in one or more columns. SELECT COUNT(*) FROM people; As you've seen, COUNT(*) tells you how many rows are in a table. However, if you want to count the number of non-missing values in a particular column, you can call COUNT on just that column.

ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC; In SQL, the ORDER BY keyword is used to sort results in ascending or descending order according to the values of one or more columns. By default ORDER BY will sort in ascending order. If you want to sort the results in descending order, you can use the DESC keyword. For example, SELECT title FROM films ORDER BY release_year DESC; ORDER BY can also be used to sort on multiple columns. It will sort by the first column specified, then sort by the next, then the next, and so on. For example, SELECT birthdate, name FROM people ORDER BY birthdate, name; sorts on birth dates first (oldest to newest) and then sorts on the names in alphabetical order. The order of columns is important! Try using ORDER BY to sort multiple columns! Remember, to specify multiple columns you separate the column names with a comma.

REPLACE Function

The Replace() function returns a new string that has the specified string or character replaced in all occurrences. TOP(5) REPLACE('_' , '-' ) AS replace_with_hyphen FROM courses; Replace function does all the hard work for us.

SUM Keyword

The SUM function returns the result of adding up the numeric values in a column: SELECT SUM(budget) FROM films;

Keywords

Words or phrases that further describe the database In this query, SELECT and FROM are called __________. In SQL, ________ are not case-sensitive, which means you can write the same query as: select name from people; That said, it's good practice to make SQL ________ uppercase to distinguish them from other parts of your query, like column and table names. It's also good practice (but not necessary for the exercises in this course) to include a semicolon at the end of your query. This tells SQL where the end of your query is!

Aliasing

You may have noticed in the first exercise of this chapter that the column name of your result was just the name of the function you used. For example, SELECT MAX(budget) FROM films; gives you a result with one column, named max. But what if you use two functions like this? SELECT MAX(budget), MAX(duration) FROM films; Well, then you'd have two columns named max, which isn't very useful! To avoid situations like this, SQL allows you to do something called aliasing. Aliasing simply means you assign a temporary name to something. To alias, you use the AS keyword, which you've already seen earlier in this course. For example, in the above example we could use aliases to make the result clearer: SELECT MAX(budget) AS max_budget, MAX(duration) AS max_duration FROM films;

RIGHT function

returns the specified number of characters starting at the right (end) of a text string The RIGHT function starts from the right hand side of the string and works back to extract the number of characters we specify. RIGHT(description, 20)

CHARINDEX function

returns the starting position of the specifies expression in a character string. The third parameter of the function is the character position to start searching for the first parameter in the second parameter. If starting location is a negative number, search starts at beginning of second parameter. The CHARINDEX function helps us find a specific character within a string. CHARINDEX( '_', url)

Null keyword

stands for "nothing" or "no object". In SQL, NULL represents a missing or unknown value. You can check for NULL values using the expression IS NULL. For example, to count the number of missing birth dates in the people table: SELECT COUNT(*) FROM people WHERE birthdate IS NULL;

WHERE keyword

the keyword that specifies the criteria that records must match to be included in the results In SQL, the WHERE keyword allows you to filter based on both text and numeric values in a table. There are a few different comparison operators you can use: = equal <> not equal < less than > greater than <= less than or equal to >= greater than or equal to For example, you can filter text records such as title. The following code returns all films with the title SELECT title FROM films WHERE title = 'Metropolis';

relational database

A database that represents data as a collection of tables in which all data relationships are represented by common values in related tables You can think of a relational database as a collection of tables. A table is just a set of rows and columns, like a spreadsheet, which represents exactly one type of entity.

Primary Key

A field (or group of fields) that uniquely identifies a given entity in a table Is a column that is used to uniquely identify each row in a table. This uniqueness can be achieved by using a sequential integer as an identity column.

Key Field

A field in a record that uniquely identifies instances of that record so that it can be retrieved, updated, or sorted KEY field can be used to reference one table to another.

BETWEEN keyword

Checking for ranges like this is very common, so in SQL the BETWEEN keyword provides a useful shorthand for filtering values within a specified range. This query is equivalent to the one above: SELECT title FROM films WHERE release_year BETWEEN 1994 AND 2000; It's important to remember that BETWEEN is inclusive, meaning the beginning and end values are included in the results!

<>

Not equal to

aggregate function

Performs a calculation on a set of values and then returns a single value. Often, you will want to perform some calculation on the data in a database. SQL provides a few functions, called aggregate functions, to help you out with this.

SQL

Stands for Structured Query Language. It is a programming language designed to manage data within a relational database