Trending December 2023 # Working Of Transactions With Examples # Suggested January 2024 # Top 19 Popular

You are reading the article Working Of Transactions With Examples updated in December 2023 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Working Of Transactions With Examples

Introduction to MongoDB Transactions

MongoDB transactions are provided in version 4.0 of Mongo DB where the transactions are supported even in Multi-document operations to maintain the ACID properties. The transactions are supported even across the sharded clusters and the replications of the documents in the Mongo database. There arise many situations where we need a transaction to maintain the atomicity in the operations where the process consists of multi-collection data or multi-document data which is supported by MongoDB transactions.

Start Your Free Data Science Course

In this article, we will have a look at the necessity of the transactions in MongoDB, the ACID properties of the transactions, and have a look at sample examples to demonstrate the use of Mongo DB transactions in your application.

MongoDB Transactions

There are multiple read and write operations being done from and to the Mongo DB database that needs a behavior where all those set of operations should be completed entirely or none of them should be reflected in the database. This integrity of a set of read and write operations is maintained by using transactions.

ACID properties of transactions

All the transactions in Mongo DB follow the properties of ACID which stand for Atomicity, Consistency, Isolation, and durability. It is important for exhibiting all these four characteristics inside your transaction for validation. Let us discuss one by one what all these four properties of ACID stand for –

Atomicity – By this property, we mean that all the operations that are present inside the transaction will together be treated as a single unit which means that the failure of even a single operation will lead to a rollback of all the previously executed operations of that transaction and marking the failure of the transaction. Inversely, if all the operations are executed successfully the transaction is also succeeded.

Consistency – By consistency in the database what we mean is that any changes in data will lead to the satisfaction of all the constraints applied on that column of a field in a database. For example, when a unique key is assigned to the document inside a collection then the field is always kept unique while maintaining its consistency.

Isolation is the property that helps in explaining the segregate execution of each transaction when multiple transactions are running in the system simultaneously.

Durability – Durability means the changes in the data being made in the database persist and are stored in the backing store as well which means is there is any kind of system failure arising then the data won’t be affected and will be stored and maintained properly.

Working of Transactions

We have to initiate a session in MongoDB with the help of the driver.

Further, you can make use of this session for executing all the operations that you wanted your transaction to carry out. This operation can be having the involvement of multiple documents, collections, and also across sharded clusters and all of them being ACID compliant.

Limitations of using transactions in MongoDB

There are many limitations while using the transactions in MongoDB which are as listed below –

Read operation cannot be performed on system collections.

Write operation cannot be done on non-existing collections or capped collections.

Indexes and collections cannot be modified or dropped in the transactions.

Example

Consider the following example of a transaction in Mongo DB.

The output of the execution of the above code snippet is as shown in the below image –

Further, we can decide if we want to commit the transaction or abort it by using the following statements –

sampleSession.abortTransaction()

Conclusion

The transactions can be single-document, multi-document, or even involving multiple clusters in it in the case of a distributed system. In Mongo DB the single document transactions are maintained internally. However, in the case of multi-document transactions, there is an extra overhead of managing these transactions. Transactions following the ACID properties make their database consistent, atomic, durable, and isolated. There are multiple methods using which we can initiate the session and abort or commit or transaction in Mongo DB.

Recommended Articles

This is a guide to MongoDB Transactions. Here we discuss the definition, Working of Transactions, examples with code implementation, limitations. You may also have a look at the following articles to learn more –

You're reading Working Of Transactions With Examples

Working Of Dateutil Module In Python With Examples

Introduction to Python dateutil

In this article, we will discuss an in-built Python module dateutil. In Python, dateutil is an extension module of the datetime module which is used for displaying the date and time. This dateutil module is used when we were in need to display timezone using the datetime module which does not support as it would be done in the time module. The dateutil has other different features that are provided in Python for computing relative deltas between two given date and datetime objects, another feature is this module is used for parsing the date and time in the string to date-time objects.

