SQL: Database Queries, Implementation Details, and Reflection

This assignment focuses on Structured Query Language (SQL) statements, providing examples and explanations for querying databases. It includes SQL statements for tasks such as counting books published by a specific publisher in a given year, identifying the highest royalty paid for a book, finding the top five authors with the highest royalties, determining the top 10 titles by sales, identifying publishers with sales, and calculating total sales for each publisher. The rationale behind each SQL statement is discussed, emphasizing the importance of identifying the necessary tables, output columns, and conditions. Special keywords like ‘IN’ and ‘EXISTS’ are explained, along with the use of GROUP BY clauses, inner joins, and left joins. The reflection section discusses alternative ways of writing SQL statements and the process of debugging queries using ACCESS software. The document concludes with a bibliography of relevant sources.

Running head: STRUCTURED QUERY LANGUAGE (SQL)
Structured Query Language (SQL)
Name of the Student
Name of the University

1STRUCTURED QUERY LANGUAGE (SQL)
Table of Contents
SQL Statements:..............................................................................................................................2
Rationale:.........................................................................................................................................4
Reflection:........................................................................................................................................4
Bibliography:...................................................................................................................................5

2STRUCTURED QUERY LANGUAGE (SQL)
SQL Statements:
Number of books published by New Moon Books in 2010:
SELECT Count(Books.title_id) AS CountOftitle_id, Publishers.pub_name FROM
Publishers LEFT JOIN Books ON Publishers.pub_id = Books.pub_id HERE
(((Books.pubdate)>=#1/1/2010# And (Books.pubdate)<=#12/31/2010#)) GROUP BY
Publishers.pub_name HAVING (((Publishers.pub_name)="New Moon Books"));
What was the highest royalty paid for a book?
SELECT Max(Books.royalty) AS MaxOfroyalty FROM Books;
Identify the five authors that made the highest royalty.
SELECT TOP 5 B.title, B.royalty FROM Books AS B WHERE (((B.royalty) In (Select
TOP 5 Bo.royalty From Books Bo Where Bo.title_id = B.title_id Order By B.royalty DESC)))
ORDER BY B.royalty DESC;
Identify who were the top 10 titles by sales.
SELECT TOP 5 B.title, B.ytd_sales FROM Books AS B WHERE (((B.ytd_sales) In
(Select TOP 10 Bo.ytd_sales From Books Bo Where Bo.title_id = B.title_id Order By
B.ytd_sales DESC ))) ORDER BY B.ytd_sales DESC;

3STRUCTURED QUERY LANGUAGE (SQL)
Which publishers had sales?
SELECT Publishers.pub_name FROM Publishers WHERE EXISTS (Select NULL
FROM Books WHere Publishers.pub_id = Books.pub_id ) GROUP BY Publishers.pub_name;
What were the total sales of each publisher?
SELECT Publishers.pub_name, Sum(Books.ytd_sales) AS SumOfytd_sales FROM
Publishers LEFT JOIN Books ON Publishers.pub_id = Books.pub_id GROUP BY
Publishers.pub_name;

4STRUCTURED QUERY LANGUAGE (SQL)
Rationale:
The main factor behind identifying the SQL statements is identifying the tables that are
required to derive the solution. Therefore, the first work was to write a SQL statement that
include all the tables. The next thing that had been done is mentioning the columns that are asked
as the output. In the next phase, the conditions have been identified and implemented. There are
various conditions that cannot be implemented simply by using the where clause and ‘=’
operator. For that cases, special keywords like ‘IN’ and ‘EXISTS’ have been used. GROUP BY
clause had special part in the SQL statements. It has enable the solution to be precise and show
the result as per the required columns. In some cases to show the highest set of values, GROUP
BY ‘column name’ DESC SQL statement has been used. The ‘inner join’ and ‘left join’ has been
used in all the table (where required) to joint two tables and show desired outcome.
In the first SQL statement, the result was extracted based on the condition that books
must be published in 2010 and published by New Moon Books. The count function has been
used for deriving the amount of total books published by any publisher. That is why in the Group
BY clause, the Publishers.pub_name has been used. The condition of selecting the books
published by New Moon Books has been implemented using the HAVING clause. The second
statement was a simple one. It just included a MAX() function. The ‘IN’ keyword has been used
in the third statement. The Order By B.royalty DESC has been used to show the highest value at
the top. The ‘TOP 5’ condition allows to show only the highest five results. The same logic has
been applied in the fourth statement. Here instead of TOP 5, TOP 10 has been used. Here, the
ORDER BY B.ytd_sales DESC; has been used. WHERE EXISTS has been used in the fifth
statement. The Null in the nested Select statement has ensured that only the polishers present in
the Books table are selected. Here the GROUP BY clause has been used as GROUP BY
Publishers.pub_name. In the sixth statement, the SUM function has been used to show the total
amount of book sales by each publisher.
Reflection:
There are various ways of writing a SQL statement that can reflect same output. First of
all, the main purpose writing a SQL statement is to get that output efficiently. The first way of
checking whether the SQL statements are right or wrong is by running it in the ACCESS
software. Often the results were not as per the requirement file. The first step, carried out after
receiving a wrong output, was to identify the columns that are used incorrectly. In the third
statement, the main problem was in the ORDER BY clause. The order by was selected for both
the B.title and B.royalty. Then, the title column was removed from the ORDER BY clause and
the putput was as expected.

5STRUCTURED QUERY LANGUAGE (SQL)
Bibliography:
Berry, D. L., Nayak, M. M., Abrahm, J. L., Braun, I., Rabin, M. S., & Cooley, M. E. (2017).
Clinician perspectives on symptom and quality of life experiences of patients during
cancer therapies: implications for eHealth. Psycho‐oncology, 26(8), 1113-1119.
Embley, D. W., Krishnamoorthy, M. S., Nagy, G., & Seth, S. (2016). Converting heterogeneous
statistical tables on the web to searchable databases. International Journal on Document
Analysis and Recognition (IJDAR), 19(2), 119-138.
Erlewad, D. M., Mundhe, K. A., & Hazarey, V. K. (2016). Dental informatics tool “SOFPRO”
for the study of oral submucous fibrosis. Journal of oral and maxillofacial pathology:
JOMFP, 20(2), 194.
Garg, R., Sharma, R., & Sharma, K. (2016). Ranking and selection of commercial off-the-shelf
using fuzzy distance based approach. Decision Science Letters, 5(2), 201-210.
Gill, R., & Singh, J. (2015). Enactment of Medium and Small Scale Enterprise ETL
(MaSSEETL)-an Open Source Tool. International Journal of Computer Science and
Information Technologies, 6(1), 141-147.
Liu, H., Al-Hussein, M., & Lu, M. (2015). BIM-based integrated approach for detailed
construction scheduling under resource constraints. Automation in Construction, 53, 29-
43.
Manning, K. (2016). The Cultural Evolution of Neolithic Europe. EUROEVOL Dataset 2:
Zooarchaeological Data. Journal of Open Archaeology Data, 5.
Patwardhan, A. (2016). Analysis of Software Delivery Process Shortcomings and Architectural
Pitfalls. arXiv preprint arXiv:1607.03748.
Pieterse, H., Olivier, M. S., & van Heerden, R. P. (2015, August). Playing hide-and-seek:
detecting the manipulation of android timestamps. In Information Security for South
Africa (ISSA), 2015 (pp. 1-8). IEEE.
Pore, S. S., & Pawar, S. B. (2015). Comparative Study of SQL & NoSQL Databases. structure,
4(5).
Prathyusha, Y., & Afroz, S. (2017). Enhanced UI Design Features for End User to Interact With
Database Servers.
Rahman, M. H., Al Abid, F. B., Zaman, M. N., & Akhtar, M. N. (2015, December). Optimizing
and enhancing performance of database engine using data clustering technique. In
Advances in Electrical Engineering (ICAEE), 2015 International Conference on (pp. 198-
201). IEEE.
Strang, G., Borre, K., & Linear Algebra, G. (2015). International Masterprogramme Geomatics.
Suckow, A. (2018). NobleControl and Noble LabData: a measurement control system and a sub-
database system for the post-processing of noble gas measurements in water samples.

6STRUCTURED QUERY LANGUAGE (SQL)
Vaikunth Pai, T., & Aithal, P. S. (2017). Disconnected Data Access Architecture using
ADO .NET Framework. International Journal of Applied Engineering and Management
Letters (IJAEML), 1(2), 10-16.
Yadav, A. K., Kanga, S., & Kumar, R. (2017). Web Enabled GIS Based Tourism Information
System for Shimla Municipality (HP), India. i-Manager's Journal on Information
Technology, 7(1), 18.