Skip to content

Linux Fun

Artikel seputar linux, teknik informatika dan sistem informasi

Menu
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Sitemap
Menu

SQL DATA DEFINITION (DDL)

Posted on March 13, 2013 by linuxfun

TABLES

  • 1 CREATE TABLE
  • 2 ALTER TABLE
  • 3 CREATE INDEX
  • 4 DROP TABLE

CREATE TABLE

Define the structure of a new table

Format:
CREATE TABLE tablename ({col–name type [(size)][constraint],…});

The ‘constraint’ clause in the CREATE TABLE statement is used to enforce referential integrity.  Specifically, PRIMARY KEY, FOREIGN KEY, and CHECK integrity can be set when you define the table.  The syntax for key and check constraints is shown below.

{PRIMARY KEY | FOREIGN KEY} (local-field) [REFERENCES foreign-field] }

for attribute constraints…

CHECK (condition)

Example: Define the student table
CREATE TABLE STUDENT
( STUID                      CHAR(5),
LNAME                     CHAR(10) NOT NULL,
FNAME                     CHAR(8),
MAJOR                     CHAR(7) CHECK (MAJOR IN (‘HIST’,’ART’,’CIS’)),
CREDITS                  INTEGER CHECK (CREDITS > 0),
PRIMARY KEY (STUID));

ALTER TABLE

Add a column to the “right” of an existing table, modify an existing attribute, or drop an existing column or constraint.

