HSCI 709

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