1 / 26

HSCI 709

HSCI 709. SQL Data Definition Language. SQL Standard. SQL-92 was developed by the INCITS Technical Committee H2 on Databases. SQL-92 was designed to be a standard for relational database management systems (RDBMSs) Next Version: SQL3

thina
Download Presentation

HSCI 709

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. HSCI 709 SQL Data Definition Language

  2. SQL Standard • SQL-92 was developed by the INCITS Technical Committee H2 on Databases. • SQL-92 was designed to be a standard for relational database management systems (RDBMSs) • Next Version: SQL3 • Will enhance SQL into a computationally complete language for the definition and management of persistent, complex objects. • generalization and specialization hierarchies • multiple inheritance • user defined data types • triggers and assertions • support for knowledge based systems • recursive query expressions • and additional data administration tools • abstract data types (ADTs) • object identifiers • Methods • Inheritance, Polymorphism, Encapsulation, and all of the other facilities normally associated with object data management

  3. SQL Programmer • You have been doing it and you did not know it.

  4. SQL Programmer • You have been doing it and you did not know it.

  5. SQL Components SQL DCL DDL DML Data I/O RDBMS Structure DBA Activities Create Record Create/Delete DBs Create Users Read Record Delete Users Create/Delete Tables Grant privileges Update Record Alter Tables Implement AccessSecurity Delete Record

  6. "Create Table" Syntax CREATE TABLE <myTable> (<Field1> <DataType>, <Field2> <DataType>,…);

  7. "Create Table" Syntax CREATE TABLE <myTable> (<Field1> <DataType>, <Field2> <DataType>,…); CREATE TABLE PAT (PAT_ID INT, PAT_FM TEXT(20),PAT_LNM TEXT(35));

  8. Create Table in MS Access(1)

  9. Create Table in MS Access(2)

  10. Create Table in MS Access(3) DATA TYPE TABLE NAME FIELD NAME

  11. "Alter Table" Statement • This statement can be used to: • Add new columns • Delete defined columns • Make a column the primary key • Change column data types • Change table names* • Make a column a foreign key • Add constraints to a column, e.g., indexing * NOT SUPPORTED IN MS ACCESS

  12. Adding a New Column ALTER TABLE <myTable ADD COLUMN <NewField> <DataType>;

  13. Adding a New Column ALTER TABLE PAT ADD COLUMN PAT_DESCR MEMO; ALTER TABLE <myTable> ADD COLUMN <NewField> <DataType>;

  14. MS Access Effects

  15. Deleting a Column ALTER TABLE <myTable> DROPCOLUMN <Field>;

  16. Deleting a Column ALTER TABLE <myTable> DROPCOLUMN <Field>; ALTER TABLE PAT DROPCOLUMN PAT_DESCR;

  17. MS Access Effects

  18. Creating a PK ALTER TABLE <myTable> ADD PRIMARY KEY(<Field>);

  19. Creating a PK ALTER TABLE PAT ADD PRIMARY KEY(PAT_ID); ALTER TABLE <myTable> ADD PRIMARY KEY(<Field>);

  20. MS Access Effects

  21. Changing Data Types ALTER TABLE <myTable> ALTER COLUMN <field> <newDataType>;

  22. Changing Data Types ALTER TABLE <myTable> ALTER COLUMN <field> <newDataType>; ALTER TABLE PAT ALTER COLUMN PAT_LNM TEXT(55);

  23. Foreign Keys PAT CLNCIAN PAT_ID INT CLNCIAN_ID INT PAT_FNM TEXT(20) PAT_LNM TEXT(35) CLNCIAN_NM TEXT(250) PAT_ID

  24. Foreign Keys PAT CREATE TABLE CLNCIAN (CLNCIAN_ID INTEGER PRIMARY KEY, CLNCIAN_NM TEXT (55), PAT_ID INT, CONSTRAINT FKPatId FOREIGN KEY (PAT_ID) REFERENCES PAT); CLNCIAN PAT_ID INT CLNCIAN_ID INT PAT_FNM TEXT(20) PAT_LNM TEXT(35) CLNCIAN_NM TEXT(250) PAT_ID

  25. MS Access

  26. Take Home Lesson SQL commands can do what Graphical User Interface does in Access

More Related