Create a student table and insert data. Implement the following MySQL commands on the student table:
- ALTER table to add new add new attributes/modify data types/drop attribute
- UPDATE table to modify data
- ORDER BY to display data in ascending /descending order
- DELETE to remove tuple(s)
- GROUP BY and find min,max,sum,count and average
In this post,we are going to create student table and insert 10 student records (data) into it.Then we will perform some MySQL queries to retrieve data from database. We will alter,update and order data in ascending and descending order by using MySQL commands. We will implement following MySQL command on the student table.
They are as follows
- Create a student table and insert data.
- Insert 10 student record into student table
- To add a new column city in the above table with appropriate data type
- To increase marks by 50% for those students who have marks less than 40
- To display Rno, Name, Marks of those Female students in ascending order of their names
- To display Rno, Gender, Name,Marks in descending order of their marks
- Delete CITY column from student table
- Delete student detail from student table whose roll no is 14
- To find min,max,total,average marks of students sectionwise only
Lets check the solutions:
Lets see how to create table in MySQL
CREATE TABLE command is used to create table in MySQL. When table is created,its columns are named,data types and sizes are also supplied for each column. One point is to be note that each table must have at least one column.
Syntax of CREATE TABLE command is :
CREATE TABLE <table-name>
(<column name1><data type1>[(size1)], <column name2><data type2>[(size2)],
<column name3><data type3>[(size3)]……………. <column namen><data typen>[(sizen)] );
To create student table whose schema is as follows :
CREATE TABLE STUDENT
(RNO INT PRIMARY KEY, NAME VARCHAR(60),CLASS INT,
SECTION VARCHAR(5),GENDER VARCHAR(10),MARKS INT);
In this way ,we can create student table with multiple columns. In this table, we declare RNO column as the primary key of the table. The primary keys can not allow NULL values.
Create a student table
Inserting data into table
We can add rows into table using INSERT command of SQL.
Syntax of INSERT INTO command :
INSERT INTO <table name>[ <column list> ]
The INSERT statement adds a new row to table giving value for every column in the row.
Note that the data values are in same order as the column names in table. We can insert number of rows into student table . If we want to insert 10 rows into student table then we need to specify 10 INSERT command to do it.
Also read : How to insert multiple rows into MySQL at a time
Insert 10 student record into student table
ALTER TABLE Command
We can add column using ALTER TABLE command .We can also redefine a column and we can change the definition of existing tables. We can change the data type or size of column using ALTER TABLE command.
To add column
Syntax of ALTER TABLE command is :
ALTER TABLE <TABLE NAME>ADD<COLUMN NAME><DATATYPE><SIZE>[<CONSTRAINT NAME>];
To add a new column city in the above table with appropriate data type
Sometimes we need to change some or all of the values in an existing row. This can be done using UPDATE command of SQL. WHERE Clause and SET keyword are used with UPDATE command.
Syntax of UPDATE command
UPDATE <table name> SET <column name> <scalar expression> WHERE condition;
To update single column, one column assignment can be specified with SET clause.
To update multiple columns ,multiple columns assignments can be specified with SET clause.
Suppose ,if you want to increase the marks for the student who have less than 40 by 50%, you could use the following code
To increase marks by 50% for those students who have marks less than 40
Sorting result—- ORDER BY CLAUSE
ORDER BY clause allows sorting of query results by one or more columns. The sorting can be done either in ascending or descending order,default order is ascending. Note that, the data in the table is not sorted;only results that appear on the screen are sorted.
SYNTAX OF ORDER BY CLAUSE
SELECT <COLUMN NAME>[, <COLUMN NAME>,……]
FROM <TABLE NAME>[WHERE <PREDICATE>] [ORDER BY <COLUMN NAME> ASC/DESC];
To specify the sort order ,we may specify DESC for descending order or ASC for ascending order. We can perform ordering on multiple attributes or columns
To display Rno, Name, Marks of those Female students in ascending order of their names
To display Rno, Gender, Name,Marks in descending order of their marks
ALTER TABLE Command with DROP Clause
We can remove specific columns from table using ALTER TABLE command with DROP clause. This command is used to remove specific column from table.
Syntax of ALTER TABLE Command with DROP Clause
ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>;
Drop column CITY from student table
The DELETE command removes the rows from table. This removes the entire rows,not individual field values.
Syntax of DELETE Command
DELETE FROM <TABLENAME>[WHERE CONDITION];
To remove all the contents of specific rows, the following command is used.
Delete student details from above table
GROUP BY CLAUSE
The GROUP BY Clause combines all those records that have identical values in particular field. It is used in SELECT statement to divide the table into groups. Grouping can be done by column name or with aggregate function. Aggregate function work with multiple rows at a time and return aggregated values. Example of aggregate function : sum(), count(), min(),max(), avg() etc.
Syntax of SELECT with GROUP BY clause
SELECT <COLUMN1>,<COLUMN2>,……. FROM TABLENAME GROUP BY COLUMNNAME;
To find minimum,maximum,total,average marks of students (section wise) only
Related Python Programs
Create garment table and insert data into it.