Get your own Free HomepageSQL has emerged as one of the most important computer languages of the 21st century. This partly reflects the changing nature of the information-tech industry, as SQL is not a "programming" language, per se; it doesn't allow you to do general computerish things like a general-purpose programming language such as C or Pascal. Rather, SQL serves the relatively specific function of allowing you to work with databases. SQL is a language that allows you to make coded procedures to create, update, and display information stored in a database. This specific focus allows SQL to be arguably more simple to use and learn than most programming languages; certainly, SQL programmers tend to worry less about those odd niche coding styles that traditional programmers have had to worry about.
Anyway, here's a quick run-through of the most important SQL commands. Before we begin, I should note how to create an SQL query in Microsoft Access, one of the most popular SOHO (Small Office/Home Office) database packages out there. While command-line SQL interfaces are built into both Oracle and MySQL, getting Access to do SQL is a little less obvious, but not difficult. Begin by going into the "Queries" section of your database. Access provides two options: "Create query in Design view" and "Create query by using wizard". Double-click on "Create query in Design view", and Access' Design view will come up. From here, the view button on the toolbar becomes available, and if you click on the drop-down arrow on the right hand of this button, you can then click on "SQL View" to transform the query into a plain Notepad-style text box that you can type your SQL command into and save as its own query in the database.
Although I'll try to take this page through a logical sequence of creating a database and doing various things to it, I'll take a slight detour and cover the SELECT command first, simply because it is argubaly the single most important SQL command and has become sort of symbolic of the language, much as "Hello, world!" programs have become symbolic of beginning most programming languages.
(Note that although commands like SELECT are properly called "keywords" in the SQL sphere, I'll stubbornly call them "commands" since I'm of the old school and tend to think of computers in terms of how they work rather than how their concepts are structured. Also, note that although SQL is not case-sensitive, I'll try to stick to writing actual SQL keywords in ALL CAPS so you can more easily separate them from other parts of the commands.)
The SELECT command
SELECT simply retrieves and displays data from a database. For example, when first logging into a new database that you're not familiar with, you might want to look at all the tables that are in the database. Under Oracle, there is a special table maintained called TAB, which is a list of all tables in the database. You can view the contents of TAB with the following command:
SELECT * FROM tab;
This command simply says "show everything in the table called tab". (The asterisk is a fairly universal computer wildcard indicating "everything".) Note that this is a rather Oracle-specific command, and other databases generally don't auto-update a table called TAB, so you'll need to use another method to list the database's tables if you're on another system. For example, MySQL uses the non-SQL command show tables;.
Of course, there's more to the SELECT command than just listing everything in a table. Part of the point of a database is being able to filter information by certain criteria. This is where the WHERE clause comes in. As an example of how you'd use this clause, suppose you have a table called EMPLOYEES, and you want to list all the records in which the field called FirstName is "John". For a task like this, you could use the following command:
SELECT * FROM employees WHERE FirstName = "John";
You can specify several such criteria by simply ANDing them:
SELECT * FROM employees WHERE FirstName = "John" AND LastName = "Smith";
Another useful clause is ORDER BY. If you use the above command, you'll get only the results you want, but you'll probably still want to sort the results in a sensible way. You might want to sort them by last name, for example, so assuming that the employees' last names are stored in a field called LastName, you could achieve this goal with a line like the following:
SELECT * FROM employees WHERE FirstName = "John" ORDER BY LastName;
Of course, you're not required to use an asterisk; the place where I've been using the asterisk is where you list what fields you want to pull from the table. If you don't want to pull all fields, you can list which fields you do want to see. For example, suppose you only want to see the employee's last name and salary. You could then use a command like this:
SELECT LastName, Salary FROM employees WHERE FirstName = "John" ORDER BY LastName;
Okay, that's enough of the SELECT command for now. Let's now go on a journey where we actually create a new table from scratch, fill it with some data, and work with that data.
The first step, of course, is to create a table. We can do this with...
The CREATE TABLE command
Let's create a table of famous hackers with a command like this:CREATE TABLE hackers (FirstName varchar(20), LastName varchar(40), City varchar(30));
This line creates a table called "hackers" with 3 fields, called FirstName, LastName, and City. All of these fields are of the "varchar" type, but they are all different sizes: 20, 40, and 30 characters, respectively.
"varchar" is a field type that simply indicates a text field of variable length. This is as opposed to the "char" type, which is the same thing but must be whatever length you specify. Generally, varchar is more memory-efficient than char because it only uses as much space as is required for the data it stores, and so most database admins habitually use varchar unless they have some special reason to use char.
The INSERT INTO command
Now let's enter some actual data into our table. This can be done with the INSERT INTO command. For example, you could use lines like the following:
INSERT INTO hackers VALUES ("Kevin", "Mitnick", "Los Angeles");
INSERT INTO hackers VALUES ("Kevin", "Poulsen", "Los Angeles");
INSERT INTO hackers VALUES ("Mark", "Abene", "New York");
This syntax assumes that you are entering in a value for each column in the table. However, perhaps you don't want to do that; maybe, for example, you know a hacker's first name and city, but not his or her last name. To leave the LastName field empty, you'll need to explicitly include a list of all the field names you're updating. You can do that with a command like this:
INSERT INTO hackers (FirstName, City) VALUES ("Steve", "Cupertino");
This will create a new record for this hacker, but leave the LastName field empty.
The UPDATE command
Of course, not all data stays the same forever. Sometimes, things change. For these events, there's the UPDATE command. In this example, let's suppose that you found out Steve's last name, and you want to change the table to reflect this new information. You could do that with a command like the following:
UPDATE hackers SET LastName = "Wozniak" WHERE FirstName = "Steve";
The DELETE command
Of course, not all data is retained forever. You can delete records, appropriately enough, with the DELETE command. For example, suppose you decide that you want this table to really be about the media definition of hackers (i.e. crackers) rather than about the true "Hacker Ethic" definition, in which case Woz probably wouldn't apply (although you could argue that his earlier blue-boxing days qualify him). You could delete his record using a command like this:
DELETE FROM hackers WHERE FirstName = "Steve" AND LastName = "Wozniak";
The DROP TABLE command
Similarly, you might want to delete a whole table at some point in time, which you can do with DROP TABLE, like this:
DROP TABLE hackers;
We've now covered the basic SQL commands. There are a few other things that you should know about working with SQL, though; these more miscellaneous pieces of information I've included down here.
SQL functions
Like a programming language, SQL includes several basic functions which are used much as they are in a programming language. Exactly which functions are available depends somewhat on your database program, so you should check your software's reference to see what it supplies, but for the purposes of showing how to use the basic syntax of a function, I'll demonstrate using a fairly standard SQL function: MAX. This function is short for maximum, and simply returns the record with the greatest applicable value. For example, suppose you want to find out the highest-paid salary in your company. You could do that with a command like this:
SELECT MAX(salary) FROM employees;
INNER JOINs
In SQL, a "join" combines data from two tables. Joins can be broadly separated into inner joins and outer joins; the former is the most common, and will be demonstrated here.
Suppose that you have two tables; imagine first our original "Hackers" table, which gives the names of several hackers as well as their city. However, suppose, now, that you want to also know what state each of these hackers is in. You could add a separate field to the "Hackers" table for the state, but this is wasteful, since this will duplicate the same data several times. For example, every time someone is from Los Angeles, you will need to have another field declaring that Los Angeles is in California. Rather, the relational database way is to create a separate table (perhaps we'd call it "Cities") which associates each city name with a state. Imagine the table has two columns, one called "City" and one called "State". Now, when we see from the "Hackers" table that a hacker lives in Los Angeles, we need to look up in the "Cities" table what state Los Angeles is in. (This is not a good real-world system since city names are not unique; there are many cities in the country which have the same name, so for example if someone is from "Albany", this does not immediately make apparent whether the person is from Albany, New York or Albany, California. A real-world, professional database would use a unique numeric identifier for each city in the "Cities" table, and use that number in the "Hackers" table rather than the city name. However, for our simple example, we know that we're not using any duplicate city names, so it works for this demonstration.)
We can show the first name, last name, and state of hackers with a command like the following:
SELECT hackers.FirstName, hackers.LastName, cities.State FROM hackers INNER JOIN cities ON hackers.City = cities.City;
The command starts off listing the three fields we want to see in our output. From there, it states that we are pulling these records from the "hackers" table, but then the command specifies an inner join. Specifically, it states that we want to match up all records where hackers.City is equal to cities.City. (This condition is called a join predicate.) The result is a cross-table view that shows us selected information from two different tables, and relates the information from these tables in such a way that it makes sense, i.e. we can see the state each hacker is from without needing to see their city.
Get your own Free Homepage