Deadlocks In DBMS

In a database management system (DBMS), a deadlock occurs when two or more transactions
are waiting for each other to release resources, such as locks on database objects, that they
need to complete their operations. As a result, none of the transactions can proceed, leading
to a situation where they are stuck or “deadlocked.”

Deadlocks can happen in multi-user environments when two or more transactions are running
concurrently and try to access the same data in a different order. When this happens, one
transaction may hold a lock on a resource that another transaction needs, while the second
transaction may hold a lock on a resource that the first transaction needs. Both transactions
are then blocked, waiting for the other to release the resource they need.

DBMSs often use various techniques to detect and resolve deadlocks automatically. These
techniques include timeout mechanisms, where a transaction is forced to release its locks
after a certain period of time, and deadlock detection algorithms, which periodically scan the
transaction log for deadlock cycles and then choose a transaction to abort to resolve the
deadlock.

It is also possible to prevent deadlocks by careful design of transactions, such as always

acquiring locks in the same order or releasing locks as soon as possible. Proper design of the
database schema and application can also help to minimize the likelihood of deadlocks

In a database, a deadlock is an unwanted situation in which two or more transactions are

waiting indefinitely for one another to give up locks. Deadlock is said to be one of the most
feared complications in DBMS as it brings the whole system to a Halt.
Example – let us understand the concept of Deadlock with an example :
Suppose, Transaction T1 holds a lock on some rows in the Students table and needs to
update some rows in the Grades table. Simultaneously, Transaction T2 holds locks on those
very rows (Which T1 needs to update) in the Grades table but needs to update the rows in the
Student table held by Transaction T1.

Now, the main problem arises. Transaction T1 will wait for transaction T2 to give up the
lock, and similarly, transaction T2 will wait for transaction T1 to give up the lock. As a
consequence, All activity comes to a halt and remains at a standstill forever unless the DBMS
detects the deadlock and aborts one of the transactions.
Deadlock in DBMS

Deadlock Avoidance: When a database is stuck in a deadlock, It is always better to avoid the
deadlock rather than restarting or aborting the database. The deadlock avoidance method is
suitable for smaller databases whereas the deadlock prevention method is suitable for larger
databases.

One method of avoiding deadlock is using application-consistent logic. In the above-given

example, Transactions that access Students and Grades should always access the tables in the
same order. In this way, in the scenario described above, Transaction T1 simply waits for
transaction T2 to release the lock on Grades before it begins. When transaction T2 releases
the lock, Transaction T1 can proceed freely.
Another method for avoiding deadlock is to apply both the row-level locking mechanism and
the READ COMMITTED isolation level. However, It does not guarantee to remove
deadlocks completely.

Deadlock Detection: When a transaction waits indefinitely to obtain a lock, The database
management system should detect whether the transaction is involved in a deadlock or not.

Wait-for-graph is one of the methods for detecting the deadlock situation. This method is
suitable for smaller databases. In this method, a graph is drawn based on the transaction and
its lock on the resource. If the graph created has a closed loop or a cycle, then there is a
deadlock.
For the above-mentioned scenario, the Wait-For graph is drawn below:
Deadlock prevention: For a large database, the deadlock prevention method is suitable. A
deadlock can be prevented if the resources are allocated in such a way that a deadlock never
occurs. The DBMS analyzes the operations whether they can create a deadlock situation or
not, If they do, that transaction is never allowed to be executed.

Deadlock prevention mechanism proposes two schemes:

 Wait-Die Scheme: In this scheme, If a transaction requests a resource that is locked

by another transaction, then the DBMS simply checks the timestamp of both
transactions and allows the older transaction to wait until the resource is available for
execution.
Suppose, there are two transactions T1 and T2, and Let the timestamp of any
transaction T be TS (T). Now, If there is a lock on T2 by some other transaction and
T1 is requesting resources held by T2, then DBMS performs the following actions:

Checks if TS (T1) < TS (T2) – if T1 is the older transaction and T2 has held some
resource, then it allows T1 to wait until resource is available for execution. That
means if a younger transaction has locked some resource and an older transaction is
waiting for it, then an older transaction is allowed to wait for it till it is available. If T1
is an older transaction and has held some resource with it and if T2 is waiting for it,
then T2 is killed and restarted later with random delay but with the same timestamp.
i.e. if the older transaction has held some resource and the younger transaction waits
for the resource, then the younger transaction is killed and restarted with a very
minute delay with the same timestamp.
This scheme allows the older transaction to wait but kills the younger one.
  Wound Wait Scheme: In this scheme, if an older transaction requests for a resource