Start Your Free Software Development Course

Working of dateutil Module in Python

In this section, we will see how to use the dateutil module along with its features and its examples. The dateutil module is a powerful extension of the datetime module with different features and this is also an in-built module in Python which is related to date and time tasks. The dateutil module can be installed from PyPI which means we can download this module from PyPI and then install it as.

pip install python-dateutil

The above statement can be run in Python 3 and above versions if PyPI is not available with a small change in the above statement and it is as follows:

pip3 install python-dateutil

Now let us see the dateutil module and its features along with examples. Firstly let us explore the computation of relative deltas using the dateutil module. The relative delta is used when we want to display the date and time of the later years or month or day. In general, relativedelta type can be applied to the current date and time to replace with some specified date-time objects or to specify some later interval of time.

Examples of Python dateutil

Given below are the examples mentioned:

Example #1

Suppose how much time is left in years or months or days for the upcoming next festival or event we can print this using relativedelta.

Code:

from dateutil.relativedelta import * from dateutil.easter import * from dateutil.rrule import * from dateutil.parser import * from datetime import * print("program to demonstrate relative delta of dateutil module:") now = parse("Sun Jun 14 17:13:46 UTC 2023") today = now.date() print("Today is: %s" % today) year = rrule(YEARLY,dtstart=now,bymonth=8,bymonthday=15,byweekday=FR)[0].year print("Year with next Aug 15th on a Friday is: %s" % year) rdelta = relativedelta(easter(year), today) print("How far is the event day of that year: %s" % rdelta) print("And the event of that year is: %s" % (today+rdelta))

Output:

Example #2

Now let us see another feature of the dateutil module which is used to parse the format of the string in date and time objects. Let us demonstrate the parse() function in the below example.

Code:

from dateutil import parser print("Program to demonstrate parse() function of dateutil:") print("The parsed date and time of the given string is as follows:") a = 'Sun Jun 14 10:36:28 2023' print (parser.parse(a)) b = 'Friday, 25. September 2008 10:36AM' print (parser.parse(b)) c = '3 / 25 / 2023 10:36:28' print (parser.parse(c)) d = '9 / 15 / 2023' print (parser.parse(d)) e = '2010-09-2T10:36:28Z' print (parser.parse(e))

Output:

In the above program, we can see that we are importing a parser module which has parse() function to convert the given date and time format in different string formats into particular date-time objects.

Example #3

Code:

from dateutil.rrule import rrule, rruleset, MONTHLY rules = rruleset() rules.rrule(rrule(MONTHLY, bymonthday=1, count=3)) rules.rrule(rrule(MONTHLY, bymonthday=2, count=3)) for date in rules: print(date)

Output:

In the above program, we can see another feature using rule which is used to display the dates in a range that is mainly used for recurrence set generations. It is in general a module used for implementing the recurrence rule documentation including support for caching of results.

Conclusion

In this article, we conclude that the dateutil module in Python is an in-built module used for date and time-related tasks. In this article, we saw dateutil is an extension of the datetime module. This dateutil module is mainly used for parsing any string format to date-time object and is also used for displaying timezones which is not supported by datetime alone as a time module. In this article, we also saw the rule module uses the dateutil module to display the range or recurrence of rule documentation.

Recommended Articles

This is a guide to Python dateutil. Here we discuss the introduction and working of python dateutil along with examples respectively. You may also have a look at the following articles to learn more –

Attributes Of Matlab Pcolor() With Examples

Introduction to Matlab pcolor()

In MATLAB, pcolor() is the plotting function which is designed to represent the input matrix data in the form of an array of colored cells creating a pseudo color plot. The colored cell is termed as face. The plot gets created as x-y plane flat surface with x and y co-ordinates as vertices(corners) of faces. The limits for x and y co-ordinates are decided by the size of the input matrix.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

There are different syntaxes that can be used to implement pcolor() method based on the input arguments given to it. Such as:

Where C is the input matrix data

pcolor(C)

Where C is the input matrix data

pcolor(X,Y,C)

X– x-coordinates of the vertices

ax defines the axes targeted for the plot

To store the pseudo color plot as surface object

Attributes of Matlab pcolor()

Here is the table of Attributes:

Attribute Description

C Input matrix data

X,Y coordinates of the vertices

Ax axes targeted for the plot

Examples to Implement Matlab pcolor()

Here are the examples mentioned:

Example #1

create pseudo color plot for input matric Cmat.

Code:

pcolor(Cmat);

Output:

Explanation: The input matrix is of size 2X3 and the vertices of the plot is decided 2X3 as default data. The pseudo color plot also can be created in the reverse direction using axis function.

Example #2

Generate Hadamard square orthogonal matrix and create pseudo color plot for same,with the origin set in the upper left corner.

Code:

axis square

Output:

Explanation: The resultant graph is a 40X40 two-color map, represented in the reverse direction.

Example #3

Create pseudo color plot for the input matrix data Cmat within the defined x and y coordinates given by matrix inputs X and Y.

Code:

pcolor(Xcod,Ycod,Cmat)

Output:

Explanation: The resultant plot has the vertices as per the values given by X and Y matrices and data from Cmat is plotted within the limits. The data from the input matrix are represented by different color code in the plot.

Example #4

MATLAB supports tilling of plots using the functions like nexttile() and tiledlayout(). The below code is wriiten to create 1X2 chart layout and creating two different pseudo color plot for two different set of inputs and functions in each cell of the layout.

pcolor(Rx,RCmat)

Output:

Explantion: The resultant plots are arranged in single layout consisting of two tiles generated using the function nexttile().

Pcolor() with Surface Object

When pcolor() method is assigned to a variable, it returns a surface object. This return value can be used to customize the properties of the plot after its creation.

Example #1

The below code is developed create a pseudo color plot for input matrix and modify the edge color and line width after the plot is generated.

Code:

s.LineWidth = 5;

Output:

Explanation: The resultant pseudo color plot is assigned to variable ‘s’. The edge color and the line width of the plot is modified using ‘s’ value as shown in the output plot.

Example #2

The below code snippet is designed to create the pseudo color plot and modify the face color of the cells using interpolated coloring using surface object properties.

Code:

s.FaceColor = ‘interp’;

Output:

Explanation: The resultant pseudo color plot is assigned to surface object ‘s’. The color representation of the faces are modified due to modification applied to the surface object property ‘facecolor’ for the surface object ‘s’.

Semi Logarithm Plotting using pcolor() Example #1

The below MATLAB code is designed to create semi logarithmic pseudo color plot and to alter the appearance using surface object properties from its return value.

Code:

s = pcolor(X,YL,Cmat);

Output:

Explanation: The resultant output is surface plot where y-coordinate input is the logarithmic function of x-coordinate inputs. The pseudo color semi logarithmic plot generated from the pcolor() method can be stored as surface object and its properties can be altered as shown in the below example:

Example #2

Code:

set(gca,’YTick’,logvals);

Output:

Explanation: The plot from the above code is generated with modified y-tick values by altering the properties of the surface object ‘s’.

Parametric Plotting using pcolor()

Parametric functions are also supported by pcolor() method.

Example #1

The below MATLAB code is designed to generate pseudo color plot from the x-y co oridinates generated from parametric equations.

Code:

pcolor(fX,fY,Cmat);

Explanation: The resultant plot represents the input matrix data, generated from repmat() function within the x-y coordinate values that are defined by two parametric equations.

Note: The size of the x-y coordinate grid must match the size of the input matrix data C. Based on the value in input matrix, the color map array gets colored from its vertices up to face chúng tôi first row gets mapped by the smallest value in C whereas the last row by the largest one, in the color map array. In case of long input array/matrix, the performance of pcolor() method seems to be slower.

Recommended Articles

