How to Use Structured Query Language (SQL)

How to Use Structured Query Language (SQL)

SQL tutorial

Structured Query Language (SQL) is a computer language for creating databases and manipulating data. SQL is an ANSI (American National Standards Institute) standard and is supported by almost all Relational Database Management Systems (RDBMS) like Oracle, MySQL, SQL Server, MS Access, PostgreSQL, etc. SQL has two parts:

• Data Definition Language (DDL): to create, alter, or drop tables and indexes.
• Data Manipulation Language (DML): to insert, update, retrieve, or delete the data in the tables.

Here's how to use SQL.

1. Install an RDBMS package. You can download MySQL from http://www.mysql.org for your operating system (OS) and install it using the given instructions. For Windows OS, it can be installed by double-clicking the installer and choosing the default values at each stage.
2. Start MySQL service. In a command prompt window, change directory to C:\mysql\bin (if you have installed MySQL under C:) and issue the following command to start the MySQL service:

   NET START mysql

3. Start MySQL client. In a command prompt window, change directory to C:\mysql\bin and issue the command mysql to get the MySQL prompt.
4. Create a database. At the MySQL prompt, enter the command 'create database' followed by any database name. Remember to put a semicolon at the end of the command:

   create database emp;

5. Set the created database as the active one. To do this, issue the 'USE' command followed by the database name at the MySQL prompt:

   use emp;

6. Create a table. To do this, use the 'CREATE TABLE' command with the name and data type of each table field. You can also specify a PRIMARY KEY and any other constraint like NOT NULL. For example:
   
   CREATE TABLE person
   (NAME VARCHAR(80) PRIMARY KEY NOT NULL,
   DSGN VARCHAR(5),
   AGE INTEGER,
   PAY INTEGER
   );
7. Insert some data into the created table. This is accomplished through the 'INSERT INTO' command followed by the table name and the values to be inserted.
   • If you need to insert a row with values for all columns, then use the following command:

     INSERT INTO person VALUES ('Kakul','MGR',26,35000);

   • If you need to insert values for selected columns only, then you need to specify those column names also in the command as shown:
     
     INSERT INTO person (NAME) VALUES ('Feroz');

   Notice that a character value is enclosed within single quotes and each command is terminated with a semicolon.

8. Update the table.
   • Use the UPDATE command with SET and name-value pairs like:

     UPDATE person SET pay=50000;

   • For updating a particular row, use the WHERE clause in the UPDATE command like:
     
     UPDATE person SET pay = 20000 WHERE NAME = 'Kakul';
9. Retrieve the stored data. Use the SELECT command to retrieve the data. For conditional retrieval, you may use the WHERE clause. Try the following queries:
   • To retrieve all columns and all rows:

     SELECT * FROM person;

   • To get the sorted list, use the ORDER BY clause:

     SELECT * FROM person ORDER BY name;

   • To retrieve a few columns of all rows:

     SELECT name FROM person;

   • To retrieve all columns of a particular row:

     SELECT * FROM person WHERE name = 'Feroz';

   • To retrieve selected columns of a particular row:
     
     SELECT pay FROM person WHERE name = 'Kakul';
   • To retrieve rows with columns having a particular pattern (i.e. pay of all those employees whose name starts with K):

     SELECT pay FROM person WHERE name LIKE 'K%';

   • To count the number of records in the table (say you want to know the number of employees):

     SELECT COUNT(*) FROM person;

   • To get the sum of a column (say you need to know total pay to be paid):
     
     SELECT SUM(PAY) FROM person;
   • Use AND/OR in the WHERE clause to retrieve data based on multiple conditions:
     
     SELECT * FROM person WHERE name LIKE 'K%' AND pay > 5000;
   • To group the results, use GROUP BY as in the following:

     SELECT * FROM person GROUP BY dsgn;

   • To show groups satisfying a criterion, use HAVING as illustrated below:
     
     SELECT * FROM person GROUP BY dsgn HAVING pay > 12000;
   • To get results if a field has any of the given values, use the IN clause:

     SELECT * FROM person where name IN ('Feroz','Kakul');

   You may try querying with other functions also like AVG, DISTINCT, BETWEEN, etc.

10. Add a column to the table. This is done through the ALTER command like:

    ALTER TABLE person ADD experience INTEGER;

11. Set an alias for the person table using a few columns only. To do this, use AS as illustrated below:

    SELECT NAME,DSGN FROM person AS employees;

12. Delete records from the table.
    • To delete a particular record, use the DELETE command with a WHERE clause like:

      DELETE * FROM person WHERE name='Feroz';

    • To delete all records, use:
      
      DELETE FROM person;
      

      All records can also be deleted using the TRUNCATE command such as:

      TRUNCATE TABLE person;

13. Drop the column added in step 10 above. You have to again use the ALTER command with DROP like this:
    
    ALTER TABLE person DROP experience;

    Note that with ADD you have to specify the data type of the column also, which is obviously not required with DROP.

14. Drop the created table. Use the DROP TABLE command followed by the table name.

    DROP TABLE person;

15. Drop the database also. Use the DROP DATABASE command followed by the database name.
    
    DROP DATABASE emp;
16. Try advanced SQL topics like creating views, stored procedures, cursors, joins, etc. from the suggested link.