SQL Basics 101

SELECT, INSERT, UPDATE with SQL

The Equivalent of HelloWorld of SQL

SELECT *

FROM table_name

Select all columns and rows from a table. In real life practice, we may want to avoid using SELECT * because it may be asking and displaying a lot of unnecessary records utilizing our precious computing resource, especially for large systems, companies with large databases. 

A Basic Select Statement

SELECT ProductID, Name
FROM Product
WHERE Price > 2.00

A Fancier Select Statement

SELECT * FROM CUSTOMERS WHERE AGE > 25 AND SEX = 'F' AND REGION='CA'

The *  means all, specifically all columns and rows in this statement. All columns and all rows will be returned. 

An Advanced Select Statement with Join Statement

SELECT p.[Name] AS ProductName,
c.[Name] AS CategoryName,
FROM SalesLT.Product AS p
JOIN SalesLT.ProductCategory AS c
ON p.ProductCategoryID = c.ProductCategoryID;

An Insert Statement

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Useful SQL interview skills

Be able to read and comprehend SQL scripts

Be able to compose advanced sql queries including aggregation, slicing and dicing.

Advanced SQL Query Select Count and Group By

It's easy to use SQL to display all the data columns and rows. But that's not practical. It's not practical for the business user to get the entire database, nor is it memory efficient. 

How to view aggregate data? Use Group By, don't forget to use Count() too, else the result is again not meaningful. 

SELECT COUNT(CUSTOMER_ID), STATE

FROM CUSTOMERS

GROUP BY STATE

ORDER BY COUNT(CUSTOMER_ID) DESC;

Group By helps aggregate and filter out data. In this case we are interested in aggregating data by State in the Customers table. What kind of state wide information are we trying to get? We are trying to count the number of customers in each state, as measured by customer_id. In addition, once data is aggregated, order the results in a descending order by count(customer_id) the largest count to the smallest. 

Compare a Select all statement which just returns all the data rows
to
Select Count() and Group By statement that aggregates data by country

SQL is great for the following queries:

• SQL Segmentation example, analyze by location, select * from sales group by location

Additional Tools

• psql is a postgres command-line tool
• postgreSQL documentation http://www.postgresql.org/docs/9.4/static/app-psql.html
• postgreSQL is popular with Ruby on Rails users

Why should you learn SQL - common SQL usage

Swift iOS Development Core Data uses sqlite as a persistent data store.

Alternatives to SQL language, sqlite, and relational databases

• ORM and ActiveRecords used in Rails
• Hadoop uses HIVE is a SQL like language
• Spark and the new way to run SQL queries on structured, distributed data
• Firebase real time database and JSON
• JSON objects
• NoSQL databases like MongoDB

SQL Security

Cross Site Scripting and SQL Injection

If allowed to enter special characters in input boxes and forms on a website, hackers may use code to run SQL queries against your database and get data illegally about your website. Many websites do not allow special characters, such as yelp. Some websites stringify the user input before processing it on the server so special characters are turned into strings so to reduce security risk.