This is a guide to Matlab pcolor(). Here we discuss an introduction to Matlab pcolor() with appropriate syntax and respective examples for better understanding. You can also go through our other related articles to learn more –

Types Of Xml Parsers With Examples

Definition of XML Parsers How does XML Parsers Work?

Fig: XML parser Process

The Parser could be categorized as validating and non- validating

Validating Parser: It needs a Document type Declaration to parse and gives an error if the respective document doesn’t match with DTD and constraints.

Non-Validating: This Parser eliminates DTD and the parser checks for the well-formed document.

Types of XML Parsers with Examples

This section talks about various types of parsers used recently in parsing XML document. They are:

DOM Parser

SAX Parser

JDOM Parser

stAX Parser

Xpath Parser

The most important type is DOM and SAX which is explained detail in this article.

1. DOM Parser (Tree-Based)

Document Object Model is a W3C Standard and converts the XML document which is to parsed into a collection of objects and uses DOM API. It consists of a collection of nodes and is associated with other nodes in the tree. DOM is much easier to use as sorting and searching process is made chúng tôi DOM parser the content of the XML file is modified with Node and Node List. The Steps involved in Parsing with java:

Getting document builder objects

Taking XML document as input, parseit and return the class.

Getting values of the input id through attributes and sub-elements.

Display the results.

First is the XML file that generates the values which are going to be parse and java objects are constructed automatically.

Example

chúng tôi

Read.java

}

And here is the Output is shown for the XML File. Save XML and java file in the same folder during execution. In this article I have used java-jdk- 13.0.1 using command prompt. Save the respective folder in any drive and do the set path.

Output:

2. SAX Parser

SAX Is Simple API for XML and meant has Push Parseralso considered to be stream-oriented XML Parser. it is used in case of high- performance applications like where the XML file is too largeand comes with the community- based standard and requires less memory. The main task is to read the XML file and creates an event to do call functionor uses call back routines. The working of this parser is just like Event handler part of the java. it is necessary to register the handlers to parse the document for handing different events. SAX Parser uses three methods startElement() , endElement() , characters().

startElement(): Is used to identify the element, start element is identified.

endElement(): To stop the Supermarket tag in the example.

character(): Is used to identify the character in the node

The xml file is the same file used in DOM Parser chúng tôi and next step generate chúng tôi file

Example

Rsax.java

}

Output:

Conclusion

Therefore, we have discovered how to use XML parsers in Java with the powerful APIs in the applications.Also, we have seen the implementation of two parsers using java. When compared with DOM,sax parser uses arbitrary size to parse whereas DOM requires available memory to load the complete chúng tôi Parsers differs based on the performance.

Recommended Articles

This is a guide to XML Parsers. Here we also discuss how does xml parsers work? along with examples and its code implementation. You may also have a look at the following articles to learn more –

A Quick Glance Of Mysql Having With Examples

Introduction to MySQL having

In real-time applications, we often need to generate complex reports from the data saved in the database and retrieve the filtered records to display the report to the user. For this, your SQL query must be optimized and correct so that the application’s performance is not hampered even if a large amount of data is present in the tables. The SELECT query constructed for such reports needs to use the functionalities provided in MySQL.

Start Your Free Data Science Course

Having a clause is one such functionality that helps apply the expressions’ filters. These expressions can consist of a single column, multiple columns, or even conditions applied to the grouped aggregated data retrieved using the GROUP BY clause. In this article, we will learn about the syntax of the HAVING clause, its evaluation order while execution, and study some examples to get a grip on the usage of the HAVING clause in your queries for retrieval of data.

Syntax:

The following SELECT query shows the syntax and placement of the HAVING clause in it –

SELECT list_of_expressions FROM name_of_table WHERE restrictions_and_conditions GROUP BY expressions_for_grouping HAVING condition_or_filter_on_grouped_expressions;

In the above syntax –

list_of_expressions – The comma-separated list of the columns and the other expressions, such as aggregated values or manipulated values such as product, etc., that the query needs to retrieve from the table data.

