MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Derived Tables
MySQL Derived Tables
The Derived tables are pretty much what their name describes: they are the tables that are derived from another MySQL database table (main table). In other words, the derived table is a virtual result-set obtained from a SELECT statement given as a subquery to another SELECT statement of the main table.
This table is similar to a temporary table. But unlike temporary tables, you need not create a derived table separately; the records in it are retrieved from the main table using a subquery. Therefore, similar to the actual database table, a derived table can also be displayed as a result-set of computations, aggregate functions, etc.
Syntax
Following is the basic syntax to display a derived table in MySQL 
SELECT column_name(s) FROM (subquery) AS derived_table_name;
Example
Let us see a simple example demonstrating how derived table is displayed in MySQL. In the following query, we are creating a new table CUSTOMERS 
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)
);
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 );
To retrieve the records of the CUSTOMERS table, execute the following query 
SELECT * FROM CUSTOMERS;
Following are the records present in 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
Now, we are retrieving a derived table from this CUSTOMERS table using the following query 
SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;
The derived table DERIVED_CUSTOMERS is hence obtained with ID, NAME and SALARY as its attributes.
ID
NAME
SALARY
1
Ramesh
2000.00
2
Khilan
1500.00
3
Kaushik
2000.00
4
Chaitali
6500.00
5
Hardik
8500.00
6
Komal
4500.00
7
Muffy
10000.00
Using WHERE Clause
We can also use the WHERE clause to filter records (or rows) from the derived table. Following is the syntax for it 
SELECT column_name(s) FROM (subquery) AS derived_table_name WHERE [condition];
Example
In the following query, we are retrieving a derived table from the CUSTOMERS table created initially. We are doing this by filtering rows from it using the WHERE clause 
SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS 
WHERE DERIVED_CUSTOMERS.SALARY > 5000.00;
Executing the query above will produce the following output 
ID
NAME
SALARY
4
Chaitali
6500.00
5
Hardik
8500.00
7
Muffy
10000.00
Aliasing a Column in Derived Table
In derived tables, not only the table name, but we can also alias a column name while displaying the contents. Following is the syntax 
SELECT column_name(s) AS alias_name(s) FROM (subquery) AS derived_table_name;
Example
In the example below, we are displaying the derived table from the CUSTOMERS table with the aliased columns using the following query
SELECT ID AS DERIVED_ID, NAME AS DERIVED_NAME, SALARY AS DERIVED_SALARY 
FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS;
Output
Executing the query above will produce the following output 
DERIVED_ID
DERIVED_NAME
DERIVED_SALARY
1
Ramesh
2000.00
2
Khilan
1500.00
3
Kaushik
2000.00
4
Chaitali
6500.00
5
Hardik
8500.00
6
Komal
4500.00
7
Muffy
10000.00
Displaying Aggregate Functions as Derived Tables
We can also show the result of an aggregate function or calculations performed on the main table's records as a derived table.
Following is the syntax to display aggregate functions as a derived table 
SELECT function_name() FROM (subquery) AS derived_table_name;
Example
In the following query, we are using the aggregate SUM() function to calculate the total salary from the CUSTOMERS table 
SELECT SUM(SALARY) FROM (SELECT SALARY FROM CUSTOMERS) AS DERIVED_CUSTOMERS;
Output
Executing the query above will produce the following output 
SUM(SALARY)
35000.00
Example
In the following query, we use the aggregate AVG() function to calculate the average salary of customers from the CUSTOMERS table.
SELECT AVG(DERIVED_SUM) AS AVERAGE_SALARY 
FROM (SELECT SUM(SALARY) AS DERIVED_SUM FROM CUSTOMERS) AS DERIVED_CUSTOMERS;
Output
Executing the query above will produce the following output 
AVERAGE_SALARY
35000.000000
Deriving Table Using a Client Program
Besides using MySQL queries to derive a table from another database table (main table), we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.
Syntax
Following are the syntaxes of this operation in various programming languages 
PHPNodeJSJavaPython
To derive a table from another database table through PHP program, we need to execute the SELECT statement using the mysqli function query() as follows 
$sql="SELECT col_1, col_2 FROM table_name WHERE col_name IN (SELECT col_name FROM table_name)";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
﻿
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.');
﻿
//derived table (sub query)
$sql = "SELECT tutorial_title, tutorial_author FROM tutorials_table WHERE tutorial_id IN (SELECT tutorial_id FROM tutorials_table);";
if ($result = $mysqli->query("$sql")) {
    printf("Derived table query worked successfully!.");
    while ($res = mysqli_fetch_array($result)) {
        print_r($res);
    }
}
if ($mysqli->errno) {
    printf("Derived table could not be worked!.", $mysqli->error);
}
﻿
$mysqli->close();
Output
The output obtained is as follows 
Derived table query worked successfully!
Array
(
    [0] => MySQL
    [tutorial_title] => MySQL
    [1] => Aman kumar
    [tutorial_author] => Aman kumar
)
Array
(
    [0] => Python
    [tutorial_title] => Python
    [1] => Sarika Singh
    [tutorial_author] => Sarika Singh
)
﻿
﻿
﻿
﻿
﻿
﻿

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Derived Tables

MySQL Derived Tables

Syntax

Example

Using WHERE Clause

Example

Aliasing a Column in Derived Table

Example

Output

Displaying Aggregate Functions as Derived Tables

Example

Output

Example

Output

Deriving Table Using a Client Program

Syntax

Example

Output