Category: mysql

MySQL – Self Join Table of content MySQL Self Join Self Join with ORDER BY Clause Self Join Using Client Program ”; Previous Next MySQL Self Join The MySQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, at least one table is temporarily renamed in the MySQL statement. Self Join is a type of inner join, which performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it. However, unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause. Syntax Following is the basic syntax of Self Join in MySQL − SELECT column_name(s) FROM table1 a, table1 b WHERE a.common_field = b.common_field; Here, the WHERE clause could be any given expression based on your requirement. Example Self Join only requires one table to join itself; so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn. CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, let us join this table using the following Self Join query. Our aim is to establish a relationship among the said customers on the basis of their earnings. We are doing this with the help of WHERE clause. SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY; Output The resultant table displayed will list out all the customers that earn lesser than other customers − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 6 Chaitali Komal 4500.00 3 Chaitali Kaushik 2000.00 2 Chaitali Khilan 1500.00 1 Chaitali Ramesh 2000.00 6 Hardik Komal 4500.00 4 Hardik Chaitali 6500.00 3 Hardik Kaushik 2000.00 2 Hardik Khilan 1500.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00 2 Komal Khilan 1500.00 1 Komal Ramesh 2000.00 6 Muffy Komal 4500.00 5 Muffy Hardik 8500.00 4 Muffy Chaitali 6500.00 3 Muffy Kaushik 2000.00 2 Muffy Khilan 1500.00 1 Muffy Ramesh 2000.00 Self Join with ORDER BY Clause Furthermore, after joining a table with itself using self join, the records in the combined table can also be sorted in an ascending order using the ORDER BY clause. Following is the syntax for it − SELECT column_name(s) FROM table1 a, table1 b WHERE a.common_field = b.common_field ORDER BY column_name; Example In this example, executing the query below will join the CUSTOMERS table with itself using self join on a WHERE clause. Then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column. Here, we are arranging the records based on the salary column SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY ORDER BY a.SALARY; Output The resultant table is displayed as follows − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 2 Chaitali Khilan 1500.00 2 Hardik Khilan 1500.00 2 Komal Khilan 1500.00 2 Muffy Khilan 1500.00 3 Chaitali Kaushik 2000.00 1 Chaitali Ramesh 2000.00 3 Hardik Kaushik 2000.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00

MySQL – Collation Table of content Implementing MySQL Collations Set Character Set and Collation At Server Level At Database Level At Table Level Displaying Default Collations ”; Previous Next MySQL Collation is a set of rules used to decide how to compare and sort various characters of a character set. MySQL supports multiple character sets including ASCII, Unicode System, Binary, etc. Every character of these character sets is subjected to a weight. A MySQL collation orders the characters based on their respective weights. For instance, when comparing two characters in a character set, if one character holds heavier weight than the other, it is greater; and vice-versa. If both characters have equal weights, they are equal. Each character set must have at least one collation (or more) and no two character sets can have the same collation. Implementing MySQL Collations MySQL implements various types of collations in order to compare character strings − Simple Collations for 8-bit Character Sets Complex Collations for 8-bit Character Sets Collations for Non-Unicode Multibyte Character Sets Collations for Unicode Multibyte Character Sets Miscellaneous Collations Every character set has a built-in binary collation, so they need not be redefined. Built-in collations like these must not be modified in any case to avoid unexpected server behaviour. Simple Collations for 8-bit Character Sets As the 8-bit character sets can only hold up to 256 characters, this type of collation is implemented by using a weights array of length 256. Each character in the character set is one-to-one mapped to the weights. It is a case-insensitive collation, so the uppercase and lowercase of same character hold the same weight. Complex Collations for 8-bit Character Sets For complex 8-bit character sets, collations are implemented by defining the order of characters using functions. Here, we create a C source file that specifies the character set properties and defines the necessary support routines to perform operations on that character set properly. Collations for Non-Unicode Multibyte Character Sets Unlike single-byte (8-bit) characters, there are two types of relationships between codes and weights of multi-byte characters. Weight of a character is equal to its code. Character codes are mapped one-to-one with weights, where weights are not necessarily equal to codes. Collations for Unicode Multibyte Character Sets Some collations are based on the Unicode Collation Algorithm (UCA). They hold the following properties − If a character has weight, each weight uses 2 bytes. If a character has no weight, then the character is ignorable. A single character can have many weights. This is called Expansion. For example, the German letter (SHARP S) has a weight of 0x0FEA0FEA. Multiple characters together can have only one weight. This is called Contraction. For example, ”ch” is a single letter in Czech and has a weight of 0x0EE2. Miscellaneous Collations Collations that do not fall into any previous categories are termed as Miscellaneous Collations. Set Character Set and Collation MySQL allows us to set the character sets and collations at three different levels. The same is described below: At Server level At Database level At Table level At Server Level In MySQL, the character set latin1 will be used as the default character set. So, the default collation will be latin1_swedish_ci. MySQL allows us to change these default settings at the server startup level. When starting up a MySQL server, if we specify a character set, it will use the default collation of that set. But if we explicitly specify both a character set and collation, MySQL will use that combination for all databases created further. Example In the following query, we will set the character set as utf8 and the collation as utf8_unicode_cs for the sever. mysqld –character-set-server=utf8 –collation-server=utf8_unicode_cs A warning is issued if –collation-server is set to a user-defined collation name. At Database Level When we create a database and if we do not provide any character set and collation, the database will use the default character set and collation of the server. We can override the default character set and collation at the database level using the CREATE DATABASE statement. If we want to override default settings for existing database, we can use the ALTER DATABASE statement. Syntax Following is the basic syntax to override the default settings at database level − [CREATE | ALTER] DATABASE database_name CHARACTER SET character_set_name COLLATE collation_name; Example Here, we are creating a database and specifying the character set as utf8 and collation as utf8_unicode_ci using the following query − CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_unicode_ci; At Table Level In MySQL, a database may contain tables with different characters sets and collations than the database”s character set and collation. We can specify the default character set and collation at the while creating the table using the CREATE TABLE statement. If we want to override default settings for existing table, we can use the ALTER TABLE statement. Syntax Following is the syntax for specifying default character set and collation for a table using the CREATE TABLE statement − [CREATE | ALTER] TABLE table_name column_name datatype (length) CHARACTER SET character_set_name COLLATE collation_name Example In the following query, we are creating a table without any character set and collation. So, it uses the database”s character set and collation. CREATE TABLE CUSTOMERS( ID VARCHAR(45), NAME VARCHAR(45), AGE INT ); Now, we are using the ALTER TABLE statement to modify the character set as ”latin1” and collation as ”latin_german_ci”.

MySQL – REGEXP_LIKE() Function Table of content MySQL REGEXP_LIKE() Function REGEXP_LIKE() Function Using a Client Program ”; Previous Next MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search. As we have seen in the previous chapter, the MySQL regexp_instr() function is used to return the position of the pattern found. But if you want to just detect whether the pattern is present in the data or not, you can use the regexp_like() function. MySQL REGEXP_LIKE() Function The MySQL regexp_like() function is also used to search for a string that is matched with specified patterns. This function returns 1 if this string matches the specified pattern, 0 if there is no match, or NULL if the string or the pattern is NULL. The pattern used in this function can be an extended regular expression and not just an ordinary string. Syntax Following is the syntax of the MySQL regexp_like() function − REGEXP_LIKE(expr, pattern[, match_type]) Parameters The regexp_like() function takes following parameter values − expr: The string in which search is performed pattern: The pattern that is searched in the string match_type: (Optional argument) A string that specifies how to perform matching; includes case-sensitive matching(c), case-insensitive matching(i), multiple-line mode(m), matching line terminators(n), matching Unix-only line endings(u). Example In this example, we are performing a search operation on a simple string using the MySQL REGEXP_LIKE() function − SELECT REGEXP_LIKE(”Welcome To Tutorialspoint!”, ”To”) AS RESULT; The search pattern ”To” is present in the string, so it returned 1 as output. Result 1 Now, if there is no match found in the string, the result will be obtained as ”0” as shown below − SELECT REGEXP_LIKE(”Welcome To Tutorialspoint!”, ”Hello”) AS RESULT; Following is the output − Result 0 Let us also pass the optional arguments to this function as case-sensitive matching(c) and observe the result − SELECT REGEXP_LIKE(”Welcome To Tutorialspoint!”, ”t”, ”c”) AS RESULT; Executing the query above will produce the following output − Result 1 Example If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the string parameter. SELECT REGEXP_LIKE(NULL, ”value”) AS Result; Following is the output − Result NULL Here, we are passing NULL as the search pattern − SELECT REGEXP_LIKE(”Welcome to Tutorialspoint”, NULL) AS Result; Executing the query above will produce the following output − Result NULL Example In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_LIKE() function. Firstly, let us create the table using the following query − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query inserts 7 records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to display all the records of CUSTOMERS table − Select * from CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following query selects records from the CUSTOMERS table where the NAME column starts with the letter ”K” − SELECT REGEXP_LIKE(NAME, ”^K”) AS RESULT FROM CUSTOMERS; If there is a name that starts with letter ”K” it gives 1 as output, else 0 − Result 0 1 1 0 0 1 0 The following query checks whether the ”ADDRESS” column in the ”CUSTOMERS” table contains the letter ”K” (case-insensitive). If the address contains ”K” or ”k,” the result is 1; otherwise, it”s 0. SELECT REGEXP_LIKE(ADDRESS, ”R”, ”i”) AS RESULT FROM CUSTOMERS; As we can see in the output table, 6th and 7th row in ADDRESS column contains a letter ”K” (case-insensitive) − Result 0 1 1 0 0 1 0 REGEXP_LIKE() Function Using a Client Program Besides using MySQL queries to perform the REGEXP_Like() function, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result. Syntax Following are the syntaxes of this operation in various programming languages − PHP NodeJS Java

MySQL – Aggregate Functions ”; Previous Next In general, aggregation is a consideration of a collection of objects that are bound together as a single entity. MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit. Following are the MySQL aggregate functions − Sr.No. Name & Description 1 AVG() This function is used to calculate the average value. 2 BIT_AND() This function is used to calculate the bitwise AND value. 3 BIT_OR() This function is used to calculate the bitwise OR value. 4 BIT_XOR() This function is used to calculate the bitwise XOR value. 5 COUNT() This function is used to count the number of rows returned. 6 GROUP_CONCAT() This function returns the concatenated string. 7 JSON_ARRAYAGG() This function returns the given values as a single JSON array. 8 JSON_objectAGG() This function returns the given values as a single JSON object. 9 MAX() This function returns the maximum value. 10 MIN() This function returns the minimum value. 11 STD() This function calculates and returns the population standard deviation. 12 STDDEV() This function calculates and returns the population standard deviation. 13 STDDEV_POP() This function calculates and returns the population standard deviation. 14 STDDEV_SAMP() This function calculates and returns the sample standard deviation. 15 SUM() This function is used to calculate the sum of the values. 16 VAR_POP() This function calculates and returns the population standard variance. 17 VAR_SAMP() This function calculates and returns the sample variance. 18 VARIANCE This function calculates and returns the population standard variance. Print Page Previous Next Advertisements ”;

MySQL – Right Join Table of content MySQL Right Join Joining Multiple Tables with Right Join Right Join with WHERE Clause Right Join Using a Client Program ”; Previous Next MySQL Right Join The Right Join or Right Outer Join query in MySQL returns all rows from the right table, even if there are no matches in the left table. So, if zero records are matched in the left table, the right join will still return a row in the result, but with a NULL value in each column of the left table. In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate. The resultant table displayed after implementing the Right Join is not stored anywhere in the database. Syntax Following is the basic syntax of Right Join in SQL − SELECT table1.column1, table2.column2… FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field; Example Assume we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2), ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Right join Query Now, let us join these two tables using the Right Join query as follows. SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output This would produce the following result − ID NAME AMOUNT DATE 3 Kaushik 3000.00 2009-10-08 00:00:00 3 Kaushik 1500.00 2009-10-08 00:00:00 2 Khilan 1560.00 2009-11-20 00:00:00 4 Chaitali 2060.00 2008-05-20 00:00:00 Joining Multiple Tables with Right Join Like Left Join, Right Join also joins multiple tables. However, the contrast occurs where the second table is returned as a whole instead of the first. Syntax Following is the syntax to join multiple tables using Right Join − SELECT column1, column2, column3… FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name RIGHT JOIN table3 ON table2.column_name = table3.column_name . . . Example Here, let us consider the previously created tables CUSTOMERS and ORDERS; along with the newly created table EMPLOYEE. We will create the EMPLOYEE table using the query below − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO EMPLOYEE VALUES (102, ”SARIKA”, 4500), (100, ”ALEKHYA”, 3623), (101, ”REVATHI”, 1291), (103, ”VIVEK”, 3426); The table is created as − EID EMPLOYEE_NAME SALES_MADE 102 SARIKA 4500 100 ALEKHYA 3623 101 REVATHI 1291 103 VIVEK 3426 Let us join these three tables using the Right Join query given below − SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID RIGHT JOIN EMPLOYEE ON ORDERS.OID = EMPLOYEE.EID; Through this query, we are trying to display the records of Customer IDs, Customer names, Orders made on specific dates and names of the employees that sold them. Output The resultant table is obtained as follows − ID NAME DATE EMPLOYEE_NAME 3 Kaushik 2009-10-08 00:00:00 SARIKA 3 Kaushik 2009-10-08 00:00:00 ALEKHYA 2 Khilan 2009-11-20 00:00:00 REVATHI 4

