My SQL Learn Easy Notes

Introduction

  

Data

Known as raw or known facts i.e. data that has not been processed.

Information

Processed data is known as information.

Database

It is a collection of data that is related in some way.

Hierarchy of Data

Character Consists of letters (A-Z), digits(0-9) and special Characters (-, + , * etc).

Field(Column) Collection of related characters e.g. name, age etc.

Record(Row) Collection of related fields e.g. information of student i.e. his name, age, address, roll no etc. is a record.

File(Table)Collection of related records is file.

Database  Collection of one or more related files is a Database.

DBMS

Is a collection of programs that enables user to create and maintain a database.  It is used to define, construct, and manipulate data in database for variaous applications.  It provides information storate, organization and retrieval capabilities.

Advantages of DBMS

1. Redundancy is controlled.
2. Unauthorised access is restricted.
3. Provides multiple user interfaces.
4. Enforces integrity constraints.
5. Provides backup and recovery of data.

Limitations of DBMS

1. Uses extra computer time and resources.
2. It is expensive to install new systems.
3. Requires well qualified and expert personnel to operate the system.
4. It is not designed for concurrent access to many users it will be of little use.
5. Authority of ownership rights over data have to be fixed, so as to determine who is eligible for using the data.

Record Relationships

Relationships are vital part of the database.  Main importance of a relationship are

·Establishes connection between a pair of tables that are logically related to each other.

·It help to refine a streamline table structure and helps to remove data redundancy.

·Data from many tables can be extracted at the same time if there is relationship.

·A well defined relationship helps maintain a high level of integrity.

Types of Relationship

One to One

One parent record is associated with only one child record in the second table.

One to Many

A one parent record is associated with many child records in the second table. 

Many to One

Two or more parent record is associated with many child records.

Many to Many

Two or more parent records are associated with two or more child records in the second table.

Primary Key

Primary keys identifies each Unique row in a table.

Candidate Key

In a table there could be more than one column that uniquely identifies a row in table.  Such columns are called as candidate key.  From among candidate keys we can choose one to to be primary key, or a combination of them to be primary key.

Foreign Key

Used to establish parent child relationship between two or more tables having a common column.  The column in a parent table is defined as a primary key and column in  a child table is defined as a foreign key referring to corresponding values in a parent table.  Thus a foreign key is the primary in another table.

SQL(Strucctured Query Language)

The Structured Query Language allows users of the database to communicate with the database.  Data in relational database can be retrieved using a Standard language like SQL.

Parts of SQL(SQL Statement)

The aim of SQL was to provide the users with a medium to ask for the data they required.  So the commands of SQL are classified into four categories.

• Data retrieval commands.
• Data Manipulation Language (DML).
• Data Definition Language (DDL).
• Data Control Language (DCL) 

Data Retrieval Commands

This category contains only one command SELECT.  It selects a requested data from the table and displays it to the user on the screen.

Data Manipulation Language

DML commands allow the user to manipulate data.  It refers to insertion, updating and deletion of data.

Data Definition Language

DDL commands allow modification of database structure by creating, replacing altering or removing database objects such as tables.

Data Control Language

DCL commands protect integrity of data by controlling and managing access to the database.  Thus SQL specifies task of creating, manipulating and communicating with the database.

Basic Parts of Speech in SQL

1.A verb, Like Select, Create or Update.

2.Column specification, which defines what has to be selected.

3.The Source, which is the name of the table from which columns have to be selected.

4.A condition, which specifies the condition based on which the column will be selected.

Check Constraint

Is used to restrict the column to have some specific values or range of values i.e conditional entry of details.

Not Null Constraint

Will not allow the user to leave a column Empty.

Unique Constraint

Used to prevent duplicate values from being entered in a column.  We can insert null values as well.

SQL Basics

Database

Database in MYSQL contains one or more tables.

Table

Tables are consist of rows and columns.  Where rows are the horizontal part of the table and the columns are the vertical part of the table.  Columns specify data type and Row contains actual data.

Data Types

