SQL Docx

PROBLEM NUMBER: 1
PROBLEM STATEMENT: Create a SHOPPE table with the ID, SName and Area as attributes
where the ID is Primary Key
SOURCE CODE:
mysql> use PRACTICAL;
Database changed
mysql> create table SHOPPE(Id varchar(20),SName varchar(20),Area varchar(20));
Query OK, 0 rows affected (7.96 sec)
SCREENSHOT OF THE SOURCE CODE:
OUTPUT :
mysql> describe SHOPPE;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Id | varchar(20) | YES | | NULL | |
| SName | varchar(20) | YES | | NULL |
| Area | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (1.49 sec)
SCREENSHOT OF Output:
1|Page
1. Insert the details of new students in the above table
SOURCE CODE:
mysql> insert into SHOPPE values("S001","ABC Computeronics","CP");
Query OK, 1 row affected (1.54 sec)
mysql> insert into SHOPPE values("S002","All Infotech Media","GK II");
mysql> insert into SHOPPE values("S003","Tech Shoppe","CP");

mysql> insert into SHOPPE values("S004","Geeks Tecno Soft","Nehru Place");

mysql> insert into SHOPPE values("S005","Hitech Tech Store","Nehru Place");

OUTPUT :
mysql> Select*from SHOPPE;
+------+--------------------+-------------+
| Id | SName | Area |
+------+--------------------+-------------+
| S001 | ABC Computeronics | CP |
| S002 | All Infotech Media | GK II |
| S003 | Tech Shoppe | CP |
| S004 | Geeks Tecno Soft | Nehru Place |
| S005 | Hitech Tech Store | Nehru Place |
+------+--------------------+-------------+
2|Page
3|Page
PROBLEM NUMBER:2
PROBLEM STATEMENT: Create a database COMPANY and within that database, create
tables PRODUCT and CLIENT and perform the following queries. (For the first table,
mention P_ID as the primary key and for the second table, mention C_ID as the primary
key.)
TABLE: PRODUCT
TABLE: CLIENT
1. to display the details of those clients whose CITY is “DELHI”

SOURCE CODE:
mysql> select*from CLIENT where CITY='DELHI';
4|Page
OUTPUT :
+------+---------------+-------+------+
| C_ID | CLIENT_NAME | CITY | P_ID |
+------+---------------+-------+------+
| 1 | COSMETIC SHOP | DELHI | FW05 |
| 12 | LIVE LIFE | DELHI | SH06 |
| 15 | PRETTY WOMAN | DELHI | FW12 |
+------+---------------+-------+------+
2. To display the details of those clients whose price is between 50 and 100
SOURCE CODE:
mysql> Select*from PRODUCT where PRICE between 50 and 100;
OUTPUT :
+------+--------------+--------------+-------+
| P_ID | PRODUCT_NAME | MANUFACTURER | PRICE |
+------+--------------+--------------+-------+
| BS01 | BATH SHOP | ABC | 55 |
| FW12 | FACE WASH | XYZ | 95 |
+------+--------------+--------------+-------+
5|Page
To display CLIENT_NAME, CITY, PRODUCT_NAME, PRICE FROM PRODUCT, CLIENT

3.
where PRODUCT. P_ID=CLIENT.P_ID
SOURCE CODE:
mysql> select CLIENT_NAME,CITY,PRODUCT_NAME,PRICE FROM PRODUCT,CLIENT where
PRODUCT.P_ID=CLIENT.P_ID;
OUTPUT :
+---------------+-----------+--------------+-------+
| CLIENT_NAME | CITY | PRODUCT_NAME | PRICE |
+---------------+-----------+--------------+-------+
| COSMETIC SHOP | DELHI | FACE WASH | 45 |
| TOTAL HEALTH | MUMBAI | BATH SHOP | 55 |
| LIVE LIFE | DELHI | SHAMPOO | 120 |
| PRETTY WOMAN | DELHI | FACE WASH | 95 |
| DREAMS | BENGALURU | TALCUM POWER | 40 |
+---------------+-----------+--------------+-------+
6|Page
4.to update the price increased by 10
SOURCE CODE:
mysql> UPDATE PRODUCT SET PRICE=PRICE+10;
Rows matched: 5 Changed: 5 Warnings: 0
mysql> SELECT*FROM PRODUCT;
OUTPUT :
+------+--------------+--------------+-------+
| P_ID | PRODUCT_NAME | MANUFACTURER | PRICE |
+------+--------------+--------------+-------+
| BS01 | BATH SHOP | ABC | 65 |
| FW05 | FACE WASH | ABC | 55 |
| FW12 | FACE WASH | XYZ | 105 |
| SH06 | SHAMPOO | XYZ | 130 |
| TP01 | TALCUM POWER | LAK | 50 |
+------+--------------+--------------+-------+
7|Page
5. To display details from product where MANUFACTURER is "ABC" AND PRICE< is less
than 50;
SOURCE CODE:
mysql> SELECT* from product where MANUFACTURER="ABC" AND PRICE<50;
OUTPUT :
Empty set (0.00 sec)
6. To display the maximum price from product table;
SOURCE CODE:
mysql> SELECT MAX(PRICE) FROM PRODUCT;
OUTPUT :
+------------+
| MAX(PRICE) |
+------------+
| 130 |
+------------+
1 row in set (0.09 sec)
8|Page
7. Display C_ID, CLIENT_NAME,CITY FROM CLIENT, PRODUCT WHERE

