CRUD Operations on Relational Data via SQL

Sunday, October 5, 1997

The original post has been migrated to a modern tech stack, transferred from http://iris.nyit.edu/~mmallari/ (sunsetted), and refreshed with newer data and references. Image Credit: https://unsplash.com/photos/Y9kOsyoWyaU

Structured Query Language (SQL, aka “sequel”) is a standard language for managing relational databases. It remains relevant due to its simplicity, flexibility, and widespread adoption. SQL allows users to efficiently interact with databases, retrieve, update, and manage data, making it a crucial tool for businesses and developers in handling structured information effectively.

Let’s perform CRUD operations on X database on MySQL.

Note: MySQL was created by Michael Widenius in 1995 as an open-source relational database management system (RDBMS). It gained popularity for web applications and became widely used in the early 2000s. Despite competition, it remains relevant due to its simplicity, performance, scalability, and active community support for modern web development and enterprise solutions

Getting Started

SELECT * FROM teams

Table 1: Displaying records 1 - 10
yearID	lgID	teamID	franchID	divID	Rank	G	Ghome	W	L	DivWin	WCWin	LgWin	WSWin	R	AB	H	X2B	X3B	HR	BB	SO	SB	CS	HBP	SF	RA	ER	ERA	CG	SHO	SV	IPouts	HA	HRA	BBA	SOA	E	DP	FP	name	park	attendance	BPF	PPF	teamIDBR	teamIDlahman45	teamIDretro
1871	NA	BS1	BNA	NA	3	31	NA	20	10	NA	NA	N	NA	401	1372	426	70	37	3	60	19	73	16	NA	NA	303	109	3.5	22	1	3	828	367	2	42	23	243	24	0.83	Boston Red Stockings	South End Grounds I	NA	103	98	BOS	BS1	BS1
1871	NA	CH1	CNA	NA	2	28	NA	19	9	NA	NA	N	NA	302	1196	323	52	21	10	60	22	69	21	NA	NA	241	77	2.8	25	0	1	753	308	6	28	22	229	16	0.83	Chicago White Stockings	Union Base-Ball Grounds	NA	104	102	CHI	CH1	CH1
1871	NA	CL1	CFC	NA	8	29	NA	10	19	NA	NA	N	NA	249	1186	328	35	40	7	26	25	18	8	NA	NA	341	116	4.1	23	0	0	762	346	13	53	34	234	15	0.82	Cleveland Forest Citys	National Association Grounds	NA	96	100	CLE	CL1	CL1
1871	NA	FW1	KEK	NA	7	19	NA	7	12	NA	NA	N	NA	137	746	178	19	8	2	33	9	16	4	NA	NA	243	97	5.2	19	1	0	507	261	5	21	17	163	8	0.80	Fort Wayne Kekiongas	Hamilton Field	NA	101	107	KEK	FW1	FW1
1871	NA	NY2	NNA	NA	5	33	NA	16	17	NA	NA	N	NA	302	1404	403	43	21	1	33	15	46	15	NA	NA	313	121	3.7	32	1	0	879	373	7	42	22	235	14	0.84	New York Mutuals	Union Grounds (Brooklyn)	NA	90	88	NYU	NY2	NY2
1871	NA	PH1	PNA	NA	1	28	NA	21	7	NA	NA	Y	NA	376	1281	410	66	27	9	46	23	56	12	NA	NA	266	137	5.0	27	0	0	747	329	3	53	16	194	13	0.84	Philadelphia Athletics	Jefferson Street Grounds	NA	102	98	ATH	PH1	PH1
1871	NA	RC1	ROK	NA	9	25	NA	4	21	NA	NA	N	NA	231	1036	274	44	25	3	38	30	53	10	NA	NA	287	108	4.3	23	1	0	678	315	3	34	16	220	14	0.82	Rockford Forest Citys	Agricultural Society Fair Grounds	NA	97	99	ROK	RC1	RC1
1871	NA	TRO	TRO	NA	6	29	NA	13	15	NA	NA	N	NA	351	1248	384	51	34	6	49	19	62	24	NA	NA	362	153	5.5	28	0	0	750	431	4	75	12	198	22	0.84	Troy Haymakers	Haymakers’ Grounds	NA	101	100	TRO	TRO	TRO
1871	NA	WS3	OLY	NA	4	32	NA	15	15	NA	NA	N	NA	310	1353	375	54	26	6	48	13	48	13	NA	NA	303	137	4.4	32	0	0	846	371	4	45	13	218	20	0.85	Washington Olympics	Olympics Grounds	NA	94	98	OLY	WS3	WS3
1872	NA	BL1	BLC	NA	2	58	NA	35	19	NA	NA	N	NA	617	2571	753	106	31	14	29	28	53	18	NA	NA	434	166	2.9	48	1	1	1548	573	3	63	77	432	22	0.83	Baltimore Canaries	Newington Park	NA	106	102	BAL	BL1	BL1

References

DeBarros, A. (2022). Practical SQL (2^nd ed.). No Starch Press.
Grippa, V. M., & Kuzmichev, S. (2021). Learning MySQL (2^nd ed.). O’Reilly.
Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (3^rd ed.). McGraw Hill.
Beaulieu, A. (2020). Learning SQL (7^th ed.). McGraw Hill.
Lemahieu, W., vanden Broucke, S., & Baesens, B. (2018). Principles of Database Management: The Practical Guide to Storing, Managing and Analyzing Big and Small Data. Cambridge University Press. https://doi.org/10.1017/9781316888773
Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2007). Modern Database Management (8^th ed.). Pearson.

Applied Computing