$($a$)$ create table STUDENT $($

sid number$(7),$

sname varchar$2(30),$

sdno number$(5))$;

A table named STUDENT is created in the database. Guess what is the meaning of $$number$(7)$ in the statement.

Answer:

$($b$)$ desc STUDENT

$$desc$$ is an SQL$*$Plus command. Therefore, it does not need to end with a semicolon. Explain the function of the SQL$*$Plus command $$desc$.$

Answer:

Note that this is a quite useful command to check the definition $($schema$)$ of a table.

$($c$)$ insert into STUDENT values $(30,$ 'John Doe', $20)$;

commit;

Insert a tuple into the table. Note the order of the values in the parentheses. It must correspond to the order of the columns in the table definition.

$$commit$$ is an SQL command that makes the insertion of the tuple to be permanent in the table. Without committing, the tuple will not be visible by other users$/$session$.$ It is related to transaction processing that will be discussed in future lectures. At this point, you only need to remember that after you insert$/$delete$/$update the content of some tables, type commit to make the changes permanent.

Now try:

insert into STUDENT values $(\text{'}$Mark Smith', $20,10)$;

Write down what happened. Why?

Answer:

$($d$)$ select $*$ from STUDENT;

Display all the columns of all the rows in the table STUDENT.

$($e$)$ insert into STUDENT values $(100,$ 'Mark Smith', $10)$;

commit;

select $*$ from STUDENT;

How many rows are there in the table?

Answer:

$($f$)$ delete from STUDENT where sid $=30$;

commit;

select $*$ from STUDENT;

Delete statement: it deletes a row whose sid is $30$ from the table. Note that delete is usually used with a $$where$$ clause to specify which rows to delete. A delete statement without a $$where$$ clause will delete ALL the tuples in a table.

$($g$)$ select table$\_$name from user$\_$tables;

This select statement returns only the table$\_$name column from the table user$\_$tables. User$\_$tables is a system table, which is part of Oracle data dictionary. It is automatically maintained by DBMS$.$ It contains tuples for every table created by the user. Since user$\_$tables has many columns $($check it with desc user$\_$tables$),$ we only display its table$\_$name column.

$($h$)$ drop table STUDENT;

Remove the table STUDENT. Note that all the tuples within the table will be removed. Use it with care!