Data Modelling & SQL Language Report - Database Development, 2018/19

This report, submitted as part of a Data Modelling & SQL Language module, addresses key aspects of database development. The report begins with an exploration of Structured Query Language (SQL), including the UPDATE and DELETE statements, along with the GRANT and REVOKE commands, providing syntax and examples for each. The core of the report focuses on data manipulation, presenting and explaining ten SQL statements designed to query and modify data within a relational database. These statements cover various operations such as joining tables, filtering data based on specific criteria, updating data, and deleting records. Finally, the report delves into database testing, discussing the importance of testing and providing an overview of various testing methods, including field size validation, handling of null values, check constraints, and referential integrity. The bibliography provides a list of relevant sources. The report is a comprehensive overview of the key concepts in database management systems, providing practical examples of SQL queries and highlighting the importance of thorough database testing.

Running head: DATA MODELLING & SQL LANGUAGE
Data Modelling & SQL Language
Name of the Student
Name of the University
Author’s Note

1
DATA MODELLING & SQL LANGUAGE
Table of Contents
Task 1: Structure Query Language (SQL)......................................................................................2
Answer to Question 1:.................................................................................................................2
Answer to Question 2:.................................................................................................................3
Task 2: Data Manipulation..............................................................................................................5
SQL Statement 1:.........................................................................................................................5
SQL Statement 2:.........................................................................................................................6
SQL Statement 3:.........................................................................................................................6
SQL Statement 4:.........................................................................................................................7
SQL Statement 5:.........................................................................................................................8
SQL Statement 6:.........................................................................................................................8
SQL Statement 7:.........................................................................................................................9
SQL Statement 8:.........................................................................................................................9
SQL Statement 9:.......................................................................................................................10
SQL Statement 10:.....................................................................................................................10
Task 3: Database Testing...............................................................................................................11
Answer to Question 1:...............................................................................................................11
Answer to Question 2:...............................................................................................................12
Bibliography..................................................................................................................................14

2
DATA MODELLING & SQL LANGUAGE
Task 1: Structure Query Language (SQL)
Answer to Question 1:
The UPDATE statement is basically utilized for updating either the data or rather row within a
particular table.
The syntax of the command of UPDATE is written below:
Update table-name set column-name = value where any condition;
Example
Let us imagine that there is a table which has been named as employee
Department_No Department_Name Employee_No Employee_Name
5 IT E1 Harry
10 IT E2 Tom
15 Finance E6 Kate
20 Management E8 David
25 Finance E10 Alice
For updating this table the query which can be utilized:
Update employee set Employee_No = E4 where Department_No = 15
This query will be producing the result:
Department_No Department_Name Employee_No Employee_Name
5 IT E1 Harry
10 IT E2 Tom

3
DATA MODELLING & SQL LANGUAGE
15 Finance E4 Kate
20 Management E8 David
25 Finance E10 Alice
The DELETE statement is utilized for the deletion of row from the entire table within the
database
Syntax:
DELETE from table-name
Example:
Let us consider the same table named employee which has been presented above.
DELETE from employee where Department_No = 20
This query will be producing the result:
Department_No Department_Name Employee_No Employee_Name
5 IT E1 Harry
10 IT E2 Tom
15 Finance E6 Kate
25 Finance E10 Alice

4
DATA MODELLING & SQL LANGUAGE
Answer to Question 2:
The administrator of database actually defines the specific command of GRANT in the SQL for
providing with the particular access to all the various users of the entire database.
Syntax:
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
Example:
GRANT SELECT ON employee to user1;
This specific command will be actually granting the specific permission SELECT on the table of
employee to the user1. The option of WITH GRANT must be utilized very much carefully due to
the fact like for an example if GRANT SELECT privilege on the table of employee to the user1
will be utilizing the option of WITH GRANT, then it can be GRANT SELECT privilege upon
the specific employee table towards some other user like user 2 and so on.
The REVOKE command will be actually removing all the rights of the user access or rather
privileges to all the various objects of the database.
Syntax:
REVOKE privilege_name
ON object_name

5
DATA MODELLING & SQL LANGUAGE
FROM {user_name |PUBLIC |role_name}
Example:
REVOKE SELECT ON employee FROM user 1;
This particular command will actually REVOKE a SELECT privilege on the particular table of
employee from the user 1. Whenever anyone will REVOKE SELECT privilege upon the
particular table from a specific user, the user will not at all be able to SELECT data from that
specific table anymore. However, if any user will be receiving the privileges of SELECT on that
specific table from about more than one single user then he/she can be actually able to SELECT
from that specific table till each and everyone who will be granting the permission actually
revokes it. Anyone cannot be able to REVOKE privileges if they have not been granted initially.
Task 2: Data Manipulation
SQL Statement 1:
select CONCAT (first_name, ' ' , last_name) as fullname, employees.gender, salaries.salary from
employees Inner Join salaries on employees.emp_no = salaries.emp_no;

6
DATA MODELLING & SQL LANGUAGE
SQL Statement 2:
SELECT titles.title, departments.dept_name From titles join dept_emp ON titles.emp_no =
dept_emp.emp_no join departments ON departments.dept_no = dept_emp.dept_no

7
DATA MODELLING & SQL LANGUAGE
SQL Statement 3:
select CONCAT (first_name, ' ' , last_name) as fullname, employees.gender From employees
join dept_emp ON employees.emp_no = dept_emp.emp_no join departments ON
departments.dept_no = dept_emp.dept_no WHERE dept_emp.dept_no = 'd004'
SQL Statement 4:
SELECT departments.dept_name, CONCAT (first_name, ' ' , last_name) as fullname From
employees join dept_emp ON employees.emp_no = dept_emp.emp_no join departments ON
departments.dept_no = dept_emp.dept_no;

8
DATA MODELLING & SQL LANGUAGE
SQL Statement 5:
SELECT departments.dept_name, CONCAT (first_name, ' ' , last_name) as fullname From
employees join dept_manager ON employees.emp_no = dept_manager.emp_no join departments
ON departments.dept_no = dept_manager.dept_no
WHERE dept_manager.from_date > ('1990-1-1');

9
DATA MODELLING & SQL LANGUAGE
SQL Statement 6:
Write an SQL statement to change any employee’s date of birth. Assume the employee has just
phoned in with his/her last name.
UPDATE employees
SET birth_date='1953-09-04'
WHERE last_name='Facello'
SQL Statement 7:
UPDATE salaries
SET salary = salary * 0.55
WHERE from_date > '1997-01-01'
SQL Statement 8:
Write an SQL statement to delete employee’s record who belongs to department 'd004' and ID is
10003.
DELETE employees

10
DATA MODELLING & SQL LANGUAGE
FROM employees
LEFT JOIN
dept_emp ON employees.emp_no = dept_emp.emp_no
WHERE dept_no = 'd004' && employees.emp_no = '10003'
SQL Statement 9:
select CONCAT (first_name, ' ' , last_name) as fullname, departments.dept_name, salaries.salary
From employees join dept_emp ON employees.emp_no = dept_emp.emp_no join departments
ON departments.dept_no = dept_emp.dept_no JOIN salaries ON salaries.emp_no =
dept_emp.emp_no

11
DATA MODELLING & SQL LANGUAGE
SQL Statement 10:
SELECT employees.first_name, departments.dept_name From employees join dept_emp ON
employees.emp_no = dept_emp.emp_no join departments ON departments.dept_no =
dept_emp.dept_no WHERE
employees.emp_no = dept_emp.emp_no AND
employees.hire_date BETWEEN '1990-1-1' AND '2000-1-1'