Beginning sql tutorial
Download
1 / 31

- PowerPoint PPT Presentation


  • 183 Views
  • Updated On :

Beginning SQL Tutorial. Author Jay Mussan-Levy. What is SQL?. Structured Query Language Communicate with databases Used to created and edit databases. Also used to create queries, forms, and reports. Table Basics. A Table is an object Database data is stored in Tables

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 '' - rory


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
Beginning sql tutorial l.jpg

Beginning SQL Tutorial

Author Jay Mussan-Levy


What is sql l.jpg
What is SQL?

  • Structured Query Language

  • Communicate with databases

  • Used to created and edit databases.

  • Also used to create queries, forms, and reports


Table basics l.jpg
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



Selecting data l.jpg
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”


Conditions used in where clause l.jpg
Conditions Used In Where Clause

= equals

> greater than

< less than

>= greater than or equal to

<= less than or equal to

<> not equal to


Slide7 l.jpg
Like

Used to make complex searching easy. If you are trying to find all people’s names which begin with E for example:

ex) select firstname from employee

where firstname LIKE 'E%';


Selecting exercise l.jpg
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


Creating tables l.jpg
Creating Tables

The statement to use is create table

Here is the syntax:

create table “tablename”

(“columnname”, “datatype”,

“columnname2”, “datatype”,

“columnname3”, “datatype”);


Creating tables cont d l.jpg
Creating Tables cont’d

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));


Creating tables steps l.jpg
Creating Tables - Steps

  • Use the command create table

  • Follow it with the correct table name

  • Put a parenthesis and type in the first column name

  • Follow it with the variable type (we will list them in a minute) then a comma

  • Continue the previous two steps until you have all your columns accounted for

  • Then put a parenthesis to close the columnname section and add a ; after it


Creating tables rules l.jpg
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.


Creating tables variables l.jpg
Creating Tables – Variables

  • If you took algebra then y=2x 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


Creating tables data types l.jpg
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


Creating tables data types cont d l.jpg
Creating Tables – Data Types cont’d

  • 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.


Creating tables constraints l.jpg
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


Creating table exercise l.jpg
Creating Table Exercise

Go to http://sqlcourse.com/create.html

Do the exercise listed under the heading

Create Table Exercise


Inserting information into tables l.jpg
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”);


Inserting information into tables19 l.jpg
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');


Inserting information into tables steps l.jpg
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


Inserting information exercise l.jpg
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


Updating records l.jpg
Updating Records

To update records use the "update" statement.

Here is the syntax:

update “tablename”

set “columnname” = “newvalue”,

“nextcolumn” = “newvalue2”, ...

where “columnname” OPERATOR

“value” and|or “columnname2 OPERATOR “value”


Updating records cont d l.jpg
Updating Records cont’d

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


Updating records cont d24 l.jpg
Updating Records cont’d

update phone_book

set last_name = 'Smith', prefix=555, sufix=9292

where last_name = 'Jones';

This changes everyone whose last name is Jones

to Smith and their number to 555-9292


Updating records exercise l.jpg
Updating Records Exercise

Go to http://sqlcourse.com/update.html

Do Exercises 1, 2, 3, 5 under the heading:

Update statement exercises


Deleting records l.jpg
Deleting Records

Here is the syntax:

delete from “tablename”

where “columnname” OPERATOR “value”

and|or “columnname2” OPERATOR “value”


Deleting records examples l.jpg
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


Deleting records exercise l.jpg
Deleting Records Exercise

Go to http://sqlcourse.com/delete.html

Do all the exercises under the heading:

Delete statement exercises


Deleting tables l.jpg
Deleting Tables

Use the drop command

drop table "tablename";

drop table employees;

Bye Bye Table, Hello Corporate Espoinage =)


Deleting table exercise l.jpg
Deleting Table Exercise

Go to http://sqlcourse.com/drop.html

Do the Exercise under the heading:

Drop Table exercises


Further sql information l.jpg
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

[email protected]


ad