8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

# Data Definition Language (DDL)

| Data type  | Function                                                              |
| ---------- | --------------------------------------------------------------------- |
| INTEGER    | A positive or negative whole number                                   |
| REAL/FLOAT | Number with decimal places                                            |
| CHAR(n)    | Character of fixed length n                                           |
| VARCHAR(n) | Character of variable length n                                        |
| BOOLEAN    | Contains values such as True or False                                 |
| DATE       | Stores date, month and year values usually formatted as 2023-01-01    |
| TIME       | Stores hour, minutes and seconds values usually formatted as 01:01:01 |

- Creation/modification of the database structure using this language
    
    - written in SQL

### Creating a database:
```sql
CREATE DATABASE <database-name>;
```
#### Example
```sql
CREATE DATABASE School;
```
### Creating a table:
```sql
CREATE TABLE <table-name> (  
<column1-name> <datatype>,  
<column2-name> <datatype>,  
...  
);
```
#### Example
Create table student without primary key:
```sql
CREATE TABLE Student(  
  StudentID CHARACTER,  
  FirstName VARCHAR(10),  
  SecondName VARCHAR(10),  
  DateOfBirth DATE,  
  ClassID Character  
);
```

Create table student with primary key:
```sql
CREATE TABLE Student(  
  StudentID CHARACTER NOT NULL,  
  FirstName VARCHAR(10),  
  SecondName VARCHAR(10),  
  DateOfBirth DATE,  
  ClassID CHARACTER,  
  PRIMARY KEY (StudentID)  
);
```
### Adding a primary key:
```sql
ALTER TABLE <table-name> ADD PRIMARY KEY (field)
```
#### Example:
```sql
ALTER TABLE Student ADD PRIMARY KEY (StudentID)
```
### Adding a foreign key:
```sql
ALTER TABLE <table-name> ADD FOREIGN KEY <field> REFERENCES <table>(field)
```
#### Example
To join ClassID in the Class table as a foreign key to ClassID in the Student table:
```sql
ALTER TABLE Student ADD FOREIGN KEY ClassID REFERENCES Class(ClassID)
```
### Adding a new attribute/column/field :
```sql
ALTER TABLE <table-name> ADD <field-name> <data-type>
```
#### Example
Add attribute Gender(either M for Male of F for Female) to the Student table:
```sql
ALTER TABLE Student ADD Gender CHAR(1)
```
### Deleting a attribute/column/field:
```sql
ALTER TABLE <table-name> DROP COLUMN <field-name>
```
#### Example
To delete the column named ‘DateOfJoining’ from the table Employeedetails:
```sql
ALTER TABLE Employeedetails DROP COLUMN DateOfJoining
```
### Modifying tables
```sql
ALTER TABLE <table-name> MODIFY COLUMN <field-name> <datatype>
```
#### Example
To modify the column named ‘EmpID’ in the table Employeedetails:
```sql
ALTER TABLE Employeedetails MODIFY COLUMN EmpID VARCHAR(6) NOT NULL PRIMARY KEY
```
## Example
```sql
CREATE DATABASE ‘Personnel.gdb’  
CREATE TABLE Training  
(EmpID INT NOT NULL,  
CourseTitle VARCHAR(30) NOT NULL,  
CourseDate Date NOT NULL,  
PRIMARY KEY (EmpID),  
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID))
```
# Data Manipulation Language (DML)

- Query and maintenance of data done using this language – written in SQL

| SQL (DML) query command | Description                                                                           |
| ----------------------- | ------------------------------------------------------------------------------------- |
| SELECT FROM             | Fetches data from a database. Queries always begin with SELECT.                       |
| WHERE                   | Includes only rows in a query that match a given condition                            |
| ORDER BY                | Sorts the results from a query by a given column either alphabetically or numerically |
| GROUP BY                | Arranges data into groups                                                             |
| INNER JOIN              | Combines rows from different tables if the join condition is true                     |
| SUM                     | Returns the sum of all the values in the column                                       |
| COUNT                   | Counts the number of rows where the column is not NUL                                 |
| AVG                     | Returns the average value for a column with a numeric data type                       |

## SQL Operators:

|=|Equals to|
|---|---|
|>|Greater than|
|<|Less than|
|>=|Greater than or equal to|
|<=|Less than or equal to|
|<>|Not equal to|
|IS NULL|Check for null values|

## Queries:
### Creating a query:
```sql
SELECT <field-name>  
FROM <table-name>  
WHERE <search-condition>
```
### A * symbol is used for field_names if all columns are to be displayed:
```sql
SELECT *
FROM <table-name>
WHERE <search-condition>
```
#### Example
**LOCATION**(City, Country)

| City         | Country   |
| ------------ | --------- |
| Kuala Lumpur | Malaysia  |
| London       | UK        |
| Perth        | Australia |

