SQL is commonly divided into four main subdivisions, each serving a specific purpose in managing and interacting with relational databases:

1. Data Definition Language (DDL):

   • Purpose: Used for defining and modifying database schema (structures such as tables, indexes, and relationships).
   • Key Commands: CREATE, ALTER, DROP.
   • Extra in MySQL: Rename, Truncate.

2. Data Manipulation Language (DML):

   • Purpose: Handles the manipulation of data within schema objects.
   • Key Commands: INSERT, UPDATE, DELETE.
   • Extra in Oracle: Merge, Upsert

   

3. Data Control Language (DCL):

   • Purpose: Manages permissions and access controls to the database.
   • Key Commands: GRANT, REVOKE.

4. Data Query Language (DQL):

   • Purpose: Allows users to retrieve data from a database
   • Key Commands: SELECT

5. Transaction Control Language (TCL): note: 5th component DTL/TCL not an ANSI standard. It is extra in MySQL and Oracle RDBMS.

   • DTL (Data transaction language) or TCL(Transaction control language) Extra in MySQL and Oracle RDBMS. This is not an ANSI standard.
   • Purpose: Manages changes made by DML commands, ensuring data integrity during transactions.
   • Key Commands: COMMIT, ROLLBACK, SAVEPOINT.

These subdivisions collectively provide the framework for defining database structures, managing data, securing access, and controlling transactions within SQL-based systems.

Rules for table names:

• Maximum Length:

  • MySQL: 64 characters
  • Oracle: 30 characters (older versions), up to 128 bytes in newer versions
  • PostgreSQL: 63 characters

• Allowed Characters:

  You can use letters (A–Z, a–z), numbers (0–9), and the underscore (_).

• Tablename is case insensitive for MySQL under windows but case sensitive under Unix/Linux

• Tablename has to begin with an alphabet

• Special characters $, #, _ allowed

• In MySQL, to use reserved characters such as # in tablename and column name enclose it in backticks EMP#

• 134 Reserved words not allowed in tablename

• Uniqueness:

  Each table name must be unique within the same database.

• Naming Conventions:

  Although not strictly enforced by MySQL, it's common practice to start table names with a letter or underscore rather than a number, and to use lowercase letters for better consistency, especially in cross-platform development.

Data types