SQL-Commands DML

Question 1

DML - Data Manipulation Language

Answer 1

statements are managing data within schema

Question 2

Select

Answer 2

Retrieve data from the dB

Select commands from 1 table:
SELECT (select & from are the only 2 required keywords)
* : all
column_name
distinct column_name : specified & non-duplicated columns
FROM table_name
count(*): total number of rows

WHERE (optional ,but must be in the specific order) - conditions
=,>,=,<>(not equal)
And (both condition)
OR(one or another, or both)
IN(specify multiple values)
BETWEEN(select a range of data between two values)
LIKE (search for a specified pattern in a column:% or_(one character )
Remove some or all (no WHERE clause) rows from a table

Some:
Delete from table 1
Where column_name = ‘student’

All:
Delete from table 1

Delete a table

DROP TABLE table_name

IS NULL/NOT NULL

GROUP BY - column-list
HAVING - conditions for number (aggregate’s WHERE)
SUM, MAX, MIN, AVG

ORDER BY column_list ASC or DESC (ASC is the default)

Select commands for more than one table:
Select all tables:
Select*
from all_tables

Question 3

Insert - how do you add record to the table

Answer 3

Insert data into a table
INSERT into table_name
Values (1,2,3,’a’)

Question 4

Update - how do you change value of the field?

Answer 4

Update existing data within a table

UPDATE table_name
SET field1=200
        field 2='ABC'
        field 3=to_date(2006-03-04 09:29, 'yyyy-mm-dd')
WHERE field = 'CD'(Condtion)

Question 5

Delete

Answer 5

deletes all (no where) or specific (+where) records from a table

Specific:
Delete from table 1
Where column_name = ‘student’

All:
Delete from table 1

• Delete a table

DROP TABLE table_name

Question 6

Select - count

How do you find the number of rows in a table?

Answer 6

Select count (*) 
From table_name

Question 7

Select - ROWNUM

Give each result a number

Answer 7

Select FirstName, LastName, Salary, ROWNUM

From Table_name