PM SHRI JAWAHAR NAVODAYA VIDYALAYA BANAVASI: KURNOOL
INFORMATIC PRACTICES
SUBJECT CODE: 065 TIME: 3 Hrs
DATE: MARKS: 30
Practical‟s: (Python 11 marks + MySQL 7 Marks) 18 M
1. Python Programming Language
2. MySQL and performing Queries
3. Practical File 14 Python Programmes + 14 SQL Queries 7M
4. Viva-Voce 5M
1. Consider the following MOVIE database and answer the SQL queries
based on it.
CREATE TABLE Movies ( MovieID INT PRIMARY KEY, MovieName VARCHAR(50),
Category VARCHAR(20), ReleaseDate DATE, ProductionCost INT, BusinessCost INT );
INSERT INTO Movies (MovieID, MovieName, Category, ReleaseDate, ProductionCost,
BusinessCost) VALUES
(001, 'Hindi_Movie', 'Musical', '2018-04-23', 124500, 130000),
(002, 'Tamil_Movie', 'Action', '2016-05-17', 112000, 118000),
(003, 'English_Movie', 'Horror', '2017-08-06', 245000, 360000),
(004, 'Bengali_Movie', 'Adventure', '2017-01-04', 72000, 100000),
(005, 'Telugu_Movie', 'Action', NULL, 100000, NULL),
(006, 'Punjabi_Movie', 'Comedy', NULL, 30500, NULL);
(a) Retrieve movies information without mentioning their column names.
Ans: SELECT * FROM MOVIE;
(b) List business done by the movies showing only MovieID, MovieName and
BusinessCost.
Ans: SELECT MovieID, MovieName, BusinessCost FROM MOVIES WHERE
BusinessCost <> 0;
(c) List the different categories of movies
Ans: SELECT DISTINCT Category FROM MOVIES;
(d) List all movies with ProductionCost greater than 80,000 and less than 1,25000
showing ID, Name and ProductionCost.
Ans: SELECT MovieID, MovieName, ProductionCost from MOVIES WHERE
ProductionCost BETWEEN 80000 AND 125000;
(f) List all movies which fall in the category of Comedy or Action.
Ans: SELECT * FROM Movies WHERE Category IN ('Action', 'Comedy');
(g) List the movies which have not been released yet.
Ans: SELECT * FROM MOVIES WHERE ReleaseDate IS NULL;
2. Suppose your school management has decided to conduct cricket matches
between students of class XI and Class XII. Students of each class are asked to join
any one of the four teams — Team Titan, Team Rockers, Team Magnet and Team
Hurricane. During summer vacations, various matches will be conducted between
these teams. Help your sports teacher to do the following:
a Create a database “Sports”.
Ans: CREATE DATABASE Sports;
b Create a table “TEAM” with following considerations:
i) It should have a column TeamID for storing an integer value between
1 to 9, which refers to unique identification of a team.
ii) Each TeamID shouldhave its associated name (TeamName), which
should be a string of length not less than 10 characters.
Ans: CREATE TABLE TEAM (TeamID INT CHECK (TeamID BETWEEN 1
AND 9),TeamName VARCHAR(20) CHECK ( LENGTH (TeamName) > 10));
c Using table level constraint, make TeamID as primary key.
Ans: ALTER TABLE TEAM ADD PRIMARY KEY (TeamID);
d Show the structure of the table TEAM using SQL command.
Ans: DESC TEAM; or DESCRIBE TEAM;
f Show the contents of the table TEAM.
Ans: SELECT * FROM TEAM;
3.A shop called Wonderful Garments that sells school uniforms maintain a database
SCHOOL_UNIFORM as shown below. It consisted of two relations — UNIFORM and
PRICE. They made UniformCode as the primary key for UNIFORM relation. Further,
they used UniformCode and Size as composite keys for PRICE relation. By analysing
the database schema and database state, specify SQL queries to rectify the following
anomalies.
CREATE TABLE uniform (UCode INT PRIMARY KEY,UName VARCHAR(50),UColor
VARCHAR(20));
CREATE TABLE PRICE (UCode INT,Size VARCHAR(20),Price DECIMAL(10, 2),FOREIGN
KEY (UCode) REFERENCES UNIFORM(UCode));
INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (1, 'Shirt', 'White');
INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (2, 'Pant', 'Grey');
INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (3, 'Skirt', 'Grey');
INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (4, 'Tie', 'Blue');
INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (5, 'Socks', 'Blue');
INSERT INTO UNIFORM (UCode, UName, UColor) VALUES (6, 'Belt', 'Blue');
INSERT INTO Price (UCode, Size, Price) VALUES (1, 'M', 500);
INSERT INTO Price (UCode, Size, Price) VALUES (1, 'L', 580);
INSERT INTO Price (UCode, Size, Price) VALUES (1, 'XL', 620);
INSERT INTO Price (UCode, Size, Price) VALUES (2, 'M', 810);
INSERT INTO Price (UCode, Size, Price) VALUES (2, 'L', 890);
INSERT INTO Price (UCode, Size, Price) VALUES (2, 'XL', 940);
INSERT INTO Price (UCode, Size, Price) VALUES (3, 'M', 770);
INSERT INTO Price (UCode, Size, Price) VALUES (3, 'L', 830);
INSERT INTO Price (UCode, Size, Price) VALUES (3, 'XL', 910);
INSERT INTO Price (UCode, Size, Price) VALUES (4, 'S', 150);
INSERT INTO Price (UCode, Size, Price) VALUES (4, 'L', 170);
INSERT INTO Price (UCode, Size, Price) VALUES (5, 'S', 180);
INSERT INTO Price (UCode, Size, Price) VALUES (5, 'L', 210);
INSERT INTO Price (UCode, Size, Price) VALUES (6, 'M', 110);
INSERT INTO Price (UCode, Size, Price) VALUES (6, 'L', 140);
INSERT INTO Price (UCode, Size, Price) VALUES (6, 'XL', 160);
(a) The PRICE relation has an attribute named Price. In order to avoid
confusion, write SQL query to change the name of the relation PRICE to
COST.
Ans: ALTER TABLE PRICE RENAME TO COST;
(b) M/S Wonderful Garments also keeps handkerchiefs of red color, medium
size of 100 each. Insert this record in COST table.
Ans: INSERT INTO UNIFORM VALUES(7,'Handkerchief','Red');
INSERT INTO COST VALUES (7, „M‟, 100);
(c) When you used the above query to insert data, you were able to enter the
values for handkerchief without entering its details in the UNIFORM relation.
Make a provision so that the data can be entered in COST table only if it is
already there in UNIFROM table.
Ans:ALTER TABLE COST ADD FOREIGN KEY (UCode) REFERENCES
UNIFORM (UCode);
(d) Further, you should be able to assign a new UCode to an item only if it
has a valid Uname. Write a query to add appropriate constraint to the
SCHOOL_ UNIFORM database.
Ans: ALTER TABLE UNIFORM ADD UNIQUE (UName);
4. Create table of your choice using constraints using below rno,regno,sname,gender,marks
and insert values up to 5. Use alter and update command of your choice.
5. Create DEPT and EMP table using Foreign key
CREATE TABLE DEPT(DeptId VARCHAR(50) PRIMARY KEY,DeptName VARCHAR(255)
NOT NULL, Location VARCHAR(255) NOT NULL);
CREATE TABLE EMP(EmpNo INT PRIMARY KEY,Ename VARCHAR(255) NOT NULL,
Salary int NOT NULL, Bonus int not null, DeptId VARCHAR(50),FOREIGN KEY (DeptId)
REFERENCES DEPT(DeptId));
From the above table use RELATIONAL, LOGICAL, MEMBERSHIP operators
From the above table use ORDER BY & LIKE OPERATORS
6. In a database there are two tables: Write MYSQL queries
Table : Doctors
(i) To display the list of all doctors whose NoofOpdDays are more than 3
(ii) To display total no of different departments from Patients table.
CREATE TABLE Doctor (
DocID INT PRIMARY KEY,
DocName VARCHAR(50),
Department VARCHAR(50),
NoofOpdDays INT
);
CREATE TABLE Patient (
PatNo INT PRIMARY KEY,
PatName VARCHAR(50),
Department VARCHAR(50),
DocID INT,
FOREIGN KEY (DocID) REFERENCES Doctor(DocID)
);
INSERT INTO Doctor(DocID,DocName,Department,NoofOpdDays)VALUES(101,'JKMishra','Ortho', 3);
INSERT INTO Doctor (DocID, DocName, Department, NoofOpdDays) VALUES (102, 'Mahesh Tripathi', 'ENT',
4);
INSERT INTO Doctor (DocID, DocName, Department, NoofOpdDays) VALUES (103, 'Ravi Kumar', 'Neuro', 5);
INSERT INTO Doctor (DocID, DocName, Department, NoofOpdDays) VALUES (104, 'Mukesh Jain', 'Physio',
3);
INSERT INTO Patient (PatNo, PatName, Department, DocID) VALUES (1, 'Payal', 'ENT', 102);
INSERT INTO Patient (PatNo, PatName, Department, DocID) VALUES (2, 'Naveen', 'Ortho', 101);
INSERT INTO Patient (PatNo, PatName, Department, DocID) VALUES (3, 'Rakesh', 'Neuro', 103);
INSERT INTO Patient (PatNo, PatName, Department, DocID) VALUES (4, 'Atul', 'Physio', 104);
Queries:
(i) To display the list of all doctors whose NoofOpdDays are more than 3
select * from Doctor where NoofOpdDays>3;
(iv) To display total no of different departments from Patients table.
select count(distinct Department) from Patient;
7. Consider the following EMPLOYEE table write MYSQL command
CREATE TABLE EMPLOYEE ( EMPNO INT, ENAME VARCHAR(50), DEPT VARCHAR(50),
SALARY INT, COMM INT );
NSERT INTO EMPLOYEE (EMPNO, ENAME, DEPT, SALARY, COMM) VALUES (1, 'ANKIT',
'HR', 20000, 1200);
QUERIES
To display the details of all SALES dept employee who are earning salary
more than 20000
Select * from employee where dept='Sales' and salary>20000;
Change the salary of NITIN from 18000 to 20000
update employee set salary=20000 where ename='NITIN';
Select ENAME,DEPT from Employee where Dept in(„HR’,’ACCOUNTS’)
8. Considering the Visitor table data, write the MySQL query
CREATE TABLE Visitor (VisitorID INT,VisitorName VARCHAR(50),
Gender CHAR(1),ComingFrom VARCHAR(50), AmountPaid INT);
INSERT INTO Visitor (VisitorID, VisitorName, Gender, ComingFrom, AmountPaid) VALUES
(1, 'Suman', 'F', 'Kanpur', 2500),(2, 'Indu', 'F', 'Lucknow', 3000),(3, 'Rachana', 'F', 'Haryana',
2000),(4, 'Vikram', 'M', 'Kanpur', 4000),(5, 'Rajesh', 'M', 'Kanpur', 3000),
(6, 'Suresh', 'M', 'Allahabad', 3600),(7, 'Dinesh', 'M', 'Lucknow', 3000),
(8, 'Shikha', 'F', 'Varanasi', 5000);
(i) Write a query to display VisitorName, Coming From details of Female Visitors
with
Amount Paid more than 3000
Select VisitorName,ComingFrom from Visitor where Gender='F' and
AmountPaid>3000;
(ii) Write a query to display all coming from location uniquely
Select distinct ComingFrom from Visitor;
(iii) Write a query to display all details of visitors in order of their AmountPaid
from
highest to lowest
Select * from visitor order by AmountPaid desc;
(iv) Write a query to display only Males
Select VisitorName from Visitor where Gender='M';