sql - identifiers, think sql

In previous chapter i have explained How To Use UNION Command In SQL now i am gonna eplain you How To Use Identifiers, think sql In SQL

As with any programming language, there are a few terms unique to SQL.

An Identifier is essentially a name of a database, table, or table column. As the creator of the database, you are free to identify these objects as you please; we merely suggest you keep these guidelines in mind when you do.

• Develop your own unique naming scheme. -- Use terms that relate to one another and will be easy to recognize apart from your code.
• Be conscious of how long your names become. -- Especially be aware when the time comes to name your columns.
• Avoid names without meaning. -- Develop a working scheme that also has meaning behind the names.
• Be consistent. -- If you capitalize one table name, capitalize each table name; if you choose to use abbreviations, make sure they do not have double meanings or ambiguous meaning.

Develop a clear, concise schema, and stick to it as your database develops.

sql - literals

Literal is a term for data types such as strings, numbers, or boolean values in SQL. These values are not named by you the programmer; they just exist.

Literal Breakdown:

string literals
'This is a string value'
'5893 Moon River Dr.'

number literals
823
-4.5
3.387920

boolean literals
TRUE
FALSE
1
0

sql - predicates

In previous chapter i have explained How To Use Identifiers, think sql In SQL now i am gonna eplain you What are the SQL Predicates

SQL Predicates are found on the tail end of clauses, functions, and SQL expression inside of existing query statements. We've actually already covered a few SQL predicates already and most on this list should be familiar to you.

SQL Predicates:

AND
OR
LIKE
BETWEEN
AS
TOP(LIMIT)

sql - top

SQL Top is used to limit the number of results returned by a query. The top command is generally followed by a number that indicates the maximum number of results to return for a given query.

SQL Code:

use mydatabase;

SELECT TOP 2 *
FROM orders

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4
2	Tizag	2008-08-01 00:00:00.000	Stapler	3

sql - structure

In previous chapter i have explained What are the SQL PredicatesL now i am gonna eplain you What is the Structure of SQL

The importance of building your tables correctly could be considered its own form of art. Psychologists have even gone as far to say that those with the ability to organize their thoughts and concepts well tend to be absolutely brilliant (ie Mozart, Einstein, DaVinci). A database is only slightly different from the human brain and clear organization is a must, especially if large quantities of people will be using the same database.

Designing the right database has a lot to do with what you need to accomplish. A database with thousands and thousands or even millions of records requires a completely different solution than a database that will only have a few hundred records. Think about the future and plan ahead.

sql - table relationships

The key to a large database is having tables with material that can relate to one another. A great example is the employee ID. Ever wonder or joke about being just another number to the company. A lot of that has to do with the way they set up their database. A large company is going to have a weekly payroll and a personal information table. They might be set up like the following:

personal_info

emp_id	last_name	first_name
0056	Mynce	George
0097	Jenkins	Fred

weekly_payroll

emp_id	hours	rateofpay
0056	37.5	27.00
0097	44.5	22.25

This set up proves useful in several ways. The tables are smaller, more compact, and precise, allowing for maximum access speeds with each query. We can also moderate who has access to each table, meaning that our accountant can have access to the payroll table, but not the personal information table, eliminating security risks.

Relationship tables are the choice for high-traffic databases. There are countless security advantages, faster query returns, and complete view customization (more on this later).

sql - commands

In previous chapter i have explained What is the Structure of SQL now i am gonna eplain you How To Use SQL Commands

SQL commands are lines of SQL code that ask the SQL application to perform simple tasks against with data inside of a database. Often we refer to commands as query statements or scripts; all of these terms are synonymous. Most of the commands and statements you will see in this tutorial are fairly standard and will work across multiple database applications despite the fact this tutorial is aimed for the SQL Server Express user.

SQL commands are declarative sentences or 'orders' executed against a SQL database. The typical command is comprised of several different components including clauses, functions, expressions, or objects but the only required components are a SQL Clause and the data object (a database or a database table).

sql - clauses

A SQL clause is the word or phrase that begins each SQL command statement and the single most important component to any SQL command. Looking at the clause of any given SQL command usually tells the SQL programmer what that particular command intends to do to the database. For instance, a SELECT command begins with the SELECT clause and any level of SQL programmer will recognize the SELECT query as a command selecting data from the database.

Other SQL clauses include:

• Add
• Drop
• Create
• Insert
• Select
• Update
• Replace
• Delete

sql - sample commands

SQL Code:

SELECT getdate(); -- Selects the current (server) date and time.

CREATE DATABASE MyDatabase; -- Creates a database named Mydatabase;

INSERT INTO orders 
 -- Inserts data into a database table
