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:

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> ]

VALUES( <value1>,<value2>,……………………….);

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

UPDATE Command

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

DELETE command

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

Bytechnocrash