SQL CREATE

The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.

The Syntax for the CREATE TABLE Statement is:

CREATE TABLE table_name
(column_name1 datatype,
column_name2 datatype,
... column_nameN datatype
);

• table_name - is the name of the table.
• column_name1, column_name2.... - is the name of the columns
• datatype - is the datatype for the column like char, date, number etc.

For Example: If you want to create the employee table, the statement would be like,
CREATE TABLE employee
( id number(5),
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

In Oracle database, the datatype for an integer column is represented as "number". In Sybase it is represented as "int". Oracle provides another way of creating a table.
CREATE TABLE temp_employee
SELECT * FROM employee


In the above statement, temp_employee table is created with the same number of columns and datatype as employee table.

<< Previous Chapter                                                                                            Next Chapter >>

SQL Delete

The DELETE Statement is used to delete rows from a table.

The Syntax of a SQL DELETE statement is:
DELETE FROM table_name [WHERE condition];

• table_name -- the table name which has to be updated.

NOTE: The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like,
DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like,
DELETE FROM employee; 

SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like,
TRUNCATE TABLE employee;

Difference between DELETE and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

SQL DROP Statement:

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.

Syntax to drop a sql table structure:
DROP TABLE table_name;

For Example: To drop the table employee, the query would be like
DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.

<< Previous Chapter                                                                                          Next Chapter >>

SQL UPDATE

The UPDATE Statement is used to modify the existing rows in a table.

The Syntax for SQL UPDATE Command is:

UPDATE table_name
SET column_name1 = value1,
column_name2 = value2, ...
[WHERE condition]


• table_name - the table name which has to be updated.
• column_name1, column_name2.. - the columns that gets changed.
• value1, value2... - are the new values.

NOTE: In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected. 

For Example: To update the location of an employee, the sql update query would be like,
UPDATE employee
SET location ='Mysore'
WHERE id = 101;


To change the salaries of all the employees, the query would be,
UPDATE employee
SET salary = salary + (salary * 0.2);

<< Previous Chapter                                                                                        Next Chapter >>

SQL INSERT

The INSERT Statement is used to add new rows of data to a table. We can insert data to a table in two ways,

1) Inserting the data directly to a table.

Syntax for SQL INSERT is:

INSERT INTO TABLE_NAME
[ (col1, col2, col3,...colN)]
VALUES (value1, value2, value3,...valueN);


• col1, col2,...colN -- the names of the columns in the table into which you want to insert data. 

While inserting a row, if you are adding value for all the columns of the table you need not specify the column(s) name in the sql query. But you need to make sure the order of the values is in the same order as the columns in the table. The sql insert query will be as follows
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3,...valueN);


For Example: If you want to insert a row to the employee table, the query would be like,
INSERT INTO employee (id, name, dept, age, salary location) VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);
 
NOTE: When adding a row, only the characters or date values should be enclosed with single quotes.
If you are inserting data to all the columns, the column names can be omitted. The above insert statement can also be written as,
INSERT INTO employee
VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);


2) Inserting data to a table through a select statement.

Syntax for SQL INSERT is:

INSERT INTO table_name
[(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM table_name [WHERE condition];


For Example: To insert a row into the employee table from a temporary table, the sql insert query would be like,
INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_name, dept, age, salary, location
FROM temp_employee;

If you are inserting data to all the columns, the above insert statement can also be written as,
INSERT INTO employee
SELECT * FROM temp_employee;

 
NOTE: We have assumed the temp_employee table has columns emp_id, emp_name, dept, age, salary, location in the above given order and the same datatype.

IMPORTANT NOTE:
1) When adding a new row, you should ensure the datatype of the value and the column matches
2) You follow the integrity constraints, if any, defined for the table.

<< Previous Chapter                                                                                         Next Chapter >>

SQL HAVING

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sql query would be like;
SELECT dept, SUM (salary)
FROM employee
GROUP BY dept
HAVING SUM (salary) > 25000


The output would be like:

dept	salary
-------------	-------------
Electronics	55000
Aeronautics	35000
InfoTech	30000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped based on the GROUP BY clause. Finally, any conditions on the group functions in the HAVING clause are applied to the grouped rows before the final output is displayed.

<< Previous Chapter                                                                                          Next Chapter >>

SQL GROUP BY

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

For Example: If you want to know the total amount of salary spent on each department, the query would be:
SELECT dept, SUM (salary)
FROM employee
GROUP BY dept;


The output would be like:

dept	salary
----------------	--------------
Electrical	25000
Electronics	55000
Aeronautics	35000
InfoTech	30000

 
NOTE: The group by clause should contain all the columns in the select list expect those used along with the group functions.

SELECT location, dept, SUM (salary)
FROM employee
GROUP BY location, dept;


The output would be like:

location	dept	salary
-------------	---------------	-----------
Bangalore	Electrical	25000
Bangalore	Electronics	55000
Mysore	Aeronautics	35000
Mangalore	InfoTech	30000

<< Previous Chapter                                                                                           Next Chapter >>

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT
 
SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.
 
For Example: If you want the number of employees in a particular department, the query would be:
SELECT COUNT (*) FROM employee
WHERE dept = 'Electronics';


The output would be '2' rows.

If you want the total number of employees in all the department, the query would take the form:
SELECT COUNT (*) FROM employee;

The output would be '5' rows.

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from employee table, the query would be:
SELECT DISTINCT dept FROM employee;

To get the count of employees with unique name, the query would be:
SELECT COUNT (DISTINCT name) FROM employee;

SQL MAX(): This function is used to get the maximum value from a column.
To get the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;

SQL MIN(): This function is used to get the minimum value from a column.
To get the minimum salary drawn by an employee, he query would be:
SELECT MIN (salary) FROM employee;

SQL AVG(): This function is used to get the average value of a numeric column.
To get the average salary, the query would be
SELECT AVG (salary) FROM employee;

SQL SUM(): This function is used to get the sum of a numeric column
To get the total salary given out to the employees,
SELECT SUM (salary) FROM employee; 

<< Previous Chapter                                                                                            Next Chapter >>