SQL Database Development & Programming
SQL, or Structured Query Language, is the standard language used by programmers to talk to databases.
I've been using SQL to control databases since my degree days, when it was taught in various modules including a comprehensive module on relational databases. A relational database, simply put, is one that can have various tables interlinked by shared "keys", and which - without exception - uses SQL as its primary channel of communication and control.
Relational databases powered by Structured Query Language have been around for many decades and have formed the backbone of vast corporate and banking systems since very early in the history of large-scale computing. The use of SQL-oriented databases in web applications is as old as the web itself. It's no surprise therefore that I've been using SQL myself to build web applications for the full 8 years that I have been involved in web development.
Every single project in my portfolio involves SQL. It's almost too common to mention, which is why I may have omitted it from some of the entries.
There are several variants of the language each relating to a particular database system (e.g. T-SQL or Transact-SQL, from SQLServer), but the core language is the same for all of them.
SQL consists of commands like SELECT, INSERT, UPDATE and DELETE which allow the programmer to manipulate and retrieve data from a centralised data store. The language in its advanced form also contains a full suite of management commands for creating, populating and managing databases.
The most common database systems are MySQL, SQL Server, Oracle, PostgreSQL and MS Access. They all use SQL as their primary means of data manipulation. MS SQL Server, also known as MSSQL, SQL2000, SQLServer etc is one of Microsoft's premier products and can be very expensive for large deployments. However free single-user and single-web site versions are available called SQL Server 2005 Developer Edition, and SQL Server 2000 Developer Edition.
A common mistake is to think that the term "SQL" refers to a specific database system but this is incorrect. The general assumption when this mistake is made is that SQL means Microsoft SQL Server, and in this regard the term "SQL" has become a kind of industry shorthand for "SQL Server" - misleading but there you go.
SQL Server Enterprise Manager is a tool used to connect to and manage an SQL Server database remotely without resorting to actual SQL commands, although these can be used instead for all administration tasks if preferred.
In web applications, the web-server is usually scripted or programmed to connect to the database system - which often resides on a separate machine from the web server itself - in order to retrieve text content and other data needed to build each web page that is requested.
This connection is usually done via a system called Open Database Connectivity (ODBC) which is a protocol over which SQL commands can be transmitted to the database and datasets returned to the application. This interchange can happen over a network and even over the internet, allowing for physical separation between the database server and its client application. The catch here though is that if the connection between the database and web servers is not super-fast then performance can suffer greatly, and timeout errors can occur.
The more advanced implementations of SQL provide a facility called Stored Procedures. These allow the developer to create and thoroughly test large chunks of SQL and have them stored within the database itself. They can then be triggered by an external request, and are thought to run significantly faster than if the full SQL command is sent to the database each time it is needed.
Another advanced feature are triggers, which are pieces of SQL code that can be made to execute automatically when certain events occur within the database (e.g. a record being updated, etc.)
SQL is a computationally complete language, which means that it can be proven to be equivalent and equal in power to any other computer language. In other words, any program you can think of can theoretically be rebuilt purely using a relational database and SQL. This in spite of its significant difference in syntax from most other languages and despite its deceptively simple appearence to the first-time user.
Stored Procedures, triggers, and the complexity and "computational completeness" of the SQL language, mean that many web developers choose to write their database-oriented applications almost entirely in SQL from within the database server's control area, as a system of interlinked stored procedures and triggers. This approach makes for extremely robust and scalable web applications.
The job of an SQL developer is to design the overall database structure, create the necessary stored procedures, and where applicable code the web interface to the database.
![http://www.gotomeeting.com]()
![http://www.mozy.com]()
I've been using SQL to control databases since my degree days, when it was taught in various modules including a comprehensive module on relational databases. A relational database, simply put, is one that can have various tables interlinked by shared "keys", and which - without exception - uses SQL as its primary channel of communication and control.
Relational databases powered by Structured Query Language have been around for many decades and have formed the backbone of vast corporate and banking systems since very early in the history of large-scale computing. The use of SQL-oriented databases in web applications is as old as the web itself. It's no surprise therefore that I've been using SQL myself to build web applications for the full 8 years that I have been involved in web development.
Every single project in my portfolio involves SQL. It's almost too common to mention, which is why I may have omitted it from some of the entries.
SQL variants
There are several variants of the language each relating to a particular database system (e.g. T-SQL or Transact-SQL, from SQLServer), but the core language is the same for all of them.
SQL syntax
SQL consists of commands like SELECT, INSERT, UPDATE and DELETE which allow the programmer to manipulate and retrieve data from a centralised data store. The language in its advanced form also contains a full suite of management commands for creating, populating and managing databases.
Common SQL databases
The most common database systems are MySQL, SQL Server, Oracle, PostgreSQL and MS Access. They all use SQL as their primary means of data manipulation. MS SQL Server, also known as MSSQL, SQL2000, SQLServer etc is one of Microsoft's premier products and can be very expensive for large deployments. However free single-user and single-web site versions are available called SQL Server 2005 Developer Edition, and SQL Server 2000 Developer Edition.
A common mistake is to think that the term "SQL" refers to a specific database system but this is incorrect. The general assumption when this mistake is made is that SQL means Microsoft SQL Server, and in this regard the term "SQL" has become a kind of industry shorthand for "SQL Server" - misleading but there you go.
SQL Server Enterprise Manager
SQL Server Enterprise Manager is a tool used to connect to and manage an SQL Server database remotely without resorting to actual SQL commands, although these can be used instead for all administration tasks if preferred.
SQL and web applications
In web applications, the web-server is usually scripted or programmed to connect to the database system - which often resides on a separate machine from the web server itself - in order to retrieve text content and other data needed to build each web page that is requested.
ODBC
This connection is usually done via a system called Open Database Connectivity (ODBC) which is a protocol over which SQL commands can be transmitted to the database and datasets returned to the application. This interchange can happen over a network and even over the internet, allowing for physical separation between the database server and its client application. The catch here though is that if the connection between the database and web servers is not super-fast then performance can suffer greatly, and timeout errors can occur.
SQL stored procedures
The more advanced implementations of SQL provide a facility called Stored Procedures. These allow the developer to create and thoroughly test large chunks of SQL and have them stored within the database itself. They can then be triggered by an external request, and are thought to run significantly faster than if the full SQL command is sent to the database each time it is needed.
SQL triggers
Another advanced feature are triggers, which are pieces of SQL code that can be made to execute automatically when certain events occur within the database (e.g. a record being updated, etc.)
Computational completeness
SQL is a computationally complete language, which means that it can be proven to be equivalent and equal in power to any other computer language. In other words, any program you can think of can theoretically be rebuilt purely using a relational database and SQL. This in spite of its significant difference in syntax from most other languages and despite its deceptively simple appearence to the first-time user.
Scalability
Stored Procedures, triggers, and the complexity and "computational completeness" of the SQL language, mean that many web developers choose to write their database-oriented applications almost entirely in SQL from within the database server's control area, as a system of interlinked stored procedures and triggers. This approach makes for extremely robust and scalable web applications.
SQL development
The job of an SQL developer is to design the overall database structure, create the necessary stored procedures, and where applicable code the web interface to the database.
- •Services: Summary
- •Services: Server-side
- •Services: Client-side
- •Services: ASP Scripting
- •Services: SQL Databases
- •Services: JavaScript
- •Services: ASP.NET / aspx
- •Services: Evolve eCommerce
Services: SQL Databases
