
import sqlite3


con = sqlite3.connect('school.sqlite')
cur = con.cursor()

q = 'select * from faculty'
cur.execute(q)

for row in cur.fetchall():
    print(row)

('98005', 'Suzanne', 'Viescas', '15127 NE 24th, #383', 'Redmond', 'WA', '98052', '425', '555-2686', '44000', '1986-05-31 00:00:00', 'Instructor', 'Full time', 'True')
('98007', 'Gary', 'Hallmark', 'Route 2, Box 203B', 'Auburn', 'WA', '98002', '253', '555-2676', '53000', '1985-01-21 00:00:00', 'Associate Professor', 'Full time', 'True')
('98010', 'Jeffrey', 'Smith', '30301 - 166th Ave. N.E.', 'Fremont', 'CA', '94538', '510', '555-2596', '52000', '1983-10-06 00:00:00', 'Associate Professor', 'On leave', 'True')
('98011', 'Ann', 'Patterson', '16 Maple Lane', 'Auburn', 'WA', '98002', '253', '555-2591', '45000', '1983-10-16 00:00:00', 'Instructor', 'Full time', 'True')
('98012', 'Michael', 'Davolio', '672 Lamont Ave', 'Houston', 'TX', '77201', '713', '555-2491', '49000', '1989-02-09 00:00:00', 'Instructor', 'Full time', 'True')
('98013', 'Ann', 'Fuller', '908 W. Capital Way', 'Tacoma', 'WA', '98413', '253', '555-2496', '44000', '1986-07-05 00:00:00', 'Instructor', 'Full time', 'True')
('98014', 'James', 'Leverling', '722 Moss Bay Blvd.', 'Kirkland', 'WA', '98033', '425', '555-2501', '60000', '1986-07-16 00:00:00', 'Professor', 'Full time', 'True')
('98019', 'Laura', 'Callahan', '901 Pine Avenue', 'Portland', 'OR', '97208', '503', '555-2526', '45000', '1989-11-02 00:00:00', 'Instructor', 'Full time', 'True')
('98020', 'Albert', 'Buchanan', '13920 S.E. 40th Street', 'Bellevue', 'WA', '98009', '425', '555-2531', '45000', '1985-08-02 00:00:00', 'Instructor', 'Full time', 'True')
('98025', 'Janet', 'Leverling', '722 Moss Bay Blvd.', 'Kirkland', 'WA', '98033', '425', '555-2576', '50000', '1984-04-12 00:00:00', 'Associate Professor', 'Full time', 'True')
('98028', 'Alaina', 'Hallmark', 'Route 2, Box 203B', 'Woodinville', 'WA', '98072', '425', '555-2631', '57000', '1984-01-17 00:00:00', 'Professor', 'Full time', 'True')
('98030', 'Amelia', 'Buchanan', '13920 S.E. 40th Street', 'Bellevue', 'WA', '98006', '425', '555-2556', '48000', '1988-05-31 00:00:00', 'Instructor', 'Full time', 'True')
('98036', 'John', 'Leverling', '611 Alpine Drive', 'Palm Springs', 'CA', '92263', '760', '555-2611', '60000', '1982-11-20 00:00:00', 'Professor', 'Full time', 'True')
('98040', 'David', 'Callahan', '101 NE 88th', 'Salem', 'OR', '97301', '503', '555-2636', '50000', '1987-01-13 00:00:00', 'Associate Professor', 'Full time', 'True')
('98042', 'David', 'Smith', '311 20th Ave. N.E.', 'Fremont', 'CA', '94538', '510', '555-2646', '52000', '1991-12-17 00:00:00', 'Associate Professor', 'Full time', 'True')
('98045', 'Michael', 'Hernandez', 'PO Box 223311', 'Tacoma', 'WA', '98413', '253', '555-2711', '60000', '1990-08-20 00:00:00', 'Professor', 'Full time', 'True')
('98048', 'Joyce', 'Bonnicksen', '2424 Thames Drive', 'Bellevue', 'WA', '98006', '425', '555-2726', '60000', '1986-03-02 00:00:00', 'Professor', 'Full time', 'True')
('98052', 'Katherine', 'Ehrlich', '777 Fenexet Blvd', 'Redmond', 'WA', '98052', '425', '555-0399', '45000', '1985-03-08 00:00:00', 'Instructor', 'Part time', 'False')
('98053', 'Gregory', 'Piercy', '4501 Wetland Road', 'Long Beach', 'CA', '90809', '562', '555-0037', '45000', '1992-02-10 00:00:00', 'Instructor', 'Full time', 'True')
('98055', 'Alastair', 'Black', '3887 Easy Street', 'Seattle', 'WA', '98125', '206', '555-0039', '60000', '1988-12-11 00:00:00', 'Professor', 'Full time', 'True')
('98059', 'Consuelo', 'Maynez', '3445 Cheyenne Road', 'El Paso', 'TX', '79993', '915', '555-2291', '48000', '1986-09-17 00:00:00', 'Instructor', 'Full time', 'True')
('98062', 'Caroline', 'Coie', '298 Forest Lane', 'Seattle', 'WA', '98125', '206', '555-2306', '52000', '1983-01-28 00:00:00', 'Associate Professor', 'Full time', 'False')
('98063', 'Ryan', 'Ehrlich', '455 West Palm Ave', 'San Antonio', 'TX', '78284', '210', '555-2311', '45000', '1988-03-02 00:00:00', 'Instructor', 'Full time', 'True')
('98064', 'Allan', 'Davis', '877 145th Ave SE', 'Portland', 'OR', '97208', '503', '555-2316', '56000', '1989-08-20 00:00:00', 'Professor', 'Full time', 'True')


