SQL Tutorial

 

SQL creates, updates, databases, etc. Pretty much any relational database management system will use SQL as the baseline for how it accesses data for creating, reading, updating, and deleting. Everything defined in SQL is universal in all of the dataase management systems.

A dayabase is a collection of servers separated out into certain table, with many tables, all of these tables linking to each other in order to create connections, and inside the table with different columns and rows. For example, 1 user is 1 record, 2 users are 2 records, etc. Everything else is columns within the data. The ways data is related to each other is how things become a relational database system, it’s a collection of different tables representing different objects inside the data.

SQL deals with data, and data is everywhere. Almost every application has some form of data and databases and SQL is good to store data in large to small scale applications. This is why it’s so crucial to learn SQL because you will have to work with databases and knowing SQL at a strong level will help you significantly to your career. Use MySQL workbench. In order to get files, get the icon top left to create SQL for executing queries. SQL syntax has keywords such as SELECT, WHERE, FROM and highlight in a specific color to distinguish keywords from non-keywords, and it’s not case sensitive, doesn’t matter. SQL uses keywords, table names, and column names in order to string together a different query for example SELECT * FROM table;

SQL has different table names, different column names, and at the end of the SQL name, you need to put a semicolon. Always put a semicolon to end of the SQL statement. Even though keywords are different, it is best practice to write keywords in full uppercase in order to distinguish them from the column names and table names, and use single quotes and put the ‘string’ in the single quotes and establish that I have a string. Let’s create a database for us to use.

Let’s create a database for a record company for a bands, albums and songs inside of it. Let’s create a database for a record company with tables from bands and albums. To get started, we need to create this database.

We’re going to write the create database command, super straightforward, CREATE DATABASE test; Inside of SQL there’s a icon with a lightning bolt, with executing, or you can execute the highlighted, or we can try to execute the statement under the keyboard servers. Once you run, MySQL workbench doesn’t update the UI, but you need to refresh the SCHEMA session in order to repopulate the database. DROP DATABASE test; will delete the database inside of the schema. Dropping the database deletes all the data inside of that database, so this is a command that you’ll almost never use.

Now let’s create the actual database CREATE DATABASE record_company; Now, we can start adding tables in this database and start adding data into those tables. We need to tell SQL we’re going to use that database and type USE and the name of the database we use on or USE record_company; . So if we create tables and add data it’ll add it and create it on the record company database. Now, we can work on creating our first table.

Now we use CREATE TABLE; and the name of the table so CREATE TABLE test;. Tables have columns inside of them that represent the different properties of the object that it’s representing. So inside the parenth          eses, we put the columns that we want in our table and tell type of data.

 

CREATE TABLE test (

                                            Test column INT

);

 

Now refreshing schema, we have the test_table and test_colum which is type Integer. Now let’s say we forgot to add another column in the beginning. We have a command called ALTER TABLE, which will change properties when requested. VARCHAR is a variable length character array with the maximum length it can be.  Adding column does:

 

ALTER TABLE test

ADD another_column VARCHAR(255);

 

SQL doesn’t care about line breaks in the statement, it just reads up to the semicolon. Now we have another column inside the database. NOT NULL means that a column must always have a name defined.

DROP TABLE test will drop the test table.

Let’s work on a table for a band. CREATE TABLE bands (

                                            name VARCHAR(255) NOT NULL

);

Using an id column comes in handy if there are 2 bands with the same name

CREATE TABLE bands (

               id INT NOT NULL AUTO_INCREMENT,

               name VARCHAR(255) NOT NULL

)

AUTO_INCREMENT increments number without us having to do anything. We need a comma in order to separate the id and name column. Id is a primary key, and it is the primary identifying column in the table. We can use PRIMARY KEY keyword with () put what the primary key is, which is the ID column. There will be an index for our primary key.

CREATE DATABASE record_company;

USE record_company;

CREATE TABLE bands (

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(255) NOT NULL,

PRIMARY KEY(id)

);

 

Let’s create album table.

CREATE TABLE albums (

Id NOT NULL AUTO_INCREMENT;

NAME VARCHAR(255)  NOT NULL,

release_year INT,

band_id INT NOT NULL,

PRIMARY KEY(id),

FOREIGN KEY(band_id) REFERENCES bands(id)                                                            

);

We have ID to uniquely identify different columns. Saving the id within the albums table will allow us to access the band table from within the album table. Let’s a band_id with an integer, and we want to make sure that it’s not null, since we don’t want to album to be null. We then need to define the relationship between the band id and the band table. To do this, we use FOREIGN KEY and we put what the key is, and we need to tell the key what table it references like FOREIGN KEY(band_id) REFERENCES band(id) so now we need to get a band name to add a band corresponding to the album. You can’t delete band unless you also delete the album. We call the table band, where we referent the table. Now we have albums table, inside the foreign key, there’s a foreign key linking are album to the band.

SQL is for adding data and reading data. We want to insert into and stuff.

We don’t need to insert id, since it automatically inserts into itself.

INSERT INTO bands (name)

VALUES (‘Iron Maiden’);

INSERT INTO bands (name)

VALUES(‘Deuce’), (‘Avenged Sevenfold’), (‘Anchor’);

and it’ll add 3 different bands to the table.

We can SELECT * FROM bands, which will have both the ID and name column.

If we only want 2 bands we do

SELECT * FROM bands LIMIT 2;

But to get column, we get name column so we do

SELECT name FROM bands;

You can rename the columns. We can change the name as the id looks.

SELECT id AS ‘ID’, name AS ‘BAND Name’

FROM bands;

And the titles for different columns have changed.

 

The last thing with the select statement is that you can order how the elements in the select statement are rendered.

