Home Education What are the Queries and Subquery in MySQL Database?

What are the Queries and Subquery in MySQL Database?

Direct Switch to Contents

  1. What is a Query?
  2. Differentiation amongst the DROP, DELETE AND TRUNCATE
  3. What is SubQuery?
  4. Conclusion
  5. FAQS

What is a Query?

The query is a question or requesting to the database and Database table for the data or information.
In other words, Query is just Command statements that take an action or database and its table also.
Here we will first start with the SELECT Command Statement QUERY.


Select Commands

Now we will Represent the Table with the help of the SELECT command query as an output of the Screen.
Syntax 1: Select * from ;


Here ‘*’ is used for all the Columns or fields from the table. We can see how basic general English is used in this Query.
In the previous blog we have inserted the data in the table. Now we will use this select command query to represent the data on the screen.
Syntax: Select * from movies;

select command

Here we can see the data from the table movies from the Bollywood database that we had created in the previous blog.

Syntax 1: Select from SELECT Movie_name, Actor_name from Movies;

Select Column

Here we select the specific field from the table, so only those fields are showing here.

A CLAUSE WHERE:

Before going to the UPDATE command lets understand this first.
Where clause is used to extract only those records to the table whose condition is matched to it. We can say that it is just to filter records.

Taking the syntax of WHERE clause

> SELECT
FROM table_name
WHERE condition;

Taking an example of WHERE clause. Suppose we need to find only the record of BARFI movie from the Movie Table.
SO here the Query:

>Select * from the movie where movie_name = ‘BARFI’;

Where clause

Here the only one record that’s holding the movie name BARFI.

Update Command:

UPDATE command just uses to modify the existing records in the table.

Syntax: Update <Table Name> set <column name>=<New Value> where <column name> = <Existing value>;

If we need any changes to the record of the table MOVIES such as the age of the Salman khan to 50, then
>Update movies set Actor_age=50 where actor_name=’ Salman Khan’;

UPDATE command

Here we can see those changes.

DELETE Command:

Delete Command is used to delete the existing record from the table.
Syntax: DELETE FROM <table_name> WHERE condition;

If where the condition is not used in the syntax then the whole table will be deleted. Here the Syntax:
DELETE FROM <table_name>;

Alter Command Statements:

1. Adding a column in the Existing table Movies.
Here the Syntax:
ALTER TABLE <table_name>
ADD <column_name datatype>;

By default column will be added to the last. We can use keywords like First, Last, After, Before for the positioning of the field in the table.

>ALTER TABLE Movies
ADD SL_No int First;

ALTER TABLE

Here we can see a new field SL_no but its values are by default null. I will explain the Null Values in the next Blog. But we can update these null values to some integer values by Update command statements query.

2. Dropping a column from the table
SYNTAX: ALTER TABLE <table_name>
DROP COLUMN <column_name>;

>ALTER TABLE movies
DROP COLUMN SL_no.;

DROP COLUMN

Here we have deleted SL_no. Field.

3. Renaming the Table name
Syntax: Alter Table <Table Name> rename to <New Table  Name>;
Here we will change the table Movies name to Films.

>Alter Table Movies rename to Films;

Alter Table 2

Truncate Command:
The truncate command is a Data Definition Language(DDL) which used to delete all the records from the table and also all the space will be deallocated. It can not be rolled back.
Syntax: Truncate Table <Table Name>;
Example-
>Truncate table films;
All the records will be deleted with this command and space will be deallocated.

Differentiation amongst the DROP, DELETE AND TRUNCATE

DELETE is a DML command whereas Drop and Truncate are DDL commands.
In DELETE Command, it deletes each rows one by one from the table depending upon WHERE condition, and if there is no WHERE condition then it deletes all rows but the structure is still there. Here deleted data can be rollback.
But In DROP and TRUNCATE, these both delete all structures of the table. TRUNCATE delete all data records at once and deallocate the space whereas DROP commands delete complete Tables and Databases also.

