Structured Query Language (SQL) is a computer language for creating database and manipulating data. SQL is an ANSI (American National Standard Institute) standard and is supported by almost all Relational Data Base Management Systems (RDBMS) like Oracle, MySQL, SQLServer, 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.
- 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 on each stage.
- Start Mysql service. In command prompt window, change directory to C:\mysql\bin (if you have installed MySQL under C:) and issue following command to start MySQL service:
NET START mysql
- Start Mysql client. In command prompt window, change directory to C:\mysql\bin and issue the command mysql to get mysql prompt.
- Create a database. On MySQL prompt, enter command 'create database' followed by any database name. Remember to put semi-colon at the end of command:
create database emp;
- Set the created database as active one. To do this, issue the 'USE' command followed by database name on mysql prompt:
- Create a table. To do this, use 'CREATE TABLE' command with name and data-type of each table field. You can also specify PRIMARY KEY and any other constraint like NOT NULL. For example:
CREATE TABLE person
(NAME VARCHAR(80) PRIMARY NOT NULL,
- Insert some data into the created table. This is accomplished through 'INSERT INTO' command followed by table name and values to be inserted.
- 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 semi-colon.
- Update the table.
- For updating a particular row, use WHERE clause in UPDATE command like:
UPDATE person SET pay = 20000 WHERE NAME = 'Kakul';
- Retrieve the stored data. Use SELECT command to retrieve the data. For conditional retrieval, you may use WHERE clause. Try following queries:
- To retrieve all COLUMNS and all rows:
SELECT * FROM person;
- To get the sorted list, use ORDER BY clause:
SELECT * FROM person ORDER BY name;
- To retrieve 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 a row 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 number of records in the table (say you want to know 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 WHERE clause to retrieve data based on multiple condition:
SELECT * FROM person WHERE name LIKE 'K%' AND pay > 5000;
- To group the results, use GROUP BY as in following:
SELECT * FROM person GROUP BY dsgn;
- To show groups satisfying a criteria, 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 value, use IN clause:
SELECT * FROM person where name IN ('Feroz','Kakul');
You may try querying with other functions also like AVG, DISTINCT, BETWEEN etc.
- Add a column to the table. This is done through ALTER command like:
ALTER TABLE person ADD experience INTEGER;
- Set an alias for person table using few columns only. To do this, use AS as illustrated below:
SELECT NAME,DSGN FROM person AS employees;
- Delete records from the table.
- Drop the column added in step 10 above. You have to again use ALTER command with DROP like this:
ALTER TABLE person DROP experience;
Note that with ADD you have to specify data-type of the column also which is obviously not required with DROP.
- Drop the created table. Use DROP TABLE command followed by table name.
DROP TABLE person;
- Drop database also. Use DROP DATABASE command followed by database name.
DROP DATABASE emp;
- Try advance SQL topics like creating views, stored procedures, cursors, join etc. from the suggested link.