Unit 3: .... Continue

The SQL supports a variety of data types as given below:

Data types	Description	Size
Number (P, S)	It is used to store numeric data types. P stands for precision (total number of decimal digits) and S stands for scale (total number of digits after decimal point).	Range of P is from 1 to 38. And S is from -87 to 127.
Date	It is used to store data and time values	Range of date is from Jan, 47 B.C. to Dec 31 999 AD
Char (Size)	It is used to store fixed size character data.	Range of char is 1 (By default) to 2000 bytes.
Varchar(Size)	It is used to store variable size character data.	Range of varchar is 1 (By default) to 4000 bytes.
Long	It is used to store variable size character data.	The range of Long is up to 2 GB
Raw(size)	It is used to store fixed binary data.	Maximum size up to 2000 bytes.
Long raw	It is used to store variable binary data.	Maximum size is up to 2 GB
Int	It is used to store numeric data	The range of int is range (2-31) to (231)-1

What is an Operator in SQL?

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

·         Arithmetic operators

·         Comparison operators

·         Logical operators

·         Operators used to negate conditions

Arithmetic Operators:

Operators	Description	Example
+	Addition - Adds values on either side of the operator	20 + 10 will give 30
-	Subtraction - Subtracts right hand operand from left hand operand	20 - 10 will give -10
*	Multiplication - Multiplies values on either side of the operator	20 * 10 will give 200
/	Division - Divides left hand operand by right hand operand	20 / 10 will give 2
%	Modulus - Divides left hand operand by right hand operand and returns remainder	20% 10 will give 0

Comparison Operators

Operators	Description	Example
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(a = b) is not true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a != b) is true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a <> b) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(a > b) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(a < b) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(a >= b) is not true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(a <= b) is true.
!<	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.	(a !< b) is false.
!>	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.	(a !> b) is true.

Logical Operators

Operators	Description
ALL	The ALL operator is used to compare a value to all values in another value set.
AND	The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANY	The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN	The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS	The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN	The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE	The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT	The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR	The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL	The NULL operator is used to compare a value with a NULL value.
UNIQUE	The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQL Syntax

SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax:

SQL database most often contains one or more Tables.

Each table is identified by a name (e.g. "Customers", "Orders", "Students", and “Subjects"). Tables contain records (rows) with data.

SQL Actions, you perform on a database are done with SQL statements also known as SQL queries.

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and the entire statements end with a semicolon (;).

Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but MySQL makes difference in table names. So if you are working with MySQL, then you need to give table names as they exist in the database.

All the examples given in this tutorial have been tested with SQL server.

SQL SELECT Statement:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name>;

SQL DISTINCT Clause:

SELECT DISTINCT Col₁, Col₂, …, Col_N FROM <Table_Name>;

SQL WHERE Clause:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name> WHERE <Condition>;

SQL AND/OR Clause:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name> WHERE <Condition1> [AND/OR] <Condition2>;

SQL IN Clause:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name> WHERE Col_Name IN (Val1, Val2, …, ValN);

SQL BETWEEN Clause:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name> WHERE Col_Name BETWEEN Value1 AND Value2;

SQL LIKE Clause:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name> WHERE Col_Name LIKE ‘Pattern’;

SQL ORDER BY Clause:

SELECT Column₁, Column₂, …, Column_N FROM <Table_Name> WHERE <Condition> ORDER BY Col_Name [ASC/DESC];

SQL GROUP BY Clause:

SELECT SUM(Col_Name) FROM <Table_Name> WHERE <Condition> GROUP BY Col_Name;

SQL COUNT Clause:

SELECT COUNT(Col_Name) FROM <Table_Name> WHERE <Condition>;

SQL HAVING Clause:

SELECT SUM(Col_Name) FROM <Table_Name> WHERE <Condition> GROUP BY Col_Name HAVING (Arithmetic Function Condition);

SQL CREATE TABLE Statement:

CREATE table <table_Name>

(

Field_Name1 Datatype(Width),

Field_Name2 Datatype(Width),

….,

Field_Name_N Datatype(width)

);

SQL DROP TABLE Statement:

DROP TABLE <Table_Name>;

SQL CREATE INDEX Statement :

CREATE UNIQUE INDEX <Index_Name> ON <Table_Name>(Col1, Col2, …, ColN);

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] Col_Name [Datatype];

SQL ALTER TABLE Statement (Rename) :

ALTER TABLE <Table_Name> RENAME TO <New_Table_Name>;

SQL INSERT INTO Statement:

INSERT INTO <Table_Name>(Col1, Col2, …, ColN) Values(Val1, Val2, …, ValN);

SQL UPDATE Statement:

UPDATE <Table_Name> SET Col1=Value1, Col2=Value2, …, ColN=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:

USE Database_Name;

