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 by Anvi Jain
Page 31 of 43
Select random number from a specific list in MySQL?
You can use elt() along with rand() for this. Let us select random number from a specific list.mysql> SELECT ELT(FLOOR(RAND() * 10) + 1, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000) AS random_value_from_listOfValues;This will produce the following output −+--------------------------------+ | random_value_from_listOfValues | +--------------------------------+ | 1000 | +--------------------------------+ 1 row in set (0.00 sec)Now we will run the query again to select random number from a specific list.mysql> SELECT ELT(FLOOR(RAND() * ...
Read MoreSelect and add result of multiplying two columns from a table in MySQL?
You can use aggregate function SUM() for this. Let us first create a table −mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerProductName varchar(100), CustomerProductQuantity int, CustomerPrice int ); Query OK, 0 rows affected (0.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) values('Product-1', 5, 400); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) values('Product-2', 3, 100); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(CustomerProductName, CustomerProductQuantity, CustomerPrice) values('Product-1', 2, 300); Query OK, 1 ...
Read MoreOrdering string as a number in a database?
To order string as a number, use CAST(). Following is the syntax −select *from yourTableName ORDER BY CAST(yourColumnName AS SIGNED) DESC;Let us first create a table −mysql> create table DemoTable ( Id varchar(100) ); Query OK, 0 rows affected (0.18 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('3'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values('20'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values('34'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values('21'); Query OK, 1 row ...
Read MoreHow to sum based on field value in MySQL?
To sum based on field values, use aggregate function SUM() along with CASE statement. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Price int, isValidCustomer boolean, FinalPrice int ); Query OK, 0 rows affected (0.23 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Price, isValidCustomer, FinalPrice) values(20, false, 40); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(Price, isValidCustomer, FinalPrice) values(45, true, 10); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Price, isValidCustomer, FinalPrice) ...
Read MoreJava DatabaseMetaData getMaxColumnsInSelect() method with example.
The getMaxColumnsInSelect() method of the DatabaseMetaData interface is used to find out the maximum number of columns that the underlying database allows in a SELECT list.This method returns an integer value, representing the maximum number of columns allowed in a SELECT list. If this value is 0 it indicates that there is no limit or, limit is unknown.To get the DatabaseMetaData object −Make sure your database is up and running.Register the driver using the registerDriver() method of the DriverManager class. Pass an object of the driver class corresponding to the underlying database.Get the connection object using the getConnection() method of ...
Read MoreCounting presence of a NOT NULL value in MySQL
To count presence of a NOT NULL value, use aggregate function COUNT(yourColumnName). Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, NumberOfQuestion int, NumberOfSolution int ); Query OK, 0 rows affected (0.20 sec)Insert some records in the table using insert command. Here, some of the values are NULL −mysql> insert into DemoTable(NumberOfQuestion, NumberOfSolution) values(20, 10); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(NumberOfQuestion, NumberOfSolution) values(20, 2); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(NumberOfQuestion, NumberOfSolution) values(20, NULL); Query ...
Read MoreJava DatabaseMetaData getMaxRowSize() method with example.
The getMaxRowSize() method of the DatabaseMetaData interface is used to find out the maximum number of bytes that the underlying database allows in a row.This method returns an integer value, representing the maximum row size. If this value is 0 it indicates that there is no limit or, limit is unknown.To get the DatabaseMetaData object −Make sure your database is up and running.Register the driver using the registerDriver() method of the DriverManager class. Pass an object of the driver class corresponding to the underlying database.Get the connection object using the getConnection() method of the DriverManager class. Pass the URL the ...
Read MoreHow can I find all columns where the column name length is greater than 5 in MySQL?
Use HAVING to find all columns where the column name length is greater than 5. This will produce the following output −+-------------------+-------------------------+ | TABLE_NAME | COLUMN_NAME | +-------------------+-------------------------+ | DemoTable | UserId | | DemoTable | EndDate | | DemoTable | StartDate | | DemoTable | StudentFavouriteSubject | | DemoTable ...
Read MoreHow can I calculate the total value of products from my MySQL product table?
Let us first create a table −mysql> create table DemoTable ( ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ProductQuantity int, ProductPrice int ); Query OK, 0 rows affected (0.19 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ProductQuantity, ProductPrice) values(10, 100); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(ProductQuantity, ProductPrice) values(5, 11); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(ProductQuantity, ProductPrice) values(3, 140); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable(ProductQuantity, ProductPrice) values(2, 450); Query OK, 1 row affected ...
Read MoreJava DatabaseMetaData getMaxTablesInSelect() method with example.
The getMaxTablesInSelect() method of the DatabaseMetaData interface is used to find out the maximum number of tables that the underlying database allows in an SQL SELECT statement.This method returns an integer value, representing the maximum number of tables allowed in a SELECT statement. If this value is 0 it indicates that there is no limit or, limit is unknown.To get the DatabaseMetaData object −Make sure your database is up and running.Register the driver using the registerDriver() method of the DriverManager class. Pass an object of the driver class corresponding to the underlying database.Get the connection object using the getConnection() method ...
Read More