This module covers the language of relational databases - SQL - structured query language. SQL is used to create relational database tables, manipulate those tables (input new rows, delete rows, or change existing data (column values). As a language SQL has syntax and you MUST follow the correct syntax in order for it to work.
We must first create and define our database tables - we need to identify the table name, and include the various columns (attributes) of that table. We do this with the CREATE SQL statement.
After creting our database table - we then can manipulate it - through the various statements:
SELECT - this is our primary SQL query statement that gets the data off of the table and displays it as we want; there ae several parts of the SELECT statement:
SELECT list of attributes separated by commas
FROM tablename
or
SELECT list of attributes
FROM tablename
WHERE condition (this is where we do comparisons to find particular data - such as WHERE Patient_Zip = 30189)
In our WHERE conditions we use our comparison operators such as "=", ">", "<", etc
We also use connectors - AND OR NOT
SELECT Patient_Lname, Patient_Fname
FROM PATIENT
WHERE Patient_Zip = 30189 AND Patient_Lname > "Murphy"
(this will get us all of parients in our patient table who live in zip code 30189 and whose name (alphabetically) is greater than the last name Murphy. Notice that Murphy is put into quotes as it is an alphabet character
UPDATE - where we change the data in column(s) that we name
DELETE - delete a row
INSERT - add a row
We will be actually creating our database in our next lab (lab 4), and then doing queries from the database tables that we have created in lab 5.
DDL - data definition language - this is used to CREATE tables
DML - data manipulation language - this is your SELECT, INSERT, DELETE, UPDATE commands
DCL - data control language - these are commands that help control the database itself - such as adding certain permissions to the tables, etc.