MySQL – UNION Operator Table of content MySQL UNION Operator UNION with WHERE clause UNION with ORDER BY clause UNION with Aliases UNION ALL Operator UNION Operator Using Client Program ”; Previous Next MySQL UNION Operator The UNION operator in MySQL combines the data (without duplicate records) from multiple tables. We can use UNION if we want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria − The same number of columns selected with the same datatype. These columns must also be in the same order. They need not have same number of rows. Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables. UNION is available as of MySQL 4.0. This section illustrates how to use it. Syntax The basic syntax of UNION operator in MySQL is as follows − SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; Example Let us first create the prospective customers table named PROSPECT using the following CREATE TABLE query − CREATE TABLE PROSPECT ( FNAME CHAR(20) NOT NULL, LNAME CHAR(20), ADDRESS VARCHAR(100) NOT NULL ); Now, we insert records into this table using INSERT statement below − INSERT INTO PROSPECT VALUES (”Peter”, ”Jones”, ”482 Rush St., Apt. 402”), (”Bernice”, ”Smith”, ”916 Maple Dr.”); The PROSPECT table is created as − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. ACTIVE Table − We then create an active customers table named ACTIVE using the following CREATE TABLE query − CREATE TABLE ACTIVE ( FNAME CHAR(20) NOT NULL, LNAME CHAR(20), ADDRESS VARCHAR(100) NOT NULL ); Using the following INSERT statement, insert records into the ACTIVE table − INSERT INTO ACTIVE VALUES (”Grace”, ”Peterson”, ”16055 Seminole Ave.”), (”Bernice”, ”Smith”, ”916 Maple Dr.”), (”Walter”, ”Brown”, ”8602 1st St.”); The ACTIVE table is created as − FNAME LNAME ADDRESS Grace Peterson 16055 Seminole Ave. Bernice Smith 916 Maple Dr. Walter Brown 8602 1st St. Now, you want to create a single mailing list by merging names and addresses from all the tables. UNION provides a way to do this. The following query illustrates how to select names and addresses from the tables all at once − SELECT FNAME, LNAME, ADDRESS FROM PROSPECT UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE; Output Following output is obtained − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. Grace Peterson 16055 Seminole Ave. Walter Brown 8602 1st St. As you can see, duplicates are avoided in the result-set. UNION with WHERE clause We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them. Syntax Following is the syntax for using the WHERE clause with UNION operator − SELECT column1, column2, column3 FROM table1 WHERE column1 = ”value1” UNION SELECT column1, column2, column3 FROM table2 WHERE column1 = ”value2”; Example Let us use the same tables from the previous example to retrieve combined records using UNION operator with WHERE clause − SELECT FNAME, LNAME, ADDRESS FROM PROSPECT WHERE LNAME = ”Jones” UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE WHERE LNAME = ”Peterson”; Output Following output is obtained − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Grace Peterson 16055 Seminole Ave. UNION with ORDER BY clause When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set. Syntax Following is the basic syntax to use UNION operator with ORDER BY clause − SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2 ORDER BY column_name; Example Let us try to sort the table records in ascending order with respect to values in the ”lname” column of result set, using the following query − SELECT FNAME, LNAME, ADDRESS FROM PROSPECT UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE ORDER BY LNAME; Output Following output is obtained − FNAME LNAME ADDRESS Walter Brown 8602 1st St. Peter Jones 482 Rush St., Apt. 402 Grace Peterson 16055 Seminole Ave. Bernice Smith 916 Maple Dr. UNION with Aliases We can use aliases in a MySQL statement of UNION operator to give a table or column a temporary name, which can be useful when working with multiple tables or columns with similar names. When using UNION with aliases, it”s important to note that the column aliases are determined by the first SELECT statement. Therefore, if you want to use different aliases for the same column in different SELECT statements, you need to use column aliases in all SELECT statements to ensure consistent column names in the final result set. Syntax Following is the syntax for using Union with Aliases − SELECT column1 AS alias1, column2 AS alias2 FROM table1 UNION SELECT column3 AS alias1, column4 AS alias2 FROM table2; Example In this following example, we are trying to combine two tables using aliases to represent the fields in result-set obtained − SELECT FNAME AS Firstname, LNAME AS Lastname, ADDRESS AS Address FROM PROSPECT UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE; Output Following output is obtained − Firstname Lastname Address Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. Grace Peterson 16055 Seminole Ave. Walter Brown 8602 1st St. UNION ALL Operator If you want to select all records, including duplicates, follow the first UNION keyword with ALL − SELECT fname, lname, ADDRESS FROM prospect UNION ALL SELECT fname, lname, ADDRESS FROM active; Output Following output is obtained − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. Grace Peterson 16055 Seminole Ave. Bernice Smith 916 Maple Dr. Walter Brown 8602 1st St. UNION

