SQL Queries and Python Programming Exercises, Lecture notes of Computer science

Download SQL Queries and Python Programming Exercises and more Lecture notes Computer science in PDF only on Docsity!

KENDRIYA VIDYALAYA SANGATHAN,

MUMBAI REGION

हमारे संरक्ष क

OUR PATRONS

श्र ीमती निनि पांडे , आईआईएस Mrs. NIDHI PANDEY, IIS आयुक्त COMMISSIONER केंद्रीय निद्य ालय संगठि KENDRIYA VIDYALAYA SANGATHAN श्रीमती सोिा सेठ Mrs. SONA SETH उपायुक्त DEPUTY COMMISSIONER के. नि. सं. मुंबई संभाग KVS MUMBAI REGION श्री समाज िी. जोगलेकर Mr. S V JOGLEKAR सहायक आयुक्त ASSISTANT COMMISSIONER के. नि. सं. मुंबई संभाग KVS MUMBAI REGION श्री कमल नकशोर स् िामी Mr. KAMAL KISHOR SWAMI पाठ्यक्र म निर्देशक COURSE DIRECTOR प्राचायय के नि चंद्र पुर PRINCIPAL KV CHANDRAPUR श्री नर्दपक पी िारजुरकर Mr. DIPAK P WARJURKAR संसािक RESOURCE PERSON स् िातकोत्त र नशक्ष क संगणक निज्ञ ाि PGT CS के नि चंद्र पुर KV WCL CHANDRAPUR श्र ी चंद्र कांत MR. CHANDRAKANT संसािक RESOURCE PERSON स् िातकोत्त र नशक्ष क संगणक निज्ञ ाि PGT CS के नि कोलाबा िं 2 KV NO. 2 COLABA

CONTENT DEVELOPMENT TEAM SNO NAME OF TOPIC NAME OF CONTRIBUTOR

NAME OF KV

1 UNIT-I REVISION, FUNCTION MR. RAVINDRA

RAMESH

CHARJAN

KV AFS OJHAR

2 UNIT-II COMPUTER NETWORKS MR. VISHANT D

KHOBRAGADE

KV VSN NAGPUR

3 UNIT-I INTRODUCTION TO

FILES, TEXT FILES, BINARY

FILES,CSV FILES, DATA

STRUCTURE

MR. ANOOP

MATHUR

KV NO. 1 VASCO-

DA-GAMA, GOA

4 UNIT-III DATABASE

MANAGEMENT

MR. ARUN

CHAUDHARI

REVIEW TEAM OF STUDY MATERIAL GROUP CONTENTS FOR REVIEW GROUP- 1 UNIT-I REVISION, FUNCTION

MR. PRAMOD TUPE

MR. SATYANARAYAN MEENA

MR. SAMRAT KOHLI

MR. GULSHAN KUMAR HANS

MR. RAJ KUMAR BOKALIA

MR. ANOOP MATHUR

GROUP- 2

UNIT-II COMPUTER NETWORKS

MRS. APARNA ASHOK DHIRDE

MR. DEEP PRAKASH CHAUDHARI

MR. VIKASH KUMAR YADAV

MR. SANTOSH WATTAMWAR

MR. ATUL THAKRE

MRS. KIRAN SONANE

MRS. SWATI VISHWAKARMA

MS. SHIVALI TIWARI

GROUP- 3

UNIT-I INTRODUCTION TO FILES, TEXT

FILES, BINARY FILES,CSV FILES, DATA

STRUCTURE

MR. NITIN UPADHYAY

MRS. SHIKHA SHAMBHARKAR

MRS. POOJA RAWAT

MR. RAJEEV KUMAR PRITHIANI

MR. SANDEEP NILKANTH PATIL

MR. RAJENDRA GAVHALE

MRS. ARPITA DAS

MR. VISHANT KHOBRAGADE

GROUP- 4

UNIT-III DATABASE MANAGEMENT

MR. DIPAK P WARJURKAR

MR. NITIN ARSE

MR. SARAS MOHAN SHRIVASTAV

MR. RAVINDRA RAMESH CHARJAN

MR. NAVEEN KUMAR GAYARI

MR. NILESH MOHURE

MR. RAJKAMAL ALARIA

MRS. POOJA AGRAWAL

