Tech Study

More sqlite Examples / Practice Programs

All SQLite Basic Select Statements with example
All SQLite Basic Subquery Statements with example
All SQLite Restricting & Sorting statement with example
All SQLite Aggregate Functions statement with an example
Write a query to get the difference between the highest and lowest adminssion fee
Write a query to get the number of students with the same branch
Write a query to get the highest, lowest, sum, and average admission fee of all students
Write a query to get the total admission fee from student table
Write a query to list the number of Branches available in the student table
Write a query to get the maximum admission fee of an student studying in Information technology branch
Write a query to get the average admission fee and number of students studying in the Chemical engineering
Write a query to display the Full name, Branch, and fee for all students whose branch id is 200 and 500, and whose fee is not equal to 10,000, 14,000, 23000
Write a query to display the first name of all students who have both an “a” and “e” in their first name
Write a query to display the Full name & admission fee for all students whose fee is not in the range 10,000 through 20,000 and are in branch 400 or 500
Write a query to display the Full name and Branch ID of all students in branch 300 or 600 in ascending order by branch ID
Write a query to display the Full name and Admission Fee of all students whose Fee is not in the range 11,000 through 15,000
Write a query to display the full name, Admission Fee and scholarship (15% of Admission Fee) of all students
Write a query to display the first names of students having ‘a’ as the 2nd character
Write a query to display the first names of students whose names have exactly 6 characters
Write a query to get nth max admission fee of student
Write a query to get 4 minimum admission fees
Write a query to get 4 maximum admission fees
Write a query to select last 5 records from a table
Write a query to find the 4th minimum admission fee in the student table
Write a query to find the 5th maximum admission fee in the student table
Write a query to find the full name and fee of the students who have a higher salary than the student whose last_name=’smith’
Write a query to find the student names, admission fee, whose admission fee is greater than the average admission fee
Write a query to get the length of the students names from student table
Write a query to get monthly fee of each and every student
Write a query to calculate 401*14+1025-500
Write a query to get the first 3 characters of first name from student table
Write a query to get the Fullname from student table
Write a query to get first name from students table after removing white spaces from both side
Write a query to get the total admission fee from student table
Write a query to get the number of students studying in the college
Write a query to get the number of students studying in the college
Write a query to get the number of unique branch available in the student table
Write a query to get the names (first_name, last_name), admission_fess, Scholarship of all the students(Scholarship is calculated as 10% of admission_fess)
Write a query to get unique branch ID from student table
Write a query to get all students details from tbl_student table order by first name, ascending
Write a query to get all students details from tbl_student table order by first name, descending
Write a query to display the Student names (first_name, last_name) using alias name “First Name”, “Last Name”

Write a query to get the average admission fee for all branches containing more than 1 students

Introduction

The following exercise will help you to improve your SQLite query skills effectively. I have used DB Browser for sqlite for following exercise.

student_id	first_name	last_name	email	mobile_number	admission_date	admission_fee	branch	branch_id
1.	Mike	Brown	mike@example.com	875695632	12-06-2013	15000	Computer Science	100
2	Chris	Lee	chris@example.com	789652369	05-06-2012	18000	Computer Science	100
3	David	Jones	David@example.com	965412369	01-07-2013	14000	Mechanical Engineering	200
4	Michael	Johnson	Michael@example.com	845632136	22-08-2014	25000	Mechanical Engineering	200
5	Tom	Cruise	Tom@example.com	789563245	09-06-2013	15000	Civil Engineering	300
6	Mary	Smith	mary@example.com	846523698	10-07-2014	23000	Electrical engineering	400
7	James	Johnson	james@example.com	896523145	11-02-2015	25000	Chemical engineering	600
8	Robert	Smith	robert@example.com	984562368	25-06-2013	22000	Information technology	500
9	Maria	Garcia	maria@example.com	895641236	12-08-2012	14500	Civil Engineering	300
10	Brett	Lee	brett@example.com	756984562	20-06-2013	15000	Chemical engineering	600
11	James	Anderson	james@example.com	895621455	15-07-2018	35000	Information technology	500
12	Joe	Root	joe@example.com	985641236	22-08-2017	40000	Chemical engineering	600

SELECT branch, AVG(admission_fee), COUNT(*) 
FROM tbl_student 
GROUP BY branch_id
HAVING COUNT(*) > 1

Result

Write a query to get the average admission fee for all branches containing more than 1 students

Python Examples

Introduction: Python Examples are the basic programming concepts of python like python syntax,python data types,,python operators,python if else,python comments etc.. …

C String Functions

C String Functions perform certain operations, It provides many useful string functions which can come into action. The <string.h> header …