Thread: For those of you who know SQL

Here's some sample SQL commands

create database RealtyCenter;

use RealtyCenter;

create table Customers
{
customerID int unsigned not null auto_increment primary key,
contactID int unsigned not null
};

create table Orders
{
orderID int unsigned not null auto_increment primary key,
customerID int unsigned not null,
amount float(6,2),
date date not null,
order_status char(10),
shipFName char(20) not null,
shipLName char(20) not null,
shipAddr char(40) not null,
shipCity char(20) not null,
shipState char(20),
shipZip char(10),
shipCountry char(20) not null
};

-------------------------------

sometimes the syntax is different like in mySQL requires parenthese instead of brackets and also accent marks around table/database names.

create table `Customers`
(
`customerID` int unsigned not null auto_increment primary key,
`contactID` int unsigned not null
)

create table `Orders`
(
`orderID` int unsigned not null auto_increment primary key,
`customerID` int unsigned not null,
`amount` float(6,2),
`date` date not null,
`orderStatus` char(10),
`shipFName` char(20) not null,
`shipLName` char(20) not null,
`shipAddr` char(40) not null,
`shipCity` char(20) not null,
`shipState` char(20),
`shipZip` char(10),
`shipCountry` char(20) not null
)

create table `Properties`
(
`propID` int unsigned not null auto_increment primary key,
`MLSID` char(40),
``
);

------------------------------------
When creating the tables and columns you can only say whether that value can have or not have a NULL value - you need to do conditional processing either when u insert the data or it really depends on the database your using as to how it will allow u to process that.

<Disclaimer: This is Oracle, maybe the functionality has another name under SQL Server. I'm not sure if it exists at all in mySQL>

When you create a table, you can create constraints like "can be null" or "may never be null". However, this cannot be conditionals dependent on table data. When you need data dependent checks, you use functions. In Oracle they are called Triggers, because these special functions get triggered on custom events. You want a check whenever a row of data is about to be changed and want the check to fail if the data of two columns does not match your criteria. Your Trigger therefore has to fire "BEFORE UPDATE OR INSERT" for you to check data before it gets into your table.
I suggest you get a good manual for your database, because the language you write the function in is different for most systems. If you happen to use Oracle, post it and I'll help you with the rest of the syntax.

Code:

CREATE OR REPLACE TRIGGER null_only_if_single_trigger BEFORE UPDATE OR INSERT ON phone_call
FOR EACH ROW
BEGIN
IF :new.call_type <> 'Single' AND :new.score IS NULL THEN
raise_application_error(-20512,'You cannot insert NULL scores for single calls');
END IF;
END;
/

Not tested, but it should work this way.

Okay.... Now that i've seen that...
SQL is evil!
Im gonna drop the F-BOMB now.