Home

SQL Commands

SQL / mySQL and PostgreSQL commands

Some commands - mainly to remind me in case I forget.

NOTE
Sometimes there is a difference between SQL, mySQL and PostgreSQL command.


PostgreSQL (I think) is case-sensitive in the WHERE CLAUSE
So if using PostgreSQL you should consider using, eg...

WHERE LOWER(myName) = 'gary kirby'
or
WHERE UPPER(myName) = 'GARY KIRBY'
or you can even say
WHERE UPPER(myName) = UPPER('Gary kirby')

in SQL & mySQL you can just say
WHERE myName = 'Gary Kirby'
and you would get back all Gary Kirby, GARY KIRBY and gary Kirby records.


And see the document in w3schools for more on SQL Tutorial - then look at its menu on the left of the page for more SQL Options.

SQL Commands

Some commands - mainly to remind me in case I forget.

And see the document in w3schools for more on SQL Tutorial - then look at its menu on the left of the page for more SQL Options.

Read in from a table

SELECT * FROM myTable
   WHERE myID > 100
   ORDER BY myName, myID;

Read in from a table using BETWEEN

SELECT * FROM myTable
   WHERE myName BETWEEN 'A' and 'J'
   ORDER BY myName, myID;
this will find all myName's that start with the letter A and go up to the letter J (but NOT J followed by other letters, eg Not Ja, Jb etc).
...may be easier to explain by...
Where myAge BETWEEN 18 and 49 - will give all ages from 18 to 49 inclusive.

Create a table

CREATE TABLE myTABLE (
   myID INTEGER,
   myName TEXT,
   myAge INTEGER
);

Create a table with constraints

CREATE TABLE myTABLE (
   myID INTEGER PRIMARY KEY,
   myName TEXT UNIQUE,
   myAge INTEGER NOT NULL,
   mySex VARCHAR(1),
   myNickName VARCHAR(20) 'Not Applicable',
   myDecimal decimal(7, 2),
   myBirthday DATE
);
(see Datatypes for more info)
(Note decimal(7,2) means 5 digits to left of decimal point and 2 after, total 7 digits)

Populate a table

INSERT INTO myTable (myID, myName, myAge)
   VALUES (1, 'Gary Kirby', 21);

Add a new field to a table

ALTER TABLE myTable
   ADD COLUMN mySex TEXT;



Read in from a table using IN