import pandas as pd


con = sqlite3.connect('school.sqlite')
def x(q):
    return pd.read_sql_query(q, con)


x('select * from faculty Limit 6')


q = 'select count(title) from faculty'
x(q)


q = 'select count(distinct title) from faculty'
x(q)


q = 'select count(distinct title) as ranks from faculty'
x(q)


x('select avg(salary) from faculty')


x('select * from student_schedules')


x('select * from class_sections')


x('select * from courses')


#available tables are buildings, class_rooms, class_sections, course_categories, courses, departments, 
#faculty, faculty_categories, faculty_courses, faculty_sections, student_schedules, students
q = 'select * from class_sections natural join courses where categoryid = "MAT"'
x(q)


q = 'select * from class_sections natural join courses'
x(q)


q = 'select avg(credits), avg(duration) from class_sections natural join courses where categoryid = "MAT"'
x(q)


q = 'select avg(credits), avg(duration) from class_sections natural join courses where categoryid = "ART"'
x(q)


q='select categoryid, avg(credits) as mean_credits from class_sections natural join courses group by categoryid'
df=x(q)
df


import seaborn as sns


sns.catplot(y='CategoryID', x='mean_credits', data=df, kind='bar')

<seaborn.axisgrid.FacetGrid at 0x23094873d88>


q = 'select * from faculty_courses'
df = x(q)
df


#save graph as an image file
#import matplotlib.pylot as plt
plt.savefig('filename.png')
#changing the file extension will change the format of the file save


#to install sqlite3 in Anaconda
pip install sqlite3
conda install sqlite3

	StudentID	SectionNumber	Grade
0	1001	1560	93.28
1	1001	2071	66.41
2	1001	4055	62.85
3	1001	4760	73.21
4	1001	4812	66.91
...	...	...	...
84	1017	4768	85.26
85	1018	1031	87.05
86	1018	1180	69.88
87	1018	1502	67.13
88	1018	4768	81.23

	CourseID	CategoryID	CourseCode	CourseName	CourseDescription
