SQL Commands

• Pure SQL Commands, Functions, Operators & Clauses

• CREATE STATEMENT
    CREATE TABLE CUSTOMER
    (CUSTID CHAR(4), LN CHAR(10), FN CHAR(10), STREET CHAR(25),
     CITY CHAR(20), STATE CHAR(2), ZIPCODE CHAR (9))

• INSERT STATEMENT
   INSERT INTO CUSTOMER VALUES
  ('9999','John','Bass','123 Walnut Street','Philadelphia','PA','191009')

• SELECT STATEMENT
  SELECT * FROM CUSTOMER
  SELECT CUSTID, LN, FN, FROM CUSTOMER

• UPDATE STATEMENT
    UPDATE CUSTOMER
     SET CUSTID = "8888"

• ALTER STATEMENT
    ALTER TABLE CUSTOMER
    ADD CUSTID CHAR(6)

• DELETE COMMAND
    DELETE FROM CUSTOMER
    WHERE CUSTID = "8888"

• COMMIT AND ROLLBACK COMMANDS
    Use rollback to undo the update
    Use commit only then when you are sure update is fine.
    Once you commit it is permanently changed.

• DROP TABLE, VIEW, INDEX COMMAND
     DROP TABLE CUSTOMER
     (This means you delete the whole table to create it again)
     SQL does not have many command but need the logic to use them in
     different business sitiuations with proper logic to get the job done.
     There are more than what have been used in these statments in terms
     of clauses, operators, etc. SQL needs practice and work experience.

• PLSQL Programming

  The stored function that takes input custID and returns city (not code):
   create or replace function get_city(cnum in customer.custid%type)
        return zipcode.city%type as ccity zipcodes.city%type;
   begin
       select city into city from customer.zipcode
        where custid = cnum and customer.zip = zipcode.zip;
   return (ccity);
   end;

Execute this way SQL>select cno, cname, get_city(custid) from customer;

• Transact SQL Programming

IF(SELECT COUNT(*)
         FROM sales
         WHERE itemname = "Orange"
         GROUP BY itemname)>3
    PRINT 'The custid of customer in the item orange is 4 or more'
    ELSE BEGIN
              PRINT 'The following customer who bought the orange'
              SELECT FN, LN
              FROM customer, sales
              WHERE customer.custid = sales.custid
              AND itemname = "Orange"
          END

Return