Some Useful My Sql Queries for beginners

My Sql is a popularly used Relational Database Management System.  Database is used store and retrieve data efficiently with the proven algorithms. Here i'm providing some sql commands which are useful for beginners in my sql queries.

Here information_schema is the default database comes with Mysql installation.

>To check the engine used for the table - mytable

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_NAME='mytable' AND TABLE_SCHEMA='mydatabase';

>To see the database create statement

SHOW CREATE DATABASE mydatabase;

>TO see the current database name

SELECT DATABASE();

>To see the table structure (create table statement)

SHOW CREATE TABLE mydatabase.mytable;

> Describes the all the columns and datatypes for the table

DESCRIBE mytable (or) DESC mytable;

>To see the warnings of the previous command

SHOW WARNINGS;

>To concatenate (Combine) and show the column vaues

SELECT CONCAT(firstname, ' . ', lastname) AS `Full Name`, marks FROM mytable;

>To Select rows in random order every time

SELECT * FROM mytable ORDER BY RAND();

>  To retrieve multiple rows with the given values, for example to fetch 3 records data with column "uid" values as 5,6,8

SELECT * FROM mytable WHERE uid IN (5,6,8);

> To get summary report for the groups use GROUP BY aggregate functions(COUNT, MIN, MAX, AVG, SUM...) on the groups, for example to get the total number of students for each branch

SELECT COUNT(*) from mytable GROUP BY branchname;

• To get the average marks in subject-wise

 SELECT AVG(marks) from classtable GROUP BY subjectname;

• To get the sum of the items in the stock

 SELECT SUM(items) from stock GROUP BY itemname;

>To combine the results of two or more tables(identical) use UNION, for example to get the highest marks in the particular subject from class9table, class10table

SELECT MAX(science) FROM (SELECT MAX(science) from class9table UNION SELECT MAX(science) from class10table) as t1