NCERT Textbook: Introduction to Structured Query Language (SQL) | Informatics Practices for Class 11 - Humanities/Arts PDF Download

 Page 1


8.1 Introduct Ion We have learnt about Relational Database 
Management System (RDBMS) and purpose in the 
previous chapter. There are many RDBMS such 
as MySQL, Microsoft SQL Server, PostgreSQL, 
Oracle, etc. that allow us to create a database 
consisting of relations and to link one or more 
relations for efficient querying to store, retrieve 
and manipulate data on that database. In this 
chapter, we will learn how to create, populate and 
query database using MySQL.
In this chapter
 » Introduction 
 » Structured Query 
Language (SQL)  
 » Data Types and 
Constraints in MySQL
 » SQL for Data Definition
 » SQL for Data 
Manipulation
 » SQL for Data Query
 » Data Updation and 
Deletion
Introduction to  
Structured Query 
Language (SQL) 
Chapter 
8 
“The most important motivation for the 
research work that resulted in the relational 
model was the objective of providing a sharp 
and clear boundary between the logical and 
physical aspects of database management.” 
– E. F. Codd
Chap 8.indd   143 19-Jul-19   3:45:57 PM
2024-25
Page 2


8.1 Introduct Ion We have learnt about Relational Database 
Management System (RDBMS) and purpose in the 
previous chapter. There are many RDBMS such 
as MySQL, Microsoft SQL Server, PostgreSQL, 
Oracle, etc. that allow us to create a database 
consisting of relations and to link one or more 
relations for efficient querying to store, retrieve 
and manipulate data on that database. In this 
chapter, we will learn how to create, populate and 
query database using MySQL.
In this chapter
 » Introduction 
 » Structured Query 
Language (SQL)  
 » Data Types and 
Constraints in MySQL
 » SQL for Data Definition
 » SQL for Data 
Manipulation
 » SQL for Data Query
 » Data Updation and 
Deletion
Introduction to  
Structured Query 
Language (SQL) 
Chapter 
8 
“The most important motivation for the 
research work that resulted in the relational 
model was the objective of providing a sharp 
and clear boundary between the logical and 
physical aspects of database management.” 
– E. F. Codd
Chap 8.indd   143 19-Jul-19   3:45:57 PM
2024-25
144
Informat Ics Pract Ices – c lass XI
8.2 Structured Query Language (SQL) 
One has to write application programs to access data in 
case of a file system. However, for database management 
systems there are special kind of programming 
languages called query language that can be used to 
access data from the database. The Structured Query 
Language (SQL) is the most popular query language 
used by major relational database management systems 
such as MySQL, ORACLE, SQL Server, etc.
SQL is easy to learn as the statements comprise of 
descriptive English words and are not case sensitive. 
We can create and interact with a database using SQL 
in an efficient and easy way. The benefit with SQL is 
that we don’t have to specify how to get the data from 
the database. Rather, we simply specify what is to be 
retrieved, and SQL does the rest. Although called a query 
language, SQL can do much more besides querying. 
SQL provides statements for defining the structure of 
the data, manipulating data in the database, declare 
constraints and retrieve data from the database in 
various ways, depending on our requirements.
In this chapter, we will learn how to create a database 
using MySQL as the RDBMS software. We will create a 
database called StudentAttendance (Figure 7.5) that we 
had identified in the previous chapter. We will also learn 
how to populate database with data, manipulate data in 
that and retrieve data from the database through SQL 
queries.
8.2.1 Installing MySQL
MySQL is an open source RDBMS software which can 
be easily downloaded from the official website https://
dev.mysql.com/downloads. After installing MySQL, 
start MySQL service. The appearance of mysql> prompt 
(Figure 8.1) means that MySQL is ready for us to enter 
SQL statements.
Few rules to follow while writing SQL statements in 
MySQL:
• SQL is case insensitive. That means name and NAME 
are same for SQL.
• Always end SQL statements with a semicolon (;). 
• To enter multiline SQL statements, we don’t write 
‘;’ after the first line. We put enter to continue on 
next line. The prompt mysql> then changes to ‘->’, 
Activity 8.1
Explore LibreOffice 
Base and compare it 
with MySQL
Chap 8.indd   144 19-Jul-19   3:45:57 PM
2024-25
Page 3


