SQL COMMANDS SYNTAX EXAMPLE
Creating the CREATE DATABASE <DATABASENAME> CREATE DATABASE SCHOOL;
database
Open the database USE <DATABASENAME> USE SCHOOL;
Creating the table CREATE TABLE <TABLENAME> CREATE TABLE
(<COLMNNAME1><DATATYPE1><SIZE>, STUDENT(STUID
<COLUMNNAME2><DATATYPE2><SIZE> INT(3),NAME CHAR(30));
Insert the records INSERT INTO <TABLENAME> INSERT INTO STUDENT
into the table VALUES(VALUE1,VALUE2…> VALUES(1,’JOHN’);
INSERT INTO STUDENT
VALUE(2,’NILA’)
Structure of the DESC <TABLE NAME> DESCRIBE STUDENT
table OR OR
DESCRIBE STUDENT DESC STUDENT
Display all the SHOW DATABASES;
databases
Display the tables SHOW TABLES ;
in the particular
database
Delete the database DROP <DATABASNAME> DROP STUDENT
Delete the table DROP TABLE <TABLEAME> DROP TABLE STUDENT
Adding the column ALTER TABLE <TABLENAME> ADD ALTER TABLE STUDENT ADD
in existing table <COLUMNNAME> <DATATYPE>(SIZE) GENDER CHAR(2);
Adding the column ALTER TABLE <TABLENAME> ADD ALTER TABLE STUDENT ADD
with common data (<COLUMNAME><DATATYPE>DEFAULT (MARKS INT(2) DEFAULT
to the existing table DATA) 100);
Rename the ALTER TABLE <TABLENAME> RENAME ALTER TABLE STUDENT
column name (<COLUMNAME><DATATYPE>DEFAULT RENAME CITY TO STATE
DATA)
Modify the data ALTER TABLE <TABLENAME> MODIFY ALTER TABLE STUDENT
type for the (<COLUMNAME><DATATYPE>SIZE) MODIFY CITY CHAR(40);
particular column
Delete a column ALTER TABLE <TABLENAME> DROP ALTER TABLE STUDENT DROP
from the table (<COLUMNAME> CITY;
Display the whole SELECT * FROM <TABLENAME> SELECT * FROM STUDENT;
records
Display the records SELECT * FROM <TABLENAME> WHERE SELECT * FROM STUDENT
according to the CONIDITION WHERE ROLLNO=101;
condition
Display single SELECT <COLUMNNAME> FROM SELECT CITY FROM
column record <TABLENAME> STUDENT;
Display the record SELECT <COLUMNNAME> FROM SELECT NAME FROM STUDENT
in single column <TABLENAME> WHERE CONDITION WHERE ROLLNO=101;
according to the
condition
Eliminate duplicate SELECT DISTINCT <COLUMN NAME> FROM SELECT DISTINCT(age) FROM
values <TABLENAME> STUDENT;
USING RELATIONAL OPERATORS(>,<,<=,>=,==,!=/<>/!=)
Applying the SELECT <COLUMNNAME> FROM SELECT MARKS FROM STUDENT
relational operators <TABLENAME> WHERE WHERE ROLLNO>100;
using where clause <COLUMNNAME><CONDITION> SELECT NAME,MARKS FROM
STUDENT WHERE ROLLNO>=100
USING LOGICAL OPERATORS(AND,OR,NOT)
Applying more than SELECT <COLUMNNAME> FROM SELECT NAME FROM STUDENT
one condition using <TABLENAME> WHERE WHERE MARKS>=80 AND
where clause <COLUMNNAME><CONDITION> AND GENDER=’M’;
<CONDITION>
SELECT <COLUMNNAME> FROM SELECT NAME FROM STUDENT
<TABLENAME> WHERE WHERE MARKS>=80 OR
<COLUMNNAME><CONDITION> OR GENDER=’M’;
<CONDITION>
SELECT <COLUMNNAME> FROM SELECT NAME FROM STUDENT
<TABLENAME> WHERE WHERE MARKS>=80 NOT
<COLUMNNAME><CONDITION> NOT GENDER=’M’;
<CONDITION>
USING ALIAS NAME
Applying the SELECT <COLUMNNAME> FROM SELECT NAME FROM STUDENT AS
duplicate table name <TABLENAME> AS <ALIASNAME> STUDENT1;
Applying the SELECT <COLUMNNAME> AS SELECT NAME AS STUDENTNAME
duplicate column <ALIASNAME>FROM <TABLENAME> FROM STUDENT
name
Including the text SELECT <COLUMNNAME> SELECT ROLLNO,NAME ,‘WAS
message between the ‘TEXTMESSAGE’ <COLUMNNAME> FROM BORN ON’,DOB FROM STUDENT;
columns <TABLENAME>
SPECIAL OPERATORS ..BETWEEN ..AND
Apply the between SELECT <COLUMNNAME> FROM SELECT ROLLNO,MARKS FROM
and not between <TABLENAME> WHERE <COLUMNNAME> STUDENT WHERE MARKS BETWEEN
BETWEEN <VALUE1> AND <VALUE2> 40 AND 80;
SELECT <COLUMNNAME> FROM SELECT ROLLNO,MARKS FROM
<TABLENAME> WHERE <COLUMNNAME> STUDENT WHERE MARKS NOT
NOT BETWEEN <VALUE1> AND BETWEEN 40 AND 80;
<VALUE2>
CONDITION BASED ON A LIST – IN AND NOT IN
Apply the in and not SELECT <COLUMNNAME> FROM SELECT ROLLNO FROM STUDENT
in values <TABLENAME> WHERE <COLUMNNAME> WHERE CITY IN MUMBAI;
IN <VALUE1>,<VALUE2>…
SELECT <COLUMNNAME> FROM SELECT ROLLNO FROM STUDENT
<TABLENAME> WHERE <COLUMNNAME> WHERE CITY NOT IN MUMBAI;
NOT IN <VALUE1>,<VALUE2>…
CONDTION BASED ON PATTERN—LIKE(USE WILD CARD CHARACTERS
%-MATCHES ANY STRING UNDERSCORE(_) MATCHES ANYONE CHARACTER
SELECT <COLUMNAME> FROM SELECT * FROM STUDENT WHERE
<TABLENAME> WHERE <COLUMNNAME> NAME LIKE “D%”;
LIKE <PATTERN>
The LIKE operator SELECT * FROM STUDENT WHERE
is used in a WHERE NAME LIKE “%D”;
clause to search for SELECT * FROM STUDENT WHERE
a specified pattern NAME LIKE “%D%”;
in a column. SELECT * FROM STUDENT WHERE
NAME LIKE “_D%”;
There are two
wildcards often
used in conjunction
with the LIKE
operator:
• The percent
sign (%)
represents
zero, one, or
multiple
characters
• The
underscore
sign (_)
represents
one, single
character