Database Management: SQL Queries, Data Manipulation, and Testing

This assignment focuses on Structured Query Language (SQL) and its applications in database management. It covers Data Definition Language (DDL) commands like CREATE and ALTER for defining and modifying database structures, as well as Data Control Language (DCL) commands like GRANT and REVOKE for managing user permissions. The assignment includes practical SQL queries for data manipulation, such as selecting, joining, and updating data across multiple tables. It also demonstrates database testing techniques, including field size validation, null value handling, check constraints, and referential integrity. The testing process is illustrated with screenshots, and the assignment concludes with a bibliography of relevant resources. Desklib offers this assignment as a resource for students to enhance their understanding of SQL and database management.

Running head: DATABASE QUERIES
Database Queries
Name of the Student:
Name of the University:
Author Note

1
DATABASE QUERIES
Table of Contents
Task 1: Structure Query Language (SQL).................................................................................2
Data definition language........................................................................................................2
Data control language............................................................................................................3
Task 2: Data Manipulation.........................................................................................................4
Query 1...................................................................................................................................4
Query 2...................................................................................................................................5
Query 3...................................................................................................................................5
Query 4...................................................................................................................................6
Query 5...................................................................................................................................7
Query 6...................................................................................................................................7
Query 7...................................................................................................................................9
Query 8.................................................................................................................................11
Query 9.................................................................................................................................12
Query 10...............................................................................................................................13
Task 3: Database Testing.........................................................................................................15
Database Testing and its importance....................................................................................15
Screenshots of the testing process........................................................................................16
Task 4: Referencing and Bibliography....................................................................................21

2
DATABASE QUERIES
Task 1: Structure Query Language (SQL)
Data definition language
The purpose of using data definition language is that is being used for the purpose of
defining the structure of data that helps at the time of data modification. Data definition
language offers a predefined syntax that is being used for the purpose of explaining the data
used in the system. In DDL, create command is being used for the purpose of creating a
database with a related objects present in it. This database contains proper triggers, functions,
views and tables. On the other hand Alter command is used for the purpose of altering the
database structure. The main reason behind using an imperative verb in structure query
language is that it helps in modifying the schema of database by modifying the elements
present within the database.
Create command: CREATE command is used mainly for the purpose of creating a
component in the relational database management system. The main components of
CREATE command are tables, assertions, views and many more. Apart from this the data
types that are being used for columns maintained by tables includes float, double, int, date,
varchar and text. The create command syntax is described below:
CREATE TABLE <TABLE_NAME>
{
Column_name1 datatype1.
Column_name2 datatype2,
};
Alter statement: alter statement is used for the purpose of altering the already existing table.
This function includes modifying the table, adding columns to the existing table, changing

3
DATABASE QUERIES
the column name and also includes modifying data type and also includes changing the size
of table. The use of ALTER statement is that it helps in changing the properties of relational
database management system. The changes depends on the relational database management
system. The syntax of ALTER is described below:
Syntax adding new column:
ALTER TABLE table_name ADD (
Column_name datatype);
Syntax for adding multiple columns:
ALTER TABLE table_name ADD(
column_name1 datatype1,
Column-name2 datatype2,
column-name3 datatype3);
adding a column with default value:
ALTER TABLE table_name ADD(
Column-name1 datatype1 DEFAULT some_value
);
Data control language
Data control language offers a syntax that is used for the purpose of performing
computer programming language. This is mainly used for having a proper control over the
data stored. The commands that are being offered in DCL includes GRANT and REVOKE.

4
DATABASE QUERIES
GRANT statement ensures that proper permission is offered to specified users. On the
other hand REVOKE command is used for cancelling the command that is passed previously.
GRANT syntax is as follows:
GRANT privilege_name
ON object_name
TO {user_name Public|role_name}
[WITH GRANT OPTION];
From the above structure it can be stated that privilege_name is used for granting
privilege towards the users. The access rights includes SELECT, ALL and EXECUTE. Here
the object_name is referred to the name of database object that contains VIEW, sequence and
TABLE. Moreover the PUBLIC is being used for providing access rights to all users of
database. The command WITH GRANT OPTION is used for providing the access rights to
other users.
The syntax of REVOKE command is as follows:
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC | role_name}
Task 2: Data Manipulation
Query 1
SELECT CONCAT(employees.`first_name`," ", employees.`last_name`) As Fullname,
titles.title FROM employees

