modul:m290:learningunits:lu05:theorie:02

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu05:theorie:02 [2024/09/25 14:25] – vdemirmodul:m290:learningunits:lu05:theorie:02 [2024/10/17 12:43] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU06b - SQL-DDL: Table Management ======+====== LU07b - SQL-DDL: Table Management ======
  
 **Source**:  **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_alter.asp|ALTER TABLE]] +  * [[https://www.w3schools.com/sql/sql_alter.asp| W3Schools | ALTER TABLE]] 
-  * [[https://www.w3schools.com/sql/sql_drop_table.asp|DROP TABLE]]+  * [[https://www.w3schools.com/sql/sql_drop_table.asp| W3Schools | DROP TABLE]]
    
 ===== Learning Objectives ===== ===== Learning Objectives =====
-In this learning unit, we will learn how to manage database tables, i.e. how to create, modify and delete database tables. +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: Let's start by creating a table based on the ERD of the student management tool, as shown below:
  
-{{:modul:m290:learningunits:lu05:theorie:lu06.a02.drawio.png?600|}} +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.
- +
-Our goal is to create the table "stundent" on our database. For that we need to know how the basic syntax for creating table is.+
  
   CREATE TABLE table_name (   CREATE TABLE table_name (
Zeile 23: Zeile 25:
  
 In our example the our concrete CREATE statement would look like: In our example the our concrete CREATE statement would look like:
 +''
   CREATE TABLE student (   CREATE TABLE student (
     stundent_id INT,     stundent_id INT,
Zeile 29: Zeile 31:
     surname VARCHAR(50),     surname VARCHAR(50),
     birthdate DATE,     birthdate DATE,
-    matrno: CHAR(6)+    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;
  
 +**Hint: <color #ed1c24>Be careful with this command, as it also deletes the entire contents of this table.</color>**
  
 ==== Vocabulary ==== ==== Vocabulary ====
 ^English ^ Deutsch ^ ^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 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir
  
  • modul/m290/learningunits/lu05/theorie/02.1727267118.txt.gz
  • Zuletzt geändert: 2024/09/25 14:25
  • von vdemir