MySQL & SQL free tutorials queries & Scripts

Primary Key is unique and not null constraint of the column to identify the row and we can use one Primary Key for one table.

Primary Key constraint

Installation
Installing and Managing MySQL database

PHP MySQL
Php functions to manage MySQL database

SQL Introduction
Types of Query and about SQL

Standard SQL functions ( commands )

Functions	Description
AVG	Average value of a column and other math functions
Alter	Changing the table Structure
Between	Records between two ranges
CASE	Condition check with select query
Count	Number of records in a table with different combinations
Copy Table	Copy data from one table to another table
CONCAT	Joining and adding strings to columns
Create Table	Create table by using SQL
Database	Create , display and delete Database
Delete	Delete records from table
Drop	Deleting table or table column
Distinct	Unique records of a table
Group by	Grouping data of a column of MySQL table
Group_concat	Grouping string data to single column
Having	Matching data by Having command with group by
Insert	Adding records to a table
IS	Is operator to check against Boolean values
IF	Checking Condition with Group By
Insert Set	Adding record with matching column and value
Inner Join	Joining table to itself
IN	Matching record with set of data
Left join	Joining two tables and getting matching records with unique combination
Limit	Getting limited records with starting and ending limit
Like	Matching record with wildcard and formatting
Locate	Matching record and getting position of the search string
MAX	Highest number of a column ( math functions: min, sum, average etc )
NULL value	Missing or unknown data
Over()	Grouping window function using over() and partition
Order By	displaying A to Z or Maximum to Minimum data
OR AND	Logical operators to use in a query
Rand	Random records from a table
Replace	Updating part of data
REGEXP	Regular expression to match pattern in string data
Rename Table	Rename table by query
Select Query	Select record from a table
Show Tables	Listing of Database and Tables
Subqueries	Query inside a query
Union	Joining tables and getting records
Update	Update record with new data
Where	Getting records with conditions

MySQL Functions

Functions	Description
Date & Time	Managing Date & time field of MySQL table by formatting outputs and applying calculations on year, month, day etc.
Math Functions	Counting total, average, Sum of a range of records by using Math Functions
String Functions	to use get length, lower case, repeat, reverse etc.

Joining More than one table

Functions	Description
left join	Managing Date & time field of MySQL table by formatting outputs and applying calculations on year, month, day etc.
left join II	Left join using Multiple tables
left join III	Example of Left Join using multiple tables
RIGHT JOIN	RIGHT Outer join of tables using ON columns
Linking Table	Displaying records connecting more than one table
Union	Join two tables and display unique records
Inner Join	Joining tables with itself and displaying records
CROSS Join	Joining all rows of both tables

Collecting records from table

Functions	Description
Record exists	Record is already available in a table ( checking userid )
second highest	Getting the second highest number in a table column
sub groups	Grouping within a group by sub-groups
Changing order	Displaying highest or lowest or changing the display order
Sum storing	Inserting sum of a column in other table
Length of Data	Length of characters present in a field
CSV Data	Download table rows as CSV file data

Exercise Try these Queries to understand how to retrieve , update and manage data

More on SQL and Database Management

Functions	Description
Keyword Search	Generate SQL from the input search string and apply
Dynamic Search	Generate Query based on various input combinations
SQL Security	database vulnerability and injection attacks
Excel to MySQL	Shifting records from Excel to MySQL

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

	17-06-2020
SELECT STUDENTID,CASE WHEN MARKS < 50 THEN 'FAIL' ELSE TO_CHAR(Marks) END AS STATUS FROM ASSESSMENT