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;