Format:
ALTER TABLE tablename
{ADD {col-name type [(size)] | constraint}}
| MODIFY {col_name type [(size)]
| DROP {col-name [drop-clause];

where the drop clause is …

            DROP {PRIMARY KEY | UNIQUE {col-name} | CONSTRAINT constraint|

Example1: Add column called MINOR to STUDENT table
ALTER TABLE STUDENT ADD MINOR CHAR(8);

Example2: Drop the MGR-SSN column
ALTER TABLE EMPLOYEE DROP MGR-SSN;

Example3: Modify existing attribute
ALTER TABLE DEPT MODIFY MINOR CHAR(10);

LIMITATIONS/ENHANCEMENTS:

When you add a column, all existing tuples get the extra column filled with NULL values.  You have to go in and update the column to enter valid data later to get rid of the NULLs.  You can only add or drop a single column at a time in the ALTER statement.

CREATE INDEX

Create an index on attribute(s) within a table. Indexes are used to improve system performance by providing a more efficient means of accessing selected attributes.

Format:
CREATE [UNIQUE] INDEX index-name ON table-name {(col-name [ASC | DESC])};

Example1: Create an index on the STUID attribute of the STUDENT table
CREATE INDEX stuindex ON STUDENT (STUID);

Example2: Create a unique index on SSN of EMPLOYEES and make it sort in reverse order
CREATE UNIQUE INDEX empindex ON EMPLOYEES (SSN) DESC;

Example 3: Create a composite index on COURSENUM and SDUID from ENROLL table
CREATE INDEX enroll-idx ON ENROLL (COURSENUM, STUID);

DROP TABLE

Remove a table (and all data) or an index on a table from database.  If the table has any foreign key constraints then the CASCADE CONSTRAINTS clause is required to drop the table.

Format:
DROP TABLE table-name [CASCADE CONSTRAINTS];
DROP INDEX index-name;

Example1: Delete the table STUDENT (no foreign key constraints)
DROP TABLE STUDENT;

Example2: Delete the table ENROLL (with foreign key constraints)
DROP TABLE ENROLL CASCADE CONSTRAINTS;

Example3: Remove the emp-name index on employee table
DROP INDEX emp-name;

When you drop a table you also delete all data currently in that table.  Be careful!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Sifat-Sifat Benda, Pengenalan Sifat Fisik dan Sifat Kimia
  • Konsep dan Pengertian Teori Keseimbangan Umum
  • Database Oracle
  • Perpajakan, Fungsi, Tujuan, dan Sistem Perpajakan di Indonesia
  • Teori Konsumen, Memahami Perilaku Konsumen dalam Memilih Barang dan Jasa
  • Asimetri Informasi, Perilaku Agen, Moral Hazard, Adverse Selection dan Pasar Asuransi
  • Memahami Konsep Penting dalam Ekonomi Perusahaan dan Industri, Pengambilan Keputusan, Teori Pasar, Investasi, dan Strategi Perusahaan
  • Mempelajari Konsep Keseimbangan Umum, Interaksi Pasar Barang dan Faktor Produksi serta Peran Pemerintah dalam Perekonomian
  • Efisiensi Pasar dan Peran Pemerintah dalam Meningkatkan Kesejahteraan Masyarakat, Memahami Konsep-konsep dalam Ekonomi Kesejahteraan
  • Eksternalitas dan Public Goods, Mempelajari eksternalitas, manfaat publik, biaya dan manfaat sosial, dan peran pemerintah dalam mengatasi pasar yang tidak sempurna
  • Analisis Pasar Monopoli, Memahami Monopoli, Persaingan Monopolistik, Oligopoli, Kartel, dan Pengaruhnya Terhadap Harga dan Kualitas Produk atau Jasa

Categories

  • Adobe
  • Alga
  • Algoritma
  • Algoritma Genetika
  • Android
  • Basis Data
  • Biologi SMA Kelas 10
  • Biometrik
  • Borland Delphi
  • Buku Pelajaran SMA
  • Buku Pelajaran SMK
  • Buku Pelajaran SMP
  • Business Intelligence
  • C++
  • Cacing
  • Citra Digital
  • Clustering
  • Customer Relationship Management
  • Data Flow Diagram
  • Data Mining
  • Data Warehouse
  • Database Terdistribusi
  • Databases
  • Desain Grafis
  • Ekonomi
  • Ekonomi Makro
  • Ekonomi Micro
  • Ekonomi SMA Kelas 12
  • Enterprise Resource Planning
  • Entity Relationship Diagram
  • Financial Management
  • Fuzzy
  • Game
  • Ganggang
  • Globalisasi
  • Hewan
  • Hidden Markov Model
  • Ilmu Pengetahuan Sosial SMP Kelas 9
  • Internet
  • Invertebrata
  • Jamur
  • Java
  • Keanekaragaman Hayati
  • Kebijakan Publik
  • Kecerdasan Buatan
  • Klasifikasi Makhluk Hidup
  • Knowledge Management
  • Komputer
  • Koperasi
  • Kriptografi
  • Logika Informatika
  • Manajemen
  • Manajemen Badan Usaha
  • Manajemen Proyek Sistem Informasi
  • Microsoft Acces
  • Monera
  • Multimedia
  • My SQL
  • Negara Berkembang
  • Negara Maju
  • Network Management
  • Otonomi Daerah
  • Pascal
  • Pelajaran IPA SMP Kelas 1
  • Pembelaan Negara
  • Pendidikan Kewarganegaraan SMP Kelas 9
  • Pengenalan Pola
  • Penutupan Siklus Akuntansi
  • Perang Dunia II
  • Perpajakan
  • PHP
  • PLC
  • Prestasi Diri
  • Protista
  • Prototype
  • Rekayasa Perangkat Lunak
  • Siklus Akuntansi Perusahaan Dagang
  • Sistem Informasi
  • Sistem Informasi Geografis
  • Sistem Kelistrikan
  • Sistem Pakar
  • Sistem Pendukung Keputusan
  • Sistem Pengapian
  • SMS Gateway
  • SQL Server
  • Teknik Sepeda Motor SMK Kelas 11
  • Teori Ekonomi
  • Thyristor
  • Tingkatan Manajemen
  • Tumbuhan
  • UML
  • Uncategorized
  • Vertebrata
  • Video
  • Virus
  • Visual Basic
© 2023 Linux Fun