SQL

SQL can be split into different language types:

• Data Definition Language - DDL
• Data Manipulation Lanhuage - DML

Here is a breakdown in the diffferences between the two:

	DDL	DML
purpose	Defines or changes the structure of a database	Retrieves or changes the data stored in the structures
Common SQL statements	CREATE / ALTER / DROP	SELECT / INSERT / UPDATE / DELETE

DDL I

We can use DDL to CREATE a new table in a database:

CREATE TABLE Customers( custID int AUTO_INCREMENT PRIMARY KEY, custName VARCHAR(255) ); CREATE TABLE Accounts( accNum VARCHAR(8) PRIMARY KEY, custID int, balance int ); CREATE TABLE Transactions( tid int AUTO_INCREMENT PRIMARY KEY, fromACCNum VARCHAR(8), toACCNum VARCHAR(8), amount int, tDate Date );

DDL II

During the design of the database, we can use DROP to delete a table or column, or ALTER to change the structure of a table eg:

DROP TABLE Customers; ALTER TABLE Accounts ADD accType VARCHAR(12); ALTER TABLE Transactions DROP COLUMN tDate;

DML I

Now that we have created some data structures (tables) in the database, we can manipulate the data using DML

INSERT INTO Customers VALUES(CS1-001); UPDATE Accounts SET balance = 1000 WHERE custID = CS1-01; DELETE FROM Transactions WHERE fromACC = "CS34-03" AND toACC = "CS1-01"

Database Administrator Task

You are a database administrator.

Open your database software eg XAMPP and create a new database called SchoolBase

Now continue to the challenges.

Database Administrator Task 1 - CREATE the tables

Using Data Definition Language, create the following 2 database tables:

Database Administrator Task 2 - INSERT values

Run the following SQL statements:

School Admin Request

School admin is contacting you, the database administrator, to handle the following requests.

Use your skills and knowledge of DDL and DML to handle the requests.