PRODUCT.PRODUCT_NAME="FACE WASH" AND CLIENT.P_ID=PRODUCT.P_ID
SOURCE CODE:
mysql> SELECT C_ID, CLIENT_NAME,CITY FROM CLIENT, PRODUCT WHERE PRODUCT.PRODUCT_NAME="FACE

WASH" AND CLIENT.P_ID=PRODUCT.P_ID;
OUTPUT :
+------+---------------+-------+
| C_ID | CLIENT_NAME | CITY |
+------+---------------+-------+
| 1 | COSMETIC SHOP | DELHI |
| 15 | PRETTY WOMAN | DELHI |
+------+---------------+-------+
9|Page
PROBLEM NUMBER: 3
PROBLEM STATEMENT:
Create a student table with the student ID, Name and marks as attributes where the
Student ID is Primary Key and perform the following queries
SOURCE CODE:
mysql> create database school;
mysql> use school;

Database changed
mysql> create table STUDENT (STUDENT_ID int(5) primary key, NAME varchar(20) not null, MARKS decimal(5,2));
Query OK, 0 rows affected, 1 warning (0.87 sec)
mysql> describe STUDENT;
OUTPUT :
+------------+--------------+------+-----+---------+---
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| STUDENT_ID | int | NO | PRI | NULL | |
| NAME | varchar(20) | NO | | NULL | |
| MARKS | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
10 | P a g e
1.Insert the details of new students in the above table

SOURCE CODE:
mysql> insert into STUDENT values(1,'Arpit',78.5);
mysql> insert into STUDENT values(2,'Bharti',87.5);

mysql> insert into STUDENT values(3,'Chintu',62.5);\

mysql> insert into STUDENT values(4,'Charmi',89.5);

mysql> insert into STUDENT values(5,'Divya',80.0);

mysql> select*from student;

OUTPUT :
+------------+--------+-------+
| STUDENT_ID | NAME | MARKS |
+------------+--------+-------+
| 1 | Arpit | 78.50 |
| 2 | Bharti | 87.50 |
| 3 | Chintu | 62.50 |
| 4 | Charmi | 89.50 |
11 | P a g e
| 5 | Divya | 80.00 |
+------------+--------+-------+
2.Delete the details of a student in the table

SOURCE CODE:
mysql> delete from student where STUDENT_ID=5;
OUTPUT :
+------------+--------+-------+
+------------+--------+-------+
| 1 | Arpit | 78.50 |
| 2 | Bharti | 87.50 |
| 3 | Chintu | 62.50 |
| 4 | Charmi | 89.50 |
+------------+--------+-------+
12 | P a g e
2.Use the select command to get the details of the students with marks more than 80.
SOURCE CODE:
mysql> select*from student where marks>80;
OUTPUT :
+------------+--------+-------+
+------------+--------+-------+
| 2 | Bharti | 87.50 |
| 4 | Charmi | 89.50 |
+------------+--------+-------+
3.Find the min, max, sum and average of the marks in a student marks table.
SOURCE CODE:
mysql> select max(Marks), min(marks),sum(marks),avg(marks) from STUDENT;
13 | P a g e
OUTPUT :
+------------+------------+------------+------------+
| max(Marks) | min(marks) | sum(marks) | avg(marks) |
+------------+------------+------------+------------+
| 89.50 | 62.50 | 318.00 | 79.500000 |
+------------+------------+------------+------------+
4.Write an SQL query to display the marks without decimal places, display the
reminder after dividing marks by 3 and display the square of marks.
SOURCE CODE:
mysql> select round(marks,0), mod(marks,3), pow(marks,2) from student;
OUTPUT :
+----------------+--------------+--------------+
| round(marks,0) | mod(marks,3) | pow(marks,2) |
+----------------+--------------+--------------+
| 79 | 0.50 | 6162.25 |
| 88 | 0.50 | 7656.25 |
| 63 | 2.50 | 3906.25 |
| 90 | 2.50 | 8010.25 |
+----------------+--------------+--------------+
14 | P a g e
5.Write an SQL query to display names into capital letter, display first 3 letters of
name, display last 3 letters of name, display the position the letter A in name.
SOURCE CODE:
mysql>select ucase(name), lcase(name), left(name,3), right(name,3), instr(name,'a') from student;
OUTPUT :
+-------------+-------------+--------------+---------------+-----------------+
| ucase(name) | lcase(name) | left(name,3) | right(name,3) | instr(name,'a') |
+-------------+-------------+--------------+---------------+-----------------+
| ARPIT | arpit | Arp | pit | 1|
| BHARTI | bharti | Bha | rti | 3|
| CHINTU | chintu | Chi | ntu | 0|
| CHARMI | charmi | Cha | rmi | 3|
+-------------+-------------+--------------+---------------+-----------------+
6.Remove extra spaces from left, right and both sides from the text-“Computer Science
Class XII”
SOURCE CODE:
mysql> select ltrim(" Computer Science Class XII") "Left spaces", rtrim(" Computer Science Class XII ") "Right
Spaces",trim(" Computer Science Class XII ") "Both spaces";
15 | P a g e
OUTPUT :
+----------------------------+-----------------------------+---------------+
| Left spaces | Right Spaces | Both spaces |
+----------------------------+-----------------------------+---------------+
| Computer Science Class XII | Computer Science Class XII | Computer Science Class XII |
+------------------------+-----------------------------+----------------+
7.Display today’s date in “Date/Month/Year” format.