held by a younger transaction, then an older transaction forces a younger transaction
to kill the transaction and release the resource. The younger transaction is restarted
with a minute delay but with the same timestamp. If the younger transaction is
requesting a resource that is held by an older one, then the younger transaction is
asked to wait till the older one releases it.

The following table lists the differences between Wait – Die and Wound -Wait scheme
prevention schemes:

Wait – Die Wound -Wait

It is based on a non-preemptive technique. It is based on a preemptive technique.
In this, older transactions must wait for the In this, older transactions never wait for
younger one to release its data items. younger transactions.
The number of aborts and rollbacks is higher in In this, the number of aborts and rollback
these techniques. is lesser.

Applications:

Delayed Transactions: Deadlocks can cause transactions to be delayed, as the resources they
need are being held by other transactions. This can lead to slower response times and longer
wait times for users.

Lost Transactions: In some cases, deadlocks can cause transactions to be lost or aborted,
which can result in data inconsistencies or other issues.

Reduced Concurrency: Deadlocks can reduce the level of concurrency in the system, as
transactions are blocked waiting for resources to become available. This can lead to slower
transaction processing and reduced overall throughput.

Increased Resource Usage: Deadlocks can result in increased resource usage, as

transactions that are blocked waiting for resources to become available continue to consume
system resources. This can lead to performance degradation and increased resource
contention.

Reduced User Satisfaction: Deadlocks can lead to a perception of poor system performance
and can reduce user satisfaction with the application. This can have a negative impact on user
adoption and retention.

Features of deadlock in a DBMS:

Mutual Exclusion: Each resource can be held by only one transaction at a time, and other
transactions must wait for it to be released.

Hold and Wait: Transactions can request resources while holding on to resources already
allocated to them.

No Preemption: Resources cannot be taken away from a transaction forcibly, and the
transaction must release them voluntarily.
Circular Wait: Transactions are waiting for resources in a circular chain, where each
transaction is waiting for a resource held by the next transaction in the chain.

Indefinite Blocking: Transactions are blocked indefinitely, waiting for resources to become
available, and no transaction can proceed.

System Stagnation: Deadlock leads to system stagnation, where no transaction can proceed,
and the system is unable to make any progress.

Inconsistent Data: Deadlock can lead to inconsistent data if transactions are unable to
complete and leave the database in an intermediate state.

Difficult to Detect and Resolve: Deadlock can be difficult to detect and resolve, as it may
involve multiple transactions, resources, and dependencies.

Disadvantages:

System downtime: Deadlock can cause system downtime, which can result in loss of
productivity and revenue for businesses that rely on the DBMS.

Resource waste: When transactions are waiting for resources, these resources are not being
used, leading to wasted resources and decreased system efficiency.

Reduced concurrency: Deadlock can lead to a decrease in system concurrency, which can
result in slower transaction processing and reduced throughput.

Complex resolution: Resolving deadlock can be a complex and time-consuming process,

requiring system administrators to intervene and manually resolve the deadlock.

Increased system overhead: The mechanisms used to detect and resolve deadlock, such as
timeouts and rollbacks, can increase system overhead, leading to decreased performance.

Create Table

The CREATE TABLE statement is used to create a new table in a database. In that table, if you want to
add multiple columns, use the below syntax.

Syntax

1. CREATE TABLE table_name (

2. column1 datatype,

3. column2 datatype,
4. column3 datatype,
 5. ....
6. );

The column parameters specify the names of the columns of the table.

The data type parameter specifies the type of data the column can hold (e.g. varchar, integer, date,
etc.).

Create Table Example

1. CREATE TABLE Employee(

2. EmpId int,

3. LastName varchar(255),
4. FirstName varchar(255),

5. Address varchar(255),
6. City varchar(255)

7. );

The EmpId column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters
and the maximum length for these fields is 255 characters.

Insert Value in this Table

The INSERT INTO statement is used to insert new records in a table.

It is possible to write the INSERT INTO statement in two ways.

Syntax

The first way specifies both the column names and the values to be inserted.

If you are adding values for all the columns of the table, then no need to specify the column names
in the SQL query. However, make sure that the order of the values is in the same order as the
columns in the table.
 1. INSERT INTO table_name (column1, column2, column3, ...)
2. VALUES (value1, value2, value3, ...);

