SQL SELECT
Statement:
SELECT column1, column2....columnN
FROM table_name;
SQL DISTINCT
Clause:
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQL WHERE
Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;
SQL AND/OR
Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1{AND|OR} CONDITION-2;
SQL IN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQL BETWEEN
Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQL LIKE
Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL ORDER BY
Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name{ASC|DESC};
SQL GROUP BY
Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQL COUNT
Clause:
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQL HAVING
Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematicfunction condition);
SELECT COUNT(*)
FROM SAMP.STAFF
GROUP BY ID
SQL CREATE
TABLE Statement:
CREATE TABLE table_name(
column1datatype,
column2datatype,
column3datatype,
.....
columnNdatatype,
PRIMARY KEY( one or more columns )
);
SQL DROP TABLE
Statement:
SQL CREATE
INDEX Statement :
CREATE UNIQUE INDEX index_name
ON table_name( column1, column2,...columnN);
SQL DROP INDEX
Statement :
ALTER TABLE table_name
DROP INDEX index_name;
SQL DESC
Statement :
SQL TRUNCATE
TABLE Statement:
TRUNCATE TABLE table_name;
SQL ALTER
TABLE Statement:
ALTER TABLE table_name{ADD|DROP|MODIFY}column_name{data_ype};
SQL ALTER
TABLE Statement (Rename) :
ALTER TABLE table_name RENAME TO new_table_name;
SQL INSERT
INTO Statement:
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
SQL UPDATE
Statement:
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQL DELETE
Statement:
DELETE FROM table_name
WHERE {CONDITION};
SQL CREATE
DATABASE Statement:
CREATE DATABASE database_name;
SQL DROP
DATABASE Statement:
DROP DATABASE database_name;
SQL USE
Statement:
SQL COMMIT
Statement:
SQL ROLLBACK
Statement:
SQL
Commands:
The standard SQL commands to interact with relational databases
are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be
classified into groups based on their nature:
DDL - Data Definition Language:
Command
|
Description
|
CREATE
|
Creates a new table, a
view of a table, or other object in database
|
ALTER
|
Modifies an existing
database object, such as a table.
|
DROP
|
Deletes an entire
table, a view of a table or other object in the database.
|
Truncate
|
Permanently delete
table from database
|
Rename
|
Rename table name from
database
|
DML - Data
Manipulation Language:
Command
|
Description
|
INSERT
|
Creates a record
|
UPDATE
|
Modifies records
|
DELETE
|
Deletes records
|
SELECT
|
Retrieves certain
records from one or more tables
|
DCL - Data Control
Language:
Command
|
Description
|
GRANT
|
Gives a privilege to
user
|
REVOKE
|
Takes back privileges
granted from user
|
TCL – Transaction Control Language:
Command
|
Description
|
ROLLBACK
|
Rollback data from
recycle bin
|
ROMMIT
|
Commit data from
database
|
SAVEPOINT
|
Save point is used to
save data for particular session
|
The SQL CREATE
TABLE statement is used to create
a new table.
Syntax:
Basic syntax of CREATE TABLE statement is as follows:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
CREATE TABLE is the keyword telling the database system what you
want to do.in this case, you want to create a new table. The unique name or
identifier for the table follows the CREATE TABLE statement.
Then in brackets comes the list defining each column in the table
and what sort of data type it is. The syntax becomes clearer with an example
below.
A copy of an existing table can be created
using a combination of the CREATE TABLE statement and the SELECT statement.
Example:
Following is an example which creates a CUSTOMERS table with ID as
primary key and NOT NULL are the constraints showing that these fileds can not
be NULL while creating records in this table:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18,2),
PRIMARY KEY (ID)
);
The SQL INSERT
INTO Statement is used to add
new rows of data to a table in the database.
Syntax:
There are two basic syntax of INSERT INTO statement is as follows:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
Here column1, column2,...columnN are the names of the columns in
the table into which you want to insert data.
You may not need to specify the column(s) name in the SQL query if
you are adding values for all the columns of the table. But make sure the order
of the values is in the same order as the columns in the table. The SQL INSERT
INTO syntax would be as follows:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
SQL SELECT Statement is used to fetch the data
from a database table which returns data in the form of result table. These
result tables are called result-sets.
Syntax:
The basic syntax of SELECT statement is as follows:
SELECT column1, column2, columnN FROM table_name;
Here column1, column2...are the fields of a table whose values you
want to fetch. If you want to fetch all the fields available in the field then
you can use following syntax:
SELECT * FROM table_name;
The SQL UPDATE Query is used to modify the existing
records in a table.
You can use WHERE clause with UPDATE query to update selected rows
otherwise all the rows would be effected.
Syntax:
The basic syntax of UPDATE query with WHERE clause is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
The SQL DELETE Query is used to delete the existing
records from a table.
You can use WHERE clause with DELETE query to delete selected
rows, otherwise all the records would be deleted.
Syntax:
The basic syntax of DELETE query with WHERE clause is as follows:
DELETE FROM table_name
WHERE [condition];
