Software And Web Development

Using and Understanding Sql Commands

Leigh Goessl's image for:
"Using and Understanding Sql Commands"
Image by: 

Structured Query Language, or more commonly known as SQL (pronounced "Sequel") is a powerful language when working in the database environment. It's a universal language, which means it's not linked exclusively to any one vendor or software application, which is handy because it allows levels of versatility.

In SQL, commands grouped together are often used to identify data tables and other aspects of the database. This is done through the use of Data Definition Language (Post, pg 146), in which the query responds to a business question that is presented to the database. Typically 4 questions are asked when building a query. The 4 questions relate to:

*Output viewed
*Information already known
*Tables involved
*The columns by which the tables involved are joined.

The basic structure of a SQL select command is SELECT, FROM, JOIN, and WHERE, so we'll start with defining these commands:

*SELECT. The purpose in using the SQL SELECT command is to retrieve data from a query. SELECT instructs the database management system in which fields (columns) to retrieve.

*FROM. FROM indicates which table(s) the information is pulled from

*JOIN. JOIN matches the conditions that bring the two tables together (also can use the command "INNER JOIN").

*WHERE. WHERE specifies the criteria which helps designate a precise answer to the question presented to the database.

Other commands such as LIKE and ORDER BY can supplement the standard statement in order to sort and/or gain more specific information, but the SELECT, FROM, JOIN, and WHERE are the standard commands used in statements.

The WHERE and HAVING statements are similar and the syntax is identical, but it's essential to note the differences between the two. WHERE gives you a return answer of every row in the original table by filtering information and specifying criteria when searching, whereas HAVING further specifies criteria by allowing inclusion and/or exclusion of certain groups (Forta, pg 78). This alleviates bringing back data that isn't useful to your query. While both commands have similar purposes, the main thing to remember is that WHERE returns filtered rows and HAVING filter groups. Since WHERE filters the rows, if the question needs grouping a HAVING statement can be written to further filter data according to group (this should follow a GROUP BY statement inserted). If both commands are used in the same query, the order of this is important because it can change calculated values.

CREATE, DELETE, UPDATE, and INSERT are other commonly used statements. Data Manipulation Language commands change data in the database as opposed to emphasis on retrieval with Data Definition Language. For instance, tables can be created and/or deleted using SQL. By inserting statements such as CREATE, DELETE, UPDATE and INSERT, you can manipulate your database or transfer data to other tables. Sometimes after the database is in use, that it may be more efficient to change some of the database structure around, and using SQL and data manipulation can accomplish this (Post Supplement, pg 77-78).

For instance, CREATE TABLE is a command that will do just as the statement implies, it creates a table, but CREATE can also be inserted as CREATE VIEW, which brings forth a new view of data or a saved query. DELETE eliminates data from a table in the database and UPDATE changes specified information in columns. DROP TABLE is a command that should be used cautiously as it will eliminate an entire table from a database. This command is not to be confused with DELETE which only eliminates particular rows. Be careful with using DROP.

SQL is a powerful method for the creation of queries as it is more straightforward and simplifies the statements within the query.

Forta, Ben, "Teach Yourself SQL in 10 minutes", SAMS Publishing, 2004
Post, Gerald, "Designing and Building Business Applications", 3rd Edition
Post, Gerald, "Database Management Systems", 3rd Edition

More about this author: Leigh Goessl

From Around the Web