11th class Python SQL Programming

Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( )

 Table Name: Student1920

password: root

mysql>show databases;

mysql>create database school;

mysql> use school;

mysql>show tables;

mysql>show databases:

-> Use SQL DDL/DML

a)CREATE TABLE

  create table student1920 

    (

     RollNo int(2) primary key,
     Name char(20),
     Class char(3),
     DOB date,
     Gender char(1),
     City char(10),
     Marks int(3)
     );

mysql>desc student1920;

b)INSERT IN TO

insert into student1920 values(1,"Nanda","X","1995/06/06","M","Agra",551);
insert into student1920 values(2,"Saurabh","XII","1993/05/07","M","Mumbai",462);
insert into student1920 values(3,"Sanal","XI","1994/05/06","F","Delhi",400);
insert into student1920 values(4,"Trisla","XII","1995/08/08","F","Mumbai",450);
insert into student1920 values(5,"Store","XII","1995/10/08","M","Delhi",369);
insert into student1920 values(6,"Marisla","XI","1994/12/12","F","Dubai",250);
insert into student1920 values(7,"Neha","X","1995/12/08","F","Moscow",377);
insert into student1920 values(8,"Nishant","X","1995/06/12","M","Moscow",489);

c)UPDATE TABLE

mysql> UPDATE <TableName>  SET <ColName>=<NewValue>      WHERE  <Condition> 

update student1920 set Class = XII where Name="Nanda";

select  *  from student1920;

d)DELETE FROM

mysql> DELETE FROM <TableName> WHERE <Condition>
DELETE  FROM STUDENT1920 WHERE ROLLNO=4;

select  *  from student1920;

create table stu as(select Name, Marks from student1920 );

DELETE FROM TABLE_NAME;
DELETE FROM STU;

select  *  from student1920;

e) ALTER TABLE

ALTER TABLE table_name ADD column_name datatype;

ex: ALTER TABLE STUDENT1920 ADD MOB CHAR(10);

select  *  from student1920;

f) MODIFY TABLE

ALTER TABLE table_name MODIFY COLUMN column_name datatype;
EX: ALTER TABLE STUDENT1920 MODIFY COLUMN MOB CHAR(11);

select  *  from student1920;

g) DROP TABLE

Syntax for creation of a table from another table is -

 mysql>CREATE TABLE <TableName>    AS (SELECT <Cols> FROM <ExistingTable>   WHERE <Condition>); 

create table stu as(select Name, Marks from student1920 );

Syntax: DROP TABLE TABLE_NAME;
EX: DROP TABLE STU;

h) SELECT-FROM-WHERE-ORDER BY ->BETWEEN, IN, LIKE

Select * from Student1920 Order by name DESC;
Select * from Student1920 Order by name;
Select * from Student1920 Order by name ASC;

select  *  from student1920;

SELECT name, marks, City FROM Student1920 Where Name LIKE "Sa%" ORDER BY Class;
SELECT name, marks, City FROM Student1920 Where Name NOT LIKE "Sa%" ORDER BY Class;

select  *  from student1920;

SELECT name, marks, City FROM Student1920 Where Name LIKE "_____" ORDER BY Class;
SELECT name, marks, City FROM Student1920 Where Name NOT LIKE "_____" ORDER BY Class;

select  *  from student1920;

SELECT name,marks, city FROM student1920 WHERE marks BETWEEN 300 AND 489;

mysql> SELECT name,marks, city FROM student1920 WHERE marks NOT BETWEEN 300 AND 489;
+-------- -+---------+----------+
| name    | marks | city      |
+------- --+----------+---------+
| Nanda  |   551    | Agra    |
| Marisla |   250    | Dubai  |
+---------+-------+-------+-----+

2 rows in set (0.00 sec)

mysql> SELECT * FROM student1920 WHERE city IN('Delhi','Mumbai');
+--------+---------+-------+------------+--------+--------+-------+
| RollNo | Name    | Class | DOB        | Gender | City   | Marks |
+--------+---------+-------+------------+--------+--------+-------+
|      2 | Saurabh | XII   | 1993-05-07 | M      | Mumbai |   462 |
|      3 | Sanal   | XI    | 1994-05-06 | F      | Delhi  |   400 |
|      4 | Trisla  | XII   | 1995-08-08 | F      | Mumbai |   450 |
|      5 | Store   | XII   | 1995-10-08 | M      | Delhi  |   369 |
+--------+---------+-------+------------+--------+--------+-------+
4 rows in set (0.04 sec)

mysql> SELECT * FROM student1920 WHERE city NOT IN('Delhi','Mumbai');
+--------+---------+-------+------------+--------+--------+-------+
| RollNo | Name    | Class | DOB        | Gender | City   | Marks |
+--------+---------+-------+------------+--------+--------+-------+
|      1 | Nanda   | X     | 1995-06-06 | M      | Agra   |   551 |
|      6 | Marisla | XI    | 1994-12-12 | F      | Dubai  |   250 |
|      7 | Neha    | X     | 1995-12-08 | F      | Moscow |   377 |
|      8 | Nishant | X     | 1995-06-12 | M      | Moscow |   489 |
+--------+---------+-------+------------+--------+--------+-------+

4 rows in set (0.00 sec)

-> Aggregate functions: MIN, MAX, AVG, COUNT,SUM

a. Find the minimum marks of a female students in student1920 table.

Solution:- SELECT  min(marks) FROM student1920  WHERE Gender="F";

Select city,Min(marks) from student1920 Group By City;

b. Find the maximum marks of a male students in student1920 table.

Solution:- SELECT name, max(marks) FROM student1920 WHERE Gender="M";

SELECT name, max(marks) FROM student1920 WHERE Gender="F";

mysql> Select Max(marks) from student1920; 

mysql> Select city,Max(marks) from student1920 where City="Delhi";
mysql> Select city,Max(marks) from student1920 Group By City;

c. Find the average marks of the students in student1920 table.

Solution:- SELECT avg(marks) from student1920;

d. Find the number of tuples in the student1920 relation.

Solution:- SELECT count(*) FROM student1920;

e. Find the total marks of those students who work in Delhi city.

Solution:- SELECT sum(marks) FROM student1920 WHERE city="Delhi";

Additional

Syntax for creation of a table from another table is -

 mysql>CREATE TABLE <TableName>    AS (SELECT <Cols> FROM <ExistingTable>   WHERE <Condition>); 

create table stu as(select Name, Marks from student1920 where city='Mumbai');