Delhi Public School Patna INFO PRATICES PRACTICAL Class: Session: Student’s Name:ACKNOWLEDGEMENT | would like to express my immense gratitude to my chemistry teacher Mrs. RUCHIRA SARKAR for the help and guidance she provided me for completing this project. I would also like to thank my parents who gave me their ideas and inputs to complete this project. Most of all I thank my school for providing us with facilities and opportunities to make this project.TABLE OF CONTENTS ‘S.No Name of the Exercises Programs using Pandas 1. | Creating a Python program to create a series using a dictionary. 3 2. | Creating a Python program to create a series using scalar value. 4 3. | Creating a Python program to create a series using numpy array. 5 4. | Creating a python program for modifying or updating existing values of series|__ 6 object. 5. | Creating a Python program for performing mathematical operations on two| 7 Series objects. 6. | Creating a Python program for calculating per capita income of four zones | 8 using Series. 7. | Creating a Python program to display attributes of a Series. 9 8. | Creating a Python program using head() and tail() in Series. 10 9. | Creating a Python program for creating a DataFrame using a nested list. 11 10. | Creating a Python program for creating a DataFrame using a dictionary of | 12 lists. 11. | Creating a Python program for accessing values of rows and columns ofa| 14 DataFrame. 12. | Creating a python program to perform operations on a DataFrame (rename,| 16 count, update, replace) 18. | Creating a Python program to filter the data of a DataFrame. 18 14. | Creating a Python program to display the attributes of the DataFrame. 19 15. | Creating a Python program to display the data of a DataFrame row-wise and| 20 column-wise using iterrows() and iteritems|) 16. | Creating a Python program to perform writing and reading operations in a] 22 CSV file. 17. | Creating a python program for plotting a line chart. 23 18. | Creating a python program for plotting a bar chart from a CSV file. 24 19. | Creating a python program for plotting a multiple bar chart froma CSVfile. | 25 20. | Creating a python program for plotting Histogram. 26 21. | SQL COMMANDS EXERCISE - 1 (Basic Queries - I) 27 22. | SQL COMMANDS EXERCISE - 2 (Basic Queries — II) 29 23. | SOL COMMANDS EXERCISE — 3 (Aggregate functions, Order by Group by, | 31 Having Clause) 24. | SQL COMMANDS EXERCISE — 4 (Math Functions) 33 25. | SQL COMMANDS EXERCISE - 5 (Text Functions) 35 26. | SOL COMMANDS EXERCISE ~ 6 (Date Functions) 37 2EX.NO: DATE: CREATING A PYTHON PROGRAM TO CREATE A SERIES USING A DICTIONARY. AIM: To write a Python program to create a Series to store 5 students Percentage Using dictionary and print all the elements that are above 75 percentage. SOURCE CODE; [import pandas as pd D={"arun":65, "Bala" :91,"Charan":74, "Dinesh" :80, "Usha" :85) s=pd. Series (D) print (S[S>75]) RESULT: ‘Thus, the above Python program has been executed successfully, and the output has been verified, SAMPLE OUTPUT: Bala 91 Dinesh 80 Usha a5 dtype: int64To write a Python program to create a Series object that stores the Initial budget allocated (50000/- each) for the four quarters of the year: Qtrl, Qtr2, Qtr3 and Qtr4. SOURCE CODE: import pandas as pd jBudget=pd. Series (50000, index=['Qtri', 'Qtr2', 'Qtr3','Qtr4']) Jprint (Budget) RESULT: Thus, the above Python program has been executed successfully, and the output has been verified. SAMPLE OUPUT: gtri 50000 Qtr2 50000 Qtr3 50000 gtra 50000 dtype: inte4DATE: CREATING A PYTHON PROGRAM TO CREATE A SERIES USING NUMPY ARRAY AIM: To write a Python program to create a Series object that stores the Employee names as index and their Salary as values SOURCE CODE: import pandas as pd impoxt numpy -y Enter the name of the employee: Anu. Enter the new salary: $8000 ‘Salary updated successfully! Employees Salary After updating Jona 60000 Bala 60000 Aaa 58000 Birundna 52000 ‘type: inte 6To create a program in python to perform following mathematical Operations on Two Series objects: (i) Addition (ii) Subtraction (iii) Multiplication (iv) Division. ‘SOURCE CODE: import pandas as pd s1=pd. Series ({10,20,30] ,andex=['a','b','c'l) =pd. Series ( [2,5], index: vB) int ("The Addition of two series object is -int (S1+82) int ("The Subtraction of two series object is:") -int (S1-S2) int ("The Multiplication of two series object is:") int (S1*S2) int ("The Division of two series object 1s -int ($1/S2) RESULT: Thus, the above Python program has been executed successfully, and the output has been verified. SAMPLE OUTPUT: ‘The Addition of two series object is: a 12.0 b 25.0 © NaN dtype: floats ‘The Subtraction of two series object is a 8.0 b 15.0 © NaN dtype: floats4 ‘The Multiplication of two series object is: a 20.0 b 100.0 e NaN dtype: floats4 whe Division of two series object is: a 5.0 b 4.0 cc NaN dtype: floatsaCREATING A PYTHON PROGRAM FOR CALCULATING PER CAPITA INCOME OF FOUR ZONES USING SERIES. AIM: To write a Python program to create two series i.e. population and average income of Four Zones, and then calculate per capita income storing in third pandas series print it. Source Code: [import pandas as pd population=pd.Series([5200,2100,4523,3422], index=[East',’West','South’,/North]) avgincome=pd.Series([117832,587324,171802,235038],index=[ East’, West’, South’, North] perCapitalncome=avgincome/ population print("Percapita Income of Four Zones are:") print("\n") print(perCapitalncome) RESULT: Thus, the above Python program has been executed successfully, and the output has been verified. Sample Output: Percapita Income of Four Zones are: East 22.660000 West 279.678095 South 37.984081 North 68.684395 type: MoatesTo write a Python program to create a Series using list and display the following attributes of the Series: (i) index (ii) dtype (ii) size (iv) shape (¥) hasnans SOURCE CODE: Gmport pandas as pd |n=[20,45,67,3, 43] |s=pd. Series (L,index=['a','b', 'c','d', 1) print ("The index of the Series is:",S.index) print ("the data type of the Series is:",8.dtype)} print ("The size of the Series is:",S.size) |print("The shape of the Series is:",S.shape) |print ("The NaN of the Series is:",S.hasnans) RESULT: ‘Thus, the above Python program has been executed successfully, and the output has been verified. SAMPLE OUTPUT: index of the Series is: Index(['a', 'b', 'c', 'd’, data type of the Series is: inté4 size of the Series is: 5 shape of the Series is: (5,) WaN of the Series is: False '], dtype=' object") eeeeeTo write a Python program to create a Series using list of Marks of 10 students and display first 5 Students’ marks and Last 2 Students’ marks from Series object. SOURCE CODE: [sport pandas as pd irks= (67, 89,90, 86, 100, 45,83 ,43, 66, 55] pd. Series (Marks, index=['stul', 'Stu2', ‘stud’, rint ("The First five student marks are:") yrint (S.head ()) ant ("\n") int ("The Last two students marks are:") srint (S.tail (2)) stud’, ‘Stus’, 'Stus', ‘Stu7’, "sus", 'Stug’, ‘stul0"]}} RESULT: ‘Thus, the above Python program has been executed successfully, and the output has been verified, SAMPLE OUTPUT: ‘The First five student marks are: stul 67 stuz 8s stu3 90 stud 86 stus 100 dtype: intéa The Last two students marks are: stu9 66 stul0 55 dtype: inté4 10DATE: AIM: To write a Python program to create a panda’s Data Frame for the following table Using Nested list: Name | Age ‘stua_[Arun 2a stu2 [Bala 23 stu3_|charan 22] SOURCE CODE; Gmport pandas as pd IL=[['Arun’,21],['Bala’,23],['Charan',22]] \D=pd.DataFrame(L,index=['Stul', 'Stu2', 'Stu3'],columns=['Name', ‘Age’ ])| lprint (D) ‘RESULT: Thus, the above Python program has been executed successfully, and the output has been verified. SAMPLE OUTPUT: Name Age stul Arun 21 stu2 Bala 23 Stu3 Charan 22 EROS DSS RESEDA SOS SSSR SAS IAA ASHE uwDATE: CREATING A PYTHON PROGRAM FOR ACCESSING VALUES OF ROWS AND. COLUMNS OF A DATAFRAME. AIM: To write a Python program to create a panda’s DataFrame called DF for the following table Using Dictionary of List and perform the following operations: (i) To Display only column Toys’ from DataFrame DF. (i) To Display the row details of ‘AP’ and 'OD' from DataFrame DF. iii) To Display the column 'Books' and ‘Uniform! for ’M.P’ and 'U.P' from DataFrame DF. (iv) To Display consecutive 3 rows and 3 columns from DataFrame DF. SOURCE CODE: [import pandas a= pd ‘woys! : (7916, 8808, 7226,76171 , Books’ : [61896 , 8208, 6149,6157), ‘Uniform! : (610,508, 611,4571, Shoes’ : (8820, 6798, 9611, 64571) vey lbe=pd.DataFrame (D, index=[ AP’, "0D! , Jprint(rthe detaiis of roys are:) Jprint (DF.1oc{:, 'Toys']) Jprint ("\a") Jprint ("the details of AP and oD are:") print (DF-1oc{’AP!:'0D', 1) Jprant ("\n") Jprint ("the detaiis of mp and U.P are:") Jprimt (DF.Loc['M.P':'U.P', ‘Books! : ‘Uniform’ ]) Jprint ("\a") print ('consecutive 3 Rows print (DF.t10c{0:3,0:3)) nd 3 Columns are:') RESULT: Thus, the above Python program has been executed successfully, and the output has been verified. 12SAMPLE OUTPUT: The details of Toys are: AP 7916 oD 508 MP 7226 vu 7617 Name: Toys, dtype: int64 ‘The details of AP and OD are: Toys Books Uniform Shoes AP 7915 61896 610 8810 op 8508 8208 508 6798 The details of MP and U.P a: Books Uniform MP 6149 611 U.P 6157 457 Consecutive 3 Rows and 3 columns are: Toys Books Uniforn AP 7916 61896 610 oD 8508 8208 508 M.P 7226 6149 611 3REATING A PYTHON (RAM FOR At VALUES OF AND To write a Python program to create a panda’s DataFrame called DF for the following table Using Dictionary of List and perform the following operations: (i) Insert a new column “Bags” with values as [5891, 8628, 9785, 4475]. (ii) Delete the row details of M.P from DataFrame DF. SOURCE CODE: import pandas as pa jD={ "Toys: [7916, 8508, 7226, 76171, "Books: [61896, 8208 , 6149, 61571, ‘Uniform! : [610, 508,611,457], ‘shoes’ : (8810, 6798 9611, 6457] } -DataFrame(D,index=['AP','OD', 'M.P', 'U.P!1) IDFL'Sags']=[5891, 8628, 9785, 4475] # Adding a new column Bags| ‘int ("After Adding a new column:") print (DF) int ("\n") Is=DF.drop('M.P') # Deleting M.P details. ‘int ("After Deleting M.P details:") int (s; RESULT: Thus, the above Python program has been executed successfully, and the output has been verified. 14SAMPLE OUTPUT: After Adding a new column: Toys Books AP 7916 61896 op 8508 «8208 M.P 7226 6149 U.P 7617 6157 After Deleting M.P details: Toys Books AP 7916 61896 op 8508 +8208 U.P 7617 6157 eased HDS SSO HESS Ooo SOI II IIIS DISA ISAS OIA Uniform Shoes 610 8810 508 6798 611 9611 457 6457 Uniform Shoes 610 8810 508 6798 457 6457 Bags 5891 8628 9785 4475 Bags 5091 8628 4475 15CREATING A PYTHON PROGRAM TO PERFORM OPERATIONS ON A DATAFRAME (RENAME, COUNT, UPDATE, REPLACE) AIM: To write a Python program to create a pandas DataFrame to analyze number of Government and Private medical college and their Total seats,Fees statewise details using the dataset available at www.data.gov.in. Also, perform the following operations. (i) To Change the name of the state AP to Andhra. ‘To Count and Display Non-NaN values of each column: (iii) To Count and Display Non-NaN values of each row. (iv) To Increase the fees of all colleges by 5% (v) To Replace all NaN values with 0. Source Code: wate al Colleges import pandas os pd import mumpy as ap |$1=pd.Series([17,30,7150,25000},index=['GC',/PC |$2=pd.Series([6,np.NaN,726,33000},index=[GC,/PC','TS',Fees]}#Assam_ |S3=pd.Series([ap.NaN,1,100,27000} index=[ GC, PCTS, Fees ])iSilcicim| |$4=pa.Series([22,24,46,23000],index=[GC', PC, TS, Fees ]}#TN |SS=pa.Series([7,np.NaN,673,15000},index=[GC', PC, TS, Fees]}#AIIMS DF=pd.DataFrame|{ AP :S1, Assam :S2, Sikkim :S3, TN 84, AllMS :$5}) print{’Original DataFrame") print(DF) print{"\2") print{"Changing the name from AP to Andhra:") DF.rename(cotumas=(AP'"Andira’},inplace=Truc) print(DF) print{"\") print{Counting and displaying non-NaN values of each column:") print(DF.count)) print(\ 2") print{"Counting and displaying non-NaN values of each row:") satel aici Soninl A wahigns be 8) {]*DP loc[ Fees :]*0.05 print("Replacing all NaN values with 0:") DF.fitina(0,inpiace=True) print(DR) RESULT: ‘Thus, the above Python program has been executed successfully, and the output has ‘been verified. 16Sample Output: Original DataFrame AP Assam Sikkim TN AUMS oc 17° 60 MN 22 7.0 PC 30 NaN 10 26 NaN TS 7150 726.0 100.0 46 673.0 Fees 25000 33000.0 27000.0 23000 15000.0 (Changing the name from AP to Andhra: ‘Andhra Assam Sikkim TN AIMS ec 17 6.0 NaN 22 7.0 PC 30 NaN 1.0 24 NaN TS 7150 726.0 100.0 46 673.0 Fees 25000 33000.0 2700.0 23000 1500.0 Counting and displaying aon-NaN values of each cofuma: Andhra 4 Assam 3 ‘Sikkim 3. ™ 4 alms 3 idtype: into Counting and displaying non-NaN values of cach row: 4 3 5 guns 5 intea Increasing the fess of ail colleges by 5% Andhra Assam Sikkim TN AIMS Gc 17.0 6.0 NaN 220 7.0 PC 90.0 NaN 1.0 24.0 NaN TS 7150.0 726.0 100.0 46.0 673.0 Fees 26250.0 34650.0 28350.0 24150.0 15750.0 Replacing all NaN values with 0: Andhra Assam Sikkim TN AIMS ec 170 60 00 220 7.0 PC 30.0 0.0 10 240 0.0 TS 7150.0 726.0 100.0 46.0 673.0 Fees 26250.0 34650.0 28350.0 24150.0 15750.0 wvTo write a Python program to create a panda’s DataFrame called DF for the following table using Dictionary of List and display the details of students whose Percentage is more than 85. Siu Name [Degree [Percentage st ‘anc 6 2 fran [Mca as 3 faa | Me 9 34| chan [ose 76 ss[ Mano | mca es SOURCE CODE; 'Degree': ['MBA','MCA','M.E','M.Sc','MCA'], "Percentage! :[90,85,91,76,84]} F=pd.DataFrame (D,index=['S1', 'S2 rint (DF.loc [DF [' Percentage ']>85]) $3','S4','S5"}) RESULT: Thus, the above Python program has been executed successfully, and the output has been verified, SAMPLE OUTPUT: Stu_Name Degree Percentage sl Anu MBA 90 83 Bala ME on 18AIM: ‘To write a Python program to create a DataFrame using Dictionary of list and display the following attributes of the DataFrame: (i) index (ii) columns (iii) axes (iv) dtypes (¥) shape (vi) dimension (vii) T SOURCE CODE: [import pandas as pa [D=(Stu_Name’:['Anu’,'Bala’,'Charan’,’Mano|], "Degree'’[MBA’,"MCA',"M.E','M.Sc}, ‘Percent :[90,85,91,76], [DF=pd.DataFrame(D,index=['Stul’,'Stu2','Stud','Stu4']} [print("\nThe index of the DataFrame is:",DF.index) [print("\nThe columns of the DataFrame is:',DF.columns] lprint("\nThe axes of the DataFrame is:",DF.axes) lprint("\nThe data types of the DataFrame is:",DF.dtypes)| Iprint("\nThe shape of the DataFrame is:",DF.shape) lprint("\nThe Dimension of the DataFrame is:',DF.ndim) int("\nThe transpose of the DataFrame is:\n",DF.T) RESULT: Thus, the above Python program is executed successfully and the output is verified. SAMPLE OUTPUT; indend{ Stu’, Stu2’, Stud’, Stud], dtype~ object) columns of the DataPrame is: Indend['Stu Name’, Degree, Percent], dtype~ebject) 19To write a Python program to create a panda’s DataFrame called Students for the following table and demonstrate iterrows and iteritems. SOURCE CODE: import pandas as pd D={"Stu Name": ["Anu", "Arun", "Bala", "Charan", "Mano"] , "Degree": ["MBA", "MCA" ,"M.E","M.So", "MCA"], "Percentage" : [90,85,91,76,84]} "s5"]) DP=pd.DataFrame(D, index=["'S1", "$2", "$3", "S4' for (row,values) in DF.iterrows(): print (row) print (values) print ()#To print an empty line after each row. for (col,values) in DF.iteritens(): print (col) print (values) print () RESULT: ‘Thus, the above Python program has been executed successfully, and the output has been verified. 20[PLE OUTPUT: # Output for Iterrows{)_ st Stu_Name Anu Degree MBA Percentage 90 Name: Si, dtype: object 82 Stu Name Arun Degree McA Percentage 85 Name: 52, dtype: object 83 Stu Name Bala Degree ME Percentage 31 Name: 83, dtype: object sé Stu Name Charan Degree M80 Percentage 76 Nane: S4, dtype: object 35 stu Name Mano Degree MCA Percentage 84 Name: $5, dtype: object # Output for Iteritems() Stu_Name st Anu 82 Arun 83 Bala S4 Charan 35 Mano Name: Stu Name, dtype: object Degree si MBA. 82 McA 3 ME S40 M.Se 85 MCA Name: Degree, dtype: object Percentage sl 90 82. 85 83 91 8476 8584 Name: Percentage, dtype: int6d 21CREATING A PYTHON PROGRAM TO PERFORM WRITING AND READING OPERATIONS INA CSV FILE AIM: To Write a Python program to store the details of Employess’ such as Empno, Name, Salary into a Employee.csv file. Also, write a code to read employee details from csv file. SOURCE CODE: import pandas as pd ":["Anu", "Ram", "Raj", "Mano", "Rajan"], 25,27,35,27,32) KA", "AB"], : [26000 , 35000 , 45000 , 25000 , 370001} DF=pd.DataFrame (Emp, index=["E1","E2","E3","E4","E5")) DF. to_csv("E: \\Data\\Emp.csv") \\Emp.csv") Data=pd.read_csv("E:\\Da print (Data) RESULT: ‘Thus, the above Python program has been executed successfully, and the output has been verified. SAMPLE OUTPUT: E1 Anu 25 1N 26000 0 £2 Ram 27 AP 35000 2 £3 Raj 35 TN 45000 2 E4 Mano 27 KA 25000 3 E5 Rajan 32 AP 37000 SAHOO SD EIR SOROS ESS SSDI SISA SA SASSI 22CREATING A PYTHON PROGRAM FOR PLOTTING A LINE CHART AIM: To write a Python program to plot a Line chart to depict the changing weekly Onion and Brinjal prices for four weeks. Also, give appropriate axes labels, title and keep marker style as Diamond and marker edge color as ‘red’ for Onion. SOURCE CODE: import matplotlib-pyplot as pit \Weeks=[1,2,3,4] lonion=[45,25,32,80] iBrinjal=[16,18,45,50] plt.title ("Price analysis") Iplt .xlabel ("weeks") plt .ylabel ("Prices") [plt .plot (Weeks, Onion,marker='D' ,markersize=15 ,markeredgecolor='r' iplt .plot (Weeks, Brinjal) Lt .show() RESULT: Thus, the above Python program has been executed and verified successfully, and its respective chart has been generated successfully. SAMPLE OUTPUT: Price Analysis 23To write a Python program to create a DataFrame for subject-wise average, save it to a CSV file, and then draw a bar chart using Matplotlib with a width of each bar as 0.25, specifying different colors for each bar. Additionally, provide a proper title and axes labels for the bar chart. suatplotlib.pyplot o> plt = pd. DataFrame({ Subject’: [Eng'yPhy’, ‘Maths’, Che, 1P])Sub_Avg':[72,85, 78, 92, 80}, index=[Sub1,/Sub2/Sub3,Subs'/SubS]) 40, cav{D:\ \Classi2\ IP\ \Subjectssv") Thus, the above Python program has been executed and verified successfully, and its respective chart has been generated successfully. Sample Output: Subject Average Analysis a *y ve Ge ra 24‘To write a Python program to plot a multiple bar chart From CSV file using Matplotlib for subject wise Scores of Class A, Class B, and Class C. Different colors represent each class, and subjects include English,Accountancy,Economics,BST and IP. Proper labels, a title and a legend are displayed on the chart. Source Code: [import pandas os pd import numpy a= mp isaport matplottib.pyplot as pit ita = { ‘Subject’: [Eng,, Acc’, Eco’, ‘BST,1P}}, ‘Class’: [85, 78, 92, 80,98}, ‘ClassB': [90, 65, 88, 75,87], ‘Class’ [75, 82, 60, 90,94]} |DF=pd.DataFrame(data) IDF.to_csv{'D:\ \CLASS12\ \IP\ \Class.csv") |Data=pd.read_csv{"D:\\CLASS12\ \IP\\Class.csv") pit.xtabell'Subjects) pit-ytabot!'Avorago’) pit titie( ‘Subject-wise Average for Classes A, B, and C’) x=np.arange(5) |pit.bar(DF[ Subject'],DF[ ClassA ],width=0.15,label='Ciass-A’)| pit.bar(x+0.15,DF[ ClassB'],width=0.15,label="Ciass. [pit.bar(x+0.30,DF[ ClaseC’],width-0.15,label='Clase-C’) pitstegend() [pit.sn RESULT: Thus, the above Python program has been executed and verified successfully, and its respective chart has been generated successfully. Sample Output: 25PLOTTING HISTOGRAM To write a Python program to plot a Histogram for the following class interval or range. Also, give appropriate axes name, title and edege color as ‘red’. [import matplotlib.pyplot as plt ‘ks=[40 ,60,55,20,35,70,60,89,20,33] plt.title ("Maths Marks-Histogram of class XII") plt.xlabel ("Mark Ranges") plt.ylabel ("No.of Students") plt.hist (Marks bins=(0,33,45,60,100] ,edgecolor=' red RESULT: Thus, the above Python program has been executed and verified successfully, and the respective chart has been generated successfully. SAMPLE OUTPUT: Maths Mars Histogram of clas 26DATE: SQL COMMANDS EXERCISE - 1 {Basic Queries - I) AIM: To write Queries for the following Questions based on the given table: EmpID| Name | Gender | Age| Dept DOJ Salary | City 1 Praveen | M 25_| Sales 1989-06-08 | 20000 | Chennai 2 ‘Arun [| M 29 | Marketing | 1989-09-26 | 22000 | Chennai 3 Usha |F 27 | Finance | 1994-08-09 | 25000 | Bangalore 4 Bala__|M 31_| Sales 1990-03-23 | 27000 | NULL 5 Rani | F 28 | Marketing | 1990-04-23 | 27000 | Mumbai 6 Nisha | F 26 | NULL. 1991-02-24 | 18000 | Bangalore 7 Manoj_|M 32 | Finance [1982-05-06 [30000 | Goa {a) Write a Query to Create a new database in the name of "EMPS". (CREATE DATABASE EMPS; (b) Write a Query to Open the database EMPS. USE EMPS; (c) Write a Query to create the above table called: Info CREATE TABLE INFO (EmpID int primary key, Name varchar(15), Gender varchar(3),Age int,Dept varchar(15),DOJ date, Salary int, City varchar(10)); (4) Write a Query to list all the existing database names ‘SHOW DATABASES; 7{e) Write a Query to List all the tables that exists in the current database. ‘SHOW TABLES; (8) Write a Query to insert all the rows of above table into Info table. INSERT INTO INFO VALUES (1,'Praveen',’M’, 25,'Sales','1989-06-08',20000','Chennai’); INSERT INTO INFO VALUES(2,'Arun’,'M',29,'Marketing’,'1989-09-26',22000,'Chennai’); INSERT INTO INFO VALUES(3, Usha’, 'F’,27,'Finance','1994-08-09',25000,'Bangalore); INSERT INTO INFO VALUES(4,'Bala;,'M’,31,'Sales','1990-03-23',27000,NULL); INSERT INTO INFO VALUES{(5, Rani,,F’,28,'Marketing’,’1990-04-23',27000, Mumbai’); INSERT INTO INFO VALUES (6,'Nisha’,'F', 26, NULL,'1991-02-24', 18000,'Bangalore'); INSERT INTO INFO VALUES (7,'Manoj',’M’, 32,'Finance','1982-05-06', 30000,'Goa'); {g) Write a Query to display alll the details of the Employees from the above table INFO’. SELECT * FROM INFO; 28Ex.No: 22 DATE: AIM: SQL COMMANDS EXERCISE - 2 {Basic Queries - Il) To write Queries for the following Questions based on the given table: EmpID | Name | Gender | Age Dept DOJ Salary City | 1 Praveen | M 25 | Sales 1989-06-08 | 20000 | Chennai 2 Arun [M 29 | Marketing | 1989-09-26 | 22000 | Chennai_| 3 Usha__[F 27 | Finance [1994-08-09 | 25000 | Bangalore] 4 Bala_|M 31 | Sales 1990-03. 27000 | NULL 5 Rani__|F 28 | Marketing | 1990-04-23 | 27000 | Mumbai_| 6 Nisha__|F 26 | NULL 1991-02-24 | 18000 | Bangalore] 7 Manoj_|M. 32_| Finance | 1982-05-06 | 30000 | Goa | {a) Write a Query to Display Employees’ name and City from the above table. SELECT NAME, CITY FROM INFO; (b) Write a Query to Display all details of Employees who are living in Chennai. SELECT * FROM INFO WHERE CITY=' ‘CHENNAT;; 29{c) Write a Query to get the name and salary of the employee whose salary is above 15000 and gender is not male. SELECT NAME,SALARY FROM INFO WHERE SALARY >15000 AND GENDER<>'M' (4) Write a query to update increase 10% Salary of an employee whose City is 'CHENNAT and Gender is MALE UPDATE INFO SET SALARY=SALARY+ (SALARY*0.10) WHERE CITY='CHENNAI' AND GENDER="MALE;; Output (After Updating): (e) Write a Query to delete the details of Employee Id 6. DELETE FROM INFO WHERE EMPID=6; 20DATE: SQL COMMANDS EXERCISE - 3 (Aggregate Functions, Order By Group By, Havning Clause) AIM: To write Queries for the following Questions based on the given table: EmpID | Name _| Gender | Age Dept DOJ Salary City 1 Praveen | M_ 25 _| Sales 1989-06-08 | 20000 | Chennai 2 Arun. M 29 | Marketing | 1989-09-26 | 22000 | Chennai 3 Usha F 27_| Finance 1994-08-09 | 25000 | Bangalore 4 Bala M 31 _| Sales 1990-03-23 | 27000 | NULL 5 Rani FE 28 _| Marketing | 1990-04-23 | 27000 | Mumbai 6 Nisha, F 26_| NULL 1991-02-24 | 18000 | Bangalore 7 Manoj_|M 32 | Finance | 1982-05-06 | 30000 | Goa (a) Write a Query to list names of Employees in Descending order. SELECT NAME FROM INFO ORDER BY NAME DESC; (b) Write a Query to find a total salary of all employees. SELECT SUM(SALARY) FROM INFO; Output: {c) Write a Query to display maximum salary and minimum salary of employees. SELECT MAX(SALARY), MIN(SALARY) FROM INFO; Output: an(a) Write a Query to count the number of employees earning more than 25000. SELECT COUNT(SALARY) FROM INFO WHERE SALARY>25000; (e) Write a query to display sum of salary of the employees grouped by department wise. SELECT DEPT, SUM(SALARY) FROM INFO GROUP BY DEPT; Output: (8) Write a query to display the department names where number of employees are greater than or equal to 2. SELECT DEPT FROM INFO GROUP BY DEPT HAVING COUNT(*)>=: 32DATE: AIM: To write Queries for the following Questions based on the given table -"STU" {a) Write a Query to Display square of age that got admission in the month of August SELECT POWER(AGE,2) FROM STU WHERE DOA LIKE '%-08-%'; (b) Write a Query to display Remainder of column Percentage divide by 3. SELECT MOD(MARKS,3) FROM STU; 33(c) Write a Query to display Student names and their Percentage in round figure. SELECT NAME, ROUND(PERCENTAGE,0) FROM STU; Output: (4) Display Name, Percentage and round up the remainder marks up to 2 decimal places. SELECT NAME, ROUND(MOD(PERCENTAGE,3),2) FROM STU; 34‘SQL COMMANDS EXERCISE — 5 (Text Functions} To write Queries for the following Questions based on the given table -"STU" {a) Write a Query to display Department name in lower case letters. SELECT LCASE(DEPT) FROM STU; (b) Write a Query to display department name and its respective number of characters in Dept column SELECT DEPT,LENGTH(DEPT) FROM STU; 35,{c) Write a Query to display first 2 characters of the column Name. SELECT LEFT(NAME,2) FROM STU; (d) Write a Query to display first 2 characters of the column Name. SELECT RIGHT(NAME,2) FROM STU; Output: (e) Write a query to display the names of all students and extract five characters from the third position of the 'Name' field. SELECT SUBSTR(NAME,3,5) FROM STU; Output: 36SQL COMMANDS EXERCISE - 6 (Date Functions! To write Queries for the following Questions based on the given table: (a) Write a Query to display student name and month of date of admission of all students. SELECT NAME, MONTH(DOA) FROM STU; Output: (b) Write a Query to display Student name and day name of the students’ DOA of the table STU. SELECT NAME, DAYNAME(DOA) FROM STU; 37(c) Write a query to display the joining year of IP students. SELECT YEAR(DOA) FROM STU WHERE DEPT='IP' (4) Write a Query to Display the month for the date_of_birth of all students, SELECT NAME, MONTHNAME(DOA)FROM STU; Output: {e) Write a query to display the names of the students who joined in the month of June. SELECT NAME FROM STU WHERE MONTHNAME(DOA)='June'; 38