MySQL EXERCISE # 1

AIM:
To create a SCHOOL database with two relations named STUDENT and
GRADE and to perform the following SQL commands.

CREATE DATABASE SCHOOL;

USE SCHOOL;

1.Write an SQL command to create a STUDENT TABLE with the following

attributes or fields.

TABLE: STUDENT
Field Name Data type
Stud_id Int Primary key
stud_name Varchar(20)
Class Char(2)
Sec Char(2)

create table student (stud_id int primary key,name varchar(20),

class char(2),sec char(2));

2. Write an SQL command to add a new field STREAM to the table.

Alter table student add stream varchar(10);

3. Write a query to display all the records from student table;

SELECT * from STUDENT;

OUTPUT:
4. Write a query to display all the records order by class and sec.
select * from student order by class,sec;

OUTPUT:

5. Write a command to update the name of the student ARYA RAJ to

ARYA RAJA.
update student set name='Arya Raja' where name='Arya Raj';

6. Write an SQL command to create a table RESULT with attributes:

Result_id(Primary key), stud_id (foreign key) , subject,
test_name, mark.

create table result (result_id int primary key,

stud_id int references student(stud_id),
subject varchar(20),
test_name varchar(20),
mark int);

7.Write an SQL query to display minimum mark group by subject.

select subject,min(mark) as 'Mininum Mark' from result group by
subject;

OUTPUT:
8. Write an SQL command to display the maximum mark score in CS exam.
select max(mark) as Highest from result where subject='cs';

OUTPUT:

9. Write an SQL command to display the marks scored by Sanjana in

let-1.
select s.subject,r.mark from student s,result r where
s.stud_id=r.stud_id and test_name='let-1' and s.name='Sanjana A';

OUTPUT:

10. Write an SQL query to display subject wise average mark from
result table.

select subject, avg(mark) from result group by subject;

OUTPUT:
 MySQL EXERCISE # 2

Aim: - To create a database CABDB with two tables VEHICLE and

CUSTOMER and perform the following SQL queries.

TABLE: VEHICLE

TABLE: CUSTOMER

1. Write an SQL query to display the details of vehicle

which are white in color.
Select * from vehicle where color='white';

OUTPUT:

2. Write an SQL query to display the details of vehicle

in the descending order of capacity.

Select make, model, capacity from vehicle order by

capacity desc;
 OUTPUT:

3. Write an SQL query to display the details of vehicle

with maximum charge.

Select * from vehicle where charge=

(select max (charge) from vehicle);

OUTPUT:

4. Write an SQL query to display the list of customer

name and vehicle booked.

Select c.name,v.model,v.make from vehicle v, customer

c where c.vcode=v.vcode ;

OUTPUT:

5. Write an SQL query to display the details of customer

name and vehicle with minimum charge.

Select c.name,v.model,v.make from vehicle v, customer

c where c.vcode=v.vcode and v.charge=(select
min(charge) from vehicle);
 OUTPUT:

6. Write an SQL query to display the count of vehicles

group by color.

Select color, count(color) from vehicle group

by color;
OUTPUT:

7. Write an SQL query to display the details of vehicle

with capacity 4.

Select * from vehicle where capacity = 4;

OUTPUT:

8. Write an SQL query to display details of vehicles

having more than one booking.

Select v.model,v.make , count(c.vcode) from vehicle v

inner join customer c on v.vcode=c.vcode group by
c.vcode having count(c.vcode) > 1;

OUTPUT:
 MYSQL Exercise # 3
Aim: - To create an ORG database with three relations named
WORKER, BONUSand TITLE and execute the following SQL commands.

TABLE: WORKER

TABLE: BONUS

TABLE: TITLE

1. Write an SQL query to fetch unique values of DEPARTMENT from

Worker table.
Select DISTINCT department from worker;

OUTPUT:
2. Write an SQL query to print all Worker details from the
Worker table order by FIRST_NAME Ascending.

select * from worker order by first_name;

3. Write an SQL query to print details for Workers with the

first name as “Vipul” and “Satish” from Worker table.
Select * from worker where first_name in(“Vipul‟,‟Satish‟);

4. Write an SQL query to print details of Workers with

DEPARTMENT name as “Admin”.
Select * from worker where department = “Admin‟;

5. Write an SQL query to print details of the Workers whose

FIRST_NAME ends with “a‟.
Select first_name from worker where FIRST_NAME like '%a';

6. Write an SQL query to print details of the Workers whose

FIRST_NAME ends with “h‟ and contains six alphabets.
Select first_name from worker where FIRST_NAME like ' h';

7. Write an SQL query to print details of the Workers whose

SALARY lies between 100000 and 500000.
 Select * from worker where salary between 100000 and 500000;

8. Write an SQL query to print details of the Workers who have

joined in Feb 2014.
Select * from worker where year (joining_date) =2014
and month (joining_date) =2;

9. Write an SQL query to fetch the no. of workers for each

department in the descending order.(group by and order by)

Select department, count (department) c from worker group

by department order by c desc;

10. Write an SQL query to print name and department of the

