REVISION OF DML COMMANDS
DML commands are:
1. Insert
2. Select
3. Update
4. Delete
INSERT INTO TABLE
INSERT INTO <TableName> (<Col1> <Col2> <Col3> <Col4>) VALUES
(<val1>,<val2>,<val3>,<val4>,. . .);
For ex- student((Roll, Name, Class, DOB)
(i) Inserting data into all columns of a table:
INSERT INTO student VALUES (101,’Rohit’,’I’,’2013-12-23’);
(ii) ) Inserting data into specific columns of a table:
INSERT INTO student(Roll, Name) VALUES(102,‘Swati Mehra’);
(iii) Inserting NULL values into a table:
INSERT INTO student(Rollno, Name, Class, Age , City) VALUES(12,‘Swati Mehra’, ‘X’, NULL);
SELECT COMMAND
SELECT [DISTINCT] <Col Names> , ….. FROM <Table Name> WHERE <Condition>
ORDER BY <column_name>;
For Ex:
SELECT * FROM student; # * means all columns & all records
SELECT * FROM student WHERE City = “Dimapur”; # selected records
SELECT Roll, Name FROM student; # selected columns and all records
SELECT Roll , Name FROM student WHERE City = “Dimapur”; # selected records and selected
columns
DISTINCT clause:
SELECT DISTINCT Class from student ;
SQL Operators
Arithmetic Operators
Relational Operators
Logical Operators
Special Operators
Arithmetic Operators : Arithmetic operators are used to perform simple arithmetic operations
like addition (+), subtraction (–), multiplication (*), division (/) and modulus (%).
For Ex:
SELECT 5 + 10 FROM DUAL;
SELECT Roll , Name , marks, marks + (marks* 5)/100 FROM student;
Relational Operators : [ < , <= , > , >= , = , != , <> ]
SELECT Rollno, Name, Marks FROM student WHERE Marks>=90;
Logical Operators : AND, OR and NOT operator.
SELECT * FROM student WHERE Marks > 80 and Gender= ‘M’;
SELECT Rollno, Name, Stream FROM student WHERE Stream= ‘Science’ or Stream=
‘Commerce’;
SELECT Name, Marks FROM student WHERE NOT (Stream = ‘Vocational’);
SQL Special operators
Between
1. SELECT Rollno, Name, Marks FROM student WHERE Marks BETWEEN 80 AND 100;
2. SELECT Rollno, Name, Marks FROM student WHERE Marks NOT BETWEEN 80 AND 100;
In
1. SELECT * FROM student WHERE Stream IN (‘Science’, ‘Commerce’, ‘Humanities’);
2. SELECT * FROM student WHERE Stream NOT IN (‘Science’, ‘Commerce’, ‘Humanities’);
Like
SQL provides two wild card characters that are used while comparing the strings with LIKE operator:
a. Percent(%) : Matches any string
b. Underscore(_) : Matches any one character
1. SELECT * FROM student WHERE Name LIKE “D%”;
2. SELECT * FROM student WHERE Name LIKE “%a”;
3. SELECT * FROM student WHERE Name LIKE “%e%;
4. SELECT * FROM student WHERE Name LIKE “_e%;
5. SELECT Rollno, Name, Marks, DOB FROM student WHERE Name LIKE “%r_ _”;
Order By
The SQL ORDER BY clause is used to sort the data in ascending or descending order based on one
or more columns.
This clause sorts the records in the ascending order (ASC) by default.
To sort the records in descending order, DESC keyword is to be used.
Sorting using ORDER BY clause can be done on multiple columns, separated by
comma.
SELECT <column-list> FROM <table_name> [WHERE <condition>]
ORDER BY <column_name> [ASC|DESC];
For example :
(a) SELECT Roll, Name FROM student ORDER BY Name;
(b) SELECT Rollno, Name, Marks FROM student ORDER BY Marks DESC, Name;
UPDATING A RECORD IN TABLE
UPDATE <table_name> SET <column1> = <value1>, <column2> = <value2>,.. WHERE <condition>;
For ex- UPDATE student SET Class = ‘II’ WHERE Roll=102; OR
UPDATE student SET Class = ‘II’;
(a) Updating multiple columns: UPDATE student SET Age = 12, DOB=‘1998-08-11’ WHERE
Name=“Payal”;
(b) Updating to NULL values : UPDATE student SET Marks = NULL WHERE Rollno = 9;
(c) Updating using an expression or formula : UPDATE student SET Marks = Marks + 10 WHERE
Roll = 5;
DELETE TABLE COMMAND:
DELETE FROM <table_name> WHERE <condition>;
For example :
(a) To delete the record only for roll number 10.
DELETE FROM student WHERE Rollno = 10;
(b) To delete all the rows from the student table.
DELETE FROM student;
Viewing Tables in a Database
USE <database_name>;
SHOW tables;
For Ex:
USE school;
SHOW tables;
