DP csvicivren SCIENCE : : PRACTICAL PROGRAMS FOR GRADE — XIl [2023-2024] . Appan Raj D M.C.A., B.Ed., - PGT resp): c : > me 7 A :TABLE OF CONTE! S.No Name of the Exercise Page.No Python Programs T._] Creating a menu driven program to perform arithmetic operations. 3 2. | Creating a python program to display Fibonacci series 5 3. | Creating a menu driven program to find factorial and sum of list ofnumbers using| 7 function. 4, | Creating a python program to implement retumning value(s) from function. 9 5. | Creating a python program to implement mathematical functions. 10 6. | Creating a python program to generate random number between | to 6 i 7. | Creating a python program to read a text file line by line and display each word| 12 separated by ‘# 8. | Creating a python program to read a text file and display the number of| 13 vowels/consonants/lower case/ upper case characters. ‘9. | Creating python program to display short words from a text file 15 10. | Creating a python program to copy particular lines of a text file into another text| 16 file, 11. | Creating a python program to create and search records in binary file 17 12. | Creating a python program to create and update/modify records in binary file. 19 18. | Creating a python program to create and search employee's record in csv file. 21 14. | Creating a python program to implement stack operations (List). 23 15. | Creating a python program to implement stack operations (Dictionary). 26 Python — SQL connectivity programs 16. | Creating a python program to integrate MYSQL with Python (Creating database | 28 and table) 17. | Creating a python program to integrate MYSQL with Python (Inserting records| 30 and displaying records) 18. | Creating a python program to integrate MYSQL with Python (Searching and| 32 displaying records) 19. | Creating a python program fo integrate MYSQL with Python (Updating records) 33 a “SQL Queries 20. | SQL COMMANDS EXERCISE - 1 35 21. | SQL COMMANDS EXERCISE — 2 36 22. | SQL COMMANDS EXERCISE = 3 38 23. | SQL COMMANDS EXERCISE —4 40 24, | SQL COMMANDS EXERCISE — 5 42 ——SS cee: esOPERATIONS AIM; ‘To write a menu driven Python Program to perform Arithmetic operations (+,-*,/) based on the user’s choice. SOURCE CODE; [print("1. Addition") print ("2. Subtraction") [print ("3. Multiplication") print ("4. Division") lopt=int (input ("Enter your choice:")) la=int (input ("Enter the First Number:")) jbeint (input("Enter the Second Number:")) if optesi: cmath print ("The Addition of two number is:",c) print ("Enter any other number other than 0") else: cma/b print ("The Division of two number is:",c) leise: print ("Invalid Option") Result: ‘Thus, the above Python program has been executed and the output is verified successfully. Se | jenPython Program Executed Output: 1. Adddition 2. Subtraction 3. Multiplication 4. Division Enter your choice:1 Enter the First Number: 10 Enter the Second Number:23 The Addition of two number is: 33To write a Python Program to display Fibonacci Series up to ‘n’ numb: SOURCE CODE: fFirst=0 |Second=1 lnowint (input ("How many Fibonacci numbers you want to display?")) if no< print ("Please Enter Positive Integer") lelse: print (First) print (Second) for i in range(2,no): Third=First+Second First=Second Second=Third print (Third) Result: ‘Thus, the above Python program has been executed and the output is verified successfully.How many Fibonacci numbers you want to display?8 FaUwNRRoDATE: CREATING A MENU DRIVEN PROGRAM TO FIND FACTORIAL AND SUM OF LIST OF NUMBERS USING FUNCTION, AIM: ‘To write a menu driven Python Program to find Factorial and sum of list of numbers using function SOURCE CODE: def Factorial (no) : Fel Af no<0: print("Sorry, we cannot take Factorial for Negative number") elif no==0 print("The Factorial of 0 is 1") else: for 1 im range (1,no#1) : FRei print ("the Factorial of",no,"is:",F) def Sum_List(L): sum=0 for 4 in range(n): Sum=sum+L [4] print("The Sum of List is # Main Program print (1. To Find Factorial") print ("2. To Find sum of List elements") optsint (input ("Enter your choice: ")) Af optesi: neint (input ("Enter a number to find Factorial:")) Factorial (n) Lf optes2: rst) neint (input ("Enter how many elements you want to store in List?:")) for i in range(n): ele=int (input ()) L.append (ale) Sun_List (L) Result: ‘Thus, the above Python program has been executed and the output is verified successfully. SS ce 7SAMPLE OUTPUT; Python Executed Program Output: 1. To Find Factorial 2. To Find sum of List elements Enter your choice:1 Enter a number to find Factorial:5 The Factorial of 5 is: 120AIM: ‘To Write a Python program to define the function Check(no1,no2) that take two numbers and Returns the number that has minimum ones digit. Source Code; Cheek(notno2): Af (n01%10) < (no2%10): return nol ‘eter return n02 int(input{"Eater the First aumber:")) int{input{"Eater the Second aumber:")} =Cheetx(a,) [The Number that has minimum one's digit is: Result: ‘Thus, the above Python program has been executed and the output is verified successfully. Sample Output: Enter the First number:245 Enter the Second number:342 ‘The Number that has minimum one’s digit is: 342 SaCREATING A PYTHON PROGRAM TO IMPLEMENT MATHEMATICAL FUNCTIONS AIM: To write a Python program to implement python mathematical functions to find: () To find Square of a Number. (i) To find Log of a Number( (ii) To find Quad of a Number Logie) SOURCE CODE; import math Jie? Square (num) : S=math. pow (num, 2) return S$ Jef Log (mum) : ‘S=math. log10 (num) return § def Quad (x, x): Semath. sqrt (X##2 + ¥##2) return § Iprint ("The Square of a Number is:",Square(5)) lprint ("The Log of a Number is:",Log(10)) lprint("the Quad of a Number is:",Quad(5,2)) Result: Thus, the above Python program has been executed and the output is verified successfully. SAMPLE OUTPUT: Python Executed Program Output: The Square of a Number is: 25.0 The Log of a Number is: 1.0 The Quad of a Number is: 5.385164807134504 a (0) esAIM: ‘To write a Python program to generate random number between 1 to 6 to simulate the dice. SOURCE CODE; import random while True Choice=input("\nDo you want to roll the dice(y/n) no=random. randint (1,6) Af Choices="y': print ("\nYour Number is:",no) el Results Thus, the above Python program has been executed and the output is verified successfully. xoll the dice(y/n) : 2 roll the dice(y/n) : —S (aTo write a Python Program to Read a text fil each word separated by '#" ‘Story.txt" line by line and display SOURCE CODE: f=open("Story. txt” Contents=f.readlini for line in Contents words=line. split () for i in words zy oO print (i+ yend=' ') print ("") £.close() Result: Thus, the above Python program has been executed and the output is verified successfully. 1 heart of a sorrow. t hangs on a cloud.To write a Python Program to read a text file "Story.txt” and displays the number of Vowels/ Consonants/ Lowercase / Uppercase/characters in the file. SOURCE CODE: feopen("Story. txt", 'r!) Contenta=£.read() Vowels=0 Consonant s=0 Lower_case=0 Upper_case=0 for ch in Content: if ch in ‘aeiouaziou Vowels=Vowels+1 Af ch in ‘bedfghjklmnpqrs tvwxyzBCDFCHIKLMNPQRSTVWXY2 ' : Consonants=Consonants+1 Af ch.islower(): Lower_case=Lower_case+1 Af ch. isupper(): Upper_caseUpper_case+1 £.close() print ("The total numbers of vowels in the file:",Vowels) print ("The total numbers of consonants in the file:",Consonants) Print("Zhe total numbers of uppercase in the file:",Upper_case) print ("the total numbers of lowercase in the file:",Lower_case) Result; ‘Thus, the above Python program has been executed and the output is verified successfully. SE a(RSAMPLE OUTPUT: Story. txt: Fa DAR RENOIR bike a Joy on the heart of a sorrow. ‘The sunset hangs on a cloud. [The total numbers of vowels in the file: 20 ithe total numbers of consonants in the fil ithe total numbers of uppercase in the file: 3 The total numbers of lowercase in the file: 46 29) ARERR Cnn EE EEE EE EOD ERE EECREATING PYTHON PROGRAM TO DISPLAY SHORT WORDS FROM A TEXT FILE AIM: ‘To Write a method Disp() in Python, to read the lines from poem.txt and display those words which are less than 5 characters. print{The follwoing words are less than 5 characters’ ‘for iin We ietenti) RESTART: C:\Users\APPS\AppData\Local \Prograns\2ython\ Python36\PR_COPY_TEXT FILE. py [ALL Lines which are starting with character ‘a! or ‘A’ bas been copied successfully into New. txt New.txt: Bena Ciera voponn ala Aeroplane was invented by the Right Brothers. An apple a day keeps the doctor away. bese a New BER ee C) peTo write a Python Program to Create a binary file with rell number and name. Search for a given roll number and display the name, if not found display appropriate message. SOURCE CODE: Tapert pickle ef Create() Feopen ("Students dat”, 'ab!) opt='y" while optem'y': Roll_Nomint (input ("Enter roll number:')) Nane=input ("Enter Name: ") = [Roll. No Name} pickle. dump (LF) optminput ("Do you want to add another student detail (y/n) :") F.close() Search() Paopen("Students. dat", ‘rb') no=int (input ("Enter ROl.No of student to search:")) found=0 while True: Sepickle. load(F) SE 8[0]meno: PEint ("The searched Rol1.No is found and Details axe:",S) founds break except: F.close() Af founde=0: Print ("The Searched Rollo is not found” Wain Progran ‘Thus, the above Python program has been executed and the output is verified successfully. SSSAMPLE OUPUT; PYTHON PROGRAM EXECUTED OUTPUT; Enter roll number:1 Enter Name:Arun Do you want to add another student detail (y/n) :y Enter roll number:2 Enter Name:Bala Do you want to add another student detail(y/n) :y Enter roll number:3 Enter Name:Charan Do you want to add another student detail(y/n) :y Enter roll number:4 Enter Name:Dinesh Do you want to add another student detail (y/n) :y Enter roll number:5 Enter Name:Divya Do you want to add another student detail(y/n):n Enter Roll.No of student to search:3 The searched Roll.No is found and Details are: [3, ‘Charan']AIM: ‘To write a Python Program to Create a binary file with roll number, name, mark and update/modify the mark for a given roll number. SOURCE CODE: amport pickle Ger Create( Feopon("Marks.dat", ‘ab') opte'y’ Wille opte='y Roll_Nosint (input (’Enter roll number:')) Mamo=input ("Enter Name: ") Markeint (Anpat ("Enters Makes") ) Ye {Roli_ No, Name, Mark} pickle. dump (LP) ‘opt=input ("Do you want to add another student detaii(y/n) doe Update’) : Paopen ("Marks dat”, 'zb+') nosint (input ("Enter Student Roll.Wo to modify mazks:")) foundeo try: while True: Fos=F.tell() ‘Sepickle.load(r) Sf S[0]=2no: PFint ("The searched Rollo is found and Details are:", S{2}=int (input ("Enter New Mark to be update:*)) F. seek (Pos) pickle. dump (s,7) found F.seek(Pos)#moving the file pointer again beginning of the current record. print(*Maxk updated Successfully and Detaile are:",8) Break except F.close() Af found==0: Print ("The Searched Roll.No is not found") (ain Progean create’) ee Result; ‘Thus, the above Python pr successfully. gram has been executed and the output is verifiedEnter roll number:1 Enter Name:Arun Enter Marks: 450 Do you want to add another student detail (y/n) :y Enter roll number:2 Enter Name:Bala Enter Marks:342 Do you want to add another student detail(y/n) :y Enter roll number: Enter Name:Charan Enter Marks: 423 Do you want to add another student detail(y/n) :y Enter roll numbe: Enter Name:Dinesh Enter Marks: 356 Do you want to add another student detail(y/n) :y Enter roll number:5 Enter Name:Divya Enter Marks:476 Do you want to add another student detail(y/n):n Enter Student Roll.No to modify marks:3 ‘The searched Roll.No is found and Details are: (3, ‘Charan’, 423] Enter New Mark to be update:470 Mark updated Successfully and Details are: [3, ‘Charan’, 470]AIM: To write a Python program Create a CSV file to store Empno, Name, Salary and search any Empno and display Name, Salary and if not found display appropriate message. SOURCE CODE: import csv def Create() Fropen("Bap. csv", ‘a’ newline="') Wecav.writer(F) opt='y’ while optes'y': No=int (input ("Enter Employee Number: ")) Mamewinput ("Enter Employee Nano: " Salefloat (input ("Enter Employee Salary: ")) T= (Ko, Name, Sal] W.wraterow(L) opt=input ("De you want to continue (y/n)?:") F.clese() Jace Search () : Peopen("imp.csv", "x! ,newLine=’\r\n') nomint (input ("Rnter Employee number to search") founded rowcav. reader (F) for data in row: Af data[0}==str (no) print ("\nEmployee Deatiis are:") ") Af found==0: print ("The searched Eaployee number is not found") F.close() Jimain Program create.) jearch() Result; ‘Thus, the above Python program has been executed and the output is verified successfully. a 0)SAMPLE OUTPUT; PYTHON PROGRAM EXECUTED OUTPUT: Enter Employee Number: 1 Enter Employee Name: Anand Enter Employee Salary:23000 Do you want to continue (y/n)?:y Enter Employee Number:2 Enter Employee Name:Akash Enter Employee Salary:25000 Do you want to continue (y/n)?:y Enter Employee Number:3 Enter Employee Name:Balu Enter Employee Salary:27000 Do you want to continue (y/n)?:y Enter Employee Number Enter Employee Name:Bavya Enter Employee Salary:29000 Do you want to continue (y/n)?:y Enter Employee Number:5 Enter Employee Name :Manoj Enter Employee Salary:35000 Do you want to continue (y/n)?:n Enter Employee number to search:3 Employee Deatils are: Name: Balu Salary: 27000.0To write a Python program to implement Stack using a list data-structure, to perform the follo ng operations: () To Push an object containing Doc ID and Doc name of doctors who specialize in "ENT" to the stack. Gi) Gi) To Pop the objects from the stack and ii) (iv) (ii) To display the elements of the stack (after performing PUSH or POP) play them. SOURCE CODE; Doe _ID=int{input('Enter the Doctor 1D:")) Doc_Name=input{"Enter the Name of the Doctors") Mob=iat{input{"Eater the Mobile Number of the Doctor:"}} Special=input("Enter the Specialization:") A Special==ENT': Stack.append([Doc_ID,Doc_Name]) Pop(): of Stack==[ ]: priat{"Stack is empty”) elser print("The deleted doctor detail is:',Stack.pop()) f Peek): if Stack==[ }: print{"Stack is empty") else topslen(Stacle}-1 print("The top of the stack Disp(): af Stack==[ print( "Stack is empty”) elser topslen(Stack}1 for iim range(topy-1y-1): »Stack[top])hey ("Performing Stack Operations Using List\n") cha yor ebee'¥ priat("4.Diop') Peek) lif optes4: Displ) ‘ehact Print{"Invalid Choice, Try Agaia!!!") chsinput(’\aDo you want te Perform another eporation(y al) ‘Result: ‘Thus, the above Python program has been executed and the output is verified successfully. Do you want to Perform auothes operationty/y SS 2.1.pUsH 2.POP S.PEEK 4.Disp Enter your choice: Enter the Doctor 1D:4 Eater the Name of the Doctor:Rani Do you want to Perform another operationly/nky. 1.PusH 2.PoP 3:PEEK a.Disp Enter yo {3, Murali] it) Aran] Do you want to Perform another operation(y/aky 1.PUsH 2.POP PEEK 4:Disp Eater your choice:3 The top of the stack is: [3, "Murali Do you want to Perform another operationly/a}y 1.PUSH 2.POP. 3.PEEK 4:Disp Enter your choice:2 ‘The deleted doctor detail is: (9, Murali’) Do you want to Perform another operation(y/n}:y 1.pusH 2.POP 3.PEEK 4.Disp Enter your choice: 11, Arun] Do you want to Perform another operation(y/n}:a SSAIM: To Write a program, with separate user-defined functions to perform the following operation: ate a function Push(Stk,D) Where Stack is an empty list and D is Dictionary of Items. from this Dictionary Push the keys (name of the student) into a stack, where the corresponding value (marks) is greater than 70 Gi) To Create a Function Pop(Stk) , where Stk is a Stack implemented by a list of student names. The function returns the items deleted from the stack. (iii) To display the elements of the stack (after performing PUSH or POP). DL Arun Jzint{input{'Entor the Maris of Aran") Df Anu Jnint{input{ Enter the Mark of Anu')) D[Vishal}sint{input("Enter the Mari of Vishal") Df Priya }=int(input( Enter the Mark of Priya:’)) D{Mano ]=iat\input(" Enter the Mark of Mano:"|), Puah(stirD) “oi opte=2sResult: Thus, the above Python program has been executed and the output is verified successfully. the Mark of Priya:45 ster the Marit of Mano:92 you want to perform another operation(y/n}:y ou want to pecform another operstioaly/a}:(CREATING DATABASE AND TABLE) To write a Python Program to integrate MYSQL with Python to create Database and Table to store the details of employees Source Code: Soper! mysqlconnector ot Create DB() ‘Consmysql.connector.connect(hosts localhost jaser= root ,password= eat) ty {fCon.is connected: cursCon.cursor() (Q='CREATE DATABASE employees” ‘cur.enseute(Q) print{"Employees database created sucessfully’) Print{‘Detabase name already exists’) Con.clotet) ot Create Tablet: ‘Con=mysql.connecter.conneet{nost= localhost ,user= root ,password= root ,database='cmployoes’) i Con.is connected(): ‘our=Con.cussor() (Qe CREATE TABLE EMP(ENO INT PRIMARY KEV,ENAME VARCHAR|20), GENDER VARCHAR(S},SALARY INT) ‘our.enseute(Q) print{'Emp Table creuted sucessfully tae print(‘Table Name already exists") Con.etose}) 'y wile eh==' or eh== print("\alnterfucing Python with Mysqt") print("1. To Create Database") print{"2. Te Create Table”) opt=intiinput{’Enter your choice: }) ifopte=1: optzinput{ Do you want to perform another operation(y/n}") Results ‘Thus, the above Python program has been executed and the output is verified successfully. SS 2Interfacing Python with Mysql 1. To Create Database 2. To Create Table Enter your choice:1 Employees database created sucessfully Do you want to perform another operation(y/n):y Interfacing Python with Mysql 1. To Create Database 2. To Create Table |Enter your choice:2 Emp Table created sucessfully Do you want to perform another operation(y/n):nTo write a Python Program to integrate MYSQL with Python by inserting records to Emp table and display the records. SOURCE CODE; inport mysql.comector con-nysq] connector. connect (host localhost: ,usernane=' root',passvord='root'database='enployees') if con.is_connected() : ccurecon.cursor() opt='y' while optes'y': Wosint (input ("Enter Exployee Sunber:")) Nane=input ("Enter Employee Nane:") Gendersinput ("Enter Enployee Gender (M/):") Salaryint (input ("Enter Buployee Salary:")) Query="INSER? INTO EMP VALUES({),'{)','{)',{))"-format (No, Name, Gender , Salary) ‘cur.execute (Query) con. commit () print("Record Stored Successfully") opteinput("Do you want to add another employee details(y/n) :") Query="SELECT + FROM EMP"; cur.execute (Query) datascur.fetchall () for 4 in data: print (i) ‘Thus, the above Python program has been executed and the output is verified successfully, SS (esEnter Employee Number: Enter Employee Name:Arun Enter Employee Gender (M/F) :M Enter Employee Salary:20000 Record Stored Successfully Do you want to add another employee details (y/n) :y Enter Employee Number:2 Enter Employee Name:Bala Enter Employee Gender (M/F) :M Enter Employee Salary:25000 Record Stored Successfully Do you want to add another employee details(y/n) :y Enter Employee Number:3 Enter Employee Name:Bavya Enter Employee Gender (M/F) :F Enter Employee Salary:27000 Record Stored Successfully Do you want to add another employee details(y/n) :y Enter Employee Number:4 Enter Employee Name: Saravanan Enter Employee Gender (M/F) :M Enter Employee Salary:29000 Record Stored Successfully Do you want to add another employee details (y/n) in (1, 'Arun', 'M', 20000) (2, 'Bala', 'M', 25000) (3, 'Bavya', 'F', 27000) (4, 'Saravanan', 'M', 29000)EX.NO: 18 DATE: CREATING A PYTHON PROGRAM TO INTEGRATE MYSQL WITH PYTHON {SEARCHING AND DISPLAYING RECORDS) AIM: To write a Python Program to integrate MYSQL with Python to search an Employee using, EMPID and display the record if present in already existing table EMP, if not display the appropriate message. SOURCE CODE; [Tapert mysql. connector Jcon=aysql connector. connect (host=' localhost’ ,usernane=' root! ,password=" root ' ,database='enployees' Jie con.4s_connected() + car=con. cursor () Print ("sstessssueusossesnsssoneseeeerensn) Print ("Welcome to Employee Search Screen") print ( “ a “*) Nomint (input ("Enter the eaployee number to search:")) Query= "SELECT * FROM EMP WHERE EMPID=()". format (Wo) cur.execute (Query) data=cur.fetchone() LE data!atione: print (data) elec: print (‘Record not Found! !!*) fi Jcon. clo: Result: ‘Thus, the above Python program has been executed and the output is verified successfully. SAMPLE OUTPUT: Python Executed Program Output: TTtT TILT itt ett titi t titi t ttt ttt ty Welcome to Employee Search Screen JS SHES HOHE a oodEiii ioe Enter the employee number to search:2 (2, 'Bala', 'M', 25000) —SS a |(:)EX.NO: 19. DATE: CREATING A PYTHON PROGRAM TO INTEGRATE MYSQL WITH PYTHON {UPDATING RECORDS) To write a Python Program to integrate MYSQL with Python to search an Employee using EMPID and update the Salary of an employee if present in already existing table EMP, if not display the appropriate message. SOURCE CODE: fiapoet miyaql connector Jconmnysql .connector connect (hosts! localhost © con.ia_connected() : usernanes! root! ,password=' root ' databases employees Nosint (input ("Enter the employee nunber to Update: ")) Query="SELECT © FROM EMP WIERE EMPID=()" format (Mo) car-execute (very) datamcur.fotchone() SE datataxone: Print ("Record found details are:") print (data) nseinput ("Do you want to update the Salary of the above employee (y/n)?:") AE ansee'y! or angee'Y: Mew Salnint (input ("Enter the New Salary of an Bxployee:")) JDDATE ENP SET SALARY=() WHERE EMPID=()". format (New_Sal,No) (Employes Salary Updated Successfully") Q2e"sELECT * FROM EMD" cur-execute (02) datancur.fetchall () for 4 in data: print () else: Deine ("Record not Found! !!*) Result: ‘Thus, the above Python program has been executed and the output is verified successfully, Se (esSAMPLE OUTPUT; Python Executed Program Output: AAA AAA eA RAAAAMMMAEAAHAME RA HAMM cE RH HE Welcome to Employee detail update Screen Enter the employee number to search: Record found details are: (3, "BAVYA", 'F*, 27000) Do you want to update the Salary of the above employee (y/n)?:y Enter the New Salary of an Employee:30000 Employee Salary Updated Successfully (1, ‘Arun’, 'M', 20000) (2, ‘Bala’, 'M", 25000) (3, 'BAWYA', 'F*, 30000) (4, ‘Saravanan', 'M', 29000)Ex.No: 20 DATE: AIM: To write Queries for the following Questions based on the given table: Rolino | Name | Gender | Age Dept DOA 1] Arun M 24 | COMPUTER | 1997-01-10 2 [Ankit M 21 [HISTORY | 1998-03-94 | 3 | Anu F 20 | HINDI 1996-12-12 4 | Bala M 19 NULL, 1999-07-01 5___| Charan | M 18 | HINDI 1997-09-05 6 | Deepa F 19 | HISTORY | 1997-06-27 7 | Dinesh [| M 22 | COMPUTER 8 [Usha i 23 [NULL (a) Write a Query to Create a new database in the name of "STUDENTS". CREATE DATABASE STUDENT! (b) Write a Query to Open the database "STUDENTS". USE STUDENTS; (c) Write a Query to create the above table called: "STU" CREATE TABLE STU(ROLLNO INT PRIMARY KEY,NAME VARCHAR(10), GENDER VARCHAR(3), AGE INT,DEPT VARCHAR(15), DOA DATE,FEES INT); (d) Write a Query to list all the existing database names. SHOW DATABASES; (e) Write a Query to List all the tables that exists in the current database. SHOW TABLES;Queries for the following Questions based on the given table: Rolino | Name | Gender | Age Dept DOA Fees 1] Arun M 24 | COMPUTER | 1997-01-10 | 120 2 ‘Ankit M 21 | HISTOR' 1998-03 200 3 ‘Amu F 20 | HINDI 1996-12-12 | 300 4 Bala M 19 | NULL. 1999-07-01 | 400 5 Charan [~_M 18 | HINDI 1997-09-05 |__250 6 Deepa FE 19 | HISTORY [1997-06-27 [~ 300 7 Dinesh | __M 22 | COMPUTER | 1997-02-25 | 210 8 Usha FE 23 [NULL 1997-07-31 | 200 (a) Write a Query to insert all the rows of above table into Info table. INSERT INTO STU VALUES (1,'Arui 24,'COMPUTER','1997-01-10', 120); INSERT INTO STU VALUES (2,'Ankit',’M’, 21,'HISTORY','1998-03-24', 200); INSERT INTO STU VALUES (3,'Anu’,'F', 20,'HINDI',,'1996-12-12', 300); INSERT INTO STU VALUES (4,'Bala’,'M', 19, NULL,'1999-07-01', 400); INSERT INTO STU VALUES (5,'Charan','M’, 18,'HINDI','1997-06-27', 250); INSERT INTO STU VALUES (6,'Deepa','F’, 19,'HISTORY','1997-06-27', 300); INSERT INTO STU VALUES (7,'Dinesh’,'M’, 22,'COMPUTER','1997-02-25', 210); F INSERT INTO STU VALUES (8,'Usha',F’, 23, NULL,'1997-07-31', 200); (b) Write a Query to display all the details of the Employees from the above table ‘STU’. SELECT * FROM STU; Output:(c) Write a query to Rollno, Name and Department of the students from STU table. SELECT ROLLNO,NAME,DEPT FROM STU; {d) Write a Query to select distinct Department from STU table SELECT DISTICT(DEPT) FROM STU; Output; (e) To show all information about students of History department SELECT * FROM STU WHERE DEPT='HISTORY'; Output:TE: AIM: ‘To write Queries for the following Questions based on the given table: Rollno Name | Gender Age Dept DOA Fees 1 Arun M 24 COMPUTER | 1997-01-10 120 2 Ankit M 21 HISTORY 1998-03-24 200 3 Anu F 20 HINDI 1996-12-12 300 4 Bala M 19 NULL, 1999-07-01 400 5 Charan M 18 HINDI 1997-09-05 250 6 Deepa F 19 HISTORY 1997-06-27. 300 7 Dinesh M 22 COMPUTER | 1997-02-25 210 8 Usha F 23 NULL. 1997-07-31 200. (a) Write a Query to list name of female students in Hindi Department. SELECT NAME FROM STU WHERE DEPT='HINDI' AND GENDER: Output: (b) Write a Query to list name of the students whose ages are between 18 to 20. SELECT NAME FROM STU WHERE AGE BETWEEN 18 AND 20;(c) Write a Query to display the name of the students whose name is starting with ‘A’. SELECT NAME FROM STU WHERE NAME LIKE 'A%'; (@) Write a query to list the names of those students whose name have second alphabet ‘n’ in their] names. SELECT NAME FROM STU WHERE NAME LIKE '_N%'; Output:Ex,No: 23 SQL COMMANDS EXERCISE - 4 DATE: AIM: To write Queries for the following Questions based on the given table: Rolino | Name | Gender | Age Dept DOA Fees 1 Arun M 24 | COMPUTER] 1997-01-10 | 120 2 Ankit M 21 | HISTORY | 1998-03-24 | 200 3 Anu F 20 | HINDI 1996-12-12 [300 4 Bala M 19 [NULL 1999-07-01 | 400 5 Charan [~_M. 18 | HINDI 1997-09-05 | 250 _| 6 Deepa | F 19 | HISTORY | 1997-06-27 | 300 7 Dinesh |__M 22 | COMPUTER | 1997-02-25 | 210 8 Usha FE 23 [NULL 1997-07-31 | 200 | (a) Write a Query to delete the details of Roll number is 8. DELETE FROM STU WHERE ROLLNt Output (After Deletion| (b) Write a Query to change the fess of Student to 170 whose Roll number is 1, if the existing fess is less than 130. UPDATE STU SET FEES=170 WHERE ROLLNO=1 AND FEES<130; Output(After Update):(c) Write a Query to add a new column Area of type varchar in table STU. ALTER TABLE STU ADD AREA VARCHAR(20); Output: (d) Write a Query to Display Name of all students whose Area Contains NULL. SELECT NAME FROM STU WHERE AREA IS NULL; Qutput; (e) Write a Query to delete Area Column from the table STU ALTER TABLE STU DROP AREA; mR () Write a Query to delete table from Database DROP TABLE STU; Outpu: QE SSS (a‘To write Queries for the following Questions based on the given table: TABLE: UNIFORM Ucode | Uname | Ucolor| StockDate Shirt | White [2021-03-31 Pant_| Black |_ 2020-01-01 Skirt | Grey |” 2021-02-18) Tie Blue | 2019-01-01 Socks [Blue | 2019-03-19 Belt _[ Black [2017-12-09 lorfen}s eo ro) TABLE: COST Ucode lo lo e-file el Price | Company | 500 Raymond. 580 Mattex 020 | Mattex 810 Yasin 940 | “Raymond 770 Yasin 830 Galin 150 Mattex 4 ooo feo feo fe (a) To Display the average price of all the Uniform of Raymond Company from table COST. SELECT AVG(PRICE) FROM COST WHERE COMPANY="RAYMOND'; = Fel (b) To display details of all the Uniform in the Uniform table in descending order of Stock date, SELECT * FROM UNIFORM ORDER BY STOCKDATE DESC; Output; ca (“a(c) To Display max price and min price of each company SELECT COMPANY,MAX(PRICE),MIN(PRICE) FROM COST GROUP BY COMPANY; (d) To display the company where the number of uniforms size is more than 2. ‘SELECT COMPANY, COUNT(*) FROM COST GROUP BY COMPANY HAVING COUNT(*)>2; {e) To display the Ucode, Uname, Ucolor, Size and Company of tables uniform and cost. SELECT U.UCODE,UNAME,UCOLOR,SIZE,COMPANY FROM UNIFORM U,COST C WHERE U.UCODE=C.UCODE; Output: aS |)