name_of_table – The table’s name resides in your database and from where the summarized data needs to be retrieved for your use case.

restrictions_and_conditions – These are the conditions you can specify on the table columns that need to be fulfilled while retrieving the data.

expressions_for_grouping – The resultset retrieved from the query structure above the GROUP BY clause can be summarized and grouped based on certain expressions, including columns and aggregated values of columns as the expressions_for_grouping.

condition_or_filter_on_grouped_expressions – The WHERE clause only applies restrictions on the individual records or row of the column. We can use the HAVING clause to apply filters and restrictions and specify conditions on the grouped expressions of the resultset.

In the case of the HAVING clause, the restriction is applied to the grouped values retrieved instead of a single row of the table. When we do not use the GROUP BY clause in the SELECT query, the HAVING clause behaves in the same manner as that of the WHERE clause and applies restriction on individual row-level of the table as grouping expression is absent. If the filter condition specified in the HAVING clause evaluates to true, the grouped record is included in the final resultset based on the GROUP BY clause. Conversely, if the condition evaluates to false, the record is excluded.

Evaluation order:

The evaluation order followed by standard SQL is different than that of MySQL. Evaluation order determines which and when clause will be considered for execution when the SELECT query contains multiple clauses. The diagram below illustrates the order in which MySQL evaluates the clauses of the SELECT query.

Here, we observe that the evaluation of the HAVING clause takes place after the FROM, WHERE, SELECT, and GROUP BY clauses, but before the LIMIT and ORDER BY clauses. However, it is important to note that in the SQL standard, the evaluation order differs. In this case, the execution of the HAVING clause occurs after the GROUP BY clause and before the SELECT clause.

