Computer Science Practical File
Instructions:
• Create Computer Science Practical File Cover page
• Write Questions as comment, then do coding and take screenshot of output . Compile
it in a word file .
• Bring the Practical File in Printed form and before printing send the pdf for approval
to me.
Do 5 Questions on Functions
1. Passing string to function
2. Passing Dictionary to a function
3. Passing List to a Function
4. Passing Tuple to a Function
5. Showing Default arguments concept.
Note : Take any question covered in class or from back exercise on the above topics
Database Questions
1. Create a Database by your Name
2. Display your database created in database list
3. Open your database
create the following tables in database & also display structure of each table after creation.
1. Write a MySQL query to create the table ‘Employee’ with the following structure and constraint. Table:
Employee
Column_Name DataType(size) Constraint
Emp_ID Int(20) Primary key
Emp_Name char(100) Not Null
Salary int(20) Not Null
Department char(30)
Age int(15) Not Null
Address Varchar(200) Unique
1. Add 5 records in it.
2. Display all Records on screen.
2. Write a MySQL query to create the table ‘Student’ with the following structure and constraint.
Table: Student
Column_Name DataType(size) Constraint
Student_ID varchar(20) Primary key
Student_Name char(80) Not Null
Gender char(20) Not Null
Class varchar(30)
Age int(20) Not Null
Address Varchar(150) Unique
Phone Int(15) Not Null, Unique
1. Add 5 records in it.
2. Display all Records on screen.
Q3 Anmol maintains a database of medicines for his pharmacy using SQL to store the data. The structure of
the table PHARMA for the purpose is as follows:
● Name of the table−PHARMA
● The attributes of PHARMA are as follows:
MID − numeric
MNAME − character of size 20
PRICE − numeric
UNITS − numeric EXPIRY − date
Create the table PHARMA Add the records as given :
Table: PHARMA
MID MNAME PRICE UNITS EXPIRY
M1 PARACETAMOL 112 120 2022−12−25
M2 CETIRIZINE 16 125 2022−10−12
M3 METFORMIN 54 150 2022−05−23
M4 VITAMIN B−6 96 120 2022−07−01
M5 VITAMIN D3 125 150 2022−06−30
M6 TELMISARTAN 22 115 2022−02−25
Do the following queries also show their output
I Display all the records of PHARMA table.
2. Display Medicine name and their Price
3. Display the medicine name where price is between 50-100
4. Display medicine name and their expiry dates where units are above 100.
5.Display the structure of the table PHARMA
6 Calculate the total unit price values of each medicine
7 Display the list of medicines whose name starts with “V”.
8 Anmol has received a new medicine to be added to his stock, but for which he does not know the
number of UNITS. So he decides to add the medicine without its value for UNITS. The rest of the
values are as follows:
MID MNAME PRICE EXPIRY
M7 SUCRALFATE 17 2022−03−20
Write the SQL command that Anmol should execute to perform the required task.
Q4 A garment store is considering maintaining its inventory using SQL to store the data. as a
database administrator, Mr.Rohit has decided that:
Name of the database − STORE
Name of the table − GARMENT
The attributes of GARMENT table are as follows:
GCODE − numeric
DESCRIPTION − character of size 50
PRICE − numeric
FCODE − varchar of size 10
Table: GARMENT
GCODE DESCRIPTION PRICE FCODE
10023 JEANS 1150 F01
10001 SHIRT 750 F02
10044 SHORTS 600 F05
10005 TIE 400 F04
10002 JACKET 5000 F01
10022 SOCKS 150 NULL
1. Write SQL query to create database STORE.
2. Write SQL query to display the list of available databases.
3. Write SQL query to use database STORE.
4. Write SQL query to display the list of available tables in database STORE.
5. Write SQL query to create table GARMENT with aforementioned attributes
and tuples.
Obtain the output of the following SQL queries based on the data given in table
GARMENT:
(i) SELECT MAX(PRICE), MIN(PRICE) FROM GARMENT;
(ii) SELECT GCODE, DESCRIPTION FROM GARMENT;
(iii) SELECT FCODE,GCODE FROM GARMENT WHERE PRICE BETWEEN 500 AND
800;
(iv) SELECT * FROM GARMENT WHERE DESCRIPTION NOT IN (‘JEANS’,’TIE’);
(v) SELECT GCODE FROM GARMENT WHERE DESCRIPTION LIKE ‘%S%’;
(vi) SELECT GCODE,PRICE FROM GARMENT WHERE DESCRIPTION LIKE ‘_ _ _’;
SELECT DISTINCT FCODE FROM GARMENT;
(vii)
SELECT SUM(PRICE) FROM GARMENT;
(viii)
(ix)SELECT * FROM GARMENT WHERE DESCRIPTION LIKE ‘%T%’ AND
FCODE!=’F02’;
(x) SELECT * FROM GARMENT ORDER BY PRICE DESC;
(xi) SELECT PRICE*10 FROM GARMENT;
(xii) SELECT COUNT(DISTINCT FCODE) FROM GARMENT;
(xiii) SELECT * FROM GARMENT WHERE FCODE NOT IN (‘F01’,’F02’) AND PRICE<500;
(xiv) SELECT GCODE, PRICE FROM GARMENT WHERE FCODE IS NULL;
(xv) SELECT * FROM GARMENT WHERE PRICE >500 AND PRICE <1000;
Note : Take the screenshot of each command outputs also
Q5The IT Company XYZ has asked their IT manager Ms. Preeti to maintain the data of all the
employees in two tables EMPLOYEE and DEPT. Ms. Preeti has created two tables EMPLOYEE and
DEPT. She entered 6 rows in the ‘EMPLOYEE’ table and 5 rows in the ‘DEPT’ table.
Table: DEPT
D_CODE D_NAME CITY
D001 INFRASTRUCTURE DELHI
D002 MARKETING DELHI
D003 MEDIA MUMBAI
D005 FINANCE KOLKATA
D004 HUMAN RESOURCE MUMBAI
Table: EMPLOYEE
E_NO NAME DOJ DOB GENDE D_CODE Salary
R
1001 Vinay 2013−09− 1991−09− MALE D001 250000
02 01
1002 Ruby 2012−12− 1990−12− FEMALE D003 270000
11 15
1003 Anuj 2013−02− 1987−09− MALE D005 240000
03 04
1007 Sunny 2014−01− 1988−10− MALE D004 250000
17 19
1004 Rohit 2012−12− 1986−11− MALE D001 270000
09 14
1005 Preeti 2013−11− 1989−03− FEMALE D002 NULL
18 31
Note: DOJ refers to the date of joining and DOB refers to the date of Birth of employees. Based on the
above data, answer the following questions:
1. Identify the column which can be considered as the primary key in the ‘EMPLOYEE’ table.
2. Identify the column which can be considered as primary key in the DEPT table
3. What is the degree and cardinality of the’ EMPLOYEE’ table?
4. What is the degree and cardinality of the’ DEPT’ table?
Write SQL queries for the following:
1) Insert two new rows in the Employee table with the following data:
1006 Rahul 2019−11−06 1992−01−04 MALE D003 156000
1008 Sonam 2022−01−06 1991−04−06 FEMALE D005 167000
2) To display E_NO, NAME, GENDER from the table EMPLOYEE in descending order
of E_NO.
3) To display the NAME of all the ‘FEMALE’ employees from the table EMPLOYEE.
4) To display the E_NO and NAME of those employees from the table EMPLOYEE
who are born between ‘1987− 01−01’ and ‘1991−12−01’.
5) To display the NAME and CITY of those employees whose DEPARTMENT is
either ‘MEDIA’ or ‘FINANCE’.
6) To display the NAME of those employees whose name starts with the letter ‘R’.
7) To display the ‘NAME’ of those employees whose name contains the letter ‘n’.
8) To display the ‘NAME’ of those employees whose name has exactly 5 letters.
9) To display D_NAME and CITY from table DEPT where D_NAME ends with the
letter ‘G’ and CITY is ‘DELHI’.
10) To display the maximum SALARY of the ‘EMPLOYEE’ table.
11) To delete data of all those employees whose age is less than 25.
12) To update SALARY to 230000 of those employees whose ‘E_NO’ is 1004.
13) To change the sequence of the DOB column in the employee table and move it
before the DOJ column.
14) To add a new column MOBILE int(20) before column SALARY in the employee
table.
15) To set SALARY to 300000 of all those employees whose age is NULL.
16) To Increase the salary of all employees by 30000 in the EMPLOYEE table.
17) To display the average SALARY of the EMPLOYEE table.
18) To display the names of employees who have a SALARY of more than 200000 in
ascending order of NAME.
19) To display department−wise average salary of employees.
20) To display the total number of departments in XYZ company.
21) To delete data of all the employees whose D_CODE is not ‘D001’.
22) To display E_NO, NAME and SALARY of all those employees who don’t live in
‘DELHI’.
23) To change column name CITY to D_CITY in the DEPT table.
24) To delete the EMPLOYEE table.
25 )To delete the ‘D_NAME’ column from the DEPT table
Note : Take the screenshot of each command outputs also
Q6 Write queries for (i) to (iv) and find ouputs for SQL queries (v) to (viii), which are based on
the tables.
Table :
VEHICLE
Note:
• PERKS is Freight Charges per kilometer.
• Km is kilometers Travelled
• NOP is number of passangers travelled in vechicle.
1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of
CNO.
2. To display the CNAME of all customers from the table TRAVEL who are travelling by
vechicle with code Vo1 or Vo2
3. To display the CNO and CNAME of those customers from the table TRAVEL who
travelled between ‘2015-1231’ and ‘2015-05-01’.
4. To display all the details from table TRAVEL for the customers, who have travel
distacne more than 120 KM in ascending order of NOE
5. SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT (*) > 1;
6. SELECT DISTINCT VCODE FROM TRAVEL :
7. SELECT A.VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A.
VCODE = B. VCODE and KM < 90;
8. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE
AND A. VCODE ‘V05’;
Q7 Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a table
COMPANY and CUSTOMER.
1. To display those company name which are having prize less than 30000.
2. To display the name of the companies in reverse alphabetical order.
3. To increase the prize by 1000 for those customer whose name starts with „S?
4. To add one more column totalprice with decimal] 10,2) to the table customer
5. SELECT COUNT(*) , CITY FROM COMPANY GROUP BY CITY;
6. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;
7. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;
8. SELECT PRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE
COMPANY. CID=CUSTOMER.CID AND PRODUCTNAME=”MOBILE”;
