Database Design and Administration 2nd Lecture PDF

11/13/2023 DATABASE DESIGN AND ADMINISTRATION 2ND LECTURE LECTURER: SALMAN SAHIB MOHAMMAD GHARIB Data definition language (DDL) It is a language. It allows the user to define the data. Define the relationship of the data to other types of...

11/13/2023 DATABASE DESIGN AND ADMINISTRATION 2ND LECTURE LECTURER: SALMAN SAHIB MOHAMMAD GHARIB Data definition language (DDL) It is a language. It allows the user to define the data. Define the relationship of the data to other types of data. 1  11/13/2023 Data Definition Language DDL statements work with the structure of the database table. Various data types used in defining columns in a database table. Integrity and value constraints. Viewing, modifying and removing a table structure. Data types: When a table is created, each column in the table is assigned a data type. Some of the important data types are as follows − o Varchar2 o Char o Number When you create a table, you have to specify the following: Table name. Name of each column. Data type of each column. Size of each column. 2  11/13/2023 DDL Commands with example Let’s see each DDL command with an example. Create ○ It is used to create a new table or a new database. ○ An example of the create command is as follows − create table it.student( stdname varchar(20) ,branch varchar(20),college varchar(20), age int, telephone bigint, address varchar(20)); A student table is created with the fields given below − 3  11/13/2023 Alter It is used to alter or change the structure of the database table. An example of the alter command is as follows: ALTER TABLE it.student ADD birthdate DATETIME; Homework: How to remove column from your creating table? Drop It is used to delete a table, index, or views from the database. An example of the drop command is as follows − DROP TABLE it.student; 4  11/13/2023 DML Data Manipulation Language It refers to a language that is used to insert, delete and update data in a database. It is used for the manipulation of data in the database. Insert into command It is used to insert a data row in a table. Syntax INSERT INTO table_name(col1,col2,col3,..) VALUES (value1,value2,value3,...); or INSERT INTO table_name VALUES (value1,value2,value3,...); 5  11/13/2023 Insert into command Example insert into it.student(stdname,branch,college,age,telephone,address,birthdate) VALUES ('ARY','Scientific','Engineering',23,07501111111,'HALABJA','2002/11/11 11:30:55'), (‘ALI','Scientific','Engineering',21,07502222222,'KARKUK','2002/11/11 12:45:35'), ('ARIN','Scientific','Engineering',22,07503333333,'SULAIMANI','2002/11/11 09:45:00’); SELECT * FROM it.student; Update command It is used to update existing data in a table of a database. It can be used to update single or multiple columns at a time as per the requirement. UPDATE table_name SET values_to_update(1_col=1_value,2_col=2_value,....) WHERE condition; table_name is the name of the table 1_col,2_col... are the column 1_value,2_value...are the updated value for columns conditions are there to select the row on which the value will get updated. 6  11/13/2023 Update command Example UPDATE it.student SET stdname='monu',address='ERBIL’ WHERE stdname=‘ALI'; Delete command It is used to delete records from a table given. One can delete single or multiple records depending upon the condition provided in the WHERE clause. DELETE FROM table_name WHERE condition; table_name is the name of the table table_name is the name of the table 7  11/13/2023 Delete command Example DELETE FROM it.student WHERE stdname='monu'; 8