INDEX SNO NAME OF TOPIC/CONTENT PAGE NO. 1 SYLLABUS 2022 - 23 2 BLUE PRINT 3 UNIT - 1 REVISION, FUNCTION, INTRODUCTION TO FILES, TEXT FILES, BINARY FILES,CSV FILES, DATA STRUCTURE 4 UNIT-II COMPUTER NETWORKS 5 UNIT-III DATABASE MANAGEMENT 6 SAMPLE QUESTION PAPERS WITH ANSWER KEY ( COMPUTER SCIENCE) 7 SAMPLE QUESTION PAPERS WITH ANSWER KEY ( IP)

Computer Science

CLASS-XII

Code No. 083

1. Prerequisites

Computer Science- Class XI

2. Learning Outcomes

Student should be able to a) apply the concept of function. b) explain and use the concept of file handling. c) use basic data structure: Stacks d) explain basics of computer networks. e) use Database concepts, SQL along with connectivity between Python and SQL.

3. Distribution of Marks:

Unit No. Unit Name Marks Periods Theory Practical I Computational Thinking and Programming - 2 40 70 50 II Computer Networks 10 15 --- III Database Management 20 25 20 Total 70 110 70

4. Unit wise Syllabus

Unit I: Computational Thinking and Programming – 2

• Revision of Python topics covered in Class XI.
• Functions: types of function (built-in functions, functions defined in module, user defined functions), creating user defined function, arguments and parameters, default parameters, positional parameters, function returning value(s), flow of execution, scope of a variable (global scope, local scope)
• Introduction to files, types of files (Text file, Binary file, CSV file), relative and absolute paths
• Text file: opening a text file, text file open modes (r, r+, w, w+, a, a+), closing a text file, opening a file using with clause, writing/appending data to a text file using write() and writelines(), reading from a text file using read(), readline() and readlines(), seek and tell methods, manipulation of data in a text file

• Interface of python with an SQL database: connecting SQL with Python, performing insert, update, delete queries using cursor, display data by using fetchone(), fetchall(), rowcount, creating database connectivity applications

5. Practical

S.No . Unit Name Marks (Total=30) 1 Lab Test:

1. Python program (60% logic + 20% documentation + 20% code quality)

2. A stub program with Python SQL connectivity must be provided with blanks (4 blanks) to be filled by the student with the desired SQL query.

2 Report^ file:

• Minimum 15 Python programs.
• SQL Queries – Minimum 5 sets using one table / two tables.
• Minimum 4 programs based on Python - SQL connectivity

3 Project (using concepts learnt in Classes 11 and 12) 8 4 Viva voce 3

6. Suggested Practical List:

Python Programming

● Read a text file line by line and display each word separated by a #. ● Read a text file and display the number of vowels/consonants/uppercase/lowercase characters in the file. ● Remove all the lines that contain the character 'a' in a file and write it to another file. ● Create a binary file with name and roll number. Search for a given roll number and display the name, if not found display appropriate message. ● Create a binary file with roll number, name and marks. Input a roll number and update the marks. ● Write a random number generator that generates random numbers between 1 and 6 (simulates a dice). ● Write a Python program to implement a stack using list. ● Create a CSV file by entering user-id and password, read and search the password for given user- id.

Database Management

● Create a student table and insert data. Implement the following SQL commands on the student table: o ALTER table to add new attributes / modify data type / drop attribute o UPDATE table to modify data o ORDER By to display data in ascending / descending order o DELETE to remove tuple(s) o GROUP BY and find the min, max, sum, count and average ● Similar exercise may be framed for other cases. ● Integrate SQL with Python by importing suitable module.

7. Suggested Reading Material

• NCERT Textbook for COMPUTER SCIENCE (Class XII)
• Support Materials on the CBSE website.

8. Project

The aim of the class project is to create something that is tangible and useful using Python file handling/ Python-SQL connectivity. This should be done in groups of two to three students and should be started by students at least 6 months before the submission deadline. The aim here is to find a real world problem that is worthwhile to solve. Students are encouraged to visit local businesses and ask them about the problems that they are facing. For example, if a business is finding it hard to create invoices for filing GST claims, then students can do a project that takes the raw data (list of transactions), groups the transactions by category, accounts for the GST tax rates, and creates invoices in the appropriate format. Students can be extremely creative here. They can use a wide variety of Python libraries to create user friendly applications such as games, software for their school, software for their disabled fellow students, and mobile applications, of course to do some of these projects, some additional learning is required; this should be encouraged. Students should know how to teach themselves. The students should be sensitised to avoid plagiarism and violations of copyright issues while working on projects. Teachers should take necessary measures for this.

Revision Tour of python class XI

Basics of Python

Observe the program first then memorize the definitions:

Python keyword/ reserve words