VALUES('A+Maintenance','8/16/08','Hanging Files',12);

sql - historical information

In previous chapter i have explained How To Use SQL Commands now i am gonna eplain you What is the History of SQL

In the '60's database software required the use of complex mainframe machines that were difficult to maintain and run. Information technologists worked around the clock monitoring, updating, and manually uncorrupting these machines.

Each mainframe ran different software from different manufacturers. IBM pulled ahead in software development internationally with efforts of software aimed at database management. The problem was that each mainframe ran a different type of "language".

Enter SQL, the new standard for any database program: Structured Query Language. SQL bridged the barriers between mainframes and allowed large corporations to network their efforts. SQL was introduced in the 1970's and quickly gained international popularity. SQL allows a programmer to tell a program exactly what data to retrieve and how to display it.

sql - under development

Software at the time was still underdeveloped and several continuous problems plagued database storage. Transactions happening at the same time were often combined, mixed together, or even lost altogether. For instance, say two individuals made a deposit at exactly the same time from two different locations. The software was unable to cope with this, and their bank transactions were completely switched. Suzy deposited $10,000 while Joe withdrew $15. Because of the faulty software Joe's withdrawal was posted on Suzy's account and Suzy's deposit was posted to Joe's account.

Along came Relational Database Management Systems (RDBMS). This software is still being used and is quite powerful. Relational databases allow developers to build relationships between databases and tables. This provides tremendous opportunities for data management and is still the favorite software used today. MySQL, SQL Server, DB2, and Oracle are all RDBMSs, and each have a substantial hold in the market share to this day.

sql - expressions

In previous chapter i have explained How To Use VIEWS Object In SQL now i am gonna eplain you How To Use Expressions In SQL Query

SQL Expressions are the pieces of a SQL query that compare values against other values or perform arithmetic calculations. Expressions can be found inside of any SQL command usually in the form of a conditional statement. In the SQL world, conditional statements and expressions test or compare values against other values.

sql - boolean expressions

Boolean expressions return rows (results) when a single value is matched.

SQL Boolean Expression:

USE mydatabase;

SELECT * FROM orders WHERE id = '1';

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4

sql - numeric expression

Numeric Expressions return a single numeric value instead of an entire row and usually perform calculations.

SQL Code:

USE mydatabase;

SELECT 15 + 4;

SQL Code:

USE mydatabase;

SELECT (15 / 5) * 10;

SQL Code:

USE mydatabase;

SELECT ((5+5) * (5+5));

Each of the examples above returns a numeric value which is displayed inside the results pane of the SQL application. SQL also offers several built-in functions to perform what is known as aggregate data calculations against a table or a specific table column.

• AVG() -- Returns the average value of a stated column.
• COUNT(*) -- Returns a count of the number of rows of table.
• SUM() -- Returns the sum of a given column.

Using one of the following functions also returns a numeric value:

SQL Code:

USE mydatabase;

SELECT COUNT(*) AS "Number of Orders"
FROM orders;

SQL Code:

USE mydatabase;

SELECT SUM(quantity)AS "Total Number of Items Purchased"
FROM orders;

SQL Code:

USE mydatabase;

SELECT AVG(quantity) AS "Average Number of Items Purchased"
FROM orders;

We can also combine these queries into a single query so that the results are viewable all at once.

SQL Code:

USE mydatabase;

SELECT COUNT(*) AS "Number of Orders",
SUM(quantity)AS "Total Number of Items Purchased",
AVG(quantity)AS "Average Number of Items Purchased"
FROM orders;

sql - date expressions

As the name suggests, Date Expressions return date/time values.

• GetDate() -- Returns the current date/time.
• Current_Timestamp -- Returns the current timestamp.

Date expressions as you may have guessed, return date values. We will be taking a closer look at date expressions later on in this tutorial. Stay tuned.

SQL Code:

USE mydatabase;

SELECT Current_Timestamp;
SELECT  GETDATE();

sql - data types

In previous chapter i have explained How To Use Expressions In SQL Query now i am gonna eplain you How To Use Data types In SQL

SQL data takes shape in several different forms, including character strings, numbers, file stores, and dates. SQL developers call the shots as to what types of data will be stored inside each and every table column when creating a SQL table. The developer must specify the column type of each new SQL table column.

Column types are synonymous with data types as the column type is what designates the type of data that will be stored inside the column. In other words, a SQL data type is a label and a guideline for SQL to understand what type of data is expected inside of each table column and this identifies how SQL will interact with the stored data. Below, we will give you an overview on the types of data that can be stored within a SQL table.

sql - numbers, decimals, and dates

Data Types:

• Integers - (3, -17)
• Point(Decimal) - (3.23415)
• Date - (2004-06-22 10:33:11.840)

Storing numbers and decimals allows the developer to collect statistical data and create reports based on the data contained inside the table. SQL can even perform mathematical calculations against numeric data, providing endless number-crunching abilities.

In SQL, decimals are often referred to as point or floating-point numbers. These data types are slightly different from the normal 'integer' data types.

For the most part, date values are treated like numbers and they can even be added together and subtracted offering the developer the option to add days, months, or years together to create new dates (more on this later). Additionally, specific data can be extracted from date values, allowing the developer to pull specific date information from a date value like only the month number, the year, or the day of the week.

sql - boolean data

• ("TRUE" / "FALSE")
• ( 1 / 0 )

Boolean values are true/false types of data. A Boolean table column will contain either string values of "True" and "False" or the numeric equivalent representation, with 0 being false and 1 being true.

sql - character strings

Character Strings:

• VARCHAR - ('Words or numbers')
• Text - ('Once upon a time...')

Strings range from a single word or character to large blocks of text including multiple paragraphs and unique symbols. Set the table column type to VARCHAR or Text in order to incorporate string data types into SQL tables.

SQL Server Table Column Types:

bigint	Integer value (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)	2^63
int	smaller Integer value (-2,147,483,648) - (2,147,483,647)	2^31
smallint	smaller Integer value (-32,768) - (32,767)	2^15
tinyint	smaller Integer values 0 - 255	2^8
bit	Integer data value (either 1 or 0 value)	1 or 0
decimal	Decimal values from -10^38 - 10^38	10^38
numeric	Decimal values from -10^38 - 10^38	10^38
money	Money values (-922,337,203,685,477.5808) - (922,337,203,685,477.5807)	2^63
smallmoney	Smaller Money Values (-214,748.3648) - (214,748.3647)	2^31
datetime	Date value (January 1, 1753) - (December 31, 9999)
smalldatetime	Smaller Date Value (January 1, 1900) - (June 6, 2079)
timestamp	Unique Number Value (updates when row is updated)
char	Character String Value (max 8,000 characters)
varchar	Character String Value maximum of 8,000 characters, unless otherwise noted)
nchar	Character String Value (max 4,000 characters)
nvarchar	Character String Value (max 4,000 characters)
text	Character String Value (max 2,147,483,647 characters)	2^31
ntext	Character String Value (max 1,073,741,823 characters)	2^30
binary	Binary Value (max 8,000 bytes)
varbinary	Binary Value (max 8,000 bytes)
image	Binary Value (max 2,147,483,647 bytes)	2^31
uniqueidentifier	Global Unique ID (GUID)

sql - defaults and null values

NULL values are 'nothing' values. When a value is null, it means the value is empty and contains no value -- not even '0'. NULLs are unique data types that are usually the default setting for all table columns. When a SQL developer runs across a NULL value in a database, it is generally an indication that this value is either new or has not been modified.

The SQL developer may specify to allow or disallow the NULL values eliminating the possibility of running across 'empty' table columns when creating a SQL table. If the developer chooses not to allow NULL values he/she may specify a custom default value instead of the NULL (nothing) value. Primary Key table columns do not allow NULL values since this column's sole purpose is to be the unique identifier for a table column. Having a NULL unique identifier would be similar to having a car license plate that is blank.

By default, NULL values are allowed on all newly created table columns meaning a table column is allowed to be 'empty', except primary key columns. A NULL value is a special type of value that can be tested for by most programming languages including SQL and can provide the developer a means to 'test' and see if data exists or has been modified. As a new programmer you may not fully understand the benefits a NULL value can bring, but with experience, you will learn to hate/appreciate them.

sql - syntax - (speaking sql)

In previous chapter i have explained How To Use Data types In SQL now i am gonna eplain you What is Syntax In SQL

Syntax, by definition, means the study of linguistic rules and patterns. Every programming language, including SQL, must follow a unique set of guidelines termed syntax. Punctuation, spaces, mathematical operators, and special characters have special meaning when used inside of SQL commands and query statements. For example, each and every SQL command will end with a semi colon (;).

Executing SQL commands that do not have proper syntax and formatting will result in a syntax error. Syntax errors might be the most common and first error messages new SQL developers will experience.

Let's now take a look at a very simple SQL command that will be used in just about every example contained in this tutorial from here on out.

Sample SQL Command:

use mydatabase;

This command identifies a database as a target database, meaning that any SQL command or query executed after this line will be run against the identified database. In this case, the database 'mydatabase' will be the target database. This is a good way to prevent mistakes and avoid potential data loss and a good reason to include this command into each and every authored SQL query.

