Kaleb Essig

Skill Showcase

This page is to showcase some of my skills related to databases. In particular, I will be show the basics of running commands in a database such as creating a table, inserting data and querying this information.

Creating Tables and Inserting Data:

The first step in creating a functioning database is to create a table. Tables are where data is stored and can be queried from. Key components of a database include:

Determining table name (in this case we are using users and info)
Column name (indicated in the parentheses, such as id or first_name)
Data types (followed after column name - examples are varchar and int)

Code:


                        CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name varchar NOT NULL , last_name varchar NOT NULL , age int (3), location char(2), favorite_food varchar);


                        CREATE TABLE info (idNum NOT NULL, car_manufact VARCHAR, car_type VARCHAR, car_color VARCHAR, FOREIGN KEY (idNum) REFERENCES users (id));

Output:

After the above code is ran, we can see the table is created with columns, however, no data:

Now that the tables are created, we can now insert data into the tables:

Code:


                        INSERT INTO users (first_name, last_name, age, location, favorite_food)
                        VALUES('Joe', 'Smith','21','CO','Pizza'),('George', 'Allen','35','CO','Pasta'),('Josh', 'Corbin','42','TX','BBQ'),('Hazel', 'Roth','25','CA','Sushi'),('Rose', 'Smith','27','AL','Pizza'),('Jonnie', 'Adams','39','CA','BBQ');


                        INSERT INTO info (idNum, car_manufact, car_type, car_color) VALUES('1', 'Honda','Sedan','Black'),('2', 'Honda','Coupe','Red'),('3', 'Mazda','SUV','Red'),('4', 'Ford','Truck','Grey'),('5', 'Dodge','Minivan','Blue'),
                        ('6', 'Mitsubishi','Coupe','Orange');

In order to see if the data successfully was inserted into the tables, we can use the following code to query the data:


                        SELECT * FROM users;
                        
SELECT * FROM info;

This will now show the output of our inserted data:

Output:

You can also use clauses and logical operators to filter your query searches. Below, I have provided some examples.

WHERE and LIKE clause

These clauses can help narrow down our query. The WHERE clause is our identifier and the LIKE clause helps find matchs.


                        SELECT * FROM users WHERE first_name LIKE 'J%';

This SQL script will show us all first_names that start with the letter "J"

Output:

Logical Operators

Logical operators can also be used to filter our queries. Below, I will be using the less than logical operator to filer our search.

Code:


                        SELECT * FROM users WHERE age < '30';

This SQL script shows us all individuals who has an age less than 30.

Table Joines

The last item I am showing is how to join tables together. This is important when trying to match data together.

Code:


                        SELECT id, first_name AS FirstName, last_name AS LastName, car_manufact, car_type, car_color  FROM users INNER JOIN info ON info.idNum = users.id;

Output:

As one can see, we have joined both the users and info table, based on the criteria listed in the SELECT clause.