Keywords are reserve words. Each keyword has a specific meaning to the Python interpreter, and we can use a keyword in our program only for the purpose for which it has been defined. As Python is case sensitive, keywords must be written exactly.

Identifiers

In programming languages, identifiers are names used to identify (Name) a variable, function, or other entities in a program. The rules for naming an identifier in Python are as follows: ➔ The name should begin with an uppercase or a lowercase alphabet or an underscore sign (). ➔ This may be followed by any combination of characters a–z, A–Z, 0–9 or underscore () Thus, an identifier cannot start with a digit. ➔ It can be of any length. (However, it is preferred to keep it short and meaningful). ➔ It should not be a keyword or reserve word. ➔ We cannot use special symbols like !, @, #, $, %, etc., in identifiers.

Variables

A variable in a program is uniquely identified by a name (identifier). Variable in Python refers to an object — an item or element that is stored in the memory. Value of a variable can be a string (e.g., ‘b’, ‘Global Citizen’), numeric (e.g., 345) or any combination of alphanumeric characters (CD67). In Python we can use an assignment statement to create new variables and assign specific values to them.

Comments

Comments are used to add a remark or a note in the source code. Comments are not executed by interpreter. Comments in python can be created as: ➔ for single line comment use # (hash symbol) ➔ for multi line comment use ‘‘‘ text ’’’ (in triple quotes)

Data Types

Every value belongs to a specific data type in Python. Data type identifies the type of data values a variable can hold and the operations that can be performed on that data.

Number

Number data type stores numerical values only. It is further classified into three different types: int, float and complex. Try the following statements on system in shell mode and observe the output: num1 = 10 #(integer positive value ) type(num1) num2 = - 1210 #(integer negative value) type(num2) float1 = - 1921.9 #(float1 variable contain decimal value so it contains float value) type(float1) float2 = - 9.810*2 #(float 2 variable contain decimal value so it contains float value) print(float2, type(float2)) var2 = - 3+7.2j #(var2 variable contain complex value) print(var2, type(var2)) Boolean var3= True # (var3 variable contain Boolean Value) print(type(var3)) # print type Bool Variables of simple data types like int, float, boolean, etc. hold single values. But such variables are not useful to hold a long list of information, for example, names of the months in a year, names of students in a class, names and numbers in a phone book or the list of artefacts in a museum. For this, Python provides data types like tuples, lists, dictionaries and sets. Sequences can used as datatype in python

Output: (10, 20, "KV", 5.5, 'a') (D) Dictionary Dictionary in Python holds data items in key : value pairs. Items in a dictionary are enclosed in curly braces { }. Every key is separated from its value using a colon (:) sign. The key : value pairs of a dictionary can be accessed using the key. The keys are usually strings and their values can be any data type. In order to access any value in the dictionary, we have to specify its key in square brackets [ ]. #create a dictionary dict1 = {'Fruit':'Apple', 1 :'Monday', 'Price Rs':120} print(dict1) output: {'Fruit': 'Apple', 1: ‘Monday’,'Price Rs': 120} print(dict1['Price Rs']) output: 120 print(dict1[1]) output:’ Monday’ (E) None None is a special data type with a single value. It is used to signify the absence of value in a situation. None supports no special operations, and it is neither False nor 0 (zero). myVar = None print(type(myVar)) <class 'NoneType'> print(myVar) Mutable and Immutable Data Types Variables whose values can be changed after they are created and assigned without changing their memory location are called mutable. Variables whose values cannot be changed after they are created and assigned or upon changing values their memory location is changed, are called immutable. When an attempt is made to update the value of an immutable variable, the old variable is destroyed and a new variable is created by the same name in new memory location. Exercise: Define a variable by assigning a value, find and note its ID, change the value and again find its ID, now observe the difference and do it for different data types. Precedence of Operators Evaluation of the expression is based on precedence of operators. When an expression contains different kinds of operators, precedence determines which operator should be applied first. Higher precedence operator is evaluated before the lower precedence operator. (Simply apply BODMAS rules) Order of Precedence (higher to lower)

1 ** Exponentiation (raised to the power) 2 ~ ,+, - Complement, unary plus and unary minus 3 * ,/, %, // Multiply, divide, modulo and floor division 4 +, - Addition and subtraction 5 <= ,< ,> ,>= Relational operators 6 == ,!= Equality operators 7 =, %=, /=, //=, - =, +=, *=, = Assignment operators 8 is is not Identity operators 9 in, not in Membership operators 10 not, and, or Logical operators *** For operators with equal precedence, the expression is evaluated from left to right except ** which is executed from right to left.

