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`