5
DATABASE QUERIES
INNER JOIN dept_manager ON employees.emp_no = dept_manager.emp_no
INNER JOIN titles ON employees.emp_no = titles.emp_no;
Query 2
SELECT CONCAT(employees.`first_name`," ", employees.`last_name`) As Fullname,
salaries.salary, departments.dept_name
FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
INNER JOIN dept_emp ON dept_emp.emp_no = employees.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no;
Query 3
SELECT CONCAT(employees.`first_name`," ", employees.`last_name`) As Fullname,
employees.hire_date, employees.birth_date

6
DATABASE QUERIES
FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
INNER JOIN dept_emp ON dept_emp.emp_no = employees.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = "Human Resources";
Query 4
SELECT departments.dept_name, CONCAT(employees.`first_name`," ",
employees.`last_name`) As Fullname
FROM departments
INNER JOIN dept_manager ON departments.dept_no = dept_manager.dept_no
INNER JOIN employees ON dept_manager.emp_no = employees.emp_no;

7
DATABASE QUERIES
Query 5
SELECT CONCAT(employees.`first_name`," ", employees.`last_name`) As Fullname,
YEAR(employees.hire_date)
FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
INNER JOIN dept_emp ON dept_emp.emp_no = employees.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = "Human Resources" AND YEAR(employees.hire_date)
> 1986;
Query 6
Select * From Employees

8
DATABASE QUERIES
Select * From Salaries
UPDATE employees,salaries
SET salary = (salary * 1.02)
WHERE employees.emp_no = salaries.emp_no AND employees.last_name = "Facello";
Select * From Salaries

9
DATABASE QUERIES
Query 7
Select * From Employees
Select * From departments;

10
DATABASE QUERIES
Select * From dept_emp;

11
DATABASE QUERIES
Select * From Employees
DELETE employees.* FROM employees
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = "Marketing" AND employees.first_name LIKE 'A%';
SELECT * FROM employees
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = "Marketing" AND employees.first_name LIKE 'A%';
Query 8
CREATE VIEW manager_salaries AS
SELECT CONCAT(employees.`first_name`," ", employees.`last_name`) As Fullname,
salaries.salary
FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
INNER JOIN dept_manager ON dept_manager.emp_no = employees.emp_no
INNER JOIN departments ON dept_manager.dept_no = departments.dept_no;
SELECT * FROM `manager_salaries`

12
DATABASE QUERIES
Query 9
CREATE VIEW dept_manager_hire AS
SELECT departments.dept_name, CONCAT(employees.`first_name`," ",
employees.`last_name`) As Manager, employees.hire_date
FROM departments
INNER JOIN dept_manager ON departments.dept_no = dept_manager.dept_no
INNER JOIN employees ON employees.emp_no = dept_manager.emp_no
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE YEAR(employees.hire_date) < 1990 AND YEAR(employees.hire_date) > 1980;
SELECT * FROM `dept_manager_hire`

13
DATABASE QUERIES
Query 10
Select * From Departments
SELECT * FROM `employees`

14
DATABASE QUERIES
SELECT * FROM `dept_manager`
SELECT * FROM `salaries`

15
DATABASE QUERIES
UPDATE employees,salaries, dept_manager
SET salary = (salary * 1.1)
WHERE employees.emp_no = salaries.emp_no AND employees.emp_no =
dept_manager.emp_no AND YEAR(employees.hire_date) > 1990;
Select * From salaries;
Task 3: Database Testing
Database Testing and its importance
It is important to have proper database testing so that all the function of database can
be understood properly. Database testing is a crucial part for every database as it includes
testing the database for checking the necessary requirements related to database. The steps
that are included in database testing are writing queries, checking tables and procedures.
Testing procedure is conducted with the help of application. This ensures that important rules