3.
4. '2nd way
5. INSERT INTO table_name

6. VALUES (value1, value2, value3, ...);

Example

Insert value in a 1st way. The column names are used here

1. INSERT INTO Employee (EmpId,LastName,FirstName,ADDRESS,City)

2. VALUES (1, 'XYZ', 'ABC', 'India', 'Mumbai' );
3. INSERT INTO Employee (EmpId,LastName,FirstName,ADDRESS,City)
4. VALUES (2, 'X', 'A', 'India', 'Pune' );

Insert value in a 2nd way.

1. INSERT INTO Employee

2. VALUES (3, 'XYZ', 'ABC', 'India', 'Mumbai' );

Select Statment in SQL

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

1. SELECT column1, column2, ...

2. FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select from the data. If you
want to select all the fields available in the table, use the following syntax:

1. SELECT * FROM table_name;

If the above query is executed, then all record is displayed.

Example

1. Select EmpId, LastName from Employee;

2.

3. Select * from Employee;

Update Table

The UPDATE statement is used to modify the existing records in a table.

Syntax

1. UPDATE table_name
2. SET column1 = value1, column2 = value2, ...

3. WHERE condition;

Example

1. UPDATE Employee
2. SET FirstName= 'KS', City= 'Pune'

3. WHERE EmpId= 1;

If the above query is executed then for EmpId= 1, "Firstname" and "City" column data will be
updated.

Update Multiple Rows

It is the WHERE clause that determines how many records will be updated.

1. UPDATE Employee
2. SET City='Pune'

Delete Statment in SQL

The DELETE statement is used to delete existing records in a table for a particular Record.

Syntax

1. DELETE FROM table_name WHERE condition;

Example

1. DELETE FROM Employee WHERE EmpId=1;

In Employee table EmpId = 1 record gets deleted.

Structured Query Language (SQL) databases can store and manage a lot of data across
numerous tables. With large data sets, it’s important to understand how to sort data,
especially for analyzing result sets or organizing data for reports or external communications.

Two common statements in SQL that help with sorting your data are GROUP BY and ORDER
BY. A GROUP BY statement sorts data by grouping it based on column(s) you specify in the
query and is used with aggregate functions. An ORDER BY allows you to organize result sets
alphabetically or numerically and in ascending or descending order.

In this tutorial, you will sort query results in SQL using the GROUP BY and ORDER BY
statements. You’ll also practice implementing aggregate functions and the WHERE clause in
your queries to sort the results even further.

Prerequisites

To follow this guide, you will need a computer running some type of relational database
management system (RDBMS) that uses SQL. The instructions and examples in this tutorial
were validated using the following environment:

 A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and
firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
 MySQL installed and secured on the server. Follow our How To Install MySQL on Ubuntu
20.04 guide to set this up. This guide assumes you’ve also set up a non-root MySQL user, as
outlined in Step 3 of this guide.

Note: Please note that many relational database management systems use their own unique
implementations of SQL. Although the commands outlined in this tutorial will work on most
RDBMSs, the exact syntax or output may differ if you test them on a system other than
MySQL.

To practice sorting data results in this tutorial, you’ll need a database and table loaded with
sample data. If you do not have one ready to insert, you can read the following Connecting
to MySQL and Setting up a Sample Database section to learn how to create a database and
table. This tutorial will refer to this sample database and table throughout.

Connecting to MySQL and Setting up a Sample Database

If your SQL database runs on a remote server, SSH into your server from your local machine:

1. ssh sammy@your_server_ip
2.

Next, open the MySQL prompt, replacing sammy with your MySQL user account
information:

1. mysql -u sammy -p
2.
Create a database named movieDB:

1. CREATE DATABASE movieDB;

2.

If the database was created successfully, you’ll receive the following output:

Output
Query OK, 1 row affected (0.01 sec)

To select the movieDB database run the following USE statement:

1. USE movieDB;
2.

Output
Database changed

After selecting the database, create a table within it. For this tutorial’s example, we’ll create a
table that stores information about a local movie theater’s showings. This table will hold the
following seven columns:

 theater_id: stores values of the int data type for each theater’s showing rooms, and will
serve as the table’s primary key, meaning each value in this column will function as a unique
identifier for its respective row.
 date: uses the DATE data type to store the specific date by the year, month, and day a movie
was shown. This data type adheres to the following parameters: four digits for the year, and
a maximum of two digits for the month and day (YYYY-MM-DD).
 time: represents the movie’s scheduled showing with the TIME data type by hours, minutes,