SQL COMMIT Statement:

Commit;

SQL ROLLBACK Statement:

ROLLBACK;

The following SQL query selects all the records in the "Students" table:

Example

SELECT *FROM Students;               (Sample Syntax)

Note:

·         SQL is NOT case sensitive, SELECT is same as select

·         Semicolon is the normal way to separate each SQL query in database systems and allow more than one SQL query to be executed in the same call to the server.

·         Some RDBMS require a semicolon at the end of each SQL statement.

The standard SQL syntax to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These SQL syntaxes can be categorized into the following groups:

·         DDL - Data Definition Language

·         DML - Data Manipulation Language

·         TCL – Transaction Control Language

·         DCL - Data Control Language

SQL Command

SQL defines following data languages to manipulate data of RDBMS.

Data Definition Language (DDL)

It is a language that allows the user to define data and their relationship to other types of data. It is mainly used to create files, database, data dictionary and tables within the database. DDL are used to create and remove database objects. DDL contains the following statements.

Command	Description
CREATE	To Create new table or Database
ALTER	To alter/ modify a table
DROP	To delete/drop table or database, constraints

Data Manipulation Language (DML)

It is defined as a language that provides a set of operation to support the basic data manipulation operations on the data held in the database. It allows user to insert, update, delete and retrieve data from the database. Data manipulation applies to all the three levels but at the internal level we must define complex low-level procedures that allows efficient data access. The part of DML that involves data retrieval is called as a query language.

The DML contains the following statements:

Command	Description
INSERT	To insert a new row
DELETE	To Delete a row
UPDATE	To Update existing Row
SELECT	Retrieves records from one or more table.

Transaction Control Language (TCL)

These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling back to original state. It can also make changes permanent.

Command	Description
COMMIT	To permanently save
ROLLBACK	To undo change
SAVEPOINT	To save temporarily

Data Control Language (DCL)

Data control language provides command provides control access to data and the dataset using statements such as GRANT and REVOKE. A privilege can be granted to a user with the help of GRANT statements. The privilege assigned van be SELECT, ALTER, DELETE, EXECUTE, INSERT INDEX etc. DCL provides the following statements:

Command	Description
GRANT	Grant permission of right
REVOKE	Take away privileges from user
COMMENT	Add a comment to the data dictionary

 1.      Data Definition Language (DDL)

CREATE Command

In order to create a table or database, the DDL statement, CREATE command is used and the columns of the table along with the data types and the width are to be specified. While creating a table we should follow the following certain rules.

1. Table names can be up to 30 characters.
2. Table name must begin with alphabets.
3. Table names are not case sensitive.
4. Table names can contain characters like a-z, 0-9, _ (underscore), - (hyphens), $ and #.
5. Database name cannot be up to 8 characters.
6. Names cannot be reserved words/characters.

Syntax:

CREATE table <table_Name> (

Field_Name1 Datatype(Width),

Field_Name2 Datatype(Width),

….,

Field_Name_N Datatype(width)

);

Example: To create a table STUDENT to store the data- R_No, Name, and Date of birth, we have to write;

CREATE TABLE STUDENT(R_No int,Name varchar(20),DOB date);

We also can add certain constraints to the table. The constraints are the rules to control the data in a column. There are three common constraints.

1. Primary key
2. Not Null
3. Check

Primary key is a constraint for the column of a table. This constraint ensures that the column cannot be null and that the values in the column will be unique. Not Null constraint ensures that the user always type the data for that column. Check constraint ensures that when data is entered, the data in the column is limited to specific values like a, b, c.

Syntax:

