Email, Rate, Bookmark This

 After contributing to thousands of requests for programming help online and in person I have many theories and ideas about how people should learn programming and how they should consider the problems that they find themselves in.  This post is the first of many that will look into commonly asked problems and give tips that if read should solve some of them. 

Much of my experience centers around SQL databases and as such is one of the areas where I think people should understand the central ideas before they begin to ask questions of others. Conceptually, there are ten main keywords in all variants of the SQL language that are used to create alter and destroy tables and databases; and to  input, modify and remove data from those tables.

You are only ready to progress with your SQL if you can read this whole article and be able to identify the words below, write more than is written, explain what they mean and use them in your database of choice without reference to other sources.  (most of my examples will be aimed at ANSI standard SQL or mySQL)  There is much more that can be read on these topics but below are the bare necessities needed to get you by.

 

1. CREATE TABLE

Before you can start with working with data, you need a container to store it in.  The container in an SQL database is called a table.  This is a series of rows (where each item of data is stored) and columns which are of various data-types that are used to identify the particular row and contain the data.  The CREATE statement enables a table to be made, specifying any rules about what can be contained in the table (and how it can be indexed). 

As this is a basic guide; I will just outline a simple create statement and what the components mean;

CREATE TABLE your_table_name (a INT NOT NULL, b VARCHAR(20));

This will create a table called your_table_name with two columns, a which is an integer, which will not allow any null values and b which is a string that can contain characters up to a length of 20.

Note; there are other options,

• you can have arguments that specify to create the table if it does not already exist.
• Tables can be created that are like other tables (copying their definition)
• Tables can be created by using a select statement and copying the data returned from it
• Temporary tables are those that are not written to the disk and kept permanently like tables without the temporary keyword, and are not designed to be permanent.
• Constraints and comments can be added to columns and to the table as a whole
• A Primary key and other indexes can be added; limiting duplication of data and increasing its searchability.
• Foreign keys can be added restricting values to be inserted depending on the values contained within other tables.
• There are many different datatypes that can be used to hold data for columns
• Default values can be set for data.
• If a table already exists then you can not create it - and an error may occur.
• Some databases need to specify the owner as part of the table name (dbo for example ie dbo.your_table_name)

2. ALTER TABLE

Once a table has been created, it may become necessary to change how data can be input into the table or how the data is stored.  Alter table is the mechanism through which these changes

ALTER TABLE your_table_name ADD ColumnName VARCHAR(10) NOT NULL;

ALTER TABLE your_table_name DROP ColumnName;

ALTER TABLE your_table_name DROP PRIMARY KEY;

If you are adding columns or adding things to the table then the same rules apply as for Create.  The same declaration options can be given, as with the same index types and restrictions.

You can remove columns by referring to them by name.

You can remove indexes and restrictions by specifying that in the drop.

Some Things To Note:

• You can not normally have two columns with the same name (this is relevant to create and alter)
• You may have problems removing certain columns if they area referenced by other foreign keys
• Problems can occur when indexes depend on columns intended to be dropped.
• Indexes can not necessarily be added if the data contained in the table does not reflect the restrictions imposed by the change.

3. DROP TABLE

DROP TABLE your_table_name;

DROP TABLE IF EXISTS your_table_name; 

Discarding tables is as easy as referring to the table you want to remove and saying drop table.  Make sure you have permissions to drop the table or it may not go that easily.  Some SQL databases support the IF exists syntax meaning that it will not give an error if the table does not exist and you try to remove it, but if it does exist then it is removed.  Similarly if you are removing tables that have dependencies upon them ie foreign keys then they may not go that quickly.

4. INSERT INTO

Normally, the point of all your SQL database skills is to work with data.  There are many statements that enable the population of data, but the most commonly used would be the insert statement.  This does exactly what it implies, it inserts rows of data into the table.  There are three major variants.  The first variation specifies the columns that data will be inserted into, including their order and the other specifies the data that will go into those columns.

INSERT INTO your_table_name ( first_column_name, another_column_name, …. ) 

VALUES ( first_value, second_value, …. )

Another method of isolating the data to be inserted is by referring to data contained within the database already.  This uses a normal select statement as defined below:

INSERT INTO your_table_name ( col_name1, col_name2, …. )  SELECT … 

The final method is to use a statement that sets each column’s value that needs to be altered to another values using an equals assignment.  I would not recommend using this method.

Things To Note:

• Some SQL databases allow the insertion of multiple rows in the execution of one statement.
• Inserting data is controlled by dependencies, checks and relationships and if the data fails these checks then errors may result.
• Make sure the data is in the type defined by the column type.
• Not all columns need necessarily be populated
• There are many functions that can manipulate the form of the data.
• Data needs to be in a format that can be converted to the correct data type - things like ‘23′ may be interpreted differently than 23
• Inverted commas within the data can present insertion problems and there are functions that can handle this.
• Always validate any data that comes from untrusted sources (ie users)
• If AutoIncrement or Identity fields are not specified then they will generally default to the highest value in the table and add one.
• Note that conversion functions may also need to be used if selecting from columns in a database that are not the same as their destination.
• Columns can be selected in a different order than they appear in the destination table also - not all need to be specified.
• Data can be changed from the input before being inserted, using functions inherent in sql for the purpose.
• You can generally use parameters to insert values into tables.

5. UPDATE

UPDATE your_table_name  SET first_column = value1, second_column = value2, ….  WHERE criteriaExists

