9+-+SQL+Part+I

** Lesson 1 ** Understanding SQL **  __Objective:__** Learn what SQL is and why it is useful. Some basic database terminology is included. ** SQL-Structured Query Language  ** **Definition:** simple & efficient way to read data from a database. ·  **SQL is a standard computer language.** ·  ** SQL is the language designed for communicating Database System ** ·  ** SQL used to retrieve and manage data in a DBMS. **  **  WHAT Is SQL? media type="youtube" key="Ulgk_oLqgpU" height="344" width="425"
 * Contents: SQL 1, 2, 3, 4 **

Three types of Commands ** ·  ** Data Definition Language ** -focuses on creating, altering, dropping database tables ·  ** Data Control Language ** -focuses on controlling the database ·  ** Data Manipulation Language ** -Focuses on updating, inserting, modifying, and querying the data in the database. ** DBMS-Database Management System  ** **Definition:** manages data as a collection of tables in which relationships occur through common values in related tables. ·  ** SQL works with DBMS such as: ** MySQL, Oracle, DB2, MS SQL Server and Sybase ·  ** Most DBMS have their own extensions to SQL like: ** MySQL, PL/SQL, SQL PL and T-SQL ·  ** Computer program that assesses a hub between the databases and the programs that want to use them. **   **__

Databases __**** ( ** A.K.A. Filing Cabinet**)** **Definition:** collection of data stored in some organized fashion. ·  ** Provide fast and efficient access to store data ** ·  ** The data is structured in Tables **

**__ Tables __**** ( ** A.K.A. Specific Files in Filing Cabinet**)** **Definition:** structured file that can store data of a specific type. ·  ** Tables usually contain a list of information ** ·  ** Each Tables contain Rows and Columns ** ·  ** Rows contain records which contain fields for every column ** ** *You cannot use the same table name twice in the same database, but you can reuse table names in different databases. ** ** __Schema__ - Used to describe table layout and properties within database. ** **  Example of Table **   ·   ** Vertical columns in the grid are the table columns ** ·   ** Horizontal rows are the table rows. ** Number of rows in the table is the number of records in it. **__ Columns __**** ( ** A.K.A. Fields**)** **Definition:** a single field in a table ·  ** All tables are made up of one or more columns ** ·  ** When braking up data, important to break into multiple columns correctly. ** ·   ** Each column has an associated datatype ** NOTE: Dr J Here. We use the terms a bit differently than Forta does. For this class, coumns should be understood as representing the attributes of an entity, not a field as outlined by Forta. The intersection of a column and row represent a unique instance of an attribute, or a FIELD. For example, If my student ID were 22534, this would be stored in the specific field of the stud_ID column for my record. **Definition:** restrict type of data that can be stored in a can. ·  ** Every table has an associated datatype that restricts (or allows) specific data in that column. ** ·   ** Special attention must be given to picking the right datatype when types are created. //If not can lead to SQL incompatibility.// ** **__ Rows __**** ( ** A.K.A. Records**)** ·  ** Data (record) in a table is stored in its own row. **  **Definition:** A column (or set of columns) whose values uniquely identify every row in a table. ·  ** Used to refer to a specific row ** Useful in future data manipulation ·  ** Multiple columns may be used together as a primary key, and values of all columns must be unique. ** ·   No two rows can have the same primary key value. ·  Every row must have a primary key value (primary key columns may not allow NULL values). ·  Values in primary key columns can never be modified or updated. ·  Primary keys can never be reused. (If a row is deleted, primary key may not be assigned to any new rows in the future). ** Lesson 2 ** Retrieving Data **__Objective:__** Learn how to use the SQL SELECT statement to retrieve a single table column, multiple table columns, and all table columns. -most frequent used SQL operation. -used to retrieve rows from a table. -to use, you must specify two pieces of information: A) What you want to select  B) Where you want to select it from **__ Keyword  __** **Definition:** a reserved word that is part of the SQL language -Never name a table or column using a keyword. -Command which identifies that SQL statement will be retrieving data from the database -Command which identifies the table from which the data will be retrieved -a filtering command -allows you to identify any search criteria Employees w/ absence levels above 5% Applicants w/ benefits administration skills media type="youtube" key="iwxwmUMadOg" height="344" width="425"
 * __Datatypes__**
 * Definition: record in a table **
 * __ Primary Keys  __**
 * Rules for column to be established as primary key: **
 * The SELECT Statement **
 * Important SQL Keywords **
 * SELECT **
 * FROM **
 * WHERE **
 * SELECT Statement Explained**

Example of SELECT statement to retrieve a single column: **SELECT** prod_name (Desired column) **FROM** Products; (Table where data is retrieved from) __** The output of this statement returns all the rows in a table. ** Retrieving Multiple Columns  ** -The only difference is that multiple column names must be specified after the SELECT keyword, and each column must be separated by a comma. -Columns can be in any order Example of retrieving multiple column statement: **SELECT** prod_id, prod_name, prod-price **FROM** Products; ** Output: **
 * Retrieving Individual Columns **
 * __ Input __**
 * __ Output:
 * **Prod_name** ||
 * Fish bean bag toy ||
 * Bird bean bag toy ||
 * Rabbit bean bag toy ||
 * 8 inch teddy bear ||
 * 12 inch teddy bear ||
 * 18 inch teddy bear ||
 * Raggedy Ann ||
 * King doll ||
 * Queen doll ||
 * __ Input: __**
 * **Prod_id ** || **Prod_name ** || **Prod_ price** ||
 * BNBG01 || Fish bean bag toy || 3.4900 ||
 * BNBG02 || Bird bean bag toy || 3.4900 ||
 * BNBG03 || Rabbit bean bag toy ||   3.4900 ||
 * BR01 || 8 inch teddy bear || 5.9900 ||
 * BR02 || 12 inch teddy bear  || 8.9900 ||
 * BR03 || 18 inch teddy bear || 11.9900 ||
 * RGAN01 || Raggedy Ann || 4.9900 ||
 * RYL01 || King doll || 9.4900 ||
 * RYL 02 || Queen dool || 9.4900 ||

