Table of Contents
SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.
Let’s have a look on different sql commands
1. To create a new table
CREATE TABLE TABLENAME(<column name> <datatype> [(<size>)],<column name> <datatype> [(<size>)],. . . );
Example
CREATE TABLE STUD (NAME VARCHAR2(10),AGE INT,ADDRESS VARCHAR2(15));*CONSTRAINT
DEFINITION: A constraint is a condition or check applicable on a field or set of fields.
Two types of constraints are
- Column constraints apply only to individual columns.
- Table constraints apply to groups of one or more columns
Syntax
CREATE TABLE TABLENAME(<column name> <datatype> [(<size>)] <column constraints>, <column name> <datatype> [(<size>)] <column constraints>,. . . );
- NOT NULL: NOT NULL immediately after the data type (and size) of a column, this means the column can never have empty values(NULL is not empty but stores an empty value).
Example
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL, AGE INT NOT NULL,ADDRESS VARCHAR2(15));DIFFERENT CONSTRAINTS:- Sometimes called as Database integrity. A few of them are
- UNIQUE CONSTRAINT: It ensures that no two rows have the same value in the specified columns.
Example
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL UNIQUE, AGE INT NOT NULL,ADDRESS VARCHAR2(15));It can be applied only to columns that have also been declared NOT NULL.
- PRIMARY KEY:- It declares a column as the primary key of the table. It cannot allow NULL values, thus it must be applied to columns declared as NOT NULL.
Example
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL PRIMARY KEY,INT NOT NULL ,ADDRESS VARCHAR2(15));- DEFAULT CONSTRAINT:- A default value can be specified for a column using the DEFAULT clause. When a user does not enter a value for the column(having default value), automatically the defined default value is inserted in the field.
Example
CREATE TABLE STUD (STUD_ID INT NOT NULL PRIMARY KEY ,
NAME VARCHAR(2) NOT NULL,
ADDRESS VARCHAR(20) DEFAULT="NOTKNOWN");- CHECK CONSTRAINT:- It limits values that can be inserted into a column of a table.
Example
CREATE TABLE STUD (NAME VARCHAR2(10) NOT NULL PRIMARY KEY,
ADDRESS VARCHAR2(15));*APPLYING TABLE CONSTRAINTS:
When a constraint is to be applied on a group of columns of the table, it is called table constraints. It appear in the end of table definition.
Example
CREATE TABLE items(icode char(5) NOT NULL,
descp char(20) NOT NULL,
rol int, qoh integer,
CHECK(rol<qoh),
UNIQUE(icode,descp));If want to define primary key that contains more than one column u can use PRIMARY KEY constraint.
Example
CREATE TABLE members(firstname char(15) NOT NULL,
lastname char(15) NOT NULL,
city char(20) PRIMARY KEY(firstname,lastname));2. SELECT Command
Syntax
SELECT <columnname> [,<columnname>],… FROM <tablename>;
- To view all rows and columns.
Example
SELECT * FROM STUD;- To view specific columns.
For Example:
SELECT NAME, AGE FROM STUD;- To view the columns in any order.
For Example:
SELECT AGE,ADDRESS,NAME FROM STUD; - To eliminate the duplicate data.
Example: Suppose u want the list of depts. Of your school and avoid repetition of rows then this can be done using DISTINCT keyword in the SELECT clause.
SELECT DISTINCT Dept FROM EXAM;- To see the list of depts. Of your school with duplicate values.
For Example:
SELECT ALL DEPT FROM EXAM;3. To view the table structure
Syntax:
DESC <tablename> ; or DESCRIBE <tablename>;
4. To select a specific rows(WHERE Clause)
Syntax:
SELECT <columnname> [,<columnname>],… FROM <tablename> WHERE <condition>;
For Example:
SELECT NAME, AGE FROM STUD WHERE AGE<17;5. RELATIONAL OPERATORS
To compare two values relational operators are used. They are =,>,<,<=,>=,<>
- To list all the students not having age 15 .
For Example:
SELECT * FROM STUD WHERE AGE<> 15;- List the students having age 17.
For Example:
SELECT * FROM STUD WHERE AGE=17;6. LOGICAL OPERATORS(NOT,OR,AND)
These are used to connect search conditions in the WHERE clause.
For Example:
- To list the name and department of employees working in production & servicing departments
For Example:
SELECT Ename, Dept FROM EMPLO WHERE Dept=’Production’ OR Dept= ‘Servicing’;- To list the name, salary and city of those employees who are living in Jalandhar and have salary greater than 10,000.
Example:
SELECT Ename, city,salary FROM EMPLO WHERE city=’Jalandhar’ AND salary>10,000;- To list all the employees details whose city are other than ‘Pune’.
Example:
SELECT * FROM EMPLO WHERE (NOT city=’Pune’ );7. CONDITION BASED ON A RANGE (BETWEEN OPERATOR)
Example:
- To list all the employees having salary in the range 3,000 to 5,000
SELECT * FROM EMPLO WHERE SALARY BETWEEN 3000 AND 5000;- To list all the employees having salary not in the range 3,000 to 5,000
SELECT * FROM EMPLO WHERE SALARY NOT BETWEEN 3000 AND 5000;8. CONDITION BASED ON A LIST (IN OPERATOR)
For Example:
- To display a list of members from ‘sPUNE’,’DELHI’,’MUMBAI’.
SELECT * FROM EMPLO WHERE CITY IN (‘PUNE’,’DELHI’,’MUMBAI’);- To display a list of members that do not match the list i.e. ‘PUNE’,’DELHI’,’MUMBAI’.
SELECT * FROM EMPLO WHERE CITY NOT IN (‘PUNE’,’DELHI’,’MUMBAI’);9. CONDITION BASED ON PATTERN MATCHES patterns are case sensitive
1. PERCENT (%) matches any substring
- To list the employees who are in areas with pincodes starting with 13.
SELECT * FROM EMPLO WHERE pincodes LIKE ‘13%’;- To list the name of employee whose ecode(employee number )ending with 8.
SELECT * FROM EMPLO WHERE ecode LIKE ‘%8’- To list have any substring match like ‘ %JA%’
SELECT * FROM EMPLO WHERE ecode LIKE ‘ %JA%’ ;2. UNDERSCORE(_) matches any character
- To list the employees having name ending with a
SELECT * FROM EMPLO WHERE name LIKE ‘_ _ _ a’;- ‘_ _ _ _’ matches any string of exactly 4 characters.
SELECT * FROM EMPLO WHERE name LIKE ‘_ _ _ _’;
10. SEARCHING FOR NULL
You can perform search using is clause .
For Exapmle
SELECT NAME,AGE FROM STUD WHERE ADDRESS IS NULL;11. SORTING RESULTS (ORDER BY Clause)
You can sort the result of query in a specific order (ascending or descending) using ORDER BY Clause. Default is ascending .
EX:
- SELECT * FROM STUD ORDER BY NAME;
- SELECT * FROM STUD ORDER BY NAME DESC;
- SELECT * FROM STUD ORDER BY NAME DESC, AGE ASC ;
NOTE: Where ASC is for ascending order.
12. TO PERFORM SIMPLE CALCULATIONS.
As we know that we should have a table name when we are using SELECT command otherwise the
SELECT fails. If we are performing simple calculation like 5 * 3, SQL provide us a dummy table called Dual to perform such calculation which has just one row and one column.
Example
SELECT 4*3 FROM DUAL;The current date can be obtained from the table dual
Example
SELECT sysdate FROM DUAL;13. AGGREGATE FUNCTIONS OR GROUPING FUNCTIONS
Aggregate functions are applied to all rows in a table or to a subset of the table specified by a WHERE clause.
Aggregate functions results is a single value. Functions are
- avg: to compute average value
- min: to find minimum value
- max: to find maximum value
- sum: to find total value
- count: to count non-null values in a column
- count(*): to count total no. of rows in a table
Example:
select sum(age), min(age),max(age),count(age) from stud;select count(*) from stud;select count (distinct age) from student;14. INSERT COMMAND
- To insert values in the table EX:
INSERT INTO stud VALUES(‘Pratap’,17,’Army Area’); - To insert values using & operator
INSERT INTO stud VALUES(‘&name’,age,’&address’); - To insert row with NULL values
INSERT INTO stud(name,age) VALUES (‘Raja’,18); NOTE: In this fieldnames having datatype char or varchar are not in single quotes.
- To insert values from other table.
INSERT INTO table1 SELECT * FROM table2 WHERE condition; NOTE: In this table1 and table2 are already created and must match the columns o/p by the subquery.
15. UPDATE COMMAND
Syntax: UPDATE <tablename> SET <colname>=<value> [,<colname>=<value>,…]
[ WHERE condition];
For Example
UPDATE stud SET age=19 WHERE name=’ritu’;16. DELETE COMMAND
SYNTAX: DELETE FROM <tablename> [WHERE condition];
Example
DELETE FROM stud WHERE AGE=15; DELETE FROM stud ;17. DROP TABLE Command
Once you drop the table ,all the data present in table got also delete as well .
SYNTAX: DROP TABLE <tablename>;
Example
DROP TABLE STUD;18. ALTER TABLE command
- To add a column to a table
Syntax: ALTER TABLE <tablename> ADD <columnname> <datatype> <size>;
Example:
ALTER TABLE stud ADD (marks int );- To delete a column in a table
Syntax : ALTER TABLE <tablename> DROP COLUMN <columnname>;
Example
ALTER TABLE stud DROP COLUMN marks;- To change the data type of a column in a table
Syntax: ALTER TABLE <table_name> ALTER COLUMN <column_name>< datatype>;
Example
ALTER TABLE stud
ADD Email varchar(30);Thank you for reading, If you have reached so far, please like the article, It will encourage me to write more such articles. Do share your valuable suggestions, I appreciate your honest feedback!
