We are given a string of any length and the task is to calculate the count of characters and words in a string using PL/SQL.PL/SQL is a combination of SQL along with the procedural features of programming languages.It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.In PL/SQL block, we have DECLARE block which is used to declare the variables used in programming and we have BEGIN block where we write the logic for the ... Read More

The task is to convert the distance from kilometers to meters and centimeters in PL/SQL.PL/SQL is the extension of SQL which combines the Data manipulation of SQL with the working of procedural language.According to the problem we should have distance in kilometers whose value we have to convert in meters and centimeters.As per the conversion rule −1km = 1000 meters1km = 100000 centimetersAccording to this conversion rule we want the distance to be converted by a logic in PL/SQL.ExampleInput: kilometer = 10 Output: meter = 10000    Centimeter = 1000000 Input: kilometer = 9 Output: meter = 9000    Centimeter ... Read More

For a similar query to UNION two collections, use JOIN in MongoDB along with aggregate(). Let us create a collection with documents −> db.demo486.insertOne({_id:1, "Amount":30, "No":4}); { "acknowledged" : true, "insertedId" : 1 } > db.demo486.insertOne({_id:2, "Amount":40, "No":2}); { "acknowledged" : true, "insertedId" : 2 } > db.demo486.insertOne({_id:3, "Amount":60, "No":6}); { "acknowledged" : true, "insertedId" : 3 }Display all documents from a collection with the help of find() method −> db.demo486.find();This will produce the following output −{ "_id" : 1, "Amount" : 30, "No" : 4 } { "_id" : 2, "Amount" : 40, "No" : 2 } { "_id" ... 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 analyzed and optimized and do not require any specific handling for security purposes.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

MySQL's ENUM data type is used to define a specific set of values in a column making it easier to manage and maintain data consistency. GROUP BY and WHERE() function In MySQL WHERE clause is used to filter the rows based on a condition before grouping them. The GROUP BY clause groups rows with identical values in specified columns, and can be used with functions like SUM, COUNT, and AVG. Together they enable focused data analysis by filtering and grouping efficiently. Syntax Following is the syntax to filter the data on certain conditions and to eliminate duplicate records. SELECT column1, ... 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

Let us create a custom function to validate date in MySQL −mysql> set global log_bin_trust_function_creators=1; Query OK, 0 rows affected (0.03 sec) mysql> delimiter // mysql> create function isValidDate(actualDate varchar(255)) returns int    -> begin    -> declare flag int;    -> if (select length(date(actualDate)) IS NOT NULL ) then    -> set flag = 1;    -> else    -> set flag = 0;    -> end if;    -> return flag;    -> end    -> // Query OK, 0 rows affected (0.11 sec) mysql> delimiter ;Case 1 −When parameter is null value i.e. the date to be ... Read More