is transferred to the statement immediately following the body of while loop. If the condition of the while loop is initially false, the body is not executed even once. Syntax of while Loop while test_condition: body of while Break and Continue Statement In certain situations, when some particular condition occurs, we may want to exit from a loop (come out of the loop forever) or skip some statements of the loop before continuing further in the loop. These requirements can be achieved by using break and continue statements, respectively.

STRINGS IN PYTHON

Python strings are characters enclosed in quotes of any type – single quotation marks, double quotation marks and triple quotation marks. An empty string is a string that has 0 characters. Python strings are immutable. Strings are sequence of characters, where each character has a unique position-id/index. The indexes of a string begin from 0 to (length - 1) in forward direction and - 1,-2,-3,….,-length in backward direction. STRING SLICES In Python, the term ‘string slice’ refers to a part of the string, where strings are sliced using a range of indices. That is, for a string say name, if we give name[n:m] where n and m are integers and legal indices, Python will return a slice of the string by returning the characters falling between indices n and m starting at n, n+1, n+2, … till m-1. 0 1 2 3 4 5 6 word a m a z i n g

• 7 - 6 - 5 - 4 - 3 - 2 - 1 Then, word[0:7] will give ‘amazing’ word[0:3] will give ‘ama’ word[2:5] will give ‘azi’ word[-7:-3] will give ‘amaz’ word[-5:-1] will give ‘azin’ In a string slice, you give the slicing range in the form [<begin-index>:]. If, however, you skip of the begin- index or last, Python will consider the limits of the string, i.e., for missing begin index, it will consider 0 (the first index) and for mission last value, it will consider length of the string. Consider the following examples to understand this: word[:7] will give ‘amazing’ word[:5] will give ‘amazi’ word[3:] will give ‘zing’ word[5:] will give ‘ng’ Note: Using the same string slicing technique, you will find that for any index n, s[:n]+s[n:] will give you original string s. STRING FUNCTIONS AND METHODS while : statement 1 if : break statement 2 statement 3 statement 4 statement 5 for in : statement 1 if : break statement 2 statement 3 statement 4 statement 5

Every string object that you create in Python is actually an instance of String class. The string manipulation methods that are being discussed below can be applied to string as per following syntax: .()

1. string.capitalize(): Returns a copy of the string with its first character capitalized Exmple: ‘true’.capitalize() will return ‘True’ ‘i love my India’.capitalize() will return ‘I love my India’ 2. string.title(): Returns a copy of the string with first character of each work capitalized. Example: ‘true’.title() will return ‘True’ ‘i love my india’.capitalize() will return ‘I Love My India’ 3. string.upper(): Returns a copy of the string converted to uppercase. Examples: string.upper() will return ‘HELLO’ string2.upper() will return ‘THERE’ string3.upper() will return ‘GOLDY’ 4. string.lower(): Returns a copy of the string converted to lowercase. Examples: string.lower() will return ‘hello’ string2.lower() will return ‘there’ string3.lower() will return ‘goldy’ 5. string.count(str): Returns the count of an string in the given string. Examples: ‘I love my india’.count(‘i’) will return 2 ‘it goes as – ringa ringa roses’.count(‘ringa’) will return 2 6. string.find(sub[,start,end]): Returns the lowest index in the string where the substring sub is found within the slice range of start and end. Returns - 1 if sub is not found. Example: string = ‘it goes as – ringa ringa roses’ sub = ‘ringa’ string.find(sub) will return 13 string.find(sub,15,22) will return - 1 string.find(sub,15,25) will return 19 7. string.index(str): Returns the lowest index in the sting where the substring is found. Example: ‘I love my India’.index(‘o) will return 3 ‘I love my India’.index(‘my’) will return 7 8. string.isalnum(): Returns True if the characters in the string are alphanumeric (alphabets or numbers) and there is at least one character, False otherwise. Example: string =”abc123” string2 = ‘hello’ string3 = ‘12345’ string4 = ‘ ’ string.isalnum() will return True string2.isalnum() will return True string3.isalnum() will return True string4.isalnum() will return False 9. string.islower(): Returns True if all cased characters in the string are lowercase. Examples: string = ‘hello’ string2 = ‘THERE’ string3 = ‘Goldy’ string.islower() will return True string2.islower() will return False string3.islower() will return False 10. string.isupper(): Returns True if all cased characters in the string are uppercase. Examples: string.isupper() will return False string2.isupper() will return True string3.isupper() will return False 11. string.isspace(): Returns True if there are only whitespace characters in the string. Examples: