SQL pt1

Learning outcomes:

Describe the use of SQL in a database
List some advantages of using query languages like SQL
Describe the parts of a basic SQL query such as : Select Title from books where PublishingDate = 2001;
Demonstrate using SQL with multiple filters and/or functions

SQL or Structured Query Language is how we can interact with our database
SQL can be used to view, organize and manage the data
SQL commands or queries are done on the command prompt and look similar to command line commands
SQL is relatively human readable once you're used to the syntax
Scripts can be written to automate things and make queries more efficient to use
Select Star SQL: Interactive textbook to learn SQL

SQL is interactive, so you can see your results pretty fast and make sure you're looking at the right info
It's close enough to programming a lot of developers are very comfortable learning it quickly
Because it's popular a lot of people learn and use it, therefore making it more popular
SQL is relatively dependable and has been in use so long a lot of people have done a lot of testing on it
Once it's setup correctly a company is unlikely to move away into a new technology
Because SQL is relatively human readable a lot of people use it in companies including marketing, QA and sales,some larger companies will even offer free courses for people to learn it

SQL was standardized in the 80s, but there are different dialects depending on what database you're using
SQL server is proprietary and owned by MS, Dialects including PostgreSQL and SQLite
For example PostgreSQL is open source and for Object oriented databases (we've been talking about relational), and can also suppose JSON data types

For right now we're just using basic SQL standard
Each table in our database will have fields, each record will have a row and column. We need to know what we want to look at to form our query properly
Each SQL query is going to start and include commands such as SELECT, UPDATE, INSERT and DELETE
You want to figure out what you want first, and then try and translate it into a query. For example if you want to look at all customers in your database you might say "SELECT * FROM Customers;"

We can also easily organize our data
Asking the database to give results in any order or organizational pattern we want without changing the data can give us the ability to play with the data in more ways
SQL has the options of Ascending or Descending
These are denoted by ASC and DESC functions, These are used by saying ORDER BY

In SQL this might look like

SELECT * FROM books_table ORDER BY cost desc;

We can also use filters or groupings to get data out of the database
FILTER can show particular pieces of data
An introduction to the GROUP BY clause and FILTER modifier with samples to try right in your browser
One example of filtering data is using the following symbols: = != < >
For example you could see all books that cost less than $10, or more then $5 or cost equal to $9.99

An example of how this might look in a SQL command would be :

SELECT * FROM books_table WHERE price < 10;

SELECT * FROM books_table WHERE price = 9.99;

We can also use AND condition and the OR condition to get multiple options selected
AND condition and the OR condition explained with examples
For example, if you wanted to look at books that cost less than $10 AND were published in the last 10 years

An example of how this might look in a SQL command would be

SELECT * FROM books_table WHERE price < 10 AND publish_date > 2015;

We could also look for items that are unique, or distinct using SQL
This would allow us to look at data without duplication
Common use cases of SQL SELECT Distinct
Example 1: If we wanted to make sure we have unique library patrons, we could look for addresses to make sure we are only seeing unique addresses and no duplicates
Example 2: Looking at books we might want to make sure we are looking at a distinct or unique catalog of books, without duplication so we can take accurate inventory of what we might be missing

SQL has a number of other functions that are helpful as well
CAUTION: The type of SQL you're using affects what functions there are and how they are labelled
Some functions are there in all the common dialects of SQL and look the same
Here are some examples of what functions look like by dialect
For example, if you wanted to find Natural logarithm of x

Identifier
Used to be Numeric only
Unique
NOT data (i.e not social security numbers)
Microsoft refers to primary keys as ID
Modern trend has been to make Primary keys GUID or UUID instead of numeric to make parallelism easier

Example of a guid: e2bd848b-3472-49ab-8836-9ff21aaf8c56
Used to make sure if multiple systems are entering in data they don't use the same key
Won't create chokepoint the way incremental key generation might
Windows people might use guid, Unix people might say uuid

Suggested Activities and Discussion Topics:

Would you like to see some more classes? Click here