MIS 2502 Data Analytics SQL 4 Putting Information

MIS 2502: Data Analytics SQL 4– Putting Information Into a Database Zhe (Joe) Deng deng@temple. edu http: //community. mis. temple. edu/zdeng 1

What do we want to do? Get information out of the database (retrieve) Put information into the database (modify/change) Database Management System

Our relational database • A series of tables • Linked together through primary/foreign key relationships

To create a database • We need to define • • The tables The fields (columns) within those tables The data types of those fields The primary/foreign key relationships • There are SQL commands that do each of those things • So let’s assume that we have a blank database and we needed to create the tables

CREATE statement (create a table) CREATE TABLE schema_name. table_name ( column. Name 1 datatype [NULL][NOT NULL], column. Name 2 datatype [NULL][NOT NULL], … PRIMARY KEY (Key. Name) ); Item Description schema_name The schema that will contain the table_name The name of the table column. Name The name of the field datatype The datatype of the field [NULL][NOT NULL] Whether the field can be empty (i. e. , null). The [] means the parameter is optional. A primary key column cannot contain NULL values. Key. Name The name of the field that will serve as the primary key.

Data types • Each field can contain different types of data • That must be specified when the table is created • There are many data types; we’re only going to cover the most important ones Data type Description Examples INT Integer 3, -10 DECIMAL(p, s) Decimal. Example: decimal(5, 2) is a number that has 3 digits before decimal and 2 digits after decimal (like 123. 45). 5 digits in total. 3. 23, 3. 14159 VARCHAR(n) String (numbers and letters) with maximum length n 'Hello', 'I like pizza', 'My. SQL!' DATETIME, DATE Date/Time, or just Date '2011 -09 -01 17: 35: 00', '2011 -04 -12' BOOLEAN Boolean value 0 or 1

Customer Looking at the “new” Customer table Column name Data type Customer. ID INT First. Name VARCHAR(45) Last. Name VARCHAR(45) City VARCHAR(45) State ? ? Zip ? ? The database management system stores this information about the table It’s separate from the data in the table (i. e. , Customer information) This is called metadata – “data about data”

Example: Creating the Customer Table CREATE TABLE schema_name. table_name ( column. Name 1 datatype [NULL][NOT NULL], column. Name 2 datatype [NULL][NOT NULL], … PRIMARY KEY (Key. Name) ); Customer. ID First. Name Last. Name City State Zip Based on this SQL statement: • The only required field is Customer. ID – the rest can be left blank. • Customer. ID is defined as the primary key.

Example: Creating the Customer Table CREATE TABLE orderdb. Customer ( Customer. ID INT NOT NULL , First. Name VARCHAR(45) NULL , Last. Name VARCHAR(45) NULL , City VARCHAR(45) NULL , State VARCHAR(2) NULL , Zip VARCHAR(10) NULL , PRIMARY KEY (Customer. ID) ); Customer. ID First. Name Last. Name City State Zip Based on this SQL statement: • The only required field is Customer. ID – the rest can be left blank. • Customer. ID is defined as the primary key.

So back to our CREATE statement CREATE TABLE orderdb. Customer ( Customer. ID INT NOT NULL , First. Name VARCHAR(45) NULL , Last. Name VARCHAR(45) NULL , City VARCHAR(45) NULL , State VARCHAR(2) NULL , Zip VARCHAR(10) NULL , PRIMARY KEY (Customer. ID) ); First. Name can be a string of up to 45 letters and numbers. Why 45? It’s the My. SQL default. State can be a string of up to 2 letters and numbers

NULL vs. NOT NULL values represent missing/empty data. CREATE TABLE orderdb. Customer ( Customer. ID INT NOT NULL , First. Name VARCHAR(45) NULL , Last. Name VARCHAR(45) NULL , City VARCHAR(45) NULL , State VARCHAR(2) NULL , Zip VARCHAR(10) NULL , PRIMARY KEY (Customer. ID) ); The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. Rule of Thumb • A primary key (e. g. , Customer. ID) column should be NOT NULL. • The rest can be left NULL.

Foreign Key • A foreign key in one table points to a primary key in another table. Customer. ID is a foreign key in the Order table, and a primary key in the Customer table CREATE TABLE orderdb. `Order` ( This ensures that every order is Order. Number INT NOT NULL , placed by a valid customer that Order. Date DATETIME NULL , already exists. Customer. ID INT NULL , PRIMARY KEY (Order. Number) , FOREIGN KEY (Customer. ID) REFERENCES orderdb. Customer(Customer. ID) );

Some more CREATE statements CREATE TABLE orderdb. Product ( Product. ID INT NOT NULL , Product. Name VARCHAR(45) NULL , Price DECIMAL(5, 2) NULL , PRIMARY KEY (Product. ID) ); CREATE TABLE orderdb. Order. Product( Order. Product. ID INT NOT NULL , DECIMAL(5, 2) indicates price can no Order. Number INT NULL , larger than 999. Product. ID INT NULL , PRIMARY KEY (Order. Product. ID) , FOREIGN KEY (Order. Number) REFERENCES orderdb. `Order`(Order. Number), FOREIGN KEY (Product. ID) REFERENCES orderdb. Product(Product. ID) );

Removing tables DROP TABLE schema_name. table_name; Example: DROP TABLE orderdb. Customer; Be careful! • This deletes the entire table and all data! • It’s a pain to get it back (if you can at all)!

Changing a table’s metadata ALTER TABLE schema_name. table_name ADD COLUMN column_name datatype [NULL][NOT NULL]; or ALTER TABLE schema_name. table_name DROP COLUMN column_name; or ALTER TABLE schema_name. table_name CHANGE COLUMN old_column_name new_column_name datatype [NULL][NOT NULL]; Adds a column to the table Removes a column from the table Changes a column in the table