and seconds (HH:MM:SS).
 movie_name: stores the movie’s name using the varchar data type with a maximum of 40
characters.
 movie_genre: uses the varchar data type with a maximum of 30 characters, to hold
information on each movie’s respective genre.
 guest_total: shows the total number of guests that attended a movie showing with the
int data type.
 ticket_cost: uses the decimal data type, with a precision of four and a scale of one,
meaning values in this column can have four digits, and two digits to the right of the decimal
point. This column represents the ticket cost for the specific movie showing.

Create a table named movie_theater that contains each of these columns by running the
following CREATE TABLE command:

1. CREATE TABLE movie_theater (

2.
3. theater_id int,
4.
5. date DATE,
6.
7. time TIME,
 8.
9. movie_name varchar(40),
10.
11. movie_genre varchar(30),
12.
13. guest_total int,
14.
15. ticket_cost decimal(4,2),
16.
17. PRIMARY KEY (theater_id)
18.
19. );
20.

Next, insert some sample data into the empty table:

1. INSERT INTO movie_theater

2.
3. (theater_id, date, time, movie_name, movie_genre, guest_total,
ticket_cost)
4.
5. VALUES
6.
7. (1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131,
18.00),
8.
9. (2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90,
18.00),
10.
11. (3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
12.
13. (4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83,
18.00),
14.
15. (5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112,
8.00),
16.
17. (6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama',
137, 8.00),
18.
19. (7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
20.
21. (8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142,
8.00),
22.
23. (9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150,
13.00),
24.
25. (10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama',
118, 13.00),
26.
27. (11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
28.
29. (12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130,
13.00);
30.
Output
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

Once you’ve inserted the data, you’re ready to start sorting query results in SQL.

Using GROUP BY

The function of a GROUP BY statement is to group records with shared values. A GROUP BY
statement is always used with an aggregate function in a query. As you may recall, an
aggregate function summarizes information and returns a single result. For instance, you can
query for the total count or sum of a column and this will produce a single value in your
result. With a GROUP BY clause, you can implement the aggregate function to get one result
value for each group you desire.

GROUP BY is useful for returning multiple desired results sorted by your specified group(s),
rather than solely one column. Additionally, GROUP BY must always come after the FROM
statement and the WHERE clause, if you choose to use one. Here’s an example of how a query
with a GROUP BY and aggregate function is structured:

GROUP BY syntax
SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

To illustrate how you can use GROUP BY statements, say you’re leading the campaign for
several movie releases, and you want to evaluate the success of your marketing efforts. You
ask a local theater to share the data they collected from guests on Friday and Saturday. Start
by reviewing the data by running SELECT and the * symbol to select “every column” from the
movie_theater table:

1. SELECT * FROM movie_theater;

2.

Output
+------------+------------+----------+-------------------------
+-------------+-------------+-------------+
| theater_id | date | time | movie_name |
movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------
+-------------+-------------+-------------+
| 1 | 2022-05-27 | 10:00:00 | Top Gun Maverick | Action
| 131 | 18.00 |
| 2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama
| 90 | 18.00 |
| 3 | 2022-05-27 | 10:00:00 | Men | Horror
| 100 | 18.00 |
| 4 | 2022-05-27 | 10:00:00 | The Bad Guys | Animation
| 83 | 18.00 |
| 5 | 2022-05-28 | 09:00:00 | Top Gun Maverick | Action
| 112 | 8.00 |
| 6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama
| 137 | 8.00 |
| 7 | 2022-05-28 | 09:00:00 | Men | Horror
| 25 | 8.00 |
| 8 | 2022-05-28 | 09:00:00 | The Bad Guys | Animation
| 142 | 8.00 |
| 9 | 2022-05-28 | 05:00:00 | Top Gun Maverick | Action
| 150 | 13.00 |
| 10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama
| 118 | 13.00 |
| 11 | 2022-05-28 | 05:00:00 | Men | Horror
| 88 | 13.00 |
| 12 | 2022-05-28 | 05:00:00 | The Bad Guys | Animation
| 130 | 13.00 |
+------------+------------+----------+-------------------------
+-------------+-------------+-------------+
12 rows in set (0.00 sec)

While this data is helpful, you want to perform a deeper assessment and sort the results for
some specific columns.

Since you worked on movies across a few different genres, you’re interested in knowing how
well-received they were by movie-goers. Specifically, you want to know the average amount
of people that watched each movie genre. Use SELECT to retrieve the various types of movies
from the movie_genre column. Then apply the aggregate function AVG on the guest_total
column, use AS to create an alias for a column called average, and include the GROUP BY
statement to group results by movie_genre. Grouping them this way will provide you with
the average results for each movie genre:

1. SELECT movie_genre, AVG(guest_total) AS average

2.
3. FROM movie_theater
4.
5. GROUP BY movie_genre;
6.

Output
+-------------+----------+
| movie_genre | average |
+-------------+----------+
| Action | 131.0000 |
| Drama | 115.0000 |
| Horror | 71.0000 |
| Animation | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

This output provides the four averages for each genre within the movie_genre group. Based
on this information, Action movies attracted the highest average number of guests per
showing.

Next, let’s say you want to measure the theater’s revenues over two separate days. The
following query returns values from the date column, as well as values returned by the SUM
aggregate function. Specifically, the aggregate function SUM will enclose a mathematical
equation in parentheses to multiply (using the * operator) the number of total guests by the
cost of a ticket, represented as: SUM(guest_total * ticket_cost). This query includes the
AS clause to provide the alias total_revenue for the column returned by the aggregate
function. Then complete the query with the GROUP BY statement to group the query results by
the date column:

1. SELECT date, SUM(guest_total * ticket_cost)

2.
3. AS total_revenue
4.
5. FROM movie_theater
6.
7. GROUP BY date;
8.

Output
+------------+---------------+
| date | total_revenue |
+------------+---------------+
| 2022-05-27 | 7272.00 |
| 2022-05-28 | 9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Since you used GROUP BY to group the date column, your output provides the results for the
total revenue in ticket sales for each day, in this case, $7,272 for Friday, May 27, and $9,646
for Saturday, May, 28.

Now imagine you want to focus on and analyze one movie: The Bad Guys. In this scenario,
you want to figure out how timing and price points impact a family’s choice to watch an
animated film. For this query use the aggregate function MAX to retrieve the maximum
ticket_cost, making sure to include AS to create the alias for the price_data column.
After, use the WHERE clause to narrow down the results by movie_name to solely “The Bad
Guys”, and use AND to also determine the most popular movie times based on guest_total
numbers that were more than 100 with the comparison operator >. Then complete the query
with the GROUP BY statement and group it by time:

1. SELECT time, MAX(ticket_cost) AS price_data

2.
3. FROM movie_theater
4.
5. WHERE movie_name = "The Bad Guys"
6.
7. AND guest_total > 100
8.
9. GROUP BY time;
10.

Output
+----------+------------+
| time | price_data |
+----------+------------+
| 09:00:00 | 8.00 |
| 05:00:00 | 13.00 |
+----------+------------+
2 rows in set (0.00 sec)
According to this output, more guests attended The Bad Guys movie at the early matinee time
of 9:00 am, which had the more affordable price point of $8.00 per ticket. However, these
results also show movie guests paid the higher ticket price of $13.00 at 5:00 pm, suggesting
that families prefer showings that aren’t too late in the day and will pay a bit more for a
ticket. This seems to be a fair assessment when compared to the 10:00 pm time when The
Bad Guys movie only had 83 guests and the price per ticket was $18.00. This can be helpful
information to provide the movie theater manager with evidence that opening more matinee
and early evening time slots can increase the attendance for families that are making a choice
based on a preferred time and price point.

Please note that even though GROUP BY is almost always used with an aggregate function,
there can be exceptions, although unlikely. However, if you did want to group your results
without an aggregate function, you can use the DISTINCT statement to achieve the same
result. A DISTINCT clause removes any duplicates in a result set by returning the unique
values in the column, and it can only be used with a SELECT statement. For example, if you
wanted to group all the movies together by name, you could do so with the following query:

1. SELECT DISTINCT movie_name FROM movie_theater;

2.

Output
+-------------------------+
| movie_name |
+-------------------------+
| Top Gun Maverick |
| Downton Abbey A New Era |
| Men |
| The Bad Guys |
+-------------------------+
4 rows in set (0.00 sec)

As you recall from viewing all the data in the table, there were duplicates of the movie names
since there were multiple showings. Therefore, DISTINCT removed those duplicates and
effectively grouped the unique values under the single column movie_name. This is
effectively identical to the following query, which includes a GROUP BY statement:

1. SELECT movie_name FROM movie_theater GROUP BY movie_name;

2.

Now that you’ve practiced using GROUP BY with aggregate functions, next you’ll learn how to
sort your query results with the ORDER BY statement.

USING ORDER BY

The function of the ORDER BY statement is to sort results in ascending or descending order
based on the column(s) you specify in the query. Depending on the data type stored by the
column you specify after it, ORDER BY will organize them in alphabetical or numerical order.
By default, ORDER BY will sort results in ascending order; if you prefer descending order,
however, you have to include the keyword DESC in your query. You can also use the ORDER
BY statement with GROUP BY, but it must come after in order to function properly. Similar to
GROUP BY, ORDER BY must also come after the FROM statement and WHERE clause. The general
syntax for using ORDER BY is as follows:

ORDER BY syntax
SELECT column_1, column_2 FROM table ORDER BY column_1;

Let’s continue with the sample data for the movie theater and practice sorting results with
ORDER BY. Begin with the following query which retrieves values from the guest_total
column and organizes those numerical values with an ORDER BY statement:

1. SELECT guest_total FROM movie_theater

2.
3. ORDER BY guest_total;
4.

Output
+-------------+
| guest_total |
+-------------+
| 25 |
| 83 |
| 88 |
| 90 |
| 100 |
| 112 |
| 118 |
| 130 |
| 131 |
| 137 |
| 142 |
| 150 |
+-------------+
12 rows in set (0.00 sec)

Since your query specified a column with numerical values, the ORDER BY statement
organized the results by numerical and ascending order, starting with 25 under the
guest_total column.

If you preferred to order the column in descending order, you would add the DESC keyword at
the end of the query. Additionally, if you wanted to order the data by the character values
under movie_name, you would specify that in your query. Let’s perform that type of query
using ORDER BY to order the movie_name column with character values in descending order.
Sort the results even further by including a WHERE clause to retrieve the data on movies
showing at 10:00 pm from the time column:

1. SELECT movie_name FROM movie_theater

2.
3. WHERE time = '10:00:00'
4.
5. ORDER BY movie_name DESC;
6.
Output
+-------------------------+
| movie_name |
+-------------------------+
| Top Gun Maverick |
| The Bad Guys |
| Men |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

This result set lists the four different movie showings at 10:00 pm in descending alphabetical
order, starting from Top Gun Maverick to Downtown Abbey A New Era.

For this next query, combine the ORDER BY and GROUP BY statements with the aggregate
function SUM to generate results on the total revenue received for each movie. However, let’s
say the movie theater miscounted the total guests and forgot to include special parties that
had pre-purchased and reserved tickets for a group of 12 people at each showing.

In this query use SUM and include the additional 12 guests at each movie showing by
implementing the operator for addition + and then adding 12 to the guest_total. Make sure
to enclose this in parenthesis. Then, multiply this total by the ticket_cost with the operator
*, and complete the mathematical equation by closing the parenthesis at the end. Add the AS
clause to create the alias for the new column titled total_revenue. Then, use GROUP BY to
group total_revenue results for each movie based on the data retrieved from the
movie_name column. Lastly, use ORDER BY to organize the results under the new column
total_revenue in ascending order:

1. SELECT movie_name, SUM((guest_total + 12) * ticket_cost)

2.
3. AS total_revenue
4.
5. FROM movie_theater
6.
7. GROUP BY movie_name
8.
9. ORDER BY total_revenue;
10.

Output
+-------------------------+---------------+
| movie_name | total_revenue |
+-------------------------+---------------+
| Men | 3612.00 |
| Downton Abbey A New Era | 4718.00 |
| The Bad Guys | 4788.00 |
| Top Gun Maverick | 5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

This result set tells us the total revenue for each movie with the additional 12 guest ticket
sales and organizes the total ticket sales in ascending order from lowest to highest. From this,
we learn that Top Gun Maverick received the most ticket sales, while Men received the least.
Meanwhile, The Bad Guys and Downton Abbey A New Era movies were very close in total
ticket sales.

In this section, you practiced various ways to implement the ORDER BY statement and how to
specify the order you prefer, such as ascending and descending orders for both character and
numerical data values. You also learned how to include the WHERE clause to narrow down
your results, and performed a query using both the GROUP BY and ORDER BY statements with
an aggregate function and mathematical equation.

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact,

1. DELETE FROM table_name;

2.

3. DELETE From Employee ;

When the above query is executed, only table Data gets deleted.