MySQL – Using Joins Table of content Types of Joins Joins Using a Client Program ”; Previous Next A Join clause in MySQL is used to combine records from two or more tables in a database. These tables are joined together based on a condition, specified in a WHERE clause. For example, comparing the equality (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc. We can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. Types of Joins There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are listed as follows − Inner Join − An Inner Join retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. This is a default join. Outer Join − An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial. Example In this example, we first create a table named CUSTOMERS using the CREATE TABLE query as follows − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 2), PRIMARY KEY(ID) ); Let us then insert the following records in the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); The table is created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 ORDERS Table − We create another table named ORDERS containing details of orders made by the customers, using the following CREATE TABLE query − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into the ORDERS table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Joining Tables − Now we write an SQL query to join these two tables. This query will select all the customers from table CUSTOMERS and will pick up the corresponding number of orders made by them from the ORDERS. SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID; Output The table is displayed as follows − ID NAME DATE AMOUNT 3 Kaushik 2009-10-08 00:00:00 3000.00 3 Kaushik 2009-10-08 00:00:00 1500.00 2 Khilan 2009-11-20 00:00:00 1560.00 4 Chaitali 2008-05-20 00:00:00 2060.00 Joins Using a Client Program In addition to Join two or more than two tables using the MySQL query we can also perform the Join operation using a client program. Syntax PHP NodeJS Java Python To join two or more than two MySQL tables through a PHP program, we need to perform the JOIN operation using the mysqli function query() as follows − $sql = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”; $mysqli->query($sql); To join two or more than two MySQL tables through a JavaScript program, we need to perform the JOIN operation using the query() function of mysql2 library as follows − sql = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”; con.query(sql); To join two or more than two MySQL tables through a Java program, we need to perform the JOIN operation using the JDBC function executeQuery() as follows − String sql = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”; st.executeQuery(sql); To join two or more than two MySQL tables through a Python program, we need to perform the JOIN operation using the execute() function of the MySQL Connector/Python as follows − using_join_query = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”