SOURCE CODE:
mysql> select concat(day(now()), "/", month(now()), "/", year(now()));
OUTPUT :
+------------------------------------------------------+
| concat(day(now()),"/", month(now()),"/",year(now())) |
+------------------------------------------------------+
| 22/10/2022 |
+------------------------------------------------------+
16 | P a g e
8.Display dayname, monthname, day of month, day of year for today’s date.
SOURCE CODE:
mysql> select dayname(now()), monthname(now()) , day(now()),dayname(now()),dayofyear(now());
OUTPUT :
+----------------+------------------+------------+----------------+------------------+
| dayname(now()) | monthname(now()) | day(now()) | dayname(now()) | dayofyear(now()) |
+----------------+------------------+------------+----------------+------------------+
| Saturday | October | 22 | Saturday | 295 |
+----------------+------------------+------------+----------------+------------------+
17 | P a g e
PROBLEM NUMBER: 4
PROBLEM STATEMENT: Create a employee table with the E_CODE, E_Name, SEX,
Dept, Salary and City as attributes and perform the following functions.
SOURCE CODE: TABLE EMPLOYEE
mysql> create table EMPLOYEE(E_CODE varchar(4),E_NAME text(20),SEX text(2), DEPT text(20),SALARY int(10),CITY
text(10));
mysql> insert into EMPLOYEE values("E01","Mukesh Mondal","M","Accountancy","25000","Delhi");
mysql> insert into EMPLOYEE values("E02","Mahesh Chatterjee","M","Physical E
ducation","15000","Gawhati");
mysql> insert into EMPLOYEE values("E03","Tuheen Kundu","M","Economics","450
00","Surat");
mysql> insert into EMPLOYEE values("E04","Ahana Singha","F","Biology","21700
","Jaipur City");
mysql> insert into EMPLOYEE values("E05","Adhrikto Das","M","Philosophy","35
300","Mumbai");
mysql> insert into EMPLOYEE values("E06","Sohom Mishra","M","Psychology","37
600","Chandigarh");
mysql> insert into EMPLOYEE values("E07","Mainak Dube","M","Litrature","1990
0","Chennai");
18 | P a g e
mysql> insert into EMPLOYEE values("E08","Suvasree Islam","F","physiology","
69000","Hridaypur");
mysql> insert into EMPLOYEE values("E09","Chandrika Ash","F","Orthopedic","3
9800","Jaipur City");
mysql> insert into EMPLOYEE values("E10","Bhumika Sammadar","F","Cardiologis
t","75000","Gawhati");
19 | P a g e
SCREENSHOT OF TABLE:
1.Display name of department each department should be displayed once.
SOURCE CODE:
mysql> select distinct DEPT from EMPLOYEE;
SCREENSHOT:
2.Find the name and salary of those employee whose salary 35000 and 40000.
SOURCE CODE:
mysql> select E_NAME,SALARY from EMPLOYEE where SALARY>35000 and salary<40000;
20 | P a g e
SCREENSHOT:
3.Find the name of those employee living in Jaipur city, surat.

SOURCE CODE:
mysql> select E_NAME from EMPLOYEE where CITY="Gawhati" or CITY="Surat" or CITY="Jaipur City";
SCREENSHOT:
4.Display name of employees whose name start with ’M’.

SOURCE CODE:
mysql> select E_NAME from EMPLOYEE where E_NAME like 'M%';
SCREENSHOT:
5.Display name of employee not assigned to a department.

SOURCE CODE:
mysql> select E_NAME from EMPLOYEE where DEPT='';
21 | P a g e
SCREENSHOT:
6.Display name of employees descending order of employee code of each

department.
SOURCE CODE:
mysql> select E_NAME from EMPLOYEE order by E_CODE desc;
SCREENSHOT:
7.Find max salary of each department.

SOURCE CODE:
mysql> select max(SALARY),avg(SALARY) from EMPLOYEE group by DEPT;
SCREENSHOT:
22 | P a g e
8.Display average salary of employee in the employee table.
SOURCE CODE:
mysql> select avg(SALARY) from EMPLOYEE;
SCREENSHOT:
9.Display min salary of female employee in the employee table.

SOURCE CODE:
mysql> select min(SALARY) from EMPLOYEE where SEX='F';
SCREENSHOT:
10.Display min salary of male employee in the employee table.

SOURCE CODE:
mysql> select sum(SALARY) from EMPLOYEE where CITY='Gawhati';
23 | P a g e
SCREENSHOT:
24 | P a g e
PROBLEM NUMBER: 5
PROBLEM STATEMENT: Create WORKERS and DESIG table and perform the following
functions
SOURCE CODE: TABLE WORKERS
mysql> create database prac;
mysql> use prac;
Database changed
mysql> create table WORKERS(W_ID varchar(20),FIRSTNAME varchar(20), LASTNAME varchar(20), ADDRESS

varchar(20), CITY varchar(20));
mysql> drop table WORKERS;
mysql> create table WORKERS(W_ID int(20),FIRSTNAME varchar(20), LASTNAME varchar(20), ADDRESS

varchar(20), CITY varchar(20));
mysql> insert into WORKERS(102,"Sam","Tones","33 Elm SL", "Paris");
mysql> insert into WORKERS values(102,"Sam","Tones","33 Elm SL", "Paris");
mysql> insert into WORKERS values(105,"Sarah","Ackerman","440 U.S 110", "New York");
mysql> insert into WORKERS values(144,"Manila","Sengupta","24 Friends Street", "New Delhi");
mysql> insert into WORKERS values(210,"George","Smith","83 First Street", "Howard");
mysql> insert into WORKERS values(255,"Mary","Jones","842 Vine Ave.", "Losantiville");
mysql> insert into WORKERS values(300,"Robert","Samuel","9 fifth cross", "Washington");
mysql> insert into WORKERS values(335,"Henry","Willium","12 Moore Street", "Boston");
mysql> insert into WORKERS values(403,"Ronny","Lee","121 Harrison St., "New York");
insert into WORKERS values(403,"Ronny","Lee","121 Harrison St., "New York");
insert into WORKERS values(403,"Ronny","Lee","121 Harrison St., "' at line 1

25 | P a g e
mysql> insert into WORKERS values(403,"Ronny","Lee","121 Harrison St.", "New York");
mysql> insert into WORKERS values(451,"Pat","Thompson","11 Red Road", "Paris");
SCREENSHOT:
26 | P a g e
SOURCE CODE: TABLE DESIG
mysql> create table DESIG(W_ID int(20),SALARY int(20), BENEFITS int(20), DESIGNATION varchar(20));
Query OK, 0 rows affected, 3 warnings (1.51 sec)
mysql> insert into DESIG values(102, 75000, 15000, "Manager");
mysql> insert into DESIG values(105, 85000, 25000, "Director");
mysql> insert into DESIG values(255, 50000, 12000, "Clerk");
mysql> insert into DESIG values(400, 32000, 7500, "Salesman");
mysql> insert into DESIG values(451, 28000, 7500, "Salesman");
27 | P a g e
SCREENSHOT:
1. To display W_ID, Firstname, Address and city of all employees living in New York
from table workers.
SOURCE CODE:
mysql> select W_ID,FIRSTNAME,ADDRESS,CITY from WORKERS where CITY="New York";
SCREENSHOT:
28 | P a g e
OUTPUT:
2.To display contents of worker table in ascending order of LASTNAME.
SOURCE CODE:
mysql> select * from WORKERS order by LASTNAME;
SCREENSHOT:
OUTPUT:
3. To display the firstname, Lastname and Total salary of all clerks from table
WORKERSand DESIG where Total Salary is calculated as salary+benefits.
SOURCE CODE:
mysql> select FIRSTNAME,LASTNAME,SALARY+BENEFITS from WORKERS,DESIG where
DESIGNATION="Clerk";
SCREENSHOT:
29 | P a g e
OUTPUT:
4.To display the minimum salary among managers and clerks from table DESIG
SOURCE CODE:
mysql> select max(SALARY) from DESIG where DESIGNATION="Manager" or
DESIGNATION="Clerk";
SCREENSHOT:
OUTPUT:
5.To display the Firstname, salary where designation is of managers and clerks from
table DESIG and WORKERS.
SOURCE CODE:
mysql> select FIRSTNAME,SALARY from WORKERS,DESIG where DESIGNATION = "Manager"and
WORKERS.W_ID=DESIG.W_ID;
30 | P a g e
SCREENSHOT:
OUTPUT:
6.To display the count of distinct number of designations from DESIG.
SOURCE CODE:
mysql> select count(DISTINCT DESIGNATION)from DESIG;
SCREENSHOT:
OUTPUT:
31 | P a g e
7.To display DESIGNATION, Sum(SALARY) from table DESIG grouped by DESIGNATION
having less than three frequency
SOURCE CODE:
mysql> select DESIGNATION, Sum(SALARY)from DESIG group by DESIGNATION HAVING
COUNT(*)<3;
SCREENSHOT:
OUTPUT:
8.To display the sum of benefits of the attributes having designation as salesman.
SOURCE CODE:
mysql> select Sum(BENEFITS)from DESIG where DESIGNATION="Salesman";
SCREENSHOT:
OUTPUT:
32 | P a g e
PROBLEM NUMBER: 6
PROBLEM STATEMENT: Consider the following MOVIE table and write the SQL queries
based on it.
SOURCE CODE: TABLE MOVIE
create table MOVIE(Movie_ID varchar(5),MovieName text(20),type text(15),ReleaseDate text(20),ProductionCost
int(15),BusinessCost int(15));
mysql> insert into MOVIE values("M001","The Kashmir Files","Action","2022-01-26","1245000","1300000");
mysql> insert into MOVIE values("M002","Attack","Action","2022-01-28","1120000","1250000");
mysql> insert into MOVIE values("M003","Looop Lapeta","Thriller","2022-02-01","250000","300000");
mysql> insert into MOVIE values("M004","Badhai Do","Drama","2022-02-04","720000","68000");
mysql> insert into MOVIE values("M005","Shabash Mitthu","Biography","2022-02-04","1000000","800000");
mysql> insert into MOVIE values("M006","Gehariyaan","Romance","2022-02-11","150000","120000");
SCREENSHOT:
33 | P a g e
Display all information from movie.
SOURCE CODE:
mysql> select * from MOVIE;
SCREENSHOT:
1.Display the type of movies.

SOURCE CODE:
mysql> select distinct type from MOVIE;

SCREENSHOT:
34 | P a g e
2. Display movie id, movie name, total_earning by showing the business done by the
movies. Calculate the business done by movie using the sum of production cost
and business cost.
SOURCE CODE:
mysql> select Movie_ID,MovieName,ProductionCost + BusinessCost "Total Earning" from MOVIE;
SCREENSHOT:
3. Display movie id, movie name and production cost for all the movies with greater
than 150000 and less than 1000000.
SOURCE CODE:
mysql> select Movie_ID,MovieName,ProductionCost from MOVIE where ProductionCost<1000000 and
ProductionCost>150000;
SCREENSHOT:
35 | P a g e
4. Display the movie of type action and romance.
SOURCE CODE:
mysql> select MovieName from MOVIE where type='Action' or type='Romance';
SCREENSHOT:
5. Display the list of movies which are going to release in February,2022.

SOURCE CODE:
mysql> select MovieName from MOVIE where month(ReleaseDate)=2;
SCREENSHOT:
36 | P a g e
6. Write a query to display cube of 5.
SOURCE CODE:
mysql> select pow(5,3);
SCREENSHOT:
7.Write a query to display the number 563.854741 rounding off to the next hundred.
SOURCE CODE:
mysql> select round(563.854741,-2);
SCREENSHOT:
8.Write a query to display “put” from the word “Computer”.

SOURCE CODE:
mysql> select mid("Computer",4,3);
SCREENSHOT:
37 | P a g e
9.Write a query to display today’s date into DD.MM.YYYY fromat.
SOURCE CODE:
mysql> select concat(day(now()),concat('.',month(now()),concat('.',year(now())))) "Date";
SCREENSHOT:
10. Write a query to display ‘DIA’ from the word ‘MEDIA’.

SOURCE CODE:
mysql> select right("Media",3);
SCREENSHOT:
11. Write a query to display moviename-type from the table movie.

SOURCE CODE:
mysql> select concat(MovieName,concat('-',type)) from MOVIE;
SCREENSHOT:
38 | P a g e
12. Write a query to display first four digits of production cost.
SOURCE CODE:
mysql> select left(ProductionCost,4) from MOVIE;
SCREENSHOT:
Write a query to display last four digits of business cost.
SOURCE CODE:
mysql> select right(BusinessCost,4) from MOVIE;
SCREENSHOT:
14.Write a query to display weekday of release dates.

SOURCE CODE:
mysql> select weekday(ReleaseDate) from MOVIE;
39 | P a g e
SCREENSHOT:
15.Write a query to display dayname on who=ich movies are going to be

released.
SOURCE CODE:
mysql> select dayname(ReleaseDate) from MOVIE;
SCREENSHOT:
40 | P a g e
PROBLEM NUMBER: 7
PROBLEM STATEMENT: Suppose your school management has decided to conduct
matches between students of class XI & XII. Students of each class are asked to join
any one of the four teams- Team Titans, Team Rockers, Team Magnet, Team
Magnet. During summer vacations, various matches will be conducted between
these teams help your sports teacher to do the following:
SOURCE CODE: TABLE SPORTS
mysql> create table team(teamid int(1),teamname varchar(10),primary key(teamid));
mysql> insert into team values(1,'Tehlka');
mysql> insert into team values(2,'Toofan');
mysql> insert into team values(3,'Aandhi');
mysql> insert into team values(4,'Shailab');
SCREENSHOT:
SOURCE CODE: TABLE MATCH_DETAILS
mysql> create table match_details(matchid varchar(2) primary key,matchdate date,firstteamid int(1) references
team(teamid),Secondteamid int(1) references team(teamid),firstteamscore int(3),secondteamscore int(3));
41 | P a g e
mysql> insert into match_details values("M1","2021-12-20",1,2,107,93);
SCREENSHOT:
Create a database “sports”.
mysql> create database sports;

mysql> use sports;

Database changed
42 | P a g e
SCREENSHOT:
1. Create a table “Team” and show the contents using DML statements.
 Structure of table:
SOURCE CODE:
mysql> desc team;
SCREENSHOT:
 Inserting the data:

SOURCE CODE:
mysql> insert into team values(1,'Tehlka');
mysql> insert into team values(2,'Toofan');

mysql> insert into team values(3,'Aandhi');

mysql> insert into team values(4,'Shailab');

SCREENSHOT:
43 | P a g e
 Showing the table details:
SOURCE CODE:
mysql> desc team;
SCREENSHOT:
2. Create another table “Match_Details” and insert show the inserted data.
SOURCE CODE:
mysql> create table match_details(matchid varchar(2) primary key,matchdate date,firstteamid int(1) references
team(teamid),Secondteamid int(1) references team(teamid),firstteamscore int(3),secondteamscore int(3));
mysql> select * from match_details;
SCREENSHOT:
44 | P a g e
PROGRAM NUMBER :8
PROGRAM STATEMENT:
Write a MySQL connectivity program in Python to
a) Create a database school
b) Create a table students with the specifications-ROLLNO integer, STNAME
character(10) in MySQL and perform the following operations:
i) Insert two records in it
ii) Display the contents of the table
SOURCE CODE:
import mysql.connector as ms
db=ms.connect(host="localhost",user="root",password="suchi",database="school")
c=db.cursor()
#Function to create database as per users choice
def c_database( ):
try:
dn=input("Enter Database Name =")
c.execute("Create Database {}".format(dn))
c.execute("use {}".format(dn))
print("Database created successfully")
except Exception as a:
print("Database Error",a)
#Function to Drop Database as per users choice
def d_database():
try:
dn=input("Enter Database Name to be dropped=")
c.execute("drop database {}".format(dn))
print("Database deleted successfully")
print("Database Drop Error",a)
#Function to create Table
def c_table():
try:
45 | P a g e
#c.execute("use {}".format(dn))
c.execute("create table students(rollno int(3),stname varchar(20))")
print("Table created successfully")
print("Create Table error",a)
#Function to insert data
def e_data():
try:
while True:
rno=int(input("Enter Student rollno="))
name = input("Enter student name=")
# c.execute("use {}".format(dn))
c.execute("insert into students values({},'{}')".format(rno,name))#("insert into students

values({},'{}',{},'{}')".format(rno,sname,marks,gr))
db.commit()
choice=input("Do you want to add more record <y/n>=")
if choice in "Nn":
break
print("Insert Record Error",a)
#Function to Display Data
def d_data():
try:
c.execute("select * from students")
data=c.fetchall()
for i in data:
print(i)
print("Display Record Error",a)
while True:
print("MENU\n1. Create Database\n2. Drop Database\n3.Create Table\n4. Insert Record\n5. Display Entire
Data\n6.Exit")
choice= int(input("Enter your choice<1-6>="))
if choice==1:
46 | P a g e
c_database()
elif choice ==2:
d_database()
elif choice==3:
c_table()
elif choice==4:
e_data()
elif choice==5:
d_data()
elif choice==6:
break
else:
print("Wrong option selected")
SCREENSHOT OF SOURCE CODE:
47 | P a g e
OUTPUT:
MENU
1. Create Database
2. Drop Database
3.Create Table
4. Insert Record
5. Display Entire Data
6.Exit
Enter your choice<1-6>=1
Enter Database Name =CBSE
Database created successfully
MENU
1. Create Database
2. Drop Database
3.Create Table
4. Insert Record
6.Exit
Enter Database Name to be dropped=CBSE
Database deleted successfully
MENU
1. Create Database
48 | P a g e
2. Drop Database
3.Create Table
4. Insert Record
6.Exit
Enter Database Name =CBSE
Database created successfully
MENU
1. Create Database
2. Drop Database
3.Create Table
4. Insert Record
6.Exit
Table created successfully
MENU
1. Create Database
2. Drop Database
3.Create Table
4. Insert Record
6.Exit
Enter Student rollno=1
Enter student name=Rik
Do you want to add more record <y/n>=y
Enter student name=Sita
Enter student name=Tina
49 | P a g e
Enter student name=Ron
Enter student name=Ayan
Do you want to add more record <y/n>=n
SCREENSHOT OF OUTPUT:
50 | P a g e
PROGRAM NUMBER:9
PROGRAM STATEMENT:
Perform all the operations with reference to table ‘students’ through MySQL-Python
connectivity.
SOURCE CODE:
import mysql.connector as ms
db=ms.connect(host="localhost",user="root",passwd="suchi",database="school")
cn=db.cursor()
def insert_rec():
try:
while True:
rn=int(input("Enter roll number:"))
sname=input("Enter name:")
marks=float(input("enter marks:"))
gr=input("enter grade:")
cn.execute("insert into students values({},'{}',{},'{}')".format(rn,sname,marks,gr))
db.commit()
ch=input("want more record? Press (N/n) to stop entry:")
if ch in 'Nn':
break
except Exception as e:
print("error",e)
def update_rec():
try:
rn=int(input("enter the rollno. to update:"))
marks=float(input("enter new marks:"))
gr=input("Enter Grade:")
cn.execute("update students set marks={},grade='{}' where roll={}".format(marks,gr,rn))
db.commit()
print("Update succesfully")
print("Error",e)
51 | P a g e
def delete_rec():
try:
rn=int(input("Enter rollno. to delete:"))
cn.execute("delete from students where roll={}".format(rn))
db.commit()
print("DElete successful")
print("Error",e)
def view_rec():
try:
cn.execute("select * from students")
data=cn.fetchall()
for i in data:
print(i)
print("Display Record Error",a)
while True:
print("MENU\n1.Insert Record\n2.Update Record\n3.Delete Record\n4.Display Record\n5.Exit")
ch=int(input("Enter Your choise<1-4>="))
if ch==1:
insert_rec()
elif ch==2:
update_rec()
elif ch==3:
delete_rec()
elif ch==4:
view_rec()
elif ch==5:
break
else:
print("wrong option selected")
52 | P a g e
OUTPUT:
MENU
1.Insert Record
2.Update Record
3.Delete Record
4.Display Record
5.Exit
Enter Your choise<1-4>=1
Enter roll number:1
Enter name:Rik
enter marks:90
enter grade:A
want more record? Press (N/n) to stop entry:y
53 | P a g e
Enter roll number:2
Enter name:Rita
enter marks:70
enter grade:B
Enter roll number:3
Enter name:Sita
enter marks:95
enter grade:A
Enter roll number:4
Enter name:Ron
enter marks:75
enter grade:B
Enter roll number:5
Enter name:Ayan
enter marks:33
enter grade:F
want more record? Press (N/n) to stop entry:n
MENU
1.Insert Record
2.Update Record
3.Delete Record
4.Display Record
5.Exit
Enter Your choise<1-4>=
54 | P a g e
55 | P a g e
PROGRAM NUMBER:10
PROGRAM STATEMENT:
Write a program in python by using the ORDER BY statement to
sort the result in ascending or descending order.
SOURCE CODE:
import mysql.connector
mydb =mysql.connector.connect(host="localhost",user="root",password="suchi",database="school")
mycursor = mydb.cursor()
sql = "SELECT * FROM student ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
OUTPUT:
(1, 'arnab', 39.8, 'a')
(5, 'Ayan', 33.0, 'F')
(1, 'Rik', 90.0, 'A')
(2, 'Rita', 70.0, 'B')
(4, 'Ron', 75.0, 'B')
(3, 'Sita', 95.0, 'A')
56 | P a g e
PROGRAM NUMBER:11
PROGRAM STATEMENT:
Write a program in python to limit the number of records
returned from the sql query, by using the "LIMIT" statement:
SOURCE CODE:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",password="suchi",database="school")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM student LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
OUTPUT:
(1, 'suchi', 100.0, 'a')
(2, 'joy', 60.0, 'b')
(3, 'arnab', 50.0, 'b')
(3, 'shreya', 99.0, 'a')
(3, 'tuheen', 90.0, 'a')
57 | P a g e

SQL Docx

Uploaded by

Copyright:

Available Formats

SQL Docx

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Docx

Uploaded by

Copyright:

Available Formats

PROBLEM NUMBER: 1

SCREENSHOT OF THE SOURCE CODE:

mysql> insert into SHOPPE values("S003","Tech Shoppe","CP");

mysql> insert into SHOPPE values("S004","Geeks Tecno Soft","Nehru Place");

mysql> insert into SHOPPE values("S005","Hitech Tech Store","Nehru Place");

SCREENSHOT OF THE SOURCE CODE:

1. to display the details of those clients whose CITY is “DELHI”

SCREENSHOT OF THE SOURCE CODE:

SCREENSHOT OF THE SOURCE CODE:

To display CLIENT_NAME, CITY, PRODUCT_NAME, PRICE FROM PRODUCT, CLIENT

SCREENSHOT OF THE SOURCE CODE:

mysql> SELECT*FROM PRODUCT;

SCREENSHOT OF THE SOURCE CODE:

SCREENSHOT OF THE SOURCE CODE:

6. To display the maximum price from product table;

SCREENSHOT OF THE SOURCE CODE:

1 row in set (0.09 sec)

7. Display C_ID, CLIENT_NAME,CITY FROM CLIENT, PRODUCT WHERE

mysql> SELECT C_ID, CLIENT_NAME,CITY FROM CLIENT, PRODUCT WHERE PRODUCT.PRODUCT_NAME="FACE

SCREENSHOT OF THE SOURCE CODE:

mysql> use school;

mysql> describe STUDENT;

SCREENSHOT OF THE SOURCE CODE:

1.Insert the details of new students in the above table

mysql> insert into STUDENT values(2,'Bharti',87.5);

mysql> insert into STUDENT values(3,'Chintu',62.5);\

mysql> insert into STUDENT values(4,'Charmi',89.5);

mysql> insert into STUDENT values(5,'Divya',80.0);

mysql> select*from student;

2.Delete the details of a student in the table

mysql> select*from student;

SCREENSHOT OF THE SOURCE CODE:

SCREENSHOT OF THE SOURCE CODE:

SCREENSHOT OF THE SOURCE CODE:

SCREENSHOT OF THE SOURCE CODE:

SCREENSHOT OF THE SOURCE CODE:

7.Display today’s date in “Date/Month/Year” format.

SCREENSHOT OF THE SOURCE CODE:

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> insert into EMPLOYEE values("E01","Mukesh Mondal","M","Accountancy","25000","Delhi");

Query OK, 1 row affected (0.01 sec)

mysql> insert into EMPLOYEE values("E02","Mahesh Chatterjee","M","Physical E

Query OK, 1 row affected (0.01 sec)

mysql> insert into EMPLOYEE values("E03","Tuheen Kundu","M","Economics","450

Query OK, 1 row affected (0.01 sec)

mysql> insert into EMPLOYEE values("E04","Ahana Singha","F","Biology","21700

Query OK, 1 row affected (0.00 sec)

mysql> insert into EMPLOYEE values("E05","Adhrikto Das","M","Philosophy","35

Query OK, 1 row affected (0.00 sec)

mysql> insert into EMPLOYEE values("E06","Sohom Mishra","M","Psychology","37

Query OK, 1 row affected (0.01 sec)

mysql> insert into EMPLOYEE values("E07","Mainak Dube","M","Litrature","1990

mysql> insert into EMPLOYEE values("E08","Suvasree Islam","F","physiology","

Query OK, 1 row affected (0.00 sec)

mysql> insert into EMPLOYEE values("E09","Chandrika Ash","F","Orthopedic","3

Query OK, 1 row affected (0.00 sec)

mysql> insert into EMPLOYEE values("E10","Bhumika Sammadar","F","Cardiologis

Query OK, 1 row affected (0.01 sec)

SCREENSHOT OF THE SOURCE CODE: