SQL (STRUCTURED QUERY LANGUAGE):
SQL is a language for storing, manipulating and retrieving the data stored in a relational
database. It was one of the first commercial languages for Edgar [Link]’s relational model. It
become a standard of the American National Standards Institute (ANSI) in 1986 and of the
International Organization for Standards (ISO) in 1987.
FACILITIES OF SQL:
Defining views on the database.
Specifying security and authorization.
Defining integrity constraints.
Specifying transaction controls.
SQL DATA TYPES:
DATA TYPE DESCRIPTION STORAGE
char(size) Fixed width character One byte per character
string. Maximum 8,000 allowed
characters
varchar(size) Variable width character One byte per
string. Maximum 8,000 character stored
characters
number(size) Number value with the Two bytes
maximum number of
digits specified in
parenthesis
date Stores year, month and Four bytes
day values. Default format
is DD-MON-YY.
float(n) Floating point to atleast N Four bytes
digits
time Stores Three bytes
hours:minutes:second
decimal A floating point number Five-Seventeen bytes
allows to specify the
maximum number and
how many digits after the
decimal place.
Ex. No. 1 DATA DEFINITION LANGUAGE
Aim:
To execute the data definition language commands.
Data Definition Language:(DDL)
It defines the database structure or schema.
CREATE:
It is used to create a table with necessary attributes.
SYNTAX:
create table tablename(columnname1 datatype,columnname2 datatype,….columnnamen
datatype);
Example:
create table student(name char(15),regno number(20));
DESC:
It is used to display the design of the table.
SYNTAX:
desc tablename;
EXAMPLE:
desc student;
ALTER:
It is used to modify the definition (structure) of a table in three ways:
1. Add
2. Modify
3. Drop
ADD:
It is used to add a column in a table.
SYNTAX:
alter table tablename add columnname datatype;
EXAMPLE:
alter table student add mark1 number(2);
alter table student add mark2 number(2);
alter table student add percentage number(2);
alter table student add city char(10);
MODIFY:
It is used to modify the datatype of a table.
SYNTAX:
alter table tablename modify columnname datatype;
EXAMPLE:
alter table student modify percentage float(2);
DROP:
It is used to delete a column in the table.
SYNTAX:
alter table tablename drop(columnname);
EXAMPLE:
alter table student drop city;
RENAME:
It is used to rename the tablename.
SYNTAX:
rename tablename to newtablename;
EXAMPLE:
rename student to student1;
TRUNCATE:
It is used to delete the records of the table;
SYNTAX:
truncate table tablename;
EXAMPLE:
truncate table student;
DROP:
It is used to delete the entire table.
SYNTAX:
drop table tablename;
EXAMPLE:
drop table student;
TABLE CREATION:
SQL> create table student(name char(30),registernumber number(12));
OUTPUT:
Table created.
ALTER(ADD):
SQL> alter table student add cgpa number(2);
OUTPUT: Table
altered.
ALTER(MODIFY):
SQL> alter table student modify cgpa float;
OUTPUT: Table
altered.
DESCRIPTION:
SQL> desc student;
OUTPUT:
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME CHAR(30)
REGISTERNUMBER NUMBER(12)
CGPA FLOAT(126)
AGE NUMBER(2)
ALTER(DROP):
SQL> alter table student drop(age);
OUTPUT: Table
altered.
DESCRIPTION:
SQL> desc student;
OUTPUT:
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME CHAR(30)
REGISTERNUMBER NUMBER(12)
CGPA FLOAT(126)
RENAME:
SQL> rename student to studentdetails;
OUTPUT:
Table renamed.
DROP:
SQL> truncate table studentdetails;
OUTPUT:
Table truncated.
DROP:
SQL> drop table studentdetails;
OUTPUT:
Table dropped.
Result:
Thus the Data Definition language Quries was performed and implemented
successfully.
[Link].2 D A T A M A NI P UL AT I O N L A NG U AG E
Aim:
To execute the data manipulation language commands.
Data Manipulation Language :( DML)
DML enable users to access or manipulate data as organized by the appropriate data
model. The types of access are:
Retrieval of information stored in the database.
Insertion of new information in to the database.
Deletion of new information from the database.
Modification of information from the database.
INSERT:
It is used to insert a new data into the database.
SYNTAX:
insert into tablename values(value1,value2,….valuen);
Example:
insert into student values(‘Jeni’,25,100);
insert into student values(‘Anie’,27,75);
insert into student values(‘Anish’,28,87);
DELETE:
It is used to delete a record from the database based on the condition.
SYNTAX:
delete from tablename where condition;
Example:
delete from student where id=27;
UPDATE:
It is used to change the old data to the required new value.
SYNTAX:
updatetablename set columnname=value where condition;
Example:
updatestudent set marks=90 where id=28;
SELECT:
It is used to display a whole database or a particular column;
SYNTAX:
DISPLAY WHOLE DATABASE:
select * from tablename;
DISPLAY PARTICULAR COLUMN:
select columnname1,columnname2 from tablename;
Example:
select * from student;
select marks from student;
DATA MANIPULATION LANGUAGE:
TABLE CREATION:
SQL> create table accounts(customerid number,customername char(20),age number(2),balance
number,loanamount number);
OUTPUT: Table
created.
DESCRIPTION:
SQL> desc accounts;
OUTPUT:
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMERID NUMBER
CUSTOMERNAME CHAR(20)
AGE NUMBER(2)
BALANCE NUMBER
LOANAMOUNT NUMBER
INSERTION:
SQL> insert into accounts values(20,'sindu',18,1000,12000);
OUTPUT:
1 row created.
INSERTION:
SQL> insert into accounts values(21,'sneha',19,1200,1231);
OUTPUT:
1 row created.
INSERTION:
SQL> insert into accounts values(23,'swapna',19,100,10000);
OUTPUT:
1 row created.
INSERTION:
SQL> insert into accounts values(22,'ashish',18,10000,122000);
OUTPUT:
1 row created.
DISPLAY:
SQL> select * from accounts;
OUTPUT:
CUSTOMERID CUSTOMERNAME AGE BALANCE LOANAMOUNT
---------- -------------------- ---------- ---------- ----------
20 sindu 18 1000 12000
21 sneha 19 1200 1231
23 swapna 19 100 10000
22 ashish 18 10000 122000
DELETION:
SQL> delete from accounts where loanamount>10000;
OUTPUT:
2 rows deleted.
QUERY:
SQL> select * from accounts;
OUTPUT:
CUSTOMERID CUSTOMERNAME AGE BALANCE LOANAMOUNT
---------- -------------------- ---------- ---------- ----------
21 sneha 19 1200 1231
23 swapna 19 100 10000
UPDATION:
SQL> update accounts set customerid=25 where customerid=23;
OUTPUT:
1 row updated.
DISPLAY:
SQL> select * from accounts;
OUTPUT:
CUSTOMERID CUSTOMERNAME AGE BALANCE LOANAMOUNT
---------- -------------------- --------- ---------- ----------
21 sneha 19 1200 1231
25 swapna 19 100 10000
DISPLAY:
SQL> select customerid,customername,loanamount from accounts;
OUTPUT:
CUSTOMERID CUSTOMERNAME LOANAMOUNT
---------- -------------------- ----------
21 sneha 1231
25 swapna 10000
Result:
Thus the Data Manipulation language Quries was performed and implemented
successfully.