MySQL – On Delete Cascade Table of content The MySQL ON DELETE CASCADE Constraint ”; Previous Next The MySQL ON DELETE CASCADE Constraint The MySQL ON DELETE CASCADE constraint ensures that when a row in the parent table is deleted, all related rows in the child table are automatically deleted as well. This constraint helps maintain referential integrity between two tables that are connected through a foreign key relationship. If we do not use this constraint, the database server will, by default, prevent us from deleting data in a table if it is referenced by other tables. Example Creating the Parent Table − First, let us create the parent table with the name PERSONS using the following query − CREATE TABLE PERSONS( P_ID int primary key, P_NAME varchar(40), P_AGE int ); Now, let us insert some values into the above created table using the INSERT statement as shown below − INSERT INTO PERSONS VALUES (1, “Priya”, 29), (2, “Sarah”, 20), (3, “Varun”, 26), (4, “Dev”, 25), (5, “Ram”, 31), (6, “Aarohi”, 34); The PERSONS table obtained is as shown below − P_ID P_NAME P_AGE 1 Priya 29 2 Sarah 20 3 Varun 26 4 Dev 25 5 Ram 31 6 Aarohi 34 Creating the Child Table − Now, let us create a child table named Films_watched with the ON DELETE CASCADE constraint. In this table, the P_ID column is a foreign key referencing the P_ID column in the Persons table − CREATE TABLE Films_watched ( P_ID INT, F_NO INT, F_NAME varchar(40), PRIMARY KEY(P_ID,F_NO), FOREIGN KEY(P_ID) REFERENCES PERSONS(P_ID) ON DELETE CASCADE ); Now, we are inserting rows into the Films_watched table − INSERT INTO Films_watched VALUES (1, 130, “RRR”), (2, 131, “Bahubali”), (3, 132, “Pushpa”), (3, 133, “KGF”), (3, 134, “Salaar”), (6, 135, “Karthikeya”); The Films_watched table produced is as follows − P_ID F_NO F_NAME 1 130 RRR 2 131 Bahubali 3 132 Pushpa 3 133 KGF 3 134 Salaar 6 135 Karthikeya Deleting a Record from the Parent Table − As we can see in the above table, we have three films that has been watched by the person with P_ID = 3. Here, we are deleting the person with P_ID = 3 from the PERSONS (parent) table − DELETE FROM PERSONS WHERE P_ID = 3; Following is the output obtained − Query OK, 1 row affected (0.01 sec) After this deletion, let us check the data in both the Persons and Films_watched tables. Querying Data from the Persons Table − To see the remaining records in the Persons table, use the following SELECT query − SELECT * FROM PERSONS; We can see in the table below, the row with P_ID = 3 is deleted − P_ID P_NAME P_AGE 1 Priya 29 2 Sarah 20 4 Dev 25 5 Ram 31 6 Aarohi 34 Querying Data from the Films_watched Table − Lastly, you can check the data in the Films_watched table − SELECT * FROM Films_watched; Output We can see in the output below that all related records with P_ID = 3 have been automatically deleted − P_ID F_NO F_NAME 1 130 RRR 2 131 Bahubali 6 135 Karthikeya Print Page Previous Next Advertisements ”;

MySQL – Numeric Functions ”; Previous Next MySQL – Numeric Functions MySQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions that are available in the MySQL. Sr.No. Name & Description 1 ABS() Returns the absolute value of numeric expression. 2 ACOS() Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1. 3 ASIN() Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1 4 ATAN() Returns the arctangent of numeric expression. 5 ATAN2() Returns the arctangent of the two variables passed to it. 6 CEIL() Returns the smallest integer value that is not less than passed numeric expression 7 CEILING() Returns the smallest integer value that is not less than passed numeric expression 8 CONV() Converts numeric expression from one base to another. 9 COS() Returns the cosine of passed numeric expression. The numeric expression should be expressed in radians. 10 COT() Returns the cotangent of passed numeric expression. 11 CRC32() Returns the Cyclic Redundancy Check (CRC) for the given value. 12 DEGREES() Returns numeric expression converted from radians to degrees. 13 EXP() Returns the base of the natural logarithm (e) raised to the power of passed numeric expression. 14 FLOOR() Returns the largest integer value that is not greater than passed numeric expression. 15 FORMAT() Returns a numeric expression rounded to a number of decimal places. 16 HEX() returns the hexadecimal representation of the given value. 17 LN() Returns the natural logarithm of the passed numeric expression. 18 LOG() Returns the natural logarithm of the passed numeric expression. 19 LOG10() Returns the base-10 logarithm of the passed numeric expression. 20 LOG2() Returns the base-2 logarithm of the passed numeric expression. 21 MOD() Returns the remainder of one expression by diving by another expression. 22 PI() Returns the value of pi 23 POW() Returns the value of one expression raised to the power of another expression 24 POWER() Returns the value of one expression raised to the power of another expression 25 RADIANS() Returns the value of passed expression converted from degrees to radians. 26 RAND() Returns a random floating-point value with in the range 0 to 1.0. 27 ROUND() Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points 28 SIGN() Returns the sign of the given number. 29 SIN() Returns the sine of numeric expression given in radians. 30 SQRT() Returns the non-negative square root of numeric expression. 31 TAN() Returns the tangent of numeric expression expressed in radians. 32 TRUNCATE() Returns numeric exp1 truncated to exp2 decimal places. If exp2 is 0, then the result will have no decimal point. Print Page Previous Next Advertisements ”;

