- Method 1
select max(salary)
from Employees
where salary
< (select max(salary) from Employees)
- Method 2
select MAX(salary)
from Employees
where salary not
in(select MAX(salary) from Employees)
- Method 3
select MAX(salary) from Employees
where salary !=
(select MAX(salary) from Employees )
-Method 4
SELECT
salary from Employee ORDER BY salary DESC LIMIT 1, 1;
How
to calculate Second Largest Salary
SELECT * FROM `book_book`as e WHERE 2=(select count(book_id) from book_book where e.book_id<=book_id)
Display Database Engines
SHOW ENGINES
Display Database
SHOW DATABASES
Query to select book categories
which start with Non-alphabets
[SQL] SELECT * FROM `book_bookcategories` WHERE `book_category_name` NOT REGEXP '^[a-z]' ORDER BY `book_bookcategories`.`book_category_name` DESC [/SQL]
Query to select DUPLICATE
categories with duplicate count
SELECT COUNT( * ) , `book_category_name` FROM `book_bookcategories`
GROUP BY book_category_name HAVING COUNT( * ) >1 LIMIT 0 , 30
Query to remove duplicate
elements.
CREATE TABLE mytable as(SELECT DISTINCT (`book_category_name`) FROM book_bookcategories)
Perform operations on date
SELECT * FROM `seller_feedback` WHERE `feedback_date`>(SELECT (CURDATE()- INTERVAL 6 MONTH))
update `subcategory` set sub_name = if(sub_name='black','white','black') where sub_name='black'
or sub_name='white'
update subcategory set sub_name = case sub_name when 'white' then 'black' when 'black' then 'white'
end where sub_name in('white','black')
update subcategory set sub_name = case sub_name when 'white' then 'black' 'white' end where sub_name
in('white','black')
Differences between SESSION
and COOKIE
COOKIE
A cookie is a text-only string that takes a place in the memory of user’s
browser. If the lifetime of the cookie is set to be longer than the time user
spends at that site, then this string is saved to file for future reference.
User could be disabled the cookie in their browser setting.
SESSION
Session values are store in server side not in user’s machine. A session is
available as long as the browser is opened. User couldn’t be disabled the
session. We could store not only strings but also objects in session.
sno
|
Session
|
Cookies
|
1
|
Sessions are server-side files that contain user information
|
Cookies are client-side files that contain user information
|
2
|
Session Max life time is 1440 Seconds(24 Minutes) as defined in php.ini file
in php.ini on line 1604 you can find You can edit this value if you need custom session life.
;http:
session.gc_maxlifetime = 1440
|
We have to set cookie max life
time manually with php code with setcookie function. In
setcookie("email",'test@example.com',time()+3600);
/*
expire in 1 hour */
above
example
Cookie Name : email
Cookie Value : test@example.com
Expire
time : I hour after current time (1 Hour = 3600 Seconds)
|
|
|
3
|
In php $_SESSION super global variable is used to manage session.
|
In php $_COOKIE super global variable is used to manage cookie.
|
4
|
Before using $_SESSION, you have to write session_start();
In that way session will start and you can access $_SESSION variable on that page.
|
You don't need to start Cookie as It is stored in your local machine.
|
5
|
You can store as much data as you like within in sessions.The only limits you can reach is the maximum memory a script can consume at one time, which by default is 128MB.
php.ini line 479
; http:
memory_limit = 128M
|
Official MAX Cookie size is 4KB
|
6
|
Session is dependent on COOKIE.
Because when you start session with session_start() then SESSIONID named key will be set in COOKIE with Unique Identifier Value for your system.
So that when you communicate with server next time then with this saved SESSIONID, Server will identify who you are?
|
|
7
|
session_destroy(); is used to "Destroys all data registered to a session", and if you want to unset some key's of SESSION then use unset() function.
unset($_SESSION["key1"], $_SESSION["key2"])
|
There is no function named unsetcookie() ,
Although
if you need to delete cookie then you have set cookie in any previous date,
so that cookie will automatically disabled/deleted. In that way you unset cookie(Set cookie in previous time)
setcookie('email', 'test@example.com', time()-3600); //expire before 1 hour
|
8
|
Session ends when user close his browser.
|
Cookie ends depends on the life time you set for it.
|
9
|
Print session print_r($_session);
|
Print cookies print_r($_cookies);
|
10
|
Set session
|
you can create a cookie by using setcookie() function:
setcookie ( $cookie_name , $value_stored_in_cookie , $expire_in_seconds = 0 , $path_available_to , $domain_available_to );
The function will return false if
it fails to make the cookie or true id it succeed.
$cookie_name; as it suggests it
is the name of the cookie and is the only part of the setcookie() function
that has to be there the rest are optional.
$value_stored_in_cookie;
It is the value stored in the cookie that can be accessed using
$_COOKIE[‘cookie_name’] variable in PHP.
$expire_in_seconds; is an integer
allowing you to define for how long is the cookie valid for example for for
how long is the user logged in and after that it’s not. it uses timestamps so
you can use time() function to get the current timestamp then add seconds that you want the cookie to be
valid added to it like time()+7200 will make it available for 2 hours.
$path_available_to; this part
will define what part of your site can access the cookie like /blog/ will
only make the cookie available to the /blog/ part of the domain but script at
/forum/ can not access it.
$domain_available_to; will define
what domain can access this information, this way no other website can access
the information set for your site alone.
OK now examples:
<?php
setcookie(“user”, “Harry Potter”, time()+3600);// will set the cookie for
harry potter for one hour
setcookie(“book”,”some book”,time()+(3600*24));// this will make the
cookie book with value of some book availible for 24 hours.
?>
You set the cookie now how to
access it’s content? here is how:
<?php
echo $_COOKIE[‘user’];// will display Harry Potter
echo “<br />”;
echo $_COOKIE[‘book’];// will display some book
?>
Like other variables you can use
isset() function to find out if the cookie has been set or not like:
<?php
setcookie(‘user’,”Sam”,time()+3600);// setting the cookie
if(isset($_COOKIE[‘user’])){
echo “You are Loged in “.$_COOKIE[‘user’];
}else{
echo “Loged out”;
}
?>
To delete the cookie you can
change the expiration time to the passed to make sure that the cookie is
expired:
<?php
setcookie(‘user’,””,time()-3600);// this will expires the cookie
?>
|
Super Global variables are an
associative array, they are predefined by PHP.They are super global because
they are available in all scopes on script.
There are several types of Super Global Variables :-
- $_GET
- $_POST
- $_REQUEST
- $_FILES
- $_SESSION
- $_COOKIE
- $_SERVER
- $_ENV
$_GET
$_GET is an associative array which
accept parameter from URL as query string.
What is query string ?
Query string is KEY=>VALUE pairs of URL after Question Mark(?)
Symbol.
We can say complete string after Question Mark Symbol is "Query
String". The question mark is used as a separator and is
not part of the query string.
How can we get Query String in our PHP page ?
With the help of $_GET super global variable.
For example if URL is :-
http://localhost/get_test.php?name=rasmus&age=35&lang=php
Then in above URL Query String is
" name=rasmus&age=35&lang=php "
key and value pairs are separated
with = symbol.
Ex: "name=rasmus"
here "name is key" and "rasmus is value".
Group of those key and value pairs
are separated with & sybmol.
<?php
echo "<pre>";
print_r($_GET);
?>
<form name="test_post" action="" >
<input type="email" required name="email" placeholder="type email here" ><br>
<input type="password" required name="pass" placeholder="type password here"><br>
<input type="submit" >
</form>
$_POST
$_POST is an associative array that
accept parameters as KEY=>VALUE pairs from submission of HTML in
$_POST super global variable as key=>value pairs
SQL SELECT Query
SQL
SELECT Statement
The
SELECT Query the basic query
is used to retrieve the selected data from database. This is very powerful and
largely used command. The result of the select query is stored in result table
which is also called as result-set
It’s part of the data
manipulation language that is responsible for query the data from the
database.
SQL SELECT statement syntax
It is the most frequently used SQL
command and has the following general syntax
SELECT column_name(s) FROM table_name
Or
SELECT * FROM table_name
- [SELECT column_name(s)] – The result set retrieve all entered columns data. It
may contain multiple columns. If pass ‘*’ then it results all columns from
selected table.
- [FROM table_name]
– This is a table name from which result set is retrieved.
Examples:
Let’s see upon executing following
query on Employee table resulting following list:
SELECT * FROM Employee
Following is the result after
executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
Date_Of_Birth
|
Phone
|
1
|
Tanya Smith
|
Female
|
1982-09-30
|
9123456789
|
2
|
Janet Tampi
|
Female
|
1980-07-10
|
9876543210
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
Male
|
1983-09-17
|
9988776655
|
5
|
Pettersen Willams
|
Male
|
1980-07-12
|
9944552299
|
Note: In SQL all queries are
case insensitive, so we can use command SELECT or select.
In above query we have seen how to
get all the rows and columns from the Employee table.
Using the SELECT keyword allows you
to select all columns or specific column(s). The ‘*’ is used to select all
columns in the result set & if you want to any specific column(s) in the
result set then you can specify comma separated column name list after the
SELECT keyword like the one shown below:
SELECT Full_name, Date_Of_Birth, Phone FROM Employee
Following is the result after
executing the above SQL query:
Full_Name
|
Date_Of_Birth
|
Phone
|
Tanya Smith
|
1982-09-30
|
9123456789
|
Janet Tampi
|
1980-07-10
|
9876543210
|
Nilsen Phil
|
NULL
|
9112233445
|
Nilsen Jones
|
1983-09-17
|
9988776655
|
Pettersen Willams
|
1980-07-12
|
9944552299
|
SQL Concat Function:
SQL query also supports the
functions to join the column data & display in one column. The Concat ()
function is used in the SELECT query to join the multiple column data.
If you are looking for Employee
Name with Gender in the format of [Full_Name, (Gender)] then Concat () function
will help you to retrieve the required data in the above format using following
query:
SELECT Concat(Full_Name, '(', Gender, ')'), Phone FROM Employee
In this query:
- Concat () SQL
function is used join the two columns values together.
- The line
“Concat(Full_Name, ‘(‘, Gender, ‘)’)” sentence is used to
join the Full_Name & Gender column data. Use of opening & closing
brackets is in Concat function with list of column names.
Following is the result after
executing the above SQL query:
|
Phone
|
Tanya Smith (Female)
|
9123456789
|
Janet Tampi (Female)
|
9876543210
|
Nilsen Phil (Male)
|
9112233445
|
Nilsen Jones (Male)
|
9988776655
|
Pettersen Willams (Male)
|
9944552299
|
SQL Alias:
When we use the any function in the
SQL query and execute the query then in the result set name of the column is
showing weird. Here while using any function in the query we can use Alias
name so that in the result set name of the column is appears as the Alias name
what you entered.
SELECT Concat(Full_Name, '(', Gender, ')') [AS] Name(M/F), Phone FROM Employee
Following is the result after
executing the above SQL query:
Name (M/F)
|
Phone
|
Tanya Smith (Female)
|
9123456789
|
Janet Tampi (Female)
|
9876543210
|
Nilsen Phil (Male)
|
9112233445
|
Nilsen Jones (Male)
|
9988776655
|
Pettersen Willams (Male)
|
9944552299
|
Such as Concat() function SQL
support lots of function, Here you can get list of most popular function used
regularly in testing:
SQL Aggregate Functions:
- SQL sum()
- SQL max()
- SQL min()
- SQL avg()
- SQL first()
- SQL last()
- SQL count()
SQL Scalar Functions:
- SQL format()
- SQL lcase()
- SQL ucase()
- SQL len()
- SQL mid()
- SQL now()
- SQL round()
SQL Where Clause
The SQL WHERE clause is powerful
& one of the most commonly used clause use in day to day life. Basically It
allows you to filter out the result set and your get limited data based on
condition. The WHERE clause can be used alongwith the SQL
SELECT statement, SQL INSERT statement,, SQL
UPDATE statement, or SQL DELETE statement.
SQL
WHERE clause syntax
It is the most frequently used SQL
command and following is the general syntax of SELECT command with WHERE clause
to fetch data from table:
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
- [SELECT column_name(s)] – The result set retrieve all entered columns
data. It may contain multiple columns. If pass ‘*’ then it results all
columns from selected table.
- [FROM table_name] – This
is a table name from which result set is retrieved.
- [WHERE] –
This Clause used to filter out the result data set based on “condition”.
You can specify any condition using WHERE clause. It supports AND or OR operators
to specify more than one condition in single query.
The WHERE clause
works similar to IF condition used in the coding language. This clause is
used to compare given value with the field value available in MySQl table. If
given value from outside is equal to the available field value in MySQL table
then it returns that row.
Let’s take example A=100 and B=200
and see how operators works with this example:
Operator
|
Description
|
Example
|
=
|
This operator checks the values of
both fields are same.
|
[A = B] is False
|
!=
|
This operator checks the values of
both fields are not same.
|
[A != B] is True.
|
<
|
This checks the value of left
operand is less than the value of right operand. If this condition satisfies
then result is True.
|
[A < B] is True.
|
>
|
This checks the value of left
operand is greater than the value of right operand. If this condition
satisfies then result is True.
|
[A > B] is not True.
|
<=
|
This checks the value of left
operand is less than equal to the value of right operand. If this condition
satisfies then result is True.
|
[A <= B] is True.
|
>=
|
This checks the value of left
operand is greater than equal to the value of right operand. If this
condition satisfies then result is True.
|
[A >= B] is not True.
|
Examples:
Let’s see upon executing following
query on Employee table resulting following list:
SELECT * FROM Employee
Following is the result after
executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
1
|
Tanya Smith
|
Female
|
Stavanger
|
9123456789
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
Male
|
Atlantic
|
9988776655
|
5
|
Peter Willams
|
Male
|
Sandnes
|
9944552299
|
Let’s now look at a practical
example.
SELECT * FROM Employee
WHERE Employee_Id = 2
Following is the filtered result
after executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
AND logical
operator:
SELECT * FROM Employee
WHERE Full_Name = ‘Nilsen Jones’
AND Gender = 'Male'
Following is the filtered result
after executing the above SQL query using AND Logical Operator:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
4
|
Nilsen Jones
|
Male
|
Atlantic
|
9988776655
|
OR logical operator:
SELECT * FROM Employee
WHERE Employee_Id = 1 OR Employee_Id = 2
Following is the filtered result
after executing the above SQL query using OR Logical Operator:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
1
|
Tanya Smith
|
Female
|
Stavanger
|
9123456789
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
IN logical
operator:
SELECT * FROM Employee
WHERE City IN (‘Chicago’, ‘Atlantic’)
Following is the filtered result
after executing the above SQL query using IN Logical Operator:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
4
|
Nilsen Jones
|
Male
|
Atlantic
|
9988776655
|
NOT IN logical
operator:
SELECT * FROM Employee
WHERE City <strong>NOT</strong> IN (‘Chicago’, ‘Atlantic’)
Following is the filtered result
after executing the above SQL query using NOT IN Logical
Operator:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
1
|
Tanya Smith
|
Female
|
Stavanger
|
9123456789
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
5
|
Peter Willams
|
Male
|
Sandnes
|
9944552299
|
BETWEEN
logical operator:
SELECT * FROM Employee
WHERE Employee_Id BETWEEN 2 AND 4
Following is the filtered result
after executing the above SQL query using
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
Male
|
Atlantic
|
9988776655
|
LIKE logical
operator:
Ending Wildcard:
SELECT * FROM Employee
WHERE Full_Name LIKE 'N%'
Following is the filtered result
after executing the above SQL query using LIKE Logical Operator with Ending
Wildcard:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
Male
|
Atlantic
|
9988776655
|
Leading
Wildcard:
SELECT * FROM Employee
WHERE Full_Name LIKE '%s'
Following is the filtered result
after executing the above SQL query using LIKE Logical Operator with Leading
Wildcard:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
4
|
Nilsen Jones
|
Male
|
Atlantic
|
9988776655
|
5
|
Peter Willams
|
Male
|
Sandnes
|
9944552299
|
Multiple
Wildcards:
Find any record which has anywhere,
the letter ‘a’
SELECT * FROM Employee
WHERE Full_Name LIKE '%a%'
Following is the filtered result
after executing the above SQL query using LIKE Logical Operator with Middle
Wildcard:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
1
|
Tanya Smith
|
Female
|
Stavanger
|
9123456789
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
5
|
Peter Willams
|
Male
|
Sandnes
|
9944552299
|
Single Character Wildcard:
Find the record which has Name started
with ‘J’ and has ‘a’ character anywhere in record.
SELECT * FROM Employee
WHERE Full_Name LIKE 'J%a%'
Following is the filtered result
after executing the above SQL query using LIKE Logical Operator with Single
Character Wildcard:
Employee_Id
|
Full_Name
|
Gender
|
City
|
Phone
|
2
|
Janet Tampi
|
Female
|
Chicago
|
9876543210
|
Summary:
- The SQL WHERE clause
is used to filter the number of rows in result set based on condition.
- The WHERE clause can
be used with a SELECT, UPDATE or DELETE query.
- In the WHERE clause
allows you to write the condition using logical operators like AND, OR,
LIKE, BETWEEN, IN, NOT IN etc.
- Using AND operator
means the all condition should satisfies.
- Using OR operator
means the any condition satisfies.
- Using IN keyword is
basically used to choose rows matching a list of values.
SQL Order by clause
Order
by clause:
To sort the result set we can use
Order by clause in either ascending or descending order. This clause is used
with conjection of SELECT statement.
What
are DESC and ASC Keywords?
DESC is used to the query
to sort result by descending
|
ASC is used to the query to
sort result by ascending
|
Query result is sorted from Bottom to Top.
|
Query result is sorted from Top to Bottom.
|
For Date types the latest dates are resulted at the top of the
result list.
|
For Date types the old dates are resulted at the top of the result
list.
|
For Numeric data types the Largest number are resulted at the top
of the result list.
|
For Numeric data types the lowest number are resulted at the top
of the result list.
|
For String data types the result list is sorted with the letter
from Z to A.
|
For String data types the result list is sorted with the letter
from A to Z.
|
The DESC and ASC
keywords are used with the combination of Select statement & the ORDER BY
clause.
Let’s see how these both keywords
are use in the basic query statement:
ASC/DESC
basic syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name(s) ASC|DESC
So here we see what is use of each
objects:
- [SELECT column_name(s)] – The result set retrieve all entered columns data. It
may contain multiple columns. If pass ‘*’ then it results all columns from
selected table.
- [FROM table_name]
– This is a table name from which result set is retrieved.
- [WHERE condition] –
It is used to restrict result set based on the given condition. It is
optional Condition.
- ORDER BY –The
ORDER BY keyword is used to sort the result-set by a specified column. If
we do not pass the ASC or DESC then default ASC is
considered.
Examples:
Let’s see upon executing following
query on Employee table resulting following list:
SELECT * FROM Employee
Following is the result upon
executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
Date_Of_Birth
|
Phone
|
1
|
Tanya Smith
|
Female
|
1982-09-30
|
9123456789
|
2
|
Janet Tampi
|
Female
|
1980-07-10
|
9876543210
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
Male
|
1983-09-17
|
9988776655
|
5
|
Peter Willams
|
Male
|
1980-07-12
|
9944552299
|
If company wants the list of
employee details list with the elder to younger as Birthday. So you can get a
list by executing query on database using Order by keyword with DESC as below.
SELECT * FROM Employee ORDER BY Date_Of_Birth DESC
Following is the result upon
executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
Date_Of_Birth
|
Phone
|
4
|
Nilsen Jones
|
Male
|
1983-09-17
|
9988776655
|
1
|
Tanya Smith
|
Female
|
1982-09-30
|
9123456789
|
5
|
Peter Willams
|
Male
|
1980-07-12
|
9944552299
|
2
|
Janet Tampi
|
Female
|
1980-07-10
|
9876543210
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
Note: In the sorting NULL values
means consider as no values i.e. not zero or empty string. In this example we
also see how the NULL results are sorted.
We can use same query with ascending
order by using following query:
SELECT * FROM Employee
ORDER BY Date_Of_Birth ASC
Or
SELECT * FROM Employee
ORDER BY Date_Of_Birth
Following is the result upon
executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
Date_Of_Birth
|
Phone
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
2
|
Janet Tampi
|
Female
|
1980-07-10
|
9876543210
|
5
|
Peter Willams
|
Male
|
1980-07-12
|
9944552299
|
1
|
Tanya Smith
|
Female
|
1982-09-30
|
9123456789
|
4
|
Nilsen Jones
|
Male
|
1983-09-17
|
9988776655
|
If we sort the result using String
then, the query result set are sorted from those starting with the letter Z
going down to the letter A.
SELECT * FROM Employee
ORDER BY Full_Name ASC
Following is the result upon
executing the above SQL query:
Employee_Id
|
Full_Name
|
Gender
|
Date_Of_Birth
|
Phone
|
2
|
Janet Tampi
|
Female
|
1980-07-10
|
9876543210
|
4
|
Nilsen Jones
|
Male
|
1983-09-17
|
9988776655
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
5
|
Peter Willams
|
Male
|
1980-07-12
|
9944552299
|
1
|
Tanya Smith
|
Female
|
1982-09-30
|
9123456789
|
We can use the Sorting of two
column using Single query. In this example we are sorting first column
with ascending & second column with descending order.
Conclusion
on ORDER BY, DESC and ASC:
- You can sort the
result ser either in ascending or descending order using ASC or DESC
keywords.
- To sort the result in
ascending order the “ASC” keyword is used.
- If we do not pass the
keyword with the ORDER BY then default keyword “ASC” is used to sort the
search result.
- To sort the result in
descending order the “DESC” keyword is used.
- Using Order by clause
conjunction with SELECT both DESC and ASC works. To limit the search
result we can use WHERE clause.
SQL INSERT INTO
What is SQL INSERT INTO?
The primary objective of database
systems is basically used to store the data in to database & recall the
same state of data from database whenever required. The data is handled by the
application which is executed over the top of database. So basically the INSERT
INTO command is used to store the data into database. The SQL INSERT
INTO command is always add a new record into the table.
SQL INSERT
INTO Syntax
Let’s see the basic syntax of Insert
into command:
INSERT INTO Table_Name
VALUES (Value1, Value2, Value3,...);
You can also use the following
command to insert data in specific rows which you have want to specify:
INSERT INTO Table_Name (Column1, Column2, Column3,...)
VALUES (Value1, Value2, Value3,...);
- INSERT INTO Table_Name – The INSERT INTO command is used to tell
database to insert new specified values in the Table_Name which
you have specified.
- (Column1, Column2, Column3,…) – These are the name of the columns for
which you have to add the new record in the table.
- VALUES (Value1, Value2, Value3,…) – These are the actual data to be added in the
new row.
Before inserting new records, the
main important thing to keep in mind is the Data Types. There are different
data types & when adding a new row, you should ensure the datatype of the
value and the column matches. For all string data types values should be
enclose in the single quotes & all numberic data types values should be
enter directly without enclosing into single quotes.
Demo Database:
In this tutorial we will use the
demo well-known Northwind sample database.
Employee_Id
|
Full_Name
|
Address_Line_1
|
City
|
Phone
|
1
|
Tanya Smith
|
Rua do Mercado
|
Stavanger
|
9123456789
|
2
|
Janet Tampi
|
Keskuskatu Road
|
Chicago
|
9876543210
|
3
|
Nilsen Phil
|
Skagen, 21 Floor
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
305 – 14th Ave. S. Suite 3B
|
Atlantic
|
9988776655
|
5
|
Peter Willams
|
Torikatu 38
|
Sandnes
|
9944552299
|
Let’s take a example of Employee
table & insert few entries in different fashion using sql server INSERT
INTO command. We will add new Employee record in the Employee table with
including the column names.
Insert into Employee
(Full_Name, Address_Line_1, City, Phone)
Values (‘Wilman Kala’, ‘305 - 14th Ave. S. Suite 3B’, ‘Seattle’, 9988665522);
Conclusion:
- The SQL INSERT INTO statement is used to insert
new records in a table.
- Single quotes should be excluded while inserting the
numeric values.
- The string and date values should be enclosed in single
quotes.
- The INSERT command can also be used to insert data into
specified columns only.
SQL UPDATE and DELETE
What
is the UPDATE keyword?
The UPDATE command is used to modify
the records in the table. Upon executing this command the record values are
modified based on values passed in the query. Along with WHERE clause you can
update the specific records from the table.
SQL UPDATE Syntax:
Let’s see the basic syntax of DELETE command:
UPDATE Table_Name
SET ‘Column_Name’ = ‘New_value’
[WHERE CONDITION];
So here we see what use of each
object:
- [UPDATE Table_Name]
– This command is used to tell database to modify the records from
specified Table_Name.
- [SET ‘Column_Name’ = ‘New_value’] – ‘Column_Name’ are the name the column for which you
have to modify the value & ‘New_value’ are the actual value which you
have to set. While setting up the new values you have to enter the new
values based on the column’s data type, means if column value is Full_Name
with data type varchar then you have to specify the values in the single
quotes. Similarly for date data type you have to enter date values single
quotes. Numeric values you have to specify the values without quotes.
- [WHERE CONDITION] – This
Clause used to filter out the result data set based on “condition”. You
can specify any condition using WHERE clause.
Let’s go ahead & learn update
query with practical example. In the Employee table each record have Phone
& one Employee want to change the phone number in the employee record &
second employee want to update the Full name. So let’s see how to update the
Phone number using UPDATE query.
SELECT * FROM Employee WHERE
Employee_Id IN (1,2);
Employee_Id
|
Full_Name
|
Gender
|
Date_Of_Birth
|
Phone
|
1
|
Tanya Smith
|
Female
|
1982-09-30
|
9123456789
|
2
|
Janet Tampi
|
Female
|
1980-07-10
|
9876543210
|
3
|
Nilsen Phil
|
Male
|
NULL
|
9112233445
|
4
|
Nilsen Jones
|
Male
|
1983-09-17
|
9988776655
|
5
|
Pettersen Willams
|
Male
|
1980-07-12
|
9944552299
|
6
|
Angels David
|
Male
|
1981-02-10
|
9955566611
|
The Employee “Tanya Smith” wants to
change phone number from “9123456789” to “9198657777” & “Janet Tampi” want
to update the Full name to “Janet Tampi – Simson” & Date of birth to
“1980-07-10”, so you can use following update queries to modify the records.
UPDATE Employee
SET Phone = ‘9198657777’
WHERE Employee_Id = 1;
UPDATE Employee
SET Full_Name = ‘Janet Tampi - Simson’, Date_Of_Birth = ‘1980-08-10’
WHERE Employee_Id = 2;
What
is SQL DELETE keyword?
As it name suggest that the DELELE
keyword is used to delete the record(s) from database when they are no longer
be used. Using DELETE keyword you can delete single or multiple records from
the database. So this command is used more powerfully with WHERE keyword while
removing the unnecessary records from the database.
Upon executing this command the
deleted data cannot be recovered, so prior executing this command you should
make sure with where condition in the query is correct. To be on safer side,
take the backups of database or tables from where you are executing the delete
query, so it will help you to compare the records after executing the query.
SQL DELETE Syntax
Let’s see the basic syntax of DELETE command:
DELETE from Table_Name
[WHERE CONDITION];
In above case we have specified
WHERE condition, so the records will be deleted from table based on the
criteria you specified.
You can also use the following command to delete all the data from
table, so only difference is don’t specifiy the WHERE condition:
DELETE from Table_Name;
So here we see what use of each
object:
- [DELETE from Table_Name] – This command is used to tell database to delete
the records from specified Table_Name.
- [WHERE CONDITION] – This
Clause used to filter out the result data set based on “condition”. You
can specify any condition using WHERE clause.
DELETE FROM Employee WHERE Employee_Id = 4;
Summary
- The UPDATE command is modify the existing records in
the database.
- To modify the limited records in the database you can
use WHERE clause is used along with UPDATE command.
- The DELETE command is use to delete the records in the
database which are no longer required in database.
- Once you delete the records then same records cannot be
recovered again, so make sure you have taken up backup prior to executing
DELETE command.
- To delete the limited records in the database you can
use WHERE clause is used along with DELETE command.
SQL Sub-Queries
What
are Sub queries?
SQL Sub queries are the queries which are embedded inside another query.
The embedded queries are called as INNER query & container query is
called as OUTER query.
The subqueries are the queries which
are executed inside of another query. The result SQL query is totally depends
on the result of sub query. First the INNER query gets executed & the
result of INNER query is passed as input to the outer query.
SQL Sub-Query Syntax:
Let’s look at the basic syntax of
the SQL Sub query command:
Three types of sub queries are
supported in SQL are – Scalar, Row and Table sub queries.
- The Scalar subquery result returns only a single
row and single column.
- The Row subquery result returns only a single
row with single/multiple column(s).
- The Table subquery result returns can be return
single/multiple row(s) or column(s).
In the Sub query you may use the
different operators to filter out the result like [=, >, =, <=, !=, ].
These Sub queries can be used conjunction with INSERT, UPDATE and DELETE
queries.
Suppose you want to find the name of
the department in which employee_id = 100 is currently working on.
Let’s see how this sub query is
constructed & executed inside of another query:
SELECT department_name FROM department
WHERE department_id =
(SELECT department_id FROM employee WHERE employee_id = 100);
Following is the result upon
executing the above SQL Sub query:
So let’s discuss how the result of
above query is calculated:
In above Row Sub-Queries, the
result of INNER query can is returned only one value.
Let’s take a look at the other Sub
query type who returns can be return single/multiple row(s) or column(s) i.e. Table
sub-query:
Suppose you want get list of
employee’s Name and Phone number who’s working in other than Quality department
& date of birth is not registered in Employee tracking system.
SELECT Full_name,Phone FROM Employee
WHERE date_of_birth is NULL and department_id IN
(SELECT department_id FROM department WHERE department_name <> ‘Quality’)
Following is the result upon
executing the above SQL Sub query:
So let’s discuss how the result of
above query is calculated:
You can use multiple INNER queries
inside INNER queries, the SQL supports INNER queries up to 32 levels.
In above examples we have seen INNER
queries up to two levels; here we are seeing three level INNER query:
In the company higher managements
wants to announce the awards to highest paying employee member, so here is the
query to get the name of the highest paying employee:
Select Full_name From employee WHERE Employee_id =
(SELECT Employee_id FROM payments WHERE salary =
(SELECT MAX(salary) FROM payments))
Sub-Queries Vs Joins!
The Subqueries are simpler to write
& easy to understand. As a result, Sub queries are more frequently used in
the beginner’s level. The Joins are complicated but more powerful than Sub
queries.
Majorly sub queries run
independently and result of the sub query used in the outer query (other than
correlated sub query) and in case of JOIN’s, a query only give the result when
the joining condition gets satisfied.
In JOIN both the tables should have
a common column name but in sub query without having a column name we can
execute the query.
If we think in terms of the
performance prospective, then the Joins are faster than the Sub queries. Using
Joins, it approximately boosts the performance of query by 500 times as compare
to Sub queries. So Joins are more popular than the Sub queries & most of
the SQL experts are preferred to use Joins instead of SubQueries.
Conclusion on SQL Sub-Queries:
- Sub queries contain
two parts, one is INNER query & other is OUTER query. The result of
INNER query is passed to OUTER query as input.
- Sub queries are simple
& easy to understand. It can be easily broken down into logical steps,
so it offers more flexibility.
- The Sub queries are
used in conjunction with SELECT, INSERT, UPDATE & DELETE commands.
- In this article we
have learnt about three types of SQL supb queries: scalar, row and table
sub queries.
- In SQL server, The Nested
query can be used up to 32 levels.
- As compare with
Joins, the performance of Sub query is low. Joins are 500 times faster
than Sub queries.
SQL JOIN's
What
is SQL JOIN’s?
In the expert level JOIN’S are more
common SQL commands used in day to day life. JOIN’s are used to retrieving the
records from multiple tables. SQL allows you to take JOIN’s on multiple tables
from same database as well as different databases from same server.
Basically the tables are
interrelated with each other using Primary & foreign key. So these keys are
used in the JOIN’s to interlink two tables.
Why
should we use JOIN’s?
Many times you are thinking “Why
use SQL JOIN’s” as same task can be done using different queries. In the
database queries are executed one by one & result of successive query can
be use for next query. If we use the JOIN’s queries then instead of processing
multiple queries SQL server process only single query which reduce the SQL
server overhead. The main advantage of SQL JOIN’s is the improved performance.
Also using multiple queries lead more data transfer from SQL server to
application which reduces the performance.
Types
of SQL JOINs:
Before we start learning SQL
JOIN’s, we will see the list of different types of SQL JOINs:
- INNER JOIN:
The INNER JOIN keyword selects all rows from both tables as long as there
is a match between the columns in both tables (Table1 and Table2).
- LEFT JOIN: The
LEFT JOIN keyword returns all rows from the left table (Table1), with the
matching rows in the right table (Table2). The result is NULL in the right
side when there is no match.
- RIGHT JOIN:
The RIGHT JOIN keyword returns all rows from the right table (Table2),
with the matching rows in the left table (Table1). The result is NULL in
the left side when there is no match.
- FULL JOIN:
The FULL OUTER JOIN keyword returns all rows from the left table (Table1)
and from the right table (Table2). The FULL OUTER JOIN keyword combines
the result of both LEFT and RIGHT joins.
Let’s go ahead & learn
different types of JOIN queries with practical example. Following are the three
tables, Customers Product & Order.
SQL
INNER JOIN Keyword:
The INNER JOIN is selects all rows
from both tables as sql query match the specified condition.
SQL INNER JOIN Syntax:
SELECT column_name(s)
FROM Table1
JOIN Table2
ON Table1.column_name=Table2.column_name;
Or
SELECT column_name(s)
FROM Table1
INNER JOIN Table2
ON Table1.column_name=Table2.column_name;
In the query you can use JOIN or
INNER JOIN, both are same
INNER
JOIN Query Example:
Suppose, the dealer want the list
of order details like Product name, Unit Price, Quantity & Price. So in
this case you can use INNER JOIN to get the records from both Product &
Order tables. The ProductId is common in both tables. So in the INNER JOIN
condition is added on ProductId & only matching records from Product &
Order tables are returned.
SELECT T2.OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity, T2.Price
FROM Product AS T1
INNER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T2.OrderID
Following is the result upon
executing the above SQL INNER JOIN query:
SQL
LEFT JOIN Keyword:
LEFT JOIN returns all
records/rows from left table and from right table returns only matched records.
Where no matches have been found in the table on the right, NULL is
returned.
SQL
LEFT JOIN Syntax:
SELECT column_name(s)
FROM Table1
LEFT JOIN Table2
ON Table1.column_name=Table2.column_name;
Or
SELECT column_name(s)
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;
LEFT JOIN query Example:
SELECT T2.OrderID AS OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity AS Quantity, T2.Price AS Price
FROM Product AS T1
LEFT OUTER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T1.ProductID
Following is the result upon
executing the above SQL LEFT OUTER JOIN query. You can see the all records from
the Product table & NULL values are displayed for all records where no
matching records found in Order table.
SQL
RIGHT JOIN Keyword:
RIGHT JOIN is exact opposite
to LEFT JOIN, it returns all records/rows from right table and from left
table returns only matched records. Where no matches have been found in the
table on the left, NULL is returned.
SELECT column_name(s)
FROM Table1
RIGHT JOIN Table2
ON Table1.column_name=Table2.column_name;
Or
SELECT column_name(s)
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;
RIGHT JOIN query Example:
SELECT T2.OrderID AS OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity AS Quantity, T2.Price AS Price
FROM Product AS T1
RIGHT OUTER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T1.ProductID
Following is the result upon
executing the above SQL RIGHT JOIN query. You can see the all records from the
Order table & NULL values are displayed for all records where no matching
records found in Product table. But if all records are matched then it will
left table then no NULL records are returned in result set.
SQL FULL OUTER JOIN Keyword:
FULL OUTER JOIN combines
left outer join and right outer join. This join returns all records/rows from
both the tables. If there are no columns matching in the both tables, it
returns NULL values.
SQL
FULL OUTER JOIN Syntax:
SELECT column_name(s)
FROM Table1
FULL OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;
FULL
OUTER JOIN query Example:
Suppose we want to get all Order
records against all the Product records, then we can use the FULL OUTER JOIN
script shown below to get all records from both tables.
SELECT T2.OrderID AS OrderID, T1.ProductID, T1.Name, T1.UnitPrice, T2.Quantity AS Quantity, T2.Price AS Price
FROM Product AS T1
FULL OUTER JOIN Order AS T2 ON T1.ProductID = T2.ProductID
ORDER BY T1.ProductID
Following is the result upon
executing the above SQL FULL OUTER JOIN query:
Conclusion on SQL JOINS:
·
JOIN’s are used to combine & get the data from different tables.
·
INNER JOIN returns rows when there is a match in both tables.
·
LEFT JOIN returns all rows from the left table, even if there are no
matches in the right table.
·
RIGHT JOIN returns all rows from the right table, even if there are no
matches in the left table.
·
FULL JOIN returns rows when there is a match in one of the tables.
·
The performances of JOIN’s are better than sub queries.
·
In the OUTER JOIN’S when no records are returned then NULL values are
returned in the result set.
·
JOIN queries can be used with the conjunction of SELECT, INSERT, UPDATE,
DELETE commands.
·
Also we can use different clauses along with JOIN’s like GROUP BY,
WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.
SQL UNION
What
is a SQL UNION?
The
SQL UNION operator
allows you to combine the result of two or more
SQL SELECT queries.
The only requirement to work SQL
UNION query is both the SQL SELECT queries must having the same number of
columns in the result set & with same data type.
SQL UNION Syntax:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
If we use UNION operator in the
query then all unique records are returned in the result set. The duplicate
records from the result set are displayed once.
SQL
UNION example:
Let take example, we have following
two tables called Table1 and Table2:
First we will see how SQL
UNION operator is combining the results of the two select statements:
SELECT Column1, Column2 FROM Table1
UNION
SELECT Column1, Column2 FROM Table2;
Following is the result upon
executing the above SQL UNION query, the only unique records are returned from
both tables.
Note: If we not specify any keyword with UNION operator then by
default it take DISTINCT keyword i.e. “UNION DISTINCT”
Now we will see how SQL
UNION ALL operator is combining the results of the two select
statements:
SELECT Column1, Column2 FROM Table1
UNION ALL
SELECT Column1, Column2 FROM Table2;
Following is the result set upon
executing the above SQL UNION ALL query, the result set includes all records
including duplicate records from both tables, so the duplicate rows are
repeated in the result set.
UNION operator is by default
returns the unique records. If you want to get the all rows including duplicate
records then you can use “ALL” keyword with UNION.
Difference
between SQL JOIN and UNION:
- In the JOINS, the
columns may be different in the tables but in the UNION the number of
column must be same.
- In case of UNION
result set put rows one after each other i.e. result set puts vertically.
But in case of JOIN result set put column one after each other i.e. result
set puts horizontally. UNION makes a Cartesian product.
- UNION is used for
joining 2 result sets and JOIN is used for joining two or more tables,
views, table-valued functions.
Conclusion
on SQL UNION Queries:
- UNION operator is
used to combine the result sets of two SELECT statements & combined
result set includes all records from both tables.
- The number of
columns & column data types must be same for using the UNION operator.
- The UNION DISTINCT
keyword is returns the duplicate records from the result set. In the UNION
operator if no any keyword is specified then it acts as DISTINCT keyword.
- UNION ALL simply
joins 2 result sets including all the rows,does not remove the duplicate
records from the result set.
SQL GROUP BY Clause
What is the
SQL Group by Clause?
The SQL GROUP BY clause is
used to group the result set based on common value present in the result set.
The grouping of result set is done after all records are retrieved from tables.
The GROUP BY clause can be used with the conjunction of SELECT query to make a
group of rows based on the values of a specific column or expression. The SQL
AGGREGATE function can be used to get summary information for every group and
these are applied to individual group. The main importance of GROUP BY clause
is to summarizing of data from database, so these commands are frequently used
in the reporting queries. Only one result is returned after the executing the
GROUP BY clause & these queries are called as grouped queries.
You can use the WHERE clause along
with GROUP BY clause, but WHERE condition is used to limit the records from the
tables & GROUP BY clause is use to group the retrieved result set based on
column name specified. If you want to filter the grouped result set then we can
use the HAVING clause. This is used after the GROUP BY clause.
SQL GROUP BY Clause Syntax:
SELECT * from Table_Name
GROUP BY Column_Name1,
[Column_Name2,….]
[HAVING CONDITION];
·
[SELECT * from Table_Name] – This is regular SELECT command to
get the records from database.
·
GROUP BY Column_Name1 – This is clause is used to group the
result set based column name specified. Here you can specify one or more column
names.
·
[Column_Name2,….] – This is optional. In the GROUP BY clause, one
column name is mandatory on which you have to group the result set. If you
specify more than one column name then result set first group on first column
value & then next column(s).
·
[HAVING CONDITION] – This Clause used to filter out the result
data set based on “CONDITION”. You can specify any condition using HAVING
clause
In this tutorial we have created a
sample Employees table on which we are executing the different SQL GROUP BY
queries,
Using GROUP BY with Aggregate
Functions:
The use of AGGREGATE
functions is very much powerful when we use then along with GROUP BY
clause. In the SQL GROUP BY queries, the most of times GROUP BY clauses
are used with AGGREGATE functions.
Here we are going to see different
GROUP BY Clause example with different combinations of the AGGREGATE Functions:
- SQL GROUP BY with
SUM() function
- SQL GROUP BY with
COUNT() function
- SQL GROUP BY with
COUNT() and SUM()
- SQL GROUP BY on more
than one column
- SQL GROUP BY with
WHERE clause
- SQL GROUP BY with
HAVING clause
1) SQL GROUP BY with SUM()
function:
Suppose user want to get the sum of
Salary paid to each department, so in the Employees table we have column called
DEPARTMENT_ID & SALARY columns. So we can group the result set using
DEPARTMENT_ID & we will use the SUM Aggregate function to get sum of salary
by each department.
SELECT DEPARTMENT_ID,
SUM(SALARY) as Total_Salary
FROM Employees
GROUP BY DEPARTMENT_ID;
Following is the result upon
executing the above SQL GROUP BY query
2) SQL GROUP BY with COUNT()
function:
Suppose user wants to get the
number employees in each department, so in the Employees table we have column
called DEPARTMENT_ID. So we can group the result set using DEPARTMENT_ID &
we will use the COUNT Aggregate function to get number of employees by each
department.
SELECT DEPARTMENT_ID,
COUNT(*) as Number_Of_Emloyees
FROM Employees
GROUP BY DEPARTMENT_ID;
Following is the result upon
executing the above SQL GROUP BY query,
3) SQL GROUP BY with COUNT() and
SUM() function:
Suppose user wants to get the
number employees in each department & sum of Salary paid to each
department, so in the Employees table we have column called DEPARTMENT_ID and
SALARY. So we can group the result set using DEPARTMENT_ID & we will use
COUNT() & SUM() Aggregate functions. COUNT() is used to get the number of
employees by each department & SUM() is used to get the sum of
SELECT DEPARTMENT_ID,
COUNT(*) as Number_Of_Emloyees,
SUM(SALARY)
FROM Employees
GROUP BY DEPARTMENT_ID;
Following is the result upon
executing the above SQL GROUP BY query,
4)
SQL GROUP BY on more than one column:
Group by Department id, Job Id &
& SUM of salary with department
Suppose user wants to get the number
employees in each department & sum of Salary paid to each department by Job
ID, so in the Employees table we have column called DEPARTMENT_ID, SALARY and
Job_ID. So here first we can group the result set using DEPARTMENT_ID &
then by JOB_ID. Also we will use the SUM() Aggregate function to get the sum of
salary by each Job_id within each department.
SELECT DEPARTMENT_ID,
JOB_ID,
SUM(SALARY)
FROM Employees
GROUP BY DEPARTMENT_ID, JOB_ID;
Following is the result upon
executing the above SQL GROUP BY query,
5) SQL GROUP BY with WHERE
clause:
Suppose user wants to get the sum
of Salary paid to DEPARTMENT_ID = 50, so in the Employees table we have column
called DEPARTMENT_ID and SALARY. So here first we will use the WHERE condition
on DEPARTMENT_ID = 50, this fitters the result set & returns only records
from employee table related to DEPARTMENT_ID = 50. Next to WHERE clause, we can
group the result set using DEPARTMENT_ID.
SELECT DEPARTMENT_ID,
SUM(SALARY)
FROM Employees
WHERE DEPARTMENT_ID = 50
GROUP BY DEPARTMENT_ID;
Following is the result upon
executing the above SQL GROUP BY query,
6) SQL GROUP BY with HAVING
clause:
Group by Department id & count
of employee with department & HAVING count > 1
Suppose user wants to get the
number of employees from each department where at least two employee present in
department. So we can group the result set using DEPARTMENT_ID & we will
use COUNT()Aggregate function to get the number of employees by each
department.
Here we will use HAVING clause to
get the employee count > 1
SELECT DEPARTMENT_ID,
COUNT(*) as Number_Of_Emloyees
FROM Employees
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 1;
Following is the result upon
executing the above SQL GROUP BY query,
Conclusion on SQL GROUP BY
Clause:
- The SQL GROUP BY
clause is used to divide the result set into smaller groups.
- The grouping can
happen after retrieves the rows from a table.
- In the GROUP BY
Clause the grouping is done using same values present in the result set.
- The GROUP BY Clause
is used with the conjunction of SQL SELECT query.
- The WHERE clause is
used to retrieve rows based on a certain condition, but it cannot be
applied to grouped result.
- The HAVING clause is
used to filter the result set of GROUP BY clause. This is used after GROUP
BY clause.
Learning
JOINs in MySQL database with examples
What is a
JOIN?
An SQL JOIN clause is used to query data from two or more tables from a
MySQL database or in other words by using JOINs you can join one or more
tables.
How many
types of JOIN in MySQL?
Wikipedia says: According to ANSI standard there are 4 types of JOIN. 1.INNER
2.OUTER 3.LEFT 4.RIGHT As a special case, there is one more JOIN called
SELF-JOIN which means a table can JOIN to itself is a SELF-JOIN. Lets see the
two tables which i am going to use in my example. The 2 tables namely people
and property from a database called phphunger.
Step
1:
First create 2 tables (people and
property) and insert the dump data with the following code.
CREATE TABLE IF NOT EXISTS
`people` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`phone` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
INSERT INTO `people`
(`id`, `name`, `phone`) VALUES
(1, 'phphunger', 1234567890),
(2, 'Larry Page', 1236547890),
(3, 'Steve Jobs', 1235468790),
(4, 'Mark Zukerberg', 1239874560);
CREATE TABLE IF
NOT
EXISTS `property` (
`id` int(10) NOT NULL,
`pid` int(10) NOT NULL,
`website` varchar(25) NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `property` (`id`,
`pid`, `website`) VALUES
(1, 1, 'www.phphunger.com'),
(2, 2, 'www.google.com'),
(2, 3, 'www.gmail.com'),
(2, 4, 'www.youtube.com'),
(3, 5, NULL),
(4, 6, 'www.facebook.com'),
(5, 7, 'www.twitter.com'),
(6, 8, 'www.linkedin.com');
Step
2:
Applying different JOINs for the created
tables.
CASE
1:
Applying Regular JOIN for the 2
tables. (Regular JOIN means INNER JOIN or JOIN). Before applying JOINs to the
tables lets first see what's the functionality of JOIN.
SELECT
name, phone, website
FROM
people
JOIN
property ON people.id = property.id
LIMIT
0 , 30
Functionality of a JOIN (INNER JOIN
or Regular JOIN):
If you perform a
JOIN on the 2 tables then you will get all the matched records and the
unmatched records will not be displayed.
Lets see the output after applying
JOIN
Observation : From the above output you can judge that i
have filtered the records based on their id's. So from this filtering i have
obtained the matched records from both the tables. Unmatched records are not
displayed here.
CASE 2:
Applying LEFT JOIN for both the
tables. Before applying LEFT JOIN to the tables lets first see what's the
functionality of LEFT JOIN.
Functionality
of a LEFT JOIN:
If you perform a LEFT JOIN on the 2
tables then you will get all the matched records as well as you will get an
extra record for each unmatched record from the left table of the JOIN. Means
from my example each column name "name" gets a mention.
SELECT name,
phone, website
FROM people
LEFT JOIN property
ON people.id = property.id
LIMIT 0 , 30
Lets see the output.
CASE 4:
Applying RIGHT OUTER JOIN for both the
tables. Before applying RIGHT OUTER JOIN to the tables lets first see
what's the functionality of RIGHT OUTER JOIN. Functionality of RIGHT
OUTER JOIN:
RIGHT OUTER JOIN is
same as RIGHT JOIN. Adding OUTER after the clause RIGHT to yield the result.
Lets see the example.
SELECT name,
phone, website
FROM people
RIGHT OUTER JOIN property
ON people.id = property.id
LIMIT 0 , 30
CASE 5:
Applying LEFT OUTER JOIN for both the
tables. Before applying LEFT OUTER JOIN to the tables lets first see
what's the functionality of LEFT OUTER JOIN.
Functionality of LEFT OUTER JOIN:
LEFT OUTER JOIN is
same as LEFT JOIN. Adding OUTER after the clause LEFT to yield the
result.
Lets see the example.
SELECT name,
phone, website
FROM people
LEFT OUTER JOIN property
ON people.id = property.id
LIMIT 0 , 30
1. Inner Join or Equi join
2. Self Join
2. Outer Join
outer join is again classified into
a) Left Outer Join
b) Right Outer Join
c) Full Outer Join
3. Cross join
Difference between MySQL delete
and truncate table
TRUNCATE vs DELETE
TRUNCATE
|
DELETE
|
TRUNCATE is a DDL command
|
DELETE is a DML command
|
TRUNCATE is executed using a table
lock and whole table is locked for remove all records.
|
DELETE is executed using a row
lock, each row in the table is locked for deletion.
|
We cannot use Where clause with
TRUNCATE.
|
We can use where clause with
DELETE to filter & delete specific records.
|
TRUNCATE removes all
rows from a table.
|
The DELETE command is used to
remove rows from a table based on WHERE condition.
|
Minimal logging in
transaction log, so it is performance wise faster.
|
It maintain the log, so it slower
than TRUNCATE.
|
TRUNCATE TABLE removes the data by
deallocating the data pages used to store the table data and records only the
page deallocations in the transaction log.
|
The DELETE statement removes rows
one at a time and records an entry in the transaction log for each deleted
row
|
Identify column is reset to its
seed value if table contains any identity column.
|
Identity of column keep DELETE
retain the identity
|
To use Truncate on a table you
need at least ALTER permission on the table.
|
To use Delete you need DELETE
permission on the table.
|
Truncate uses the less transaction
space than Delete statement.
|
Delete uses the more transaction
space than Truncate statement.
|
Truncate cannot be used with
indexed views
|
Delete can be used with indexed
views
|
Drop all object’s statistics and
marks like High Water Mark free extents and leave the object really empty
with the first extent. zero pages are left in the table
|
Keeps object’s statistics and all
allocated space. After a DELETE statement is executed, the table can
still contain empty pages.
|
TRUNCATE TABLE can’t activate a
trigger because the operation does not log individual row deletions. When we
run truncate command to remove all rows of table then it actually doesn’t
removes any row, rather it deallocates the data pages. In case of Truncate
triggers will not be fired because no modification takes place, we have just
deallocated the data pages not deleted any row from table.
|
Delete activates a trigger because
the operation are logged individually. When we execute Delete command, DELETE
trigger will be initiated if present. Delete is a DML command and it deletes
the data on row-by-row basis from a table. Which means delete is modifying
the data by deleting it from the table. Triggers are fired when a DML
statement executed on a table, so trigger will be fired in case of Delete
command execution.
|
Both remove records from the table,
so what is the difference. Very simple, read along.
For this article, I will use a
‘friends’ table.
delete from friends;
and
truncate table friends;
Both the above statements remove
all records from the table, but the essentially difference is as follows.
delete from friends
– will delete all records from the friends table. That’s it. I.e. the
auto_increment counter does not get
reset.
truncate table friends
– will delete all records from the table and also rebuild the table, thus resetting the auto_increment counter.
Try these steps to identify
yourself:
Step 1:
create table friends (
id int not null primary key auto_increment,
name varchar(20)
);
Step 2:
insert into friends (name) values (‘Sunil’);
insert into friends (name) values (‘Vishal’);
insert into friends (name) values (‘Vikram’);
Step 3:
select * from friends;
Note the id of the last record. It
will be 3.
Step 4:
delete from friends;
Step 5:
insert into friends (name) values (‘Mithil’);
Step 6:
select * from friends;
Note that the id of the only record
will be 4. This means that delete from friends removed all records, but did not
reset the auto_increment counter.
Step 7:
Repeat Step 2 – 3 and goto step 8.
Step 8:
truncate table friends;
Step 9:
Repeat step 5 – 6 and goto step 10.
Note that after you repeat step 6,
you will see that the only record i.e. of Mithil has id 1. This means that
truncate deletes all the records from the table and resets the auto_increment
pointer to start from 1.
. Delete use for delete row by row
but truncate will delete the entire table.
2. In Truncate rollback not possible.
3. Value of Auto Increment will reset from starting after use of Truncate not
in Delete.
4. Truncate is a DDL command and Delete is a Dml command.
5. When Delete the Particular row the Corresponding Delete Trigger(if exists)
Fire.
In Case of Truncate the Trigger is not fired.
What is the
difference between explode and split?
Both the functions are used to Split a
string. Explode is used to split a string using another string.
Explode- The explode() function splits the string by
string.
E.g explode (" this", "this is a
string"); will return “Is a string”
$st = "Test your PHP code online, right here";
$spl=explode("
", $st);
print_r($spl)."\n";
$im
= implode(" ", $spl);
print_r($im)."\n";
Output
Array //Explode function use.
(
[0] => Test
[1] => your
[2] => PHP
[3] => code
[4] => online,
[5] => right
[6] => here
)
Test your PHP code online, right here // after implode()
function use.
Split is used to split a string using a regular
expression
$date
= "04/03/2015";
$result
= split("/", $date);
print_r($result);
Output
Array
(
[0] => 04
[1] => 03
[2] => 2015
)
Both the functions are used to Split a
string. However, Split is used to split a string using a regular expression. On
the other hand, Explode is used to split a string using another string.
E.g explode (" this", "this is a
string"); will return “Is a string”
Split ("
+ ", "This+ is a string")
What’s the
difference between using the GET method versus POST?
GET:
- Parameters remain in browser history because they are
part of the URL
- Can be bookmarked.
- GET method should not be used when sending passwords or
other sensitive information.
- 7607 character maximum size.
- Url example: page2.php?category=sport
POST:
- Parameters are not saved in browser history.
- Can not be bookmarked.
- POST method used when sending passwords or other
sensitive information.
- 8 Mb max size for the POST method.
- Url example: page2.php
|
GET (HTTP)
|
POST (HTTP)
|
History
|
Parameters remain in browser history because they are part of the URL
|
Parameters are not saved in browser history.
|
Bookmarked
|
Can be bookmarked.
|
Can not be bookmarked.
|
BACK button/re-submit behaviour
|
GET requests are re-executed but may not be re-submitted to server if
the HTML is stored in the browser cache.
|
The browser usually alerts the user that data
will need to be re-submitted. |
Encoding type (enctype attribute)
|
application/x-www-form-urlencoded
|
multipart/form-data or application/x-www-form-urlencoded Use
multipart encoding for binary data.
|
Parameters
|
can send but the parameter data is limited to what we can stuff into
the request line (URL). Safest to use less than 2K of parameters, some
servers handle up to 64K
|
Can send parameters, including uploading files, to the server.
|
Hacked
|
Easier to hack for script kiddies
|
More difficult to hack
|
Restrictions on form data type
|
Yes, only ASCII characters allowed.
|
No restrictions. Binary data is also allowed.
|
Security
|
GET is less secure compared to POST because data sent is part of the
URL. So it's saved in browser history and server logs in plaintext.
|
POST is a little safer than GET because the parameters are not stored
in browser history or in web server logs. |
Restrictions on form data length
|
Yes, since form data is in the URL and URL length is restricted. A
safe URL length limit is often 2048 characters but varies by browser and web
server.
|
No restrictions
|
Usability
|
GET method should not be used when sending passwords or other
sensitive information.
|
POST method used when sending passwords or other sensitive
information.
|
Visibility
|
GET method is visible to everyone (it will be displayed in the
browser's address bar) and has limits on the amount of information to send.
|
POST method variables are not displayed in the URL.
|
Cached
|
Can be cached
|
Not cached
|
Large variable values
|
7607 character maximum size.
|
8 Mb max size for the POST method.
|
require
will throw a PHP Fatal Error if the file cannot be loaded. (Execution stops)
include
produces a Warning if the file cannot be loaded. (Execution continues)
· include_once
and require_once do the same
thing, but only if the file was not already loaded.
Difference: Website vs Web
Application
Web Site : -
1)A Website is Informational
2)All the files in folder structure are automatically included in the website.
There is no .csproj/.vbproj project files in Website
3)When you deploy or publish Website, you need to upload both .aspx and .cs/.vb
files to the server
4)By default explicit namespaces are not added to pages, controls or classes,
but you can add them manually
5)There is no need to re-compile or built the website before publish or
deployment
Web Application :-
1)A Web Application is Interactive
2)A Visual Studio .csproj/.vbproj project file stores information about the Web
Application, such as list of included project files or any internal or external
project to project references
3)When you deploy or publish Web Application, you only need to upload .aspx
files and there is no need to upload .cs/.vb files because code-behind files
are pre-compiled in .dll file
4)By default explicit namespaces are added to pages, controls and classes
5)You need to re-compile or built the web application before publish or
deployment because it contains .dll files with reference of other project file
details
What is the difference
between Stored Procedures and triggers?
Trigger
We can execute a stored procedure whenever we want with the help of the exec
command, but a trigger can only be executed whenever an event (insert, delete,
and update) is fired on the table on which the trigger is defined.
We can call a stored procedure from inside another stored procedure but we
can't directly call another trigger within a trigger. We can only achieve
nesting of triggers in which the action (insert, delete, and update) defined
within a trigger can initiate execution of another trigger defined on the same
table or a different table.
Stored procedures can be scheduled through a job to execute on a predefined
time, but we can't schedule a trigger.
Stored procedure can take input parameters, but we can't pass parameters as
input to a trigger.
Stored procedures can return values but a trigger cannot return a value.
We can use Print commands inside a stored procedure for debugging purposes but
we can't use print commands inside a trigger.
We can use transaction statements like begin transaction, commit transaction,
and rollback inside a stored procedure but we can't use transaction statements
inside a trigger.
We can call a stored procedure from the front end (.asp files, .aspx files,
.ascx files, etc.) but we can't call a trigger from these files.
Stored procedures are used for performing tasks. Stored procedures are normally
used for performing user specified tasks. They can have parameters and return
multiple results sets.
The Triggers for auditing work: Triggers normally are used for auditing work.
They can be used to trace the activities of table events.
No1
1) Stored Procedure is predefined collection of sql statements.
2) Stored Procedure is Precompiled Execution.
3) Trigger is not Precompiled Exectution.
4) Trigger is not Reduce Client / Server Network Traffic.
5) Trigger Execute Automatic Execution Process. Stored Procedure is not
automatic Procedure process.
No2
1. Stored Procedure can be run independently
The triggers executes based on table events
2. The Stored Procedures are used for performing tasks
The Triggers for auditing work
3. The Stored Procedures can have the parameters
The Triggers cannot have any parameters
4. The Stored Procedure cannot call triggers
The triggers can call Stored Procedures
What is the
difference between Code Igniter and Cake PHP?
CakePHP and Codeigniter are two most popular PHP frameworks. I’m going
to compare both of these frameworks here:
1. Simplicity:
CodeIgniter: CodeIgniter is simple and not too strict to work with.
CakePHP: CakePHP is not so simple as CodeIgniter. It is also a bit strict and
most of its simplicity comes via automation.
2. Ajax:
CodeIgniter: CodeIgniter doesn’t have any Ajax helper.
CakePHP: CakePHP comes with Ajax helper.
3. Code Generation:
CodeIgniter: CodeIgniter doesn’t have any code generation or console features.
CakePHP: CakePHP’s ‘Bake Console’ can create any of CakePHP’s basic
ingredients: Models, Controllers, Views. It can basically create a fully
functional application in minutes.
4. Plugins:
CodeIgniter: There aren’t many plugins available for CodeIgniter as compared to
CakePHP.
CakePHP: Good amount of plugins are available for CakePHP as compared to
CodeIgniter.
5. PHP Version:
CodeIgniter: CodeIgniter although runs on PHP 4 and PHP 5, doesn’t take
advantage of PHP 5 object oriented features.
CakePHP: CakePHP version 2.0 runs only on PHP 5.2 which definitely takes
advantage of PHP 5′s Object Oriented features.
Cash Study->
=> We have three tables like students,
events and proprietary.
evt_id
|
Evt_name
|
1
|
Music
|
2
|
Dance
|
3
|
Song
|
4
|
Sports
|
=> Students Events Proprietary
pty_id
|
Std_id
|
Evt_id
|
1
|
3
|
1
|
2
|
4
|
2
|
3
|
2
|
3
|
4
|
1
|
4
|
Std_id
|
Std_name
|
1
|
Jon
|
2
|
Smith
|
3
|
Pradeep
|
4
|
Ramesh
|
Output
look like this
Pty_id
|
Std_Name
|
Std_id
|
Evt_Name
|
Evt_id
|
1
|
Pradeep
|
3
|
Music
|
1
|
2
|
Ramesh
|
4
|
Dance
|
2
|
3
|
Smith
|
2
|
Song
|
3
|
4
|
Jon
|
1
|
Sports
|
4
|
Query
1-> SELECT pty.pty_id,
std.std_name, std.std_id, evt.evt_name, evt.evt_id FROM students std, events evt , proprietary pty WHERE pty.evt_id=evt.evt_id and
pty.std_id=std.std_id order by pty.pty_id;
--------+----------+--------+
| emp_id |
emp_name | salary |
+--------+----------+--------+
| 1
| James | 2000 |
| 2
| Jack | 4000 |
| 3
| Henry | 6000 |
| 4
| Tom
| 8000
|
+--------+----------+--------+
4 rows IN
SET (0.00 sec)
mysql> SELECT
* FROM
Department;
+---------+-----------+
| dept_id |
dept_name |
+---------+-----------+
| 101
| Sales |
| 102
| Marketing |
| 103
| Finance |
+---------+-----------+
3 rows IN
SET (0.00 sec)
mysql> SELECT
* FROM
Register;
+--------+---------+
| emp_id |
dept_id |
+--------+---------+
| 1 |
101 |
| 2 |
102 |
| 3 |
103 |
| 4 |
102 |
+--------+---------+
4 rows IN
SET (0.00 sec)
mysql> SELECT
emp_name, dept_name FROM Employee e JOIN
Register r ON e.emp_id=r.emp_id JOIN
Department d ON r.dept_id=d.dept_id;
+----------+-----------+
| emp_name |
dept_name |
+----------+-----------+
| James | Sales |
| Jack | Marketing |
| Henry | Finance |
| Tom | Marketing |
+----------+-----------+
4 rows IN
SET (0.01 sec)
