Class 11 IP Ch 8 SQL Introduction NOTES Important Points
Class 11 IP Ch 8 SQL Introduction NOTES Important Points
Introduction
A Database Management System(DBMS) is a software that can be used to create and manage databases. There are many RDBMS such as MySQL, Microsoft SQL Server, PostgreSQL, Oracle, etc.
Structured Query Language
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 and is not case sensitive.
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. SQL provides statements for defining the structure of
the data, manipulating data in the database, declare constraints and retrieve data from the database.
Installing MySQL
MySQL is an open source RDBMS software which can be easily downloaded from the official website https://dev.mysql.com/downloads.
Few rules to follow while writing SQL statements in MySQL:
- SQL is case insensitive. That means name and NAME are same for SQL.
- 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 ‘->’, indicating that statement is continued to the next line. After the last line, put ‘;’ and press enter.
- Always end SQL statements with a semicolon (;).
NOTE: A database consists of one or more relations and each relation (table) is made up of attributes (column). Each attribute has values of particular data type.
Data type in MySQL
Data type refers to the type of data value that an attribute(column) 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 given below
1. Char(n): It Specifies character type data of length n where n could be any value from 0 to 255. Char(n) is a fixed length data type.
2. Varchar(n): It also 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.
3. Int: Int data type specifies an integer value. Each value value occupies 4 bytes of storage.
4. Float: Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
5. Date: The DATE type is used for dates in ‘YYYY-MM-DD’ format.
Class 11 IP Ch 8 SQL Introduction NOTES Important Points
Constraints in MySQL
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.
NOTE: It is not mandatory to define constraint for each attribute of a table.
| Constraint | Description |
| NOT NULL | Ensures that a column can not have NULL values |
| 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. |
SQL Commands
SQL provides commands for defining the relation schemas, modifying relation schemas and deleting relations. These are called Data Definition Language(DDL).
Various DDL commands are: Create, Alter, Drop
Data definition starts with the create statement. This statement is used to create a database and its tables. Before creating a database, we should be clear about the number of tables in the database and the columns (attributes) in each table. This is how we decide the relation schema.
DML commands are used to manipulate and query data stored in a database. Examples of DML commands in SQL include SELECT , INSERT , DELETE , and UPDATE
Create Database
This command is used to create database as shown below:
Query-1
mysql>create database mydb;
The above command will create a database named “mydb” in MySQL.
Query-2
mysql>show databases;
The above command shows the names of all the databases in MySQL. By writing this command, you can verify that “mydb” named database is created or not.
Use Database
After creating the database in MySQL, we need to open that database for work. Write the following command to open the database in which you want to work.
Query-3
mysql>use mydb;
To see names of all the tables in a database, we can use the following command.
Query-4
mysql>show tables;
Empty set (0.00 sec)
Since we have not created a single table till now, so the above command is showing Empty set.
Create Table
This command is used to create the table in MySQL. Syntax of this command is given below:
Syntax:
CREATE TABLE tablename(
attributename1 datatype constraint,
attributename2 datatype constraint,
:
attributenameN datatype constraint);
NOTE: Read the following points about create table command
- 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.
Let we create the following table “STUDENT” in database “mydb“
| Attribute Name | Data type | Constraint |
| Admno | Integer | Primary Key |
| Name | Varchar(25) | Not Null |
| DOB | Date | |
| Mobile | Char(10) |
create table student(Admno integer primary key, Name Varchar(25) not null, DOB Date, Mobile Char(10));
Describe Table
We can view the structure of an already created table using the describe statement.
Syntax:
Describe tablename; OR Desc tablename
Query-5
Now if we execute the command “show tables”, it will now return a table “Student
Query-6
mysql>Show tables;
Tables_in_mydb |
+----------------+
| student |
+----------------+
Let we create another table “Fees” in same database “mydb”
| Attribute Name | Data type | Constraint |
| Admno | Integer | |
| Fee_id | Integer | |
| Class | Integer | Not Null |
| Fee | Float(7,2) |
Query-7
mysql> Create table Fees(
-> Admno Integer,
-> Class Integer Not Null,
-> Fee Float(7,2));
Query OK, 0 rows affected (0.26 sec)
Now if we execute the command “show tables”, it will now return a table “Student” and “Fees”
Query-8
mysql>Show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| fees |
| student |
+----------------+
Insertion of Records
Statement used to insert new records in a table is “INSERT INTO”. Its syntax is:
INSERT INTO tablename
VALUES(value 1, value 2,….);
Here, value 1 corresponds to attribute 1, value 2 corresponds to attribute 2 and so on.
Let us insert some records in the Student Table:
Query-9: Insert a new record in table student.
mysql> insert into student values(121, "Anil Kumar", "2002-05-25", "1096452352");
Query OK, 1 row affected (0.01 sec)
Query-10: Insert a new record in table student.
mysql> insert into student values(173, "Suman Rana", "2003-11-15", "1000010200");
Query OK, 1 row affected (0.01 sec)
We can use the SQL statement SELECT * from Student to view the inserted records. The SELECT statement will be explained in next section.
Query-11: Display all records of table STUDENT.
mysql> select * from student;
+-------+------------+------------+---------------------+
| Admno | Name | DOB | Mobile |
+-------+------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1096452352 |
| 173 | Suman Rana | 2003-11-15 | 1000010200 |
+-------+------------+------------+------------+
2 rows in set (0.01 sec)
If we want to insert values only for particular columns in a table then we can use the following syntax of INSERT INTO statement.
Syntax:
INSERT INTO tablename (column1, column2, …)
VALUES (value1, value2, …);
For example: If we want to insert values in column Admno, Name and DOB only of table student then we can do as shown below.
Class 11 IP Ch 8 SQL Introduction NOTES Important Points
Query-12: Insert another record in table STUDENT.
mysql> insert into student (Admno, Name, DOB)
-> values(143, "Anuj Sethi", "2001-07-26");
Query OK, 1 row affected (0.00 sec)
To view all the inserted records in table, execute the following command again
Query-13: Display all records of table STUDENT.
mysql> select * from student;
+-------+------------+------------+-------------------+
| Admno | Name | DOB | Mobile |
+-------+------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1096452352 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
| 173 | Suman Rana| 2003-11-15 | 1000010200 |
+-------+------------+------------+------------+
3 rows in set (0.00 sec)
NOTE: Value in Mobile column is showing NULL as we did not entered any value.
(Empty or no value is called NULL value)
Insert two more records in table student:
Query-14: Insert another record in table STUDENT.
mysql> insert into student values(123, "Raman Sharma", "2002-01-31", "2121212121");
Query OK, 1 row affected (0.00 sec)
Query-15: Insert another record in table STUDENT.
mysql> insert into student values(147, "Ashu Khanna", "2001-06-25", "2000000001");
Query OK, 1 row affected (0.01 sec)
Now the table student will look like
Query-16: Display all records of table STUDENT.
mysql> select * from student;
+-------+--------------+------------+----------------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1096452352 |
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Rana | 2003-11-15 | 1000010200 |
+-------+--------------+------------+------------+
5 rows in set (0.00 sec)
Similarly insert five records in the “FEES” table as shown below:
Query-17: Insert records in table FEES.
mysql> insert into fees values(121, 11, 7050);
Query OK, 1 row affected (0.01 sec)
mysql> insert into fees values(123, 11, 6780);
Query OK, 1 row affected (0.01 sec)
mysql> insert into fees values(143, 12, 7500);
Query OK, 1 row affected (0.01 sec)
mysql> insert into fees values(147, 12, 7280.25);
Query OK, 1 row affected (0.01 sec)
mysql> insert into fees values(173, 10, 5280.50);
Query OK, 1 row affected (0.01 sec)
Now the table Fees will look like:
Query-18. Display all records of table FEES.
mysql> select * from fees;
+-------+-------+---------+
| Admno | Class | Fee |
+-------+-------+---------+
| 121 | 11 | 7050.00 |
| 123 | 11 | 6780.00 |
| 143 | 12 | 7500.00 |
| 147 | 12 | 7280.25 |
| 173 | 10 | 5280.50 |
+-------+-------+---------+
5 rows in set (0.00 sec)
SQL for Data Query
So far we have learnt how to create database and to store data. Now the next part is to retrieve data from databases in whatever way we want. The user enters the SQL commands called queries where the specific requirements for data to be retrieved are provided.
Select Statement
SELECT statement is used to retrieve data from the tables in a database and the output is also displayed in tabular form.
Syntax:
SELECT attribute1, attribute2, …
FROM table_name
WHERE condition
Here, attribute1, attribute2, … are the column names of the table.
The FROM clause is always written with SELECT clause as it specifies the name of the table from which data is to be retrieved. The WHERE clause is optional and is used to retrieve data that meet specified condition(s).
Select Statement to display particular columns
Query-19: To display Admission number and Name column from table Student
mysql> Select Admno, Name from Student;
+-------+--------------+
| Admno | Name |
+-------+--------------+
| 121 | Anil Kumar |
| 123 | Raman Sharma |
| 143 | Anuj Sethi |
| 147 | Ashu Khanna |
| 173 | Suman Rana |
+-------+--------------+
5 rows in set (0.00 sec)
Query-20: To display Admission number, Name and Date of Birth column from table Student
mysql> Select Admno, Name, DOB from Student;
+-------+--------------+------------+
| Admno | Name | DOB |
+-------+--------------+------------+
| 121 | Anil Kumar | 2002-05-25 |
| 123 | Raman Sharma | 2002-01-31 |
| 143 | Anuj Sethi | 2001-07-26 |
| 147 | Ashu Khanna | 2001-06-25 |
| 173 | Suman Rana | 2003-11-15 |
+-------+--------------+------------+
5 rows in set (0.00 sec)
NOTE: Columns will be displayed in the same order as mentioned in the query
Query-21: To display all columns of table Student
mysql> select * from student;
+-------+--------------+------------+----------------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1096452352 |
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Rana | 2003-11-15 | 1000010200 |
+-------+--------------+------------+------------+
5 rows in set (0.00 sec)
Select Statement to display particular rows using Where clause
Query-22: To display details of student whose admission number is 147.
mysql> select * from student where Admno = 147;
+-------+-------------+------------+------------------+
| Admno | Name | DOB | Mobile |
+-------+-------------+------------+------------------+
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
+-------+-------------+------------+-----------------+
1 row in set (0.01 sec)
Query-23: To display Date of Birth and Mobile number of Anil Kumar.
mysql> select DOB, Mobile from student where Name="Anil Kumar";
+------------+------------+
| DOB | Mobile |
+------------+------------+
| 2002-05-25 | 1096452352 |
+------------+------------+
1 row in set (0.00 sec)
Query-24: Display Admission number and Class of all those students whose fees between 5000 and 7000 (Both inclusive)
mysql> select Admno, Class from Fees where Fee>=5000 and Fee<=7000;
+-------+-------+
| Admno | Class |
+-------+-------+
| 123 | 11 |
| 173 | 10 |
+-------+-------+
2 rows in set (0.01 sec)
The above query defines a range that can also be checked using a comparison operator BETWEEN
mysql> select Admno, Class from Fees where Fee between 5000 and 7000;
+-------+-------+
| Admno | Class |
+-------+-------+
| 123 | 11 |
| 173 | 10 |
+-------+-------+
2 rows in set (0.00 sec)
Renaming Columns
In case we want to rename any column while displaying the output, we can do so by using alias ‘AS’ in the query as shown below:
Query-25: Display Name as Student Name in the output for all the students.
mysql> Select Name as "Student Name" from student;
+--------------+
| Student Name |
+--------------+
| Anil Kumar |
| Raman Sharma |
| Anuj Sethi |
| Ashu Khanna |
| Suman Rana |
+--------------+
5 rows in set (0.00 sec)
NOTE: If alternate name or alias of column have space then it should be enclosed in single or double quotes
Query-26: Display Admission Number of all students along with their Annual fees(Fee * 12) from table student.
mysql> select Admno, fee * 12 as "Annual Fees" from fees;
+-------+-------------+
| Admno | Annual Fees |
+-------+-------------+
| 121 | 84600.00 |
| 123 | 81360.00 |
| 143 | 90000.00 |
| 147 | 87363.00 |
| 173 | 63366.00 |
+-------+-------------+
5 rows in set (0.01 sec)
NOTE: Annual Fees will not be added as a new column in the table. It is just for displaying the output of the query.
DISTINCT Clause
The SELECT statement when combined with DISTINCT clause, returns records without repetition (distinct records). For example
mysql> Select Class from fees;
+-------+
| Class |
+-------+
| 11 |
| 11 |
| 12 |
| 12 |
| 10 |
+-------+
5 rows in set (0.00 sec)
#It shows duplicate values of column "Class"
mysql> Select distinct Class from fees;
+-------+
| Class |
+-------+
| 11 |
| 12 |
| 10 |
+-------+
3 rows in set (0.01 sec)
#It shows distinct values of column "Class"
Membership Operator IN
Query-27: Display details of students from table Fees of Admission number 121, 123 and 273
mysql> select * from Fees where Admno = 121 or Admno = 123 or Admno = 173;
+-------+-------+---------+
| Admno | Class | Fee |
+-------+-------+---------+
| 121 | 11 | 7050.00 |
| 123 | 11 | 6780.00 |
| 173 | 10 | 5280.50 |
+-------+-------+---------+
3 rows in set (0.00 sec)
The above query can also be written using IN Operator.
Query-28: Display details of “Anil Kumar” and “Anuj Sethi” students from table STUDENT.
mysql> select * from student where Name IN ("Anil Kumar", "Anuj Sethi");
+-------+------------+------------+------------+
| Admno | Name | DOB | Mobile |
+-------+------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1096452352 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
+-------+------------+------------+------------+
2 rows in set (0.01 sec)
Query-29: Display details of all students except “Anil Kumar” and “Anuj Sethi” students from table STUDENT.
mysql> select * from student where Name NOT IN ("Anil Kumar", "Anuj Sethi");
+-------+--------------+------------+------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Rana | 2003-11-15 | 1000010200 |
+-------+--------------+------------+------------+
3 rows in set (0.00 sec)
Note: Here we need to combine NOT with IN as we want to retrieve all records except "Anil Kumar" and "Anuj Sethi"
ORDER BY Clause
ORDER BY clause is used to display data in an ordered form(Ascending or Descending). By default, ORDER BY displays records in ascending order of the specified column’s values. For Example
Query-30: Display details of all students from table Fees in ascending order of their Fee.
mysql> Select * from Fees order by Fee;
+-------+-------+---------+
| Admno | Class | Fee |
+-------+-------+---------+
| 173 | 10 | 5280.50 |
| 123 | 11 | 6780.00 |
| 121 | 11 | 7050.00 |
| 147 | 12 | 7280.25 |
| 143 | 12 | 7500.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)
NOTE: Fee column display values in Ascending Order
Query-31: Display details of all students from table Fees in descending order of their Fee.
mysql> Select * from Fees order by Fee desc;
+-------+-------+---------+
| Admno | Class | Fee |
+-------+-------+---------+
| 143 | 12 | 7500.00 |
| 147 | 12 | 7280.25 |
| 121 | 11 | 7050.00 |
| 123 | 11 | 6780.00 |
| 173 | 10 | 5280.50 |
+-------+-------+---------+
5 rows in set (0.00 sec)
NOTE: Fee column display values in Descending Order
Handling NULL Values
SQL supports a special value called NULL to represent a missing or unknown value. For example in table “STUDENT” the column Mobile has no value for “Anuj Sethi” (Refer result of Query-21)
NOTE:
- It is important to note that NULL is different from 0 (zero).
- Arithmetic operation performed with NULL value gives NULL. For example: 5 + NULL = NULL
Query-32: Display details of all students from table STUDENT whose mobile number is blank(or NULL)
mysql> Select * from STUDENT where Mobile is NULL;
+-------+------------+------------+--------+
| Admno | Name | DOB | Mobile |
+-------+------------+------------+--------+
| 143 | Anuj Sethi | 2001-07-26 | NULL |
+-------+------------+------------+--------+
1 row in set (0.00 sec)
NOTE : Don't use '=' with NULL
Query-33: Display details of all students from table STUDENT whose mobile number is not blank(or not NULL)
mysql> Select * from STUDENT where Mobile is not NULL;
+-------+--------------+------------+------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1096452352 |
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Rana | 2003-11-15 | 1000010200 |
+-------+--------------+------------+------------+
4 rows in set (0.00 sec)
Data Updation and Deletion
Updation and deletion of data are also the parts of SQL data manipulation.
Data Updation
Sometimes we may need to make changes in the value(s) of one or more columns of existing records in a table. The UPDATE statement is used to make such modifications in the existing data.
Syntax:
UPDATE table_name
SET attribute1 = value1, attribute2 = value2, … Where <Condition>
For Example:
Query-34: Change the mobile number of “Anil Kumar” to 1245785623.
mysql> update STUDENT set Mobile='1245785623' where Name = "Anil Kumar";
Query OK, 1 row affected (0.26 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Verify the changes by writing the following query
mysql> Select * from STUDENT;
+-------+--------------+------------+------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1245785623 |
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Rana | 2003-11-15 | 1000010200 |
+-------+--------------+------------+------------+
5 rows in set (0.00 sec)
Query-35: Increase the fees of Class 12 by Rs. 500(use FEES Table).
mysql> update Fees set fee = fee + 500 where Class = 12;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
NOTE: If we miss the where clause in the UPDATE statement then the fees will be increased of all students by Rs. 500
Verify the changes by writing the following query
mysql> Select * from Fees;
+-------+-------+---------+
| Admno | Class | Fee |
+-------+-------+---------+
| 121 | 11 | 7050.00 |
| 123 | 11 | 6780.00 |
| 143 | 12 | 8000.00 |
| 147 | 12 | 7780.25 |
| 173 | 10 | 5280.50 |
+-------+-------+---------+
5 rows in set (0.00 sec)
We can also update values for more than one column using the UPDATE statement. For Example
Query-36: Suman Rana has requested to change the Name to “Suman Raanaa” and Mobile number to “1234587960”
mysql> update STUDENT set Name = "Suman Raanaa", Mobile = 1234587960 where Name = "Suman Rana";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
NOTE: If we miss the where clause in the UPDATE statement then the Name and Mobile number will be changed of all the students;
Verify the changes by writing the following query
mysql> Select * from STUDENT;
+-------+--------------+------------+------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 121 | Anil Kumar | 2002-05-25 | 1245785623 |
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Raanaa | 2003-11-15 | 1234587960 |
+-------+--------------+------------+------------+
5 rows in set (0.00 sec)
Data Deletion
The DELETE statement is used to delete one or more record(s) from a table.
Syntax:
DELETE FROM table_name
WHERE condition;
For Example
Query-37: Delete the record of student whose admission number is 121.
mysql> Delete from STUDENT where Admno = 121;
Query OK, 1 row affected (0.01 sec)
NOTE: All the records will be deleted if we miss the Where Clause
Verify the changes by writing the following query
mysql> Select * from STUDENT;
+-------+--------------+------------+------------+
| Admno | Name | DOB | Mobile |
+-------+--------------+------------+------------+
| 123 | Raman Sharma | 2002-01-31 | 2121212121 |
| 143 | Anuj Sethi | 2001-07-26 | NULL |
| 147 | Ashu Khanna | 2001-06-25 | 2000000001 |
| 173 | Suman Raanaa | 2003-11-15 | 1234587960 |
+-------+--------------+------------+------------+
4 rows in set (0.00 sec)
Drop Command
DROP statement is used to remove a database or a table permanently from the system.
NOTE: We should be very cautious while using this statement as it cannot be undone.
Syntax to drop a table:
DROP TABLE table_name;
Syntax to drop a database:
DROP DATABASE database_name;
NOTE: If we drop a database then all the tables in the database will also be deleted.
Using the Drop statement to remove a database will ultimately remove all the tables within it.
Query-38: Delete FEES table permanently
mysql> Drop table FEES;
The above command will delete the FEES table permanently from MySQL.
Alter Command
Alter command is used to change the structure of the table like add/remove an attribute or to modify the datatype of an existing attribute or to add constraint in attribute.
Syntax:
ALTER TABLE tablename ADD/Modify/DROP attribute1, attribute2,…..
1. Add an attribute to an existing table
Alter command help us to add a new column in an existing table. It can be done using the syntax given below:
ALTER TABLE table_name ADD attribute_name DATATYPE;
Query-39: Add a new column “Fee_id” of data type “Char (5)” in a table FEES.
mysql> Alter table FEES add column Fee_id char(5);
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
Verify the result by executing the following query
mysql> select * from FEES;
+-------+-------+---------+--------+
| Admno | Class | Fee | Fee_id |
+-------+-------+---------+--------+
| 121 | 11 | 7050.00 | NULL |
| 123 | 11 | 6780.00 | NULL |
| 143 | 12 | 8000.00 | NULL |
| 147 | 12 | 7780.25 | NULL |
| 173 | 10 | 5280.50 | NULL |
+-------+-------+---------+--------+
5 rows in set (0.00 sec)
NOTE: We can insert the value in the newly added column by UPDATE Command. for example
Query-40: Insert the Fee_id value “F_121” of student having admission number 121.
mysql> update FEES set Fee_id = "F_121" where Admno = 121;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
NOTE: Similarly we can add values for other Admission numbers
2. Modify datatype of an attribute
We can modify data types of the existing attributes of a table using the following ALTER statement.
Syntax:
ALTER TABLE table_name MODIFY attribute DATATYPE
Query-41: Change the size of attribute “Name” from VARCHAR(25) to VARCHAR(35) of the FEES table.
mysql> Alter table Student modify Name Varchar(35);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
Verify the result by executing the following query.
mysql> Desc Student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Admno | int(11) | NO | PRI | NULL | |
| Name | varchar(35) | YES | | NULL | |
| DOB | date | YES | | NULL | |
| Mobile | char(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3. Add Primary Key to an existing relation/table
We can add Primary key to a table even after it’s creation. Let we create a primary key in table “FEES”.
Query-42: Make the column Fee_id as Primary Key in table “FEES”
mysql> Alter table FEES Add Primary Key(Fee_id); Query OK, 0 rows affected (1.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. Add constraint UNIQUE to an existing attribute
Query-43: Add unique constraint to the column “Mobile” of “Student” table.
mysql> Alter table Student add unique(Mobile);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
5. Add default value to an attribute
If we want to specify default value for an attribute, then use the following syntax:
Syntax:
ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;
Query-44: Set default value of “Class” attribute of “FEES” table to ’10’.
mysql> Alter table FEES MODIFY Class Varchar DEFAULT 10;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
NOTE: We have to specify the data type of the attribute along with DEFAULT while using MODIFY.
6. Remove an attribute
Alter command is used to remove attributes from a table.
Syntax:
ALTER TABLE table_name DROP attribute;
Query-45: Remove column “Mobile” from table STUDENT.
mysql> Alter table STUDENT DROP Mobile;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
Q1. Differentiate between Alter and Update Command.
Ans.
| Alter | Update |
| This command is used to change the structure of the table like adding a column, deleting a column, change data type of column etc. | This command is used to change the data of the table. |
| It is a DDL command. | It is a DML command. |
Q2. Differentiate between Delete and Drop Command.
| Delete | Drop |
| This command is used to delete the specific row/record. | This command is used to delete the entire database permanently. |
Class XI & XII IP Syllabus 2024-25
Class XI & XII CS Syllabus 2024-25
SUMMARY
1. Database is a collection of related tables. MySQL is a ‘relational’ DBMS. A table is a collection of rows and columns, where each row is a record and columns describe the feature of records.
2. SQL is the standard language for most RDBMS. SQL is case insensitive.
3. CREATE DATABASE statement is used to create a new database.
4. USE statement is used for making the specified database as active database.
5. CREATE TABLE statement is used to create a table.
6. Every attribute in a CREATE TABLE statement must have a name and a datatype.
7. ALTER TABLE statement is used to make changes in the structure of a table like adding, removing or
changing datatype of column(s).
8. The DESC statement with table name shows the structure of the table.
9. INSERT INTO statement is used to insert record(s) in a table.
10. UPDATE statement is used to modify existing data in a table.
11. DELETE statement is used to delete records in a table.
12. The SELECT statement is used to retrieve data from one or more database tables.
13. SELECT * FROM table_name displays data from all the attributes of that table.
14. The WHERE clause is used to enforce condition(s) in a query.
15. DISTINCT clause is used to eliminate repetition and display the values only once.
16. The BETWEEN operator defines the range of values inclusive of boundary values.
17. The IN operator selects values that match any value in the given list of values.
18. NULL values can be tested using IS NULL and IS NOT NULL.
19. ORDER BY clause is used to display the result of an SQL query in ascending or descending order with
respect to specified attribute values. The default is ascending order.
Chapter Wise MCQ
2. Flow of Control (Loop and Conditional statement)
3. 140+ MCQ on Introduction to Python
4. 120 MCQ on String in Python
7. 100+ MCQ on Flow of Control in Python
8. 60+ MCQ on Dictionary in Python
Important Links
100 Practice Questions on Python Fundamentals
120+ MySQL Practice Questions
90+ Practice Questions on List
50+ Output based Practice Questions
100 Practice Questions on String
70 Practice Questions on Loops
70 Practice Questions on if-else
Disclaimer : I tried to give you the correct notes of ” Class 11 IP Ch 8 SQL Introduction Important Points” , but if you feel that there is/are mistakes in the handouts or explanation of “Class 11 IP Ch 8 SQL Introduction Important Points “ given above, you can directly contact me at csiplearninghub@gmail.com. The above Notes of “Class 11 IP Ch 8 SQL Introduction NOTES Important Points ” are created by referring NCERT Book of Class 11.