Articles on Trending Technologies

Technical articles with clear explanations and examples

How to write a single MySQL query for displaying a value for multiple inputs?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 367 Views

For this, use BETWEEN keyword. Let us first create a table −mysql> create table DemoTable1537    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20)    -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1537(StudentName) values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1537(StudentName) values('Bob'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1537(StudentName) values('Sam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1537(StudentName) values('Mike'); Query OK, 1 row affected (0.16 sec) mysql> insert into ...

Read More

Using Update statement with TINYINT in MySQL?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 2K+ Views

Let us first create a table. We have set one of the columns with type TINYINT −mysql> create table DemoTable    -> (    -> EmployeeId int,    -> isMarried tinyint    -> ); Query OK, 0 rows affected (6.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(101, true); Query OK, 1 row affected (1.94 sec) mysql> insert into DemoTable values(102, false); Query OK, 1 row affected (0.76 sec) mysql> insert into DemoTable values(103, true); Query OK, 1 row affected (1.14 sec) mysql> insert into DemoTable values(104, true); Query OK, 1 row affected (1.22 ...

Read More

A single MySQL query to search multiple words from different column values

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 2K+ Views

For this, you can use WHERE clause with multiple LIKE. Let us first create a table −mysql> create table DemoTable1536    -> (    -> Sentence text    -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1536 values('I like MySQL database.'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1536 values('Java is an Object Oriented Programming Language'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable1536 values('I only like data structure'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1536 values('MongoDB is ...

Read More

MySQL Order by beginning letter?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 668 Views

To order by the first letter, use ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable1535    -> (    -> Value varchar(100)    -> ); Query OK, 0 rows affected (2.26 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1535 values('MySQL is good relational database.'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1535 values('is MySQL easy to lean'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable1535 values('You need to start basic SQL'); Query OK, 1 row affected (0.35 sec)Display all records from the table ...

Read More

Count(*) rows from multiple tables in MySQL?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 1K+ Views

To count rows from multiple tables in MySQL, the syntax is as follows −Select     (select count(*) from yourTableName1) as anyAliasName1,     (select count(*) from yourTableName2) as anyAliasName2     from dual;Let us first create a table −mysql> create table DemoTable1    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY    -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(), (), (), (), (), (); Query OK, 6 rows affected (0.24 sec) Records: 6  Duplicates: 0  Warnings: 0Display all records from the ...

Read More

Order a column in MySQL with IP Address records?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 765 Views

For this, use INET_ATON() in MySQL. The INET_ATON() method would allow a user to convert IP Address records to a number and then we can use ORDER BY to order them.Let us first create a table −mysql> create table DemoTable    -> (    -> IpAddress varchar(50)    -> ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('192.168.110.78'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('192.168.110.87'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('192.168.110.75'); Query OK, 1 row affected (0.26 ...

Read More

MySQL equivalent to Sybase ASE command?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 248 Views

The MySQL equivalent to sybase ASE command is EXPLAIN keyword. Let us first create a table −mysql> create table DemoTable1531    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20)    -> ); Query OK, 0 rows affected (2.92 sec) mysql> create index Name_index1 on DemoTable1531(StudentName); Query OK, 0 rows affected (0.99 sec) Records: 0  Duplicates: 0  Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1531(StudentName) values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1531(StudentName) values('Robert'); Query OK, 1 row affected (0.10 sec) mysql> insert into ...

Read More

MySQL query to select maximum and minimum salary row?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 912 Views

For this, use sub query along with MIN() and MAX(). To display both the maximum and minimum value, use UNION ALL. Let us first create a table −mysql> create table DemoTable    -> (    -> EmployeeName varchar(20),    -> EmployeeSalary int    -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Bob', 8800); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Chris', 9800); Query OK, 1 row affected (0.63 sec) mysql> insert into DemoTable values('David', 7600); Query OK, 1 row affected (0.11 sec) mysql> ...

Read More

Finding the average and display the maximum average of duplicate ids?

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 178 Views

For this, use AVG(). To find the maximum average value, use MAX() and group by id. Let us first create a table −mysql> create table DemoTable    -> (    -> PlayerId int,    -> PlayerScore int    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(2, 82); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(1, 45); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(3, 97); ...

Read More

Using GROUP BY and COUNT in a single MySQL query to group duplicate records and display corresponding max value

AmitDiwan
AmitDiwan
Updated on 12-Dec-2019 260 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> ClientId int,    -> Value int    -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10, 678); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(20, 678); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(30, 678); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------+-------+ | ClientId | Value ...

Read More
Showing 52351–52360 of 61,248 articles
Advertisements