1 / 22

SQL (Standard Query Language)

SQL (Standard Query Language). Yong Choi School of Business CSU, Bakersfield. Study Objectives. Understand the basic commands and functions of SQL Learn how to use SQL to query a database to extract useful information Practice SQL. Introduction to SQL.

oakley
Download Presentation

SQL (Standard Query Language)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL (Standard Query Language) Yong Choi School of Business CSU, Bakersfield

  2. Study Objectives • Understand the basic commands and functions of SQL • Learn how to use SQL to query a database to extract useful information • Practice SQL

  3. Introduction to SQL • Standard Query Language (SQL) is a computer language for the relational database model. • SQL is a nonprocedural language (click here for the example). So, it is much easier to use. • what is to be done without having to worry about how it's to be done. • Procedural language (click here for the example) • Must be programmed correctly and compiled • Java, C++, and Pascal. • SQL is relatively easy to learn • SQL commands set has a basic vocabulary of less than 100 words. Go To Next Topic

  4. Back SQL Example SELECTID, L_Name, F_Name, Salary, Dept_No FROM Employee; ID L_NameF_NameSalaryDept_No --------- ---------- ---------- ---------- ------------------------------------------------------------------------- 1 Kim John 1000 100 2 Johnson Steve 1200 100 3 Jonson Paul 1100 200 4 Lee Jim 1100 200 5 Basinger Jon 1300 6 Stone Sharon 1000  6 rows selected.

  5. Back JAVA Example public class JavaProgramming { public static void main ( String[] args ) { long payAmount; payAmount = 123; System.out.println("The variable contains: " + payAmount ); } } • Output:The Variable contains 123.

  6. Basic SQL Commands • Followings are the most frequently used commands • Use always • SELECT <field list> • FROM <table list> • Use when conditions must be specified • WHERE <condition> • HAVING <group condition> • ORDER BY <sorting field> • GROUP BY < grouping records>

  7. The SELECT and FROM Statement • Need both commands almost always….. • The SELECT statement is used to select data from a table. • The FROM statement is used to select tables. • Syntax: • SELECT column_name(s) • FROM table_name • To select all columns (fields) from a table, use *symbol instead of column names:  • SELECT* • FROM table_name

  8. The WHERE Statement • To conditionally select data from a table, a WHERE clause can be added to the SELECT statement. • Syntax: • SELECT column • FROM table • WHERE column operator value • See next slide for various operators

  9. SQL Comparison Operators FOR WHERE clause AND logical operator OR logical operator NOT Warehouse =‘3’ LIKE: LIKE ‘a*’, LIKE ‘*s’, Like ‘*Oxford*’ BETWEEN 45000 AND 78000

  10. Semicolon after SQL Statements? • Semicolon is the standard way to a block of SQL statement in database systems.So, you must use a semicolon at the end of a block of SQL statement. • Access SQL commands are not case sensitive but try to follow exact names for better readability. • Download SQL data file form the class web site. • SQL_300.mdb

  11. CustomerNum OrderNum OrderNum RepNum PartNum LastName Description OrderDate PartNum CustomerName FirstName NumOrdered OnHand CustomerNum Class Street QuotedPrice Street Warehouse City Price State City Zip State Commission Zip Balance Rate CreditLimit RepNum 148 21608 20 21608 AT94 Kaiser Iron 10/20/2003 AT94 Al's Appliance and Sport Valerie 11 50 148 HW 624 Randall $21.95 2837 Greenway 3 Grove $24.95 FL Fillmore 33321 FL $20,542.50 33336 $6,550 0.05 $7,500 20 Customer OrderLine Orders Part Rep

  12. SQL Preparation • Review tables and relationships first… • Review each table in both views • Design view • Datasheet view • Recognize PK and FK information • Examine Relationships information • SQL using Access • Design view of query  Select SQL View using Style button • No need to add tables (unlike QBE)

  13. SQL Sample • I’d like to know the list of CustomerNum and CustomerName. • SELECTCustomerNum, CustomerName • FROM Customer; • I’d like to know the list of PartNum, Description, RepNum, and LastName • SELECTPartNum, Description, RepNum, LastName • FROM Part, Rep;

  14. SQL Example 1 & 2 • Example 1: • I’d like to know a list of the Customer number, Customer name, and balance of all customers. • Save as SQL 1 • Example 2: • I’d like to know a list of the Order number, Part number, Price and Order Date. • Save as SQL2

  15. SQL Example 3 • Restriction requirements • Where command • Exact Match • Example 3: • I’d like to know customer nameswho are located in the city of Grove • Save as SQL3

  16. SQL Query to Find All Customers in ‘Grove’

  17. SQL Example 4 • Restriction requirements • Where command • Comparison operator • Example 4: • I’d like to knowa list of the number, name, credit limit, and balance for customers with credit limits that exceed their balances. • Save as SQL4

  18. Query to find Customers with Credit Limit Exceeding Balance

  19. SQL Example 5 • Restriction requirements • Where command • Compound conditions • Example 5: • List the description of all parts that are located in warehouse 3 and for which there are more than 20 units on hand. • Save as SQL5

  20. SQL Query with Compound Condition using ‘AND’

  21. SQL Example 6 • Default value of ORDER BY: ascending • Example 6: • List the number, name, and credit limit of all customers. Sort the customers by name in ascending order. • Save as SQL6

  22. SQL Query to Sort Data

More Related