SQL DDL: Database Modification and Advanced Features

Advanced Features

Advanced features facilitate consultation similar to a spreadsheet because they work based on rows and columns.

• COUNT(): Count the number of rows in the column set
• MIN(): Find the minimum value of the column set
• MAX(): Find the maximum value of the column set
• AVG(): Gets the average values in the column set
• SUM(): Gets the total value implied by the values obtained in the column provided

Modifying the Database

SQL DDL has modules for the definition of data that allows us to create or modify table structure.

The instructions for these operations are:

• CREATE TABLE: Allows you to create an empty data table.
• INSERT: Allows you to store records in a table created.
• UPDATE: You can modify data records stored in the table.
• DELETE: Deletes a record or group of records.
• CREATE INDEX: Creates an index that can assist us for consultation.
• DROP TABLE: You can delete a table.
• DROP INDEX: Deletes the specified index.

Structure of INSERT

INSERT INTO table_name VALUES (Set registry values);

Structure of the CREATE TABLE statement

CREATE TABLE table_name (Attribute 1: length datatype, Attribute 2: length datatype, Attribute 3: length datatype, ..., Attribute n: length datatype, PRIMARY KEY (Optional));

Structure of the CREATE INDEX

CREATE INDEX index_name ON table_name (Field(s) by which the index was created);

Example:

Create a table index by the field Alum Specialty.

CREATE INDEX Picture1 ON Student (Specialty);

This index contains all students sorted by field specialty.

CREATE UNIQUE INDEX INDEX Picture2 ON Student (Specialty);

Structure of the UPDATE

UPDATE table_name SET Values WHERE (condition);

Example:

Change the number of control register Table Daniel Colin pupil by the number 96,310,518.

UPDATE Student SET NControl = '96310518' WHERE nombree = 'Daniel Colin';

Structure of the DROP TABLE statement

DROP TABLE table_name;

Example:

Delete the Student table created earlier.

DROP TABLE Student;