SQL Tutorial

SQL Commands

by anupmaurya
written by anupmaurya 0 comment

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>)],. . . );

For 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

  1. Column constraints apply only to individual columns.
  2. 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).

For 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.  

For 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.  

For 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.

For 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.

For 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.

For 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. 

For 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>;

i)  To view all rows and columns.

For Example:

SELECT * FROM  STUD;


ii) To view specific columns.

For Example:

SELECT NAME, AGE FROM STUD;

iii)  To view the columns in any order.

For Example:

SELECT AGE,ADDRESS,NAME FROM STUD;

iv)  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;

v) 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 =,>,<,<=,>=,<>

I) To list all the students not having age 15 . 

For Example:

SELECT * FROM STUD WHERE AGE<> 15;

2) 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:

  1. 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’;
  1. 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;
  1. 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: 

  1. To list all the employees having salary in the range 3,000   to 5,000
SELECT * FROM EMPLO WHERE SALARY BETWEEN 3000 AND 5000;
  1. 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: 

  1. To display a list of members from ‘sPUNE’,’DELHI’,’MUMBAI’.
SELECT * FROM EMPLO WHERE  CITY  IN  (‘PUNE’,’DELHI’,’MUMBAI’);
  1. 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%’ ;
  1.       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:  

  1. SELECT * FROM STUD ORDER BY NAME;
  2. SELECT * FROM STUD ORDER BY NAME DESC;
  3. 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. 

EX:      SELECT 4*3  FROM DUAL;

The current date can be obtained from the table dual 

EX:      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

  1. avg: to compute average value
  2. min:  to find minimum value
  3. max:  to find maximum value
  4. sum:  to find total value
  5. count:  to count non-null values in a column
  6. 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

i)   To insert values in the table EX:

INSERT INTO stud  VALUES(‘Pratap’,17,’Army Area’);

ii)  To insert values using & operator

INSERT INTO stud  VALUES(‘&name’,age,’&address’);

iii) 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.

iv) 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];

For 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>;

For Example 

DROP TABLE STUD;

(18) ALTER TABLE command

  • To add a column to a table 

Syntax:  ALTER TABLE <tablename> ADD <columnname> <datatype> <size>;

For Example:   

ALTER TABLE stud ADD (marks int );
  • To delete a column in a table

Syntax : ALTER TABLE <tablename> DROP COLUMN <columnname>;

For 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>;

For Example

ALTER TABLE stud
 ADD Email varchar(30);

Hey there, My name is Anup Maurya. I was born with love with programming and worked with TCS. One of best global (IT) services and consulting company as System Administrator . I also love graphics designing. It's my pleasure to have you here.

You may also like

SQL Datatypes

SQL Tutorial

MySQL Cheatsheet