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

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';