Data Definition Language (DDL) And Data Manipulation Language (DML) (Copy)

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

Key Concepts

• Data Definition Language (DDL):
  • Used to define, modify, and remove database structures.
  • Deals with creating and altering tables, indexes, schemas, and constraints.
  • Commonly written as SQL scripts.
• Data Manipulation Language (DML):
  • Used to add, update, delete, and retrieve data in a database.
  • Works with the actual data inside tables.
  • Includes commands like SELECT, INSERT, UPDATE, and DELETE.
• SQL (Structured Query Language):
  • A widely used standard for both DDL and DML.
  • Originally developed in the 1970s and has become the industry standard.

8.3.1 Industry-Standard Methods for Building and Modifying Databases

• DBMS (Database Management System) uses:
  • DDL to define and structure relational databases.
  • DML to manipulate the data stored within them.
• Differences Between DDL and DML:
  • DDL affects the structure, such as creating or deleting tables.
  • DML affects the contents, such as adding or modifying records.

8.3.2 SQL (DDL) Commands and Scripts

• SQL DDL commands allow database structuring.
• Important DDL commands include:
  

  Command	Description
  CREATE DATABASE	Creates a new database.
  CREATE TABLE	Defines a new table structure.
  ALTER TABLE	Modifies an existing table’s structure.
  PRIMARY KEY	Adds a unique primary key to a table.
  FOREIGN KEY ... REFERENCES ...	Adds a foreign key constraint to link tables.

• Data Types Used in SQL DDL:
  • CHARACTER(n): Fixed-length text.
  • VARCHAR(n): Variable-length text.
  • BOOLEAN: Stores True or False (often represented as 1 or 0).
  • INTEGER: Stores whole numbers.
  • REAL: Stores decimal numbers.
  • DATE: Stores dates in YYYY-MM-DD format.
  • TIME: Stores time in HH:MM:SS format.

Example SQL DDL Commands

• Creating a Database:

  CREATE DATABASE School;
  

• Creating a Table:

  CREATE TABLE Student (
      StudentID CHARACTER,
      FirstName CHARACTER,
      SecondName CHARACTER,
      DateOfBirth DATE,
      ClassID CHARACTER
  );
  

• Adding a Primary Key:

  ALTER TABLE Student ADD PRIMARY KEY (StudentID);
  

• Creating Another Table with Foreign Key:

  CREATE TABLE Class (
      ClassID CHARACTER,
      Location CHARACTER,
      LicenceNumber CHARACTER
  );
  
  ALTER TABLE Class ADD PRIMARY KEY (ClassID);
  
  ALTER TABLE Student ADD FOREIGN KEY (ClassID) REFERENCES Class(ClassID);
  

8.3.3 SQL (DML) Commands and Scripts

• SQL DML commands handle data manipulation.
• Essential DML query commands:
  

  Command	Description
  SELECT FROM	Retrieves data from a database.
  WHERE	Filters rows based on conditions.
  ORDER BY	Sorts query results.
  GROUP BY	Groups results based on attributes.
  INNER JOIN	Combines rows from multiple tables based on a condition.
  SUM	Computes the sum of values in a column.
  COUNT	Counts the number of rows.
  AVG	Calculates the average value of a numeric column.

• SQL DML Maintenance Commands:
  

  Command	Description
  INSERT INTO	Adds new records to a table.
  DELETE FROM	Removes records from a table.
  UPDATE	Modifies existing records.

Example SQL DML Commands

• Fetching Data:

  SELECT FirstName, SecondName
  FROM Student
  WHERE ClassID = '7A'
  ORDER BY SecondName;
  

• Joining Tables:

  SELECT Teacher.TeacherName, Subject.SubjectName
  FROM Teacher 
  INNER JOIN Subject 
  ON Teacher.LicenceNumber = Subject.LicenceNumber;
  

• Inserting Data:

  INSERT INTO Student VALUES ('S1301', 'Peter', 'Probert', '2011-06-06', '7A');
  

• Updating Data:

  UPDATE Student 
  SET FirstName = 'John' 
  WHERE StudentID = 'S1301';
  

• Deleting Data:

  DELETE FROM Student WHERE StudentID = 'S1301';
  

• Calculating Sum of Exam Marks:

  SELECT SUM(ExamMark) FROM StudentSubject;
  

Practical Applications of DDL and DML

• DDL Applications:
  • Used in designing and structuring databases before data is entered.
  • Example: Creating a school database with tables for students, teachers, and classes.
• DML Applications:
  • Used to retrieve and modify records once the database is operational.
  • Example: Querying student lists, updating grades, and deleting records.

Key Takeaways

• DDL is used for defining the database structure, while DML is used for handling data within that structure.
• SQL is the most common language for both DDL and DML operations.
• Understanding SQL commands is essential for effective database management.
• SQL scripts allow repetitive database tasks to be automated.