0	1	ACC	ACC 210	Financial Accounting Fundamentals I	Introduces basic accounting concepts, principl...
1	2	ACC	ACC 220	Financial Accounting Fundamentals II	Applications of basic accounting concepts, pri...
2	3	ACC	ACC 230	Fundamentals of Managerial Accounting	Analysis of accounting data as part of the man...
3	4	ACC	ACC 251	Intermediate Accounting	In-depth review of financial accounting princi...
4	5	ACC	ACC 257	Business Tax Accounting	Basic principles, practices and governmental r...
5	6	BUS	BUS 101	Introduction to Business	Survey of businss practices. Covers business t...
6	7	BUS	BUS 155	Developing A Feasibility Plan	With the aid of a counselor, a feasibility pla...
7	8	BUS	BUS 151	Introduction to Enterpreneurship	Overview of the entrepreneurial process, exami...
8	9	BUS	BUS 170	Information Technology I	Uses Word for Windows word processing skills, ...
9	10	BUS	BUS 171	Information Technology II	Uses intermediate Word features including form...
10	11	ART	ART 100	Introduction to Art	Historical backgrounds and design fundamentals...
11	12	ART	ART 101	Design	Studio sudies in the fundamentals of two-dimen...
12	13	ART	ART 111	Drawing	Study of line, value, space, perspective, and ...
13	14	ART	ART 201	Painting	Beginning painting in oil or synthetic media u...
14	15	ART	ART 210	Computer Art	Explore the elements of art such as line, valu...
15	16	ART	ART 251	Art History	Surveys major forms of visual expression from ...
16	17	BIO	BIO 100	Biological Principles	An introductory biology course with lab for th...
17	18	BIO	BIO 101	General Biology	Basic bilogical concepts with emphasis on gene...
18	19	BIO	BIO 280	Microbiology	Introduction to micro-organisms including micr...
19	20	CHE	CHE 101	Chemistry	General chemistry for non-science majors. Comp...
20	21	CHE	CHE 139	Fundamentals of Chemistry	Prepatory for the science major chemistry cour...
21	22	CHE	CHE 231	Organic Chemistry	Structure, nomenclature, reactions, and synthe...
22	23	CIS	CIS 101	Microcomputer Applications	This is a "hands-on" course. Students will lea...
23	24	CIS	CIS 102	Information Systems Concepts	Provides a broad introduction to computers and...
24	25	CIS	CIS 114	Problem Solving and Structured Programming	Covers design, documentation, and coding of pr...
25	26	CIS	CIS 236	Database Management	Includes database concepts, data management te...
26	27	CSC	CSC 110	Programming in BASIC	Computer programming and program design using ...
27	28	CSC	CIS 142	Computer Programming	Introduction to computer science using the C p...
28	29	JRN	JRN 104	College Publications	Hands-on course in college publishing. Covers ...
29	30	ECO	ECO 100	Survey of Economics	Economics applied to various comtemporary soci...
30	31	ECO	ECO 200	Principles of Economics: Microeconomics	Covers resource allocation and income distribu...
31	32	ECO	ECO 201	Principles of Economics: Macroeconomics	Analysis of the aggregate economy: GDP, inflat...
32	33	MUS	MUS 100	Music in the Western World	An introduction to music. Features music from ...
33	34	MUS	MUS 101	First Year Theory and Ear Training	Rudiments of music - notation, scales, interva...
34	35	MUS	MUS 201	Second Year Music Theory	Continuation of MUS 101. Chromatic harmony, mo...
35	36	MUS	MUS 204	History of Jazz	Traces the roots of jazz in America from New O...
36	37	ENG	ENG 101	Composition - Fundamentals	Introduces the nature of the writing process i...
37	38	ENG	ENG 102	Composition - Intermediate	Continues instruction on the writing process, ...
38	39	ENG	ENG 104	Advanced English Grammar	Study of the grammar and rhetoric of the Engli...
39	40	GEG	GEG 100	Introduction to Geography	An introduction to the major cultures of the w...
40	41	GEG	GEG 205	Physical Geography	Study of the Earth, the materials that make it...
41	42	HIS	HIS 101	World History to 1500	Historic foundations and development of the gr...
42	43	HIS	HIS 111	U.S. History to 1877	American history from the colonial period thro...
43	44	HIS	HIS 112	U.S. History Since 1865	Includes Reconstruction, industrialization, ur...
44	45	MAT	MAT 080	Preparatory Mathematics	Individualized instruction in Arithmetic, Alge...
45	46	MAT	MAT 097	Elementary Algebra	First course in Algebra includes signed number...
46	47	MAT	MAT 098	Intermediate Algebra	Sets and the real number system, polynomial an...
47	48	MAT	MAT 103	Geometry and Visualization	Basic plane geometry concepts, emphasizing pro...
48	49	MAT	MAT 104	Trigonometry	Elementary plane goemetry, right triangle tirg...
49	50	PHY	PHY 100	Survey Of Physics	Basic laws of phyics from the laws of motion t...
50	51	PHY	PHY 101	General Physics	Classical mechanics; kinematics and dynamics. ...
51	52	PHY	PHY 201	Engineering Physics I	Development of the basic principles of classic...
52	53	PHY	PHY 203	Engineering Physics II	Waves and oscillations. Mechanical waves and s...
53	54	POL	POL 101	Introduction to Political Science	Introduction to theory, organization, politics...
54	55	POL	POL 102	American Government	Origin and development of the U.S. government....
55	56	POL	POL 213	Women and Politics	Introduction to concepts of power and policy i...

	SectionNumber	CourseID	RoomNumber	Credits	StartTime	Duration	MondaySchedule	TuesdaySchedule	WednesdaySchedule	ThursdaySchedule	FridaySchedule	SaturdaySchedule	CategoryID	CourseCode	CourseName	CourseDescription