SELECT * FROM myTable
   WHERE myName in ('Gary Kirby', 'Tilly Kirby)';

Update a field in a table

UPDATE myTable
   SET mySex='M'
   WHERE myName='Gary Kirby';

Delete a row from a table

DELETE FROM myTable
   WHERE myName="Gary Kirby";
or
DELETE FROM myTable
   WHERE mySex IS NULL;

Select first 100 records only

SELECT TOP 100 *
   FROM myTable;
(SQL Command)
or
SELECT *
   FROM myTable
   LIMIT 100;
(MySQL command)

Add a constraint to a column

ALTER TABLE myTable
   ALTER COLUMN mySex
   SET NOT NULL;
(this will ADD the constraint 'NOT NULL' to 'mySex')

Drop a constraint from a column

ALTER TABLE myTable
   ALTER COLUMN mySex
   DROP NOT NULL;
(this will DROP the constraint 'NOT NULL' from 'mySex')

make a field into a Primary Key

ALTER TABLE myTable
   ADD PRIMARY KEY (myID);


Top

Check Constraints

Here is a document covering PostgreSQL constraints which could be worth taking a look at.


In some situations, we might want to establish specific rules to determine what makes a row valid.
For example, we might want to ensure that the myAge column is:
An Integer
NOT NULL
A Positive value greater than 18 AND less than 80

While the first two rules can be handled by the datatype and NOT NULL constraints the last one will need some additional logic to check the value.

To do this we need to include the ADD CHECK constraint.

For example...

add a CHECK CONSTRAINT to an existing table

ALTER TABLE myTable
ADD CHECK (myAge > 18);

add a CHECK CONSTRAINT while CREATING A TABLE

CREATE TABLE myTABLE (
   myID INTEGER PRIMARY KEY,
   myName TEXT UNIQUE,
   myAge INTEGER NOT NULL,
   mySex VARCHAR(1),
   CHECK (myAge > 18 AND myAge < 80)
);

The above will allow the system to assign its own 'constraint name' to the CHECK - although this is not really recommended.
IDEALLY you should give it a 'constraint name' yourself.

Eg...

CREATE TABLE myTABLE (
   myID INTEGER PRIMARY KEY,
   myName TEXT UNIQUE,
   myAge INTEGER NOT NULL,
   mySex VARCHAR(1),
    CONSTRAINT CK_myTable_Age CHECK (myAge > 18 AND myAge < 80)
);

Creating unique constraints

As well as making individual fields in a table unique by creating the table, eg...

CREATE TABLE myTest (
   id INTEGER PRIMARY KEY,
   email VARCHAR(25) UNIQUE,
   name VARCHAR(25)
);

Or making a field unique AFTER the table was created by, eg...

ALTER TABLE myTest
   ADD UNIQUE (email);
NOTE... This only works if there are NO DUPLICATES already in the table in the email field.

You can also add a CONSTRAINT that checks multiple fields to make sure that the combination of the fields result in a unique entry, eg...

CREATE TABLE myTest (
   id INTEGER PRIMARY KEY,
   email VARCHAR(25),
   name VARCHAR(25),
   UNIQUE (email, name);
);
this means that email + name, when combined, must be a unique result.

Top

Foreign Keys

See this document from w3schools on Foreign Keys as there may be slightly different terminology for SQL and MySQL.

When information in tables depend upon records having to exist in a different table - this is known as Referential Integrity.

For example...

If we have two tables registrations and talks
and people register to visit one of the talks being made then we know that the table registrations must reference the talks table to make sure the talk actually exists.

In this case the talks table will be classed as a parent of the registrations (child) table.

Referential integrity can be enforced by adding a FOREIGN KEY on the child table that references the primary key of a parent table.

Therefore we may have a scenario where the
talks table has a Primary Key called id
and the registrations table has a field called talk_id that needs to be the same value as one of the talks.id values.
So we would use foreign Keys on the registrations table like so...

ALTER TABLE registrations
ADD FOREIGN KEY (talk_id)
REFERENCES talks (id);

Or if creating the registrations table from scratch we would...

CREATE TABLE registrations (
   id INTEGER PRIMARY KEY NOT NULL,
   talk_id INTEGER NOT NULL,
   name VARCHAR(25),
   FOREIGN KEY (talk_id) REFERENCES talks (id)
);

Other SQL Commands

There are other commands that I won't go into detail here
but are covered in the w3schools SQL Commands site - Found Here - then look at its menu on the left of the page for more SQL Options.

NOTE...
Only one ALTER COLUMN command can be used at any one time.

But you can ADD more than one field at a time, eg...
ALTER TABLE table_name
   ADD column_1 column_definition,
      column_2 column_definition,
      ...
      column_n column_definition;


And...some other commands that may be useful (?) like:-

DELETE / UPDATE CASCADE
Some information can be found Here.
It causes the updates or deletes to automatically be applied to any child tables.


'LIKE' on the WHERE clause
LIKE can be a useful operator when you want to compare similar values.
For instance, this movie could be stored as ‘Se7en’ or ‘Seven’.
So you could say...

SELECT *
FROM movies
WHERE title LIKE 'se_en'

(the search on the where clause is NOT case sensitive (PostgreSQL is I think).
underscore = 1 single chr
% = multiple chr's (2 or more))
Visit the w3schools document here for SQL LIKE Operator for examples

Top

Other SQL commands (more)

Using CASE in SELECT

SELECT myID, myName,
   CASE
      WHEN myAge > 70 THEN 'pensioner'
      WHEN myAge > 17 THEN 'Adult'
      ELSE 'Child'
   END as 'Category'
FROM myTable;
this will create a column head 'Category' and fill with Pensioner, Adult or Child

Using DISTINCT in SELECT

SELECT DISTINCT myID, myName
FROM myTable;
this will display rows without duplicating the details displayed.
Note... in MS SQL you can also use DISTINCTROW which looks at the whole record and not just the displayed details. You could get duplicates displayed with this as some data not being displayed may be different in the table.

Using COUNT, GROUP BY & HAVING when reading

SELECT count(myID) as Quantity, myAge
FROM myTable
GROUP BY MyAge
HAVING MyAge > 17;
this will count the number of records found in each myAge value and show the count followed by the myAge value for anyone over 17.

SQL Calculations (aka Aggregates)

Common aggregates are:-

COUNT() - count the number of rows
SUM() - sums the values in a column
MAX() - gets the largest value
MIN() - gets the lowest value
AVG() - the average of the values in a column
ROUND(a, n) - round the values (to n places) in the column

Eg...

SELECT ROUND(myVal, 2) as myValRounded, *
FROM myTable

Top

Some commands entered in one of my 'tests'

Select * from parts;

ALTER TABLE parts
ALTER COLUMN code SET NOT NULL;
 
ALTER TABLE parts
ADD UNIQUE(code);

UPDATE parts
SET description='default'
WHERE description IS NULL; 

ALTER TABLE parts
ALTER COLUMN description SET NOT NULL;

INSERT INTO parts 
    (id, description, code, manufacturer_id) 
    values 
    (54, 'default', 'V1-009', 9);

ALTER TABLE reorder_options
ALTER COLUMN price_usd SET NOT NULL;

ALTER TABLE reorder_options
ALTER COLUMN quantity SET NOT NULL;

ALTER TABLE reorder_options
ADD CHECK (price_usd>0 and quantity>0);

ALTER TABLE reorder_options
ADD CHECK (
    ((price_usd/quantity)> 0.02) 
    and 
    ((price_usd/quantity)<25) 
);

ALTER TABLE parts
ADD PRIMARY KEY (id);

ALTER TABLE reorder_options
ADD FOREIGN KEY (part_id) REFERENCES parts (id);

ALTER TABLE locations
ADD CHECK (qty> 0);

ALTER TABLE locations
ADD UNIQUE (part_id, location);

ALTER TABLE locations
ADD FOREIGN KEY (part_id) REFERENCES parts (id);

ALTER TABLE parts
ADD FOREIGN KEY (manufacturer_id) 
    REFERENCES manufacturers (id);

INSERT INTO manufacturers (id, name) 
    values 
    (11, 'Pip-NNC Industrial');

Select * from manufacturers;

UPDATE parts
SET manufacturer_id = 11 
WHERE manufacturer_id=1 or manufacturer_id=2;

Select * from parts;
Top