How to Use SQL Commands

SQL commands are used to query, input or modify information in a relational database. A query is used when you need to know specific information about the data, such as which students scored over 90 in a class or which students were enrolled in a particular subject. The input commands control what goes into the database. Any external interface, such as a Web page, must call scripts that execute SQL commands to send field information. SQL commands that modify a database are those which delete, rearrange or update information. This is especially useful for data items that have expired or are no longer in use, such as addresses and telephone numbers.This article demonstrates how to create, populate, modify and delete a table in SQL. The database used for the samples is MySQL.

Things You'll Need

• Access to a database program, such as MySQL or Oracle

Instructions

1. 

   Log in to MySQL. Once you have remotely connected to the server, type the following commands to enter MySQL
   >mysql -u your_user_name -p
   >your_password
   mysql>use your_database_name
   mysql>
   Now you are ready to issue SQL commands. Remember you must type "use" and then your own database name.

2. 

   Create a table. The table for this article will have three fields: name, subj and grade. The table will be named "std_info." Use the CREATE TABLE command as shown below:
   CREATE TABLE std_info (name VARCHAR(255)NAT NULL, subj VARCHAR(255) NOT NULL,grade INT NOT NULL);
   This command will create a table with the fields: "name", "subj" and "grade." VARCHAR states the number of characters allowed in each cell. INT states that the data item is an integer. NOT NULL assures that the cell does not accept null entries. If a null entry is sent, SQL will return an error message.

3. 

   Populate the table. To populate the table you will have to invent some values for each field. This example populates the table with the information from two students, two different subjects and four different grades. The code to populate the first item of input is given below.
   INSERT INTO std_info VALUES('Mark Smith','Math',85);
   To populate the other items of input, repeat the code and change the field information.

4. 

   Modify the information. You use the UPDATE command to modify the information in a table. In this example the subject "Math" will be changed to "English" for the student named "Mark Smith."
   UPDATE std_info SET subj='English' WHERE subj='Math' AND name='Mark Smith';
   To make sure the changes have been made type:
   SELECT * FROM std_info;
   This will display all of the input in the table. The "*" instructs to select all fields of the table.

5. Delete information in the table. SQL allows you to delete individual rows or to delete all of the information in a table. To delete a particular row, do the following:
   DELETE FROM std_info WHERE subj='Math' AND name='Mary Jones';
   To delete all of the information in the table without deleting the table itself type:
   DELETE FROM std_info;

6. 

   Eliminate the table. To eliminate the table you will have to use the DROP command. This will erase the table and all of the information within it.
   DROP TABLE std_info;

Tips & Warnings

• The SQL commands have been written in uppercase for expository purposes. If you are using a different database (e.g., Oracle), the SQL commands will not change, especially for the fundamental ones given in this article.

References

• MySQL® Tutorial; L. Welling and L. Thomson; 2003

Resources

• MySQL Tutorial