UNIVERSITY OF BAHRI
COLLAGE OF COMPUTER SCIENCES & MATHEMATICS
2ND YEAR - BATCH 2020
Database Fundamentals
College Requirements -Compulsive Courses
CSIT2204
Iman siddig Adam
imanuofb@[Link]
DATA MANIPULATION
LANGUAGE
Insert, update and delete data
Database Lab (1) 3
Database Lab (1) 4
COMPANY DATABASE
Database Lab (1) 5
EMPLOYEE
CREATE TABLE Employee
(
Fname VARCHAR(15) NOT NULL,
Minit CHAR(1),
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9),
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY (Ssn)
);
Database Lab (1) 6
DEPARTMENT
CREATE TABLE DEPARTMENT
(
Dname VARCHAR(15) NOT NULL,
Dnumber INT PRIMARY KEY ,
Mgr_ssn CHAR(9) ,
Mgr_start_date DATE,
UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
);
Database Lab (1) 7
Alter EMPLOYEE Tbale
ALTER TABLE EMPLOYEE ADD ( FOREIGN KEY
(Super_ssn) REFERENCES EMPLOYEE(Ssn)
);
ALTER TABLE EMPLOYEE ADD (OREIGN KEY (Dno)
REFERENCES DEPARTMENT(Dnumber)
)
Database Lab (1) 8
DEPT_LOCATIONS
CREATE TABLE DEPT_LOCATIONS
(
Dnumber INT,
Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES
DEPARTMENT(Dnumber)
);
Database Lab (1) 9
PROJECT
CREATE TABLE PROJECT
(
Pname VARCHAR(15) NOT NULL,
Pnumber INT ,
Plocation VARCHAR(15),
Dnum INT NOT NULL,
PRIMARY KEY (Pnumber),
UNIQUE (Pname),
FOREIGN KEY (Dnum) REFERENCES
DEPARTMENT(Dnumber)
);
Database Lab (1) 10
WORKS_ON
CREATE TABLE WORKS_ON
(
Essn CHAR(9) NOT NULL,
Pno INT NOT NULL,
Hours DECIMAL(3,1) NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES
PROJECT(Pnumber)
);
Database Lab (1) 11
DEPENDENT
CREATE TABLE DEPENDENT
(
Essn CHAR(9) NOT NULL,
Dependent_name VARCHAR(15) NOT NULL,
Sex CHAR,
Bdate DATE,
Relationship VARCHAR(8),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES
EMPLOYEE(Ssn)
);
Database Lab (1) 12
DATA MANIPULATION
LANGUAGE
DML
Database Lab (1) 13
Data Manipulation Language
• DML commands
• Are used to modify the database.
• It is responsible for all form of CHANGES in the
database.
• The command of DML is not auto-committed.
• They can be rollback.
• Here are some commands that come under DML:
• INSERT
• UPDATE
• DELETE
The INSERT Statement Syntax
• Syntax
INSERT INTO tablename
[(column [, column...])] VALUES
(value [, value...]);
• Add new rows to a table.
• Only one row is inserted at a time with this
syntax.
Insert Statement -Example
• Insert a new row containing values for each
column.
• List values in the default order of the columns in
the table.
• Optionally, list the columns in the INSERT
clause.
INSERT INTO PROJECT VALUES ('ProductX', 1, 'Bellaire', 5);
• 1 row created.
Enclose character and date values within single
quotation marks.
16
Insert Statement -Example
• Inserting a record with specified fields
• INSERT INTO DEPARTMENT (DNAME,DNUMBE)
VALUES ('Research‘ ,5);
• INSERT INTO DEPARTMENT (DNAME,DNUMBE,
Mgr_start_date) VALUES ('Research‘ ,5,NULL);
• The SYSDATE function records the current date
and time.
• INSERT INTO DEPARTMENT VALUES ('Administration',
4,SYSDATE);
• INSERT INTO DEPARTMENT VALUES ('Headquarters', 1,
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'));
The UPDATE Statement Syntax
• Modify existing rows with the UPDATE
statement.
UPDATE tableName
SET column = value
[, column = value, ...]
[WHERE condition];
• Update more than one row at a time, if
required.
Updating Rows in a Table
• Specific row or rows are modified if you specify the
WHERE clause.
UPDATE employee
SET dno = 5
WHERE sex = ‘f’;
• All rows in the table are modified if you omit the
WHERE clause.
UPDATE employee
SET dno = 1;
Updating Rows: Integrity Constraint Error
UPDATE employees
SET Dno = 55
WHERE Dno = 5;
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found
Department number 55 does not exist
The DELETE Statement
You can remove existing rows from a table by
using the DELETE statement.
DELETE FROM tableName
[WHERE condition];
Deleting Rows from a Table
• Specific rows are deleted if you specify the
WHERE clause.
DELETE FROM department
WHERE department_name = 'Research';
• All rows in the table are deleted if you omit
the WHERE clause.
DELETE FROM copy_emp;
Deleting Rows: Integrity Constraint Error
DELETE FROM department
WHERE DNUMBER = 5;
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint
(HR.EMP_DEPT_FK) violated - child record
found
You cannot delete a row that contains a primary key
that is used as a foreign key in another table.
Using Explicit Default Values
• DEFAULT with INSERT:
INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
• DEFAULT with UPDATE:
UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;
Database Lab (1) 24
TRANSACTION CONTROL
LANGUAGE
COMMIT, ROLLBACK, SAVEPOINT
Database Lab (1) 25
Transaction Control Language
• TCL commands can only use with DML
commands like INSERT, DELETE and UPDATE
only.
• These operations are automatically committed in
the database that's why they cannot be used
while creating tables or dropping them.
• Here are some commands that come under TCL:
• COMMIT
• ROLLBACK
• SAVEPOINT
Database Lab (1) 26
COMMIT Statements
• Commit:
• Commit command is used to save all the
transactions to the database.
• Syntex:
• COMMIT;
Committing Data
• Make the changes.
DELETE FROM employee
INSERT INTO department VALUES (‘HR’, 2, ‘19-JUN-81’);
• Commit the changes.
COMMIT;
Database Lab (1) 28
Rollback
• Rollback command is used to undo transactions
that have not already been saved to the database.
• Syntex:
• ROLLBACK;
• Example:
• DELETE FROM employee WHERE sex= ‘M’;
• ROLLBACK;
Database Lab (1) 29
SAVEPOINT
• SAVEPOINT It is used to roll the transaction back
to a certain point without rolling back the entire
transaction.
• Syntex:
• SAVEPOINT SAVEPOINT_NAME;
State of the Data After ROLLBACK
Discard all pending changes by using the ROLLBACK
statement:
• Data changes are undone.
• Previous state of the data is restored.
• Locks on the affected rows are released.
DELETE FROM copy_emp;
ROLLBACK;
Database Lab (1) 31
Controlling Transactions
Rolling Back Changes to a Marker
• Create a marker in a current transaction by using
the SAVEPOINT statement.
• Roll back to that marker by using the ROLLBACK
TO SAVEPOINT statement.
UPDATE...
SAVEPOINT update_done;
INSERT...
ROLLBACK TO update_done;
Summary
In this lesson, you should have learned how to use DML
statements and control transactions.
Statement Description
INSERT Adds a new row to the table
UPDATE Modifies existing rows in the table
DELETE Removes existing rows from the table
COMMIT Makes all pending changes permanent
SAVEPOINT Is used to rollback to the savepoint marker
ROLLBACK Discards all pending data changes
DATA QUERY
LANGUAGE (SELECT)
Next Lab (Lab-2)