Showing entries 1 to 10 of 1149
10 Older Entries »
Displaying posts with tag: sql (reset)
SQL Group By Example | Group By Clause In SQL Tutorial

SQL Group By Example | Group By Clause In SQL Tutorial is today’s topic. The GROUP BY clause in SQL is used to arrange the same data into groups with the help of some functions. The GROUP BY clause group rows that have the same values into summary rows, like “find the number of customers in each city.” The Group By statement allows you to arrange the rows of a query in the groups. The groups are determined by the table columns that you specify in a GROUP BY clause.

SQL Group By Example

The GROUP BY statement is also used with the aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result by one or more columns. Its main work is to summarize the data from the database. If you want to build some kind of chart then the GROUP BY clause will be …

[Read more]
Structuring Your Unstructured JSON data

The world seems awash in unstructured, NoSQL data, mainly of the JSON variety.  While this has a great many benefits as far as data mutability and not being locked into a rigid structure there are some things missing that are common in the structured world of SQL databases.

What if there was a way to take this unstructured NoSQL JSON data and cast it, temporarily, into a structured table?  Then you could use all the processing functions and features found in a relation database on you data.  There is a way and it is the JSON_TABLE function.

JSON_TABLE
You can find the documentation for JSON_TABLE here  but there are some examples below that may make learning this valuable function easier than the simple RTFM.

I will be using the world_x dataset for the next example

If …

[Read more]
SQL Left Join Tutorial With Example | Left Outer Join in SQL

SQL Left Join Tutorial With Example | Left Outer Join in SQL is today’s topic. The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The LEFT JOIN clause allows us to query data from the multiple tables. It returns all the rows from a left table and the matching rows from a right table. This means that the left join returns all the values from a left table, plus matched values from a right table or NULL in the case of no matching join predicate.

In some SQL databased, LEFT JOIN is also called as LEFT OUTER JOIN.

SQL Left Join Tutorial With Example

The syntax of SQL Left Join is the following.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON …
[Read more]
MySQL Tutorial – Managing MySQL Server Logs: Rotate, Compress, Retain & Delete

MySQL Server generates several logs that can help you monitor the activities of the server. However, once these logs are enabled, they can grow in size and start taking up too much disk space. This is why it’s important to have an automated way of archiving and preserving MySQL log files for a certain duration, as well as deleting the old ones. In this blog post, we describe some best practices for setting up and managing MySQL error logs, general logs and slow query logs for your MySQL deployments.

Setting Up MySQL Server Logging

Let’s look at how to setup the following 3 types of logs:

Error Log

Logs all the problems encountered during starting, running, or stopping mysqld. This log can be enabled by having the following option in /etc/my.cnf file:

[Read more]
SQL Update Query Example | SQL Update Statement Tutorial

SQL Update Query Example | SQL Update Statement Tutorial is today’s topic. The SQL UPDATE statement is used to modify the existing records in a table. You need to be very careful when updating records in a table. SQL WHERE clause in the UPDATE statement specifies which record(s) that should be updated. If you omit the WHERE clause completely, then all records in the table will be updated!

SQL Update Query Example

You need to specify which record needs to be updated via WHERE clause, otherwise all the rows would be affected. We can update the single column as well as multiple columns using the UPDATE statement as per our requirement.

SQL Update Syntax

The syntax is following.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; …
[Read more]
SQL SELECT Query Example | SQL SELECT Statement Tutorial

SQL SELECT Query Example | SQL SELECT Statement Tutorial is today’s topic. The Select is the most commonly used statement in SQL. SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table.

The Select command in SQL is one of the most powerful and heavily used commands. This is I guess the first command anyone learns in SQL even before CREATE which is used to create a table in SQL. SELECT is used in SQL to fetch records from database tables, and you can do a lot many things using Select.

For example, you can select all records; you can choose few records based on the condition specified in WHERE clause, select all columns using the wild card (*) or only selecting a few columns by explicitly declaring them in a query.

SQL SELECT Query Example

The SQL SELECT syntax is following.

SELECT column-names …
[Read more]
MySQL High Availability Framework Explained – Part III: Failure Scenarios

In this three-part blog series, we introduced a High Availability (HA) Framework for MySQL hosting in Part I, and discussed the details of MySQL semisynchronous replication in Part II. Now in Part III, we review how the framework handles some of the important MySQL failure scenarios and recovers to ensure high availability.

MySQL Failure Scenarios Scenario 1 – Master MySQL Goes Down

  • The Corosync and Pacemaker framework detects that the master MySQL is no longer available. Pacemaker demotes the master resource and tries …
[Read more]
SQL Where Clause Example | SQL Where Query Tutorial

SQL Where Clause Example | SQL Where Query Tutorial is today’s topic. The WHERE clause is used to filter the database records. The WHERE clause is used to extract only those records that fulfill the specified condition. The SQL WHERE clause is used to specify the condition while fetching the data from a single table or by joining the multiple tables. If a given condition is satisfied, then only it returns the specific value from the table. You should use a WHERE clause to filter the records and fetching the necessary records.

SQL Where Clause Example

The WHERE clause is not only used in the SELECT statement; it is also used in an UPDATE, DELETE statement. The syntax of a SELECT statement with a WHERE clause is following.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

“SELECT column1, column2, column3 FROM tableName” is the standard …

[Read more]
SQL Insert Query Tutorial | SQL INSERT INTO Statement Example

SQL Insert Query Tutorial | SQL Insert Into Statement Example is today’s topic. If you are not familiar with creating a table in SQL, then please check out my how to create a table in SQL tutorial. The INSERT INTO statement is used to add new values into the database. The INSERT INTO statement adds the new record to the table. INSERT INTO can contain the values for some or all of its columns. INSERT INTO can be combined with a SELECT to insert records.

SQL Insert Query Tutorial

The general syntax of SQL INSERT INTO Statement is following.

INSERT INTO table-name (column-names) 
VALUES (values)

Here, column-names could be column1, column2, column3,…columnN are the names of the columns in a table into which you want …

[Read more]
How does a relational database execute SQL statements and prepared statements

Introduction In this article, we are going to see how a relational database executes SQL statements and prepared statements. SQL statement lifecycle The main database modules responsible for processing a SQL statement are: the Parser, the Optimizer, the Executor. A SQL statement execution looks like in the following diagram. Parser The Parser checks the SQL statement and ensures its validity. The statements are verified both syntactically (the statement keywords must be properly spelled and following the SQL language guidelines) and semantically (the referenced tables and column do exist in the database). During... Read More

The post How does a relational database execute SQL statements and prepared statements appeared first on …

[Read more]
Showing entries 1 to 10 of 1149
10 Older Entries »