* 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 on each stage.
2.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
3.Start Mysql client. In command prompt window, change directory to C:\mysql\bin and issue the command mysql to get mysql prompt.
4.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;
5.Set the created database as active one. To do this, issue the 'USE' command followed by database name on mysql prompt:
use emp;
6.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,
DSGN VARCHAR(5),
AGE INTEGER,
PAY INTEGER
);
7.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 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 semi-colon.
8.Update the table.
* Use UPDATE command with SET and name-value pairs like:
UPDATE person SET pay=50000;
* For updating a particular row, use WHERE clause in UPDATE command like:
UPDATE person SET pay = 20000 WHERE NAME = 'Kakul';
9.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.
10.Add a column to the table. This is done through ALTER command like:
ALTER TABLE person ADD experience INTEGER;
11.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;
12.Delete records from the table.
* To delete a particular record, use DELETE command with WHERE clause like:
DELETE * FROM person WHERE name='Feroz';
* To delete all records, use:
DELETE FROM person;
All records can also be deleted using TRUNCATE command such as:
TRUNCATE TABLE person;
13.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.
14.Drop the created table. Use DROP TABLE command followed by table name.
DROP TABLE person;
15.Drop database also. Use DROP DATABASE command followed by database name.
DROP DATABASE emp;
16.Try advance SQL topics like creating views, stored procedures, cursors, join etc.
www.howtodothings.com
No comments:
Post a Comment