Categories of SQL commands
1. DDL [ Data Definition Language ] Commands
2. DML [ Data Manipulation Language ] commands
DDL Commands – These commands are used for changing the structure of the
database or table.
Examples are : CREATE, ALTER TABLE, DROP
DML Commands – These commands are used to change the data in the table. It
consists of the query and update commands.
Examples are : SELECT, UPDATE, DELETE, INSERT INTO
Table name – Stud
Sno Names Age Class
1 Ajay 16 10
2 Vivek 15 10
3 Sunil 17 11
4 John 17 11
5 Firoz 15 12
6 Sanju 17 10
7 Steve 16 12
8 Manu 15 12
9 Paul 17 11
10 Binoy 15 10
Create a table-
Create table Stud ( Sno int, Names varchar(25), Age int , Class int);
Remove a table-
Drop table Stud;
To add a new row to the table-
insert into stud values (1, ‘Ajay’, 16,10);
insert into stud values (2, ‘Vivek’, 15,10);
Note-
• Character, date and time should be enclosed in quotes.
• Numeric values should not be enclosed in quotes.
Retrieve records from the table and to display it-
SELECT * FROM Student ;
Select names, age from student;
Here * Represents ALL
Change the structure of a table –
Alter table command is used to Add, remove or modify columns in a table.
• To add a new column :
ALTER TABLE Stud ADD Games VARCHAR(20);
• To change the structure of a column:
ALTER TABLE Stud MODIFY games INTEGER;
• To delete a column:
ALTER TABLE Stud DROP Games;
Eliminating Duplicate values
• The keyword DISTINCT is used to eliminate the duplicate values in the result.
Select DISTINCT Class from Stud;
Arithmetic Operators
• Arithmetic operators are + - * /
• It is used to perform mathematical calculations.
• Select 7 + 76;
• SELECT Age+5 FROM Stud;
WHERE clause
• It is used to filter records.
• It extracts only those records that fulfill a certain condition.
• Select * from stud where Class>10;
• Select * from stud where Names=’Ajay’;
Condition based on Range [ Between ]
• select names from stud where Age between 16 and 17;
• Both the minimum and maximum range values will be included in the output.
Condition based on a List [ IN ]
• If a list of values are specified, the IN operator is used to select a value that
matches any value in the list.
• select * from stud where age IN (15,17 );
• select * from stud where Names IN (‘Paul‘,’Firoz’,’Binoy’ );
UPDATE STATEMENT
• It is used to modify the existing data in the table.
• UPDATE stud SET Class = 12 WHERE names = “Paul”;
DELETE STATEMENT
• Used to DELETE one or more rows from a table.
• It removes the entire row.
• Delete from stud where Sno=6;
• To remove all the rows of the table use,
DELETE from stud;
SORTING THE RESULTS- ORDER BY
• ORDER BY is used to display the results of the select statement in ascending or
descending values.
• Select * from stud order by age;
• To display in descending order:
Select * from stud order by age desc;
