UoL CS Notes
Menu

SQL Data Manipulation Language (DML)

COMP207 Lectures

Queries are not covered in this lecture and will have their own notes.

Insert

For the following table:

name number programme
Anna 20171989 G402
John 20174378 G702

If you want to insert a value you would use the following command:

INSERT INTO Students VALUES('Oliver', 20171112, 'G402');

' is used to denote the start and end of a string and not ".

To insert a tuple while missing an attribute you can use the following:

INSERT INTO Students(programme, name) VALUES('G702', 'Danny');

The unused attribute will be filled with null.

Comments

In the SQL languages -- is used to denote comments.

Delete

To remove an entry from the result of a simple query you can do the following:

DELETE FROM Students WHERE name='John';

If you write DELETE FROM Students; you will remove all the entries from the table.

WHERE Clauses

For comparisons you can use the following:

  • =,<,<=,>=,>
  • You can also use <> or != for the last item.

You can also use some boolean operations:

  • AND
  • OR
  • NOT
  • BETWEEN
    • E.g. Price BETWEEN 10 AND 20.
  • LIKE
    • _ matches single characters.
    • % matches any number of characters.

For combinations you can use the following:

... WHERE name='Oliver' AND programme='G402';

IN

To specify a set to match against you can use the IN keyword:

DELETE FROM Students WHERE name IN('John','Sebastian');

Updating

To update the contents of a row you can do the following:

UPDATE Students SET programme='G402' WHERE name='Oliver';

You can also update programatically:

UPDATE Students SET number=number+1 WHERE name='Oliver';