For this, you can use UNION ALL along with LIMIT concept. For our example, we will create three tables.Let us create the first table −mysql> create table demo3 −> ( −> value int −> ); Query OK, 0 rows affected (1.39 sec)Insert some records into the table with the help of insert command −mysql> insert into demo3 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into demo3 values(20); Query OK, 1 row affected (0.08 sec) mysql> insert into demo3 values(30); Query OK, 1 row affected (0.08 sec)Display records from the table using select statement −mysql> select ... Read More

Following is the syntax implementing multiple LIKE operators with ORDER BY −select *from yourTableName order by (    yourColumnName like '%yourValue1%' ) + (    yourColumnName like '%yourValue2%' ) + . . N desc;Let us create a table −mysql> create table demo2 −> ( −> id int not null auto_increment, −> name varchar(100), −> primary key(id) −> ); Query OK, 0 rows affected (1.53 sec)Insert some records into the table with the help of insert command −mysql> insert into demo2(name) values('John'); Query OK, 1 row affected (0.18 sec) mysql> insert into demo2(name) values('David'); Query OK, 1 row affected (0.09 ... Read More

In order to get number located at 2 places before decimal point, you can use the concept of div.Let us create a table −mysql> create table demo1 −> ( −> value float −> ); Query OK, 0 rows affected (2.20 sec)Insert some records into the table with the help of insert command −mysql> insert into demo1 values(456.54); Query OK, 1 row affected (0.16 sec) mysql> insert into demo1 values(50.64); Query OK, 1 row affected (0.17 sec) mysql> insert into demo1 values(1000.78); Query OK, 1 row affected (0.13 sec)Display records from the table using select statement −mysql> select *from demo1;This will ... Read More

Before discussing on Simple and complex, first we should know what is View. A View is the logical virtual table created from one or more tables which can be primarily used to fetch the columns from one or more different tables at a time. On the basis of tables involved in the view we can distinguish between Simple and Complex View in SQL.Following are the important differences between Simple and Complex View.Sr. No.KeySimple ViewComplex View1DefinitionSimple View in SQL is the view created by involving only single table. In other words we can say that there is only one base table ... Read More

Static SQLStatic SQL refers to those SQL statements which are fixed and can be hard coded into the application. As static sqls are fixed queries, these statements can be analysed and optimized and do not require any specific handling for security purpose.Dynamic SQLDynamic SQL refers to those SQL statements which are generated dynamically based on user's input and run in the application. Dynamic Sqls helps to develop general and flexible applications. Dynamic SQL may need more permissions and security handling and a malicious user can create dangerous code as well.Following are some of the important differences between Static Routing and ... Read More

For this, use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable2040    -> (    -> StudentCode varchar(20)    -> ); Query OK, 0 rows affected (0.85 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2040 values('John-232'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2040 values('Carol-901'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable2040 values('David-987'); Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −mysql> select *from DemoTable2040;This will produce the following output −+-------------+ | StudentCode | +-------------+ | ... Read More

To display a list of records in a specific order, you need to set conditions and use ORDER BY. For this, use ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable2039    -> (    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2039 values('John Doe'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable2039 values('John Smith'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2039 values('Chris Brown'); Query OK, 1 row affected ... Read More

To return TRUE for positive values and FALSE for negative, use MySQL IF(). Let us first create a table −mysql> create table DemoTable2038    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Value int    -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2038(Value) values(57); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable2038(Value) values(-100);; Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2038(Value) values(-78); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable2038(Value) ... Read More

Let us first create a table. Here, we have set the status using ENUM −mysql> create table DemoTable2037    -> (    -> StudentId int,    -> status enum('Active', 'Inactive')    -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2037 values(99, 'Active'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2037 values(99, 'Inactive'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable2037 values(100, 'Inactive'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable2037 values(99, 'Active'); Query OK, 1 row ... Read More

For this, use the concept of LEFT() function. Let us first create a table −mysql> create table DemoTable2036    -> (    -> FirstLetter varchar(20),    -> Title varchar(20)    -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2036(Title) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2036(Title) values('John'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2036(Title) values('Adam'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable2036;This will produce the ... Read More