SQL

 :::::::from Google Course:::::::

What is a query?

A query is a request for data or information from a database. When you query databases, you use SQL to communicate your question or request. You and the database can always exchange information as long as you speak the same language.

Every programming language, including SQL, follows a unique set of guidelines known as syntax. Syntax is the predetermined structure of a language that includes all required words, symbols, and punctuation, as well as their proper placement. As soon as you enter your search criteria using the correct syntax, the query starts working to pull the data you’ve requested from the target database.

The syntax of every SQL query is the same: 

• Use SELECT to choose the columns you want to return.

• Use FROM to choose the tables where the columns you want are located.

• Use WHERE to filter for certain information.

A SQL query is like filling in a template. You will find that if you are writing a SQL query from scratch, it is helpful to start a query by writing the SELECT, FROM, and WHERE keywords in the following format: 

Next, enter the table name after the FROM; the table columns you want after the SELECT; and, finally, the conditions you want to place on your query after the WHERE. Make sure to add a new line and indent when adding these, as shown below:

Following this method each time makes it easier to write SQL queries. It can also help you make fewer syntax errors.

Example of a query

Here is how a simple query would appear in BigQuery, a data warehouse on the Google Cloud Platform.

The above query uses three commands to locate customers with the first name Tony:

1. SELECT the column named first_name

2. FROM a table named customer_name (in a dataset named customer_data) (The dataset name is always followed by a dot, and then the table name.)

3. But only return the data WHERE the first_name is Tony

The results from the query might be similar to the following:

first_name
Tony
Tony
Tony

As you can conclude, this query had the correct syntax, but wasn't very useful after the data was returned.

Multiple columns in a query

In real life, you will need to work with more data beyond customers named Tony. Multiple columns that are chosen by the same SELECT command can be indented and grouped together.

If you are requesting multiple data fields from a table, you need to include these columns in your SELECT command. Each column is separated by a comma as shown below:

Here is an example of how it would appear in BigQuery:

The above query uses three commands to locate customers with the first name Tony.

1. SELECT the columns named customer_id, first_name, and last_name

2. FROM a table named customer_name (in a dataset named customer_data) (The dataset name is always followed by a dot, and then the table name.)

3. But only return the data WHERE the first_name is Tony

The only difference between this query and the previous one is that more data columns are selected. The previous query selected first_name only while this query selects customer_id and last_name in addition to first_name. In general, it is a more efficient use of resources to select only the columns that you need. For example, it makes sense to select more columns if you will actually use the additional fields in your WHERE clause. If you have multiple conditions in your WHERE clause, they may be written like this:

Notice that unlike the SELECT command that uses a comma to separate fields/variables/parameters, the WHERE command uses the AND statement to connect conditions. As you become a more advanced writer of queries, you will make use of other connectors/operators such as OR and NOT. 

Here is a BigQuery example with multiple fields used in a WHERE clause:

The above query uses three commands to locate customers with a valid (greater than 0) customer ID whose first name is Tony and last name is Magnolia.

1. SELECT the columns named customer_id, first_name, and last_name

2. FROM a table named customer_name (in a dataset named customer_data) (The dataset name is always followed by a dot, and then the table name.)

3. But only return the data WHERE customer_id is greater than 0, first_name is Tony, and last_name is Magnolia.

Note that one of the conditions is a logical condition that checks to see if customer_id is greater than zero.

If only one customer is named Tony Magnolia, the results from the query could be:

customer_id	first_name	last_name
1967	Tony	Magnolia

If more than one customer has the same name, the results from the query could be:

customer_id	first_name	last_name
1967	Tony	Magnolia
7689	Tony	Magnolia

Capitalization, indentation, and semicolons

You can write your SQL queries in all lowercase and don’t have to worry about extra spaces between words. However, using capitalization and indentation can help you read the information more easily. Keep your queries neat, and they will be easier to review or troubleshoot if you need to check them later on.

