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
-
Economics & Finance
Articles on Trending Technologies
Technical articles with clear explanations and examples
Using MySQL where clause and ordering by avg() to find the average of duplicate individual elements
For this, use having clause instead of where. Let us first create a table −mysql> create table DemoTable1338 -> ( -> Name varchar(10), -> Score int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. Here, we have inserted duplicate names with scores −mysql> insert into DemoTable1338 values('Chris', 8); Query OK, 1 row affected (0.80 sec) mysql> insert into DemoTable1338 values('Bob', 4); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1338 values('Bob', 9); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1338 values('Chris', 6); ...
Read MoreFetch maximum value from a column with values as string numbers like Value440, Value345, etc. in SQL
For this, you can use MAX() along with substring(). Let us first create a table −mysql> create table DemoTable1337 -> ( -> Value varchar(50) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1337 values('Value400'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1337 values('Value345'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1337 values('Value567'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1337 values('Value489'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement ...
Read MoreSelect different fields in MySQL even if a field is set to null?
For this, you can use COALESCE(). Let us first create a table −mysql> create table DemoTable1336 -> ( -> FirstName varchar(20) -> , -> SecondName varchar(20) -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1336 values('John', NULL); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1336 values(NULL, 'Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1336 values('David', 'Mike'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable1336;This ...
Read MoreSelect entries with timestamp after X time in MySQL
Let us first create a table −mysql> create table DemoTable1335 -> ( -> ArrivalTime datetime -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command. We have inserted date time records here −mysql> insert into DemoTable1335 values('2019-09-19 22:54:00'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:59:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:56:00'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:52:00'); Query OK, 1 row affected (0.11 sec)Display all records from the table using ...
Read MoreInsert values in two tables with a single stored procedure call in MySQL
Following is the syntax to insert values in two tables with a stored procedure −DELIMITER // CREATE PROCEDURE yourProcedureName(anyVariableName int) BEGIN insert into yourTableName1(yourColumnName1) values(yourVariableName); insert into yourTableName2(yourColumnName2) values(yourVariableName); END //Let us first create a table −mysql> create table DemoTable1 -> ( -> StudentScore int -> ); Query OK, 0 rows affected (0.58 sec)Following is the second table −mysql> create table DemoTable2 -> ( -> PlayerScore int -> ); Query OK, 0 rows affected (0.52 sec)Here is the query to create a stored procedure and insert values in two tables ...
Read MoreMySQL row declarations for ZF?
The ZF stands for ZEROFILL i.e. row declarations for zero fill Let us first create a table. Here, we have set the int field size to be 10 −mysql> create table DemoTable1332 -> ( -> Number int(10) ZEROFILL NOT NULL DEFAULT 0 -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1332 values(); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1332 values(1); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1332 values(10); Query OK, 1 row affected (0.10 sec) mysql> insert ...
Read MoreHow do I use the @ sign in MySQL?
To use the @ sign, use MySQL SET command. The @sign is used to set user-defined variables. Following is the syntax −SET @anyVariableName:=yourValue;Let us first create a table −mysql> create table DemoTable1331 -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1331 values(10, 'Chris'); Query OK, 1 row affected (0.71 sec) mysql> insert into DemoTable1331 values(101, 'David'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1331 values(40, 'Bob'); Query OK, 1 row affected (0.12 sec) ...
Read MoreHow to TRIM x number of characters, beginning from the last in MySQL?
For this, you can use substring() along with length(). Let us first create a table −mysql> create table DemoTable1329 -> ( -> StudentName varchar(40) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1329 values('David Miller'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1329 values('Chris Brown'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1329 values('Adam Smith'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1329 values('John Doe'); Query OK, 1 row affected (0.44 sec)Display all records from the ...
Read MoreMySQL query to extract time in a format without seconds
For this, you can use time_format(). Let us first create a table −mysql> create table DemoTable1326 -> ( -> Arrivaltime time -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1326 values('12:10:45'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1326 values('20:00:00'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1326 values('22:45:55'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1326 values('04:10:24'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select * ...
Read MoreOrder MySQL results without identifier?
To order MySQL results without identifier, the syntax is as follows −select * from yourTableName order by 1 DESC LIMIT yourLimitValue;Let us first create a table −mysql> create table DemoTable1325 -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1325 values(100, 'Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1325 values(101, 'Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1325 values(120, 'David'); Query OK, 1 row affected (0.14 sec) mysql> insert ...
Read More