SQL Intro: Learning to Query Databases with DDL and DML Commands, Slides of Introduction to Database Management Systems

Download SQL Intro: Learning to Query Databases with DDL and DML Commands and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Introduction to Structured

Query Language (SQL)

In this chapter, you will learn:

• The basic commands and functions of SQL
• How to use SQL for data administration (to create tables, indexes, and views)
• How to use SQL for data manipulation (to add, modify, delete, and retrieve data)
• How to use SQL to query a database to extract useful information

Introduction to SQL (continued)

• SQL is relatively easy to learn
• Basic command set has a vocabulary of less than 100 words
• Nonprocedural language
• American National Standards Institute (ANSI) prescribes a standard SQL
• Several SQL dialects exist

SQL Data Definition

Commands

Data Definition Commands

• Examine the simple database model and the database tables that will form the basis for the many SQL examples
• Understand the data environment

The Database Model

The Database Schema

• Authentication
  • Process through which the DBMS verifies that only registered users are able to access the database
  • Log on to the RDBMS using a user ID and a password created by the database administrator
• Schema
  • Group of database objects—such as tables and indexes—that are related to each other

Data Types

• Data type selection is usually dictated by the nature of the data and by the intended use
• Pay close attention to the expected use of attributes for sorting and data retrieval purposes

Creating Table Structures

• Use one line per column (attribute) definition
• Use spaces to line up the attribute characteristics and constraints
• Table and attribute names are capitalized
• NOT NULL specification
• UNIQUE specification
• Primary key attributes contain both a NOT NULL and a UNIQUE specification
• RDBMS will automatically enforce referential integrity for foreign keys
• Command sequence ends with a semicolon

Other SQL Constraints

• NOT NULL constraint
  • Ensures that a column does not accept nulls
• UNIQUE constraint
  • Ensures that all values in a column are unique
• DEFAULT constraint
  • Assigns a value to an attribute when a new row is added to a table
• CHECK constraint
  • Validates data when an attribute value is entered

A Duplicated TEST Record

Data Manipulation Commands

• Adding table rows
• Saving table changes
• Listing table rows
• Updating table rows
• Restoring table contents
• Deleting table rows
• Inserting table rows with a select subqueryDocsity.com

A Data View and Entry

Form

Saving Table Changes

• Changes made to table contents are not physically saved on disk until - Database is closed - Program is closed - COMMIT command is used
• Syntax
  • COMMIT [WORK]
• Will permanently save any changes made to any table in the database Docsity.com