Modifying data

It is possible to modify an existing table's data using SQL. There are commands for inserting new data, deleting existing data and modifying existing data.

Format
Inserting a new row of data, list of values must match order of table structure.
(You must include NULL if value is blank)
INSERT INTO table VALUES(<list of values>)l

Insert multiple rows of data at once (Not available in Microsoft Access)
INSERT INTO table VALUES (<list of values>),
                                                  (<list of values>),
                                                  (<list of values>);

Delete row of data from table
DELETE FROM table WHERE <condition>

Modify existing row's value
UPDATE table SET attribute - <new value> WHERE <condition>

Modifying table structures

The ALTER statement can be used in a variety of ways to modify the table structure. It can be used to modify, add, and delete columns. Alter can also be used to change a table's name.

Format
Add an attribute
ALTER TABLE table ADD new_attribute data Type <properties>'

Delete an entire attribute(column) from table
ALTER TABLE table DROP COLUMN attribute;

Edit type and property existing attribute(Not available in Microsoft Access)
ALTER TABLE table MODIFY attribute data type <properties>;

Rename a table (Not available in Microsoft Access)
ALTER TABLE table RENAME TO <new name>;

CREATE Statement

Not only can SQL retrieve data, it can also define data structures and create objects. The create statement can be used to create tables, schemas and even users.

Data types
INTEGER- whole numbers(no decimals)
NUMERIC- decimal numbers allowed
VARCHAR(n)- text, can have a maximum length of n
CHAR(n)- text, must be of length n
DATE-date value

Properties
NOT NULL- value cannot be null
UNIQUE-value must be unique within attribute
AUTO_INCREMENT- automatically increase value for next row

Format
CREATE TABLE table(
attribute1 data type <properties>,
attribute2 data type <properties>,
attribute3 data type <properties>
PRIMARY KEY(attribute1)
);

Create table with foreign key
CREATE TABLE table(
attribute1 data type <properties>,
attribute2 data type <properties>,
attribute3 data type <properties>
PRIMARY KEY(attribute1)
FOREIGN KEY (attribute2) REFERENCES other table
);

Add composite keys instead
PRIMARY KEY(attribute1, attribute2)

SQL

SQL - Structured Query Language

A programming language designed to manage and access data in a relational DBMS. Popular SQL version or dialects: MySQL, pgSQL, Microsoft Access

DML-Data Manipulation Language

SQL commands focused on manipulating data in the database. Typical operation include retrieving deleting, inserting and updating data in the database.

SELECT-retrieves data from the database
INSERT- inserts a new tuple(row) of data
UPDATE- modifies existing data in a table
DELETE- deletes tuples (rows) from a table

DDL- Data Definition Language

SQL commands focused on defining database structures or schema in the database. Typical operations include creating schemas, modifying existing schemas, deleting tables and others.

CREATE-creates objects such as schemas, tables, or even users
ALTER- modifies an existing structure in the database
DROP- deletes an entire table including table structure
TRANCATE - deletes all data within the table.

SQL Aggregate Functions

There are five aggregate function defined in the ISO standard for SQL.

1. COUNT: Returns the number of values in a specified column.
2. SUM: Returns the sum of values in a specified column.
3. AVG: Returns the average of the values in a specified column.
4. MIN: Returns the smaller value in a specified column.
5. MAX: Returns the largest value in a specified column.

• Each operates on a single column of a table and returns a single value. 
• COUNT, MIN and MAX apply to numeric and non-numeric fields but SUM and AVG may be used on numeric fields only.
• Apart from COUNT(*) each function eliminates nulls first and operates only on remaining non-null values.
• COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur
• Can use DISTINCT before column name to eliminate duplicates.
• DISTINCT has no effect with MIN/MAX but may have with SUM/AVG.

Format
Count how many rows there are in a table
SELECT COUNT(*) AS "total number of rows"  
FROM table;

Count how many distinct values there are in an attribute.
*Note: This does not work in Microsoft Access.
SELECT COUNT(DISTINCT attribute) AS "number of unique values"
FROM table;

Sum all the values in an attribute
SELECT SUM(attribute) AS "sum of attribute"
FROM table;

Find average of all the values in an attribute
SELECT AVG(attribute) AS "average of attribute"  
FROM table;

Find the smallest value in an attribute
SELECT MIN(attribute) AS "smallest of attribute"  
FROM table;

Find the biggest value in an attribute
SELECT MAX(attribute) AS "biggest of attribute"
FROM table;

Using aggregate function while grouping result based on another attribute
SELECT <aggregate function of attribute1>, attribute2  
FROM table  
GROUP BY attribute2; 

Same as previous but restrict the grouping with a condition 
SELECT <aggregate function of attribute1>,attribute2 
FROM table  
GROUP BY attribute2  
HAVING <aggregate function of attribute with condition>; 

Other SELECT commands

There are other commands that can be used with the SELECT statement, such as sorting distinct values, retrieving data from multiple tables and sub-queries. They can be used with or without the WHERE condition

Note: When using a sub-query in a WHERE condition, the sub-query must only return one result.

Format

Sorting query results in ascending order by attribute
SELECT  <attribute> FROM table ORDER BY attribute;

Sorting query results in descending order by attribute
SELECT  <attribute> FROM table ORDER BY attribute DESC;

Show only unique values of an attribute
SELECT DISTINCT attribute FROM table;

Show attributes from multiple tables (pk - primary key, fk - foreign key)
SELECT <attribute> FROM < list of tables> WHERE table1.pk=table2.fk;

Using sub-queries to retrieve results from another source
SELECT <attributes> FROM < SUB_QUERY>;
SELECT <attributes> FROM table WHERE attribute = < SUB_QUERY>;

SELECT with conditions

To control which rows of data to retrieve, we can apply various conditions for the table values and only display rows that match that condition

 

Format

SELECT <attributes> FROM <source table> WHERE <conditio>'

 

Basic condition <> symbol means "not equal"

SELECT*FROM table WHERE attribute = "value";
SELECT*FROM table WHERE attribute <> "value";
SELECT*FROM table WHERE attribute >1;
SELECT*FROM table WHERE attribute >=1;
SELECT*FROM table WHERE attribute <=1;

Condition for NULL values

SELECT*FROM table WHERE attribute IS NOT NULL;
SELECT*FROM table WHERE attribute IS NULL;

Multiple condition in one statement

SELECT*FROM table WHERE att1="a" AND att2="b";
SELECT*FROM table WHERE att1="a" OR att1="b";

Specify a range using either the AND command or BETWEEN command

SELECT*FROM table WHERE attribute >=0 AND attribute <=100;
SELECT*FROM table WHERE attribute BETWEEN 0 AND 100;

Specify multiple values an attribute can match

SELECT * FROM table WHERE attribute IN ("a", "b", "c");

Match a sub-word in the value.*means anything can be in that position a _means any one character can be in that position. 

*Note: LIKE with_is not available in Microsoft SQL
**Note: In My SQL, use a % symbol instead of a *
SELECT*FROM table WHERE attribute LIKE "*word*';
SELECT*FROM table WHERE attribute LIKE "wo_d";

Note:  It is possible to compare date values in a condition such as
WHERE game_date > #03-19-04#

Microsoft Access SQL uses date format MM/DD/YY wrapped with #.
Fro example if the date was March 19th 2004 the the SQL value should be #03-19-04#.

My SQL uses " " rather than # # for dates and in the format YYYY/MM/DD.
For example "2004-03-19".