Semester 1 SQL Assignment: Data Manipulation and Control

Desklib provides solved assignments and past papers to help students succeed.

Assignment 2
1

Table of Contents
Task 1: Structure Query Language..................................................................................................3
Task 2: Data Manipulation..............................................................................................................5
Task 3: Database testing................................................................................................................11
References......................................................................................................................................12
2

Task 1: Structure Query Language
1. Explain the two Data Manipulation Language (DML) statements, Update and Delete,
with correct syntax and appropriate examples of each statement.
Update: this is the statement which is used to make the updates in the table that already
exists. It can be used to update the multiple fields in a single frame of time.
Syntax: UPDATE `table_name` SET `column_name` = `new_value` [WHERE
condition];
Example:
Employee table
Emp_no Emp_name DOB
1001 Albert 23-07-1967
UPDATE employee SET DOB = ’23-05-1966’ where emp_no=’1001’;
Emp_no Emp_name DOB
1001 Albert 23-05-1966
Delete: this statement is used to delete all the records from the table of the database. It is
beneficial for deleting the data that is temporary. Once deleted row cannot be recovered.
Syntax: DELETE FROM `table_name` [WHERE condition];
Example:
Employee table
Emp_no Emp_name DOB
1001 Albert 23-07-1967
DELETE FROM employee where emp_no=’1001’;
Emp_no Emp_name DOB
3

2. Explain the two Data Control Language (DCL) statements, Grant and Revoke, with
correct syntax and appropriate examples of each statement.
Grant: this is the statement used to grant the privileges to the user or the other person who
can use the database.
Syntax: GRANT privileges ON object TO user;
Revoke: this statement is used to take back the permissions from the person to whom the
privilege is given earlier.
Syntax: REVOKE privileges ON object FROM user;
4

Task 2: Data Manipulation
1. Write an SQL statement to list all employees with their full names, gender, and salary
5

2. Write an SQL statement to show the title of all employees and their department name.
6

7

3. Write an SQL statement to show the full names and gender who belong to
department number 'd004'.
8

4. Write an SQL statement to show all departments and their department’s managers.
5. Write an SQL statement to show a list of department’s managers who were hired after
1990.
6. Write an SQL statement to change an employee’s date of birth. Assume the
employee has just phoned in with his/her last name.
7. Write an SQL statement to increase salaries of all employees up to 5.5% who are
working from 1997.
8. Write an SQL statement to delete employee’s record who belongs to department
'd004' and ID is 10003.
9. Create a database view to list full names of all employees their departments and salaries.
10. Create a database view to list all departments and their department’s managers,
who were hired between 1990 and 2000.
9

10

Task 3: Database testing
1. What is Database testing and why database testing is important?
Database testing is considered as the major testing as it requires the testing by the
expertise of the table, procedures, and queries. It could be performed in desktop or web
application. It generally includes:
 Data integrity testing
 Database related performance
 Testing of functions, triggers, and procedures
 Data validity testing
In the testing of the database, the functionality is tested and each and every action that
is performed is checked. The actions which need to be checked include saving options,
addition, and deletion. These are some of the major actions that need regular
monitoring. It is somewhat a complex task and the tester required should be
experienced for performing testing. It is important as it ensures that the correct values
are being retrieved by an application.
2. Provide the screenshots of the testing process on Employees database for the
following constraints
a. field size validation
b. Null values
c. Check constraints.
d. referential integrity
11

References
Beginner-SQL-Tutorial, 2019. Learning SQL. [online]. Beginner-SQL-Tutorial. Available at:
https://beginner-sql-tutorial.com/sql-grant-revoke-privileges-roles.htm. [Accessed on 7th August
2019].
Guru99, 2019. Database (Data) testing tutorial with simple test cases. [Online]. Available at:
https://www.guru99.com/data-testing.html [Accessed on 7th August 2019].
12