SQL is a must have skill in today’s tech industry and Pioneer Academy offers you a course that teaches you how to use SQL to retrieve information from databases. Now available on both mobile and desktop platforms. Take the first several lessons for free at http://pioneer-academy1.teachable.com/courses/basic-sql-queries
I have created a brand new online course on SQL, specifically designed for people who are not database experts. It will give you the knowledge and tools you need to be able to operate on any relational database that allows access to its underlying SQL. Practically all database management systems provide such access. The course is a highly interactive hands-on course. You download a free database management system and solve problems by running SQL queries against a sample database. The course is personally administered by Allen Taylor, the course developer and best-selling author of multiple books on database and SQL. You can discuss each concept and challenge with Allen and with your fellow students.
The course, SQL for Business Analysts is ready to go, but will not be “officially” released until August 1. If you register now, before the official roll-out, you can do so for half the regular price, a 50% discount. Click here to find out more.
Some things that you may want to retrieve from a database can be very easy to obtain with an SQL statement. Other questions might be more involved, and correspondingly require a rather complex SQL statement. Much like English-language sentences, SQL statements can contain multiple clauses that serve to precisely filter out all the data you don’t want, leaving only the information you do want, in the form that you want to see it.
Let’s look at a very simple retrieval operation to answer a simple question, and proceed from there to add clauses to SQL SELECT statements to home in on more tightly targeted questions.
For anyone working for a business or even a non-profit entity, it is probably important that you know as much about your existing customers as possible, so that you can find new customers with similar needs. You can delve into your database’s tables to find out more about your customers and what they have bought in the past. Let’s look at how we could do this with SQL..
After launching your DBMS, connect to a database that contains the information of interest. Take a look at the list of tables included in the database. Suppose one of them is named ‘customer.’
Perhaps the first thing you want to know is the number of customers you have in your customers table. You can answer this question with a simple SELECT statement. Go to the blank window that your DBMS provides, into which you can type SQL statements.
In the window, type:
SELECT * FROM customers ;
The asterisk is a wildcard character that means “all columns.” This will cause the data in all the columns of the customers table to be returned. The semi-colon denotes the end of the statement.
To execute the statement, There should be an icon or button that you can click. Once you click it, your statement will be executed and the result of the operation will be displayed. Along with a list of all the fields in all the rows of your database, there will probably also be a message telling you how many rows have been returned.
Since all you wanted was the number of customers, not a display of all the data for all those customers, there is another way to answer your question, with the statement:
SELECT COUNT (*) FROM customers ;
That returns the number of rows in the customers table.
It’s nice to know how many customers you have, but more useful from a marketing perspective might be to know how many customers you have in a particular region that you could target with advertising. Suppose you want to know how many customers you have in California, for example. A small addition to your original SELECT statement will do the trick. Type:
SELECT * FROM customers
WHERE state = ‘CA’ ;
The WHERE clause returns only those rows where the value in the state column is CA. Text strings such as CA must be enclosed in quote marks for the database engine to understand that it is looking at a text string.
Earlier, we used COUNT to count the number of customers in the customers table COUNT is an example of a Set function. Other set functions are: MAX, MIN, SUM, and AVG. As you would expect, MAX will return the maximum value that exists in the specified column, MIN will return the minimum, SUM will add up all the values, and AVG will return the average value.
Suppose we want to know the total of all the sales recorded in the invoices table of the database. I bet you could figure out what the SQL for that would be. It would be:
SELECT SUM(Total) FROM invoices ;
Execute that statement and you receive the total amount of sales that have been made.
More likely, you are interested in the total sales during an interval of time, for example a month. We can obtain this information by adding a WHERE clause to our statement.
SELECT SUM(Total) FROM invoices
WHERE InvoiceDate > ‘2017-01-31’ AND InvoiceDate < ‘2017-03-01’ ;
This gives us the total sales for the month of February 2017. The AND keyword is a logical connective that enables us to express a compound condition. It only returns rows where both the predicate before the AND keyword and the predicate after the AND keyword are true. A predicate is a statement that may either be logically True or logically False. A date is either greater than February first 2017 or it is not.
Other logical connectives are OR and NOT, although NOT does not connect two predicates. A predicate preceded by a NOT keyword evaluates to True if the predicate itself evaluates to False. A clause with an OR connective is considered to be true if either of the two predicates in contains, evaluates to a true value.
Elizabeth Blackburn of the Salk Institute in La Jolla, California has a new book named “The Telomere Effect” that explains lifestyle choices you can make that will add years to your life. Read about it here:
Commercial Crew missions through 2024 are now contracted.
Billions of people are using smartphone apps every day, either on their iPhone, their Android device, or something else. Have you ever wondered what it would take to write such an app? Peter Leow has written an Android app as an example. It stores some basic information in an SQL database and gives the user the ability to update or delete it. If you use SQL to make queries into an existing database, you don’t need to know any of this, but it’s interesting to see what goes on “under the covers” of the apps you are using on your phone. Take a look at Peter’s code. Even if you are not a programmer, you can follow the general flow of what he does in his Java language smartphone app.
Data science, particularly the part called analytics is exploding with opportunity. You are probably wondering how you might climb aboard this gravy train. As the article given below explains, SQL is the onramp of choice to this career option.