Notice that the SQL statement shown above has a semicolon at the end. The semicolon is a statement terminator and is part of the American National Standards Institute (ANSI) SQL-92 standard, which is a recommended common syntax for adoption by all SQL databases. However, not all SQL databases have adopted or enforce the semicolon, so it’s possible you may come across some SQL statements that aren’t terminated with a semicolon. If a statement works without a semicolon, it’s fine.

WHERE conditions

In the query shown above, the SELECT clause identifies the column you want to pull data from by name, field1, and the FROM clause identifies the table where the column is located by name, table. Finally, the WHERE clause narrows your query so that the database returns only the data with an exact value match or the data that matches a certain condition that you want to satisfy. 

For example, if you are looking for a specific customer with the last name Chavez, the WHERE clause would be: 

WHERE field1 = 'Chavez'

However, if you are looking for all customers with a last name that begins with the letters “Ch," the WHERE clause would be:

WHERE field1 LIKE 'Ch%'

You can conclude that the LIKE clause is very powerful because it allows you to tell the database to look for a certain pattern! The percent sign (%) is used as a wildcard to match one or more characters. In the example above, both Chavez and Chen would be returned. Note that in some databases an asterisk (*) is used as the wildcard instead of a percent sign (%).

SELECT all columns

Can you use  SELECT * ?

In the example, if you replace SELECT field1 with SELECT * , you would be selecting all of the columns in the table instead of the field1 column only. From a syntax point of view, it is a correct SQL statement, but you should use the asterisk (*) sparingly and with caution. Depending on how many columns a table has, you could be selecting a tremendous amount of data. Selecting too much data can cause a query to run slowly.

Comments

Some tables aren’t designed with descriptive enough naming conventions. In the example, field1 was the column for a customer’s last name, but you wouldn’t know it by the name. A better name would have been something such as last_name. In these cases, you can place comments alongside your SQL to help you remember what the name represents. Comments are text placed between certain characters, /* and */, or after two dashes (--) as shown below. 

Comments can also be added outside of a statement as well as within a statement. You can use this flexibility to provide an overall description of what you are going to do, step-by-step notes about how you achieve it, and why you set different parameters/conditions. 

The more comfortable you get with SQL, the easier it will be to read and understand queries at a glance. Still, it never hurts to have comments in a query to remind yourself of what you’re trying to do. This also makes it easier for others to understand your query if your query is shared. As your queries become more and more complex, this practice will save you a lot of time and energy to understand complex queries you wrote months or years ago. 

Example of a query with comments

Here is an example of how comments could be written in BigQuery:

In the above example, a comment has been added before the SQL statement to explain what the query does. Additionally, a comment has been added next to each of the column names to describe the column and its use. Two dashes (--) are generally supported. So it is best to use -- and be consistent with it. You can use # in place of -- in the above query, but # is not recognized in all SQL versions; for example, MySQL doesn’t recognize #.  You can also place comments between /* and */ if the database you are using supports it. 

As you develop your skills professionally, depending on the SQL database you use, you can pick the appropriate comment delimiting symbols you prefer and stick with those as a consistent style. As your queries become more and more complex, the practice of adding helpful comments will save you a lot of time and energy to understand queries that you may have written months or years prior.

Aliases

You can also make it easier on yourself by assigning a new name or alias to the column or table names to make them easier to work with (and avoid the need for comments). This is done with a SQL AS clause. In the example below, the alias last_name has been assigned to field1 and the alias customers assigned to table. These aliases are good for the duration of the query only. An alias doesn’t change the actual name of a column or table in the database.

Example of a query with aliases

Putting SQL to work as a data analyst

Imagine you are a data analyst for a small business and your manager asks you for some employee data. You decide to write a query with SQL to get what you need from the database. 

You want to pull all the columns: empID, firstName, lastName, jobCode, and salary. Because you know the database isn’t that big, instead of entering each column name in the SELECT clause, you use SELECT *.  This will select all the columns from the Employee table in the FROM clause.