8.1 Introduct Ion We have learnt about Relational Database 
Management System (RDBMS) and purpose in the 
previous chapter. There are many RDBMS such 
as MySQL, Microsoft SQL Server, PostgreSQL, 
Oracle, etc. that allow us to create a database 
consisting of relations and to link one or more 
relations for efficient querying to store, retrieve 
and manipulate data on that database. In this 
chapter, we will learn how to create, populate and 
query database using MySQL.
In this chapter
 » Introduction 
 » Structured Query 
Language (SQL)  
 » Data Types and 
Constraints in MySQL
 » SQL for Data Definition
 » SQL for Data 
Manipulation
 » SQL for Data Query
 » Data Updation and 
Deletion
Introduction to  
Structured Query 
Language (SQL) 
Chapter 
8 
“The most important motivation for the 
research work that resulted in the relational 
model was the objective of providing a sharp 
and clear boundary between the logical and 
physical aspects of database management.” 
– E. F. Codd
Chap 8.indd   143 19-Jul-19   3:45:57 PM
2024-25
144
Informat Ics Pract Ices – c lass XI
8.2 Structured Query Language (SQL) 
One has to write application programs to access data in 
case of a file system. However, for database management 
systems there are special kind of programming 
languages called query language that can be used to 
access data from the database. The Structured Query 
Language (SQL) is the most popular query language 
used by major relational database management systems 
such as MySQL, ORACLE, SQL Server, etc.
SQL is easy to learn as the statements comprise of 
descriptive English words and are not case sensitive. 
We can create and interact with a database using SQL 
in an efficient and easy way. The benefit with SQL is 
that we don’t have to specify how to get the data from 
the database. Rather, we simply specify what is to be 
retrieved, and SQL does the rest. Although called a query 
language, SQL can do much more besides querying. 
SQL provides statements for defining the structure of 
the data, manipulating data in the database, declare 
constraints and retrieve data from the database in 
various ways, depending on our requirements.
In this chapter, we will learn how to create a database 
using MySQL as the RDBMS software. We will create a 
database called StudentAttendance (Figure 7.5) that we 
had identified in the previous chapter. We will also learn 
how to populate database with data, manipulate data in 
that and retrieve data from the database through SQL 
queries.
8.2.1 Installing MySQL
MySQL is an open source RDBMS software which can 
be easily downloaded from the official website https://
dev.mysql.com/downloads. After installing MySQL, 
start MySQL service. The appearance of mysql> prompt 
(Figure 8.1) means that MySQL is ready for us to enter 
SQL statements.
Few rules to follow while writing SQL statements in 
MySQL:
• SQL is case insensitive. That means name and NAME 
are same for SQL.
• Always end SQL statements with a semicolon (;). 
• To enter multiline SQL statements, we don’t write 
‘;’ after the first line. We put enter to continue on 
next line. The prompt mysql> then changes to ‘->’, 
Activity 8.1
Explore LibreOffice 
Base and compare it 
with MySQL
Chap 8.indd   144 19-Jul-19   3:45:57 PM
2024-25
Introduct Ion to Structured Query Language (SQL)
145
indicating that statement is continued to the next 
line. After the last line, put ‘;’ and press enter.  
8.3 d ata t ype S and c on Stra Int S In My SQL
We know that a database consists of one or more 
relations and each relation (table) is made up of attributes 
(column). Each attribute has a data type. We can also 
specify constraints for each attribute of a relation.
8.3.1 Data type of Attribute
Data type indicates the type of data value that an 
attribute can have. The data type of an attribute decides 
the operations that can be performed on the data of 
that attribute. For example, arithmetic operations can 
be performed on numeric data but not on character 
data. Commonly used data types in MySQL are numeric 
types, date and time types, and string (character and 
byte) types as shown in Table 8.1.
Figure 8.1: MySQL Shell
Think and Reflect
Can you think of an 
attribute for which 
fixed length string is 
suitable?
Table 8.1 Commonly used data types in MySQL
Data type Description
CHAR(n)
Specifies character type data of length n where n could be any value from 0 to 
255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve 
spaces for 10 characters. If data does not have 10 characters (for example, 
‘city’ has four characters), MySQL fills the remaining 6 characters with spaces 
padded on the right.
VARCHAR(n)
Specifies character type data of length ‘n’ where n could be any value from 0 
to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, 
declaring VARCHAR (30) means a maximum of 30 characters can be stored 
but the actual allocated bytes will depend on the length of entered string. So 
‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only.
Activity 8.2
What are the other 
data types supported in 
MySQL? Are there other 
variants of integer and 
float data type?
Chap 8.indd   145 19-Jul-19   3:45:57 PM
2024-25
Page 4


