Transcript SQL (Structured Query Language)

SQL (Structured Query
Language)
SQL (Structured Query Language) is a standard language for
accessing and manipulating databases.
SQL is categorised into different categories
based on the purpose.
• i) Data Definition Language (DDL) statement
• ii) Data Manipulation Language (DML)
statement
• iii) Transaction Control Statement
• iv) Session Control Statement
• v) System Control Statement
• vi) Embedded SQL Statement
In SQL there are three main data types:
Character, Number, and Date types.
1. Character data type
Character data types stores alphanumeric data
Character data types are;
• i)char
• ii) varchar
• iii) varchar2
• char
Format: char(n)
– Fixed-length character string having maximum length ‘n’.
– has fixed length, right padded with spaces.
• varchar
Format: varchar (n)
– Variable-length character string having maximum length
‘n’.
– has fixed length, right padded with NULL
• Varchar2
Format: varchar2 (n)
– is used to store variable length character strings
– has variable length.
Number data type
Numeric data types are mainly used to store
number with or without fraction part.
The numeric data types are:
1.
2.
3.
4.
5.
NUMBER
DECIMAL
NUMERIC
INT
FLOAT
• NUMBER: The Number data type is used to store
integers negative, positive, floating number of
up to 38 digits of precision.
Format:
NUMBER (p, s)
• 'p' is the total number of significant decimal
digits
• 's' is the number of digits from the decimal point
decimal and numeric
Format
• DECIMAL[ (p[ , s] )] and NUMERIC[ (p[ , s] )]
Square brackets ([ ]) are option.
• 'p' is the total number of significant decimal
digits
• 's' is the number of digits from the decimal point
• int : This used to store integer number
(without any fraction part).
• float: This data type is used to store number
with fraction part(real numbers).
****************
DDL
• CREATE TABLE command is used to create
table structure.
In SQL, we have the following constraints:
NOT NULL - To check a column cannot store NULL value.
PRIMARY KEY - To check that a column have an unique identity which
helps to find a particular record in a table.
DDL
1)CREATE command ->used to create a table
ID
NAME
SALARY
1
ARUN
6000
2
VARUN
8000
Write the command to create a table student with
attribute id,name,salary.
create table student(id int, name char(20),salary int );
DDL
1)CREATE command ->used to create a table
Column
name
Data type
constraints
Adno
Numeric(3)
Primary key
Name
Varchar (20)
Not null
Class
Numeric(2)
Section
Char(1)
Fees
Numeric(10,2)
CREATE TABLE student (Adno Numeric (3) Primary Key,
Name varchar (20) not null, Class Numeric (2),
Section char (1), Fees numeric (10, 2));
DDL
2)DROP command ->used to delete a table
Write the command to delete a table student.
drop table student;
DDL
3) ALTER Command ->Used to add attribute, modify
attribute, delete attribute
1) Write the command to add a new attribute ‘address’ with
data type varchar(20).
ALTER TABLE STUDENT ADD ADDRESS VARCHAR(20);
2)Write the command to change the data type of the attribute
‘address’ to varchar(30).
ALTER TABLE STUDENT MODIFY ADDRESS VARCHAR(30);
3)Write the command to delete the attribute ‘address’.
ALTER TABLE STUDENT DROP ADDRESS ;