1 / 14

More about maintaining a table

More about maintaining a table. Use speaker notes for additional information!. CREATE TABLE.

cairo-walker
Download Presentation

More about maintaining a table

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. More about maintaining a table Use speaker notes for additional information!

  2. CREATE TABLE To review the create process, when you create a table you issue the command CREATE TABLE followed by the table name. Then, within parenthesis, you list the column/field names followed by the type and length where needed. When all fields have been listed, close the parenthesis and key in the semi-colon. SQL> CREATE TABLE stu_maintain 2 (stuidno VARCHAR2(4), 3 name VARCHAR2(20), 4 major CHAR(2), 5 numcrs NUMBER(3), 6 gpa NUMBER(5,3), 7 stdate DATE); Table created.

  3. DESCRIBE The DESCRIBE command followed by the table name is used to show the structure of the table. SQL> DESCRIBE stu_maintain; Name Null? Type ------------------------------- -------- ---- STUIDNO VARCHAR2(4) NAME VARCHAR2(20) MAJOR CHAR(2) NUMCRS NUMBER(3) GPA NUMBER(5,3) STDATE DATE

  4. INSERT INTO To review, the INSERT INTO command followed by the name of the table and then the word VALUES followed by the values needed will create a row/record in the table. Note that the VALUES have to be in the same order as the columns/fields on the file. When I did the CREATE TABLE, stuidno was first so the data for that field is also entered first. Name was second so the data for name is entered second. SQL> INSERT INTO stu_maintain 2 VALUES('1111','John Randolph', 'CI',12,3.333,'10-SEP-98'); 1 row created. SQL> INSERT INTO stu_maintain 2 VALUES ('2222','Susan Quincy', 'CI', 15, 3.250, '11-SEP-99'); 1 row created. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99

  5. INSERT INTO If you only want to put data in specific fields, you can list the fields after the table name and then only enter data for the names specified. SQL> INSERT INTO stu_maintain (stuidno, name, stdate) 2 VALUES('3333','AnnTanner','23-JAN-00'); 1 row created. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333Ann Tanner23-JAN-00

  6. UPDATE STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner 23-JAN-00 In this example, I want to update an existing row/record by changing the major which has no data to BU. I want to make this change only for the record where the stuidno is 3333. If you eliminate the WHERE clause, all records would be changed. SQL> UPDATE stu_maintain 2 SET major = 'BU' 3 WHERE stuidno = '3333'; 1 row updated. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner BU 23-JAN-00

  7. UPDATE STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner BU 23-JAN-00 SQL> UPDATE stu_maintain 2 SET numcrs = 4, gpa = 2.575 3 WHERE stuidno = '3333'; 1 row updated. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner BU 42.575 23-JAN-00

  8. SQL> INSERT INTO stu_maintain (stuidno, name) 2 VALUES ('3434','TO DELETE'); 1 row created. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner BU 4 2.575 23-JAN-00 3434 TO DELETE SQL> DELETE FROM stu_maintain 2 WHERE stuidno = '3434'; 1 row deleted. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE ---- -------------------- -- --------- --------- --------- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner BU 4 2.575 23-JAN-00 DELETE FROM DELETE FROM removes the specified row/record from the table.

  9. ALTER TABLE SQL> ALTER TABLE stu_maintain 2 ADD (stu_opt VARCHAR2(5)); Table altered. SQL> DESCRIBE stu_maintain; Name Null? Type ------------------------------- -------- ---- STUIDNO VARCHAR2(4) NAME VARCHAR2(20) MAJOR CHAR(2) NUMCRS NUMBER(3) GPA NUMBER(5,3) STDATE DATE STU_OPT VARCHAR2(5) SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE STU_O ---- -------------------- -- --------- --------- --------- ----- 1111 John Randolph CI 12 3.333 10-SEP-98 2222 Susan Quincy CI 15 3.25 11-SEP-99 3333 Ann Tanner BU 4 2.575 23-JAN-00

  10. UPDATE SQL> INSERT INTO stu_maintain (stu_opt) 2 SET stu_ 3 SQL> UPDATE stu_maintain 2 SET stu_opt = 'prog' 3 WHERE stuidno = '2222'; Above, I updated stu_opt for stuidno 2222 and below I updated stu_opt for stuidno 1111 and 3333. The results are shown in the SELECT. SQL> UPDATE stu_maintain 2 SET stu_opt = 'trans' 3 WHERE stuidno = '1111' OR stuidno = '3333'; 2 rows updated. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE STU_O ---- -------------------- -- --------- --------- --------- ----- 1111 John Randolph CI 12 3.333 10-SEP-98 trans 2222 Susan Quincy CI 15 3.25 11-SEP-99 prog 3333 Ann Tanner BU 4 2.575 23-JAN-00 trans

  11. SQL> DESC stu_maintain; Name Null? Type ------------------------------- -------- ---- STUIDNO VARCHAR2(4) NAME VARCHAR2(20) MAJOR CHAR(2) NUMCRS NUMBER(3) GPA NUMBER(5,3) STDATE DATE STU_OPT VARCHAR2(5) SQL> ALTER TABLE stu_maintain 2 MODIFY (stu_opt VARCHAR2(10)); Table altered. SQL> DESC stu_maintain; Name Null? Type ------------------------------- -------- ---- STUIDNO VARCHAR2(4) NAME VARCHAR2(20) MAJOR CHAR(2) NUMCRS NUMBER(3) GPA NUMBER(5,3) STDATE DATE STU_OPT VARCHAR2(10) SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE STU_OPT ---- -------------------- -- --------- --------- --------- ---------- 1111 John Randolph CI 12 3.333 10-SEP-98 trans 2222 Susan Quincy CI 15 3.25 11-SEP-99 prog 3333 Ann Tanner BU 4 2.575 23-JAN-00 trans ALTER MODIFY Original table structure had stu_opt 5 characters. MODIFY changes the structure of the table. After modification, stu_opt is 10 characters.

  12. UPDATE STUI NAME MA NUMCRS GPA STDATE STU_OPT ---- -------------------- -- --------- --------- --------- ---------- 1111 John Randolph CI 12 3.333 10-SEP-98 trans 2222 Susan Quincy CI 15 3.25 11-SEP-99 prog 3333 Ann Tanner BU 4 2.575 23-JAN-00 trans SQL> UPDATE stu_maintain 2 SET stu_opt = 'transfer' 3 WHERE stuidno = '1111' OR stuidno = '3333'; 2 rows updated. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE STU_OPT ---- -------------------- -- --------- --------- --------- ---------- 1111 John Randolph CI 12 3.333 10-SEP-98 transfer 2222 Susan Quincy CI 15 3.25 11-SEP-99 prog 3333 Ann Tanner BU 4 2.575 23-JAN-00 transfer

  13. ALTER MODIFY STUI NAME MA NUMCRS GPA STDATE STU_OPT ---- -------------------- -- --------- --------- --------- ---------- 1111 John Randolph CI 12 3.333 10-SEP-98 transfer 2222 Susan Quincy CI 15 3.25 11-SEP-99 prog 3333 Ann Tanner BU 4 2.575 23-JAN-00 transfer SQL> ALTER TABLE stu_maintain 2 MODIFY (stu_opt VARCHAR2(5)); MODIFY (stu_opt VARCHAR2(5)) * ERROR at line 2: ORA-01441: column to be modified must be empty to decrease column length In this example, I tried to decrease the size of the stu_opt field. The field contained data that was more than 5 characters so data would have been lost with this decrease. Note the error message that resulted.

  14. UPDATE When the WHERE clause is omitted, the UPDATE effects all records. SQL> UPDATE stu_maintain 2 SET GPA = 4.000; 3 rows updated. SQL> SELECT * 2 FROM stu_maintain; STUI NAME MA NUMCRS GPA STDATE STU_OPT ---- -------------------- -- --------- --------- --------- ---------- 1111 John Randolph CI 12 4 10-SEP-98 transfer 2222 Susan Quincy CI 15 4 11-SEP-99 prog 3333 Ann Tanner BU 4 4 23-JAN-00 transfer

More Related