Beginning SQL Tutorial - PowerPoint PPT Presentation

Loading...

PPT – Beginning SQL Tutorial PowerPoint presentation | free to download - id: 3efa43-ZmM0N



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Beginning SQL Tutorial

Description:

Beginning SQL Tutorial Author Jay Mussan-Levy What is SQL? Structured Query Language Communicate with databases Used to created and edit databases. – PowerPoint PPT presentation

Number of Views:449
Avg rating:3.0/5.0
Slides: 32
Provided by: JayMuss7
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Beginning SQL Tutorial


1
Beginning SQL Tutorial
  • Author Jay Mussan-Levy

2
What is SQL?
  • Structured Query Language
  • Communicate with databases
  • Used to created and edit databases.
  • Also used to create queries, forms, and reports

3
Table Basics
  • A Table is an object
  • Database data is stored in Tables
  • Each table has a unique name
  • Columns have various attributes, such as column
    name and data type
  • Rows contain records or data for the columns

4
Weather Sample Table
City State High Low
Phoenix Arizona 105 90
Tuscon Arizona 101 92
Flagstaff Arizona 88 69
San Diego California 77 60
Albuquerque New Mexico 80 60
5
Selecting Data
  • The Select statement is used to get data which
    matches the specified criteria. Here is the
    basic syntax
  • ex)
  • select columnname1, columnname2
  • from tablename
  • where condition

6
Conditions Used In Where Clause
  • equals
  • gt greater than
  • lt less than
  • gt greater than or equal to
  • lt less than or equal to
  • ltgt not equal to

7
Like
  • Used to make complex searching easy. If you are
    trying to find all peoples names which begin
    with E for example
  • ex) select firstname from employee
  • where firstname LIKE 'E'

8
Selecting Exercise
  • Go to http//sqlcourse.com/select.html
  • Scroll to the bottom of the page.
  • Do exercises 2, 4, and 6.
  • The tables you need are in your handout packet

9
Creating Tables
  • The statement to use is create table
  • Here is the syntax
  • create table tablename
  • (columnname, datatype,
  • columnname2, datatype,
  • columnname3, datatype)

10
Creating Tables contd
  • Here is a real example
  • create table employee
  • (first varchar(15),
  • last varchar(20),
  • age number(3),
  • address varchar(30),
  • city varchar(20),
  • state varchar(20))

11
Creating Tables - Steps
  1. Use the command create table
  2. Follow it with the correct table name
  3. Put a parenthesis and type in the first column
    name
  4. Follow it with the variable type (we will list
    them in a minute) then a comma
  5. Continue the previous two steps until you have
    all your columns accounted for
  6. Then put a parenthesis to close the columnname
    section and add a after it

12
Creating Tables - Rules
  • Table and column names must start with a letter
  • They can not exceed 30 characters
  • They can not be key words such as create, insert,
    select, etc.

13
Creating Tables Variables
  • If you took algebra then y2x might be familiar.
    y and x are unknown information, which is a
    variable.
  • Now a string is a bunch of letters and numbers
  • A number is a bunch of numbers
  • A data type determines what a variable can hold,
    i.e. strings or numbers

14
Creating Tables Data Types
  • char(size) - all column entries must be size,
    which you specify at the beginning, if size 10
    then you must have ten characters
  • varchar(size) - all column entries must be less
    than or equal to whatever size is, if size is 10,
    then the string must be between 1-10 charcters

15
Creating Tables Data Types contd
  • number(size) - a number value that can not
    exceed, size columns, for example if you have
    size 10, then you can only have 10 different
    digit places, like 1,000,000,000
  • date - date value
  • number(size,d) - This works the same as the
    regular number except d represents of columns
    after the decimal.

16
Creating Tables - Constraints
  • A constraint is a rule.
  • Some examples constraints are
  • unique - no two entries will be the same
  • not null - no entry can be blank
  • primary key - unique identification of each
    row
  • primary keys will be very important to you as
    your knowledge of databases progresses

17
Creating Table Exercise
  • Go to http//sqlcourse.com/create.html
  • Do the exercise listed under the heading
  • Create Table Exercise

18
Inserting Information into Tables
  • To insert into tables you need only use the
  • keyword insert. Here is the syntax
  • insert into "tablename"
  • (first_column, ..., last_column)
  • values (first_value, ...,last value)

19
Inserting Information into Tables
  • Here is a practical example
  • insert into employees
  • (first, last, age, address, city, state)
  • values ( 'Luke', 'Duke', 45, '2130 Boars Nest',
  • 'Hazard Co', 'Georgia')

20
Inserting Information into Tables Steps
  • All strings should be enclosed by single
    quotes 'string'
  • Use the keyword "insert into" followed by the
    tablename
  • Then on the next line, in parenthesis, list all
    the columns you are inserting values for.
  • Then on the line after, type values, then in
    parenthesis, put the values in the same order as
    the columns they belong to

21
Inserting Information Exercise
  • Go to http//sqlcourse.com/insert.html
  • All the employees you need to insert are listed
    in
  • your handout
  • Do exercises 3, 4, 5, and 8 under the heading
  • Insert statement exercises

22
Updating Records
  • To update records use the "update" statement.
  • Here is the syntax
  • update tablename
  • set columnname newvalue,
  • nextcolumn newvalue2, ...
  • where columnname OPERATOR
  • value andor columnname2 OPERATOR value

23
Updating Records contd
  • Here are some practical examples
  • ex)
  • update phone_book
  • set area_code 623
  • where prefix 979
  • This changes the area code all numbers
  • beginning with 979 to 623

24
Updating Records contd
  • update phone_book
  • set last_name 'Smith', prefix555, sufix9292
  • where last_name 'Jones'
  • This changes everyone whose last name is Jones
  • to Smith and their number to 555-9292

25
Updating Records Exercise
  • Go to http//sqlcourse.com/update.html
  • Do Exercises 1, 2, 3, 5 under the heading
  • Update statement exercises

26
Deleting Records
  • Here is the syntax
  • delete from tablename
  • where columnname OPERATOR value
  • andor columnname2 OPERATOR value

27
Deleting Records Examples
  • ex) delete from employees
  • deletes all records from that table
  • ex) delete from employee
  • where lastname'May'
  • deletes all records for people whose last
    name is May
  • ex) delete from employee
  • where firstname'Mike' or firstname'Eric'
  • deletes all records for anyone whose first
    name is Mike or Eric

28
Deleting Records Exercise
  • Go to http//sqlcourse.com/delete.html
  • Do all the exercises under the heading
  • Delete statement exercises

29
Deleting Tables
  • Use the drop command
  • drop table "tablename"
  • drop table employees
  • Bye Bye Table, Hello Corporate Espoinage )

30
Deleting Table Exercise
  • Go to http//sqlcourse.com/drop.html
  • Do the Exercise under the heading
  • Drop Table exercises

31
Further SQL Information
  • Congratulations, you have just learned the
  • basics of SQL!!
  • However, there are still a few more advanced
  • concepts that you should learn before you can
  • say your good Querying, Reports, and Forms
  • If you have any questions e-mail me at
  • greywolf_at_eden.rutgers.edu
About PowerShow.com