16
DATABASE QUERIES
for implementing database are followed at the time of database testing. Apart from this it is
important to ensure that proper working is taking place and is independent of SQL queries.
Database testing steps includes data integrity testing, performance testing data validity
testing, testing all the procedures, triggers and functions. The main reason behind database
testing is to check the functionality related to the database. The actions that are being
included in database testing includes saving options, adding options and deletion. This checks
whether the data is being removed from database or not.
In addition to this it is important to ensure proper management of database system so
that it can provide high relevance towards the data maintained within the system. With the
help of database testing it can be ensured that proper data is maintained within the system.
This ensures proper management of data in an efficient way and allows users to perform
multiple task at a time. Database management system manages, stores and organizes the
information that are maintained within a software application.
Screenshots of the testing process
Field size validation: Field size validation ensures that proper data is entered within the
fields. Field size data validation is a process that ensures proper validation of the data. The
data quality needs to be maintained properly within the database. The field size is important
for determining the size of the field.
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('d000000002', 'test1');
The above query is used for testing. The test results are provided below:

17
DATABASE QUERIES
It is observed that the data in field is truncated.
Output:
SELECT * FROM `departments`
Null values: every column within the database can hold NULL values. In case the user wants
to have not null value within the column it becomes important to define NOT NULL
constraints. NULL constraint will allow the user to enter null values within the database.
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`,
`hire_date`) VALUES ('123', '', '', '', '', '');

18
DATABASE QUERIES
The above query is used for testing. The test results are provided below:
Output
SELECT * FROM `employees`
Check constraints: check constraint is defined on a table for refereeing to only columns in
the table. However it is important to ensure that checks constraint does not include any sub

19
DATABASE QUERIES
query. A check constraint can be defined in either a CREATE TABLE statement or an
ALTER TABLE statement.
Result is that duplicate entry into database is not allowed.
Referential integrity: Referential integrity constraint is basically defined as a part of an
association between two entity types. The referential integrity constraint is used for the
purpose of specifying the following information, this includes principal end of constraint.
This helps in identifying the primary key within the tables of the database. This also helps in
establishing the relationships between two columns in the same table or between different
tables. In this one or more columns can be defined as a foreign key. The different types of
referential integrity constraints includes:
 Entity integrity constraints.
 Referential Integrity Constraints
 Key constraints.
INSERT INTO `salaries` (`emp_no`, `salary`, `from_date`, `to_date`) VALUES ('1007', '200',
'2017-08-22', '2018-07-16');
The above query is used for testing. The test results are provided below:

20
DATABASE QUERIES

21
DATABASE QUERIES
Task 4: Referencing and Bibliography
Huang, Z., Chen, Y., Wan, L., & Peng, X. (2017). GeoSpark SQL: An effective framework
enabling spatial queries on spark. ISPRS International Journal of Geo-Information,
6(9), 285.
Kornacker, M., Behm, A., Bittorf, V., Bobrovytsky, T., Ching, C., Choi, A., ... & Joshi, I.
(2015, January). Impala: A Modern, Open-Source SQL Engine for Hadoop. In Cidr
(Vol. 1, p. 9).
Panev, K., Michel, S., Milchevski, E., & Pal, K. (2016). Exploring databases via reverse
engineering ranking queries with PALEO. Proceedings of the VLDB Endowment,
9(13), 1525-1528.
Tan, W. C., Zhang, M., Elmeleegy, H., & Srivastava, D. (2017). Reverse engineering
aggregation queries. Proceedings of the VLDB Endowment, 10(11), 1394-1405.
Zhang, Y., Genkin, D., Katz, J., Papadopoulos, D., & Papamanthou, C. (2017, May). vSQL:
Verifying arbitrary SQL queries over dynamic outsourced databases. In 2017 IEEE
Symposium on Security and Privacy (SP) (pp. 863-880). IEEE.