Blog For SQL (Structured Query Language) Tutorials: February 2015

Monday, February 9, 2015

SQL SELECT COUNT

The SQL COUNT() function is used to return the number of rows in a query. The count() function is generally used in SQL SELECT function. It is very useful to count the numbers of records.

Let's see the syntax of SQL COUNT statement.

SELECT COUNT (expression)
FROM tables
WHERE conditions;

Let's see the examples of sql select count function.

SQL SELECT COUNT(column_name)

SELECT COUNT(name) FROM employee_table;

It will return the total number of names of employee_table. But null fields will not be counted.

SQL SELECT COUNT(*)

SELECT COUNT(*) FROM employee_table;

The "select count(*) from table" is used to return the number of records in table.

SQL SELECT COUNT(DISTINCT column_name)

SELECT COUNT(DISTINCT name) FROM employee_table;

It will return the total distinct names of employee_table.

SQL SELECT DISTINCT

The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique data.

In a table, there may be a chance to exist a duplicate value and sometimes we want to retrieve only unique values. In such scenarios, SQL SELECT DISTINCT statement is used.

Note: SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are same.

Let's see the syntax of select distinct statement.

SELECT DISTINCT column_name ,column_name
FROM table_name;

Let's try to understand it by the table given below:

Student_Name	Gender	Mobile_Number	HOME_TOWN
Rahul Ojha	Male	7503896532	Lucknow
Disha Rai	Female	9270568893	Varanasi
Sonoo Jaiswal	Male	9990449935	Lucknow

Here is a table of students from where we want to retrieve distinct information For example: distinct home-town.

SELECT DISTINCT home_town
FROM students

Now, it will return two rows.

HOME_TOWN
Lucknow
Varanasi

SQL SELECT UNIQUE

Actually, there is no difference between DISTINCT and UNIQUE.

SELECT UNIQUE is an old syntax which was used in oracle description but later ANSI standard defines DISTINCT as the official keyword.

After that oracle also added DISTINCT but did not withdraw the service of UNIQUE keyword for the sake of backward compatibility.

In simple words, we can say that the select statement used to query or retrieve data from a table in the database.

Let's see the syntax of select unique statement.

SELECT UNIQUE column_name
FROM table_name;

SQL SELECT

The most commonly used SQL command is SELECT statement. It is used to query the database and retrieve selected data that follow the conditions we want.

In simple words, we can say that the select statement used to query or retrieve data from a table in the database.

Let's see the syntax of select statement.

SELECT expressions
FROM tables
WHERE conditions;

Here expression is the column that we want to retrieve.

Tables indicate the tables, we want to retrieve records from.

Optional clauses in SELECT statement

There are some optional clauses in SELECT statement:

[WHERE Clause] : It specifies which rows to retrieve.

[GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be applied to each group.

[HAVING Clause] : It selects among the groups defined by the GROUP BY clause.

[ORDER BY Clause] : It specifies an order in which to return the rows.

For example, let a database table: student_details;

ID	First_name	Last_name	Age	Subject	Hobby
1	Amar	Sharma	20	Maths	Cricket
2	Akbar	Khan	22	Biology	Football
3	Anthony	Milton	25	Commerce	Gambling

From the above example, select the first name of all the students. To do so, query should be like this:

SELECT first_name FROM student_details;

Note: the SQL commands are not case sensitive. We can also write the above SELECT statement as:

select first_name from student_details;

Now, you will get following data:

Amar

Akbar

Anthony

We can also retrieve data from more than one column. For example, to select first name and last name of all the students, you need to write

SELECT first_name, last_name FROM student_details;

Now, you will get following data:

Amar	Sharma
Akbar	Khan
Anthony	Milton

We can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement.

Here a point is notable that only SELECT and FROM statements are necessary in SQL SELECT statements. Other clauses like WHERE, GROUP BY, ORDER BY, HAVING may be optional.

Saturday, February 7, 2015

SQL ALTER TABLE

The ALTER TABLE statement is used to add, modify or delete columns in an existing table. It is also used to rename a table.

You can also use SQL ALTER TABLE command to add and drop various constraints on an existing table.

SQL ALTER TABLE Add Column

If you want to add columns in SQL table, the SQL alter table syntax is given below:

ALTER TABLE table_name ADD column_name column-definition;

If you want to add multiple columns in table, the SQL table will be

ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
.....
column_n column-definition);

SQL ALTER TABLE Modify Column

If you want to modify an existing column in SQL table, syntax is given below:

ALTER TABLE table_name MODIFY column_name column_type;

If you want to modify multiple columns in table, the SQL table will be

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
.....
column_n column_type);

SQL ALTER TABLE DROP Column

The syntax of alter table drop column is given below:

ALTER TABLE table_name DROP COLUMN column_name;

SQL ALTER TABLE RENAME Column

The syntax of alter table rename column is given below:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

SQL TEMP TABLE

The concept of temporary table is introduced by SQL server. It helps developers in many ways:

Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These temporary tables are created inside tempdb database.

There are two types of temp tables based on the behavior and scope.

Local Temp Variable
Global Temp Variable

Local Temp Variable

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from instances. It is started with hash (#) sign.

CREATE TABLE #local temp table (
User id int,
Username varchar (50),
User address varchar (150)
)

Global Temp Variable

Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always ready for all users and not deleted until the total connection is withdrawn.

CREATE TABLE ##new global temp table (
User id int,
User name varchar (50),
User address varchar (150)
)

SQL COPY TABLE

If you want to copy a SQL table into another table in the same SQL server database, it is possible by using the select statement.

The syntax of copying table from one to another is given below:

Select * into <destination table> from <source table>

For example, you can write following command to copy the records of hr_employee table into employee table.

Select * into admin_employee from hr_employee;

Note: SELECT INTO is totally different from INSERT INTO statement.

SQL TRUNCATE TABLE

A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause.

TRUNCATE TABLE Vs DELETE TABLE

Truncate table is faster and uses lesser resources than DELETE TABLE command.

TRUNCATE TABLE Vs DROP TABLE

Drop table command can also be used to delete complete table but it deletes table structure too. TRUNCATE TABLE doesn't delete the structure of the table.

Let's see the syntax to truncate the table from the database.

TRUNCATE TABLE table_name;

For example, you can write following command to truncate the data of employee table

TRUNCATE TABLE Employee;

Note: The rollback process is not possible after truncate table statement. Once you truncate a table you cannot use a flashback table statement to retrieve the content of the table.