SQL: DDL Create and Alter, DCL Grant and Revoke, Data Manipulation, Database Testing

This article covers SQL concepts such as DDL create and alter, DCL grant and revoke, data manipulation, and database testing. It includes SQL statements and examples for each concept.

COVER PAGE

Task 1: Structure Query Language (SQL)
1. Describe DDL create and alter
CREATE
Create statement is a data definition language statement that is used to create a new table in a
database.
The correct syntax for create table statement is shown below;
CREATE TABLE name_of_table (
Column1 datatype,
Column2 datatype,
….
);
An example of create table statement can be demonstrated by creating a customers table in the
example below;
CREATE TABLE customers (
Customerid integer primary key,
Firstname varchar(50) not null,
Lastname varchar(50) no null.
Email varchar(50) not null unique,
Address varchar(50) null
);
When executed, the query above will create a customers table with customerid column as the
primary key. This mean that the customer id should be unique for every customer record. The
email is also supposed to be unique for every customer.
ALTER
The alter statement is data definition language that is used to add, delete and modify columns
and constraints in a table. The statement is used for an existing table.
The correct syntax for altering a table is;
ALTER TABLE name_of_table
ADD column_name datatype;
For example considering the customers table created in the sub-section above, a mobile number
column can be added using the alter command ash shown below;
ALTER TABLE customers
ADD mobile_number varchar(25) not null;
The alter statement can also be used to drop a column and the syntax for dropping a column is
shown below;

ALTER TABLE name_of_table
DROP COLUMN name_of_column;
An example of dropping a column is shown below;
ALTER TABLE customers
DROP COLUMN mobile_number;
The Alter statement can also be used to modify a column as shown in the example below;
ALTER TABLE customers
ALTER COLUMN mobile_number varchar(25) null;
When the statement above is executed, it alters the mobile_number from not null to null.
For constraints, the ALTER statement can be used to add, modify or drop constraints as shown in
the examples below;
ALTER TABLE customers
ADD CONSTRAINT customers_fk (customerid) references person
(personID);
The statement above adds a foreign key to the customers table assuming there exists a person
table with a personID column which is the primary key.
2. Describe DCL Grant and Revoke
GRANT
Grant is a data control language statement that is used to grant privileges to users and roles for
database objects including tables or views.
For example the database administrator can grant update privilege to a user db_user1 for the
customers table using the code below;
GRANT UPDATE ON customers TO db_user1;
The statement above gives update privilege to db_user1 on the customers table.
REVOKE
Revoke is a data control language statement that is used to revoke privileges or rights from
users or roles.
For example, the database administrator can revoke update privilege from db_user1 on the
customers table using the code below;
REVOKE UPDATE ON customers FROM db_user1;

When executed, the statement above revokes the update right from db_user1 on table
customers.
Task 2: Data Manipulation
1. Create a SQL statement to list all managers and their titles.
select concat (first_name,' ',last_name),title from departments inner
join dept_manager on dept_manager.dept_no=departments.dept_no inner join
employees on employees.emp_no=dept_manager.emp_no inner join titles on
titles.emp_no=employees.emp_no;
2. Create a SQL statement to show the salary of all employees and their department name.
select concat(first_name,' ',last_name),salary,dept_name from employees
inner join dept_emp on dept_emp.emp_no=employees.emp_no inner join
salaries on salaries.emp_no=employees.emp_no inner join departments on
dept_emp.dept_no=departments.dept_no group by employees.emp_no;
3. Create a SQL statement to show the hire date and birth date who belongs to HR
department
select concat(first_name,' ',last_name),hire_date, birth_Date from
employees 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';

4. Create a SQL statement to show the all departments and their department’s managers.
select dept_name, concat (first_name,' ',last_name) from departments
inner join dept_manager on dept_manager.dept_no=departments.dept_no
inner join employees on employees.emp_no=dept_manager.emp_no group by
dept_name order by to_date desc;
5. Create a SQL statement to show a list of HR’s employees who were hired after 1986
select concat(first_name,' ',last_name),hire_date from employees 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(hire_date)>=1986;
6. Create a SQL statement to increase any employee’s salary up to 2%.Assume the
employee has just phoned in with his/her last name.
Assuming the employee with the last name Maliniak called;
Before update
Update statement

