Write a SQL query to order the (student ID, marks) table in descending order of the marks.

create table student
     (student_id int(5) primary key,
     name char(20),
     class char(3),
     DOB date,
     gender char(1),
    city char(10),
    marks int(3)
    );
Query OK, 0 rows affected, 2 warnings (1.42 sec)

desc student;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| student_id | int(5)   | NO   | PRI | NULL    |       |
| name       | char(20) | YES  |     | NULL    |       |
| class      | char(3)  | YES  |     | NULL    |       |
| DOB        | date     | YES  |     | NULL    |       |
| gender     | char(1)  | YES  |     | NULL    |       |
| city       | char(10) | YES  |     | NULL    |       |
| marks      | int(3)   | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
7 rows in set (0.02 sec)

insert into student values(12301,"abhinav","X","1995/06/06","M","Agra",551);
insert into student values(12302,"abhineet","XII","1993/05/07","M","Mumbai",462);
insert into student values(12303,"aniket","XI","1994/05/06","F","Delhi",400);
insert into student values(12304,"apar","XII","1995/08/08","F","Mumbai",450);
insert into student values(12305,"brij","XII","1995/10/08","M","Delhi",369);
insert into student values(12306,"bhuvnesh","XI","1994/12/12","F","Dubai",250);
insert into student values(12307,"devesh","X","1995/12/08","F","Moscow",377);
insert into student values(12308,"dhruva","X","1995/06/12","M","Moscow",489);

select * from student;
+-------------+----------+-------+------------+--------+--------+-------+
| student_id | name     | class |   DOB        | gender | city       | marks |
+-------------+----------+-------+------------+--------+--------+-------+
|      12301    | abhinav    | X       | 1995-06-06 | M        | Agra      |   551 |
|      12302    | abhineet   | XII     | 1993-05-07 | M        | Mumbai |   462 |
|      12303    | aniket       | XI      | 1994-05-06 | F         | Delhi      |   400 |
|      12304    | apar          | XII     | 1995-08-08 | F         | Mumbai |   450 |
|      12305    | brij            | XII     | 1995-10-08 | M        | Delhi      |   369 |
|      12306    | bhuvnesh | XI      | 1994-12-12 | F         | Dubai     |   250 |
|      12307    | devesh      | X       | 1995-12-08 | F         | Moscow |   377 |
|      12308    | dhruva      | X       | 1995-06-12 | M        | Moscow  |   489 |
+------------+----------+-------+------------+--------+--------+-------+
8 rows in set (0.00 sec)

 select student_id,marks from student order by marks;
+------------+-------+
| student_id | marks |
+------------+-------+
|      12306 |   250 |
|      12305 |   369 |
|      12307 |   377 |
|      12303 |   400 |
|      12304 |   450 |
|      12302 |   462 |
|      12308 |   489 |
|      12301 |   551 |
+------------+-------+
8 rows in set (0.00 sec)

mysql> select student_id,marks from student order by marks desc;
+------------+-------+
| student_id | marks |
+------------+-------+
|      12301 |   551 |
|      12308 |   489 |
|      12302 |   462 |
|      12304 |   450 |
|      12303 |   400 |
|      12307 |   377 |
|      12305 |   369 |
|      12306 |   250 |
+------------+-------+
8 rows in set (0.00 sec)

Find the total number of customers from each country in the table (customer ID, customer name, country) using group by.

create table customer
     (cust_ID integer primary key,
     cust_name char(20),
     country char(10)
     );

mysql> desc customer;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| cust_ID   | int(11)  | NO   | PRI | NULL    |       |
| cust_name | char(20) | YES  |     | NULL    |       |
| country   | char(10) | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

insert into customer values(101,"Bhuvanesh","Pakistan");
insert into customer values(102,"Aniket","India");
insert into customer values(103,"Devesh","Srilanka");
insert into customer values(104,"Jashan","Pakistan");
insert into customer values(105,"Jatin","Japan");
insert into customer values(106,"Apar","India");
insert into customer values(107,"Ashish","Nepal");
insert into customer values(108,"Priyanshu","Srilanka");
insert into customer values(109,"Vasu","Bangladesh");
insert into customer values(110,"Omprakash","Japan");
insert into customer values(111,"Prateek","India");

