Direct Switch to Contents
- What is a Query?
- Differentiation amongst the DROP, DELETE AND TRUNCATE
- What is SubQuery?
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.
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;
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;
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
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’;
Here the only one record that’s holding the movie name BARFI.
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’;
Here we can see those changes.
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;
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.;
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;
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>;
>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:
- Single-row subquery
- Multiple row subquery :
- Multiple column subqueries
- Correlated subqueries
- 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 Name||Movie Year||Actor Name||Actor age|
|Biwi no. 1||1999||Salman Khan||54|
|2 States||2014||Arjun Kapoor||34|
|Kabir Singh||2019||shahid kapoor||39|
|Dabangg 3||2019||Salman Khan||54|
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);
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.
WHERE value IN (SELECT column-name
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.
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.
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
Previous Blog | What are DML Commands and DDL Commands in SQL?