DML Commands

The main DML commands are:
select, insert, update, delete

• select – Retrieves information from the database. "select into" is used to perform create table and insert functionality.
•  insert – adds new rows to a table.
• update – changes existing rows in a table.
• delete – removes specific rows from a table.

Select Command Syntax
SELECT tname
FROM SYS.SYSCATALOG
WHERE tname LIKE 'SYS%' ;

SELECT count(*)
FROM Employees;

SELECT year, model, color, sum(sales)
FROM sales_tab
GROUP BY ROLLUP (year, model, color);

SELECT * INTO #TableTemp FROM lineitem
WHERE l_discount < 0.5

------------------------------------------------------------------------------------------------------
Insert Command Syntax
insert into table_name
values (constant1, constant2, ...)
Use a separate insert statement for each row you add. 
 
insert into table_name
select column_list
from table_list
where search_conditions

Insert Example
insert into publishers (pub_id, pub_name)
values ("1756", "The Health Center")
------------------------------------------------------------------------------------------------------ 
Update Command Syntax
update titles
set price = price * 2

update titles
set total_sales = total_sales + 1, @price = price
where title_id = "BU1032"

update authors
set state = "PC", city = "College Town"
where state = "CA" and city = "Oakland"

update titleauthor
set title_id = titles.title_id
from titleauthor, titles, authors
where titles.title = "The Psychology of Computer Cooking"
and authors.au_id = titleauthor.au_id
and au_lname = "Stringer"

As an alternative to the from clause in the update statement, you can use a subquery, which is ANSI-compliant.

update stores_cal
set stor_name = "Barney’s"
where syb_identity = 1
You can use the syb_identity keyword, qualified by the table name, where necessary, to update an IDENTITY column.
------------------------------------------------------------------------------------------------------ 
Delete Command Syntax
delete titles
from authors, titles, titleauthor
where titles.title_id = titleauthor.title_id
and authors.au_id = titleauthor.au_id
and city = "Big Bad Bay City"

delete sales_monthly
where syb_identity = 1
------------------------------------------------------------------------------------------------------