Class 10 –Computer- SQL
UNIT – (iii) Chap:-11
SQL – STRUCTURED QUERY LANGUAGE
SQL – STRUCTURED QUERY LANGUAGE
SQL:-Structured Query Language (SQL) is the
language used in RDBMS for writing queries. Using
SQL, a user can create queries to fetch and
manipulate the data of the database.
The SQL commands are of five types:
• Data Definition Language (DDL) commands
• Data Manipulation Language (DML) commands
• Transaction Control Language(TCL) commands
• Data Control Language(DCL) commands
• Data Query Language(DQL) commands
SQL – STRUCTURED QUERY LANGUAGE
Data Definition Language (DDL) Commands:-
These commands are used to define and modify the structure of a
database.
The commands that fall under the category are listed as follows:
DDL Commands Usage
CREATE Creates a new database or table
ALTER Modifies the structure of an existing database or table
DROP Deletes an existing database or table
TRUNCATE Removes all table records .
RENAME Renames an existing database or table
SQL – STRUCTURED QUERY LANGUAGE
Data Manipulation Language (DML) Commands
Data manipulation is the process of editing or modifying the
data. A user can manipulate database anytime by using certain
DML commands.
These commands help in retrieval, insertion, deletion, and
modification of the information present in the database.
Following are some of the commonly used DML commands:
DML Commands Usage
INSERT Inserts data into a table
UPDATE Updates the existing data within a table
DELETE Deletes the records from the table
SQL – STRUCTURED QUERY LANGUAGE
Create Command
The CREATE statement is used to create a new database or
table in MS Access or any other RDBMS software. A commonly
used CREATE command is CREATE TABLE.
Syntax:
CREATE TABLE Table_name (
Field_name1 Data type,
Field_name2 Data type,
Field_name3 Data type);
SQL – STRUCTURED QUERY LANGUAGE
In the above syntax:
• Table_name is the name of the table.
• Field_name is the name of the columns (separated by comma).
• Data types are used to specify the type of data a field will hold,
for example, VARCHAR, INTEGER, DATE, etc.
Example:
CREATE TABLE CLIENT(
CLIENT_ID INTEGER,
FIRST_NAME VARCHAR (20),
LAST_NAME VARCHAR (20),
MOBILE_NUMBER INTEGER (10));
SQL – STRUCTURED QUERY LANGUAGE
The above command will create a CLIENT table with the
fields CLIENT_ID, FIRST_NAME, LAST_NAME, and
MOBILE_NUMBER. The CLIENT_ID is of type INTEGER,
so it will hold the integer value, FIRST_NAME,
LAST_NAME, and MOBILE_NUMBER are of type
INTEGER, so they will hold up to 20 characters
SQL – STRUCTURED QUERY LANGUAGE
Create Command With Primary Key
We can create a table with a primary key field using the Create
Table command. To assign a primary key to a field (Stu_ID), you
can type the command as:
CREATE TABLE Results (
STU_ID INTEGER NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR (20),
RESULT VARCHAR(8));
Note:- In this result, STU_ID field is a primary key field. It will support integers
values, and cannot be NULL.
SQL – STRUCTURED QUERY LANGUAGE
RESULT
STU_ID FIRSTNAME LASTNAME RESULT
SQL – STRUCTURED QUERY LANGUAGE
Insert Command
The INSERT command is used to add one or more records to a
table.There are two methods to use the INSERT command.
Method 1: In the first method, you need to specify both the field
names and values to be inserted in a table.
Syntax:
INSERT INTO TABLE_NAME(
Field1, Field2, Field3,…..)
VALUES (Value1, Value2, Value3,…..));
SQL – STRUCTURED QUERY LANGUAGE
Method 2: In the second method, there is no need to specify the
column names if you are adding values for all of them. In such a
case, make sure that you enter the values in the same order as
that of the order of the fields in the table. The syntax of the
INSERT command for this case will be:
INSERT INTO TABLE NAME
VALUES (Value 1, Value2, Value3,_);
Example:
Type the following INSERT command to insert another record in
the Result table.
Insert into Results
Values(102,’Ram’,’Sharma’,’Pass’);
SQL – STRUCTURED QUERY LANGUAGE
Commonly Used SQL Commands:
Select Command
The SELECT statement is used to retrieve multiple records from
one or more tables of a database. It is the most commonly used
DQL command.
There are some optional clauses that can be used with the SELECT
command, i.e., you can use them as per your
• WHERE clause specifies criteria about the rows to be retrieved.
• ORDER BY clause specifies an order in which the rows are to be
retrieved.
SQL – STRUCTURED QUERY LANGUAGE
Retrieving All Records
The following syntax of Select command is used to retrieve all
the records from the table of a database.
Syntax:
SELECT * FROM table_name;
Example:
SELECT * FROM Results;
SQL – STRUCTURED QUERY LANGUAGE
Retrieving Data from Specific Fields
The following syntax of Select query is used to retrieve specific
field values from the table.
Syntax:
SELECT Column, Column2,...
FROM table_name;
Example: On executing the query, you will get the output
SQL – STRUCTURED QUERY LANGUAGE
Commonly Used SQL Commands:
Using Where Clause
The Where clause allows you to fetch specific records based on a
criteria It retrieves only those record that with the specified
criteria. Basically, it works like a filter.
Suppose, from the Results table, you want to fetch the records of
the students who have passed in the exams. In case, you can
give a condition with the RESULT field.
Let us see how to use the Where clause with the Select
command, to retrieve some specific records.
SQL – STRUCTURED QUERY LANGUAGE
To retrieve all the records To retrieve data from specific fields
Syntax: Syntax:
SELECT * SELECT Field1, Field2,…
FROM Table_name FROM Table_name
WHERE Condition; WHERE Condition;
Example: Example:
SELECT * SELECT STU ID, FirstName
FROM RESULTS FROM RESULTS
WHERE RESULT = ‘Pass'; WHERE RESULT = ‘Pass';
SQL – STRUCTURED QUERY LANGUAGE
Commonly Used SQL Commands:
Using Order By Clause
The Order By clause is used to get the records in the ascending
or descending order. By default, it sorts the records in the
ascending order. The DESC keyword is used to sort the records
in the descending order.
To retrieve all the records in the To retrieve data from specific fields in the
ascending order descending order
Syntax: Syntax:
SELECT * SELECT Column1, Column2,…
FROM Table name FROM Table name
ORDER BY Condition; ORDER BY Condition;
SQL – STRUCTURED QUERY LANGUAGE
To retrieve all the records in the To retrieve data from specific fields in
ascending order the descending order
Example: Example:
SELECT * SELECT STU_ID, FIRSTNAME
FROM RESULTS FROM RESULTS
ORDER BY FIRSTNAME; WHERE RESULT = 'Pass'
ORDER BY FIRSTNAME DESC;
SQL – STRUCTURED QUERY LANGUAGE
Commonly Used SQL Commands:
Update Command
Sometimes, you need so modify the existing records in a table.
The UPDATE command of the SQL can be used for this purpose.
Syntax: Example:
UPDATE Table_Name UPDATE Results
SET Column1=value1, SET LASTNAME= 'Sharma',
Column2=value2,…..
WHERE STU_ID=103;
WHERE Condition;
SQL – STRUCTURED QUERY LANGUAGE
Commonly Used SQL Commands:
Delete Command
The DELETE command is used to remove the existing records
from a table.
Syntax:
DELETE FROM Table_Name
WHERE Condition;
Example:
DELETE FROM Results WHERE STU_ID=105:
SQL – STRUCTURED QUERY LANGUAGE
Commonly Used SQL Commands:
Drop Command
The DROP TABLE command is used for removing an existing table
from a database.
Syntax:
DROP TABLE Table_Name;
Example:
DROP TABLE Results;
Thank you
