Pin Me

DML Queries in SQL

written by: •edited by: Michele McDonough•updated: 4/28/2010

This tutorial describes all of the DML queries available in SQL in detail. DML queries allow you to manipulate the data in your database tables.

  • slide 1 of 6

    DML Queries in SQL

    Data Manipulation Language (DML) Queries in SQL are used to manipulate the data present in SQL tables. As we explain how to construct these queries, we'll use the following sample table.

    NAME: ID FIRST LAST

    1 PAT S

    2 MOO X

    3 RAM YO

    4 JIM CHOO

  • slide 2 of 6

    SELECT Command

    The SELECT query can be used to select tuples of data in the table and display them.

    The basic syntax of the SELECT query is:

    SELECT COLUMNNAME FROM TABLENAME;

    This will display all the values of that column present in the table.

    For example, the command

    SELECT FIRST FROM NAME

    would give the following output.

    FIRST

    PAT

    MOO

    RAM

    JIM

    To select all columns and display them, use:

    SELECT * FROM TABLENAME;

    For example, the command

    SELECT * FROM NAME;

    would give the output:

    ID FIRST LAST

    1 PAT S

    2 MOO YO

    3 RAM YO

    4 JIM CHOO

    To select only distinct values in a column, use:

    SELECT DISTINCT COLUMNNAME FROM TABLENAME;

    For example,

    SELECT DISTINCT LAST FROM NAME;

    gives the output:

    LAST

    S

    YO

    CHOO

    Note: that the YO surname will be displayed only once.

  • slide 3 of 6

    WHERE Clauses and AND/OR Statements

    If you don't want to select all values, but only those which fulfill a certain condition, use the WHERE clause.

    SELECT COLUMNNAME FROM TABLENAME WHERE [CONDITION];

    For example,

    SELECT FIRST FROM NAME WHERE ID=1;

    gives the output:

    FIRST

    PAT

    SELECT FIRST FROM NAME WHERE ID>2;

    gives the output:

    FIRST

    RAM

    JIM

    SELECT FIRST FROM NAME WHERE ID IN (1,3);

    yields:

    FIRST

    PAT

    RAM

    You can also use AND / OR logical connectives to combine result sets.

    SELECT COLUMNNAME FROM TABLENAME WHERE CONDITION1 AND CONDITION2;

    SELECT COLUMNNAME FROM TABLENAME WHERE CONDITION1 OR CONDITION2;

    For example,

    SELECT FIRST FROM NAME WHERE ID=2 AND LAST="YO";

    gives:

    FIRST

    MOO

    Note: In an AND connective, only those results which satisfy both conditions are returned.

    SELECT FIRST FROM NAME WHERE ID=2 OR LAST="YO";

    yields the following result:

    FIRST

    MOO

    RAM

    Note: In an OR connective, all those results which satisfy either of the two, or both conditions are returned.

    You can also combine the two for a complex query:

    SELECT FIRST FROM NAME WHERE (ID=2 OR ID=4) AND LAST="CHOO";

    returns the following information:

    FIRST

    JIM

  • slide 4 of 6

    INSERT Command

    The INSERT query can be used to insert data into the table.

    The basic syntax of the INSERT command is:

    INSERT INTO TABLENAME VALUES (value1, value2, value3);

    or

    INSERT INTO TABLENAME (COLUMN1, COLUMN2, COLUMN3) VALUES (value1, value2, value3);

    If you use the first syntax, you don't have to specify the column order, but you have to insert values for all the columns in the order in which the columns are defined in the table.

    For example,

    INSERT INTO NAME VALUES (5, 'AAA', 'B');

    This will insert a new tuple in the table:

    ID FIRST LAST

    5 AAA B

    Using the second syntax, you can change the order of the column variables.

    For example,

    INSERT INTO NAME(FIRST, LAST, ID) VALUES ('AAA', 'B', 5);

    This will insert the same tuple in the table:

    ID FIRST LAST

    5 AAA B

    If you use the second syntax, you can also skip columns which you don't need.

    For example,

    INSERT INTO NAME(FIRST, ID) VALUES ('AAA', 5);

    This will insert the same tuple in the table:

    ID FIRST LAST

    5 AAA

  • slide 5 of 6

    UPDATE Command

    Using the update command, you can update the values in the table to whichever values you want.

    The basic syntax is:

    UPDATE TABLENAME

    SET COLUMNNAME="newvalue" WHERE [CONDITION]

    For example,

    UPDATE NAME SET FIRST="XYZ" WHERE ID=3;

    This command will change the value of RAM to XYZ.

  • slide 6 of 6

    DELETE Command

    The DELETE command will tuples in the table based on the condition specified.

    DELETE FROM TABLENAME WHERE [CONDITION]

    For example,

    DELETE FROM NAME WHERE ID=1;

    This command will delete the first tuple in the table.

    You can also delete all the tuples by not specifying the condition.

    DELETE FROM NAME;

    OR

    DELETE * FROM NAME;

    This will delete all tuples.

    We are done with the basic DML commands. In the next article, we will study basic aggregate and scalar functions in SQL.

Introduction to SQL

This series is an introduction to SQL. It includes details on tables, DDL and DML queries, joins in SQL, and more.
  1. Short Introduction to SQL
  2. DML Queries in SQL
  3. Aggregate and Scalar Functions in SQL
  4. Using Joins in SQL