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).

Write a Django based web server to parse a user request (POST), and write it to a CSV file

Programming steps(Reference)


1. O.S windows 64bit
2. Internet connection compulsory
3. python 64 bit

4. open cmd prompt
  pip install django

5. open windows drive d
   create folder dj

6. open cmd prompt
   d:
   cd dj
   django-admin startproject KVS     #open windows drive d and dj folder check KVS folder creation
   cd KVS                                           # inner folder and outer folder and it creates python files
   manage.py runserver
   copy the starting development server ip address: http://127.0.0.1:800/ and paste in the webpage
   In the command prompt press ctrl+c
   manage.py startapp teacher

7. open windows drive d wid Django folder
   -open  outer and inner KVS folder
    - open setting file with IDLE
     coding
            installed_apps add teacher at the last
            go to the template and include in dirs template

8. open note pad and type html code in the file first.html and save it in the folder
D:\Django\KVS\temp    # Get method
<html>
 <head><title>Welcome to KVS</title></head>
  <body bgcolor=skyblue>
      <h1> This is first Django Project.</h1>
           {{even}}
     
        {% for a in even %}
          {{a}}
           <br>
        {% endfor %}
 
    {{name}}
 
    <a href="http://127.0.0.1:8000/stuentry">
     <h1>Click Here to add new student.</h1>
     </a>
  </body>
</html>

9. open note pad and type html code in the file stuentry.html and save it in the folder
D:\dj\KVS\temp  # post method
<html>
 <head><title>Student entry form</title></head>
  <body bgcolor=skyblue>
     <h1>Enter the details</h1>

     <form action="#" method="POST">
         {%csrf_token%}
         Roll: &nbsp <input type="text" name="roll no"> <br><br>
         Name: &nbsp <input type="text" name="name"> <br><br>
         <input type="submit"> <br><br>
      </form>
      <a href="http://127.0.0.1:8000/first">
         <h1>Click Here to view th details of students.
         </h1>
      </a1>
   </body>
</html>

10.create excel and save it as "stud.csv" and put some entry
e.g.,
roll,name
1,Hari
2,Mukesh

11.In dj\KVS, go to teachers folder and edit views.py in IDLE
from django.shortcuts import render
import pandas as pd
from django.http import HttpResponseRedirect
import csv

# Create your views here.
 def show(request):
     lst=[x for x in range(2,20,2)]
     df=pd.read_csv("D:\\dj\\stud.csv")
     return render(request,'first.html',{"even":lst,"name":df})
 def stuentry(request,):
     if request.method=='POST':
         stu_dict=request.POST
         with open("D:\\dj\\stud.csv","a") as st:
             Val=csv.writer(st)
             L=[]
             for x in stu_dict:
                 if x=="rollno":
                     L.append(stu_dict[x])
                 if x=="name":
                     L.append(stu_dict[x])
             Val.writerow(L)
     return render(request,'stuentry.html')

12.Now go to KVS folder inside of KVS. Edit urls.py with IDLE
from django.contrib import admin
from django.urls import path
from teacher import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('first',view.show),
    path('stuentry',views.stuentry),
    ]

13.open cmd prompt
   -manage.py runserver
   -copy server address (e.g., http://127.0.0.1:8000/)

14.open web browser and paste the address with "first" (i.e., http://127.0.0.1:8000/first)

Now you have your web with GET and POST method

Write a algorithm and python function program to find minimum and maximum in a list without using built in function min() and max() through looping :

#user defined function
def max_min(n):
    ls=[]
    for i in range(n):
        nu=int(input("enter the number"))
        ls.append(nu)
    print(ls)
    l=ls[0]
    for j in range(n):
        if(ls[j]>l):
            l=ls[j]
    print("larger=",l)

    s=ls[0]
    for j in range(n):
        if(ls[j]<s):
            s=ls[j]
    print("smaller=",s)
       


#main program
n=int(input("Enter the size of the list="))
max_min(n)

output
Enter the size of the list=4
enter the number6
enter the number8
enter the number2
enter the number3
[6, 8, 2, 3]
larger= 8
smaller= 2

Integrate SQL with Python by importing the MySQL module

Programming steps (Reference):

1. Operating system: windows 64 bit
2. Python: 64 bit
3. Mysql:  64 bit           # open parallely cmd prompt, python window &  mysql prompt

4. open cmd prompt
    pip install mysql-connector   Enter
    pip install mysqlclient              Enter

5. python interactive mode
   >> import mysql.connector     Enter
 Ans: >>


6.   open mysql cmd prompt
     Enter password: root
     select current_user;

7. open python script mode
     import mysql.connector
     mydb=mysql.connector.connect(host="localhost",user="root",passwd="root")
     print(mydb)

8.  open mysql cmd prompt
      show databases;
      drop database school;   ( if existing)
      show databases;

9. open python script mode   # creating school database logic
    import mysql.connector
    mydb=mysql.connector.connect(host="localhost",user="root",passwd="root")

    mycursor=mydb.cursor();
    mycursor.execute("create database school ")

10.  open mysql cmd prompt     # printing database mysql python
       use school;
      show databases; 

11. open python script mode      # printing database through python
    import mysql.connector
    mydb=mysql.connector.connect(host="localhost",user="root".passwd="root")

    mycursor=mydb.cursor();
    mycursor.execute("show databases")
    for x in mycursor:
           print(x)

 13.open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("CREATE TABLE student1920(Roll int(2) Primary key, Name char(20), class int(2), DOB Date, Gender Char(1), City Char(10), Marks int(3))")

14.  open mysql cmd prompt
    use school;
    desc student1920;

15. open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("show tables")
for x in mycursor:
           print(x)

16. open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
sql="insert into student1920(Roll,Name,class,DOB,Gender,City,Marks) values (%s,%s,%s,%s,%s,%s,%s) "
val=(01,"Nanda",10,1995/06/06,"M","Agra",551)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount,"1 Record inserted")

17.  open python script mode   #Repeat all the steps remaining rows change the data
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
sql="insert into student1920(Roll,Name,class,DOB,Gender,City,Marks) values (%s,%s,%s,%s,%s,%s,%s) "
val=(02,"Saurabh",12,1993/05/07,"M","Mumbai",462)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount,"1 Record inserted")

.
.
18.open mysql cmd prompt
  select * from student1920;

19.open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("select * from student")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)



# questions
1. SELECT COUNT(*), CITY FROM STUDENT1920 GROUP BY CITY HAVING COUNT(*)>1;

#open python script mode

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute(" SELECT COUNT(*), CITY FROM STUDENT1920 GROUP BY CITY HAVING COUNT(*)>1")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)

2. SLECT MAX(DOB), MIN(DOB) FROM STUDENT1920;

#open python script mode

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("SLECT MAX(DOB), MIN(DOB) FROM STUDENT1920;")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)


3. SELECT NAME,GENDER FROM STUDENT1920 WHERE CITY=''DELHI";
#open python script mode

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("SELECT NAME,GENDER FROM STUDENT1920 WHERE CITY=''DELHI"")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)




                                                                 ***End***