SQL User Guide - Team-Juan/user-guides GitHub Wiki

SQL Brief:

SQL is a programming language that stands for Structured Query Language. Some folks pronounce it "ess-que-el" and some others pronounce it "sequel." It is a very powerful language that allows you to manipulate and retrieve data in a database.

SQL Use:

SQL is used worldwide as a database management language. It is the standard language that nearly everyone uses when manipulating databases. In order to use SQL, you must issue query statement commands to the database. One such example of an SQL statement:

/* This is a comment. You can provide simple notes in comment blocks. */

SELECT sID, fName, lName FROM Students WHERE lName LIKE 'A%'; `

/* This SQL statement will be explained below. */

Capitalization of command words (such as SELECT) is not necessary, but makes your code easier to read, practice good habits! However, capitalization can matter when refereeing to specific databases, tables, or rows! A table named 'Students' is different from a table named 'sTuDeNtS' depending on the database software.

Creating a database/table:

In order to manipulate data in a database, you must first have a database. To create a database use the SQL statement:

CREATE DATABASE UniversityDB;

USE UniversityDB;

This creates an empty database, next up we must create a table in this database. A table contains columns and rows of data. This data is what we want to manipulate. To create a table:

CREATE TABLE Students(

sID int NOT NULL AUTO_INCREMENT,

fName varChar(16) NOT NULL,

lName varChar(24) NOT NULL,

sYear int NOT NULL,

sGPA decimal(4, 3) NOT NULL

);

Let's explain each step:

CREATE TABLE Students( - Creates a table in the database named Students.

sID int NOT NULL AUTO_INCREMENT, - Creates an integer column named sID. NOT NULL means that this row cannot be left blank. AUTO_INCREMENT means it will automatically assign a number to the row starting with 1.

fName varChar(16) NOT NULL, - Creates a variable character column named fName. Maximum character length of this column is 16. In lName, maximum character length is 24.

sGPA decimal(4, 3) NOT NULL - Creates a number column named sGPA. The number can have a maximum length of 4 numbers, with 3 of them being in the decimal place.

); - This concludes the statement.

Inserting data into the database:

Now a database that has an empty table is not very useful, although I'm sure there is someone out there who could find use for an empty table. So in order to solve this problem we have to add rows to our table. To do this we complete an INSERT statement. There are two different methods of using an INSERT statement.

INSERT INTO Students(sID, fName, lName, sYear, sGPA)

VALUES (NULL, "John", "Doe", 2018, 3.752);

Is the equivalent to:

INSERT INTO Students VALUES (NULL, "John", "Doe", 2018, 3.752);

It is only necessary to specify the column name when you are specifically inserting info into the specified column.

Retrieving data from a database:

Retrieving data from a database is a simple task, that can evolve into a very complicated looking statement. To continue the example from above. Let's walk through each part of it.

SELECT - Is the command you use to retrieve data (rows) from the database.

sID, fName, lName - These are column names from the database. A * can be used as a wildcard.

FROM Students - This is the name of the table you are retrieving data from.

WHERE lName LIKE 'A%'- This is a conditional operator that allows you to specify specific conditions to meet with the data. In this instance, we will only retrieve rows from the table where the student's last name begins with an 'A'.

; - This is the conclusion to your statement. This tells SQL to run all the proceeding code, without it, it will not execute!

Modifying data in the database:

Want to update a row in your database? You must issue the UPDATE statement.

UPDATE Students SET sGPA = '3.962' WHERE sID = '1';

This will update the sGPA column of the student's row whose sID is equal to 1.

Removing data from the database:

In order to remove a row from your database, issue a DELETE command.

DELETE FROM Students WHERE sID = '1';

This deletes the row for the student whose sID is equal to 1.

In order to remove a table from the database, issue the DROP command.

DROP TABLE Students;

In order to remove a database entirely, same as before.

DROP DATABASE UniversityDB;

SQL Challenge:

Take the SQL Challenge here: Click