MySQL – RESIGNAL Statement Table of content The MySQL RESIGNAL Statement Handling Warnings with RESIGNAL Resignal Statement Using Client Program ”; Previous Next When working with stored procedures in MySQL, it is important to manage exceptions that may arise during their execution. These exceptions could otherwise lead to an abrupt termination of the procedure. To address this issue, MySQL offers a way to handle exceptions through error handlers. These handlers can be declared using the DECLARE … HANDLER statement. The MySQL RESIGNAL Statement The MySQL RESIGNAL statement is used to provide error information to handlers, applications, or clients when an exception occurs within a stored procedure. RESIGNAL is specifically used within error handlers and must always include attributes. These attributes specify the SQL state, error code, and error message to be associated with the raised error. Customizing Error Messages The RESIGNAL statement allows you to customize error messages using the SET MESSAGE_TEXT command, ensuring smoother procedure execution. Syntax Following is the syntax of the MySQL RESIGNAL Statement − RESIGNAL condition_value [SET signal_information_item] Where, condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”. signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME. Example In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than BBA, BCA, MD and ITI, an error message is generated using the RESIGNAL statement − DELIMITER // CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50)) BEGIN DECLARE wrong_choice CONDITION FOR SQLSTATE ”45000”; DECLARE EXIT HANDLER FOR wrong_choice RESIGNAL SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; IF degree=”BBA” THEN SET full_form = ”Bachelor of Business Administration”; ELSEIF degree=”BCA” THEN SET full_form = ”Bachelor of Computer Applications”; ELSEIF degree=”MD” THEN SET full_form = ”Doctor of Medicine”; ELSEIF degree=”ITI” THEN SET full_form = ”Industrial Training Institute”; ELSE SIGNAL wrong_choice; END IF; END // DELIMITER ; You can call the above procedure to retrieve the result as shown below − CALL example(”MD”, @fullform); You can retrieve the value of the variable using the following SELECT statement − SELECT @fullform; Following is the output obtained − @fullform Doctor of Medicine If you pass an invalid value to the procedure, it will generate an error message as follows − CALL example (”IIT”, @fullform); The output obtained is as follows − ERROR 1001 (45000): Given degree is not valid Handling Warnings with RESIGNAL Let us see another example where we do not pass optional attributes to the RESIGNAL statement − DELIMITER // CREATE PROCEDURE testexample (num INT) BEGIN DECLARE testCondition1 CONDITION FOR SQLSTATE ”01000”; DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL; IF num < 0 THEN SIGNAL testCondition1; END IF; END // DELIMITER ; You can call the above procedure by passing two values. But, any SQLSTATE value that starts with ”01” refers to a warning, so the query is executed with a warning as shown below − CALL testexample(-15); The output obtained is as follows − Query OK, 0 rows affected, 1 warning (0.00 sec) Resignal Statement Using Client Program We can also perform resignal Using Client Program. Syntax PHP NodeJS Java Python To perform the resignal statement through a PHP program, we need to execute the “Stored Procedure” using the mysqli function query() as follows − $sql = “CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE RESIGNAL SQLSTATE ”01000” SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; RESIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END”; $mysqli->query($sql); To perform the resignal statement through a JavaScript program, we need to execute the “Stored Procedure” using the query() function of mysql2 library as follows − var createProcedureSql = ` CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE RESIGNAL SQLSTATE ”01000” — Raise a warning SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; RESIGNAL SQLSTATE ”45000” — Raise an error SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END`; con.query(createProcedureSql); To perform the resignal statement through a Java program, we need to execute the “Stored Procedure” using the JDBC function execute() as follows − String sql = “CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE RESIGNAL SQLSTATE ”01000” SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; RESIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END”; statement.execute(sql); To perform the resignal statement through a Python program, we need to execute the