Workers who are also Managers.
Select first_name,department from worker,title where
worker_id=worker_ref_id and worker_title = 'manager';
 MySQL EXERCISE # 4

AIM:
To create an INVENTORY database with two relations named PRODUCT
and MANUFACTURER and to execute the following SQL commands.

TABLE: MANUFACTURERS

TABLE: PRODUCT

QUESTIONS:
1. Compute the number of products with a price larger than or
equal to 180.
SELECT count (*) from products where price >= 180;
OUTPUT:
2. Select the name and price of all products with a price larger
than or equal to 180, and sort first by price (in descending
order), and then by name (in ascending order).

Select name,price from products where price > 180 order by

price desc, name ;

OUTPUT:

3. Select all the data from the products, including all the data
for each product's manufacturer.
SELECT p.name, p.price,m.name from products p, manufacturers m
where p.manufacturer = m.m_code;

OUTPUT:

4. Select the average price of each manufacturer's products,

showing the manufacturer's name.

Select m.name,avg(p.price) from products p, manufacturers m

where p.manufacturer = m.m_code group by p.manufacturer;
5. Select the names of manufacturer whose products have an
average price larger than or equal to 150.
Select m.name,avg(p.price) from products p, manufacturers m
where p.manufacturer = m.m_code group by p.manufacturer having
avg(price) > 150;

6. Select the name and price of the cheapest product.

Select name,price from products where price=(select min(price)
from products);

OUTPUT

7. Select the name of each manufacturer along with the name and
price of its most expensive product.
Select p.name,p.price,m.name from products p,manufacturers m
where m_code=manufacturer and price=(select max(price) from
products where m_code=manufacturer);
OUTPUT
8. Select the name of each manufacturer which have an average
price above 145 and contain at least 2 different products.

Select m.name,count(m.name),avg(p.price) from products p,

manufacturers m where m_code = manufacturer group by
manufacturer having avg(price) > 150 and count(*) >=2;
OUTPUT:

9. Update the name of product 8 to "Laser Printer".

update products set name="Laser Printer" where p_code=8;

OUTPUT:

10. Apply a 10% discount to all products with a price larger

than or equal to 120.
Select name, round(price - price*10/100) as 'Discounted Price',
price 'Actual Price' from products;

OUTPUT:
 PYTHON – MySQL Connectivity#1

Aim:- To write a python script to establish Python

MySQLconnectivity.

SOURCE CODE:
import mysql.connector as mysql

def connect_with_mysql():

try:

con=mysql.connect(host="localhost",user="root",password="Vvsn@
123",database="stud")

if con.is_connected():

db_info=con.get_server_info()

print("=====Python- MySQL integration===========")

print()

print("Connection established to MySQLversion",db_info)

except:

print("Connection error")

finally:

if con.is_connected():

con.close()

print("Connection closed")

print("======================================")

connect_with_mysql()

OUTPUT:-
 PYTHON – MySQL Connectivity#2

Aim: - Write a program to display all record from table

‘student’ using MySQL database connectivity in python.

Source Code:-

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
password="Vvsn@123",database="student")
mycursor=mydb.cursor()
mycursor.execute("Select * from classA")
mydata=mycursor.fetchall()
for i in mydata:
print(i)
print("The total number of rows are fetched:",
mycursor.rowcount)

Output:-

(101, 'Ajay', (2000, 12, 22), 'Anna Nagar', 'Madurai')

(102, 'Siva', (2001, 10, 20), 'Yercaud Road', 'Salem')
(103, 'Sanjay', (2000, 9, 2), 'JJ Colony', 'Salem')
(104, 'Bilal', (2002, 10, 22), 'KK Nagar', 'Trichy')
(105, 'Saran', (2000, 8, 5), 'SS Nagar', 'Chennai')
The total numbers of rows are fetched: 5
 PYTHON – MySQL Connectivity#3

Aim: - Write a program to display one record from table

‘student’ using MySQL database connectivity in python.

Source Code:-

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
password="Vvsn@123",database="student")
mycursor=mydb.cursor()
mycursor.execute("Select * from classA")
mydata=mycursor.fetchone()
print(mydata)
print("The total number of rows are fetched:",
mycursor.rowcount)

Output:-

(101, 'Ajay', (2000, 12, 22), 'Anna Nagar', 'Madurai')

The total numbers of rows are fetched: 1
 PYTHON – MySQL Connectivity#4

Aim: - Write a program to display number of record from

table ‘student’ using MySQL database connectivity in
python.

Source Code:-

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
password="Vvsn@123",database="student")
mycursor=mydb.cursor()
mycursor.execute("Select * from classA")
mydata=mycursor.fetchmany(3)
for i in mydata:
print(i)
print("The total number of rows are fetched:",
mycursor.rowcount)

Output:-

(101, 'Ajay', (2000, 12, 22), 'Anna Nagar', 'Madurai')

(102, 'Siva', (2001, 10, 20), 'Yercaud Road', 'Salem')
(103, 'Sanjay', (2000, 9, 2), 'JJ Colony', 'Salem')

The total numbers of rows are fetched:3