Database Queries

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