Database Queries

This document provides a comprehensive guide on database queries, covering topics such as Structure Query Language (SQL), Data Manipulation, Data Control Language, and Database Testing. It explains the purpose and syntax of various SQL commands, such as CREATE and ALTER, and discusses the importance of database testing. The document also includes sample queries and examples for reference.

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 2...................................................................................................................................5
Query 3...................................................................................................................................5
Query 4...................................................................................................................................6
Query 5...................................................................................................................................6
Query 6...................................................................................................................................7
Query 7...................................................................................................................................7
Query 8...................................................................................................................................8
Query 9...................................................................................................................................9
Query 10...............................................................................................................................10
Task 3: Database Testing.........................................................................................................11
Task 4: Referencing and Bibliography....................................................................................16

2
DATABASE QUERIES
Task 1: Structure Query Language (SQL)
Data Definition Language
The main purpose behind using data definition language is that it helps in defining the
data structure that is used for modifying the data within the SQL. This is having a predefined
syntax that is used with the aim of discussing the need of data. The two commands that are
used in DDL are create and alter. The create command is used with the aim of creating a new
database or for creating a new table. This includes the functions related to the table, views
and triggers. On the other hand ALTER command is used for the purpose of altering the
structure of the database. The SQL is used for the purpose of collecting the imperative verbs
that focuses on modifying the schema based on the database by deleting the modifying
elements within the database and also offers adding elements within the database.
CREATE command: CREATE command is used for creating a component within the
relational database management system. The components that are related to creation of a
database elements includes views, assertions and tables and many more. The data types that
are offered in the table includes int, double, float, varchar, date and text. The main aim
behind using the create command is that it is used at the time of creating database within a
relational database management system. The syntax for create command is as follows:
CREATE DATABASE <DB_NAME>;
In order to create a table, the syntax used is as follows:
CREATE TABLE <TABLE_NAME>
{
Column_name1 datatype1.
Column_name2 datatype2,

3
DATABASE QUERIES
};
ALTER command: the command is used for making changes within the already existing table
structure. The functions associated with this includes adding up of a column to the already
existing table, renaming the already existing column, includes changing data within the
database. This also offers modifying the size of the table and also allows the user to drop the
columns from the chosen table. ALTER command is used with the purpose of changing the
properties within the relational database management system. The changes that are allowed to
be altered mainly depends on RDBMS. The syntax that is used for the ALTER Command are
as follows:
ALTER TABLE table_name ADD (
Column_name datatype);
In order to add multiple new columns the syntax used are as follows:
ALTER TABLE table_name ADD (
column_name1 datatype1,
Column-name2 datatype2,
column-name3 datatype3);
For adding column with the use of a default value:
ALTER TABLE table_name ADD (
Column-name1 datatype1 DEFAULT some_value
);

4
DATABASE QUERIES
Data Control Language
The data control language is used for the purpose of computer programming
language. This are used for controlling the access that is provided towards the data stored.
The type of commands that are included within the DCL command are GRANT and
REVOKE. GRANT command allows the user to perform specific tasks. On the other hand
REVOKE command is used with the purpose of cancelling the previously granted or denied
permissions. The syntax that is used for GRANT is as follows:
GRANT privilege_name
ON object_name
TO {user_name Public|role_name}
[WITH GRANT OPTION];
In this the privilege_name is used with the aim of providing privilege to the users. The access
rights offered towards the user includes SELECT, ALL and EXECUTE. The object_name is
referred to the database object that contains TABLE, sequence and VIEW. Public is used
with the aim of granting access rights towards all the users. A set of privileges are used for
ROLES that ensures proper grouping of the components. GRANT option is mainly used with
the aim of providing access rights towards the users.
The syntax of REVOKE command is as follows:
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC | role_name}

5
DATABASE QUERIES
Task 2: Data Manipulation
Query 1
select CONCAT(first_name, " " , last_name) As 'Full_Name', gender, salary from employees,
salaries where employees.emp_no = salaries.emp_no;
Query 2
Select CONCAT(first_name, " ", last_name) As 'Full_Name',title, dept_name from
employees, titles, dept_emp, departments where employees.emp_no = dept_emp.emp_no and
dept_emp.dept_no = departments.dept_no and employees.emp_no = titles.emp_no;
Query 3
select first_name, last_name, gender from employees, departments, dept_emp where
dept_emp.emp_no = employees.emp_no and departments.dept_no = dept_emp.dept_no and
departments.dept_no = 'd004';

6
DATABASE QUERIES
Query 4
Select CONCAT(first_name, " ", last_name) As 'Full_Name',dept_name from employees,
dept_manager, departments where employees.emp_no = dept_manager.emp_no and
dept_manager.dept_no = departments.dept_no;
Query 5
Select CONCAT(first_name, " ", last_name) As 'Full_Name',dept_name,
dept_manager.from_date from employees, dept_manager, departments where
employees.emp_no = dept_manager.emp_no and dept_manager.dept_no =
departments.dept_no and dept_manager.from_date >'1987-01-01';