CREATE TABLE <Table_Name> (

Field_Name1 Datatype (Width) PRIMARY KEY,

Field_Name2 (Width) NOT NULL,

….,

Field_Name_N(width) CHECK (<Field_Name> in (‘a’,’b’,’c’)

);

For Example, to create a table Customer with following fields;

Cust_No          Int       Primary key

FName                        varchar(20)

LName                        Varchar(15)

Addr               varchar(25)

City                 Varchar(15)

DOB               Date

Status              varchar(1)  Check status in ‘V’ or ‘I’

Solution:

CREATE TABLE Customer

(

Cust_No int primary key,

FName varchar(20),

LName varchar(15),

Addr varchar(25),

City varchar(15),

DOB date,

Status varchar(1) check (Status in (‘V’,’I’)

);

ALTER Command

Alter command is used to make changes to the table. It is used to make the following changes to a table.

a.       To add new column.

b.      To add new integrity constraints.

c.       To modify existing column. i.e.

·         To expand length

·         To change default

·         To decrease length but all values in the column must be null

·         To change data type but all values in the column must be null

d.      To drop integrity constraints.

We cannot drop or rename columns. For this we have to create new table. To alter a table we must have the following privileges.

a.       Ownership of the table

b.      ALTER privilege on the table

c.       ALTER any table, even outside your schema

Syntax:

1.      To add a column

ALTER TABLE <Table_Name> ADD (Column Datatype (width));

2.      To modify a column

ALTER TABLE <Table_Name> MODIFY (Column datatype (width));

3.      To add Contraint

ALTER TABLE <Table_Name> ADD (Constraint_Name (Column_Name));

ALTER TABLE <Table_Name> ADD CONSTRAINT <Constraint_Name> CONSTRAINT (Column_Name (‘value’));

4.      To drop constraint

ALTER TABLE <Table_Name> DROP CONSTRAINT;

ALTER TABLE <Table_Name> DROP CONSTRAINT <Constraint_Name>;

5.      To add column with default value

ALTER TABLE <Table_Name> ADD (Column_Name Datatype Default Data);

DROP Command

Drop command is used to remove constraints. It can be used to delete table from schema. We cannot drop or rename columns. The table must be rebuilt for this.

Syntax:

DROP TABLE <Table Name>; (To delete a table)

DROP DATABASE <Database_Name>; (To delete a Database)

Examples:

Create a table SUPPLIER with the following structure.

ID                    NUMBER       3

NAME                        VARCHAR    20

S_CODE         NUMBER       4

DEPOSIT       NUMBER       6

Now write the following SQL queries.

1.      Add new column PinCode and City to table SUPPLIER

2.      Change the width of PinCode to 5.

3.      Add a primary Key (after table creation).

4.      Remove a primary key.

5.      Add a NOT NULL constraint.

6.      Drop a NOT NULL constraint.

7.      Add constraint- check to the deposit column.

8.      To drop this check constraint.

9.      To add a primary key constraint.

10.  Drop the primary key constraint.

SOLUTION:

1.      ALTER TABLE SUPPLIER ADD (PINCODE VARCHAR(3), CITY VARCHAR(5));

2.      ALTER TABLE SUPPLIER MODIFY (PINCODE VARCHAR (5));

3.      ALTER TABLE SUPPLIER ADD PRIMARY KEY (ID);

4.      ALTER TABLE SUPPLIER DROP PRIMARY KEY ;

5.      ALTER TABLE SUPPLIER MODIFY NAME NOT NULL;

6.      ALTER TABLE SUPPLIER MODIFY NAME NULL;

7.      ALTER TABLE SUPPLIER ADD CONSTRAINT CKDEP CHECK ( DEPOSIT BETWEEN 40000 AND 50000);

8.      ALTER TABLE SUPPLIER DROP CONSTRAINT CKDEP;

9.      ALTER TABLE SUPPLIER ADD CONSTRAINT PID PRIMARY KEY (ID);

10.  ALTER TABLE SUPPLIER DROP CONSTRAINT PID;

NOTE: CKDEP and PID are the name of constraint.

1.      Data Manipulation Language (DML)

 DML is used to manipulate data in the database. Some commands are:

INSERT, DELETE, UPDATE and SELECT.

INSERT Command

This command is used to add data i.e. rows (or Tuple) to the table. While inserting data to a table, we have to remember the following points:

2.      The values are specified in the same order as we used during table creation.

3.      The character values are typed within single quotes (‘ ‘).

4.      The date values are typed within the single quotes.

5.      The numeric values are typed as it is.

Syntax: to add records interactively

INSERT INTO <TABLE_NAME> VALUES (VALUE1, ’VALUE2’ , ’VALUE3’ , …. ‘VALUE_N’);

Syntax: to add records to an specific fields

INSERT INTO <TABLE_NAME> ( COL1, COL2, COL3, ……., COL_N) VALUES ( VALUE1, ‘VALUE2’, ‘VALUE3’, ……., ‘VALUE_N’);

For example: to insert a record to the above table CUSTOMER

Interactively:

INSERT INTO CUSTOMER VALUES(001,’Rahul’,‘Acharya’, ’Dailekh’, ‘Narayan’,’Jan-1-1985’,’V’);

Specific Field:

INSERT INTO CUSTOMER (Cust_No, FName, LName, Addr, City, DOB, Status) values(002,’Binod’,’Giri’,’Surkhet’,’Birendranagar’,’Feb-1-1885’,’I’);

SELECT Command

This command is used to display/retrieve the records from the table. We can use some clause and Operators (=, <, >, >=, <=, <>, AND, OR, NOT for complex conditions) while using select statement.

Syntax:

SELECT *From <Table_Name>; (To retrieve all the records from the table)

SELECT Col1, Col2, Col3, ……., Col_N From <Table_Name>; ( To select specific columns/Fields)

SELECT *FROM WHERE <Condition>;

SELECT col1, Col2, Col3, …, ColN From <Table_Name> Where <Condition>;