5. Data Definition Language (DDL).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2278d1fe-e568-4bca-8d15-7928d2192248",
   "metadata": {},
   "source": [
    "# 5. Data Definition Language (DDL)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4d22946-476f-4f9e-8e83-bf7e3a32379f",
   "metadata": {},
   "source": [
    "Data definition language (DDL) refers to SQL commands that design the **database structure**. Database engineers use DDL to create and modify database objects based on the business requirements. For example, the database engineer uses the CREATE command to create database objects such as tables, views, and indexes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f1231b1-756a-4486-b372-4b8408f7385e",
   "metadata": {},
   "source": [
    "## CREATE statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a9d0327-aca3-46a4-a256-929efcd3eef0",
   "metadata": {},
   "source": [
    "The `CREATE` statement is used to create new database objects. The most common use is for creating tables."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d146baba-47d3-405c-9423-cdaf4822cafc",
   "metadata": {},
   "source": [
    "**CREATE schema/database**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "687adccc-e182-49b3-8123-0f869e293c5c",
   "metadata": {},
   "source": [
    "```sql\n",
    "CREATE SCHEMA LibraryNearMe;\n",
    "-- creating the database\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dce40e0-9ba4-418b-8258-490dfa6a7731",
   "metadata": {},
   "source": [
    "**CREATE table**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b10de081-6525-45bf-b49e-ffea548c6832",
   "metadata": {},
   "source": [
    "```sql\n",
    "CREATE TABLE authors (\n",
    "\tauthor_id INT PRIMARY KEY AUTO_INCREMENTE,\n",
    "    author_name VARCHAR(255) NOT NULL,\n",
    "    birth_date DATE,\n",
    "    nationality VARCHAR(255)\n",
    "    ); -- creating authors table\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c52d00a2-6d99-431b-86b9-1f28276e7c24",
   "metadata": {},
   "source": [
    "Creating a copy of an existing table can also be done by using `CREATE TABLE`:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9c43da8-f382-4503-a07f-46640f822625",
   "metadata": {},
   "source": [
    "```sql\n",
    "CREATE TABLE bookshopnearme.authors AS SELECT * FROM librarynearme.authors;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f98756c8-e09d-4863-8d21-b67beea71068",
   "metadata": {},
   "source": [
    "With this command I am able to copy all the structure of table Authors from database LibraryNearMe to the database BookshopNearMe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e61b14d-7bb7-4e3f-9c21-eeb14c1eb835",
   "metadata": {},
   "source": [
    "**CREATE view**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d949e288-8745-47ad-981d-52f573fd2889",
   "metadata": {},
   "source": [
    "```SQL\n",
    "CREATE VIEW bookNames AS -- creating a view\n",
    "SELECT book_id, title FROM books;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ff68e2d-f2f2-4f14-8a53-ac3d260f8546",
   "metadata": {},
   "source": [
    "**CREATE event**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7539b8f-d621-4b50-a7e6-085155befc17",
   "metadata": {},
   "source": [
    "Events are created to automate scheduled tasks or operations within the database.  \n",
    "Events in MySQL are similar to scheduled tasks in other environments and are useful for running periodic maintenance, generating reports, or performing other automated actions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fc587f1-2e20-4d65-bcf1-77b6cc444a2a",
   "metadata": {},
   "source": [
    "```sql\n",
    "CREATE EVENT backup_event -- creating a backup with an event\n",
    "ON SCHEDULE EVERY 1 DAY\n",
    "STARTS CURRENT_TIMESTAMP\n",
    "DO\n",
    "BEGIN\n",
    "    SET @backup_path = 'C:\\Users\\Ance\\backups';\n",
    "    SET @current_timestamp = DATE_FORMAT(NOW(), '%Y%m%d%H%i%s');\n",
    "    SET @backup_file = CONCAT(@backup_path, 'backup_', @current_timestamp, '.sql');\n",
    "    SET @mysql_dump_command = CONCAT('mysqldump -u root -p123456789 bookshopnearme > \"',@backup_file'\"');\n",
    "    PREPARE stmt FROM @mysql_dump_command;\n",
    "    EXECUTE stmt;\n",
    "    DEALLOCATE PREPARE stmt;\n",
    "END;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d3a970f-544b-4fa2-a9ce-ef01ab91f963",
   "metadata": {},
   "source": [
    "## ALTER statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f30dba39-bd13-4396-9cf9-c33b71bcbd25",
   "metadata": {},
   "source": [
    "The `ALTER` statement is used to modify existing database objects. It can be used to add, modify, or delete columns, constraints, or other object properties."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeea4a18-c220-426b-b076-7b69d21aad06",
   "metadata": {},
   "source": [
    "**ALTER TABLE\n",
    "    ADD column**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ca5ab31-91d4-4890-a6db-fb8f64cc221f",
   "metadata": {},
   "source": [
    "```sql\n",
    "ALTER TABLE loyaltypoints  -- adding a new column to te table to track the date when the last loyalty points were earned\n",
    "ADD COLUMN transaction_date DATE;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edba7dc5-a56c-4d11-9b9f-35b836b87a0f",
   "metadata": {},
   "source": [
    "**ALTER TABLE DROP column**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59efe2bb-8fe4-4b95-a7e5-337bdc142df3",
   "metadata": {},
   "source": [
    "```sql\n",
    "ALTER TABLE loyaltypoints -- because of business requirement changes, this table had to be deleted\n",
    "DROP COLUMN points_spent; \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff3c09f8-57f7-4577-8fde-816b16096ab6",
   "metadata": {},
   "source": [
    "**ALTER TABLE ADD contstraint**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98042df6-855d-4bba-a775-6b55781db864",
   "metadata": {},
   "source": [
    "```sql\n",
    "ALTER TABLE authorsbooks  -- normalization of tables because of the previous migration\n",
    "ADD PRIMARY KEY (authors_id, book_id);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfe41a7f-bc49-4cdd-9073-534a2b6c3356",
   "metadata": {},
   "source": [
    "```SQL\n",
    "ALTER TABLE authorsbooks -- adding foreign key to an existing table\n",
    "ADD FOREIGN KEY (authors_id) REFERENCES authors(authors_id);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ead54c1c-47be-4a96-be77-0570ea17209d",
   "metadata": {},
   "source": [
    "```sql\n",
    "ALTER TABLE reviews -- adding a constraint to control if the values in the rating column are in the correct range\n",
    "ADD CONSTRAINT checkrating CHECK (rating>=1 AND rating <=10);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d09a1edf-6326-4e7a-800c-b31dd5740f95",
   "metadata": {},
   "source": [
    "**ALTER TABLE ALTER column**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c650593-c2b9-45a8-a377-32fb05a717c8",
   "metadata": {},
   "source": [
    "```sql\n",
    "ALTER TABLE reviews -- changing the column`s review_date type to year\n",
    "ALTER COLUMN review_date YEAR;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8baea20c-0b62-44fb-aab8-b4c7f07865b4",
   "metadata": {},
   "source": [
    "**ALTER TABLE RENAME column**\n",
    "```sql\n",
    "ALTER TABLE loyaltypoints -- renaming a column and asigning data type\n",
    "CHANGE loyality_id  loyalty_id INT;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2044b9b9-a34d-4831-b331-ce6ebdf8a4ca",
   "metadata": {},
   "source": [
    "**ALTER VIEW**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69e8ac35-96d5-45dd-a141-6576a53dced9",
   "metadata": {},
   "source": [
    "```SQL\n",
    "ALTER VIEW bookNames AS -- adding a new column to the view\n",
    "SELECT book_id, title, price FROM books;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c60a7b3a-b02c-4474-9fe0-781ddccdaa34",
   "metadata": {},
   "source": [
    "## DROP statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49b0778e-028b-4a24-90fe-49fb3cfb9752",
   "metadata": {},
   "source": [
    "The `DROP` statement is used to delete database objects or database itself. It is typically used to remove tables, indexes, or views. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2adfbb7a-6b08-4061-b881-951737f8b046",
   "metadata": {},
   "source": [
    "```sql\n",
    "DROP SCHEMA librarynearme; -- deleting previous database schema\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eee4bf1f-c6d4-4a37-a276-01962324ea63",
   "metadata": {},
   "source": [
    "```SQL\n",
    "DROP TABLE shippers; -- deleting a table that won`t be neccessary anymore"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a89dd2f8-f558-4ebf-8c5c-cddd4290998a",
   "metadata": {},
   "source": [
    "## TRUNCATE statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6948a0d-31e0-43c2-b3a1-57b30a3b88f6",
   "metadata": {},
   "source": [
    "Although `TRUNCATE` is similar to DELETE , it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways: Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables. Also TRUNCATE requires the DROP privilege."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ef75a32-918a-481d-ad10-3bf5698ea2ca",
   "metadata": {},
   "source": [
    "```sql\n",
    "TRUNCATE TABLE customers; -- this removes all rows from the Customers table\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c5b4981-dacc-4e9c-9840-a1d349d1215e",
   "metadata": {},
   "source": [
    "## RENAME statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4bb90ac-f86c-42cb-963f-e22f28ff8083",
   "metadata": {},
   "source": [
    "`RENAME` is used to change the name of an existing database object."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cb181e4-226b-46fb-8b72-323bceaec0bb",
   "metadata": {},
   "source": [
    "```sql\n",
    "RENAME TABLE author TO authors;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}