Now, you can get more specific about the data you want from the Employee table. If you want all the data about employees working in the SFI job code, you can use a WHERE clause to filter out the data based on this additional requirement. 

Here, you use:

A portion of the resulting data returned from the SQL query might look like this:

empID	firstName	lastName	jobCode	salary
0002	Homer	Simpson	SFI	15000
0003	Marge	Simpson	SFI	30000
0034	Bart	Simpson	SFI	25000
0067	Lisa	Simpson	SFI	38000
0088	Ned	Flanders	SFI	42000
0076	Barney	Gumble	SFI	32000

Suppose you notice a large salary range for the SFI job code. You might like to flag all employees in all departments with lower salaries for your manager. Because interns are also included in the table and they have salaries less than $30,000, you want to make sure your results give you only the full time employees with salaries that are $30,000 or less. In other words, you want to exclude interns with the INT job code who also earn less than $30,000. The AND clause enables you to test for both conditions. 

You create a SQL query similar to below, where <> means "does not equal":

The resulting data from the SQL query might look like the following (interns with the job code INT aren't returned):

empID	firstName	lastName	jobCode	salary
0002	Homer	Simpson	SFI	15000
0003	Marge	Simpson	SFI	30000
0034	Bart	Simpson	SFI	25000
0108	Edna	Krabappel	TUL	18000
0099	Moe	Szyslak	ANA	28000

With quick access to this kind of data using SQL, you can provide your manager with tons of different insights about employee data, including whether employee salaries across the business are equitable. Fortunately, the query shows only an additional two employees might need a salary adjustment and you share the results with your manager. 

Pulling the data, analyzing it, and implementing a solution might ultimately help improve employee satisfaction and loyalty. That makes SQL a pretty powerful tool. 

Resources to learn more

Nonsubscribers may access these resources for free, but if a site limits the number of free articles per month and you already reached your limit, bookmark the resource and come back to it later.

• W3Schools SQL Tutorial: If you would like to explore a detailed tutorial of SQL, this is the perfect place to start. This tutorial includes interactive examples you can edit, test, and recreate. Use it as a reference or complete the whole tutorial to practice using SQL. Click the green Start learning SQL now button or the Next button to begin the tutorial.

• SQL Cheat Sheet: For more advanced learners, go through this article for standard SQL syntax used in PostgreSQL. By the time you are finished, you will know a lot more about SQL and will be prepared to use it for business analysis and other tasks.

https://www.w3schools.comhttps://www.coursera.org/learn/foundations-data/supplement/hCQCE/endless-sql-possibilities/sql/default.asp

https://towardsdatascience.com/sql-cheat-sheet-776f8e3189fa

Standard SQL Structure

This is Part 1 to a series of PostgreSQL cheat sheets and will cover SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT.

The basic structure of a query pulling results from a single table is as follows.

SELECT 
	COLUMN_NAME(S)
FROM
	TABLE_NAME
WHERE
	CONDITION
GROUP BY
	COLUMN_NAME(S)
HAVING
	AGGREGATE_CONDITION
ORDER BY
	COLUMN_NAME
LIMIT
	N

What is SQL?

SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database or retrieve data from a database.

What is Relational Database Management System (RDBMS)?

An RDBMS organizes data into tables with rows and columns. The term relational means that values within each table have a relationship with each other.

• Rows — also known as records
• Columns — also known as fields, have a descriptive name and specific data type.

What is PostgreSQL?

PostgreSQL is a general-purpose and relational database management system, the most advanced open-source database system.

Other common database management systems are MySQL, Oracle, IBM Db2, and MS Access.

Let’s begin!

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

Specific columns

SELECT
	COLUMN_1,
	COLUMN_2
FROM
	TABLE_NAME

All columns

Using the * you can query every column in your table

SELECT *
FROM
	TABLE_NAME

DISTINCT Columns

Finding all the unique records in a column

SELECT 
	DISTINCT(COLUMN_NAME)
FROM
	TABLE_NAME

COUNT all rows

If you want to know all the values in the entire table use COUNT(*) you will get a single number.

SELECT
	COUNT(*)
FROM
	TABLE_NAME

COUNT DISTINCT values

If you want the number of distinct values in a column using COUNT with DISTINCT and you will get a number representing the total unique values of a column

SELECT 
	COUNT (DISTINCT COLUMN_NAME)
FROM
	TABLE_NAME

WHERE

Using the WHERE the clause, you can create conditions to filter out values you want or don't want.

NOTE — WHERE is always used before a GROUP BY (More on this later)

SELECT *
FROM
	TABLE_NAME
WHERE
	CONDITION

Conditions

There are a variety of conditions that can be used in SQL. Below are some examples of a table that consists of students’ grades in school. You only need to specify WHERE once, for the sake of the example, I have included WHERE in each step.

WHERE FIRSTNAME      = 'BOB'      -- exact match
WHERE FIRSTNAME     != 'BOB'     -- everything excluding BOB 
WHERE NOT FIRSTNAME  ='BOB'    -- everything excluding BOBWHERE FIRSTNAME IN ('BOB', 'JASON')       -- either condition is met
WHERE FIRSTNAME NOT IN ('BOB', 'JASON')   -- excludes both valuesWHERE FIRSTNAME = 'BOB' AND LASTNAME = 'SMITH'  -- both conditions 
WHERE FIRSTNAME = 'BOB' OR FIRSTNAME = 'JASON'  -- either conditionWHERE GRADES > 90           -- greater than 90
WHERE GRADES < 90           -- less than 90
WHERE GRADES  >= 90         -- greater than or equal to 90
WHERE GRADES  <= 90         -- less than or equal to 90WHERE SUBJECT IS NULL       -- returns values with missing values
WHERE SUBJECT NOT NULL      -- returns values with no missing values

Conditions — Wildcards

LIKE operator is used in a WHERE clause to search for a specified pattern in a column. When you pass the LIKE operator in the '' upper and lower case matters.

There are two wildcards often used in conjunction with the LIKE operator:

• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character

WHERE FIRSTNAME LIKE ‘B%’ -- finds values starting uppercase BWHERE FIRSTNAME LIKE ‘%b’ -- finds values ending lowercase bWHERE FIRSTNAME LIKE ‘%an%’ -- find values that have “an” in any positionWHERE FIRSTNAME LIKE ‘_n%’ -- find values that have “n” in the second positionWHERE FIRSTNAME LIKE ‘B__%’ -- find values that start with “B” and have at least 3 characters in lengthWHERE FIRSTNAME LIKE ‘B%b’ -- find values that start with “B” and end with “b”WHERE FIRSTNAME LIKE ‘[BFL]’ -- find all values that start with ‘B’, ‘F’ OR ‘L’WHERE FIRSTNAME LIKE ‘[B-D]’ -- find all values that start with ‘B’, ‘C’, OR ‘D’WHERE FIRSTNAME LIKE ‘[!BFL]%’ -- find everything exlcusing values that start with ‘B’, ‘F’ OR ‘L’WHERE FIRSTNAME NOT LIKE ‘[BFL]%’ -- same as above. excludes values starting with ‘B’, ‘F’, OR ‘L’WHERE GRADES BETWEEN 80 and 90 -- find grades between 80 and 90

GROUP BY

The GROUP BY function helps calculate summary values by the chosen column. It is often used with aggregate functions (COUNT, SUM, AVG, MAX, MIN).

SELECT
	SUBJECT,	
	AVG(GRADES)
FROM
	STUDENTS
GROUP BY
	SUBJECT

The query above will group each subject and calculate the average grades.

SELECT
	SUBJECT,	
	COUNT(*)
FROM
	STUDENTS
GROUP BY
	SUBJECT

The above query will calculate the number (count) of students in each subject.

HAVING

The HAVING clause is similar to WHERE but is catered for filtering aggregate functions. The HAVING function comes after the GROUP BY, in comparison the WHERE comes before the GROUP BY.

If we wanted to find which subject had an average grade of 90 or more, we could use the following.

SELECT
	SUBJECT,	
	AVG(GRADES)
FROM
	STUDENTS
GROUP BY
	SUBJECT
HAVING 
	AVG(GRADES) >= 90

ORDER BY

Using the ORDER BY function, you can specify how you want your values sorted. Continuing with the Student tables from earlier.

SELECT
	*
FROM
	STUDENTS
ORDER BY
	GRADES DESC

When using the ORDER BY by default, the sort will be in ascending order. If you want to descend, you need to specify DESC after the column name.

LIMIT

In Postgres, we can use the LIMIT function to control how many rows are outputted in the query. For example, if we wanted to find the top 3 students with the highest grades.

SELECT
	*
FROM
	STUDENTS
ORDER BY
	GRADES DESC
LIMIT
	3

Since we use ORDER BY DESC we have the order of students with the highest grades on top - now limiting it to 3 values, we see the top 3.

Transforming data in SQL

Data analysts usually need to convert data from one format to another to complete an analysis. But what if you are using SQL rather than a spreadsheet? Just like spreadsheets, SQL uses standard rules to convert one type of data to another. If you are wondering why data transformation is an important skill to have as a data analyst, think of it like being a driver who is able to change a flat tire. Being able to convert data to the right format speeds you along in your analysis. You don’t have to wait for someone else to convert the data for you. 

In this reading, you will go over the conversions that can be done using the CAST function. There are also more specialized functions like COERCION to work with big numbers, and UNIX_DATE to work with dates. UNIX_DATE returns the number of days that have passed since January 1, 1970 and is used to compare and work with dates across multiple time zones. You will likely use CAST most often.  

Common conversions 

The following table summarizes some of the more common conversions made with the CAST function. Refer to Conversion Rules in Standard SQL for a full list of functions and associated rules. 

Starting with	CAST function can convert to:
Numeric (number)	- Integer - Numeric (number) - Big number - Floating integer - String
String	- Boolean - Integer - Numeric (number) - Big number - Floating integer - String - Bytes - Date - Date time - Time - Timestamp
Date	- String - Date - Date time - Timestamp

The CAST function (syntax and examples)

CAST is an American National Standards Institute (ANSI) function used in lots of programming languages, including BigQuery. This section provides the BigQuery syntax and examples of converting the data types in the first column of the previous table. The syntax for the CAST function is as follows:

CAST (expression AS typename)

Where expression is the data to be converted and typename is the data type to be returned.

Converting a number to a string

The following CAST statement returns a string from a numeric identified by the variable MyCount in the table called MyTable. 

SELECT CAST (MyCount AS STRING) FROM MyTable

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table

• CAST indicates that you will be converting the data you select to a different data type

• AS comes before and identifies the data type which you are casting to

• STRING indicates that you are converting the data to a string

• FROM indicates which table you are selecting the data from

Converting a string to a number

The following CAST statement returns an integer from a string identified by the variable MyVarcharCol in the table called MyTable. (An integer is any whole number.)

SELECT CAST(MyVarcharCol AS INT) FROM MyTable

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table

• CAST indicates that you will be converting the data you select to a different data type

• AS comes before and identifies the data type which you are casting to

• INT indicates that you are converting the data to an integer

• FROM indicates which table you are selecting the data from

Converting a date to a string

The following CAST statement returns a string from a date identified by the variable MyDate in the table called MyTable. 

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table

• CAST indicates that you will be converting the data you select to a different data type

• AS comes before and identifies the data type which you are casting to

• STRING indicates that you are converting the data to a string

• FROM indicates which table you are selecting the data from

Converting a date to a datetime

Datetime values have the format of YYYY-MM-DD hh: mm: ss format, so date and time are retained together. The following CAST statement returns a datetime value from a date. 

In the above SQL statement, the following occurs:

• SELECT indicates that you will be selecting data from a table

• CAST indicates that you will be converting the data you select to a different data type

• AS comes before and identifies the data type which you are casting to

• DATETIME indicates that you are converting the data to a datetime value

• FROM indicates which table you are selecting the data from

The SAFE_CAST function

Using the CAST function in a query that fails returns an error in BigQuery. To avoid errors in the event of a failed query, use the SAFE_CAST function instead. The SAFE_CAST function returns a value of Null instead of an error when a query fails. 

The syntax for SAFE_CAST is the same as for CAST. Simply substitute the function directly in your queries. The following SAFE_CAST statement returns a string from a date.

SELECT SAFE_CAST (MyDate AS STRING) FROM MyTable

More information

Browse these resources for more information about data conversion using other SQL dialects (instead of BigQuery):

• CAST and CONVERT: SQL Server reference documentation

• MySQL CAST Functions and Operators: MySQL reference documentation

• How to: SQL Type Casting: Blog about type casting that has links to other SQL short guides

Manipulating strings in SQL

Knowing how to convert and manipulate your data for an accurate analysis is an important part of a data analyst’s job. In this reading, you will learn about different SQL functions and their usage, especially regarding string combinations. 

A string is a set of characters that helps to declare the texts in programming languages such as SQL. SQL string functions are used to obtain various information about the characters, or in this case, manipulate them. One such function, CONCAT, is commonly used. Review the table below to learn more about the CONCAT function and its variations.

Function	Usage	Example
CONCAT	A function that adds strings together to create new text strings that can be used as unique keys	CONCAT (‘Google’, ‘.com’);
CONCAT_WS	A function that adds two or more strings together with a separator	CONCAT_WS (‘ . ’, ‘www’, ‘google’, ‘com’) *The separator (being the period) gets input before and after Google when you run the SQL function
CONCAT with +	Adds two or more strings together using the + operator	‘Google’ + ‘.com’

CONCAT at work

When adding two strings together such as ‘Data’ and ‘analysis’, it will be input like this: 

• SELECT CONCAT (‘Data’, ‘analysis’);

The result will be:

• Dataanalysis

Sometimes, depending on the strings, you will need to add a space character, so your function should actually be:

• SELECT CONCAT (‘Data’, ‘  ‘, ‘analysis’);

And the result will be:

• Data analysis

The same rule applies when combining three strings together. For example,

• SELECT CONCAT (‘Data’,’ ‘, ‘analysis’, ‘ ‘, ‘is’, ‘ ‘, ‘awesome!’);

And the result will be

• Data analysis is awesome!

Practice makes perfect

W3 Schools is an excellent resource for interactive SQL learning, and the following links will guide you through transforming your data using SQL:

• SQL functions: This is a comprehensive list of functions to get you started. Click on each function, where you will learn about the definition, usage, examples, and even be able to create and run your own query for practice. Try it out for yourself!   

• SQL Keywords: This is a helpful SQL keywords reference to bookmark as you increase your knowledge of SQL. This list of keywords are reserved words that you will use as your need to perform different operations in the database grows.

• While this reading went through the basics of each of these functions, there is still more to learn, and you can even combine your own strings.

1. Practice using CONCAT

2. Practice using CONCAT WS

3. Practice using CONCAT with +

Pro tip: The functions presented in the resources above may be applied in slightly different ways depending on the database that you are using (e.g. mySQL versus SQL Server). But, the general description provided for each function will prepare you to customize how you use these functions as needed. 

Key takeaway

The most important thing to remember is how to use SELECT, FROM, and WHERE in a query. Queries with multiple fields will become simpler after you practice writing your own SQL queries later in the program.