CREATE Command
• SQL CREATE command is a type of DDL Command and is among the commands which is
primarily used for creating databases and tables. The CREATE command has a particular
syntax which needs to be followed In order to create databases or tables with desired
structure.
• Before executing any other functions, we need to create database and it is the first step
towards learning SQL.
Syntax
OR
OR
Ex-
• Consider a database of school named as “db_school“ , containing student and teacher as
two tables present in it.
Step-1 : Create the database first.
CREATE Database db_school;
Step2 : For creating tables in this database. Firstly we need to get inside the database. This can be
done using.
USE db_school;
Step-3 : For creating tables in “db_school”, use following query.
CREATE Table teacher ( Id int, Name varchar(20),
Designation varchar(20) );
CREATE Table student( Roll_no int,
Name varchar(20), Marks int );
• Both the tables are now successfully created. If the user wants to check the structure of
tables and how they look like, below query can be executed.
Show Tables ;
DESC tablename
Points:
• Always mention primary key while creating the tables inside the database. Example syntax is
mentioned below.
Syntax : CREATE Table employee
(ID int NOT NULL Primary Key,
Name Varchar, Dept Varchar(20) );
Here, “ID” of the of an employee will remain unique and can act as a primary key.
INSERT Command
• Once the tables in the databases are created, it’s now time to insert values into these tables.
This can be done using SQL INSERT command. INSERT command can be used in two
different types. Both of them are explained below
INSERT Command
OR
INSERT Command
• Inserting values in each tables student and teacher of the database db_school.
i) INSERT Into teacher Values(101, ‘Amit Sharma’, ‘PGT’);
ii) INSERT Into student Values(1, ‘Ashwani Singh’, 87);
The thing one must remember while inserting the data in the database is the order of columns. If
value of roll_no is inserted into name or vice-versa, the query will not execute.
• Also, values should be inserted according to the data types mentioned while creating the
tables.
SELECT Command
The SQL SELECT command is the only Data Query Language Command over which whole data
extraction and retrieval is [Link] SQL SELECT command is used for “fetching and
extracting data from databases or tables”.
• There are a number of possible combinations while using SELECT query out of which some
are listed below.
OR
OR
OR
SQL Clauses
• Clauses means “Conditions or Part or Section”. SQL clauses are used in SQL queries to
extract or update or manipulate data in/from the database.
FROM Clause
• The FROM clause is the most basic clause and is widely used in almost all the SQL queries.
The from clause is used to fetch data in form of results from the database or tables.
Syntax : SELECT * FROM Table_Name;
WHERE Clause
• The WHERE clause in SQL is an abstract form/type of FROM clause. The WHERE clause is
added along with FROM clause in order to generate most filtered/summarized result.
Syntax : SELECT * FROM Table_Name WHERE “Condition”;
For Ex :
SELECT * FROM Student WHERE Roll_No = 1;
DISTINCT Clause
• The DISTINCT clause in SQL is used to fetch unique and non-redundant data/records every
time the query is executed. It is used with SELECT clause.
Syntax : SELECT DISTINCT Column_Name1, Column_Name2,… FROM Table_Name;
For Ex :
SELECT DISTINCT City FROM Student;
GROUP BY Clause
The GROUP BY clause in SQL is used with SQL aggregate functions such as “Min”, “Max”, “Avg”,
“Sum” and “Count”. The GROUP BY clause will return the summarized data into possible categories
according to the fixed query.
Syntax : SELECT Column1, Column2,… FROM Table_Name GROUP By Column1;
For Ex :
SELECT Count (Roll_No), Gender FROM Student GROUP By Gender;
HAVING Clause
• HAVING clause in SQL is also used with SQL aggregate functions such as Min, Max, Avg,
Sum and Count. Whenever a condition marked with HAVING clause is true, then only the
query will return the result. Also, HAVING clause is used with GROUP BY clause always.
Syntax : SELECT Column1, Column2,… FROM Table_Name
GROUP By “Condition” HAVING “Condition”;
For Ex : Consider following query HAVING clause can be used to fetch data from table on the
basis of location
SELECT Count(Roll_No), Location FROM Student
GROUP BY Location HAVING Count(Roll_No) >= 2;
The ORDER BY Clause
• ORDER BY clause is a simple keyword which can be used with SQL aggregate function or
HAVING clause or GROUP BY clause. The main function of ORDER BY clause is to sort the
result in either ascending or descending order. This can be done using either of the two
keywords.
o ASC – For sorting results in ascending order.
o DESC – For sorting results in descending order.
• If we do not place either “ASC or DESC” at the end of the query, by default it query will sort
data in ascending order.
Syntax : SELECT Column1, Cloumn2,… FROM Table_Name
ORDER BY Column_Name ; (Default/Ascending);
OR
Syntax : SELECT Column1, Cloumn2,… FROM Table_Name
ORDER BY Column_Name ASC; (For Ascending Order);
OR
Syntax : SELECT Column1, Cloumn2,… FROM Table_Name
ORDER BY Column_Name DESC; (For Descending Order);
Ex-1 :
SELECT * FROM Student ORDER BY Location DESC;
Ex-2 : ORDER BY clause also supports SQL aggregate functions. Consider the same table used
above and below is the sample query for ORDER BY with SQL aggregate functions.
SELECT Count(Roll_No), Location From Student GROUP BY Location
ORDER BY Count(Roll_No) DESC;
UPDATE Command
• The UPDATE command is used to “Update or Modify” the existing values/records in the
database tables. The records which needs to be modified can be a single one or multiple
values which can be based upon the query used.
Syntax
• The syntax of UPDATE Command in SQL looks like :
If WHERE clause is not used then new values will be set for all records in table.
Ex : Write SQL statement to update marks of student “SAURAV”.
UPDATE student_details SET marks = ’70’ where roll_no = 1;