Chapter 6 and 7: SQL Commands and Data Defintion

SQL COMMANDS

  • Where - Filters the rows subject to some condition

    • Between/Not Between - tests endpoints

    • Could also use where + and

  • Group By - forms groups of rows with the same column value

 

  • Having - filters he groups subject to csome condition

  • Specifies which columns are to appear in the output

  • Order By - specifies the order of the output

    • Strings are ordered alphabetically default (ASC)

      • Use DESC to reverse

    • Numeric data Is ordered in ascending order (smallest to largest)

      • Use DESC to order largest to smallest

  • From - specifies the table/s to be used

  • Distinct - removes duplicates

  • As - when using calculated fields, you can name the new column

SELECT staffNo, salary/12 AS monthlySalary

FROM Staff

  • Without it, it would be given the name "col2"

  • In - specify multiple values in a where clause

SELECT staffNo, position

FROM Staff

WHERE position IN ('Manager','Supervisor')

  • COUNT, MIN, and MAX can be used for both numeric and nonnumerric data but SUM and AVG can only be used on numeric data

Updating the Database

INSERT

 

INSERT INTO Staff (staffNo, fName,lname, position, salary, branchNo)

VALUES ('SG44', 'Anne', 'Jones', 'Assitant', 65000, 'B003')

UPDATE

 

UPDATE Staff

SET salary = 80000, position = 'Manager'

WHERE staffNo = 'SG44'

DELETE

 

DELETE FROM Viewing

WHERE propertyNo = 'PG4'

---

DELETE FROM Viewing: deletes all rows from table

  • Deleting the database

    • DROP DATABASE databasname;

  • Creating a database

    • CREATE DATABASE databasename;

  • Add Column

    • ALTER TABLE tableName;

ADD columnName datatype;

  • Dropping a column

ALTER TABLE tableName

DROP COLUMN columnName;

  • Dropping a table

DROP TABLE tableName