We can select from the bands table and ORDER BY name, so we can now order in alphabetical order of the name or do it in Descending order.

SELECT * from bands ORDER BY name DESC;

To do ascending order, just don’t put DESC.

Now, let’s add in albums to the albums table down below.

INSERT INTO albums (name, release_year, band_id)

VALUES (‘The Number of the Beast’, 1985, 1),

             (‘Power Slave’, 1984, 1),

             (‘Nightmare’, 2018, 2),

            (‘Nightmare’, 2010, 3),

            (‘Test Album’, NULL, 3);

 

Now running this, we’ve actually all these different albums to our albums table.

Now we can do SELECT * FROM albums; with all albums with the release year and the band id that they correspond with.

We can SELECT name FROM albums; and if we run this, we get all the names from the albums in the database.

To get only unique rows, we do

SELECT DISTINCT name FROM albums;

This DISTINCT line compares everything that gets returned so you only get 1 unique row for every single item inside of your database instead of getting duplicates.

To change update year, we need to use UPDATE so

UPDATE albums

SET release_year = 1982

 

Running this right now has release year to 1982, but we only want to update a single element. The WHERE statement can filter down to the actual results being returned.       

 

UPDATE albums

SET release_year = 1982

WHERE id = 1;

Now if we query albums table we see that the number is now 1982 instead of 1985. This WHERE statement can be added whenever you want to filter.

If we want to select the table where release year before 2000

 

SELECT * FROM albums

WHERE release_year < 2000;

 

We can also use wild cards where a certain string is there, so

SELECT * FROM albums

WHERE name LIKE ‘%er%’;

The letters er in order, and any amount of characters after the string. Think about it as It can be anything.

You can also combine different WHERE clauses inside

 

SELECT * FROM albums

WHERE name LIKE ‘%er%’ OR band_Id = 2;

Or we can do AND

 

SELECT * FROM albums

WHERE release_year = 1984 AND band_id = 1;

 

Now 2 more quick ways in how the where statement can be used, such as filtering more values.

SELECT * FROM albums

WHERE release year BETWEEN 2000 AND 2018;

 

We use the BETWEEN keyword and you get the minimum value and the corresponding maximum value.

We can also filter for things that are NULL

 

SELECT * FROM albums

WHERE release_year IS NULL;

 

We can remove data by putting

DELETE FROM albums WHERE id = 5;

 

The JOIN statement allows us to join 2 different tables together on different properties. It allows us to create relations between our data inside of our database.

The most basic join statement is

SELECT * FROM bands

JOIN albums ON bands.id = albums.band_id;

We compare the different values in these rows together and compare them and see if they are equal.

The Iron Maiden is duplicated has 2 albums for the same band. There’s multiple different ways you can join, which you can do INNER JOIN which Is a basic join. There’s also LEFT JOIN and RIGHT JOIN. INNER join only returns values that have a match.

INNER JOIN combines data where there’s both a value on the table of the left(bands, what you write first) and the table on the right (albums), it only returns values that have a match. LEFT JOIN lists everything from the left hand side and will list all of those tables even if they don’t have matching albums, if value is NULL.

LEFT JOIN has all of the bands who don’t have any albums either.

SELECT * FROM bands

LEFT JOIN albums ON bands.id = albums.band_id;

Now we get additional results with  no albums associated with the following band.

A RIGHT JOIN joins on the right side, returns an album that isn’t associated. I will join on the right side, so if there is an album with no band associated, it will perform a RIGHT JOIN.

 

For the most part, we’re going to use inner joins and left joins, and inner joins are useful when there’s  value from left and right. Left values are useful gets everything from left side table and still return the thing from the left side even If it doesn’t return anything in the right side.

The very last function is aggregate, and create a SELECT which uses aggregate function using the average, aggregate of the data.

SELECT AVG(release_year) FROM albums.

If we run this, we get one single row returned to use, for example 1998.5. The aggregate takes the data returned from the select. We can also

SELECT SUM(release_year) FROM albums.

Average, sum, and count are useful.

SELECT band_id, COUNT(band_id) FROM albums;

We want to figure out how many albums each of these band ids, so we need to group by the band id. GROUP_BY takes all the records and squishes them into a table.

SELECT band_id, COUNT(band_id) FROM albums

GROUP BY band_id;

Returns multiple rows since the aggreagates work on band 1, band 2, and band 3, and displays them in the chart, respectively. We can combine GROUP BY and JOIN to make complex and usefule queries inside SQL.

The last query takes what we created and gives us a little bit more meaningful information. We want to know what band something is instead of looking at Id. We copy what we already created and select band name and return this as band_name and get the COUNT of different albums.

SELECT b.name AS band_name, COUNT(a.id) as num_albums

FROM band AS b

LEFT_JOIN albums AS a ON b.id = a.band_id

GROUP BY b.id;

We can see the band name and the number of albums now.

This works very similary to all these statements, but we grouped it all into 1.

The first thing we did was talked about the columns we want to select. We’ve aliased these different column names and we LEFT_JOIN because we don’t get names that don’t have any album records. We then group these by the band id, so we can have unique rows by the different ids. Since the name is the same and id is the same, and the COUNT will tell us how many unique items we have for the band album through the group ID.

What if we want to filter by the aggreagate? All we do is WHERE num_albums = 1?

No this won’t actually work, since where statement happens before GROUP BY and we just needs to have HAVING because having is same as where but after the GROUP BY function.

 

SELECT b.name AS band_name, COUNT(a.id) AS num_albums

FROM bands AS B

LEFT JOIN ALBUMS AS a ON b.id = a.band_id

GROUP BY b.id

HAVING num_albums =1 ;