**Retrieving All Columns** -Requires all columns without having to list them individually. -Done when using an asterisk (*) wild character Input **SELECT** * **FROM** Products; -When a wildcard (*) is specified, all columns in the table are returned. **The output of these statements are not filtered nor are they sorted**
 * __ Example: __**

Enter select statements to: Lesson 3: Sorting Retrieved data Objective: You will learn how to sort retrieved data using the SELECT statement’s ORDER BY clause. Sorting Data: Most likely if a user doesn’t input a sorting requirement the output of data will be in the order of it’s originally input into the system. Clause: SQL keyword and supplied day that allows you to sort data to a required specification. Example: SELECT prod_name (column) FROM Product (table) ORDER BY prod_name (clause)
 * SELECT Statement Exercise**
 * 1) Display the first name and age for everyone that's in the table.
 * 2) Display the first name, last name, and city for everyone that's not from Payson.
 * 3) Display all columns for everyone that is over 40 years old.
 * 4) Display the first and last names for everyone whose last name ends in an "ay".
 * 5) Display all columns for everyone whose first name equals "Mary".
 * 6) Display all columns for everyone whose first name contains "Mary".

prod_name

12 inch teddy bear 18 inch teddy bear 8 inch teddy bear Bird bean bag toy Fish bean bag toy King doll Queen doll Rabbit bean bag toy Raggedy Ann Sorting by Multiple Columns: SELECT prod_id, prod_price, prod_name From Products ORDER BY prod_price, pro_name; prod_id __ || __ prod_price __ || __ prod_name __ || BNNG02 ||  3.4900   ||  Bird bean bag toy || BANG01 ||  3.4900   ||  Fish bean bag toy || BNBG03 ||  3.4900   ||  Rabbit bean bag toy || RGAN01 ||  4.9900   ||  Raggedy Ann || BR01 ||  5.9900   ||  8 inch teddy bear || BR02 ||  8.9900   ||  12 inch teddy bear || RYL01 ||  9.4900   ||  King doll || RYL02 ||  9.4900   ||  Queen doll || BR03 ||  11.9900   ||  18 inch teddy bear ||
 * __

Notice that the output is sorted by prod_price and then by prod_name this is due to the ORDER BY clause. Sort by Column Position: SELECT prod_id, prod_price, prod_name From Products ORDER BY 2, 3; This order sequence allows you to choose the order by selecting particular columns from the SELECT input. Because we selected columns 2 and 3 the result will be the same as the one above. Specifying Sort Direction: Sorted data will automatically be sorted in an ascending order. To specify that you would wish to have data sorted in a descending order the keyword DESC must be used for the column you want to sort. SELECT prod_id, prod_price, prod_name From Products ORDER BY prod_price DESC; prod_id __ || __ prod_price __ || __ prod_name __ || BNNG02 ||  11.9900   ||  18 inch teddy bear || BANG01 ||  9.4900   ||  Queen doll || BNBG03 ||  9.4900   ||  King doll || RGAN01 ||  8.9900   ||  12 inch teddy bear || BR01 ||  5.9900   ||  8 inch teddy bear || BR02 ||  4.9900   ||  Raggedy Ann || RYL01 ||  3.4900   ||  Rabbit bean bag toy || RYL02 ||  3.4900   ||  Fish bean bag toy || BR03 ||  3.4900   ||  Bird bean bag toy ||
 * __

The keyword DESC only applies for to the column name that directly precedes it. Lesson 5: Filter Data Objective: You will learn how to filter returned data using the SELECT statement’s WHERE clause. Using the WHERE Clause: This allows you to extract a subnet of rows from a tables database that you specify in the WHERE clause. SELECT prod_id, prod_price, prod_name From Products ORDER BY prod_price = 3.49; prod_id __ || __ prod_price __ || __ prod_name __ || RYL01 ||  3.4900   ||  Rabbit bean bag toy || RYL02 ||  3.4900   ||  Fish bean bag toy || BR03 ||  3.4900   ||  Bird bean bag toy ||
 * __

The clause specified that it only wanted data with prod_pric that are $3.49. WHERE Clause Operators Operator || Descriptions || =  ||  Equality || <>  ||  Nonequality || !=  ||  Nonequality || <  ||  Less than || <=  ||  Less than or equal to  || !<  ||  Not less than || >  ||  Greater than || >=  ||  Greater than or equal to  || !>  ||  Not greater than || BETWEEN || Between two specified values || IS NULL || Is a NULL value ||

Checking for No Value: When a table is created the table designer can specify whether or not individual columns can contain no value. When a column contains no value, it is said to contain a NULL value. NULL or No Value, as opposed to a field containing 0, or an empty string, or just spaces. SELECT prod_name FROM Products WHERE prod_price IS NULL Results vend_id FNG01

[|**Answers**]


 * __Links__

[|What is SQL??]**


 * [|SELECT Statement]

[|Blog SQL] **** [|Help with writing SQL] **