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
MySQL query to count all the column values from two columns and exclude NULL values in the total count?
Let us first create a table −mysql> create table DemoTable1975 ( StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1975 values('John', 45); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1975 values('Chris', 67); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1975 values('David', 59); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1975 values('Bob', NULL); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1975;This ...
Read MoreDynamic SQL to get a parameter and use it in LIKE for a new table created inside a stored procedure
For this, use prepared statement. Let us first create a table −mysql> create table DemoTable1973 ( StudentId int, StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1973 values(101, 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1973 values(102, 'John Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1973 values(103, 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1973 values(104, 'John Smith'); Query OK, 1 row affected (0.00 sec)Display all records from the table using ...
Read MoreMySQL query to get a specific row from rows
Let us first create a table −mysql> create table DemoTable1972 ( Section char(1), StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1972 values('D', 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1972 values('B', 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1972 values('A', 'Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1972 values('C', 'Carol'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1972;This ...
Read MoreMySQL procedure with SELECT to return the entire table
Let us first create a table −mysql> create table DemoTable1971 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20), StudentPassword int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1971(StudentName, StudentPassword) values('John', '123456'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1971(StudentName, StudentPassword) values('Chris', '123456'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1971(StudentName, StudentPassword) values('David', '123456'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1971(StudentName, StudentPassword) values('Mike', '123456'); Query OK, 1 row affected (0.00 sec)Display all ...
Read MoreOrder by numeric value from string records separated by numbers like CSE 15, CSE 11, etc.?
Let us first create a table −mysql> create table DemoTable1969 ( BranchCode varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1969 values('CSE 101'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1969 values('CSE 11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1969 values('CSE 15'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1969 values('CSE 6'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1969 values('CSE 201'); Query OK, 1 row affected (0.00 sec) mysql> insert into ...
Read MoreSet a specific value for the first three column values in MySQL?
To set a specific value for only 1st three values, you need to use LIMIT 3. Let us first create a table −mysql> create table DemoTable1968 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1968(Name) values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1968(Name) values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1968(Name) values('Sam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1968(Name) values('Mike'); Query OK, 1 ...
Read MoreHow to use if/else condition in select in MySQL?
Let us first create a table −mysql> create table DemoTable1966 ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserName varchar(20), PhotoLiked int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1966(UserName, PhotoLiked) values('Chris', 57); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1966(UserName, PhotoLiked) values('David', 100); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1966(UserName, PhotoLiked) values('Mike', 68); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1966(UserName, PhotoLiked) values('Sam', 78); Query OK, 1 row affected (0.00 sec)Display all ...
Read MoreGet maximum age from records with similar student names in MySQL
For this, you can use GROUP BY along with aggregate function MAX(). Let us first create a table −mysql> create table DemoTable1964 ( StudentName varchar(20), StudentAge int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1964 values('Chris', 23); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1964 values('David', 34); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1964 values('Chris', 27); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1964 values('Sam', 31); Query OK, 1 row affected (0.00 sec) mysql> ...
Read MoreMySQL query to create user and grant permission
To create a user and grant permission, the syntax is as follows −create database yourDatabaseName DEFAULT CHARACTER SET utf8; create user `yourUserName` identified by yourPassword; GRANT SELECT ON yourDatabaseName .* TO `yourUserName`; GRANT INSERT ON yourDatabaseName .* TO `yourUserName`; GRANT UPDATE ON yourDatabaseName .* TO `yourUserName`; GRANT DELETE ON yourDatabaseName .* TO `yourUserName`; GRANT EXECUTE ON yourDatabaseName .* TO `yourUserName`;Here is the query to create user and grant permission −mysql> create database demo_app DEFAULT CHARACTER SET utf8; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create user `John_123` identified by '123456'; Query OK, 0 rows affected (0.00 sec) ...
Read MoreMySQL query to separate and select string values (with hyphen) from one column to different columns
For this, you can use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable1962 ( EmployeeInformation text ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1962 values('101-John-29'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1962 values('102-David-35'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1962 values('103-Chris-28'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1962;This will produce the following output −+---------------------+ | EmployeeInformation | +---------------------+ | 101-John-29 ...
Read More