MN405 Data & Information Management: SQL Queries and Database Tasks

This assignment solution details the creation of a database, its population with sample data, and the execution of SQL queries to retrieve specific information. The solution covers creating a 'Chandra_Customers' table and displaying customer details in ascending order of 'customer_id' using the 'ORDER BY' clause. It also demonstrates how to retrieve booking details from the 'Chandra_Bookings' table, showing how to select all fields using the asterisk (*) symbol. Furthermore, the solution illustrates how to count the total number of booking records in the 'Chandra_Bookings' table using the 'COUNT()' function and rename the output field using the 'AS' keyword. Screenshots of the SQL queries and their corresponding outputs are provided to visualize the results. Desklib provides a platform to explore more solved assignments.

Running head: DATABASE CREATION, POPULATION AND SQL QUERIES
DATABASE CREATION, POPULATION AND SQL QUERIES
Name of the Student
Name of the University
Author Note

1DATABASE CREATION, POPULATION AND SQL QUERIES
Table of Contents
Solution A2 – SQL queries........................................................................................................2
Solution a...............................................................................................................................2
Solution b...............................................................................................................................3
Solution c...............................................................................................................................4
Reference....................................................................................................................................6

2DATABASE CREATION, POPULATION AND SQL QUERIES
Solution A2 – SQL queries
On successful creation of the database with proper table and attribute names and
successfully populating all the tables successfully with valid sample data, the sample
database is established. Now, in order to retrieve information from the populated database
tables, some sql queries can be done. These queries, with specific details helps a person to
retrieve the data they need the most (provided the data is present in the database). One can
sort the data according to a specific field order (ascending or descending), rename the
retrieved field name, count the total amount of data present in a specific table or can simply
view all the details of a selected table. Data can also be retrieved based on specific
constraints; the constraints can be anything from retrieval based on a specific field value like
greater then, less than or equal to the mentioned value. The following SQL solutions will
detail about some basic sql queries with their outputs.
Solution a
In the created Chandra_Customers table in the Party Booking DB database, all the
customer record details can be displayed by using the ‘*’ symbol. The asterisk in a database
table means all the details in that selected table. In Chandra_Customers table, all the details
include ‘customer_id’ and ‘customer_details’. Now the problem statement has an additional
requirement. That is the result should be displayed with sorted ascending order of
‘customer_id’. In order to sort the desired out in ascending order of ‘customer_id’, the
ORDER BY keyword is used; by default, the order is considered as ascending (if neither
ascending nor descending is mentioned after writing the ORDER BY keyword and the
column name) [1]. Now, for the SQL query to display all the customer details with sorted
customer_id as ascending order, it is as follows:

3DATABASE CREATION, POPULATION AND SQL QUERIES
SELECT *
FROM Chandra_Customers
ORDER BY Chandra_Customers.customer_id;
The screenshot of the query and the result output are as follows:
Fig1: Solution a SQL query screenshot
Fig2: Solution a SQL output result screenshot
Solution b
The Chandra_Bookings table in the Party Booking DB database includes
‘booking_id’, ‘booking_taken_by_staff_id’, ‘customer_id’, ‘date_booked’, ‘date_of_event’
and ‘other_details’. Now, in order to find the booking details in the Chandra_Bookings table,
field details like for which id the booking was done (booking_id), who made the booking
(customer_id) and who took the booking (booking_taken_by_staff_id) are needed. More
details like when the booking was done (date_booked), for which date the reservation have
been made (date_of_event) and about the event booking status (other_details) are also needed

4DATABASE CREATION, POPULATION AND SQL QUERIES
in order to find the booking details. While populating the database, it have been thought out
that the other_details can be of three types (event success, event pending and event failed).
Now in order to view all the details the asterisk sign can be used (like the last solution).
Hence, the SQL query for the same is as follows:
SELECT*
FROM Chandra_Bookings;
This will display all the bookings related details from the Chandra_Bookings table. The
screenshot of the above query and the result output are as follows:
Fig3: Solution b SQL query screenshot
Fig4: Solution b SQL output result screenshot
Solution c
In order to find the total number of booking data present in the Chandra_Bookings
table, it is convenient to use COUNT () [2]. Normally the count is done on the primary key
field (booking_id). It is so because a field with primary key will not have any duplicate
values in it. All the values will have to be distinct from each other or else the entered value
will not be registered as a proper one and an error message will pop up mentioning, duplicate

5DATABASE CREATION, POPULATION AND SQL QUERIES
values in primary key field is not allowed. Knowing this, the booking_id field data are
counted using COUNT (). While using COUNT () the name of the field displaying the total
count gets renamed automatically. In order to give it a proper name, AS keyword is used.
After writing COUNT and then the field name within the brackets, AS keyword is written
and then the new name is written which the user wants as output field name. An example is
shown below with the solution SQL syntax.
SELECT Count(booking_id) AS TotalBookings
FROM Chandra_Bookings;
This will display the total number of recorded bookings in the Chandra_Bookings table with
remaining the output field name as TotalBookings. The screenshot of the above query and the
result output are as follows:
Fig5: Solution c SQL query screenshot
Fig6: Solution c SQL output result screenshot

6DATABASE CREATION, POPULATION AND SQL QUERIES
Reference
[1] Zygiaris S, Zygiaris S. Structured Query Language (SQL): Introducing Queries',
Database Management Systems.
[2] Ahmed N, Ahamed S, Rafiq JI, Rahim S. Data processing in Hive vs. SQL server: A
comparative analysis in the query performance. In2017 IEEE 3rd International
Conference on Engineering Technologies and Social Sciences (ICETSS) 2017 Aug 7
(pp. 1-5). IEEE.