chapter 8

Cards (60)

  • SQL
    Structured Query Language - a query language that allows users to specify conditions to retrieve data or information from a database
  • SQL
    • Relatively easy to learn
    • Basic command set has vocabulary of less than 100 words
    • Nonprocedural language
    • ANSI prescribes a standard SQL
  • ANSI
    American National Standards Institute - an organization that oversees the creation, promulgation and use of thousands of norms and guidelines that directly impact businesses
  • Query
    A user request to retrieve data or information from a database with certain conditions
  • SQL
    • Allows users to specify conditions to retrieve data or information from a database
    • Results of the query are stored in the form of a table
    • Provides statistical information of the data
    • Goes through all records in the database and selects those that satisfy the condition
  • SQL functions fit into two broad categories: data definition language and data manipulation language
  • Data definition language
    SQL commands to create database objects, such as tables, indexes, and views, and define access rights to those objects
  • Data manipulation language
    SQL commands to insert, update, delete, and retrieve data within database tables
  • Data definition vs data manipulation
    • CREATE TABLE VENDOR (V_CODE INTEGER NOT NULL UNIQUE, PRIMARY KEY (V_CODE)) - data definition
    • UPDATE PRODUCT SET P_INDATE = 18-JAN-2012 WHERE P_CODE = '13564' - data manipulation
  • SQL Server Management Studio is a tool used to administer and develop SQL Server database objects
  • SQL Server Management Studio
    • Has 13 tools available from the View menu, standard toolbar, and associated hotkeys
    • Some useful tools include Object Explorer, Template Explorer, and Solution Explorer
  • Creating a new database in SQL Server Management Studio
    1. Right click on Databases in Object Explorer
    2. Select New Database
    3. Name the database
  • To do queries in SQL Server Management Studio, you need to connect to the database engine and set the Available Databases to the database you want to use
  • The basic SQL query syntax is: SELECT all(*) / select(name of columns) FROM tablename WHERE condition
  • Authentication
    The process through which the DBMS verifies that only registered users are able to access the database
  • Schema
    A group of database objects, such as tables and indexes, that are related to each other
  • Numeric data types in SQL
    • Int/Integer
    • Smallint
    • Tinyint
    • Decimal
    • Number
  • Date data types in SQL
    • Datetime
    • Smalldatetime
    • Date
  • Character string data types in SQL
    • Char
    • Varchar
  • Example data types for a product database
    • Product_Price - Number
    • Vendor_code - Integer
    • Product_quantity on hand - Smallint
    • Product_minimum units - Smallint
    • Product_discount - Number
  • The data is from January 1, 1900, through June 6, 2079, with an accuracy of one minute
  • Char (L)
    Fixed-length character data with a maximum length of 8,000 characters. Remaining spaces will be left unused and stored as maximum specified. E.g. CHAR (25) – Smith and Van der Merwe stored as 25 characters
  • Varchar (L)
    Variable-length data with a maximum of 8,000 characters. Will not leave unused spaces if max not used
  • Some more examples
    • V_code (4 digits product code)
    • V_name (35 characters product description)
    • V_contact (Name of contact person)
    • V_areacode (4 digit postal code)
    • V_phone (10 digit cellphone number)
    • V_country (Use abbreviation of 2 letters)
    • V_order (Use number from previous order)
  • V_code
    4 digits, Integer
  • V_name
    35 characters, Char(35)
  • V_contact
    Name of contact person, Char(25)
  • V_areacode
    4 digit postal code, Char/Integer(5)
  • V_phone
    10 digit cellphone number, Char/Integer(25)
  • V_country
    Use abbreviation of 2 letters, Char(2)
  • V_order
    Use number from previous order, Char/Integer(1)
  • Creating Table Structures
    1. Use one line per column (attribute) definition
    2. Use spaces to line up attribute characteristics and constraints
  • NOT NULL constraint

    Ensures that column does not accept nulls
  • UNIQUE constraint
    Ensures that all values in column are unique
  • DEFAULT constraint
    Assigns value to attribute when a new row is added to table
  • CHECK constraint
    Validates data when attribute value is entered e.g min order
  • Primary key attributes contain both a NOT NULL and a UNIQUE specification
  • RDBMS will automatically enforce referential integrity for foreign keys
  • Creating Table Structures
    CREATE TABLE TABLENAME (
    COLUMN1 datatype
    constraint,
    COLUMN2 datatype
    constraint,
    PRIMARY KEY (COLUMN1));
  • Creating the Vendor table

    • CREATE TABLE VENDOR (
    V_CODE
    INTEGER
    NOT NULL UNIQUE,
    V_NAME
    VARCHAR (35)
    NOT NULL,
    PRIMARY KEY (V_CODE));