Plus 2 Notes- SQL

                                                Chapter 12

STRUCTURED QUERY LANGUAGE (SQL)

Query based language for designing, accessing and manipulating data in a data base system.

SQL allows accessing a Database.

SQL allows defining a Database.

SQL can retrieve, insert, delete and update data.

SQL works with MS-Access, Oracle, DB2, PostgreSQL, MS-SQL,

SQL Data types

1. Char (size) - used to store string of max. Size mentioned in bracket (allocate

maximum size memory).

E.g.   Name Char (25), Place Char (30), Type Char (3),

2. Varchar (size) - used to store string of maximum size mentioned in bracket( variable

size memory allocation, according to string size).

E.g. Name varchar (30),  Address varchar (40),

3. Int – to store integer number.

E.g. Age int Mark int

4. Decimal – to store Decimal number.

Syntax: Dec (size, scale)

E.g. Dec (5,2)- means can store 5 digit decimal point and 2 digit right of decimal point

5. Numeric

Declared as NUMBER and is the same as decimal

E.g. Number (5,2)

6. Small Integer

Declared as SMALLINT and is same as integer

E.g. Class smallint

7. Date – to store date.

E.g. Dob date

8. Time – to store time.

E.g. Jointime time 

SQL Statements/SQL Query

SQL Statements can be broadly classified into two, 

• DDL (Data Definition Language)
• DML (Data Manipulation Language).

Data Definition Language (DDL)

DDL is collection of Commands that makes SQL Statements to define Structure of a

Database, which include defining Database, Table, and Constraints. DDL related to Schema

of a Database.

• CREATE TABLE

• ALTER TABLE

• DROP TABLE

Data Manipulation Language (DML)

DML is collection of Commands that makes SQL Statements to manipulate data in a

database, like insertion, updating, deletion and retrieval of data according to the user

requirement from a table. DML related to instance of a Database.

• INSERT INTO

• UPDATE

• DELETE

• SELECT

Data Definition Language (DDL Commands)

Create Table command

We can create new table using DDL Command CREATE TABLE

(Words in uppercase letters are SQL Commands, within < >are user defined names and

within [ ] are optional parameters)

Syntax:

CREATE TABLE <table name> (<column name1> data type [column

constraint], <column name2> data type [column constraint].........., <column name n>

data type [column constraint]);

Create table student (Rollno int, Name varchar (20), Class int, Division varchar (10));

This command will create a table named ‘student’ in the memory in the following format

 

RollNo	Name	Class	Division

Rules for Naming a table

Rule1. Name must ‘not’ be a SQL keyword.

Rule2. Name must begin with an alphabet.

Rule3. Name may contain letters, alphabets and underscore (_).

Rule4. Name should ‘not’ be the name of another table.

Rule5. Name is same whether uppercase or lower case is used.

Constraints: Constraints are small rule applied on a column in a table. There are five

constraints,

•  Primary key: If a particular column is defined as primary key, we can enter only unique values to that column. it is used to eliminate redundant data. We can set more than one column as primary key, in such case both columns works together for storing unique data.
•  Unique: If we set a column as primary key and need to be enter unique valuesto another column, in such case we can define that column within unique constraints.
• Not null: If we want, a column never be empty, then we can define such column with not null constraints.
• Check: If want to store only some predefined values to a particular column,we can use check constraints as follows.
•  Default: It is used with column to set default value if user do not enter anything. Default can be used with those column, which is not defined using, not null constraint.