Term
|
Definition
| Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one “joined” collection of data. |
|
|
Term
| How do you add record to a table? |
|
Definition
| INSERT into table_name VALUES (‘JEN’, 30 , ‘F’); |
|
|
Term
| How do you add a column to a table? |
|
Definition
| ALTER TABLE Department ADD (AGE, NUMBER); |
|
|
Term
| How do you change value of the field? |
|
Definition
UPDATE EMP_table set number = 200 where item_munber = ‘CD’;
update name_table set status = 'enable' where phone = '7182000555';
update SERVICE_table set REQUEST_DATE = to_date ('2014-04-02 10:19', 'yyyy-mm-dd hh24:MM') where phone = '7182000555'; |
|
|
Term
|
Definition
| Saving all changes made by DML statements |
|
|
Term
|
Definition
| The column (columns) that has completely unique data throughout the table is known as the primary key field. |
|
|
Term
|
Definition
| Foreign key field is a field that links one table to another table’s primary or foreign key. |
|
|
Term
| What is the main role of a primary key in a table? |
|
Definition
| The main role of a primary key in a data table is to maintain the internal integrity of a data table. |
|
|
Term
| Can a table have more than one foreign key defined? |
|
Definition
| A table can have any number of foreign keys defined. It can have only one primary key defined. |
|
|
Term
| List all the possible values that can be stored in a BOOLEAN data field. |
|
Definition
| There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false). |
|
|
Term
| Describe how NULLs work in SQL? |
|
Definition
| Its how SQL handles missing values. Arithmetic operation with NULL in SQL will return a NULL. |
|
|
Term
|
Definition
| Process of table design is called normalization. |
|
|
Term
| Can one select a random collection of rows from a table? |
|
Definition
Use SAMPLE clause. Ex:
SELECT * FROM EMPLOYEES SAMPLE(15);
15% of rows selected randomly will be returned. |
|
|
Term
|
Definition
| DML and DDL are subsets of SQL. DML stands for Data Manipulation Language and DDL – Data Definition Language. |
|
|
Term
| What are DML some commands? |
|
Definition
SELECT: retrieve data from database
INSERT: insert data into table
UPDATE: updates existing data within table
DELETE: deletes all records from table
EXPLAIN PLAN: explain access path to data
LOCK TABLE: control concurrency |
|
|
Term
| What are DDL some commands? |
|
Definition
CREATE: create objects in database
ALTER: alters structure of database
DROP: delete objects from database
TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed
COMMENT: add comments to data dictionary
RENAME: rename an object |
|
|
Term
|
Definition
Data Control Language statements.
Ex:
GRANT: gives user's access privileges to database
REVOKE: withdraw access privileges given with the GRANT command |
|
|
Term
|
Definition
| Transaction Control statements manage the changes made by DML statements. Groups statements together into logical transactions. |
|
|
Term
|
Definition
COMMIT: save work done
SAVEPOINT: identify a point in a transaction where you can later roll back
ROLLBACK: restore database to original since the last COMMIT
SET TRANSACTION: Change transaction options like isolation level and what rollback segment to use |
|
|
Term
| Difference between TRUNCATE, DELETE and DROP commands |
|
Definition
DELETE: used to remove some or all rows from a table.
TRUNCATE: removes ALL rows from a table. Cannot be rolled back
DROP: removes a table from the database. All the tables' rows, indexes and privileges will also be removed. |
|
|