update employees,salaries set salary=salary+salary*0.02 where
employees.emp_no=salaries.emp_no and last_name='Maliniak';
After Update
7. Create a SQL statement to delete employee’s record who belongs to marketing
department and name start with A
delete from employees where emp_no in (select employees.emp_no from
departments,dept_emp where dept_emp.dept_no=departments.dept_no and
departments.dept_name='Marketing') and left(last_name,1)='A';
8. Create a database view to list the full names of all departments’ managers, and their
salaries.
create view employee_details as select concat(first_name,'
',last_name),dept_name,salary from employees inner join dept_emp on
dept_emp.emp_no=employees.emp_no inner join departments on
departments.dept_no=dept_emp.dept_no inner join salaries on
salaries.emp_no=employees.emp_no;

Getting data from the view;

9. Create a database view to list all departments and their department’s managers, who
were hired between 1980 and 1990.
create view department_managers as select
dept_name,concat(first_name,' ',last_name),hire_date from
departments inner join dept_manager on
dept_manager.dept_no=departments.dept_no inner join employees on
employees.emp_no=dept_manager.emp_no where hire_date between
'1980-01-01' and '1990-12-31';
10. Create a SQL statement to increase salaries of all department’s managers up to 10%
who are working since 1990.
update salaries set salary=salary+(salary*0.01) where emp_no in (select
emp_no from dept_manager where year(from_date)>=1990);

Task 3: Database Testing
1. Describe database testing and why its important
Database testing is the process of testing different aspects and components of the database to make
sure they work the way they are supposed to. Testing in a database is done in levels or layers. The layers
involved in testing a database are;
 User interface layer- testing at this level is done to make sure that the database will work
correctly with the end application designed to use the database.
 Business layer- Testing at this level is done to make sure the database meets all the business
requirements of the business on which the database is based on.
 Data access layer – Testing at this level is done to make sure the database is accessible and the
data stored in the database is useful
 Database layer- Testing at this level is done on the database itself and includes testing ACID
properties and data integrity in the database.
Database testing is very important as it helps ensure that the forms used in the front end part of the
application can be correctly mapped to the respective database fields thus ensuring data mapping is
successful. Database testing is also done to validate the ACID properties of the database. Testing ACID
properties involves testing for;
 Atomicity- Atomicity specifies that database transactions should either pass or fail such that if a
transaction passes then changes should reflect in the database but if the transaction then no
changes should reflect in the database. A transaction should either pass or fail and there is
nothing else (Sharma et al., 2019).
 Consistency- Consistency is done to make sure a database transaction leaves the database in a
consistent state.
 Isolation- Isolation ensures that database transactions execute independently and concurrently
while at the same time maintaining database consistency. This means that concurrent execution
of many transactions should happen as if the transactions are executing in order.
 Durability- Durability testing ensures that a database transaction that is committed leads to
permanent changes in the database which are written on disk. However if a transaction is rolled
back, no changes should be done on the database (Rouse, 2006).
2. Testing process with screenshots
 Field size validation
Field size validation involves testing that the size specified for a column in the database is not
violated. For example if a column is only supposed to take only 5 characters, if an insert or
update transaction is performed on the table, then the database should automatically trim the
data to 5 characters or throw an error depending on the configuration parameters.

For example, field size validation can be tested by inserting data that exceeds the specified
length for dept_no column in the departments table. According to the structure of the
departments table, the dept_no is supposed to take 4 characters as shown below;
Thus an insert with more tha 4 characters should fail or trim the characters to 4 characters;
insert into departments (dept_no,dept_name) values
('d123456789','My department');
Executing the query above results to a trimmed value.
 Null values
Testing for null values means that a column with null attribute can take or not take a value.
Columns that are defined as not null cannot take a null value. To test the null and not null values
in a database, a null value can be inserted into a field defined as not null and the database
should throw an error as shown below;
Considering the insert query below which is supposed to insert a record in the department
table;
insert into departments (dept_no,dept_name) values ('d923',null);
The query is executed successfully but throws an error because a null value cannot be inserted
into a column defined as not null.

 Check constraint
A check constraint is enforced to ensure that the value inserted or updated in a column meets
the condition of the check constraint. For example in the employees table, the gender is
supposed to be either M for male or F for Female. Violating this should throw an error.
Thus adding a check constraint in employees table can be done using the code below;
alter table employees add constraint check (gender in ('F','M'));
Inserting a value that is not F or M in the gender column should result to an error or insert a null
value depending on the server configuration;
insert into employees (emp_no,birth_Date, first_name,last_name,
gender,hire_date) values (1000244,'1990-01-
02','John','Doe','K','2008-09-09');
This results to a null value in the gender column because the value inserted was not F or M.
 Referential Integrity

Testing referential integrity involves testing that relationships between primary keys and foreign
keys always maintain referential integrity (Poolet, 2009). This means that when inserting or
updating a foreign key, there must exist a foreign key with that value in the parent table.
For example trying to insert a record in the child table for a primary key that does not exist
should result to an error as shown below;
insert into titles (emp_no,title,from_date,to_date) values
(1000043,'Lead developer','2012-12-12','2018-12-12');
When the query above is executed, it results to an error because the emp_no supplied does not
exist in the parent thus violates referential integrity;
References
Poolet, M. (2009). SQL By Design: The Four Integrities. [online] IT Pro Today. Available at:
https://www.itprotoday.com/sql-server/sql-design-four-integrities [Accessed 13 Mar. 2019].

Rouse, M. (2006). What is ACID (atomicity, consistency, isolation, and durability)? - Definition from
WhatIs.com. [online] SearchSQLServer. Available at:
https://searchsqlserver.techtarget.com/definition/ACID [Accessed 13 Mar. 2019].
Sharma, R., Mohamed, A., Aryan, M., kuchana, r. and Ramesh, M. (2019). Database Testing Complete
Guide (Why, What, and How to Test Data). [online] Softwaretestinghelp.com. Available at:
https://www.softwaretestinghelp.com/database-testing-process/ [Accessed 13 Mar. 2019].