13 : SQL "TOP" Clause

Now , we are moving to advanced SQL.
Click SQL-create Database & Table  to go back to Basic SQL 

==================================================================================
The TOP clause is used to specify the number of records to return.
The TOP clause can be very useful on large tables with thousands of records. 

The created Table is : 

Create a Database named "Studysql" and a table named "tbl_biodata"
Right click on the database Studysql and select new query.Then we can write queries as per the requirements.
( Click here to know how created this table )

ID	NAME	AGE	CITY	PHONE
1	Priyada	25	Cochin	1000000001
2	Mohan	30	Cochin	2000000002
3	Jaimon Jacob	40	Vytila	3000000003
4	Sumith	24	Kottayam	4000000004
5	Shaiju	36	Thrissur	5000000005

==================================================================================

1. SQL "TOP Clause" Example

Refer the above table we created.
Now we want to select only the first two records in the table above.

To do so, write the command like this in SQL.

SELECT TOP 2 * FROM tbl_biodata

The result-set will look like this:

2 . SQL "TOP PERCENT" Example

Now we want to select only 50% of the records in the table we created.

To do so, write the command like this in SQL.

SELECT TOP 50 PERCENT * FROM tbl_biodata

That is , we selected 50% of the datas.Note that , we entered 6 datas in to the table.

So , the result-set will look like this:

SQL SELECT Syntax of the above table is : 

SELECT TOP number|percent column_name(s)
FROM table_name

NEXT CHAPTER   >>

WILL CONTINUE......

12 : SQL "DELETE" Statement

The DELETE statement is used to delete records in a table.

The created Table is : 

Create a Database named "Studysql" and a table named "tbl_biodata"
Right click on the database Studysql and select new query.Then we can write queries as per the requirements.
( Click here to know how created this table )

ID	NAME	AGE	CITY	PHONE
1	Priyada	25	Cochin	1000000001
2	Mohan	30	Cochin	2000000002
3	Jaimon Jacob	40	Vytila	3000000003
4	Sumith	24	Kottayam	4000000004
5	Shaiju	36	Thrissur	5000000005

==================================================================================

In the previous chapter ( SQL "UPDATE" Statement ) , we inserted a new row "Resmi" as shown :

Now , we want to delete the 7th row "Reshmi"

To do so, write the command like this in SQL.

DELETE FROM tbl_biodata WHERE names ='Reshmi' AND age='23'

When executing we can see , 

The result-set will look like this:

SQL SELECT Syntax of the above table is : 

DELETE FROM table_name
WHERE some_column=some_value

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name

or

DELETE * FROM table_name

Be very careful when deleting records. You cannot undo this statement!

PREVIOUS CHAPTER

   Now , you can move to ADVANCED SQL

11 : SQL "UPDATE" Statement

The UPDATE statement is used to update records in a table.

The created Table is : 

Create a Database named "Studysql" and a table named "tbl_biodata"
Right click on the database Studysql and select new query.Then we can write queries as per the requirements.
( Click here to know how created this table )

ID	NAME	AGE	CITY	PHONE
1	Priyada	25	Cochin	1000000001
2	Mohan	30	Cochin	2000000002
3	Jaimon Jacob	40	Vytila	3000000003
4	Sumith	24	Kottayam	4000000004
5	Shaiju	36	Thrissur	5000000005

==================================================================================

In the previous chapter ( SQL "INSERT INTO" Statement ) , we inserted a new row "Resmi" as shown :




Now , we want to update the details of "Resmi" .
Note that , the " city and phone" fields are null.
We have to update that details (7th row).

To do so, write the command like this in SQL.

UPDATE tbl_biodata SET city='Quilon', phone='7000000007' WHERE names='Reshmi' AND age='23'

When executing we can see , 



The result-set will look like this:

SQL SELECT Syntax of the above table is : 

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Be careful when updating records. If we had omitted the WHERE clause , all records will be updated! 
Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated.

NEXT CHAPTER

PREVIOUS CHAPTER

10 : SQL "INSERT INTO" Statement

The INSERT INTO statement is used to insert new records in a table.

The created Table is : 

Create a Database named "Studysql" and a table named "tbl_biodata"
Right click on the database Studysql and select new query.Then we can write queries as per the requirements.
( Click here to know how created this table )

ID	NAME	AGE	CITY	PHONE
1	Priyada	25	Cochin	1000000001
2	Mohan	30	Cochin	2000000002
3	Jaimon Jacob	40	Vytila	3000000003
4	Sumith	24	Kottayam	4000000004
5	Shaiju	36	Thrissur	5000000005

We can write the INSERT INTO statement in two forms.

1 : Insert Data without specifying column names : 

The first form doesn't specify the column names where the data will be inserted, only their values:

Now we want to insert a new row in the "biodata" table.

To do so, write the command like this in SQL.

INSERT INTO tbl_biodata VALUES ('Arya', '25', 'Cochin', '6000000006')

Here, as per the above given table format , I inserted a new row (6th row) .

When executing , we can see a message :

Then exit from the Query window , and come to tbl_biodata, then we can see 5 records are there

Select EXECUTE ( see the figure below ) , to view the inserted new row .

The result-set will look like this:

Note , the symbol used for executing .Or you can select the EXECUTE option from the menu " QUERY DESIGNER " .

SQL SELECT Syntax of the above table is : 

INSERT INTO table_name

VALUES (value1, value2, value3,...)

2 : Insert Data Only in Specified Columns : 

The second form specifies both the column names and the values to be inserted:

Now we want to insert a new row in the "biodata" table , in a specific column.

To do so, write the command like this in SQL.

INSERT INTO tbl_biodata (names, age) VALUES ('Reshmi', '23')

When executing we can see , 



The result-set will look like this:

NEXT CHAPTER

PREVIOUS CHAPTER

9 : SQL "ORDER BY" Keyword

The ORDER BY keyword is used to sort the result-set.

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sorts the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

The created Table is : 

Create a Database named "Studysql" and a table named "tbl_biodata"
Right click on the database Studysql and select new query.Then we can write queries as per the requirements.
( Click here to know how created this table )

ID	NAME	AGE	CITY	PHONE
1	Priyada	25	Cochin	1000000001
2	Mohan	30	Cochin	2000000002
3	Jaimon Jacob	40	Vytila	3000000003
4	Sumith	24	Kottayam	4000000004
5	Shaiju	36	Thrissur	5000000005

ORDER BY Example

Now we want to select all the persons from the table above, however, we want to sort the persons by their name.

To do so, write the command like this in SQL.

select * from tbl_biodata order by names

The result-set will look like this:

ORDER BY DESC Example

Now we want to select all the persons from the table above, however, we want to sort the persons descending by their name.

To do so, write the command like this in SQL.

SELECT * FROM tbl_biodata ORDER BY names DESC

The result-set will look like this:

SQL SELECT Syntax of the above table is : 

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

NEXT CHAPTER

PREVIOUS CHAPTER