Columns of data can contain information that is incorrect or needs to be modified.  The mechanism that SQL gives you is the update statement.  Typically this will involve only modifying the data in one table, even if other tables are used for criteria.  Above will update the table called your_table_name and assign the value in the first column equal to "value1" and second_column to value2 where there is specific criteria (elaborated in where below).  If there are no criteria then every row is updated.

Things To Note:

• Limits can be applied so only a given number of rows is effected
• Other tables can be joined in the where condition
• It is not generally a good idea to alias the tables in the update statement.
• The value does not have to be fixed and can refer to a manipulation of the current or different columns or constants.
• Not all columns need be effected by the update.

6. DELETE

DELETE FROM your_table_name WHERE criteriaExists

Removing rows from tables is achieved using the delete statement.  This is as simple as identifying the table and criteria the rows should meet and then specifying that you want to delete, using a statement similar to above.  The criteria is explained below.

Things To Note:

• Limits can be applied specifying a maximum number of rows to be removed
• Deletes can have problems referring to other rows within the same table and as such temporary tables may need to be utilised.
• Deletes normally can only effect one table at a time (even if another is linked for criteria purposes)
• Deletion can have problems if they remove foreign key dependencies relied upon with other tables.

 

7. SELECT

 

A normal select statement will appear similar to the following

SELECT   definition_for_First_column as fc, t1.second_column, ….  FROM tableName as t1  WHERE criteria_exists

 

Select simply identifys the data to be displayed across the columns.  Note that this may appear more than once in a query if you have an embedded query (sub query) either as part of a column definition (if it only returns one column and one row) or as part of the from (using the query like it was a table) or in the where condition (using it to prove or disprove a set of criteria)

Things To Note:

 

• Often mistakes occur because there is not one data definition between each (except for the last)
• Some databases have a 256 or 1024 column limit
• The DISTINCT keyword after select will only give unique rows (the whole row must be unique)
• GROUP BY at the end of the statement can effect what can be displayed in the select
• Columns do not have to be from tables, they can be constants
• Columns can also represent mathematics between constants
• Columns can be passed through functions
• It is not good practice to have multiple columns with the same name
• Columns can be aliased by using a word to give it a name after a space after the column definition ie (tableName.t1 as field1 or by doing tablename.t2 xxx) which would create two columns called field1 (1st example) and xxx as the second example
• The tablename does not necessarily need to be used to address a field, provided the field name is unique to that table or subquery
• There does not necessarily need a from or where criteria depending on the database

8. FROM

  FROM table_name

  FROM (subquery)

  This is normally seen where a statement requires information pertaining to a table or a set of data.  In first instances you may only want to use this relating to given tables, in more advanced scenarios you can select from an embedded query.  This is amongst the most straight forward - where basically everything after the from must be either a table, view or something that appears like a table or view (ie has rows and columns)

Things To Note:

• There can be multiple tables or subqueries in the from definition
• Tables or subqueries can be given names ie FROM table_name as t1 would mean wherever t1 was referenced it referenced that table
• The same table or subquery can be included multiple times and each is treated independently
• Everything listed as part of the from clause will impact the query (even if it is not specifically addressed) as there is joining between each of these data sets.

9. WHERE

 Ordinarily You do not want to see every piece of data in a database.  Restricting what is returned, based upon criteria is the whole point of the where keyword.

In essence Where statements read like their English equivalents.  Such that for each row - the database asks, does applying this statement return true, if so then display the row - otherwise don’t.  You can have or statements and use complex logic but every where statement boils down this simplification.

Examples such as:

WHERE  t1.currency = "AUD"

WHERE  t1.field1 > t2.field3

WHERE (t1.field1 + t2.field3) = (t2.field2 * t2.field7)

WHERE your_table_name.firstName = "Andrew" or your_table_name.firstName = "Becky"

WHERE your_table_name.Gender = "Male" and your_table_name.age between 18 and 25

 If you can read these statements they seem to make sense.  The first will only return values that are in Australian Dollars and the second will only show rows where a given field is greater than another one.  Any of these statements must evaluate to true to return rows. 

Things To Note:

•  There are many functions that can be used to evaluate to true or false in a where condition - signs such as > >= < <= / \ != in() (+)between % and many more can be used - if you are unsure and they are in a where condition then look up what they mean, for the statement to work they will always be used in a manner that can give either a true or a false after they are evaluated.

• Some databases join tables in their where conditions and others dont.
• It is possible to have statements that will never return true ie WHERE 1 != 1 will always be false as 1 is never going not to be equal to 1 (some databases use <> for not equals)
• Sub Queries can be returned to check if a particular value does or does not exist within that sub query
• Errors may occur if you are trying to compare values that are not of the same type ie "1" is unequal to 1 in many databases as the first is the character and the second is the value
• Where conditions are normally evaluated using BOMDAS ie Brackets Of Multiply Divide Add and Subtract.
• Where statements may use the quirk that true and false are treated as numbers in some databases -ie false may = 0 and true is any other number
• Conditional logic may be used to evaluate conditions - ie iif, if and case statements

 

Conclusion:

Understanding these statements is important.  There are many nuances and subtleties that were not covered by this introductory document.  If you want one or more details examined in more depth, please contact me and I will write another post addressing your concerns.

SQL was something I hated when first learning it, nowadays I think its possibilities and use are awesome.

If you liked this post please comment below, share it with friends or on social networks and Subscribe to my RSS Feed

%DIGG%   Subscribe To The Terabell Blog Via RSS