Mastering SQL for Database Operations

How to: SQL By: Sam Loch

Goal To retrieve data from and insert data into a relational database Order-Product OrderProductID OrderID ProductID Quantity Order OrderID CustomerID OrderDate ShippingInstruction s Product ProductID ProductName Price InventoryLevel Customer CustomerID FirstName LastName Email Phone Address

How to Accomplish Our Goal Structured Query Language (SQL) Using SQL we can Retrieve data from a database Insert data into a database Update data in a database Delete data in a database Add and delete tables form a database Combine tables in a database

Creating a Database Define the database Create tables Define columns within these tables and choose their data types Each table needs a Primary Key Some tables will need Foreign Key s to communicate with other tables in the database

Creating a Database (2) CREATE statements These can be used to create a table within a database Example: Text Description CREATE TABLE db_name.table_name ( NULL/NOT NULL Defines whether the field can be empty (NULL) or whether it cannot (NOT NULL) Primary Key s cannot be NULL The data-type of the column. ColumnName1 datatype NOT NULL, ColumnName2 datatype NULL, PRIMARY KEY (KeyName)); datatype

Creating a Database (3) Another Example: CREATE TABLE salesdb.Order ( Order OrderID CustomerID OrderDate ShippingInstruction s OrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATE NULL, ShippingInstructions VARCHAR(45) NULL, PRIMARY KEY (OrderID));

Data Types Each column can contain a different type of data The data type of each column must be specified when it is created A few data types: Data type INT DECIMAL(p,s) Description Integer Decimal. Example: decimal(6,3) is a number that has 3 digits after the decimal and 6 digits total (123.456) String of any characters with a maximum length of x Examples 4, 62, -20 6.5, 0.9987, 123.456 VARCHAR(x) Howdy , She sells seashells by the sea shore '2017-05-03 20:43:00', '2017-05-03' 0,1 DATETIME, DATE BOOLEAN Date and time, or just Date Boolean value

