CREATE - creating a database

CREATE DATABASE databaseName;

CREATE - creating a table

CREATE TABLE tableName (

    column1 dataType,

    column2 dataType constraint,

    column3 dataType

);

ALTER - adding key fields

• add primary keys
• add foreign keys

ALTER TABLE tableName 

    ADD PRIMARY KEY (primaryKey);

ALTER TABLE tableName2 

    ADD FOREIGN KEY (primaryKey)

        REFERENCES tableName(primaryKey);

ALTER - add / delete columns

• add columns and respective data type
• remove columns

ALTER TABLE tableName

ADD columnName dataType;

ALTER TABLE tableName

DROP COLUMN columnName;

ALTER - changing field data type

ALTER TABLE tableName

ALTER COLUMN columnName newDataType;

NOT NULL

ensures that a column cannot have a NULL (empty) value

UNIQUE

ensures that all values in a column are different

PRIMARY KEY (key)

a combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY (key)

uniquely identifies a row/record in another table

CHECK (condition)

ensures that all values in a column satisfies a specific condition

DEFAULT <value>

sets a default value for a column when no value is specified

INDEX

used to create and retrieve data from the database very quickly

DML

Also known as Data Manipulation Language.

• used when a database is first created
• queries and management can be done using DML
• populates tables with data
  • adding (inserting)
  • deleting
  • modifying

Commands

Data maintenance

INSERT INTO Band ('ComputerKidz', 5);

INSERT INTO Band-Booking (BandName, BookingID)

    VALUES ('ComputerKidz', '2016/023');

UPDATE Band

    SET NumberOfMembers = 6;

DELETE FROM BandName

    WHERE BandName = 'ITWizz';

Queries

SELECT BandName

    FROM Band

        ORDER BY BandName;

SELECT BandName

    FROM Band-Booking

        WHERE Headlining = 'Y'

            GROUP BY BandName;

SELECT

selects an attribute

• a query always starts with SELECT

FROM

locates attribute's table: 'Band'

ORDER BY

produces an ordered list, sorted by 'BandName'

WHERE

identifies items in 'BandName' that have the Headlining attribute as 'Y'

GROUP BY

groups (puts together) these identified items by 'BandName'