SQL SELECT Statement is used to fetch the data from a database table which returns
this data in the form of a result table. These result tables are called result-sets.
SELECT column1, column2....columnN FROM table_name;
EX- SELECT * FROM table_name;
SELECT ID, NAME, SALARY FROM CUSTOMERS;
SQL DISTINCT Clause is used in conjunction with the SELECT statement to eliminate all
the duplicate records and fetching only unique records.
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SELECT SALARY FROM CUSTOMERS ORDER BY SALARY;
SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;
SQL WHERE Clause is used to specify a condition while fetching the data from a single
table or by joining with multiple tables.
SELECT column1, column2....columnN
FROM table_name WHERE CONDITION;
Ex- SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000;
SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE NAME = 'Hardik';
SQL AND/OR Clause are used to combine multiple conditions to narrow data in an SQL
statement
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
Ex-SELECT ID, NAME,SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;
SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 OR age < 25;
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 is used to compare a value to similar values using wildcard operators.
There are two wildcards used in conjunction with the LIKE operator.
The percent sign (%)
The underscore (_)
The percent sign represents zero, one or multiple characters. The underscore represents
a single number or character. These symbols can be used in combinations.
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';
SQL ORDER BY Clause is used to sort the data in ascending or descending order, based
on one or more columns. Some databases sort the query results in an ascending order
by default.
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;
SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
SQL GROUP BY Clause is used in collaboration with the SELECT statement to arrange
identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT
statement and precedes the ORDER BY clause.
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;
SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY 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 (arithematic function condition);
SQL CREATE TABLE Statement Creating a basic table involves naming the table and
defining its columns and each column's data type.
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
Ex :
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)
);
SQL DROP TABLE Statement is used to remove a table definition and all the data,
indexes, triggers, constraints and permission specifications for that table.
DROP TABLE table_name; DROP TABLE CUSTOMERS; Display table details cmd-
DESC Customers
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
DESC table_name;
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 is used to add new rows of data to a table in the database.
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
EX- INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
Populate one table using another table
You can populate the data into a table through the select statement over another table;
provided the other table has a set of fields, which are required to populate the first
table. Here is the syntax −
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
SQL UPDATE Statement- is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;
UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00;
SQL DELETE Statement is used to delete the existing records from a table.
DELETE FROM table_name
WHERE {CONDITION};
DELETE FROM CUSTOMERS WHERE ID = 6; delete records based on condition.
DELETE FROM CUSTOMERS; delete all records
SQL CREATE DATABASE Statement is used to create a new SQL database.
CREATE DATABASE database_name; Ex- CREATE DATABASE testDB;
SQL DROP DATABASE Statement is used to drop an existing database in SQL schema.
DROP DATABASE database_name; ex-DROP DATABASE Test_db
SQL USE Statement is used to select any existing database in the SQL schema.
USE database_name; Ex- USE Test_db SHOW databases
SQL COMMIT Statement
COMMIT;
SQL ROLLBACK Statement
ROLLBACK;
The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.
All the databases do not support the TOP clause. For example MySQL supports
the LIMIT clause to fetch limited number of records while Oracle uses
the ROWNUM command to fetch a limited number of records.
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]
SELECT TOP 3 * FROM CUSTOMERS;
SELECT * FROM CUSTOMERS LIMIT 3;
SELECT * FROM CUSTOMERS WHERE ROWNUM <= 3;