Comparing Dates

Based from Professional PHP Developers http://groups.google.com/group/professional-php/browse_thread/thread/3472388d5af249db post question.

This is the sample season_dates table:

This is the sample check-in and check-out (check_in_out) table:

This is the SQL query:

SELECT
 *,
 IF(`cio`.`out` > `sd`.`ends`,(DATEDIFF(`sd`.`starts`,`cio`.`in`) + DATEDIFF(`cio`.`out`,`sd`.`ends`)),DATEDIFF(`sd`.`starts`,`cio`.`in`)) AS `low`,
 
 IF(`cio`.`out` > `sd`.`ends`,(DATEDIFF(`cio`.`out`, `sd`.`starts`) - DATEDIFF(`cio`.`out`,`sd`.`ends`)), DATEDIFF(`cio`.`out`, `sd`.`starts`)) AS `high`
 
 FROM
 `check_in_out` AS `cio`,
 `season_dates` AS `sd`
WHERE
 (`cio`.`in` >= `sd`.`starts` AND `cio`.`in` <= `sd`.`ends`)
 OR 
 (`cio`.`out` >= `sd`.`starts` AND `cio`.`out` <= `sd`.`ends`)

This is the query result:

Read more!

Tutorial for SQL - SQL Syntax

Tutorial for SQL will teach about SQL Syntax is the first thing you need to learn before jumping to SQL commands. Below are the list of SQL Syntax. This will serve as your reference to any SQL Query you'll run. Please Bookmark this page (Ctrl + D) to your browser for quick reference as a SQL documentation.

There are two (2) types of SQL Statements:

1. Data Manipulation Statements
   
   • CREATE DATABASE Syntax
   • CREATE INDEX Syntax
   • CREATE TABLE Syntax
   • ALTER DATABASE Syntax
   • ALTER TABLE Syntax
   • RENAME DATABASE Syntax
   • RENAME TABLE Syntax
   • DROP DATABASE Syntax
   • DROP INDEX Syntax
   • DROP TABLE Syntax
   
   
   
   
2. Data Definition Statements
   
   • INSERT Syntax
   • SELECT Syntax
   • UPDATE Syntax
   • TRUNCATE Syntax
   • DELETE Syntax
   
   

---

1.1 CREATE DATABASE Syntax. This will create a database.

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

Examples:

• CREATE DATABASE `students`; // Creates a database
• CREATE DATABASE IF NOT EXISTS `students`; // Creates a database if database name doesn't exist

2.2 SELECT Syntax

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FOR UPDATE | LOCK IN SHARE MODE]]

Read more!

SQL Introduction

SQL (stands for Structured Query Language) is a standard language for accessing and manipulating databases.

On this SQL tutorial, I will teach you how to use SQL to access and manipulate data in your MySQL Server and to give an idea on how to develop a dynamic website application using MySQL.

SQL can retrieve, insert, update, delete, create database, create table on database, and  a lot more...

I hope this free SQL tutorial / SQL references will help you learn/understand what is really SQL. Enjoy! Learning is fun. :)

Read more!