• Unit 8: Introduction to SQL

    Structured Query Language (SQL) is the main data definition language used for the creation and maintenance of databases. In this unit, we will look at basic SQL syntax, including some data definition and data manipulation language commands. When developing or using a database, we have to consider how the data should be organized so that it is stored and accessed efficiently, and how needed information can be found. If the data is for a specific application, the solutions would be based on knowledge of the application domain. However, a database is a common system used for many applications, and thus many solutions need to be generic. Relational algebra, relational calculus, and normalization help address these problems.

    The evolution from a specific solution to a general solution is similar to the evolution from a special-purpose to a general-purpose computer, which was accomplished by the development of programming languages. In a similar manner, database languages implement the creation of databases and access of information for many applications, which allows us to use databases as a common component for many applications and types of systems.

    Completing this unit should take you approximately 5 hours.

    • Upon successful completion of this unit, you will be able to:

      • compare DDL commands to DML commands;
      • discuss when to use the appropriate SQL commands;
      • create a set of database tables using DDL;
      • describe the various types of integrity constraints and how they are used; and
      • describe the various types of Data Manipulation commands and how they are used.

    • 8.1: What is SQL?

      • Introduction to SQL URL

        This resource is a nice concise summary of SQL Data Definition, Data Manipulation, and Data Creation commands. 

      • Another Introduction to SQL Page

        This presentation discusses querying a database. It is presented in the style of a workshop, and gives practice in SQL DML (Data Manipulation Language). It also includes exercises.

    • 8.2: Data Definition Language (DDL)

      • SQL Structured Query Language File

        Chapter 15 gives a good treatment of SQL DDL, incuding data types and constraints, and SQL DML, like the SELECT and JOIN statements.

      • SQL Tutorial URL

        SQL consists of a Data Definition Language (DDL), a Data Manipulation Language (DML), and a Data Control Language (DCL). Use this resource as a reference for these languages. For now, spend some time to review the following pages in this tutorial: SELECT, JOIN, CREATE/ALTER/DROP SCHEMA, CREATE/ALTER DATABASE, SQL DROP, and CREATE/DROP/ALTER TABLE.

      • Drop Table Page

        This video illustrates the DROP TABLE command.

      • Alter Table Page

        These videos illustrate the ALTER TABLE command.

    • 8.3: Column- and Table-Level Constraints

      • SQL Tutorial URL

        Review the following pages in this tutorial: CONSTRAINTS, PRIMARY KEY, and FOREIGN KEY.

        Constraints are used in DDL commands to incorporate rules for a table. Constraints are defined with Create or Alter Table commands. The NOT NULL constraint defines a rule that a column cannot store a null value. Null and NOT NULL are also used in the Select command to find NULL or NOT NULL values in fields or columns. This rule specifies that the value in a field or column of a table is different for each record or row. Primary Key is a constraint that defines a field or column that uniquely identifies each row (is NOT NULL and is Unique). A foreign key is a column in a table that contains a value that is a primary key of another table. Thus, a foreign key links a referenced table to a referencing table. Default specifies a default value for a column. This constraint checks that a value in a column satisfies a specified condition.

    • 8.4: Data Manipulation Language (DML)

      • SQL Tutorial URL

        Review the following pages in this tutorial: INSERT STATEMENT, DELETE STATEMENT, and SELECT STATEMENT.

        We are using the SQL 2003 ANSI Standard to illustrate the SQL commands; the other databases, MySQL and OpenBase for example, have similar commands. The Insert statement adds a record into a table. Delete removes records from a table. Select is the SQL command for querying a database.

      • SQL Insert, Update, and Delete Commands Page

        This video illustrates the INSERT, UPDATE, and DELETE commands.

    • Unit 8 Assessment

      • Unit 8 Assessment Quiz
        Receive a grade

        Take this assessment to see how well you understood this unit.

        • This assessment does not count towards your grade. It is just for practice!
        • You will see the correct answers when you submit your answers. Use this to help you study for the final exam!
        • You can take this assessment as many times as you want, whenever you want.