Example of MySQL having CREATE TABLE educba_articles ( id INTEGER AUTO_INCREMENT PRIMARY KEY, articlename varchar(10) NOT NULL, author varchar(10) NOT NULL, rate decimal(5,2) DEFAULT NULL, month varchar(10) NOT NULL, status varchar(10) NOT NULL, pages INTEGER DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

that gives the following output after execution –

Now, we will insert some records in it –

INSERT INTO `educba_articles` VALUES (1, 'sp', 'Payal', 125.65, 'May','Submitted' ,3), (2,'having', 'Vyankatesh', 326.22, 'June','Pending' ,2), (3,'something', 'Omprakash', 123.22, 'July','Approved' ,2), (4, 'anything','sakshi', 645.54 ,'May','Pending' ,3), (5, 'everything','prerna', 356.54 ,'June','Submitted' ,4), (6,'events', 'Vyankatesh', 326.22, 'June','Approved' ,2), (7,'group by', 'Omprakash', 123.22, 'July','Approved' ,2), (8, 'from','sakshi', 645.54, 'May','Pending' ,3), (9, 'where','prerna', 356.54 ,'June','Submitted' ,4), (10, 'limit ', 'Payal', 125.65, 'May','Submitted' ,3), (11, 'coalesce ', 'Payal', 125.65, 'May','Submitted' ,3), (12,'order by', 'Vyankatesh', 326.22, 'June','Approved' ,2), (13,'datatypes', 'Omprakash', 123.22, 'July','Approved' ,2), (14, 'varchar','sakshi', 645.54 ,'May','Pending' ,3), (15, 'integer','prerna', 356.54 ,'June','Approved' ,4), (16,'date', 'Vyankatesh', 326.22, 'June','Pending' ,2), (17,'now', 'Omprakash', 123.22, 'July','Approved' ,2), (18, 'curdate','sakshi', 645.54 ,'May','Pending' ,3), (19, 'not null','prerna', 356.54, 'June','Pending' ,4);

that provides the following result after execution –

SELECT author, MONTH, SUM(rate*pages) AS payment FROM educba_articles GROUP BY author, MONTH

that gives the following output –

Let’s consider another example where our goal is to retrieve the total payment that we need to pay to each author. In this case, the total payment for all months should exceed 5000. To achieve this, we need to group the data by author, calculate the payment by multiplying the rate and pages, and aggregate this value using the SUM() function to calculate the total payment for each author. Additionally, we should apply a restriction on the calculated value within the HAVING clause. Our query statement will look like follows –

SELECT author, SUM(rate*pages) AS payment FROM educba_articles GROUP BY author;

that gives the following output after execution –

Conclusion

HAVING clause can be used to apply restrictions and filters on the grouped expressions in complex queries that are generally used for reporting purposes using the SELECT query statement in MySQL.

Recommended Articles

We hope that this EDUCBA information on “MySQL having” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Mysql Select Statement With Examples

What is SELECT query in MySQL?

SELECT QUERY is used to fetch the data from the MySQL database. Databases store data for later retrieval. The purpose of MySQL Select is to return from the database tables, one or more rows that match a given criteria. Select query can be used in scripting language like PHP, Ruby, or you can execute it via the command prompt.

SQL SELECT statement syntax

It is the most frequently used SQL command and has the following general syntax

HERE

SELECT is the SQL keyword that lets the database know that you want to retrieve data.

FROM tableName is mandatory and must contain at least one table, multiple tables must be separated using commas or joined using the JOIN keyword.

WHERE condition is optional, it can be used to specify criteria in the result set returned from the query.

GROUP BY is used to put together records that have the same field values.

HAVING condition is used to specify criteria when working using the GROUP BY keyword.

ORDER BY is used to specify the sort order of the result set.

*

The Star symbol is used to select all the columns in table. An example of a simple SELECT statement looks like the one shown below.

SELECT * FROM `members`;

The above statement selects all the fields from the members table. The semi-colon is a statement terminate. It’s not mandatory but is considered a good practice to end your statements like that.

Practical examples

You can learn to import the .sql file into MySQL WorkBench

The Examples are performed on the following two tables

Table 1: members table

membership_ number full_names gender date_of_ birth physical_ address postal_ address contct_ number email

1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 [email protected]

2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL

3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345

4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL

Table 2: movies table

movie_id title director year_released category_id

1 Pirates of the Caribean 4 Rob Marshall 2011 1

2 Forgetting Sarah Marshal Nicholas Stoller 2008 2

3 X-Men NULL 2008 NULL

4 Code Name Black Edgar Jimz 2010 NULL

5 Daddy’s Little Girls NULL 2007 8

6 Angels and Demons NULL 2007 6

7 Davinci Code NULL 2007 6

9 Honey mooners John Schultz 2005 8

16 67% Guilty NULL 2012 NULL

Getting members listing

Let’s suppose that we want to get a list of all the registered library members from our database, we would use the script shown below to do that.

SELECT * FROM `members`;

Executing the above script in MySQL workbench produces the following results.

membership_ number full_names gender date_of_ birth physical_ address postal_ address contct_ number email

1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 [email protected]

2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL

3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345

4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL

Our above query has returned all the rows and columns from the members table.

Let’s say we are only interested in getting only the full_names, gender, physical_address and email fields only. The following script would help us to achieve this.

SELECT `full_names`,`gender`,`physical_address`, `email` FROM `members`;

Executing the above script in MySQL workbench produces the following results.

full_names gender physical_address email

Janet Jones Female First Street Plot No 4 [email protected]

Janet Smith Jones Female Melrose 123

Robert Phil Male 3rd Street 34

Gloria Williams Female 2nd Street 23 NULL

Getting movies listing

Remember in our above discussion that we mention expressions been used in SELECT statements. Let’s say we want to get a list of movie from our database. We want to have the movie title and the name of the movie director in one field. The name of the movie director should be in brackets. We also want to get the year that the movie was released. The following script helps us do that.

SELECT Concat(`title`, ' (', `director`, ')') , `year_released` FROM `movies`;

HERE

The Concat () MySQL function is used join the columns values together.

The line “Concat (`title`, ‘ (‘, `director`, ‘)’) gets the title, adds an opening bracket followed by the name of the director then adds the closing bracket.

String portions are separated using commas in the Concat () function.

Executing the above script in MySQL workbench produces the following result set.

Concat(`title`, ‘ (‘, `director`, ‘)’) year_released

Pirates of the Caribean 4 ( Rob Marshall) 2011

Forgetting Sarah Marshal (Nicholas Stoller) 2008

NULL 2008

Code Name Black (Edgar Jimz) 2010

NULL 2007

NULL 2007

NULL 2007

Honey mooners (John Schultz) 2005

NULL 2012

Alias field names

The above example returned the Concatenation code as the field name for our results. Suppose we want to use a more descriptive field name in our result set. We would use the column alias name to achieve that. The following is the basic syntax for the column alias name

HERE

“[AS]” is the optional keyword before the alias name that denotes the expression, value or field name will be returned as.

“`alias_name`” is the alias name that we want to return in our result set as the field name.

The above query with a more meaningful column name

SELECT Concat(`title`, ' (', `director`, ')') AS 'Concat', `year_released` FROM `movies`;

We get the following result

Concat year_released

Pirates of the Caribean 4 ( Rob Marshall) 2011

Forgetting Sarah Marshal (Nicholas Stoller) 2008

NULL 2008

Code Name Black (Edgar Jimz) 2010

NULL 2007

NULL 2007

NULL 2007

Honey mooners (John Schultz) 2005

NULL 2012

Getting members listing showing the year of birth

Suppose we want to get a list of all the members showing the membership number, full names and year of birth, we can use the LEFT string function to extract the year of birth from the date of birth field. The script shown below helps us to do that.

SELECT `membership_number`,`full_names`,LEFT(`date_of_birth`,4) AS `year_of_birth` FROM members;

HERE

“LEFT(`date_of_birth`,4)” the LEFT string function accepts the date of birth as the parameter and only returns 4 characters from the left.

“AS `year_of_birth`” is the column alias name that will be returned in our results. Note the AS keyword is optional, you can leave it out and the query will still work.

Executing the above query in MySQL workbench against the myflixdb gives us the results shown below.

membership_number full_names year_of_birth

1 Janet Jones 1980

2 Janet Smith Jones 1980

3 Robert Phil 1989

4 Gloria Williams 1984

SQL using MySQL Workbench

We are now going to use MySQL workbench to generate the script that will display all the field names from our categories table.

2. MySQL workbench will automatically create a SQL query and paste in the editor.

3. Query Results will be show

Notice that we didn’t write the SELECT statement ourselves. MySQL workbench generated it for us.

Why use the SELECT SQL command when we have MySQL Workbench?

Now, you might be thinking why learn the SQL SELECT command to query data from the database when you can simply use a tool such as MySQL workbench’s to get the same results without knowledge of the SQL language. Of course that is possible, but learning how to use the SELECT command gives you more flexibility and control over your SQL SELECT statements.

MySQL workbench falls in the category of “Query by Example” QBE tools. It’s intended to help generate SQL statements faster to increase the user productivity.

Learning the SQL SELECT command can enable you to create complex queries that cannot be easily generated using Query by Example utilities such as MySQL workbench.

To improve productivity you can generate the code using MySQL workbench then customize it to meet your requirements. This can only happen if you understand how the SQL statements work!

Summary

The SQL SELECT keyword is used to query data from the database and it’s the most commonly used command.

The simplest form has the syntax “SELECT * FROM tableName;”

Expressions can also be used in the select statement . Example “SELECT quantity + price FROM Sales”

The SQL SELECT command can also have other optional parameters such as WHERE, GROUP BY, HAVING, ORDER BY. They will be discussed later.

MySQL workbench can help develop SQL statements, execute them and produce the output result in the same window.

Update the detailed information about Working Of Transactions With Examples on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!