I have needed to learn more about SQL for a while. I’ve installed SQL Express and SSMS 20 at home, and I’m building a database to help me track reloading data. <\/p>\n\n\n\n
I used ChatGPT to help me design the structure, combining what it already knows about reloading with my personal preferences. (AI tip… don’t explain to it what it already knows!) I tried to type the queries, rather than copy and paste, as that’s how I learn best. (I heard an argument that you should copy and paste for efficiency’s sake, and I agree with that unless I’m actually trying to learn.) <\/p>\n\n\n\n
The first mistake I made was getting caught up in the tables and forgetting to make the database! Creating the database was easy:<\/p>\n\n\n\n
CREATE DATABASE ReloadingDB;\nGo<\/code><\/pre>\n\n\n\nUSE ReloadingDB;\nGO<\/code><\/pre>\n\n\n\nI then created the first table, Calibers. This table has only two fields. <\/p>\n\n\n\n
CREATE TABLE Caliber (\n CaliberID INT PRIMARY KEY AUTO-INCREMENT,\n CaliberName VARCHAR(50) NOT NULL\n);<\/code><\/pre>\n\n\n\nThe idea was that the Caliber ID would be automatically created and incremented, but it turns out that SQL Express needs a slightly different terminology for that:<\/p>\n\n\n\n
CaliberID INT PRIMARY KEY IDENTITY(1,1)<\/code><\/pre>\n\n\n\nMy understanding is that (1,1) means start at one, increment by one. <\/p>\n\n\n\n
In hindsight, I’m not sure that having CaliberID be the Primary Key was the best move. We’ll see if I pay for that later when doing joins. I think using Caliber itself as the Primary Key might have prevented duplication. But, there may be other ways to do that and other reasons to have an “ID” field that I don’t know about yet. <\/p>\n\n\n\n
At this point I began adding data to the table. I used this:<\/p>\n\n\n\n
INSERT INTO Caliber (CaliberName) VALUES ('9mmm Luger'); <\/code><\/pre>\n\n\n\nBut I didn’t want to do this over and over, so I asked ChatGPT to help me import the calibers from a .TXT. I had some real trouble with that! I must have missed where you select the destination table, and it just created a new table. (I might consider using that for creating a table later… I figure a lot of DBs probably start out as spreadsheets anyway) <\/p>\n\n\n\n
I then tried to copy the contents of the new table back to the Caliber table. I’m not sure if I did it wrong or if ChatGPT steered me wrong, so I decided that it wasn’t helping me to learn by wondering which it was. I removed BOTH tables with the below query, and started over. (Not much time was invested, and I thought the repetition would be more valuable at this point.)<\/p>\n\n\n\n
DROP TABLE Caliber; <\/code><\/pre>\n\n\n\nI then built back the Caliber table. This time I decided not to give up on a quick way to enter calibers, but I didn’t want to try the import again. I asked ChatGPT about any sort of “Powershell-like” way to create a variable, so I could just change that and re-run the query to add data. ChatGPT suggested this: <\/p>\n\n\n\n
DECLARE @CaliberName NVARCHAR(50);\nSET @CaliberName = '9mm';\n\nINSERT INTO Caliber (CaliberName)\nVALUES (@CaliberName);\n<\/code><\/pre>\n\n\n\nHowever, ChatGPT also suggested an actual Powershell script, and I decided that was a much better option. I had already been thinking about some kind of “front end” application for this DB, and since I understand Powershell, I figured that’d be a good place to start. (I’ll not cover the details of that in this post… trying to stay on task with SQL! Python is also calling my name for this…)<\/p>\n\n\n\n
At this point, I started to wonder were all SQL “commands” called “queries,” and ChatGPT told me that either would be understood but there are differences. I decided not to get into them at this point, but I wanted to put that in here to remind me to look into it later.<\/p>\n\n\n\n
I decided to move on to the Bullet table next. Creating the table I had to deal with a new type of variable. The diameter will always be a decimal between 0 and .5, and always measured in thousands of an inch. <\/p>\n\n\n\n
Diameter DECIMAL(3,0)<\/code><\/pre>\n\n\n\nEvidently this format says “this is a three digit number where 0 of the digits are right of the decimal point.” EDIT: Apparently I did this backwards… will have to fix. <\/strong><\/p>\n\n\n\nSimilarly, the weight measured in grains will always be a whole number:<\/p>\n\n\n\n
BulletWeight DECIMAL(3,0)<\/code><\/pre>\n\n\n\nBoth of the diameter and bullet weight fields are also set to NOT NULL, but there’s also a field for type that can be null. <\/p>\n\n\n\n
Inserting data into this table made more sense to me than how I was inserting data into the Caliber table. (Without Powershell. I’ll make a Powershell function for this later.) In this case, the values were kind of in an array, I guess:<\/p>\n\n\n\n
INSERT INTO Bullet (Diameter, BulletWeight, BulletType) \nVALUES\n\t(.223, 55, 'FMJ'),\n\t(.264, 123, 'SST'),\n\t(.264, 123, 'OTM'),\n\t(.308, 150, 'Interlock'),\n\t(.223, 55, 'FMJ'),\n\t(.310, 123, 'PSP');\n<\/code><\/pre>\n\n\n\nI’ll continue on with some other tables in the future, and I hope to create some views that will bring the load data together for me. <\/p>\n","protected":false},"excerpt":{"rendered":"
I have needed to learn more about SQL for a while. I’ve installed SQL Express and SSMS 20 at home, and I’m building a database to help me track reloading…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1902","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"aioseo_notices":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"post-thumbnail":false,"newspaperly-grid":false,"newspaperly-slider":false,"newspaperly-small":false},"uagb_author_info":{"display_name":"admin","author_link":"https:\/\/pelnar.tech\/?author=1"},"uagb_comment_info":3,"uagb_excerpt":"I have needed to learn more about SQL for a while. I’ve installed SQL Express and SSMS 20 at home, and I’m building a database to help me track reloading...","_links":{"self":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts\/1902","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1902"}],"version-history":[{"count":3,"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts\/1902\/revisions"}],"predecessor-version":[{"id":1909,"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts\/1902\/revisions\/1909"}],"wp:attachment":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1902"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}