8.1 Introduct Ion We have learnt about Relational Database 
Management System (RDBMS) and purpose in the 
previous chapter. There are many RDBMS such 
as MySQL, Microsoft SQL Server, PostgreSQL, 
Oracle, etc. that allow us to create a database 
consisting of relations and to link one or more 
relations for efficient querying to store, retrieve 
and manipulate data on that database. In this 
chapter, we will learn how to create, populate and 
query database using MySQL.
In this chapter
 » Introduction 
 » Structured Query 
Language (SQL)  
 » Data Types and 
Constraints in MySQL
 » SQL for Data Definition
 » SQL for Data 
Manipulation
 » SQL for Data Query
 » Data Updation and 
Deletion
Introduction to  
Structured Query 
Language (SQL) 
Chapter 
8 
“The most important motivation for the 
research work that resulted in the relational 
model was the objective of providing a sharp 
and clear boundary between the logical and 
physical aspects of database management.” 
– E. F. Codd
Chap 8.indd   143 19-Jul-19   3:45:57 PM
2024-25
144
Informat Ics Pract Ices – c lass XI
8.2 Structured Query Language (SQL) 
One has to write application programs to access data in 
case of a file system. However, for database management 
systems there are special kind of programming 
languages called query language that can be used to 
access data from the database. The Structured Query 
Language (SQL) is the most popular query language 
used by major relational database management systems 
such as MySQL, ORACLE, SQL Server, etc.
SQL is easy to learn as the statements comprise of 
descriptive English words and are not case sensitive. 
We can create and interact with a database using SQL 
in an efficient and easy way. The benefit with SQL is 
that we don’t have to specify how to get the data from 
the database. Rather, we simply specify what is to be 
retrieved, and SQL does the rest. Although called a query 
language, SQL can do much more besides querying. 
SQL provides statements for defining the structure of 
the data, manipulating data in the database, declare 
constraints and retrieve data from the database in 
various ways, depending on our requirements.
In this chapter, we will learn how to create a database 
using MySQL as the RDBMS software. We will create a 
database called StudentAttendance (Figure 7.5) that we 
had identified in the previous chapter. We will also learn 
how to populate database with data, manipulate data in 
that and retrieve data from the database through SQL 
queries.
8.2.1 Installing MySQL
MySQL is an open source RDBMS software which can 
be easily downloaded from the official website https://
dev.mysql.com/downloads. After installing MySQL, 
start MySQL service. The appearance of mysql> prompt 
(Figure 8.1) means that MySQL is ready for us to enter 
SQL statements.
Few rules to follow while writing SQL statements in 
MySQL:
• SQL is case insensitive. That means name and NAME 
are same for SQL.
• Always end SQL statements with a semicolon (;). 
• To enter multiline SQL statements, we don’t write 
‘;’ after the first line. We put enter to continue on 
next line. The prompt mysql> then changes to ‘->’, 
Activity 8.1
Explore LibreOffice 
Base and compare it 
with MySQL
Chap 8.indd   144 19-Jul-19   3:45:57 PM
2024-25
Introduct Ion to Structured Query Language (SQL)
145
indicating that statement is continued to the next 
line. After the last line, put ‘;’ and press enter.  
8.3 d ata t ype S and c on Stra Int S In My SQL
We know that a database consists of one or more 
relations and each relation (table) is made up of attributes 
(column). Each attribute has a data type. We can also 
specify constraints for each attribute of a relation.
8.3.1 Data type of Attribute
Data type indicates the type of data value that an 
attribute can have. The data type of an attribute decides 
the operations that can be performed on the data of 
that attribute. For example, arithmetic operations can 
be performed on numeric data but not on character 
data. Commonly used data types in MySQL are numeric 
types, date and time types, and string (character and 
byte) types as shown in Table 8.1.
Figure 8.1: MySQL Shell
Think and Reflect
Can you think of an 
attribute for which 
fixed length string is 
suitable?
Table 8.1 Commonly used data types in MySQL
Data type Description
CHAR(n)
Specifies character type data of length n where n could be any value from 0 to 
255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve 
spaces for 10 characters. If data does not have 10 characters (for example, 
‘city’ has four characters), MySQL fills the remaining 6 characters with spaces 
padded on the right.
VARCHAR(n)
Specifies character type data of length ‘n’ where n could be any value from 0 
to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, 
declaring VARCHAR (30) means a maximum of 30 characters can be stored 
but the actual allocated bytes will depend on the length of entered string. So 
‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only.
Activity 8.2
What are the other 
data types supported in 
MySQL? Are there other 
variants of integer and 
float data type?
Chap 8.indd   145 19-Jul-19   3:45:57 PM
2024-25
146
Informat Ics Pract Ices – c lass XI
Think and Reflect
Which two constraints 
when applied together 
will produce a Primary 
Key constraint?
INT
INT specifies an integer value. Each INT value occupies 4 bytes of storage. The 
range of values allowed in integer type are -2147483648 to 2147483647. For 
values larger than that, we have to use BIGINT, which occupies 8 bytes.
FLOAT
Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
DATE
The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit 
year, MM is the 2 digit month and DD is the 2 digit date. The supported range 
is '1000-01-01' to '9999-12-31'.
8.3.2 Constraints
Constraints are certain types of restrictions on the data 
values that an attribute can have. They are used to 
ensure the accuracy and reliability of data. However, it 
is not mandatory to define constraint for each attribute 
of a table. Table 8.2 lists various SQL constraints.
Table 8.2 Commonly used SQL Constraints
Constraint Description
NOT NULL
Ensures that a column cannot have NULL values where NULL means missing/
unknown/not applicable value.
UNIQUE
Ensures that all the values in a column are distinct/unique.
DEFAULT
A default value specified for the column if no value is provided.
PRIMARY KEY
The column which can uniquely identify each row or record in a table.
FOREIGN KEY
The column which refers to value of an attribute defined as primary key in another 
table.
8.4 SQL for d ata d ef In It Ion SQL provides commands for defining the relation 
schemas, modifying relation schemas and deleting 
relations. These are called Data Definition Language 
(DDL) through which the set of relations are specified, 
including their schema, data type for each attribute, the 
constraints as well as the security and access related 
authorisations.
Data definition starts with the create statement. This 
statement is used to create a database and its tables 
(relations). Before creating a database, we should be 
clear about the number of tables in the database, the 
columns (attributes) in each table along with the data 
type of each column. This is how we decide the relation 
schema.
8.4.1 CREATE Database
To create a database, we use the CREATE DATABASE 
statement as shown in the following syntax:
CREATE DATABASE databasename;
Chap 8.indd   146 19-Jul-19   3:45:57 PM
2024-25
Page 5


