DDL: Creation/modification of the database structure

Data Types:

• CHARACTER

  Represents one character only

• VARCHAR(n)/Text/Alphanumeric
  Represents a string. n represents the max number of letters

• BOOLEAN
  TRUE/FALSE

• INTEGER
  A whole number (Maybe +ve, -ve or 0)

• REAL
  A number with decimal

• DATE
  May use string with dd/mm/yyyy format

• TIME

  May use string with HH:mm AM/PM format

Creating a Database

CREATE DATABASE <database-name>
Eg.
CREATE DATABASE library.db

Creating a Table

CREATE TABLE <table-name> (...)

Eg.
CREATE TABLE Orders(
OrderID INTEGER NOT NULL PRIMARY KEY,
OrderNumber INTEGER NOT NULL,
CustomerID INTEGER FOREIGN KEY REFERENCES Customers(CustomerID)
);

Eg. (Recommended)
CREATE TABLE Class(
ClassID VARCHAR(5) NOT NULL,
Description VARCHAR(30),
StartDate DATE,
ClassTime TIME,
NoOfSessions INTEGER NOT NULL,
AdultsOnly BOOLEAN,
Room VARCHAR(6),
PRIMARY KEY (ClassID, Room),
FOREIGN KEY (Room) REFERENCES RoomTable (RoomNo));

Changing/Altering a Table

ALTER TABLE <table-name> <the command>
Eg.

Add new field
ALTER TABLE username ADD COLUMN height REAL;

Modify Data Type
ALTER TABLE username MODIFY COLUMN height INTEGER;

Remove a Column
ALTER TABLE username DROP COLUMN email;

Adding a Primary Key

ADD PRIMARY KEY (field/fields)

Adding a Foreign Key

ADD FOREIGN KEY (field) REFERENCES <table>(field)

Deleting a Table

DROP TABLE users;

8.3.2 Data Manipulation Language (DML)

DML: Query and maintenance of data is done using this language

Insert Data to table

INSERT INTO <table-name>(field1, field2, ……)
VALUES (value1, value2, value3, ……);

Eg.

Let a table be defined: Vehicle(regNo, model, year)

Option 1: (Using field names, field names serial might be altered)

INSERT INTO Vehicles(model, regNo, year)
VALUES('Corolla X', 'DHAKA-METRO-T-58-5658', 2007);

Option 2: (Without Using field names, field names serial must match with Table Definition)

INSERT INTO Vehicles
VALUES('DHAKA-METRO-T-58-5658', 'Corolla X', 2007);

Deleting a record

DELETE FROM <table-name>
WHERE <condition>

Updating a field in a table

UPDATE <table-name>
SET <field-name> = <value>
WHERE <conditions>

<Conditions> SQL Operators

Query General Format

SELECT <Fields/Functions separated with comma> AS <name>
FROM <table-names separated by comma>
WHERE <conditions separated by AND or OR>
ORDER BY <field-name/s> ASCENDING/DESCENDING
GROUP BY <field-name/s>;

Note: When using multiple fields from different tables, mention the field name by <table-name>.<field-name>. “AS <name>” assigns a display name to the selected field and is option

SELECT Command

SELECT *: Selects all the records from specified table
SELECT <field-name>: Returns the select field name/names (separated using ,)
SELECT COUNT(<field-name>): Returns no. of field-names
SELECT SUM(<field-name>): Returns sum of field-names
SELECT AVG(<field-name>): Returns avg of field-names

INNER JOIN

This combines data from two tables (or more) into a single query.

General Syntax
SELECT <Field/Function>
FROM <table1>
INNER JOIN <table2> ON <table1.column> = <table2.column>
WHERE <conditions>
ORDER BY <field-name>
GROUP BY <field-name>;

Eg.
SELECT CUSTOMER.FirstName, CUSTOMER.LastName
FROM CUSTOMER, RENTAL
WHERE RENTAL.DepositPaid = No
AND RENTAL.CustomerID = CUSTOMER.CustomerID;

Eg. (Using Inner Join)
SELECT CUSTOMER.FirstName, CUSTOMER.LastName
FROM CUSTOMER
INNER JOIN RENTAL ON RENTAL.CustomerID = CUSTOMER.CustomerID
WHERE RENTAL.DepositPaid = 'No';