SQL COMMANDS
*To create a database:
CREATE DATABASE VVM; Here VVM is the name of the database.
*To open the database:
USE VVM;
*To SHOW ALL THE TABLE NAMES:
SHOW TABLES;
*TO DISPLAY THE STRUCTURE OF A TABLE:
DESC/DESCRIBE STUDENT;
*TO DISPLAY ALL THE DETAILS OF A TABLE:
SELECT * FROM STUDENT;
*TO DISPLAY SPECIFIC COLUMNS OF A TABLE:
SELECT ROLL, NAME, MARKS FROM STUDENT;
*TO DISPLAY SPECIFIC RECORDS BASED ON A CONDITION:
SELECT * FROM STUDENT WHERE MARKS >=75;
*TO DISPLAY SPECIFIC COLUMN AND ROW
SELECT NAME, MARKS FROM STUDENT WHERE GRADE=’A’;
Write a command to display the name and the stream of those students who has secured >=80 marks and
from science stream.
SELECT NAME, STREAM FROM STUDENT
WHERE MARKS>=80 AND STREAM=’SCIENCE’;
To display the details of those students who has secured marks in the range 40 to 60 (Both numbers included)
Select * from student where marks between 40 and 60;
To display the name and marks for those student whose marks <40 and >70;
SELECT NAME, MARKS FROM STUDENT WHERE MARKS<40 AND MARKS>70;
OR
SELECT NAME, MARKS FROM STUDENT WHERE MARKS NOT BETWEEN 40 AND 70;
PATTERN MATCHING: FOR THIS TWO WILDCARD CHARACTERS ARE USED.
_ REPRESENTS A SINGLE CHARACTER
% REPRESENTS A COLLECTION OF CHARCTERS
TO DISPLAY DETAILS OF THOSE STUDENTS WHO ARE FROM CLASS XII.
SELECT * FROM STUDENT WHERE CLASS LIKE ‘XII%’;
TO DISPLAY NAME AND MARKS OF THOSE STUDENTS WHOSE NAME STARTS WITH ‘A’.
SELECT NAME, MARKS FROM STUDENTS WHERE NAME LIKE ‘A%’;
TO DISPLAY NAME AND MARKS OF THOSE STUDENTS WHOSE NAME ENDS WITH ‘A’.
SELECT NAME, MARKS FROM STUDENTS WHERE NAME LIKE ‘%A’;
TO DISPLAY NAMES OF THOSE STUDENTS WHOSE NAMES ARE OF 4 CHARACTERS LIKE ‘AMAN’
SELECT NAME FROM STUDENT WHERE NAME LIKE ‘_ _ _ _’;
TO DISPLAY DETAILS OF THOSE STUDENT WHOSE NAME CONTAINS ‘i’ AS 3RD LETTER.
SELECTT * FROM STUDENT WHERE NAME LIKE ‘_ _ i %’;
TO DISPLAY DETAILS OF THOSE MEMBERS FROM THE TABLE MEMBER WHOSE PINCODE STARTS WITH 13.
SELECT * FROM MEMBER WHERE PINCODE LIKE ‘13%’;
TO DISPLAY THE MEMBER NAME AND THEIR CITY WHOSE PINCODE ENDS WITH 4.
SELECT MNAME, CITY FROM MEMBER WHERE PINCODE LIKE ‘%4’;
TO DISPLAY DETAILS OF THOSE MEMBERS WHO ARE FROM THE CITY RANCHI, BOKARO, JAMSHEDPUR AND
DHANBAD.
SELECT * FROM MEMBER WHERE CITY IN (‘RANCHI’, ‘BOKARO’, ‘JAMSHEDPUR’, ‘DHANBAD’);
TO DISPLAY THE MEMBER NAMES WHO ARE NOT FROM THESE CITIES.
SELECT NAME FROM MEMBER WHERE CITY NOT IN (‘RANCHI’, ‘BOKARO’, ‘JAMSHEDPUR’, ‘DHANBAD’);
TO DISPLAY THE RECORDS IN A PARTICULAR ORDER I.E. ASCENDING OR DESCENCDING:
SELECT * FROM STUDENT ORDER BY NAME;
TO DISPLAY NAME AND MARKS OF THOSE STUDENTS WHO HAS SECURED >=60 IN THE DESCENDING ORDER
OF MARKS.
SELECT NAME, MARKS FROM STUDENT
WHERE MARKS >= 60
ORDER BY MARKS DESC;
AGGREGATE FUNCTIONS:
SELECT SUM(MARKS) FROM STUDENT WHERE CLASS = ‘XIID’;
SELECT AVG(MARKS) FROM STUDENT WHERE CLASS = ‘XIID’;
SELECT MAX(MARKS) FROM STUDENT WHERE CLASS = ‘XIID’;
SELECT MIN(MARKS) FROM STUDENT WHERE CLASS = ‘XIID’;
SELECT COUNT(*) FROM STUDENT WHERE CLASS = ‘XIID’;
SELECT COUNT(NAME) FROM STUDENT WHERE CLASS = ‘XIID’;
To insert a record in a table INSERT INTO command is used. Eg. To insert a record in the STUDENT table having
fields Roll, Name, Class, Marks the command will be:
INSERT INTO STUDENT VALUES( 23,”AMAN”,”XII D”, 456);
To update a record the UPDATE command is used. Eg. To change the marks of the student AMAN the
command will be:
UPDATE STUDENT
SET MARKS=465
WHERE NAME=’AMAN’;
To delete a record DELETE command is used. Eg. To remove the record of Aman from the table student.
DELETE FROM STUDENT
WHERE NAME=’AMAN’;