Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Articles by Rama Giri
Page 2 of 9
MySQL query to search within the last 5 characters in a column?
Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeName varchar(100) -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Adam Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Carol Taylor'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('David Miller'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Chris Evan'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select ...
Read MoreHow to select the sum of the column values with higher value in reach row with MySQL?
Use the CASE statements and set conditions for the same. Let us first create a table −mysql> create table DemoTable -> ( -> X int, -> Y int -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20, 30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(40, 15); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(80, 85); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select ...
Read MoreHow do I multiply an unsigned int by -1 on a MySQL SELECT?
Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------+ | Value | +-------+ | 10 | | ...
Read MoreHow to concatenate MySQL distinct query results into a string?
Use group_concat() function from MySQL to concatenate. Let us first create a table −mysql> create table DemoTable -> ( -> Subject varchar(10) -> ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('C'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('C++'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('C++'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values('MongoDB'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('MySQL'); Query ...
Read MoreWhere MySQL views can be inconsistent and how can we ensure their consistency?
In case of updateable views, it is quite possible that we update the data that is not visible through the view because we create a view to revealing only the partial data of a table. Such kind of updates makes the view inconsistent. We can ensure the consistency of views by using WITH CHECK OPTION while creating or modifying the views. Although WITH CHECK OPTION clause is an optional part of CREATE VIEW statement but it is very useful to make views consistent.Basically, the WITH CHECK OPTION clause prevents us from updating or inserting the rows which are not visible ...
Read MoreHow can we get the count of all MySQL event-related operations collectively?
With the help of SHOW STATUS statement, we can get the count of MySQL event-related operations. It can be used as follows −mysql> SHOW STATUS LIKE '%event%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_alter_event | 16 | | Com_create_event | 6 | | Com_drop_event | 4 | | Com_show_binlog_events | 0 | | Com_show_create_event | 0 | | Com_show_events | 4 | | Com_show_relaylog_events | 0 | +--------------------------+-------+ 7 rows in set (0.17 sec)
Read MoreHow changes, made in the current transaction, can be permanently recorded\\nin MySQL database?
We can use COMMIT command to make the changes, made in a current transaction, permanently recorded in MySQL database. Suppose if we run some DML statements and it updates some data objects, then COMMIT command will record these updates permanently in the database.Examplemysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Marks Values(1, 'Aarav', 'Maths', 50); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Marks Values(2, 'Harshit', 'Maths', 55); Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.06 sec)In this example, the COMMIT statement will ...
Read MoreWhat MySQL MAKE_SET() function returns if there are all NULL at the place of strings?
MySQL MAKE_SET() function will return nothing if there are all NULL at the place of strings. Following example will demonstrate it −Examplemysql> Select MAKE_SET(2, NULL,NULL,NULL); +-----------------------------+ | MAKE_SET(2, NULL,NULL,NULL) | +-----------------------------+ | | +-----------------------------+ 1 row in set (0.00 sec)
Read MoreIn MySQL, how IN() comparison function works?
Basically, IN() comparison function checks whether a value is within a set of values or not. If the value is within a set of values then it returns 1 otherwise 0. Its syntax can be as follows;Expression IN (val1, val2, …, valN)Here, The expression is the value that is to be searched within the set of N values within IN list.Val1, val2, …, valN is the set of N values, forms the IN list, from which the search happens.Examplemysql> Select 100 IN (50, 100, 200, 400, 2000); +------------------------------+ | 100 IN (50, 100, 200, 400, 2000) | +------------------------------+ | ...
Read MoreWhat happens if I pass only one argument to the MySQL CONCAT() function?
MySQL allows us to pass only one argument to the CONCAT() function. In this case, MySQL returns the same argument as output. Following example will exhibit it −Examplemysql> Select Concat('Delhi'); +-----------------+ | Concat('Delhi') | +-----------------+ | Delhi | +-----------------+ 1 row in set (0.00 sec)
Read More