Foreign Keys The Foreign Key in one table connects that table to a Primary key in another table. Product ProductID ProductName Price InventoryLevel Order-Product OrderProductID OrderID ProductID Quantity ProductID is a foreign key in the Order-Product table, and the primary key in the Product table. CREATE TABLE salesdb.`Order-Product` ( OrderProductID INT NOT NULL, OrderID INT NULL, ProductID INT NULL, Quantity INT NULL, PRIMARY KEY (OrderProductID), FOREIGN KEY (OrderID) REFERENCES salesdb.Order(OrderID) FOREIGN KEY (ProductID) REFERENCES salesdb.Product(ProductID)

Deleting Tables DROP TABLE db_name.table_name; EX: DROP TABLE salesdb.Product;

Altering the Data in a Table Adding a column: ALTER TABLE db_name.table_name ADD COLUMN column_name datatype (NULL or NOT NULL); Deleting a column: ALTER TABLE db_name.table_name DROP COLUMN column_name; Changing a column: ALTER TABLE db_name.table_name CHANGE COLUMN old_column_name new_column_name datatype (NULL or NOT NULL);

Altering the Data in a Table (2) Examples: Adds Color column to Product table ALTER TABLE salesdb.Product ADD COLUMN Color VARCHAR(10) NULL; ALTER TABLE salesdb.Product Deletes Color column to Product table DROP COLUMN Color; ALTER TABLE salesdb.Product CHANGE COLUMN Color Changes Color column from Product table to Version and changes character limit from 10 to 20 Version VARCHAR(20) NULL;

Adding a Row to a Table INSERT INTO db_name.table_name (ColumnName1, ColumnName2, ColumnName3) VALUES (Value1, Value2, Value3); Example: INSERT INTO salesdb.Customer (CustomerID, FirstName, LastName, Email, Phone, Address) VALUES (2003, Kit , Fisto , KFisto@Gmail .com , 717.555.1234, 560 Lois Lane Pittsburgh, PA ) CustomerID FirstName LastName Email Phone Address 1999 Johnny Bravo Jbravo@gmail.com 215.555.5678 1624 N. 18th Street Philadelphia, PA 2000 Dwight Shrute BeetBoi@Hotmail.co m Dballislife@gmail.com 717.555.5555 12 Farm Street Scranton, PA O Houlihan 2001 Patches 717.555.0000 34 Legend Lane Lancaster, PA 2002 Eric Foreman Eforeman@yahoo.com 215.555.9876 1919 S. 19th Street Philadelphia, PA 2003 Kit Fisto Kfisto@gmail.com 717.555.123 4 560 Lois Lane Pittsburgh, PA

Changing a Row UPDATE db_name.table_name SET ColumnName1 = Value1, ColumnName2 = Value2 WHERE condition; Example: UPDATE salesdb.Product SET ProductName = Shake Weight , Price = 99.99 WHERE ProductID = 1341; ProductID ProductName Price The Price of the Shake Weight was changed to 99.99 1341 Shake Weight 99.99 1342 The Claw 29.99 1343 SHAM WOW 4.99

Deleting a Row DELETE FROM db_name.table_name WHERE condition; Example: DELETE FROM salesdb.Product WHERE ProductID = 1738;

Retrieving Data From a Database SELECT statements SELECT statements are used to retrieve data from a database EX: SELECT column_name(s) FROM db_name.table_name;

SELECT statements CustomerID FirstName LastName Email Phone Address 1999 2000 Johnny Dwight Bravo Shrute Jbravo@gmail.com BeetBoi@Hotmail.co m Dballislife@gmail.com Eforeman@yahoo.com Kfisto@gmail.com 215.555.5678 717.555.5555 1624 N. 18th Street Philadelphia, PA 12 Farm Street Scranton, PA O Houlihan 2001 2002 2003 Patches Eric Kit 717.555.0000 215.555.9876 717.555.1234 34 Legend Lane Lancaster, PA 1919 S. 19th Street Philadelphia, PA 560 Lois Lane Pittsburgh, PA Foreman Fisto If you had a customer table and wanted to see the first and last names of all of the customers you would type: SELECT FirstName, LastName FirstName LastName FROM salesdb.Customer Johnny Bravo This would return: Dwight Shrute O Houlihan Patches Eric Foreman Kit Fisto

Retrieving All Columns in a Table SELECT * FROM salesdb.Customer CustomerID FirstName LastName Email Phone Address 1999 2000 Johnny Dwight Bravo Shrute Jbravo@gmail.com BeetBoi@Hotmail.co m Dballislife@gmail.com Eforeman@yahoo.com Kfisto@gmail.com 215.555.5678 717.555.5555 1624 N. 18th Street Philadelphia, PA 12 Farm Street Scranton, PA O Houlihan 2001 2002 2003 Patches Eric Kit 717.555.0000 215.555.9876 717.555.1234 34 Legend Lane Lancaster, PA 1919 S. 19th Street Philadelphia, PA 560 Lois Lane Pittsburgh, PA Foreman Fisto

Retrieving Values To retrieve only unique values: SELECT DISTINCT Price FROM salesdb.Product; To retrieve certain records only SELECT * FROM salesdb.Product WHERE Price = 99.99; SELECT * FROM salesdb.Product WHERE Price > 5;

WHERE Clause The following is a list of operators that can be used in the WHERE Clause: Operator = > >= < <= <> Description Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to

Sorting Results of a Select Statement SELECT * FROM salesdb.Customer WHERE CustomerID >= 2000 ORDER BY FirstName; CustomerID FirstName LastName Email Phone Address 2000 Dwight Shrute BeetBoi@Hotmail.co m Eforeman@yahoo.com Kfisto@gmail.com Dballislife@gmail.com 717.555.5555 12 Farm Street Scranton, PA 2002 2003 2001 Eric Kit Patches Foreman Fisto 215.555.9876 717.555.1234 717.555.0000 1919 S. 19th Street Philadelphia, PA 560 Lois Lane Pittsburgh, PA 34 Legend Lane Lancaster, PA O Houlihan

ORDER BY Ascending (ASC) and Descending (DESC) ProductID ProductName Price SELECT * FROM salesdb.Product 1343 Shake Weight 4.99 ORDER BY Price ASC; 1342 The Claw 29.99 1341 SHAM WOW 99.99 ProductID ProductName Price SELECT * FROM salesdb.Product 1341 Shake Weight 99.99 ORDER BY Price DESC; 1342 The Claw 29.99 1343 SHAM WOW 4.99

Other SQL Functions COUNT() Returns the number of rows MAX() Returns the largest value MIN() Returns the smallest value SUM() Returns the sum AVG() Returns the average value

ProductID ProductName Price Fun With Functions 1341 Shake Weight 99.99 1342 The Claw 29.99 Price SELECT MAX(Price) FROM salesdb.Product; 1343 SHAM WOW 4.99 99.99 SELECT COUNT(ProductID) FROM salesdb.Product; 3 Price SELECT MIN(Price) FROM salesdb.Product; 4.99 Price SELECT SUM(Price) FROM salesdb.Product; 134.97 Price SELECT AVG(Price) FROM salesdb.Product; 44.99

SELECTing From Multiple Tables To SELECT from multiple tables you must join the tables with WHERE Example: SELECT * FROM salesdb.Customer, salesdb.Order WHERE Customer.CustomerID = Order.CustomerID; This Returns: First Nam e e Dwig ht te Custom er.Cust omerID Last Nam Email Phone Address OrderID OrderDate Order.CustomerID 2000 Shru BeetBoi@H otmail.com 717.555.5 555 12 Farm Street Scranton, PA 102 2017-4-15 1002 2002 Eric Fore man Eforeman@ yahoo.com 215.555.9 876 1919 S. 19th Street Philadelphia, PA 103 2017-4-25 1003 2003 Kit Fisto Kfisto@gm ail.com 717.555.1 234 560 Lois Lane Pittsburgh, PA 104 2017-4-30 1004 O Ho uliha n 2001 Patc hes Dballislife @gmail.co m 717.555.0 000 34 Legend Lane Lancaster, PA 105 2017-5-3 1005

JOIN Syntax SELECT * FROM salesdb.Customer, salesdb.Order WHERE Customer.CustomerID = Order.CustomerID; SELECT * Return all the columns from both tables FROM salesdb.Customer, salesdb.Order The two tables to be joined WHERE Customer.CustomerID = Order.CustomerID Only choose records where the CustomerID exists in both tables

Summary Text NULL/NOT NULL Description Defines whether the field can be empty (NULL) or whether it cannot (NOT NULL) Primary Key s cannot be NULL The data-type of the column. Use CREATE statements to create a table datatype Use DROP TABLE to delete a table Use ALTER TABLE to change the data in a table Data type INT DECIMAL(p,s) Description Integer Decimal. Example: decimal(6,3) is a number that has 3 digits after the decimal and 6 digits total (123.456) String of any characters with a maximum length of x Examples 4, 62, -20 6.5, 0.9987, 123.456 ADD COLUMN DROP COLUMN CHANGE COLUMN Use INSERT INTO to add a row VARCHAR(x) Howdy , She sells seashells by the sea shore '2017-05-03 20:43:00', '2017-05-03' 0,1 Use UPDATE and SET to change a row DATETIME, DATE BOOLEAN Date and time, or just Date Use DELETE FROM to delete a row Boolean value

Summary(2) Use SELECT statements to retrieve data from a database SELECT * selects all data from COUNT() Returns the number of rows MAX() Returns the largest value MIN() Returns the smallest value SUM() Returns the sum AVG() Returns the average value