{"id":313,"date":"2025-03-25T13:19:16","date_gmt":"2025-03-25T06:19:16","guid":{"rendered":"https:\/\/binus.ac.id\/semarang\/information-systems\/?p=313"},"modified":"2025-03-25T14:05:27","modified_gmt":"2025-03-25T07:05:27","slug":"313","status":"publish","type":"post","link":"https:\/\/binus.ac.id\/semarang\/information-systems\/2025\/03\/25\/313\/","title":{"rendered":"Modifying Table using SQL(Oralce APEX)"},"content":{"rendered":"

Modifying Table using SQL<\/strong><\/p>\n

 <\/p>\n

SQL databases organize data and relationships within tables. Sometimes, these tables also need modifications to meet new requirements. While all SQL table structures can be altered, some changes are simpler to implement than others.
\nThere are multiple way to modify a table :<\/p>\n

1. Adding a Column The most easiest one can alter from a table is to add a new field to the table. This is generally a very simple and risk operation as it doesn’t have any dependency on the other components within the database.<\/p>\n

Syntax: ALTER<\/strong> TABLE<\/strong> NamaTabel ADD<\/strong> NamaKolom TipeData;<\/p>\n

2. Modifying a Column To change the data type, size, or constraints of an existing column, the MODIFY or ALTER COLUMN clause is employed, depending on the SQL dialect. It’s important to note that some modifications may be restricted based on the existing data within the column.<\/p>\n

Syntax: ALTER<\/strong> TABLE<\/strong> NamaTabel ALTER<\/strong> COLUMN<\/strong> NamaKolom TipeDataBaru;<\/p>\n

Example<\/strong>: ALTER<\/strong> TABLE<\/strong> Kelas ALTER<\/strong> COLUMN<\/strong> umur VARCHAR(2);<\/p>\n

3. Deleting a Column To remove an unnecessary column from a table, the DROP COLUMN clause is used. This action is irreversible; once a column is dropped, all data contained within it is permanently deleted.<\/p>\n

Syntax: ALTER<\/strong> TABLE<\/strong> NamaTabel DROP<\/strong> COLUMN<\/strong> NamaKolom;<\/p>\n

Example<\/strong>: Removing the Email column from the Customers table: ALTER TABLE Mobil DROP COLUMN Banyak_Ban;<\/p>\n

4. Renaming a Column To change the name of an existing column, the RENAME COLUMN clause is utilized. The exact syntax can vary between different SQL databases.<\/p>\n

Syntax: ALTER TABLE NamaTabel RENAME COLUMN NamaLama TO NamaBaru;<\/p>\n

Example: ALTER<\/strong> TABLE<\/strong> Rumah RENAME<\/strong> COLUMN<\/strong> Luas TO<\/strong> Besar;<\/p>\n

5. Adding Constraints Constraints are the rules at the table level, ensuring the accuracy and reliability of the data. Common constraints include that are being used to make one unique or become the main way to distinct one table from another.<\/p>\n

Syntax: ALTER<\/strong> TABLE<\/strong> NamaTabel ADD<\/strong> CONSTRAINT<\/strong> NamaCostraint TipeCosntraint (NamaKolom);<\/p>\n

Example<\/strong>: ALTER<\/strong> TABLE<\/strong> Pekerja ADD<\/strong> CONSTRAINT<\/strong> UNIQUE_EMAIL<\/strong> UNIQUE<\/strong> (Email); This make all values in the Email column unique across the Employees table.<\/p>\n

6. Dropping Constraints To remove an existing constraint from a table, the DROP CONSTRAINT clause is used. The specific syntax may vary depending on the database system.<\/p>\n

Syntax:
\nALTER TABLE NamaTabel DROP CONSTRAINT NamaConstraint;<\/p>\n

Example<\/strong>: Dropping<\/strong> the UNIQUE_EMAIL constraint from the Mobil table: ALTER TABLE<\/strong> Mobil<\/strong> DROP CONSTRAINT<\/strong> UNIQUE_EMAIL<\/strong>;
\nThis removes the uniqueness requirement from the Email column, allowing duplicate entries.<\/p>\n

7. Renaming a Table If you are unhappy with the name and want to change the already created table name. To change the name of a table, use RENAME TO.<\/p>\n

Syntax: ALTER<\/strong> TABLE<\/strong> NamaTabelLama RENAME<\/strong> TO<\/strong> NamaTabelBaru;<\/p>\n

Example: Renaming the Customers table to Clients: ALTER<\/strong> TABLE<\/strong> Mobil RENAME<\/strong> TO<\/strong> Mobilku;<\/p>\n

8. Truncating a Table To remove all data from a table without deleting the table itself, the TRUNCATE TABLE statement is used. This operation is faster than deleting all rows individually and resets any auto-increment counters.<\/p>\n

Syntax: TRUNCATE TABLE NamaTabel;
\nExample<\/strong>: TRUNCATE<\/strong> TABLE<\/strong> Sales;
\nThis command deletes all rows in the Sales table, effectively resetting it while preserving its structure for future data.<\/p>\n

9. Setting Unused Columns If you don’t want to delete you can instead of deleting a column, you can hide it, which is faster for big tables.<\/p>\n

Syntax : ALTER<\/strong> TABLE<\/strong> NamaTable SET<\/strong> UNUSED<\/strong> (NamaKolom);<\/p>\n

Example<\/strong> : ALTER<\/strong> TABLE<\/strong> Kelas SET UNUSED<\/strong> (TanggalKelas);<\/p>\n

DBMS : Oralce APEX<\/strong><\/p>\n

Link Infographic: https:\/\/www.canva.com\/design\/DAGgqwYZYOI\/80A8ctG0mzjrK4b1bkijqg\/edit?utm_content=DAGgqwYZYOI&utm_campaign=designshare&utm_medium=link2&utm_source=sharebutton<\/p>\n

References :<\/p>\n

https:\/\/support.microsoft.com\/en-us\/office\/create-or-modify-tables-or-indexes-by-using-a-data-definition-query-d935e129-229b-48d8-9f2d-1d4ee87f418e
\nhttps:\/\/study.com\/academy\/lesson\/modifying-tables-using-sql.html
\nhttps:\/\/yasirutomo.com\/sql-tutorial-ddl-create-alter-dan-drop-database-table\/<\/p>\n

Editor : Edi Purnomo Putra<\/p>\n

\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

Modifying Table using SQL   SQL databases organize data and relationships within tables. Sometimes, these tables also need modifications to meet new requirements. While all SQL table structures can be altered, some changes are simpler to implement than others. There are multiple way to modify a table : 1. Adding a Column The most easiest […]<\/p>\n","protected":false},"author":11,"featured_media":342,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-313","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-articles"],"_links":{"self":[{"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/posts\/313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/comments?post=313"}],"version-history":[{"count":6,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/posts\/313\/revisions"}],"predecessor-version":[{"id":338,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/posts\/313\/revisions\/338"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/media\/342"}],"wp:attachment":[{"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/media?parent=313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/categories?post=313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binus.ac.id\/semarang\/information-systems\/wp-json\/wp\/v2\/tags?post=313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}