8.1 Introduct Ion We have learnt about Relational Database 
Management System (RDBMS) and purpose in the 
previous chapter. There are many RDBMS such 
as MySQL, Microsoft SQL Server, PostgreSQL, 
Oracle, etc. that allow us to create a database 
consisting of relations and to link one or more 
relations for efficient querying to store, retrieve 
and manipulate data on that database. In this 
chapter, we will learn how to create, populate and 
query database using MySQL.
In this chapter
 » Introduction 
 » Structured Query 
Language (SQL)  
 » Data Types and 
Constraints in MySQL
 » SQL for Data Definition
 » SQL for Data 
Manipulation
 » SQL for Data Query
 » Data Updation and 
Deletion
Introduction to  
Structured Query 
Language (SQL) 
Chapter 
8 
“The most important motivation for the 
research work that resulted in the relational 
model was the objective of providing a sharp 
and clear boundary between the logical and 
physical aspects of database management.” 
– E. F. Codd
Chap 8.indd   143 19-Jul-19   3:45:57 PM
2024-25
144
Informat Ics Pract Ices – c lass XI
8.2 Structured Query Language (SQL) 
One has to write application programs to access data in 
case of a file system. However, for database management 
systems there are special kind of programming 
languages called query language that can be used to 
access data from the database. The Structured Query 
Language (SQL) is the most popular query language 
used by major relational database management systems 
such as MySQL, ORACLE, SQL Server, etc.
SQL is easy to learn as the statements comprise of 
descriptive English words and are not case sensitive. 
We can create and interact with a database using SQL 
in an efficient and easy way. The benefit with SQL is 
that we don’t have to specify how to get the data from 
the database. Rather, we simply specify what is to be 
retrieved, and SQL does the rest. Although called a query 
language, SQL can do much more besides querying. 
SQL provides statements for defining the structure of 
the data, manipulating data in the database, declare 
constraints and retrieve data from the database in 
various ways, depending on our requirements.
In this chapter, we will learn how to create a database 
using MySQL as the RDBMS software. We will create a 
database called StudentAttendance (Figure 7.5) that we 
had identified in the previous chapter. We will also learn 
how to populate database with data, manipulate data in 
that and retrieve data from the database through SQL 
queries.
8.2.1 Installing MySQL
MySQL is an open source RDBMS software which can 
be easily downloaded from the official website https://
dev.mysql.com/downloads. After installing MySQL, 
start MySQL service. The appearance of mysql> prompt 
(Figure 8.1) means that MySQL is ready for us to enter 
SQL statements.
Few rules to follow while writing SQL statements in 
MySQL:
• SQL is case insensitive. That means name and NAME 
are same for SQL.
• Always end SQL statements with a semicolon (;). 
• To enter multiline SQL statements, we don’t write 
‘;’ after the first line. We put enter to continue on 
next line. The prompt mysql> then changes to ‘->’, 
Activity 8.1
Explore LibreOffice 
Base and compare it 
with MySQL
Chap 8.indd   144 19-Jul-19   3:45:57 PM
2024-25
Introduct Ion to Structured Query Language (SQL)
145
indicating that statement is continued to the next 
line. After the last line, put ‘;’ and press enter.  
8.3 d ata t ype S and c on Stra Int S In My SQL
We know that a database consists of one or more 
relations and each relation (table) is made up of attributes 
(column). Each attribute has a data type. We can also 
specify constraints for each attribute of a relation.
8.3.1 Data type of Attribute
Data type indicates the type of data value that an 
attribute can have. The data type of an attribute decides 
the operations that can be performed on the data of 
that attribute. For example, arithmetic operations can 
be performed on numeric data but not on character 
data. Commonly used data types in MySQL are numeric 
types, date and time types, and string (character and 
byte) types as shown in Table 8.1.
Figure 8.1: MySQL Shell
Think and Reflect
Can you think of an 
attribute for which 
fixed length string is 
suitable?
Table 8.1 Commonly used data types in MySQL
Data type Description
CHAR(n)
Specifies character type data of length n where n could be any value from 0 to 
255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve 
spaces for 10 characters. If data does not have 10 characters (for example, 
‘city’ has four characters), MySQL fills the remaining 6 characters with spaces 
padded on the right.
VARCHAR(n)
Specifies character type data of length ‘n’ where n could be any value from 0 
to 65535. But unlike CHAR, VARCHAR is a variable-length data type. That is, 
declaring VARCHAR (30) means a maximum of 30 characters can be stored 
but the actual allocated bytes will depend on the length of entered string. So 
‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only.
Activity 8.2
What are the other 
data types supported in 
MySQL? Are there other 
variants of integer and 
float data type?
Chap 8.indd   145 19-Jul-19   3:45:57 PM
2024-25
146
Informat Ics Pract Ices – c lass XI
Think and Reflect
Which two constraints 
when applied together 
will produce a Primary 
Key constraint?
INT
INT specifies an integer value. Each INT value occupies 4 bytes of storage. The 
range of values allowed in integer type are -2147483648 to 2147483647. For 
values larger than that, we have to use BIGINT, which occupies 8 bytes.
FLOAT
Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
DATE
The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit 
year, MM is the 2 digit month and DD is the 2 digit date. The supported range 
is '1000-01-01' to '9999-12-31'.
8.3.2 Constraints
Constraints are certain types of restrictions on the data 
values that an attribute can have. They are used to 
ensure the accuracy and reliability of data. However, it 
is not mandatory to define constraint for each attribute 
of a table. Table 8.2 lists various SQL constraints.
Table 8.2 Commonly used SQL Constraints
Constraint Description
NOT NULL
Ensures that a column cannot have NULL values where NULL means missing/
unknown/not applicable value.
UNIQUE
Ensures that all the values in a column are distinct/unique.
DEFAULT
A default value specified for the column if no value is provided.
PRIMARY KEY
The column which can uniquely identify each row or record in a table.
FOREIGN KEY
The column which refers to value of an attribute defined as primary key in another 
table.
8.4 SQL for d ata d ef In It Ion SQL provides commands for defining the relation 
schemas, modifying relation schemas and deleting 
relations. These are called Data Definition Language 
(DDL) through which the set of relations are specified, 
including their schema, data type for each attribute, the 
constraints as well as the security and access related 
authorisations.
Data definition starts with the create statement. This 
statement is used to create a database and its tables 
(relations). Before creating a database, we should be 
clear about the number of tables in the database, the 
columns (attributes) in each table along with the data 
type of each column. This is how we decide the relation 
schema.
8.4.1 CREATE Database
To create a database, we use the CREATE DATABASE 
statement as shown in the following syntax:
CREATE DATABASE databasename;
Chap 8.indd   146 19-Jul-19   3:45:57 PM
2024-25
Introduct Ion to Structured Query Language (SQL)
147
To create a database called StudentAttendance, we 
will type following command at mysql prompt.
mysql> CREATE DATABASE StudentAttendance;
Query OK, 1 row affected (0.02 sec)
Note: In LINUX environment, names for database and tables 
are case-sensitive whereas in WINDOWS, there is no such 
differentiation. However, as a good practice, it is suggested to write 
database or table name in the same letter cases that were used at 
the time of their creation.
A DBMS can manage multiple databases on one 
computer. Therefore, we need to select the database 
that we want to use. Once the database is selected, we 
can proceed with creating tables or querying data. Write 
the following SQL statement for using the database: 
mysql> USE StudentAttendance;
Database changed
Initially, the created database is empty. It can be 
checked by using the Show tables command that lists 
names of all the tables within a database. 
mysql> SHOW TABLES;
Empty set (0.06 sec)
8.4.2 CREATE Table
After creating database StudentAttendance, we need 
to define relations (create tables) in this database and 
specify attributes for each relation along with data types 
for each attribute. This is done using the CREATE TABLE 
statement.
 Syntax:
CREATE TABLE tablename(
attributename1 datatype constraint,
attributename2 datatype constraint,
:
attributenameN datatype constraint);
It is important to observe the following points with 
respect to the Create Table statement:
• N is the degree of the relation, means there are N 
columns in the table.
• Attribute name specifies the name of the column in 
the table.
• Datatype specifies the type of data that an attribute 
can hold.
• Constraint indicates the restrictions imposed on the 
values of an attribute. By default, each attribute can 
take NULL values except for the primary key.
Activity 8.3
Type the statement 
show database;.  Does 
it show the name of 
StudentAttendance 
database?
Show
Chap 8.indd   147 19-Jul-19   3:45:57 PM
2024-25