MySQL supports three categories of data types they are Strings, Numeric and Date/Time.

Char/String

This data type can not be used for mathematical calculations and manipulations for example:- names, address etc.

Char(width)

Used to store fixed length character strings which can contain alphabets, numbers and special symbols.  It can hold up 255 bytes.  If the length of string entered is less than maximum width specified, then the value is blank padded to the given length so that each record in that column acquires same amount of memory space.  In either case if user enters a value larger than specified width then database would return an error.  Comparison and sorting of CHAR is case sensitive.

Varchar(width)

Used to support variable length character strings.  This data type is similar to char but is used instead of char in order to save memory space as varchar data type uses only that amount of memory that is required by the value entered in the column.  Its maximum length is 65,535 bytes(64 KB).  Comparison and sorting of VARCHAR is case sensitive.

Note:-  Data stored in both CHAR and VARCHAR when compared are not case sensitive.

Numeric Data Type

Used to hold integer and floating point numbers, either positive or negative.  Alphabets and special symbols are not allowed in this data type.  Integer Data types (smallint, bigint) can store only integers while fractional data type(float, double, decimal supports the use of decimals.

Type                  Storage

SMALLINT       2 bytes

BIGINT              8 bytes

FLOAT(p,s)       4 bytes

DOUBLE(p,s)    8 bytes

DECIMAL(p,s)  p+1 bytes

where

p = precision (Default precision is 10)

s = scale (Default precision is 0)

Boolean Data(TRUE/False)

Represented by constants TRUE or FALSE which evaluates to 1 and 0 respectively.

Temporal Data Type

Represents Date and Time as follows:-

Type      Format                 Storage

DATE    YYYY-MM-DD       3 Bytes

YEAR    YYYY                      1 Byte

TIME    HHH:MM:SS          3 Bytes

DATETIME 
YYYY-MM-DD HH:MM:SS     8 Bytes

TIMESTAMP             
YYYY-MM-DD HH:MM:SS     4 Bytes

Timestamp is same as datetime but it records the date and time of an insert or update operations automatically.

Uses of Different prompts in MySQL

mysql> ready for new command

->waiting for next line multiple command

‘>waiting for next line string completion that begin with single quote.

“>waiting for next line string completion that begin with double quote.

`>waiting for next line completion of identifier that begin with back tick

/*>waiting for next line completion of comment that begin with back /*.

Creating Database

To work in MySQL we need to create database and that can be created using Create Command.  And to view which databases already exist on the server Show Databases Command is used.  And to work on a particular database we need to use, Use Command.

mysql> show databases;

mysql> create database sandhya;

mysql>use sandhya;

Creating Tables

Tables can be created using Create Table Statement.  The Create table command can be used with name of the table and the columns we want in this table along with its data type.

Syntax:- Create Table <Table Name> (Column Name 1 Data- type (size) …..Column N Data-type (size));

MySQL>Create Table Emp (Emp_code int (4),Add1 Varchar(20), Add2 Varchar(20),sal int(7,2), DA int(5),TA int(5));

Viewing Table Structure

Table structure can be viewed by DESC Statement.

Syntax: Desc <Table Name>

MySQL>Desc Emp

UNSIGNED:-

Is used to indicate that values of a column are positive.

NOT NULL

Column value can not be empty.

DEFAULT

This is used to give a column a fixed value, if no value is assigned to it.

e.g SALARY Deimal(7,2) UNSIGNED DEFAULT 1000

AUTO_INCREMENT

The Auto_Increment is used to indicate that MySQL should automatically generate a number for that column as the value of previous row+1.  This can only be used with integer data type and there can be only one AUTO_INCREMENT column in a table.

e.g. PR_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT

Keys

• Keys ensures that each record in the table is properly identified.
• They help establish and enforce integrity rules.
• They help establish relationship between tables.
• They enable faster searches in a table.

Primary Keys

A primary key is the Unique key in the table.  A table can have only one primary key  Important elements of primary key:-

1) It must uniquely identify each record.

2)     It must contain unique values.

3)                  It cannot be a NULL field.

4)                  It cannot be a multi-part field.

5)                  It should contain a minimum number of fields necessary to be called unique.

6)                  Its value cannot be modified except in very rare cases.

e.g. CONSTRAINT PK PRIMARY KEY(Emp_code)

Rules for Establishing A Primary Keys

1)                  Each table must have only one primary key.

2)                  Each table within a database should have a unique primary key.

Foreign Keys

Foreign key is a copy of a primary key in another table.  The key connects to another table when a relationship is being established.

Important elements of Foreign Keys are

• It has the same name as primary key from which it was copied.
• Field specifications of primary key from which it was copied have to be the same for the foreign key too.
• Values of foreign key are a subset or are drawn from the primary key.

Inserting Data in Table

With INSERT statement records can be added into a table.

MySQL> Insert into Emp

                Values( ‘1001’, ‘9 Avenue’, ‘Thane’, 5000.50,300,400);

MySQL> Insert into Emp

               Values( ‘1002’, ‘101 Priya Apt’, ‘Kalyan’, 9500.50,500,900);

MySQL> Insert into Emp

               Values( ‘1003’, ‘09 Krish Apt’, ‘Thane’, 8900.50,450,800);

MySQL> Insert into Emp

                Values( ‘1004’, ‘29 Anupam Soc’, ‘Mulund’, 7000.80,650,900);

Inserting the Partially Filled Records in to a Table

We can insert data to a selected columns with the INSERT statement as

MySQL> Insert into Emp (Emp_code, TA)

           Values (1005,550);

Displaying Data

Data entered into the table can be seen by giving Select Statement.

NOTE:* (Astrics) indicates all columns of the table are to be viewed.

MySQL> Select * from Emp

To view Selective Columns

Selective columns can be viewed by entering column names separated by comma.

MySQL> Select Emp_code, Add1 from Emp;

Creating Tables Using another Table

A table can be created using the columns and data from another table.

MySQL>Create Table Emp1 Select Emp_code,Add1,sal from Emp;

MySQL>Create Table Emp1 Select Emp_code,Add1,sal from Emp where Add2=’Thane’;

Alter Statement

This is used to add, modify and delete columns and also to rename the table.

Alter Statement (Add)

MySQL> Alter table emp Add TelNo int(8);

To Add a Specific Field after another field:-

MySQL> Alter table emp Add Empname varchar (10) after Emp_code;

Alter Statement (Modify)

This is used to change the size of column in the table.

MySQL> Alter table emp Modify Empname varchar (20);

Alter Statement (Change Column Name)

This is used to change the column name in the table.

MySQL> Alter table emp Change Empname EName varchar (20);

Alter Statement (Deleting Column)

This is used to Delete the column name from the table.

MySQL> Alter table emp Drop TelNo;

Alter Statement (Deleting/Adding Primary Key)

Can also be used to delete and adding the Primary Key in the table.

MySQL> Alter table emp Add Primary Key (Emp_code);

MySQL> Alter table emp Drop Primary Key;

Alter Statement (Renaming the Table)

This can also be used to rename the name of the table.

MySQL> Alter table emp rename NewEmp;

Update and Set Statements

The Update statement is used to modify the data in the rows of the table.

MySQL> Update emp set Add2= ‘Mulund’ where Emp_code=1004;

Delete Statement

To delete a specific row this can be used.

MySQL> Delete from emp where Emp_code=1004;

To delete all rows this can also be used with.

MySQL> Delete from emp;

To Delete a Table Drop Table Command is used.

MySQL> Drop Table emp;

Built In Functions in MySQL

Inbuilt functions in MySQL are readymade functions which can be used directly.  They are As follows:-

  

STRING FUNCTIONS

Lower(str)

Returns characters in Lower Case.

MySQL>         Select Lower(‘SANDHYA’);

Upper(str)

Returns characters in Upper Case.

MySQL>         Select Upper (ename) ENAME from emp;

MySQL>         Select Upper(‘shruti’);

Length(str)

Returns the length of the input string.

MySQL>         Select Length(Ename) from Emp;

MySQL>         Select Length(‘This is my Place’) from Emp;

Ltrim(str)

Returns string leading spaces removed.

MySQL>         Select LTrim (‘        Amitabh’);

Rtrim(str)

Returns string with the trailing spaces removed.

MySQL>         Select LTrim (‘Amitabh                  ’);

Trim(str)

Returns string with both leading and trailing spaces removed.

MySQL>         Select LTrim (‘                   Amitabh                  ’);

Left(str, len)

Returns LeftMost len Characters from the str.  If len is 0 or negative an empty string is returned

MySQL>         Select Left(“Sandhya”,3);

Right(str, len)

Returns RightMost len Characters from the str.  If len is 0 or negative an empty string is returned

MySQL>         Select Right(“Sandhyashree”,3);

           

Mid(str,pos,len) or SubString(str,pos,len)

Returns characters from the middle of a text string, given a starting position pos and length. If len is omitted, then all characters starting with position pos are obtained.  If len is zero or negative an empty string is returned.

MySQL>Select MID("sandhyashree",4,3);

MySQL>Select SUBSTRING("sandhyashree",4,3);

Concat (str1, str2, ...)

Combines first string with second string.  It returns Null if any of the arguments are NULL.

MySQL>         Select concat('Sandhya','chatarji');

DATE FUNCTIONS

Now ( )           

Returns Current System date and time as ‘YYYY-MM-DD HH:MM:SS’

MySQL>         Select Now( )  ;

Time(expr)

Extracts the time from datetime expr.

MySQL>         Select Time(‘2009-11-01 16:19:40’)   ;

Day (date)      

Returns the day of the month for the date, as a number from 1 to 31.

MySQL>         Select Day((‘2009-11-01’);

Month(date )

Returns the day of the month for the date, as a number from 1 to 12

MySQL>         Select Month(‘2009-11-01’);

Year (year)

Returns the year for the date, as a number in the range 1000 to 9999

MySQL>         Select Year(‘2009-11-01’);

Dayname ( )

Returns name of the weekday for the specified date

MySQL>         Select Dayname(‘2009-11-01’);

Monthname ( )

Returns name of the weekday for the specified date

MySQL>         Select Monthname(‘2009-11-01’);

Date (expr )

Returns the date part of the date or datetime expression expr

MySQL>         Select Date(‘2009-11-01 16:20:40’);

NUMERIC FUNCTIONS

1)      Abs(x)

Returns the Absolute value of input i.e. value without its sign.

MySQL>         Select Abs(-90);

2)      Exp(x)

Returns the value of ex

MySQL>         Select Exp(2);

3)      Mod(x,y)

Returns remainder from the divisor of the provided numbers.

MySQL>         Select Mod(20,3);

4)      Pow(x,y)

It raise the value to a given integer exponent.

MySQL>         Select pow(5,2);

5)      SQRT(x)

Returns the square root of the given number.

MySQL>         Select SQRT(5);

6)      Round(n,d)

Rounds the number of digits of precision.  Default precision is Zero.  If d is omitted or 0 it returns nearest integer.  If d is -1 then it rounds n to the nearest 10.

MySQL>         Select Round(469.564,2);

Some other built in functions in MySQL are as follows:-

ASCII(string)                          Returns ASCII value of leftmost character in the string.

LPAD(string,len,padstring)      Returns string left padded with padstring until it is len characters long.

RPAD(string,len,padstring)      Returns string right padded with padstring until it is len characters long.

UCASE(string)                       Returns the string in uppercase.

LCASE(string)                        Returns the string in lowercase.

CHAR(numeric)                      Returns the ascii character of the number.

REVERSE(string)                   Returns the reverse of the string.

SPACE(numeric)                    Returns the string having that many blanks.

REPEAT(string,n)                   Returns the same string repeated n number of times.

LEAST(value1, value2..)        Returns the minimum value from among the values which can be

                                              numbers,alphabets or dates.

GREATEST(value1, value2)   Returns the maximum value from among the values which

                                              can be numbers, alphabets or dates.

SIGN(Y)                                Returns the sign of Y

FLOOR(Y)                            Returns the largest integer not greater than Y

CEILING(Y)                          Returns the smallest integer not less than Y

DAYOFWEEK(date)             Returns a number from 1 (Sunday) to 7 (Saturday)

HOUR(time)                           Returns the hours from the time.

DAYOFYEAR(date)              Returns a number from 1 to 366 depending on the date

Creating and Retriving Data From MySQL Table

Create Statement is used to create a table.  Now create following DEPT table with Create Table command:-

Emp_code       int(4)

Emp_Name     varchar(20)

DeptID            smallint(6)

City                 varchar(20)

MgrID             smallint(6)

Salary              decimal(7,2)

DOJ                 date

Insert Statement is used to insert records in the table.  Now we will insert some number of records at the same time as follows:-

MySQL>         insert into Dept values

                        (1, ‘Sandhya Banerjee’ 101, ‘Jaipur’,515,45000,’1990-03-05),

                        (2, ‘Anita Mukadam’ 102, ‘Mumbai’,512,35000,’1999-02-09),

                        (3, ‘Sheetal Mane’ 103, ‘Pune’,515,40000,’1995-03-02),

                        (4, ‘Priya Kapoor’ 104, ‘Thane’,511,385000,’2001-05-05);

Select Statement is used to retrieve rows selected from one or more tables.  It is used to perform queries on tables.

MySQL>         Select * from DEPT;

MySQL>         Select Emp_code,Emp_Name,Salary from DEPT;

A column can be labeled with another name, by writing that other name after it(called its alias), Separated by AS clause:-

MySQL>         Select Emp_code As Employee Code from DEPT;

Calculations can be done using select statement :-

MySQL>         Select Emp_Name, salary,salary*.10 from DEPT;

Where clause is used along with SELECT statement to give a condition.  It is used to choose the rows that we want to display as a result of the query.

MySQL>         Select Emp_code, Emp_Name, City from DEPT where City=’Thane’;

Relational Operators

Relational operators can also be used into a predicate.  Relational operators are descried as follows:

• Equal to (  =  )
• Not Equal to ( <>,  I=,  ^=  )
• Greater Than (  >  )
• Less than (  <  )
• Greater than Equal to ( >= )
• Less than Equal to ( <= )
• IS NULL (is equal to NULL)
• <=> (NULL safe Equal to)
• expr BETWEEN lowest and highest (expr >= lowest and expr<=highest
• expr IN(value1, value2...) (expr = value 1 or expr=value2 ...)

To display only rows containing City ‘Mumbai’ the query is:-

MySQL>         Select * from DEPT where City=’Mumbai’;

To Display only Emp_code, Emp_Name, Salary and Date of Joining for Salary>30000:-

MySQL>         Select Emp_code, Emp_Name, Salary,DOJ from DEPT where Salary>30000’;

To Display only Emp_Name, Manager Id and For mgrID=512:-

MySQL>         Select Emp_Name, mgrID, Salary from DEPT where MgrID=512’;

To Display only Emp_code, City and DOJ if DOJ is after Jan1, 2000:-

MySQL>         Select Emp_code, City, DOJ where DOJ> ‘2000-01-01’;

Is NULL, Is Not NULL

NULL values means there is no value in the table or column or it may be empty.  It is used to check if the value in a column is empty or not.

MySQL>         Select Emp_Name, DOJ from DEPT where City is not NULL;

MySQL>         Select Emp_Name, DOJ from DEPT where City is NULL;

Logical Operators

Used to write more complex queries.  Complex queries are formed when we combine two or more conditions with the use of Logical Operators.

AND               True when both the conditins before and after it are true.

OR                  True if either of the conditions before and after it are true.

NOT(!)            True if the condition written after it is false.

MySQL>         Select * from DEPT where MgrID != 515;

BETWEEN...AND.. OPERATOR

This is used to select rows with values which will fall within the range of specified value.  Hence to indicate >= and <= we can use BETWEEN operator as:-

MySQL>         Select Emp_code, DeptID, mgrID, salary from Dept where salary between 30000 and 40000;

IN OPERATOR

This is used to select rows, for a given column having values matching with a given set of values.

MySQL>         Select Emp_code, Emp_Name, DeptID from Dept where DeptID = 101 or DeptID = 103;

Can also be written as:-

MySQL>Select Emp_code, Emp_Name, DeptID from Dept where DeptID IN(101, 103);

MySQL>Select Emp_code,Emp_Name, City from Dept where City IN(‘Mumbai’,’Pune’);

LIKE OPERATOR

LIKE operator can be used to match strings containing wild card characters % and _.  Where  % represents one or more characters and _ represents a single character.  For Eg.

‘b%’                Starting with b or B

‘%b’                Ending with b or B

‘%b%’             Containing with b or B in any position.

‘_ _ _’              Contains any three characters

‘_A_’               Has 3 characters and second character is a or A.

‘_ _c%’            Third character is c or C

‘%c’                 The second last character is c or C.

‘a%c’               First character is a or A and last character is c or C.

MySQL>Select Emp_code,Emp_Name, City from Dept where Emp_Name like ‘%o%‘;

MySQL>Select Emp_code,Emp_Name, City from Dept where City like ‘%e‘;

MySQL>Select Emp_code,Emp_Name, Salary from Dept where Emp_Name like ‘_i%‘;

MySQL>Select Emp_code,Emp_Name, City,Salary from Dept where City like ‘m%‘;

DISTINCT

This can be used to ensure that duplicate rows are not obtained as a result of the query.

MySQL>Select DISTINCT City from Dept;

LIMIT

This is used to control which rows are to be displayed as a result of a query.

MySQL>Select * from Dept LIMIT 5;

MySQL>Select * from Dept LIMIT 3,4;

ORDER BY

Can be used to sort the table rows on selected column/field.  ASC is ascending order which is by default and DESC is arranging the table in Descending order.

MySQL>Select Emp_Name, DOJ from Dept Order by Emp_Name;

MySQL>Select Emp_Name, DOJ from Dept Order by Emp_Name Desc;

We can also sort multiple columns using ORDER BY clause:-

MySQL>Select Emp_Name, DeptID, Salary from Dept Order by DeptID, Salary Desc;

Sorting can be done also by Column Position which is given by its number:-

MySQL>Select Emp_code, DeptID, Salary from Dept Order by 2 Desc;

MySQL>Select * FROM Dept ORDER BY 4,6 Desc;

Concat  function is used to join data as follows:-

MySQL> Select concat(Emp_Name, ‘Salary is ‘, salary) from Dept;

MySQL> Select concat(Emp_Name, ‘Joined the Service on ‘, DOJ) from Dept;

Aggregate Functions

These functions are to be used to calculate results using columns values from multiple records:-

Count 

Counts number of rows returned by the Query inclusive of all NULL values. 

MySQL>         Select count (Distinct City ) from Dept;

Max    

Displays highest value among those returned by the Query.

MySQL>         Select Max (Salary) from Dept;

Min

Displays lowest value among those returned by the Query.

MySQL>         Select Min (Salary) from Dept;

Avg

Used to determine Average of the values present in  a particular column.

MySQL>         Select Avg(Salary) from Dept;

Sum

Used to obtain arithmetic sum of the rows output by the Query.

MySQL>         Select Sum(Salary) form Dept;

Group By

With Group by Clause aggregate functions can be applied to group of records based on column values.  It is used to sort data into groups for the purpose of aggregation.  Thus Group By clause is always used with the aggregate functions. 

MySQL>         Select City, Count( * ) from Dept Group by City;

MySQL>         Select DeptID, Avg(Salary) from Dept Group by DeptID;

MySQL>         Select DeptID, Min(Salary),Max(Salary) from Dept Group by DeptID;

MySQL>         Select City, Sum( Salary ),Avg(Salary) from Dept Group by City;

Having Clauses

Having Clause, is used to filter grouped results.

MySQL>         Select City, Count( * )from Dept Group by City having City=’Mumbai’;

MySQL>         Select DeptID, Count(*) from Dept Group by DeptID having DeptID=101;

MySQL>         Select MgrID, Sum(Salary) from Dept Group by MgrID having MgrID=515;

Create Company Table As Follows:-

DeptID                        smallint(6)

DeptName       varchar(20)

MgrID             smallint(6)

Target              decimal(9,2)

Multi-Table Queries

A multi table query is used when data from more that one table has to be accessed that are related to each other on a common field.  Multi-Table queries can be applied to form the joins on more than two tables. 

What is Join?

Join is a Special Feature, where we combine two or more tables on certain common field. Thus this feature of RDBMS enables different groups of data to come together on the basis of relationship.  In  a JOIN the tables to be joined are all listed one after another in the FROM part of the query separated by a Comma.  The columns to be selected are listed in SELECT part of the Query.  As the columns are from more than one table, they should be defined or qualified by their names.  Relationship between two tables is called the JOIN condition.  Relationship is set in the WHERE clause.  There are three types of JOINS.

·         Simple-Join

It is a simplest Join called as a natural join also.  Simple join has a condition based on the basis of Equality.  This is the most commonly used join.

MySQL>         Select A.Emp_Name, A.City, B.DeptName, B.Target

                        From Dept A, Company B Where A.DeptID = B.DeptID;

MySQL>         Select Emp_Name, DeptName, Salary

                        From Dept A, Company B Where A.DeptID = B.DeptID;

·         Inner-Join

Complex Queries can be held with the Self (Inner) Join.  A Inner join is a join of a table within itself. This query is executed by logically making two copies of same table.  To do this same table has to be given two different aliases.  Which can then be compared to one another.

MySQL>         Select Emp_code, DOJ, A.MgrID

                        From Dept A Inner Join Company B on A.DeptID = B.DeptID;

MySQL>         Select Emp_Name, DeptName,Salary

                        From Dept A Inner Join Company using (DeptID);

SubQueries

Subqueries are queries embedded in queries. i.e. one query written within another query, also called as nested queries.  They provide a powerful yet simple way to find difficult information.

A sub query can contain any valid SELECT statement, but it must return a single column with expected number of results.  For Example:- If sub query returns only one result, then main query may check for equality, inequality, greater than, less than etc.  On the other hand, If sub query returns more than one result, main query may check to see if a field value is(or is not) in the set of values returned.

MySQL>         Select Emp_Name, Salary from Dept

                        Where Salary>(select avg(salary) from Dept) Order By Emp_Name;

MySQL>         Select Emp_code, Emp_Name, DeptID, DOJ from Dept

                        Where DeptID in (Select DeptID from Dept where City=’Mumbai’);

MySQL>         Select Emp_code, Salary, DOJ from Dept

                        Where DeptID in (Select DeptID from Dept where Emp_Name =’%r’);

MySQL>         Select Emp_code, DeptID, Emp_Name, Salary from Dept

Where DeptID = (Select DeptID from Compnay where DeptName =’export’);

MySQL>         Select Emp_code, DeptID, Emp_Name, Salary from Dept

Where DeptID in (Select DeptID from Compnay where DeptName !=’export’);

TRANSACTIONS

Transaction is a group of statements that are batched together as a set.  This set of statements may change the state of the table or may have no effect on it.

Start Transaction

After issuing this command we can start giving a series of MySQL statements.

Commit

To save the changes made as a result of transaction we use this command.

RollBack

If we dont want to save the transaction, we can use this command.  This ensures that all the series of statements were cancelled.

MySQL>         Start Transaction;

MySQL>         Delete From Emp where Salary>50000;

If we dont want to delete the rows whose salary is >50000 then rollback command can be issued which will retrieve those records that are deleted.

MySQL>         RollBack;

And If we want to save the changes then commit command can be used.  But it will not work if RollBack command is used before.

MySQL>         Commit;