An example of each ALTER TABLE orderdb. Product ADD COLUMN Manufacturer VARCHAR(45) NULL; Adds ‘Manufacturer’ column to Product table ALTER TABLE orderdb. Product DROP COLUMN Manufacturer; Removes ‘Manufacturer’ column from Product table

An example of each ALTER TABLE orderdb. Product CHANGE COLUMN Price Sales. Price DECIMAL(6, 2) NULL; Changes name of Price column in Product table to Sales. Price and its data type to DECIMAL(6, 2) ? ? ? Changes data type of Price column in Product table to DECIMAL(6, 2) but leaves the name unchanged.

Adding a row to a table (versus columns) Adding a column Adding a row • A change in the table structure • Done using ALTER TABLE • A change in the table data • Done using INSERT INTO

Adding a row INSERT INTO schema_name. table_name (column. Name 1, column. Name 2, column. Name 3) VALUES (value 1, value 2, value 3); Item Description schema_name The schema that contains the table_name The name of the table column. Name The name of the field value The data value for the field datatype The datatype of the field BIG TIP: The order of the values MUST match the order of the field names!

INSERT example INSERT INTO schema_name. table_name (column. Name 1, column. Name 2, column. Name 3) VALUES (value 1, value 2, value 3); Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 1004 Eric Foreman Warminster PA 19111 1005 Chris Taub Princeton NJ 09120 BIG TIP: Note that field names are surrounded by back quotes (`) and string field values are surrounded by regular single quotes (')

INSERT example INSERT INTO orderdb. Customer (Customer. ID, First. Name, Last. Name, City, State, Zip) VALUES (1005, 'Chris', 'Taub', 'Princeton', 'NJ', '09120'); Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 1004 Eric Foreman Warminster PA 19111 1005 Chris Taub Princeton NJ 09120 BIG TIP: Note that field names are surrounded by back quotes (`) and string field values are surrounded by regular single quotes (')

Changing a row UPDATE schema_name. table_name SET column. Name 1=value 1, column. Name 2=value 2 WHERE condition; Item Description schema_name The schema that contains the table_name The name of the table column. Name The name of the field value The data value for the field condition A conditional statement to specify the records which should be changed

UDPATE example Product UPDATE schema_name. table_name SET column. Name 1=value 1, column. Name 2=value 2 WHERE condition; Product. ID Product. Name Price 2251 Honey Nut Cheerios 4. 50 1. 29 2282 Bananas 1. 29 2. 99 2505 Eggo Waffles 2. 99 Product. ID Product. Name Price 2251 Cheerios 3. 99 2282 Bananas 2505 Eggo Waffles

UDPATE example UPDATE orderdb. Product SET Product. Name='Honey Nut Cheerios', Price=4. 50 WHERE Product. ID=2251; Product The “safest” way to UPDATE is one record at a time, based on the primary key field. Product. ID Product. Name Price 2251 Honey Nut Cheerios 4. 50 1. 29 2282 Bananas 1. 29 2. 99 2505 Eggo Waffles 2. 99 Product. ID Product. Name Price 2251 Cheerios 3. 99 2282 Bananas 2505 Eggo Waffles

Changing multiple rows UPDATE orderdb. Customer SET City='Cherry Hill' WHERE State='NJ'; Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 1004 Eric Foreman Warminster PA 19111 Customer. ID First. Name Last. Name City State Zip 1001 Greg House Cherry Hill NJ 09120 1002 Lisa Cuddy Cherry Hill NJ 09123 1003 James Wilson Cherry Hill NJ 09121 1004 Eric Foreman Warminster PA 19111 Be careful! You can do a lot of damage with a query like this!

Deleting a row DELETE FROM schema_name. table_name WHERE condition; Item Description schema_name The schema that contains the table_name The name of the table condition A conditional statement to specify the records which should be changed

DELETE example Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 1004 Eric Foreman Warminster PA 19111 Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 DELETE FROM orderdb. Customer WHERE Customer. ID=1004; Again, the “safest” way to DELETE is based on the primary key field.

Deleting multiple rows id Avo ng i do s! thi DELETE FROM orderdb. Customer WHERE Customer. ID>1002; Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 1004 Eric Foreman Warminster PA 19111 Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123

One more DELETE example DELETE FROM orderdb. Customer WHERE State='NJ' AND Zip='09121‘; Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1003 James Wilson Pittsgrove NJ 09121 1004 Eric Foreman Warminster PA 19111 Customer. ID First. Name Last. Name City State Zip 1001 Greg House Princeton NJ 09120 1002 Lisa Cuddy Plainsboro NJ 09123 1004 Eric Foreman Warminster PA 19111

Summary • Given the schema of a database, be able to • • • Create a table using CREATE TABLE Change the structure of a table using ALTER TABLE Add a record to a table using INSERT Update an existing record in a table using UPDATE Delete a record from a table using DELETE • Be familiar with using conditional statements in the UPDATE and DELETE statements using WHERE • Be familiar with My. SQL data types (INT, DECIMAL, BOOLEAN, DATETIME/DATE, VARCHAR, etc. )

Summary: DDL and DML Commands • DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in database. • CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers). • DROP – is used to delete objects from the database. • ALTER-is used to alter the structure of the database. • DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements. • • • SELECT – is used to retrieve data from the a database. INSERT – is used to insert data into a table. UPDATE – is used to update existing data within a table. DELETE – is used to delete records from a database table. More on DDL, DML, DCL and TCL Commands can be found here (https: //www. geeksforgeeks. org/sql-ddl-dml-dcl-tcl-commands)

Time for our th 6 ICA!