Question

Table Course:    Column    dataType Constraint

ccode char(8) primary key,

meetingTime char(4),

room char(6),

fid    decimal(3) NOT NULL,

cTitle    varchar(24) NOT NULL,

fee    decimal(4,2),   

prereq char(8)

1. Select all Course table data and display in primary key order. Show this query result.

2. By default MySQL runs in autocommit mode. Issue a START TRANSACTION command before a series of update commands that may need to be rolled back or to be committed. ROLLBACK will "undo" commands given in the transaction. [Note: COMMIT is not needed here, but makes work final and completes the transaction.]

Answer 1

1. The "order by" clause is used to order the result of a query by a particular column. The query for selecting all the course is :-

select * from courses order by ccode asc;

This query will select all the courses ordering them by ccode in ascending order. If we want to arrange the result in descending order we can use 'desc' in place of 'asc'.

2. To create transaction query we first of all need to start a transaction which can be done using the statement :-

start transaction;

Here after we have started the transaction we can perform any no. of sql statements that modify the database.

After we are done executing the statements we can either save those modifications or can completely remove them. For saving them we can use 'commit' statement and to remove those changes we can use rollback statement.

Eg :-

start transaction;

update Course set fee = fee + 100.00; -- This statement increases fees of all courses by 100

rollback; -- It will undo all the changes.

If we wanted to save the changes instead of undoing those changes then we could have used commit to do that.