0	1000	11	1231	5	1899-12-30 10:00:00	50	True	False	True	False	True	False	ART	ART 100	Introduction to Art	Historical backgrounds and design fundamentals...
1	1002	12	1619	4	1899-12-30 15:30:00	110	True	False	True	False	True	False	ART	ART 101	Design	Studio sudies in the fundamentals of two-dimen...
2	1004	13	1627	4	1899-12-30 08:00:00	50	True	False	True	False	True	False	ART	ART 111	Drawing	Study of line, value, space, perspective, and ...
3	1006	13	1627	4	1899-12-30 09:00:00	110	True	False	True	False	True	False	ART	ART 111	Drawing	Study of line, value, space, perspective, and ...
4	1012	14	1627	4	1899-12-30 13:00:00	170	False	True	False	True	False	False	ART	ART 201	Painting	Beginning painting in oil or synthetic media u...
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
71	4810	23	3319	5	1899-12-30 08:00:00	50	False	True	False	True	False	False	CIS	CIS 101	Microcomputer Applications	This is a "hands-on" course. Students will lea...
72	4812	24	3330	3	1899-12-30 12:00:00	80	False	True	False	True	False	False	CIS	CIS 102	Information Systems Concepts	Provides a broad introduction to computers and...
73	4813	24	3406	3	1899-12-30 10:00:00	50	True	False	True	False	True	False	CIS	CIS 102	Information Systems Concepts	Provides a broad introduction to computers and...
74	4825	25	3319	5	1899-12-30 11:00:00	50	True	True	True	True	True	False	CIS	CIS 114	Problem Solving and Structured Programming	Covers design, documentation, and coding of pr...
75	4880	26	1231	5	1899-12-30 13:30:00	110	True	False	True	False	True	False	CIS	CIS 236	Database Management	Includes database concepts, data management te...

	CategoryID	mean_credits
0	ACC	5.000000
1	ART	4.375000
2	BIO	5.000000
3	BUS	4.000000
4	CHE	4.750000
5	CIS	4.333333
6	CSC	5.000000
7	ECO	5.000000
8	ENG	5.000000
9	GEG	5.000000
10	HIS	5.000000
11	JRN	2.000000
12	MAT	4.666667
13	MUS	4.500000
14	PHY	5.000000
15	POL	5.000000

	FacultyID	FacFirstName	FacLastname	FacStreetAddress	FacCity	FacState	FacZipCode	FacAreaCode	FacPhoneNumber	Salary	DateHired	Title	Status	Tenured