Question: Which Command is Faster DELETE or Truncate?
The TRUNCATE command is much faster than the DELETE command because the DELETE command puts a condition for each record while deleting the rows. Whereas, TRUNCATE command locks the table, not rows, and removes all rows at once.

What is SubQuery?

Subquery is a query that is inside another query. In another and simple words we can say that nested queries are called Subqueries.
Queries inside queries are Subqueries.
The subquery can be nested inside the various command statements like SELECT, INSERT, UPDATE, or DELETE or inside another subquery.
Subqueries are usually added within the WHERE Clause of another SELECT command statements.
To understand the subqueries we need to learn Operators, MySQL Functions, Filters, etc. We will get it on later blogs. For now, just have the Basic of Subqueries and How it gets executed?
Taking a simple Example of Subquery:
>INSERT INTO[ (column1 [, column2 ]) ]
SELECT [*|column1 [,column2]
FROM table1 [,table2]
[WHERE VALUE OPERATOR];

Type of Subqueries:

  1. Single-row subquery
  2. Multiple row subquery :
  3. Multiple column subqueries
  4. Correlated subqueries
  5. Nested subqueries
  • Single-row subquery: It returns zero or one row to the query.
  • Multiple row subquery: It returns one or more rows to the query.
  • Multiple column subqueries: It returns one or more columns to the query.
  • Correlated subqueries: It references one or more columns in the outer MySQL command statement. The subquery is related to the outer MySQL command statements.
  • Nested subqueries: These subqueries are placed within another subquery. SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query. The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

Let us understand subquery, We have a Film Table in our database

Movie NameMovie YearActor NameActor age
Barfi2012Rabir Kapoor37
Biwi no. 11999Salman Khan54
2 States2014Arjun Kapoor34
Fan2016Shahrukh Khan54
Kabir Singh2019shahid kapoor39
Dabangg 32019Salman Khan54

Herewith the help of this table we will write a subquery.
>Select Movie_name, Movie_year from Films
WHERE Actor_age IN
(SELECT Actor_age from Flims where Movie_year=2019);

SUBQUERIES

Note: Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.

There is no general syntax; subqueries are regular queries placed inside the parenthesis.
Subqueries can be used in different ways and at different locations inside a query. Here is a subquery with the IN operator.

SELECT column-names
FROM table-name1
WHERE value IN (SELECT column-name
FROM table-name2
WHERE condition)

Conclusion

In Conclusion, so far we have learned how to write and execute the different queries and also the subqueries with different commands like Select, Update, Atler, Delete, etc. SubQuery is an alternate way of returning data from the database.

FAQs

1. What are the examples of subquery ?

It can be placed in a number of SQL clauses: WHERE , HAVING n FROM clause.
THEY can be used with select , update , insert , delete, statement along with expression operator.

2. How do we run a query?

Locate the query in the nevigation plane .
Double click the query you want to run. Click the query you want to run , then press ENTER

Next Blog | What are Keys in MySQL? Overview And Understanding

Previous Blog | What are DML Commands and DDL Commands in SQL?

RELATED ARTICLES

How an Emergency Line of Credit Can Aid Your Financial Situation?

Whenever there is a financial emergency, people want quick funds to deal with the emergency. In order to cater to this, banks...

Things That Are Harming Your Credit Score

A credit score is a vital parameter to getting the best loan offers. A score of 630 or above is good enough...

The 10 Most Common Mistakes Made When Applying for a Business Loan

A business loan can be extremely useful for your business venture. Whether you are looking to obtain a working capital loan or...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

How an Emergency Line of Credit Can Aid Your Financial Situation?

Whenever there is a financial emergency, people want quick funds to deal with the emergency. In order to cater to this, banks...

Things That Are Harming Your Credit Score

A credit score is a vital parameter to getting the best loan offers. A score of 630 or above is good enough...

The 10 Most Common Mistakes Made When Applying for a Business Loan

A business loan can be extremely useful for your business venture. Whether you are looking to obtain a working capital loan or...

Top Trading Techniques & Strategies Traders Should Know

There are a number of effective trading tactics you will come across when trading on the financial markets...

Recent Comments