SQL

• What is the main focus of Chapter 7 in the book?

  Introduction to Structured Query Language (SQL)
• What will students learn in this chapter about SQL?

  • Basic commands and functions of SQL
  • SQL for data administration (creating tables and indexes)
  • SQL for data manipulation (adding, modifying, deleting, retrieving data)
  • SQL for querying databases for useful information
• What are the two broad categories of SQL functions?

  Data definition language and data manipulation language
• How many words does the basic SQL command set have?

  Fewer than 100 words
• Who prescribes the standard SQL?

  American National Standards Institute (ANSI)
• What is the purpose of the CREATE TABLE command in SQL?

  To create a new table in the database
• What are the two tasks involved in creating a database?

  1. Create database structure
  2. Create tables to hold end-user data
• What dictates the selection of data types in SQL?

  Nature of data and intended use
• What are some supported data types in SQL?

  • Number(L,D), Integer, Smallint, Decimal(L,D)
  • Char(L), Varchar(L), Varchar2(L)
  • Date, Time, Timestamp
  • Real, Double, Float
  • Interval day to hour
• What is the syntax for the CREATE TABLE command?

  CREATE TABLE tablename ( column1 data type [constraint], ... );
• What does the NOT NULL specification ensure in a table?

  Data entry will be made and not left empty
• What does the UNIQUE specification do in a table?

  Creates a unique index to avoid duplicates
• What is the purpose of the PRIMARY KEY specification?

  Enforces entity integrity requirements
• What does the ON UPDATE CASCADE specification ensure?

  Changes in VENDOR’s V_CODE apply to all references
• What are the SQL constraints mentioned in the material?

  • NOT NULL: Ensures column does not accept nulls
  • UNIQUE: Ensures all values in column are unique
  • DEFAULT: Assigns value when a new row is added
  • CHECK: Validates data when attribute value is entered
• What does the CHECK constraint validate in the CUSTOMER table?

  Ensures CUS_AREACODE is within specified values
• What does the DEFAULT constraint do in the INVOICE table?

  Assigns a default date to a new invoice
• What are the basic data manipulation commands in SQL?

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
  • COMMIT
  • ROLLBACK
• What is the purpose of the INSERT command?

  To enter data into a table
• What is the syntax for the INSERT command?

  INSERT INTO tablename VALUES (value1, ...);
• What must be done to save changes made to table contents?

  Use the COMMIT command
• What does the SELECT command do?

  Lists contents of a table
• What is the syntax for the SELECT command?

  SELECT columnlist FROM tablename;
• What does the UPDATE command do?

  Modifies data in a table
• What is the syntax for the DELETE command?

  DELETE FROM tablename [WHERE conditionlist];
• What does the ROLLBACK command do?

  Undoes changes since last COMMIT
• What is the purpose of the INSERT command with a SELECT subquery?

  Inserts multiple rows from another table
• What can be used to fine-tune a SELECT command?

  Conditional restrictions, arithmetic operators, logical operators
• What does the WHERE clause do in a SELECT statement?

  Adds restrictions to rows included in output
• What is the rule of precedence for arithmetic operators?

  Operations within parentheses first
• What are the logical operators used in SQL?

  AND, OR, and NOT
• What does the BETWEEN operator check?

  Whether attribute value is within a range
• What does the EXISTS operator check?

  If subquery returns any rows
• What are the options available with the ALTER command?

  • ADD: Adds a column
  • MODIFY: Changes column characteristics
  • DROP: Deletes a column
• What does the ALTER command do?

  Makes changes in table structure
• What happens when you use ALTER to change a column's data type?

  Some RDBMSs may not permit changes if column is not empty
• What does the ORDER BY clause do in a SELECT statement?

  Sorts output by specified columns
• What does the DISTINCT clause do in a SELECT statement?

  Produces a list of unique values
• What does the COUNT function do in SQL?

  Tallies number of non-null values of an attribute
• What does the GROUP BY clause do in SQL?

  Creates frequency distributions