Exercitii – comenzi DML si DDL

CREATE TABLE elevi( nr_matr NUMBER(5), cnp NUMBER(13), nume VARCHAR2(30), prenume VARHAR2(30), bursier CHAR(1), nota NUMBER(4,2), total_abs NUMBER(3), abs_nemotiv NUMBER(3))

Adaugă o colonă premiant la tabela eleviALTER TABLE eleviADD premiant char(1)

Sterge coloana abs_nemotiv ALTER TABLE elevi DROP COLUMN abs_nemotiv

Modifică tipul coloanei prenume de le VARCHAR2(30) la VARCHAR2(50)

ALTER TABLE elevi MODIFY prenume VARCHAR2(50)Stabiliti o valoare implicita pentru coloana premiant

ALTER TABLE elevi MODIFY premiant CHAR(1) DEFAULT ’D’

6. In d_events table, the COST column is mandatory, but the cost is not known at the time of insert. Zero (0) will have to be inserted as the default cost. The DESCRIPTION column is nullable -demonstrate these inserting a new raw in d_events table.

INSERT INTO copy_d_events (id, name, event_date,description,cost , venue_id, package_code,

theme_code, client_number) VALUES ( 12, 'Weding','12-MAR-2007',NULL,0,45,56,67,9);

7. Bob Miller is an employee in the f_staffs table. Management has decided to reward him by increasing his overtime pay. Bob Miller will receive an additional $0.75 per hour .  Update the f_staffs table to show these new value. (Note: Bob Miller currently doesn't get overtime pay. What function do you need to use to convert a null value to 0?)

UPDATE copy_f_staffs SET overtime_rate=0.75 WHERE first_name='Bob' and last_name='Miller';

8. Now that all the information in f_staffs table is available for Monique Tuttle, update her record to include the following: Monique will have the same manager as Sue Doe. She does not qualify for overtime. Leave the values for training, manager budget and manager target as null. The table has to look like this one:

ID

FIRST_NAME

LAST_NAME

BIRTHDATE

SALARY

OVERTIME_RATE

TRAINING

STAFF_TYPE

MANAGER_ID

MANAGER_BUDGET

MANAGER_TARGET

12

Sue Doe01-07-1980

6,75 10,25 - Order Taker

19 - -

9 Bob Miller19-03-1979

10 ,75 Grill Cook 19 - -

19

Monique Tuttle30-03-1969

60 - - Manager 19 - -

UPDATE copy_f_staffs SET manager_id=( SELECT manager_id FROM f_staffs WHERE first_name='Sue' and last_name='Doe' ) WHERE first_name='Monique' and last_name='Tuttle'

9. Monique Tuttle has decided to go back to college and does not have the time to work and go to school. Delete he from the f_staffs table. Verify that the changes were made. The table will look like this:

ID

FIRST_NAME

LAST_NAME

BIRTHDATE

SALARY

OVERTIME_RATE

TRAINING

STAFF_TYPE

MANAGER_ID

MANAGER_BUDGET

MANAGER_TARGET

12 Sue Doe01-07-1980

6,75 10,25 - Order Taker

19 - -

9 Bob Miller19-03-1979

10 ,75 Grill Cook 19 - -

DELETE FROM copy_f_staffs WHERE first_name='Monique' and last_name='Tuttle';

10. Write the syntax to create the grad_candidates table as shown:

Confirm creation of the table using DESCRIBE;

CREATE TABLE grad_candidates ( student_id Number(6), last_name VARCHAR2(15),first_name

VARCHAR2(15),credits Number(3), graduation_date Date ); DESC grad_candidates;