7
DATABASE QUERIES
Query 6
UPDATE employees SET birth_date ='1950-10-11' where last_name = 'Bamford';
Query 7
select * from employees, departments, dept_emp where dept_emp.emp_no =
employees.emp_no and departments.dept_no = dept_emp.dept_no and departments.dept_no
= 'd004' and employees.emp_no = '10003';

8
DATABASE QUERIES
DELETE from dept_emp where EXISTS (select * from employees, departments where
dept_emp.emp_no = employees.emp_no and departments.dept_no = dept_emp.dept_no and
departments.dept_no = 'd004' and employees.emp_no = '10003');
select * from employees, departments, dept_emp where dept_emp.emp_no =
employees.emp_no and departments.dept_no = dept_emp.dept_no and departments.dept_no
= 'd004' and employees.emp_no = '10003';
Query 8
create view emp_details AS
Select CONCAT(first_name, " ", last_name) As 'Full_Name', dept_name, salary from
employees, dept_emp, departments, salaries where employees.emp_no = salaries.emp_no and
employees.emp_no = dept_emp.emp_no and dept_emp.dept_no = departments.dept_no ;

9
DATABASE QUERIES
SELECT * FROM `emp_details`
Query 9
CREATE view dept_managers AS
Select CONCAT(first_name, " ", last_name) As 'Full_Name',dept_name from employees,
dept_manager, departments where employees.emp_no = dept_manager.emp_no and
dept_manager.dept_no = departments.dept_no and dept_manager.from_date > '1980-01-01'
and dept_manager.from_date < '1991-01-01';
SELECT * FROM `dept_managers`

10
DATABASE QUERIES
Query 10
SELECT * FROM `salaries`, employees WHERE employees.emp_no = salaries.emp_no
UPDATE salaries, employees
SET salary = salary * 1.1
WHERE employees.emp_no = salaries.emp_no and employees.hire_date >= ‘1990-01-01’;
SELECT * FROM `salaries`, employees WHERE employees.emp_no = salaries.emp_no

11
DATABASE QUERIES
Task 3: Database Testing
Database testing has become one of the important testing that are used by the testers
so that every requirement can be tested properly within system before implementation.
Testing are generally conducted with aim of understanding the application. This ensures that
proper database is implemented by following the major rules. Apart from this the ester needs
to ensure that proper working is taking place within the SQL queries. The steps included
within the database testing includes data validity testing, performance associated with the
database, testing the procedure and triggering the functions.
It is important to ensure that proper database management system is implemented so
that high relevance can be offered towards the data stored within the system. This also
ensures that proper consistency is maintained. With the database testing proper data can be
stored within the system, this ensures proper working of the system. The database
management system stores proper data and organizes huge amount of information that can be
stored with the help of a single software application.
Field size validation: This helps in ensuing that proper data is stored within the fields with
appropriate length. This ensures that the length size is maintained throughout the validation
process. The quality of data needs to be maintained within the database. Field size is one of
the major factors that is used for determining the field size.

12
DATABASE QUERIES
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES ('d11111111111', 'test')
The above query is used for testing. The test results are provided below:
Null values: NULL values constraint are used for the purpose of storing NULL values. In
case user needs to have not null value within the column. This is important to ensure NOT
NULL constraint so that the column can avoid having a null value.
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`,
`hire_date`) VALUES ('123', '', '', '', '', '');
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
query. A check constraint can be defined in either a CREATE TABLE statement or an
ALTER TABLE statement.

13
DATABASE QUERIES
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 `dept_emp` (`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES
('123', 'd999', '2015-09-16', '2017-08-16');


14
DATABASE QUERIES
Task 4: Referencing and Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Mahajan, S., Abrol, P. and Lehana, P.K., 2016. Design and Development of 3D Insect
Modeling, Identification, and Dynamic Database Updation. International Journal of Scientific
and Technical Advancements, 2(2), pp.43-46.
Mallikarjun Sharada, S., Bligaard, T., Luntz, A.C., Kroes, G.J. and Nørskov, J.K., 2017.
SBH10: A Benchmark Database of Barrier Heights on Transition Metal Surfaces. The
Journal of Physical Chemistry C, 121(36), pp.19807-19815.
Pawar, P., Joshi, O. and Saraf, M., 2017. Generation of 3D-Digital Indian Public Road Profile
Database and Its Application for Vehicle Development through Road-Vehicle Interaction
Study. SAE International Journal of Vehicle Dynamics, Stability, and NVH, 1(2017-26-
0275), pp.80-88.
Sterner, E., Flanagan, N. and Gildersleeve, J.C., 2016. Perspectives on anti-glycan antibodies
gleaned from development of a community resource database. ACS chemical biology, 11(7),
pp.1773-1783.
Youseff, G.A. and Ibrahim, R.E., 2016. Design and development of web-based database for
managing NARSS projects. International Journal of Information Technology and
Management Information System, 7(2), pp.27-35.
Zhang, N. and Song, W., 2016. Database Design on Construction Project Cost System.