SQL Basics-1

SQL - What are Subqueries: Advanced Queries
             Subqueries can be performed inside of existing queries. This expands the capabilities of SQL in a number of ways providing a 3rd dimension to the language you might say. Again, we will discuss subqueries in more detail in a later lesson. Feel free to familiarize yourself with what a subquery may look like using the example below.
SQL Code: 
SELECT * FROM table_one WHERE unique_column = (SELECT unique_column FROM table_two WHERE id_column = 1)
Above is a look at where you might cross subqueries. The logic behind the entire query is fairly confusing at this point, try and stay with us and focus on the syntax of the query and subquery.

SQL - Syntax
           SQL follows a general syntax, there are not many quotations or other symbols to throw into your statements. Generally we follow a Do what To what syntax, meaning first we decide what we want to do, then we decide what we want to do it to, and finally we end the whole thing with a semicolon (;).
           A statement begins with a clause. Clauses are commands in the SQL world and the backbone of any script. The first clause of a statement gives a general idea of what type of action a script is taking. A few basic clauses are SELECT, INSERT, or CREATE.
            We will look at each of these clauses a little more in depth on the next few pages but it may be obvious to you already what each of those clauses does. SQL statements end with a semicolon as most with most programming languages. A basic statement might look like this:
SQL Code:
SELECT * FROM table_name;

                Above we have a SELECT clause asking for all columns and values (*) from our database table. As shown above, a good habit is to capitalize your clauses. Later on when we have larger statements and subqueries it will make life much easier to go back and debug your code.
                 Formating your statements in a similar fashion will also aid your debugging efforts. The common formatting technique is to begin each line with a clause or to break up and list columns or tables as needed. More on this in a moment.
SQL Code: 
SELECT * FROM table_name; 

The advantage of this isn't apparent with this example. Each are fairly easy to read. However the example below shows an example where this format shines.
SQL Code: 
SELECT column_one, column_two 
FROM table_name
WHERE ( 
column_one, 
column_two, 
column_three, )
 = (SELECT column_one,
 column_ two
FROM column_ two
WHERE table_one.id = 'table_two.id');

As you can see, when subqueries are thrown into the mix things become a little more complicated. A one line statement will not fit across your screen. Both statements are neither right nor wrong, each are easier to follow. Parentheses generally depict order of operations but it is not an exact science. Quotations are not found until the predicate of the statement.

SQL - Data Types
SQL recognizes 4 general types of data. As the database designer you will be selecting which type of data that can be placed in each table column. Before we look at each type of table column we will elaborate on specific data types and how they are handled in SQL.

Character Strings - ('Words or numbers')
Numbers - (3, 3.423, -17)
Booleans - (True / False)
Nulls - (empty fields)

SQL - NULL Values
A null value may be the most foreign to new programmers. Stating that a value has a null value indicates that nothing exists in that table field. When the table is created you may either allow a table to have a null value or may disallow null values for each table column.
SQL Code:
 CREATE TABLE weekly_payroll (employee_id VARCHAR(10) PRIMARY KEY, total_hours INT NULL, hourly_rate MONEY NOT NULL,);

SQL - Numeric Data
Dates, time stamps, integers, and money are all numeric data types. The advantage of working with numbers is that SQL has built in functions such as the AVG() or SUM() functions that will return the average or the sum of a numeric column.
Numbers:
rate_of_pay
27
26.66
28.40


SQL - Boolean Data
Boolean values are either yes/no (true/false) types of data. Others use a 1/0 (1 for yes 0 for no) approach. Either something is or something is not.

Boolean Values:
admin
1
1
0

SQL - Character Strings
Character strings are sentences, symbols, or a combination of both. Math functions can not be performed with character strings.
Character Strings:
employee_id
TS_0036
TS_0078
CL_1099