Education-Portal - SQL Query Assignments

Q.A Write down the Programs in PYTHON :-

Write a PYTHON program to take input for a number and print it table ?


no = int(input("Enter Number"))
i = 1
while(i<=10):
	table = no*i
	print(no,'x',i,'=',table)
	i = i + 1

Write a PYTHON program to take input for a number and print its factorial using Recursion ?


number = int(input("Enter Number :"))
i = 1
fact = 1
while(number>=1):
    fact = fact * number
	number = number -1
	

print("Palindrom of number is ",fact)

Write a PYTHON program to take input for 3 number check and print the largest number ?


firstno = int(input('Enter first no'))
secondno = int(input('Enter second no'))
thirdno = int(input('Enter third no'))
if(firstno>secondno and firstno > thirdno):
    print('First no is greater')
elif(secondno > firstno and secondno > thirdno):
    print('Second no is greater')
else:
    print('Third no is greater')

Write a recursive Python program to test if a STRING is palindrome or not ?


Mystr = 'dad'

Reverse_str = reversed(Mystr)


if list(Mystr) == list(Reverse_str):
   print("The string is a palindrome.")
else:
   print("The string is not a palindrome.")

Note : List() and Reverse() both function in Class XI

Read a file line by line and print it in(Python) ?


with open('myfile.txt', 'r') as file:
    for Multiplelines in file:
        print(line)

or


with open('myfile.txt', 'r') as file:
    lines = file.readlines()
    for line in lines:
        print(line)

Write a program to count total number of character,total number of alphabet,total number of digits,total number of special character?


data = "kul@Si8#CH&T"
countAlphabet = 0
SpecialCharacter = 0
digit = 0
for i in data:
	if(i.isalpha()):
		countAlphabet += 1
	elif(i.isdigit()):
		digit += 1
	else:
		SpecialCharacter += 1


print("Total Characters ",len(data))
print("Total Alphabet ",countAlphabet)
print("Total digit ",digit)
print("Special characters ",SpecialCharacter)

Write a menu driven Python program to implement a stack having names of student


mystack = []
def AddStudent():
 a = input("Enter Item to be added ")
 mystack.append(a)
 print("Item added successfully")


while True:
   print('STACK PROGRAM')
   print('1. Add item')
   choice = int(input("Enter your choice "))
   if choice == 1:
     AddStudent()
   else:
     print("Invalid entry")

Q.1 Write a menu driven Python program based on table-HOSPITAL

No.	Name	Age	Department	Date of Admission	Charges	Sex
1	Sandeep	54	Surgery	23/07/1991	400	M
2	Ravina	24	Orthopedic	20/01/1998	200	F
3	Arvind	45	Orthopedic	23/12/1997	200	M
4	Taruna	12	Surgery	01/01/1998	300	F
5	Gurpreet	36	ENT	12/01/1998	250	M
6	Ketaki	26	ENT	12/02/1998	300	F
7	Seema	29	Cardiology	20/02/1998	800	F
8	Sonam	45	Gyneocology	22/02/1998	400	F
9	Ravindra	29	Cardiology	13/01/1997	800	M
3	Shaily	31	Medicine	19/02/1997	400	F

Q.A Write down the SQL Queries of the following :-

Display all the records whose sex is Male

select * from HOSPITAL where sex = 'M';

Display all the records whose Department is orthopedic

select * from HOSPITAL where Department = 'Orthopedic';

Display all the records where charges are greater than and equal to Rs.400

select * from HOSPITAL where charges >= 400;

Find the Max(),Min(),Sum(),Avg() and count() for column charges

select MAX(charges) from HOSPITAL;
select MIN(charges) from HOSPITAL;
select AVG(charges) from HOSPITAL;
select count(charges) from HOSPITAL;

Write a sql query to show the charges in descending order

select * from HOSPITAL order by charges desc;

select name, charges and Department from the table using group by department

select name,charges,department from hospital group by department;

Write a SQL query to show name,age,Date of Admission whose name start with letter 'S'

select name,age,doa from hospital where name like 's%';

Write a SQL query to update the record of 'Taruna' to 'Taruna Jain' and Age is 24

