You do not need to store a field PRICE as an int or as float in the database. For this, you can set the DECIMAL()..Most of the time integers can be used to represent the float point numbers and these integers are internally cast into DECIMAL() data type. Therefore, if you have field PRICE then always use DECIMAL() data type. The syntax is as follows −DECIMAL(M, D);Here, M represents the ‘TotalNumberOfDigit’ and D represents the ‘Number OfDigitAfterDecimalPoint’.To understand the above concept, let us create a table with field PRICE as DECIMAL data type. The query is as follows −mysql> create ... Read More

To declare a datetime variable, you need to use a user-defined variable using the SET command. The syntax is as follows −SET @anyVariableName=’yourdatetimeValue’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table datetimeVariables -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> ArrivalDatetime datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query to ... Read More

To return order of MySQL SHOW COLUMNS, you need to use ORDER BY clause. The syntax is as follows −SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘yourTableName’ AND column_name LIKE 'yourStartColumnName%' ORDER BY column_name DESC;Let us create a table in database TEST. The query to create a table is as follows −mysql> create table OrderByColumnName -> ( -> StudentId int, -> StudentFirstName varchar(10), -> StudentLastName varchar(10), -> StudentAddress varchar(20), -> StudentAge int, -> StudentMarks int ... Read More

You will get an error whenever you return multiple rows in the benchmark. Return a scalar value or single row instead of multiple rows. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE yourCondition.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserDemo    -> (    -> UserId int,    -> UserName varchar(20),    -> RegisteredCourse varchar(10)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserDemo values(1, ... Read More

To insert a record in the table using PreparedStatement in Java, you need to use below syntax to insert records. The syntax is as follows −String anyVariableName= "INSERT INTO yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, .........N)" + "VALUES (?, ?, ?, ..............N)";Now use the PreparedStatement object to set the value for all columns. The syntax is as follows −PreparedstatementObject = con.prepareStatement(query); PreparedstatementObject .setXXX(1, yourValue); PreparedstatementObject .setXXX(2, yourValue); PreparedstatementObject .setXXX(3, yourValue); . . . NThe above prepared statement will solve your problem. Now first create a table in MySQL. The query to create a table is as follows −mysql> create table CourseDemo -> ( ... Read More

To count the unique values on a column, you need to use keyword DISTINCT. To understand how it is done, let us create a table. The query to create a table is as follows −mysql> create table UniqueCountByIPAddress    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserHits int,    -> UserIPAddress varchar(50),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UniqueCountByIPAddress(UserHits, UserIPAddress) values(10, '127.0.0.1'); Query OK, 1 row affected (0.20 sec) mysql> insert into ... Read More

You can use GROUP BY clause and COUNT() function for this. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ..N, COUNT(*) as anyAliasName FROM yourTableName GROUP BY yourColumnName1, yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table GroupAndCountByDate    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> TripDate date,    -> ShopId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.79 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More

You will get this type of exception whenever your JDBC URL is not accepted by any of the loaded JDBC drivers by the method acceptsURL. You need to mention the MySQL JDBC driver which is as follows −The MySQL JDBC url is as follows −jdbc:mysql://localhost:3306/test?useSSL=falseThe prototype of acceptsURL is as follows −boolean acceptsURL(String url) throws SQLExceptionThe acceptsURL returns boolean that means if the JDBC driver understands the database URL it returns true otherwise false. It takes one parameter of type String which is a database URL.The entire database URL connection is as follows. The syntax −con = DriverManager. getConnection("jdbc:mysql://localhost:3306/yourDatabaseName?useSSL=false", "yourUserName", ... Read More

You can sort the table_name property from INFORMATION_SCHEMA.TABLES with ORDER BY clause. Sort in ascending order or descending order with the help of ASC or DESC respectively. The syntax is as follows −SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema='yourDatabaseName' ORDER BY table_name DESC;Use the database with the name sample and have some tables. First, we will show all tables after that we will apply to sort on the table name. The query to display all tables is as follows −mysql> show tables;The following is the output −+--------------------------+ | Tables_in_sample         | +--------------------------+ | ... Read More

You need to use LOCATE() along with SUBSTR(). The below syntax will find the word after delimiter. Here, delimiter is colon(:), you can use another i.e. it is up to you. The syntax is as follows −SELECT SUBSTR(yourColumnName, LOCATE(':', yourColumnName)+1, (CHAR_LENGTH(yourColumnName) - LOCATE(':', REVERSE(yourColumnName)) - LOCATE(':', yourColumnName))) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchTextBetweenDelimitersDemo -> ( -> ... Read More