Some SQL queries

• Our data
  • The Cape Codd Outdoor Sports Company has a simple database.
  • You have used part of this in your exercises.
  • Page 36 and 37 give some details, 93-95 give more.
• A database schema is a standard way to give a complete logical view of the tables in a database.
  • It shows all tables
  • And all of the fields within the tables.
  • It shows all keys
  • And indicates foreign keys
  • Tables are given as tuples (field1, field2, ... fieldn)
  • Primary keys are underlined.
  • Foreign keys are given in italics
• So the codd database is
  • SKU_DATA(SKU, SKU_Description, Department, Buyer)
  • RETAIL_ORDER(ORDERNumber, StoreNumber, StoreZIP, OrderMonth, OrderYear, OrderTotal)
  • ORDER_ITEM(OrderNumber,SKU,Quantity,Price, ExtendedPrice)
  • WAREHOUSE(WarehouseID,WarehouseCity, WarehouseState,Manager, SquareFeet)
  • INVENTORY(WarehouseID,SKU,SKU_Description,QuantityOnHand,QuantityOnOrder)
• SQL
  • Structured Query Language
  • This is a special purpose language
  • Sometimes called a fourth generation language
  • This is a pseud-standard
  • Endorsed by American National Standards Institute (ANSI)
  • And by the International Organization for Standardization (ISO)
  • There are many versions of the standard, as it grows through the years.
• BUT
  • "Despite the existence of such standards, though, most SQL code is not completely portable among different database systems without adjustments.", Wikipedia.
  • "The SQL3 standard represents the ideal, but very few vendors currently meet or exceed the Core SQL3 requirements. The Core standard is like the interstate speed limit: some drivers go above it and others go below it, but few go exactly the speed limit", SQL in a Nutshell.
  • Each version has some variation in datatypes
  • And in extensions to the language
  • And some don't do all pieces.
  • The MySQL Standards Compliance statement in the manual.
• SQL Consists of
  • A data definition language,

    • 	      DROP TABLE IF EXISTS RETAIL_ORDER;
      
      CREATE TABLE RETAIL_ORDER (
         OrderNumber  INTEGER         PRIMARY KEY,
         StoreNumber  INTEGER         NOT NULL,
         StoreZIP     VARCHAR(9)      NOT NULL,
         OrderMonth   VARCHAR(12)     NOT NULL,
         OrderYear    SMALLINT        NOT NULL,
         OrderTotal   DECIMAL(11,2)   NOT NULL
      ) ENGINE=INNODB;
      
      	      

  • A Data manipulation Language

    • SELECT * FROM WAREHOUSE;
      	      

    • INSERT INTO ORDER_ITEM
              (OrderNumber, SKU, Quantity, Price, ExtendedPrice)
              VALUES
              (1000, 201000,1,300.00,300.00),
              (1000, 202000,1,130,130);
      	      

  • SQL/Persistent stored modules
    • A way to define functions and procedures
    • Extend SQL in some sense
  • A Transaction Control Language
    • START TRANSACTION
    • COMMIT
  • A Data Control Language

    • CREATE USER 's743916s'@'localhost' 
          IDENTIFIED BY 'xxxxxxxx';
      
      GRANT USAGE ON * . * 
         TO 's743916s'@'localhost' 
         IDENTIFIED BY 'xxxxxxxxx' 
         WITH 
             MAX_QUERIES_PER_HOUR 0 
             MAX_CONNECTIONS_PER_HOUR 0 
             MAX_UPDATES_PER_HOUR 0 
             MAX_USER_CONNECTIONS 0;
      
      CREATE DATABASE IF NOT EXISTS `s743916s`;
      
      GRANT ALL PRIVILEGES ON `s743916s` . * 
          TO 's743916s'@'localhost';
      
      DROP DATABASE IF EXISTS s743916s;
      
      DROP USER  's743916s'@'localhost';