- INSERT DATA INTO TABLE - DELETE Tasks/Assignments(s) Creating Database objects by using Data Definition Language (DDL) : 1. Using the Data Definition Language statement, create new database named University. 2. Create the tables Student, Instructor, Course and Enrolled with the following columns. Declare the corresponding primary and foreign keys : corresponding rows are deleted (or updated) from the referencing table if that row is deleted (or updated) from the parent table. 2. Instructor Table: 3. Course Table : 4. Enrolled Table : 3. In both Student and Instructor tables, add constraint that limits possible values of the Gender columnin F or M. 4. Enrolled Table: 3. In both Student and Instructor tables, add constraint that limits possible values of the Gender column in F or M. 4. Add new column Address ( varchar(20), null ) to Instructor table. 5. Modify the Credit hours column: the default value is 2 hours. 6. Modify the Course_Name column to have a unique value. 7. Delete column Address from Instructor table. 8. Insert values into tables: 1. Student Table: 3. Course Table: 2. Instructor Table: 3. Course Table: 4. Enrolled Table: Deliverables(s) Database file containing queries and output. DML(Data Manipulation Language) a. Inserting Data Tasks/Assignments(s) i. INSERT INTO table_riame VALUES (valuel,value2,...) ii. INSERT INTO table_name (column I,column 2, ) VALUES (valuel, value 2, ) Note: Table's relation should be considered to decide on the sequence of data insertion in the tables Question: which table we should start with ??!! (D) Example: Inserting new record for new Event as following: Event name: Trip to beach, Date: 2013-08-08, Purpose.gathering. Type.tun, Organizer number: 202 [Inserting Specific No. of columns] USE E_volunterSys; INSERT INTO Event (EName, "Date", purpose, "Type",Org_ID) values (Trip to beach',2013-08-08','gathering','fun',202) (1) Example: Reaistor_new.woluateeninformation_on.Rarticipate_IN relation. beach',"2013-08-08',"gathering', 'fun',202) [3 Example: Register new volunteer information on Participate_IN relation. [Inserting a full record] USE_E_volunteerSys: INSERT INTO Participate_IN VALUES (101,1,50,'Good') This section will have a list of tasks/assignments carried out by the students during the lab. b. Updating Data (i) Modifies cxisting data in a table or view (1) Syntax: (9. UPDATE table_name / view_name SET column_ I= expression, column 2= expression [WHERE condition] E. Example: Inerease the points of the participated volunteer No. 102 by 100 points and the performance to be excellent? USE E_volunteerSys; UPDATE Participate.IN SET points = points +100, Performance = 'excellent' WHERE V ID =102 [D Example: change the Interest of the Sponser Donator (301) to be 'fun and joy 4 ? USE E volunteerSys: UPDATE Sponsor_Donator SET Interest='fun and joy' WHERE SID =301 C. Deleting Data i. DELETE command : [6 deletes all table row by row B. While Deletion is done row by row along with recording every delete operation in the Transaction log to allow canceling the transaction by Rolling it back [ DELETE Syntax: DELETE [FROM] table_name WHERE condition [1. Example: Remove the volunteers'(ID-110) participation information where the event is 'Block supper'? USE E_volunteerSys; DELETE FROM Participate_IN WHERE V ID = 110 AND EName = 'Block supper' What will happen if didn't specify the records to be deleted (no WHERE clause)??!! Be Carefultt All records will be deleted II. TRUNCATE TABLE command : If deletes all table once at a time (6) While Deletion is done one time, so no trunsactions are recorded in the Transaction log which will not allow Rolling back the transaction TRUNCATE TABLE Syntax: TRUNCATE TABLE table_name back the transaction [ TRUNCATE TABLE Syntax: TRUNCATE TABLE table_name (8) Q: If I have a column in my table with the IDENTITY property, and after deleting all table rows what number will it generate after inserting a new row? 70 DELETE : will continue the numbering of the last row which has been deleted (7) TRUNCATE TABLE: Will start new numbering from the initial start value Exercise: Create a testing table with identily column, fil it with some records, then try the Delete and Trancate commands to see the difference? Tasks/Assignments(s) Creating Database objects: 1: Using the Data Definition Language statement, ereate new database named Sehool. CREATE DATABSE School 2: Create the tables Student and Teacher with the following columns. (Declare the corresponding primary and foreign keys). Tasks/Assignments(s) Creating Database objects: 1: Using the Data Definition Language statement, create new database naned Sehool. CREATE DATABSE School 2: Create the tables Student and Teacher with the following columns. (Declare the corresponding primary and foreign keys). (1) Each student taught by one teacher. [1] Each teacher taught one or more students. 3: Delete column Std_Birth from Student table. 4: Insert new four rows into the Tescher table with the following values: 5: Insert new six rows into the Student table with the following values: 6: Add new column Address (varchar(20), null) to Student table. 7: change for student No 101 the group No to G4. 8: change for student No 102 the teacher No to 1001 and the Student Name to Nasser. 9: create view containing a list of Names and IDs of all students who teacher Id is 1002 4- Assessment 1. Student will be asked to list some examples of DDL queries 2. Student will bearked to list some examples of DML queries