sql part i n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL – Part I PowerPoint Presentation
Download Presentation
SQL – Part I

Loading in 2 Seconds...

play fullscreen
1 / 48

SQL – Part I - PowerPoint PPT Presentation


  • 113 Views
  • Uploaded on

SQL – Part I. Yong Choi School of Business CSU, Bakersfield. Study Objectives. Understand the basic commands and functions of SQL Learn how SQL is used for data manipulation (to add, modify, delete, and retrieve data) Learn how to use SQL to query a database to extract useful information

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL – Part I' - elam


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql part i

SQL – Part I

Yong Choi

School of Business

CSU, Bakersfield

study objectives
Study Objectives
  • Understand the basic commands and functions of SQL
  • Learn how SQL is used for data manipulation (to add, modify, delete, and retrieve data)
  • Learn how to use SQL to query a database to extract useful information
  • Learn how SQL is used for data administration (to create tables, indexes, and views)
  • Practice SQL
ideal database language requirements
Ideal Database Language Requirements
  • Create database and table structures.
    • SQL has a data definition component that gives us the ability to meet this requirement.
  • Manage the data component of the database.
    • SQL gives us a set of commands to add, update, and delete data within the database tables.
  • Provide detailed data query capability.
    • "Standard" SQL uses a set of approximately thirty commands that allow us to retrieve data and to convert the raw data into useful information.
brief history of sql
Brief History of SQL
  • Standard Query Language (SQL) is the relational model’s standard language.
  • The original version of SQL was developed at IBM's San Jose Research Laboratory. This language, originally called Sequel. The Sequel language has evolved since then, and its name has changed to SQL (Structured Query Language).
  • In 1986, the American National Standards Institute (ANSI) published an SQL standard.
    • In 1992, work was completed on a significantly revised version of the SQL standard (SQL-92).
introduction to sql
Introduction to SQL
  • SQL is relatively easy to learn
    • SQL commands set has a basic vocabulary of less than 100 words.
  • SQL is a nonprocedural language. So, it is much easier to use.
    • Its user merely commands what is to be done without having to worry about how it's to be done.
    • Procedural language: COBOL, C, or Pascal.
  • See SQL by “Restaurant” DB on the class website
basic structure of sql queries
Basic Structure of SQL Queries
  • SELECT <attribute list>
  • FROM <table list>
  • WHERE <condition>
  • GROUP BY < grouping attribute(s)>
  • HAVING <group condition>
  • ORDER BY <attribute list>
the select and from statement
The SELECT and FROM Statement
  • The SELECT statement is used to select data from a table. The tabular result is stored in a result table (called the result set). The FROM statement is used to select tables.
  • Syntax:
    • SELECT column_name(s)
    • FROM table_name
  • To select all columns from a table, use a * symbol instead of column names: 
    • SELECT * FROM table_name
the where statement
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
typical data types
Typical Data Types
  • INTEGER: Numbers without a decimal point
  • SMALLINT: Uses less space than INTEGER
  • DECIMAL(p,q): P number of digits; q number of decimal places
  • CHAR(n): Character string n places long
  • DATE: Dates in DD-MON-YYYY or MM/DD/YYYY
semicolon after sql statements
Semicolon after SQL Statements?
  • Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
  • MS Access and SQL Server do not require to put a semicolon after each SQL statement, but other database SQLs force you to use it such as Oracle. So, you must use a semicolon for this class.
    • Access SQL commands are not case sensitive (including table and attribute names) but try to follow exact names for better readability.
    • Download SQL data file form the class web site.
slide11

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

example 2
Example 2
  • Example 2: Save as example 2
    • List the number, name, and balance of all customers.
example 22
Example 2

SELECT Customernum, CustomerName, Balance

FROM Customer;

example 3
Example 3
  • Example 3: Save as example 3
    • List the complete Part table.
    • Use of “ * “
example 31
Example 3

SELECT *

FROM Part;

oracle 7 0 sql example
Oracle 7.0 SQL Example

SQL> select * from employee;

 EMP_ID EMP_LNAME EMP_FNAME EMP_SALARY EMP_DEPT_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.

