SQL Commands

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.

SQL Commands

Let’s have a look on different sql commands

1. How To Create A New Table

To create a new table in SQL, we use create table command, syntax for the same is given below

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

Select Command is used to retrieve data from a database. The SELECT clause specifies the columns to be displayed in the query result.

Syntax: 

SELECT <columnname> [,<columnname>],… FROM <tablename>;

1. To view all rows and columns.

For Example:

SELECT * FROM  STUD;

2. To view specific columns.

For Example:

SELECT NAME, AGE FROM STUD;

3. To view the columns in any order.

For Example:

SELECT AGE,ADDRESS,NAME FROM STUD; 

4. To eliminate the duplicate data.

SELECT DISTINCT Dept FROM EXAM;

5. To see the list of  depts. Of your school with duplicate values.  

 SELECT ALL DEPT FROM EXAM;

3. How To view the Table structure 

In SQL, you can use the describe command to view the structure of a table, including the field names and data types.

4. How To select a specific rows(WHERE Clause)

In SQL, the WHERE clause is used to select specific rows from a table.

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

1. To list all the students not having age 15 . 

For Example:

SELECT * FROM STUD WHERE AGE<> 15;

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

2. 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;

3. 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;

2. 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’);

2. 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’);

SELECT * FROM EMPLO WHERE pincodes LIKE ‘13%’;

SELECT * FROM EMPLO WHERE ecode LIKE ‘%8’

SELECT * FROM EMPLO WHERE ecode LIKE  ‘ %JA%’ ;

SELECT * FROM EMPLO WHERE name LIKE  ‘_ _ _ a’;

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

For 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

The INSERT command is a data manipulation language (DML) command that adds new records to a database table.

SYNTAX: INSERT INTO <tablename> VALUES (<Value1>,<Value2>,<Value3>,<Value4>);

1. To insert values in the table EX:

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

2. To insert values using & operator

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

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

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

The UPDATE command in SQL changes the data of one or more records in a table. It is a Data Manipulation Language (DML) command that works on the records of a particular database table.

SYNTAX: UPDATE <tablename> SET <colname>=<value> [,<colname>=<value>,…][ WHERE condition];

For Example: 

 UPDATE   stud  SET  age=19  WHERE name=’ritu’;

16. DELETE COMMAND

The DELETE command is a data manipulation language (DML) command that removes records from a table. The DELETE command can remove all records from a table at once, or it can delete specific records based on a condition.

SYNTAX:  DELETE FROM <tablename> [WHERE condition];

For example:

DELETE FROM stud WHERE AGE=15; 

DELETE FROM stud ;

17. DROP TABLE Command

The DROP TABLE command in SQL removes an existing table in a database, along with all its rows, data, indexes, triggers, constraints, and metadata. 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

The ALTER TABLE command in SQL is used to change the structure of an existing table, including adding, modifying, dropping, or renaming columns, adding constraints, and adding a primary key. 

1. To add a column to a table 

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

For Example:  

ALTER TABLE stud ADD (marks int );

2. To delete a column in a table

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

For Example

ALTER TABLE stud DROP COLUMN marks;

3. 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);