Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu05:theorie:02 [2024/09/16 15:22] – vdemir
+++ modul:m290:learningunits:lu05:theorie:02 [2024/10/17 12:43] (aktuell) – vdemir
@@ Zeile 1: / Zeile 1: @@
-====== LU06b - Table Management ======
+====== LU07b - SQL-DDL: Table Management ======
 **Source**:
-  * [[https://www.w3schools.com/sql/sql_create_table.asp|CREATE TABLE]]
+  * [[https://www.w3schools.com/sql/sql_create_table.asp| W3Schools | CREATE TABLE]]
-  * [[https://www.w3schools.com/sql/sql_drop_table.asp|DROP TABLE]]
+  * [[https://www.w3schools.com/sql/sql_alter.asp| W3Schools | ALTER TABLE]]
-  * [[https://www.w3schools.com/sql/sql_alter.asp|ALTER TABLE]]
+  * [[https://www.w3schools.com/sql/sql_drop_table.asp| W3Schools | DROP TABLE]]
 ===== Learning Objectives =====
-  - ...
+In this learning unit, we will learn how to manage database tables, i.e.
+  - how to create
+  - how to modify and
+  - how to delete database tables
+===== CREATE TABLE =====
+Let's start by creating a table based on the ERD of the student management tool, as shown below:
+Our goal is to create the table "stundent" in our database. For that we need to know how the basic syntax for creating of table is.
+  CREATE TABLE table_name (
+      column1 datatype,
+      column2 datatype,
+      column3 datatype,
+     ....
+  );
+In our example the our concrete CREATE statement would look like:
+''
+  CREATE TABLE student (
+    stundent_id INT,
+    name VARCHAR(50),
+    surname VARCHAR(50),
+    birthdate DATE,
+    matrno CHAR(6)
+  );
+After executing the DDL create command, the result would look like the following illustration.
+{{:modul:m290:learningunits:lu05:theorie:m290_lu06b.abb2.png?600|}}
+**Hints:**
+  * On the right upper side you can see, that the table "student" has actually been created.
+  * After a select on this table, we can see that the table does not yet contain any data.
+  * It's not a big deal so far, is it? 8-).
+===== ALTER TABLE =====
+It may happen that we need to change something, e.g. the change the attribute name or add a new attribute. In this case, the ALTER TABLE commands will do the work.
+The basic syntax of the DDL "ALTER TABLE" syntax is:
+  ALTER TABLE table_name
+  ADD column_name datatype;
+==== ADD COLUMN ====
+In practical use, if we want to add an email to the student's data our SQL command would look like:
+  ALTER TABLE student
+  ADD Email varchar(255);
+==== DROP COLUMN ====
+If we can add a column, it should come as no surprise that we also can delete a column. The syntax for deleting an entire column from a table is:
+  ALTER TABLE table_name
+  DROP COLUMN column_name;
+The following DDL statement removes the currently added column email from the table student.
+  ALTER TABLE Customers
+  DROP COLUMN Email;
+==== RENAME COLUMN ====
+Naturally, we also can just rename a wrongly spelled column name. The syntax in that case is:
+  ALTER TABLE table_name
+  RENAME COLUMN old_name to new_name;
+Or more practical, if we want to change the name of the column  matrno in studentno, we need the following DDL command:
+  ALTER TABLE student
+  RENAME COLUMN matrno to studentno;
+==== MODIFY DATATYPE ====
+We also can change the datatype of colums. The basic syntax would be:
+  ALTER TABLE table_name
+  ALTER COLUMN column_name datatype;
+And of course, as a concrete practical example, in which we change the length of the column "name" from 50 characters to 100:
+  ALTER TABLE student
+  MODIFY COLUMN name VARCHAR(100);
+===== DROP TABLE =====
+If, for whatever reason, we have created a table incorrectly or want to get rid of an obsolete table, we need the command DROP TABLE:
+  DROP TABLE table_name;
-===== Overview =====
+**Hint: <color #ed1c24>Be careful with this command, as it also deletes the entire contents of this table.</color>**
-...
-  - ...
 ==== Vocabulary ====
 ^English ^ Deutsch ^
-| ::: | ... |
+| column | Spalte |
 ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir