CS403 Study Guide

Unit 8: Introduction to SQL

8a. compare DDL commands to DML commands

  • What are DDL and DML?
  • What is the general syntax of the commands?

Early versions (1992, 2003) of SQL grouped SQL commands into categories, three of which are widely used: DDL, DML, and DCL. DDL (data definition language) commands are used to create a database and to enter and organize data in tables; DML (data manipulation language) commands are used to add, remove, update, and select the data in the tables; and DCL (data control language) commands are used to manage the DBMS. In this Unit, we address DDL commands, which are used to define the tables in the database, and DML commands, which are used to maintain the data in the tables and to select data from the tables. DML commands correspond to the relational algebra operations. SQL commands or statements are constructed of components called clauses, expressions (which include functions), and predicates or conditions.

 

8b. discuss when to use the appropriate SQL commands

  • Who uses these commands?
  • When and why are they used?
  • How are they used?

SQL has a relatively small number of DDL commands: CREATE, ALTER, DROP for defining the database and its structure. There are a few more DML commands: INSERT, UPDATE, DELETE, SELECT for entering data, maintaining and selecting it.

DDL commands are used by database administrators to initially define and periodically maintain a database and its tables. DML commands are by application users on a daily basis to access the data to perform application-specific functions with the data, and by end-users on a daily basis to query, update, or report the data. Application users include people and application programs.

In the following learning objective discussions, you should use the SQL Sandbox (a learning environment for trying and experimenting with SQL commands). 

 

8c. create a set of database tables using DDL

  • What is the syntax of the CREATE command?
  • What is the semantics of the CREATE command?
  • After a table is created, can it be changed?

The CREATE command or statement is used to create a set of database tables. The syntax of the CREATE command is as follows:

CREATE <table name>
( <column name 1> datatype optional column constraint, 
<column name 2> datatype optional column constraint, 
    .
    .
    .
<column name n>, datatype, optional column constraint
Optional table constraints
);

The meaning or semantics of the CREATE statement is the definition of a table with the name <table name> and columns, <column name 1>, <column name 2>, ... , and <column name n. The last clause defines any optional table constraints. Any 'name' within < > are application-specific names. Datatype is one of many (twenty or more) data types for the value domain of a column, for example, character or integer type. 

Optional column constraint may be NULL, NOT NULL, UNIQUE, PRIMARY KEY, and DEFAULT.

Optional table constraints may be IDENTITY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT.

After a table is created, the ALTER command is used to remove columns and to add or remove constraints. Don't forget that commercial SQLs may adhere to the ANSI (American National Standards Institute) standards, but may also have extensions and variations depending on the DBMS they support, such as Microsoft SQL Server, MySQL, PostgreSQL, or Oracle.

The DDL DROP statement is used to remove a database or to remove a table from the database:

DROP DATABASE database name;
DROP TABLE table name;

Try the CREATE statement using the sandbox to define and create a database table called Student: use the schema Student(StudentId, StudentName, Department, AdmitDate).

 

8d. describe the various types of integrity constraints and how they are used

  • Describe the SQL column integrity constraints.
  • Describe the SQL table integrity constraints.
  • What SQL commands can include integrity constraints?
  • What is the syntax for a SQL integrity constraint?

Integrity constraints are SQL keywords that restrict the initial value for a field of a record (the row-column intersection of a table) or restricts the values for a column. Constraints are SQL features that represent the semantics of the application. SQL column integrity and table constraints are keywords used in the CREATE and ALTER statements. Constraints are used to check the validity of an action; if a constraint is violated during an action on a table, the constraint aborts the action. Depending on the dialect and version of SQL, more or less constraints may be available. 

Typical column integrity keywords are NULL, NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT. The keyword PRIMARY KEY is followed by the primary key name; the keyword DEFAULT by the default value. Several of these column constraints, like UNIQUE and PRIMARY KEY, can also be specified as table constraints. 

Typical table integrity keywords are IDENTITY, UNIQUE, FOREIGN KEY, PRIMARY KEY, CHECK, and DEFAULT. A table constraint can be given a name using the following syntax: CONSTRAINT <constraint name> <constraint keyword>, with additional syntax depending on the keyword. Each table constraint key may have additional syntax to identify necessary data associated with the constraint.

Try the CREATE statement using the sandbox to define column and table constraints for the database table called Student that you created.

 

8e. describe the various types of Data Manipulation commands and how they are used

  • What are the standard SQL DML statements?
  • What is the generic syntax for the SQL DML statements?
  • What is their semantics?
  • Who uses the DML statements?
  • When do they use them?

After the database and tables are created, we want to populate the tables with actual values, to search the tables for information, and, eventually, to update the tables. We use the DML statements to perform those tasks. Whereas DDL only includes a few statements, CREATE, ALTER, DROP, which pertain to the organization of the data in tables, DML includes SELECT, INSERT, UPDATE, DELETE, and JOIN statements, which pertain to the values stored in the tables.

The syntax of the SELECT command has the form:

SELECT parameters
FROM <table name>
WHERE <condition>;

Optional parameters are written, SELECT * to signify all columns; or, SELECT {DISTINCT <column> AS <alias>,...}, where the braces indicate that a list of parameters, separated by columns can be specified. 'Alias' is an optional keyword used as the column heading for the attribute <column>. An expression, which computes a derived value from the field's values, can also be used in place of a column. DISTINCT is an optional keyword that signifies that only one column value for each record shall be retrieved. If there are duplicate values in a column, only one will be retrieved. The WHERE keyword specifies a condition that filters column values. 

SELECT is a retrieval command that finds data in a table using conditions specified in the parameters, keywords, or clauses.

INSERT INTO <table name> columns
VALUES ( .....); 

INSERT takes three parameters: the name of the table to receive values, the column names where the values will be stored, and the values. If the columns are not specified, the values must comprise one record for the table. If the columns are specified, the values must correspond in order to those columns. Thus, this statement adds a full or partial record to the table.

UPDATE <table name> SET column 1=value 1, column 2=value 2, ..., column n=value n
WHERE <condition>;

UPDATE changes values in a table. The parameters identify the values that update the columns and the WHERE condition identifies the rows that will be updated.

DELETE FROM <table name> 
WHERE condition;

DELETE has one parameter, the name of the table. The WHERE clause is used to specify the record to be deleted. If the WHERE clause is not used, all records will be deleted from the table.

DDL statements are used by database designers and analysts when the database is being developed, tested, and deployed. DML statements are used by database administrators, application users, and end-users when the database is operational.

The syntax of a DDL or DML statement has a meaningful name that suggests its semantics, and has parameters, keywords, and/or clauses that provide the information needed to carry out the behavior of semantics of the command. 

Try the commands with the table you have created in the SQL sandbox.

 

Unit 8 Vocabulary

This vocabulary list includes terms that might help you with the review items above and some terms you should be familiar with to be successful in completing the final exam for the course. 

Try to think of the reason why each term is included.

  • DDL
  • DML
  • Syntax
  • Semantics
  • Users
  • CREATE
  • ALTER
  • DROP
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • JOIN