mysql> select * from customer;
+---------+-----------+------------+
| cust_ID | cust_name | country    |
+---------+-----------+------------+
|     101 | Bhuvanesh | Pakistan   |
|     102 | Aniket    | India      |
|     103 | Devesh    | Srilanka   |
|     104 | Jashan    | Pakistan   |
|     105 | Jatin     | Japan      |
|     106 | Apar      | India      |
|     107 | Ashish    | Nepal      |
|     108 | Priyanshu | Srilanka   |
|     109 | Vasu      | Bangladesh |
|     110 | Omprakash | Japan      |
|     111 | Prateek   | India      |
+---------+-----------+------------+
11 rows in set (0.10 sec)

mysql> select distinct(country) from customer;
+------------+
| country    |
+------------+
| Pakistan   |
| India      |
| Srilanka   |
| Japan      |
| Nepal      |
| Bangladesh |
+------------+
6 rows in set (0.03 sec)


mysql> select country, count(country) from customer group by country;
+------------+----------------+
| country    | count(country) |
+------------+----------------+
| Pakistan   |              2 |
| India      |                3 |
| Srilanka   |              2 |
| Japan      |              2 |
| Nepal      |              1 |
| Bangladesh |              1 |
+------------+----------------+
6 rows in set (0.04 sec)

Find the min, max, sum, and average of the marks in a student marks table.

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

 Table Name: Student1920

mysql> use school;
Database changed
mysql> 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)
     );
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);

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

Solution:- SELECT avg(marks) from student1920;

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

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

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

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

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

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

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

Solution:- SELECT count(*) FROM student1920;

Take a sample of 10 phishing e-mails and find the most common words.

# Write a program to find the most common words in a file.

import collections
fin = open('D:\\email.txt','r')
a= fin.read()
d={ }
L=a.lower().split()

for word in L:
     word = word.replace(".","")
     word = word.replace(",","")
     word = word.replace(":","")
     word = word.replace("\"","")
     word = word.replace("!","")
     word = word.replace("&","")
     word = word.replace("*","")

for k in L:
     key=k
     if key not in d:
           count=L.count(key)
           d[key]=count

n_print = int(input("How many most common words to print: "))
print("\nOK. The {} most common words are as follows\n".format(n_print))

word_counter = collections.Counter(d)
for word, count in word_counter.most_common(n_print):
      print(word, ": ", count)

fin.close()

OUTPUT:

How many most common words to print: 5
OK. The 5 most common words are as follows

the : 505
a : 297
is : 247
in : 231
to : 214

Write a program to write those lines which have the character 'a' from one text file to another text file.

Note:
 - Open widows D drive and open book.txt file and
     type the number of lines

fin=open("D:\\book.txt","r")
fout=open("D:\\story.txt","a")
s=fin.readlines()
for j in s:
   if 'a' in j:
      fout.write(j)
fout.close()
fin.close()

Output:
**Write contents of book.txt and story.txt

-  After executing above program open story.txt file

book.txt file
jkjhkll aa
kllkklk
twinkla jsad

story.txt

jkjhkll aa
twinkla jsad

Compute EMIs for a loan using the numpy librarary

* Open the cmd prompt window 

    pip install numpy

* Open the python window

import numpy as np

interest_rate= float(input("Enter the interest rate : "))

monthly_rate = (interest_rate)/ (12*100)

years= float(input("Enter the total years : "))

number_month = years * 12

loan_amount= float(input("Enter the loan amount : "))

emi = abs(np.pmt(monthly_rate, number_month, loan_amount))

print("Your EMI will be Rs. ", round(emi, 2))

Output

Enter the interest rate : 7.5

Enter the total years : 15

Enter the loan amount : 200000

Your EMI will be Rs. 1854.02

Open a webpage using the urllib library.

Programming steps:
1. Open the python script mode and type below  statements

import urllib.request
data = urllib.request.urlopen('https://www.youtube.com/')
print(data.read())

Output:

Python 3.8.0 (tags/v3.8.0:fa919fd, Oct 14 2019, 19:21:23) [MSC v.1916 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license()" for more information.
>>>
== RESTART: C:/Users/kvhebbal/AppData/Local/Programs/Python/Python38-32/gbh.py =

squeezed text(180 lines).