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
Database Articles
Page 265 of 547
How can the rows be sorted out in a meaningful way?
For sorting the rows in a meaningful way we can use ORDER BY clause. Suppose we want to sort the rows of the following table −mysql> Select * from Student; +--------+--------+--------+ | Name | RollNo | Grade | +--------+--------+--------+ | Gaurav | 100 | B.tech | | Aarav | 150 | M.SC | | Aryan | 165 | M.tech | +--------+--------+--------+ 3 rows in set (0.00 sec)The query below sorted the table by ‘Name’.mysql> Select * from student order by name; +--------+--------+--------+ | Name | RollNo | Grade | +--------+--------+--------+ | Aarav | ...
Read MoreHow can we delete a MySQL stored function from the database?
If we have ALTER ROUTINE privileges then with the help of DROP FUNCTION statement, we can delete a MySQL stored function. Its syntax can be as follows −SyntaxDROP FUNCTION [IF EXISTS] function_nameHere function_name is the name of the function which we want to delete from our database.Examplemysql> DROP FUNCTION if exists Hello1; Query OK, 0 rows affected (0.70 sec)Now after deleting the function, check for the CREATE FUNCTION statement and we will get the error as follows −mysql> SHOW CREATE FUNCTION Hello1; ERROR 1305 (42000): Function Hello1 does not exist.
Read MoreCreate a MySQL stored procedure that counts the number of rows gets affected by MySQL query?
Following is a procedure that counts the number of rows get affected by MySQL query −mysql> Delimiter // mysql> CREATE PROCEDURE `query`.`row_cnt` (IN command VarChar(60000)) -> BEGIN -> SET @query = command; -> PREPARE stmt FROM @query; -> EXECUTE stmt; -> SELECT ROW_COUNT() AS 'Affected rows'; -> END // Query OK, 0 rows affected (0.00 sec) mysql> Delimiter ; mysql> Create table Testing123(First Varchar(20), Second Varchar(20)); Query OK, 0 rows affected (0.48 sec) mysql> CALL row_cnt("INSERT INTO testing123(First,Second) Values('Testing First','Testing Second');"); +---------------+ | Affected rows | +---------------+ | 1 | +---------------+ 1 row in set (0.10 sec) Query OK, 0 rows affected (0.11 sec)
Read MoreWhat kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
MySQL scalar subquery returns exactly one column value from one row and we can use it where a single column is permissible. Followings are the cases when scalar subqueries return value other than one row −Case1 − When it returns 0 rowsIn case if the subquery returns 0 rows then the value of scalar subquery expression would be NULL.Case2 − When it returns more than one rowsIn case if the subquery returns more than one row then, due to the property of scalar subquery, MySQL returns an error.It can be understood with the help of an example which uses the ...
Read MoreHow can we use a MySQL subquery with INSERT statement?
It can be understood with the help of an example in which we would copy the values of a table into other table. We are using the data from table ‘cars’ and copy its data to table ‘copy_cars’ −mysql> CREATE TABLE copy_cars LIKE cars; Query OK, 0 rows affected (0.86 sec) mysql> SELECT * from copy_cars; Empty set (0.08 sec)The following query using the subquery will insert the values same as ‘cars’ to table ‘copy_cars’ −mysql> INSERT INTO Copy_cars Select * from Cars; Query OK, 8 rows affected (0.07 sec) mysql> SELECT * from copy_cars; +------+--------------+---------+ | ID ...
Read MoreHow can I convert 1st January of the current year into epoch?
It can be done by using UNIX_TIMESTAMP() function as follows −mysql> Select UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01')); +--------------------------------------------------+ | UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01')) | +--------------------------------------------------+ | 1483209000 | +--------------------------------------------------+ 1 row in set (0.03 sec)It can be verified by the following query −mysql> Select FROM_UNIXTIME(1483209000); +---------------------------+ | FROM_UNIXTIME(1483209000) | +---------------------------+ | 2017-01-01 00:00:00 | +---------------------------+ 1 row in set (0.02 sec)
Read MoreHow can I convert the epoch stored in MySQL table into readable dates?
To illustrate it we are using the following example of a table named ‘vistors’ which have the epoch as follows −mysql> Create table visitors(userid int not null, name varchar(20), epoch int NOT NULL); Query OK, 0 rows affected (0.42 sec) mysql> Insert into visitors Values(1, 'Gaurav', 1358658942); Query OK, 1 row affected (0.04 sec) mysql> Insert into visitors Values(2, 'Raman', 1358659585); Query OK, 1 row affected (0.04 sec) mysql> Select userid, name, FROM_UNIXTIME(epoch) from visitors; +--------+--------+----------------------+ | userid | name | FROM_UNIXTIME(epoch) | +--------+--------+----------------------+ | 1 | Gaurav | 2013-07-24 16:05:42 | | 2 | Raman | 2013-07-24 16:16:25 | +--------+--------+----------------------+ 2 rows in set (0.00 sec)
Read MoreWhat are MySQL group functions?
Group functions are the function which applied to a group of rows or in other words group functions operate on sets of values. The following table gives the Description of MySQL group functions −Sr.No.Name & Description1AVG()It returns the average value of the argument.2BIT_AND()It returns bitwise AND.3BIT_ORIt returns bitwise OR.4BIT_XOR()It returns bitwise XOR.5COUNT()It returns a count of the number of rows returned.6COUNT(DISTINCT)It returns the count of a number of different values.7GROUP_CONCAT()It returns a concatenated string.8MAX()It returns the maximum value.9MIN()It returns the minimum value.10ST()It returns the population standard deviation.11STDDEV()It returns the population standard deviation.12STDDEV_POP()It returns the population standard deviation.13STDDEV_SAMP()It returns the sample ...
Read MoreHow can I write a MySQL stored function that calculates the factorial of a given number?
Following is the example of a stored function that can calculate the factorial of a given number −CREATE FUNCTION factorial (n DECIMAL(3,0)) RETURNS DECIMAL(20,0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20,0) DEFAULT 1; DECLARE counter DECIMAL(3,0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END // mysql> Select Factorial(5)// +--------------+ | Factorial(5) | +--------------+ | 120 | +--------------+ 1 row in set (0.27 sec) mysql> Select Factorial(6)// +--------------+ | Factorial(6) | +--------------+ | 720 | +--------------+ 1 row in set (0.00 sec)
Read MoreWhat are MySQL subqueries and its general categories?
A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at runtime. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.A subquery can be used any place where an expression is allowed ...
Read More