sql example where clause
SQL Example – WHERE clause
  • Example 4: Save as example 4
    • List the name of every customers with $10,000 credit limit.
      • Credit limit must be equal to $10000
slide20

Example 4

SQL Query with Where Condition

example 4
Example 4

SELECT Customername

FROM Customer

WHERE CreditLimit=10000;

sql example where clause1
SQL Example – WHERE clause
  • Example 5: Save as example 5
    • Find the name of customer 148.
slide23

Example 5

SQL Query to Find Customer 148

slide24

SQL Comparison Operators For WHERE clause

NOT Warehouse =‘3’

LIKE: LIKE ‘a*’, LIKE ‘*s’, Like ‘*Oxford*’

(NOT) BETWEEN 45000 AND 78000

(NOT) IN (123, 345)

sql examples
SQL Examples
  • Example 6: Save as example 6
    • Find the customer name for every customer located in the city of Grove
sql examples1
SQL Examples
  • Example 7: Save as example 7
    • List the number, name, credit limit, and balance for customers with credit limits that exceed their balances.
sql examples compound conditions
SQL Examples – Compound Conditions
  • Example 8: Save as example 8
    • List the description of all parts that are located in warehouse 3 and for which there are more than 20 units on hand.
slide30

Example 8

SQL Query with Compound Condition using ‘AND’

sql examples compound conditions1
SQL Examples – Compound Conditions
  • Example 9: Save as example 9
    • List the descriptions of all parts that are located in warehouse 3 or for which there are more than 20 units on hand.
slide32

Example 9

SQL Query using ‘OR’

sql examples2
SQL Examples
  • Example 10: Save as example 10
    • List the description of all parts that are not in warehouse 3.
    • Use “NOT” (i.e., where NOT A = 100;)
slide34

Example 10

SQL Query using ‘NOT’

sql examples3
SQL Examples
  • Example 11: Save as example 11
    • List the number, name, and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.
    • (NOT) BETWEEN 45000 AND 78000
slide36

Example 11

Query with ‘BETWEEN’ Operator

sql examples computed field
SQL Examples – Computed Field
  • Computed field can involve:
    • addition(+), subtraction(-), Multiplication(*), or division (/)
  • Example 12: Save as example 12
    • List the number, name and available credit for all customers.
    • Use “AS” for assigning a new field name
slide38

Example 12

SQL Query with Computed Field

sql examples computed field1
SQL Examples – Computed Field
  • Computed field can involve:
    • addition(+), subtraction(-), Multiplication(*), or division (/)
  • Example 13: Save as example 13
    • List the number, name, and available credit for all customers with credit limits that exceed their balances.
slide40

Example 13

SQL Query with Computed

Field and Condition

sql examples like and in
SQL Examples – LIKE and IN
  • Example 14: Save as example 14
    • List the number, name, and complete address of every customer located on a street that contain the letters “Oxford.”
      • Customer names begin with B: Like B*
      • Customer names end with E: Like *E
      • Fine exact customer last name: like ‘*Choi*’
slide42

Example 14

SQL Query with ‘LIKE’ Operator

sql examples like and in1
SQL Examples – LIKE and IN
  • Example 15: Save as example 15
    • List the number, name, and credit limit for every customer with a credit of $7,500, $10,000, or $15,000.
    • IN (7500, 10000, 15000);
slide44

Example 15

SQL Query with ‘IN’ Operator

sql examples4
SQL Examples
  • Default value of ORDER BY: ascending
  • Example 16: Save as example 16
    • List the number, name, and credit limit of all customers. Sort the customers by name in ascending order .
slide46

Example 16

SQL Query to Sort Data

sql examples5
SQL Examples
  • Default value of ORDER BY: ascending
  • Example 17: Save as example 17
    • List the number, name, and credit limit of all customers. Sort the customers by name (minor) in ascending order within credit limit (major) in descending order.
    • What about this case?
      • customer name (minor) and credit limit (major)
slide48

Example 17

SQL Query to Sort on Multiple Fields