- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
2K+ Views
“WITH ROLLUP” is a modifier that is used with GROUP BY clause. Mainly, it causes the summary output to include extra rows that represent higher-level summary operations.ExampleIn the example below, WITH ROLLUP modifier gave the summary output with total price value in the extra row.mysql> Select Item, SUM(Price) AS Price from ratelist Group by item WITH ROLLUP; +------+-------+ | Item | Price | +------+-------+ | A | 502 | | B | 630 | | C | 1005 | | h | 850 | | T | 250 | | NULL | 3237 | +------+-------+ 6 rows in set (0.00 sec)
711 Views
With the help of the following built-in commands, MySQL can execute a query even if semicolon (;) terminator symbol is not used.egoWe can use this command by using \G option. It means to send the current statement to the server to be executed and display the result in vertical format. When we use \G and omitting semicolon(;) in a statement (single or multiple lines), MySQL determines the end of the statement as and when it encounters \G. Consider the example below −mysql> Select * from ratelist\G *************************** 1. row *************************** Sr: 1 Item: A Price: 502 *************************** 2. row ... Read More
137 Views
We can also use the BEGIN statement to start a new transaction. It is the same as the START TRANSACTION statement.Examplemysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Marks Values(1, 'Aarav', 'History', 40); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Marks Values(2, 'Harshit', 'History', 48); Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.04 sec)In this example, the transaction is initiated by the BEGIN Statement rather than START TRANSACTION statement. Two INSERT statements are then executed followed by a ROLLBACK statement. ROLLBACK statement will ... Read More
278 Views
Actually ROLLUP and ORDER BY are mutually exclusive in MySQL hence it is not a good practice to use both of them in a query. But still, if we use ROLLUP in ORDER BY then the main disadvantage is that the summary rows would get sorted along with the rows they are calculated. It is also significant to notice that the sort order will decide the position of summary rows.The summary rows would be at the beginning of ascending order and at the end of descending order. Consider the following example to understand it more clearly −mysql> Select * from ... Read More
83 Views
In the case, where we use explicit sort order (ASC or DESC) with column names in the GROUP BY list along with the “WITH ROLLUP” modifier, the summary rows added by ROLLUP still appear after the rows from which they calculated regardless of the sort order.As we know that the default sort order is ascending hence in the example below if we will not use any explicit sort order then the output would be as follows −mysql> Select sr, SUM(Price) AS Price from ratelist Group by sr with rollup; +-----+-------+ | sr | Price | +-----+-------+ | 1 | ... Read More
49 Views
MySQL can manage the behavior of a transaction with the help of the following two modes −Autocommit OnIt is the default mode. In this mode, each MySQL statement (within a transaction or not) is considered as a complete transaction and committed by default when it finishes. It can be started by setting the session variable AUTOCOMMIT to 1 as follows −SET AUTOCOMMIT = 1 mysql> SET AUTOCOMMIT = 1; Query OK, 0 rows affected (0.07 sec)Autocommit OffIt is not the default mode. In this mode, the subsequent series of MySQL statements act like a transaction, and no activities are committed ... Read More
73 Views
With the help of the MySQL SUM() function, we can get the total value by category in one output row. For example in table ‘ratelist’ if we want to get the total value of category ‘price’ then we can use SUM() on price as follows −mysql> select SUM(price) as totalprice from ratelist; +------------+ | totalprice | +------------+ | 3237 | +------------+ 1 row in set (0.00 sec)The query above returns the total value of price in one output row.
1K+ Views
From the output returned by MySQL, it is very much clear that how many rows are there in the result set along with the execution time.ExampleFor example, in the following MySQL output we can see there are 3 rows in the result set.mysql> Select * from ratelist ORDER BY Price LIMIT 3; +----+------+-------+ | Sr | Item | Price | +----+------+-------+ | 5 | T | 250 | | 1 | A | 502 | | 2 | B | 630 | +----+------+-------+ 3 rows in set (0.00 sec)But suppose if MySQL query has ... Read More
139 Views
We can specify the number of records to be returned in output by adding a LIMIT clause in MySQL query. LIMIT clause restricts the number of rows to be returned. Consider the following example −mysql> Select * from ratelist ORDER BY Price; +----+------+-------+ | Sr | Item | Price | +----+------+-------+ | 5 | T | 250 | | 1 | A | 502 | | 2 | B | 630 | | 4 | h | 850 | | 3 | C | 1005 | +----+------+-------+ 5 rows in set ... Read More
55 Views
Actually, as we know that we can specify a sort order with the help of the ORDER BY clause. We need to write the ORDER BY keyword followed by the name of the column on which we want to sort the table. It is not necessary that we have to use that column name after the SELECT keyword in the query.Examplemysql> Select Sr, Item from ratelist ORDER BY Price; +----+------+ | Sr | Item | +----+------+ | 5 | T | | 1 | A | | 2 | B | | 4 | h | ... Read More