COLLEGE OF COMPUTER STUDIES AND MULTIMEDIA ARTS
CCS0021L
(INFORMATION MANAGEMENT)
EXERCISE
7
DATA MANIPULATION LANGUAGE
Bautista, Ishmael Jehoshaphat J.
Student Name /
Group Name:
Name Role
Members (if Group):
CCS0021L – TN21
Section:
Ma’am Beau Habal
Professor:
I. PROGRAM OUTCOME/S (PO) ADDRESSED BY THE LABORATORY EXERCISE
a. Apply knowledge through the use of current techniques and tools necessary for the IT profession.
[PO: I]
II. COURSE LEARNING OUTCOME/S (CLO) ADDRESSED BY THE LABORATORY
EXERCISE
• Create SQL statements that retrieve information requirements of the organization needed for reports
generation. [CLO: 4]
III. INTENDED LEARNING OUTCOME/S (ILO) OF THE LABORATORY EXERCISE
At the end of this exercise, students must be able to:
• Use SQL command to manipulate the data in the table
• Use the commit, rollback and save point as transaction control
IV. BACKGROUND INFORMATION
• A DML statement is executed when you: Add new rows to a
table
Modify existing rows in a table
Remove existing rows from a table
• A transaction consists of a collection of DML statements that
form a logical unit of work.
INSERT
• Add new rows to a table by using the INSERT statement:
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
• With this syntax, only one row is inserted at a time.
• Adds one or more rows to a table
• Inserting into a table
CCS0021L-Information Management Page 2 of 14
• Inserting a record that has some null attributes requires identifying the fields that actually
get data
• Inserting from another table
UPDATE
• Modify existing values in a table with the UPDATE statement:
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
• Update more than one row at a time (if required).
SELECT
• Used for queries on single or multiple tables
• Clauses of the SELECT statement:
– SELECT
• List the columns (and expressions) to be returned from the query
– FROM
• Indicate the table(s) or view(s) from which data will be obtained
– WHERE
• Indicate the conditions under which a row will be included
in the result
– GROUP BY
• Indicate categorization of results
– HAVING
• Indicate the conditions under which a category (group) will
be included
– ORDER BY
• Sorts the result according to specified criteria Syntax:
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
SELECT STATEMENT WITH AGGREGATE FUNCTIONS
Useful aggregate functions:
AVG() - Returns the average value
COUNT() - Returns the number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
CCS0021L-Information Management Page 3 of 14
MIN() - Returns the smallest value
SUM() - Returns the sum
Syntax:
SELECT AVG(column_name) FROM table_name;
SELECT min(column_name) FROM table_name;
SELECT max(column_name) FROM table_name where [condition];
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
UCASE() - Converts a field to upper case
LCASE() - Converts a field to lower case
MID() - Extract characters from a text field
LEN() - Returns the length of a text field
ROUND() - Rounds a numeric field to the number of decimals specified
NOW() - Returns the current system date and time
FORMAT() - Formats how a field is to be displayed
SQL ORDER BY Syntax
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name
ASC|DESC; Example:
SELECT * FROM Customers
ORDER BY Country ASC;
SELECT * FROM Customers
ORDER BY Country;
The SQL BETWEEN Operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE Price NOT BETWEEN 10
AND 20;
CCS0021L-Information Management Page 4 of 14
BETWEEN Operator with IN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a
CategoryID of 1,2, or 3 should not be displayed:
Example
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
The SQL LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
Tip: The "%" sign is used to define wildcards (missing letters) both before and after the pattern. You will
learn more about wildcards in the next chapter.
The following SQL statement selects all customers with a City ending with the letter "s":
Example:
SELECT * FROM Customers
WHERE City LIKE '%s';
DELETE
Removes rows from a table
Delete certain rows
DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE = ‘HI’;
Delete all rows
DELETE FROM CUSTOMER_T;
TRUNCATE Statement
• Removes all rows from a table, leaving the table empty and the table structure intact.
• It is a data definition language (DDL) statement rather than a DML statement; cannot easily be
undone.
A database transaction consists of one of the following:
• DML Statements that constitute one consistent change to the data.
• One DDL statement.
• One data control language (DCL) statement.
SAVEPOINT
• Create a marker in the current transaction by using the SAVEPOINT statement.
CCS0021L-Information Management Page 5 of 14
ROLLBACK
• Discard all pending changes by using the ROLLBACK statement.
• Roll back to the marker by using the ROLLBACK TO SAVEPOINT statement.
COMMIT
• Save the changes made permanently in the database by using COMMIT.
CCS0021L-Information Management Page 6 of 14
V. GRADING SYSTEM / RUBRIC
Criteria Descriptions Points
Creation of Tables (1-10 All the tables in the ERD are 50
properly made and complete
Changes made on the The use of correct Data Definition 50
Tables Language to change the
characteristics of the table are
correct
Total 100%
CCS0021L-Information Management Page 7 of 14
VI. LABORATORY ACTIVITY
Instructions: Download the two files before starting this exercise, namely: hrcreate.txt and
hrpopulate.txt. Both files are found in Canvas. Copy the contents of hrcreate.txt first to the
worksheet in SQL developer and click run script. Then do the same with hrpopulate.txt file. One
you see the tables and contents inside the table, you can start answering the following:
Write the DML code the show the following. Then for each number, copy and paste the code
including the screenshot of the output.
Queries:
1. Show the employee id, first name, and last names of employees who are managed
by manager id 114.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM Employees
WHERE MANAGER_ID = '114';
2. Show the employee id and the salary of the employees who has a salary higher than
11,999.
SELECT EMPLOYEE_ID, SALARY
FROM Employees
WHERE SALARY > 11999;
3. Show the job title of the job id AC_MGR and IT_PROG.
SELECT JOB_TITLE FROM JOBS
WHERE JOB_ID = 'AC_MGR' OR JOB_ID = 'IT_PROG';
CCS0021L-Information Management Page 8 of 14
4. Show all the columns of the departments table that has no manager id.
SELECT *
FROM DEPARTMENTS WHERE MANAGER_ID IS NULL;
CCS0021L-Information Management Page 9 of 14
5. Show the country id and country names of countries that are either Italy, Singapore,
United States of America or countries found in region 1.
SELECT COUNTRY_ID, COUNTRY_NAME FROM COUNTRIES
WHERE COUNTRY_NAME = 'Italy' OR COUNTRY_NAME = 'Singapore' OR COUNTRY_NAME =
'United States of America' OR COUNTRY_NAME = 'ITALY' OR REGION_ID = '1';
6. Using the job history table, sow the employee id, start dates, and end dates of
employees has an id of 102, 101 or 176 or their end dates are Dec 31, 1998.
SELECT EMPLOYEE_ID, START_DATE, END_DATE FROM JOB_HISTORY WHERE EMPLOYEE_ID =
'102' OR EMPLOYEE_ID = '101' OR EMPLOYEE_ID = '176' OR END_DATE = '12/31/1998';
7. Show all the columns of the employees that has a job id of AD_PRES.
SELECT *
FROM Employees
WHERE JOB_ID = 'AD_PRES';
CCS0021L-Information Management Page 10 of 14
8. Show the job id and the number of employees that has a job id of SA_REP or
ST_CLERK. Group it by job id and arrange it by its job id.
SELECT JOB_ID, PHONE_NUMBER
FROM Employees
WHERE JOB_ID = 'SA_REP' OR JOB_ID = 'ST_CLERK'
GROUP BY JOB_ID
ORDER BY JOB_ID;
9. Show the job ids and average salaries per job id of the employees table, group it based on
job id.
Show only the job ids that has average greater than 10,000, and arrange it based
on job id.
SELECT JOB_ID,
CCS0021L-Information Management Page 11 of 14
AVG(SALARY)
FROM Employees
WHERE SALARY > 10000 GROUP
BY JOB_ID
ORDER BY JOB_ID ASC;
10. Show the salary, and the count of employees who has the same salary. Arrange it
by salary in ascending order. Rename the count of employees to
‘Number_of_Persons’.
SELECT SALARY,
COUNT(SALARY) AS NUMBER_OF_PERSONSCCS0021L-Information Management Page 12 of 14
FROM Employees
GROUP BY SALARY ORDER BY SALARY ASC;
CCS0021L-Information Management Page 12 of 14
Please do the following in sequential order:
11. Add a new job record in the jobs table. The new record has the following details:
Job id – MK_ASST
Job title – Marketing Assistant
Minimum salary 3000
Maximim salary is 13000
INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES('MK_ASSIST', 'Marketing Assistant', 3000, 13000);
12. Add a new employee using the new job id that was created in number 11. The
following details are below:
Employee id is 500
First name is Alicia
Last name is Santos
Phone number is 650.124.0000
Job id is MK_ASST
Salary is 10000
Department id is 20
INSERT INTO Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HI
RE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
VALUES(500, 'Alicia', 'Santos', 'ASantos', '650.124.0000', '05/11/2021', 'MK_ASSI
ST', 10000, 20);
13. Change the last name Delos Santos and salary to 12,500 of the employee with an
employee id of 500.
UPDATE Employees SET LAST_NAME = 'Delos Santos', SALARY = 12500
WHERE EMPLOYEE_ID = 500;
14. Create a save point name SPEmp500.
SAVEPOINT SPEmp500;
CCS0021L-Information Management Page 13 of 14
15. Delete the record of the employee id 500.
DELETE FROM Employees
WHERE EMPLOYEE_ID = 500;
16. Restore the deleted record (employee id 500) back to the employees table.
ROLLBACK;
17. Create a view that has all the details of the employees table.
CREATE VIEW EMP_COPY AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID,
SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM Employees;
VII. QUESTION AND ANSWER
1. What is the advantage of using a view?
- View can simplify query commands, help the security of data,
improve programming productivity, and contains the most
current base table data, uses only minimal storage space,
provides customized view for users.
VIII. REFERENCES
• Hoffer, J.A., Prescott, M.B., McFadden, F.R. (2007). Modern Database
Management 8th Edition. New Jersey: Pearson Prentice Hall.
CCS0021L-Information Management Page 14 of 14
