SQL

1. SQL

2. Tutorials • To understand some of the topics please analyze the following tutorials: • The following tutorials will help: • http://www.sqlcourse.com/intro.html • http://www.w3schools.com/sql/default.asp

3. What can we do with SQL • Querying Data • Single Table • Multiple Tables • Inserting Data • Updating Data • Deleting Data

4. Querying Data • Querying Single Tables • Basic Query Format • Search Conditions • Querying Multiple Tables

5. Querying Single Tables • Basic Format: SELECT column(s) FROM table WHERE condition(s) • Rules: • SELECT must be first clause • FROM must be second clause • table/column names must be spelled as in the database

6. Selecting All Rows • SELECT column(s) FROM table; • Example: SELECT Customer_ID FROM Order_t;

7. Selecting Unique Rows • SELECT DISTINCT column(s) FROM table; • Example: SELECTDISTINCT Customer_ID FROM Order_t;

8. Search Conditions • For retrieving specific rows: • Comparison Operators • Boolean Operators • Other Operators • Wildcards • Expressions • Functions • Nulls • SELECT column(s) FROM table WHERE <search condition(s)>;

9. Comparison Operators SELECT Order_ID, Order_Date FROM Order_t WHERE Customer_ID = 1;

10. Comparison Operators, con’t... • Comparison Operators: = equal to > greater than < less than >= greater than or equal to <= less than or equal to <> not equal to

11. Boolean Operators • Boolean Operators • AND all search conditions must be met • OR any search condition must be met • NOT a search condition must not be met • SELECT Customer_ID, Order_ID, Order_Date FROM Order_t WHERE (Customer_ID = 1) AND (Order_ID > 1005);

12. Other Operators • BETWEEN • The BETWEEN ... AND operator selects an inclusive range of data between two values. These values can be numbers, text, or dates. SELECT Customer_ID, Order_Date FROM Order_t WHERE Customer_ID BETWEEN 1 AND 5;

13. Wildcards • * SELECT* FROM Order_t WHERE Customer_ID IN (1, 3, 5) • You can use a "*" to select all columns. • LIKE • Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. • SELECT Customer_Name, Customer_ID FROM Customer_t WHERE Customer_Name LIKE (‘%furn%’); SELECT Customer_Name, Customer_ID FROM Customer_t WHERE Customer_Name LIKE (‘%furn_____’);

14. Min and Max • Finds minimum/maximum value of attribute SELECT MAX(Order_Date) FROM Order_t; SELECTMIN(Order_Date) FROM Order_t WHERE Customer_ID > 8;

15. Nulls • Means: • unknown • not applicable • SELECT Customer_Name FROM Customer_t WHERE Customer_address IS NULL;

16. Querying Multiple Tables • Joins • Brings data together from multiple tables • Same column in multiple tables • Use table_name.column_name to distinguish columns • Use WHERE clause to join tables • Example • SELECT Customer_t.Customer_ID, Customer_t.Customer_Name, Order_date FROM Customer_t, Order_t WHERE Order_date > (‘11/01/98’,’MM/DD/YY’) AND Customer_t.Customer_ID = Order_t.Customer_ID; ****Notice that the Customer_ID is the primary Key for Customer_t and Foreign key for order_t table**** This is the only way you can connect two tables !!!

17. Inserting Data • INSERT INTO table_name [(column1_name, column2_name, …)] VALUES (column1_value, column2_value, …); • Examples: INSERT INTO Order_t (order_id, customer_id) VALUES (9000,8);

18. Updating Data • UPDATE table_name SET column1_name = new_value [,column2_name = new value,…] WHERE condition(s); • Example: UPDATE Order_t SET Customer_ID = 2 WHERE Customer_ID = 1;

19. Deleting Data • DELETE FROM table_name [WHERE condition(s)]; • Examples: DELETE FROM Order_t WHERE Customer_ID = 2;