0	98005	Suzanne	Viescas	15127 NE 24th, #383	Redmond	WA	98052	425	555-2686	44000	1986-05-31 00:00:00	Instructor	Full time	True
1	98007	Gary	Hallmark	Route 2, Box 203B	Auburn	WA	98002	253	555-2676	53000	1985-01-21 00:00:00	Associate Professor	Full time	True
2	98010	Jeffrey	Smith	30301 - 166th Ave. N.E.	Fremont	CA	94538	510	555-2596	52000	1983-10-06 00:00:00	Associate Professor	On leave	True
3	98011	Ann	Patterson	16 Maple Lane	Auburn	WA	98002	253	555-2591	45000	1983-10-16 00:00:00	Instructor	Full time	True
4	98012	Michael	Davolio	672 Lamont Ave	Houston	TX	77201	713	555-2491	49000	1989-02-09 00:00:00	Instructor	Full time	True
5	98013	Ann	Fuller	908 W. Capital Way	Tacoma	WA	98413	253	555-2496	44000	1986-07-05 00:00:00	Instructor	Full time	True

	SectionNumber	CourseID	RoomNumber	Credits	StartTime	Duration	MondaySchedule	TuesdaySchedule	WednesdaySchedule	ThursdaySchedule	FridaySchedule	SaturdaySchedule	CategoryID	CourseCode	CourseName	CourseDescription
0	2889	45	2423	5	1899-12-30 09:00:00	50	True	True	True	True	True	False	MAT	MAT 080	Preparatory Mathematics	Individualized instruction in Arithmetic, Alge...
1	2891	45	2423	5	1899-12-30 11:00:00	50	True	True	True	True	True	False	MAT	MAT 080	Preparatory Mathematics	Individualized instruction in Arithmetic, Alge...
2	2895	45	2423	5	1899-12-30 13:00:00	50	True	True	True	True	True	False	MAT	MAT 080	Preparatory Mathematics	Individualized instruction in Arithmetic, Alge...
3	2907	46	3445	5	1899-12-30 08:00:00	50	True	True	True	True	True	False	MAT	MAT 097	Elementary Algebra	First course in Algebra includes signed number...
4	2911	46	3445	5	1899-12-30 12:00:00	50	True	True	True	True	True	False	MAT	MAT 097	Elementary Algebra	First course in Algebra includes signed number...
5	2915	46	3353	5	1899-12-30 10:00:00	50	True	True	True	True	True	False	MAT	MAT 097	Elementary Algebra	First course in Algebra includes signed number...
6	2917	47	3422	5	1899-12-30 14:00:00	50	True	True	True	True	True	False	MAT	MAT 098	Intermediate Algebra	Sets and the real number system, polynomial an...
7	2925	47	3422	5	1899-12-30 15:00:00	50	True	False	True	False	True	False	MAT	MAT 098	Intermediate Algebra	Sets and the real number system, polynomial an...
8	2933	47	3422	5	1899-12-30 09:00:00	240	False	False	False	False	False	True	MAT	MAT 098	Intermediate Algebra	Sets and the real number system, polynomial an...
9	2633	48	3420	5	1899-12-30 16:00:00	50	False	True	False	True	False	False	MAT	MAT 103	Geometry and Visualization	Basic plane geometry concepts, emphasizing pro...
10	2639	49	3353	3	1899-12-30 09:00:00	50	True	False	True	False	True	False	MAT	MAT 104	Trigonometry	Elementary plane goemetry, right triangle tirg...
11	2647	49	3353	3	1899-12-30 18:00:00	50	False	True	False	True	False	False	MAT	MAT 104	Trigonometry	Elementary plane goemetry, right triangle tirg...