update hospital set name='Taruna sharma',age = 24 where no = 4;

Write a SQL query to delete the record of 'Ketaki'

delete from hospital where name = 'Ketaki';

Q.2 Write a menu driven Python program based on table-Employee

EmpNo	EName	Job	Hiredate	Salary	DeptNo
7369	Aaradhya	Manager	17/12/2000	12000	5
7499	Atul	Clerk	20/02/2001	6000	10
7521	Sunil	Salesman	20/02/2001	8000	5
7566	Rajesh	Clerk	02/04/2001	6500	15
7564	Gurpreet	Manager	28/09/2001	1150	10
7598	Nand lal	Salesman	01/05/2001	8900	15
7782	Aakash	Salesman	09/12/2002	9200	10
7839	Ravindra	Manager	17/11/2001	13000	NULL
7876	Mithlesh	Clerk	03/12/2001	7000	15
7900	Aamir	Salesman	23/01/2002	8900	15

Q.Write down the SQL Queries of the following :-

List the name of the employee whose empno are 7369, 7521,7698,7782
```
SELECT * from employee where empno = 7369;
or
SELECT * from employee where empno = 7521;
or
SELECT * from employee where empno = 7698;
or
SELECT * from employee where empno = 7782;
```
List the employee names whose name is having 'a' as second character.

SELECT * FROM employee WHERE ename LIKE '_a%';

List the employee names whose name contains 'es' anywhere in the string

SELECT Ename FROM EMPLOYEE WHERE Ename like '%es%';

List the employee whose with job using DISTINCT clause

SELECT DISTINCT job from employee;

List the employee whose salary is 8900 and deptno is 15

SELECT * from employee where salary = 8900 and deptno = 15;

List the employee whose salary is greater than 6000 and salary is less than 10000

select * from employee where salary >= 6000 and salary < 10000 ;

List the employee whose Empno is 7564 or 7522 or 7611

select * from employee where empno = 7564 OR empno =7522 OR empno = 7611;

List the employee whose deptno is NULL

SELECT * FROM EMPLOYEE WHERE deptno = 'D01' is Null;

Q.3 Write a menu driven Python program based on table-Empsalary

Table - Employees

empid	firstname	lastname	address	city
010	harish	sharma	ibm colony	Udaipur
106	vijay	gaur	shastrinagar	ajmer
163	ravindra	dadhich	vaishalinagar	Jaipur
215	gurpreet	singh	naisarak	ganganagar
244	arvind	sharma	namstey chowk	jodhpur
670	ahemad	khan	check point	Mumbai
555	vandna	thompson	cnt. Road	delhi
460	pawan	winy	lalchand marg	alwar
440	peter	same	near charch	kota

Table - Empsalary

Empid	salary	benefits	designation
010	7500	12000	manager
106	6000	10000	manager
163	3700	25000	director
215	5000	12300	manager
244	5500	11000	clerk
670	4000	3000	clerk
555	2000	10000	clerk
460	3200	7500	salesman
440	2800	12800	salesman

Q.Write down the SQL Queries of the following :-

To display firstname, lastname ,address and city of all employee living kota

SELECT firstname,lastname,address,city from employee where city = 'kota';

Display firstname, address,city,benefits

select firstname,address,city,benefits from employees, empsalary WHERE employees.empid = empsalary.empid;

Display firstname, address,city,benefits where Empid = 555

select firstname,address,city,benefits from employees JOIN empsalary ON employees.empid = empsalary.empid WHERE employees.empid = 555;

Display firstname, Sum of (salary + benefits) where Empid = 215

select firstname,(salary+benefits) as 'Total' from employees JOIN empsalary ON employees.empid = empsalary.empid WHERE employees.empid = 215;

Count number of people who have designation manager

select count(*) from empsalary where designation = 'manager';

select records from table empsalary using group by clause


SELECT * from empsalary GROUP by designation;

Count Number of records according to the designation


SELECT designation,count(*) as 'Designation' from empsalary GROUP by designation;

Show the Increased salary by 5 percent for all the members those who have clerk


SELECT salary,((salary*5)/100) as 'Increment salary' from empsalary where designation = 'clerk';