sql - syntax: capitalization and spacing

In some programming languages, capitalizing commands or excessive spacing may or may not cause syntax code errors and cause the command to fail. SQL syntax is very loose when it comes to capitalization and spacing, allowing a lot of room for the developer to decide on his/her own preference in regards to capitalization and spacing.

Let's rewrite the same SQL command from the previous example and take advantage of SQL's loose syntax characteristics.

Sample SQL Command:

USE


mydatabase;

The example above, though it does look different due to the capitalization and spacing, will yield the same results as the first example.

sql - syntax: building good habits

While coding in any language, it is important to develop good, consistent habits and maintain clean code. Clean code allows another SQL developer to step right in where you have left off without missing a beat. A developer with diligent coding habits will prevent many syntax errors before executing his/her scripts and also will be able to detect possibly syntax problems before they cause problems in a SQL Command.

Good habits include:

• Consitency
• Clean and Concise
• Use of Comments (more on this later)
• Scalability

Coding in any language is as much of an art form as authoring best selling novels and stories. Take pride in doing so and always do your best to follow good coding habits.

sql - union

In previous chapter i have explained What is Syntax In SQL now i am gonna eplain you How To Use UNION Command In SQL

SQL UNION combines two separate SQL queries into one result set. A JOINstatement adds additional table columns to a result set (horizontally), UNIONcombines row results from one table with rows of another table (vertically).

In order to perform a UNION the columns of table 1 must match those of table 2. This rule ensures that the result set is consistent as rows are fetched by SQL.

For these next exercises we suggest creating two different tables that are identical in structure but contain unique rows of data. We challenge you to do this by reviewing the SQL Create queries and modifying them to create two brand new employee tables.

SQL Select Union Code:

USE mydatabase;

SELECT * FROM employees
UNION
SELECT * FROM employees2;

SQL Table:

ID	Lastname	Firstname	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
104	Maines	Brad	crew

The result is a complete listing of every employee from the two tables, perhaps representing a list of employees from two different departments.

The next example shows a more practical means of using a union clause. Here we will select all of our employees from both tables and join them with our invoices table to generate a complete list of sales from both stores on a given day.

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
UNION
SELECT employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id = invoices.EmployeeID;

SQL Table:

Lastname	Firstname	Sale	Price
Johnson	David	HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
Davis	Julie	CK SLD	3.99
Yazzow	Jim	HOT DOG	1.99
Carlson	Kevin	LG SFT DRK	1.49

Here we combined a join query with the union clause to create one table.

sql - union all

UNION ALL selects all rows from each table and combines them into a single table. The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows. Instead, it just pulls all rows from all tables fitting your query specifics and combines them into a table.

SQL Code:

SELECT * FROM employees
UNION ALL
SELECT * FROM employees2;

SQL Table:

ID	Lastname	Firstname	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
11	Davis	Julie	manager
104	Maines	Brad	crew

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
UNION ALL
SELECT employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id = invoices.EmployeeID;

SQL Table:

Lastname	Firstname	Sale	Price
Johnson	David	HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
Davis	Julie	CK SLD	3.99
11	Davis	Julie	manager
Yazzow	Jim	HOT DOG	1.99
Carlson	Kevin	LG SFT DRK	1.49
11	Davis	Julie	manager
11	Davis	Julie	manager

sql - delete command(s)

In previous chapter i have explained How To Use Subqueries Statement In SQL

now i am gonna eplain you How To Use DELETE Command In SQL

In the SQL world, databases, rows, and columns all have one thing in common: once a DELETE statement has been executed successfully against them, the data they once contained is lost forever! Be very careful with these commands and be sure to properly backup all data before proceeding with any type of DELETE command(s).

SQL offers several ways to tackle data deletion. Here are the differences.

SQL Delete Commands:

DELETE - Deletes any number of rows from a data object.
DROP - Removes table columns, tables, and all data objects SQL applications.
TRUNCATE - Empties out data without removing the object itself.

sql - delete

DELETE queries work much like UPDATE queries and like UPDATE, it is much advised to always use a WHERE condition when running any delete query or else you risk deleting too much data.

SQL Delete Query:

USE mydatabase;

DELETE 
FROM orders
WHERE customer = 'A+Maintenance';

SQL Results:

1 Row(s) affected

sql - truncate

SQL TRUNCATE is the fastest way to remove all data from a SQL table, leaving nothing but an empty shell. You might choose to use this command when all the data inside of a table needs to be removed but you'd like the table column definitions to remain intact.

SQL Truncate Table Code:

USE mydatabase;

TRUNCATE TABLE orders;

NOTE: Executing the command above will empty your table data and you will lose this data forever! If you plan on following along do not execute this query.

sql - drop

SQL DROP is another command that removes data from the data store. The DROP command must be performed on SQL objects including databases, tables, table columns, and SQL views. Dropping any of these objects removes them completely from your SQL application and all data contained in any of the data objects dropped are lost forever.

SQL Drop Examples:

USE mydatabase;

DROP TABLE orders;
DROP DATABASE mydatabase;
DROP VIEW viewname;
DROP INDEX orders.indexname;

-- FOR USE WITH ALTER COMMANDS
DROP COLUMN column_name
DROP FOREIGN KEY (foreign_key_name)

The above example also includes the syntax to drop table columns and foreign keys. These items are outlined in the SQL ALTER lesson.

sql - dateadd()

In previous chapter i have explained How To Use DELETE Command In SQL

now i am gonna eplain you How To Use DATEADD() Function In SQL

DATEADD() is the SQL function used to add and increment date values. Hours, minutes, months, and days can be added to any date value. In fact, dates can be added based on any type of date part discussed in the SQL DATEPART()lesson.

SQL Code:

USE mydatabase;

SELECT DATEADD(year, 1, getdate()) AS "+1 Year";

SQL Results:

+1 Year

2009-06-31 00:00:00.000

This example shows how to use DATEADD() to take a specified date value and increment it by the 'year' date part. By replacing the middle parameter with a negative value, we can utilize the same DATEADD() function to subtract dates as well.

SQL Code:

USE mydatabase;

SELECT DATEADD(day,-1, '2006-06-01') AS "-1 Day";

SQL Results:

-1 Day

2006-05-31 00:00:00.000

In each example, SQL is able to perform a calculation on each date value based on a timestamp, and after the calculation, a timestamp value returned. Also note that the date parameter can be based on another SQL function or the result of a subquery.

SQL Code:

USE mydatabase;

SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days";

SQL Results:

-30 Days

2008-07-17 00:00:00.000

Here we have now constructed a very useful, dynamic statement pulling the most current order (MAX) in the orders table, and we've been able to subtract one day from that value. While this information does not directly prove useful, if we take this query one step further and place this statement in a WHERE as a subquery, we should be more satisfied with the results.

SQL Code:

USE mydatabase;

SELECT * 
FROM orders
WHERE day_of_order >
(SELECT DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30 Days");

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Hanging Files	11
2	Tizag	2008-08-01 00:00:00.000	Stapler	3
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5
5	Tizag	2008-07-25 00:00:00.000	19" LCD Screen	5
6	Tizag	2008-07-25 00:00:00.000	HP Printer	4

By placing this calculated date in the WHERE clause, we were able to pull all the records that have happened within 30 days of the most recent order (2008-07-17 00:00:00.000). We are able to query the orders table and request this information with a dynamic query that will yield different results as new orders are placed and time goes by.

sql - datepart

In previous chapter i have explained How To Use DATEADD() Function In SQL

now i am gonna eplain you How To Use DATEPART() Function In SQL

DATEPART() is a SQL function used to extract all kinds of date information from timestamps, and it is a function that is unique to Microsoft's SQL Server Application.

SQL Datepart:

USE mydatabase;

SELECT DATEPART(year, '2007-06-01') AS "Year";

SQL Results:

Year

2007

DATEPART() requires 2 parameters separated by a comma (,). The first parameter specifies what type of date data will be extracted, and the second parameter is a timestamp value.

SQL Datepart:

USE mydatabase;

SELECT DATEPART(year, '2007-06-01') AS "Year",
    DATEPART(month, '2007-06-01') AS "Month",
    DATEPART(day, '2007-06-01') AS "Day",
    DATEPART(dayofyear, '2007-06-01') AS "DayofYear",
    DATEPART(weekday, '2007-06-01') AS "Weekday";

SQL Results:

Year	Month	Day	DayofYear	Weekday
2007	6	1	152	6

Datepart Abbreviation Chart:

DatePart	Abbreviation	Example
year	yy, yyyy	DATEPART(yy, getdate())
quarter	qq, q	DATEPART(qq, getdate())
month	mm, m	DATEPART(mm, getdate())
dayofyear	dy, y	DATEPART(dy, getdate())
day	dd, d	DATEPART(dd, getdate())
week	wk, ww	DATEPART(wk, getdate())
weekday	dw	DATEPART(dw, getdate())
hour	hh	DATEPART(hh, getdate())
minute	mi	DATEPART(mi, getdate())
second	ss	DATEPART(ss, getdate())
millisecond	ms	DATEPART(ms, getdate())