Display all attributes for the country Australia
```sql
SELECT *  
FROM LOCATION  
WHERE Country = 'Australia';
```
Will display:

|City|Country|
|---|---|
|Perth|Australia|

Display city name for all cities in UK
```sql
SELECT City  
FROM LOCATION  
WHERE Country = 'UK';
```
Will display:

|City|
|---|
|London|

### COUNT, GROUP BY

Example Display the total number of cities for each country in the table.
```sql
SELECT COUNT(City), Country,  
FROM LOCATION  
GROUP BY Country;
```
Will display:

|Count(City)|Country|
|---|---|
|1|Pakistan|
|2|UK|
|1|Malaysia|

### SUM
Example Calculate the total of all students ids from student table
```sql
SELECT SUM(StudentID)  
FROM STUDENT;
```
Will display:

| SUM(StudentID) |
| -------------- |
| 18             |

### AVG
#### Example:
**COURSE**(CourseName, Duration)

| CourseName  | Duration |
| ----------- | -------- |
| Database101 | 2        |
| Python101   | 4        |

Calculate and show the average duration of courses
```sql
SELECT AVG(Duration)  
FROM COURSE;
```
Will display:

|AVG(Duration)|
|---|
|3|

### Sort into ascending order:
```sql
ORDER BY <field-name>
```
### Sort into decending order:
```sql
ORDER BY <field-name> Desc
```
#### Example:

|StudentID|StudentName|Major|DateOfBirth|
|---|---|---|---|
|S291|David Watson|Science|21/03/1998|
|S157|Helen Williams|Economics|30/04/1999|
|S918|Paul Ingram|Architecture|14/05/1998|
|S241|Rosemarie Collin|Science|19/07/1999|
|S511|John Rayner|Business|12/11/1998|
|S256|Alan Cross|Economics|12/08/1999|
|S791|Samuel Smith|Architecture|19/10/1999|
```sql
SELECT * FROM Student WHERE DateOfBirth >= '01/01/1999' ORDER BY Major, StudentNume Desc
```
Will display:

| StudentID | StudentName                              | Major                                  | DateOfBirth |
| --------- | ---------------------------------------- | -------------------------------------- | ----------- |
| S791      | Samuel Smith                             | <font color= green>Architecture</font> | 19/10/1999  |
| S157      | <font color=orange>Helen Williams</font> | <font color= green>Economics</font>    | 30/04/1999  |
| S256      | <font color=orange>Alan Cross</font>     | <font color= green>Economics</font>    | 12/08/1999  |
| S241      | Rosemarie Collin                         | <font color= green>Science</font>      | 19/07/1999  |

<font color= green>Green</font>: ascending order
<font color=orange>Orange</font>: descending order
## Joining together fields of different tables:
```sql
INNER JOIN
```
**STUDENT**(StudentID, StudentName, City)

| StudentID | StudentName | City      |
| --------- | ----------- | --------- |
| 5         | Ahmed       | Doha      |
| 6         | Sageev      | London    |
| 7         | Ali         | Islamabad |

**STUDENT_RECORD**(StudentID, CourseName, CourseDate)

| StudentID | CourseName  | CourseDate |
| --------- | ----------- | ---------- |
| 6         | Database101 | 2022-02-02 |
| 6         | Python101   | 2022-08-08 |
| 7         | Database101 | 2022-01-01 |
| 7         | Python101   | 2022-09-09 |

Please display student name and course date that takes Database101 on 2022-01-01.
```sql
SELECT STUDENT.StudentName, STUDENT_RECORD.CourseDate  
FROM STUDENT, STUDENT_RECORD  
WHERE STUDENT_RECORD.CourseName = 'Database101' AND STUDENT_RECORD.CourseDate = '2022/01/01' AND STUDENT.StudentID = STUDENT_RECORD.StudentID
```
Use INNER JOIN statement:
```sql
SELECT STUDENT.StudentName, STUDENT_RECORD.CourseDate  
FROM STUDENT  
INNER JOIN STUDENT_RECORD  
ON STUDENT.StudentID = STUDENT_RECORD.StudentID  
WHERE STUDENT_RECORD.CourseName = 'Database101' AND STUDENT_RECORD.CourseDate = '2022/01/01'
```

Will display:

|StudentName|CourseDate|
|---|---|
|Ali|2022-01-01|

## Data Maintenance:

### Adding data to table:
```sql
INSERT INTO <table-name>(field1, field2, field3)  
VALUES (value1, value2, value3)
```
### Deleting a record:
```sql
DELETE FROM <table-name>  
WHERE <condition>
```
### Updating a field in a table:
```sql
UPDATE <table-name>  
SET <field-name> = <value>  
WHERE <condition>
```