Sas Guide Paper

Only available on StudyMode
  • Topic: SQL, Given name, Family name
  • Pages : 22 (4616 words )
  • Download(s) : 55
  • Published : January 23, 2013
Open Document
Text Preview
Sample Table: empinfo|
first| last| id| age| city| state|
John| Jones| 99980| 45| Payson| Arizona|
Mary| Jones| 99982| 25| Payson| Arizona|
Eric| Edwards| 88232| 32| San Diego| California|
Mary Ann| Edwards| 88233| 32| Phoenix| Arizona|
Ginger| Howell| 98002| 42| Cottonwood| Arizona|
Sebastian| Smith| 92001| 23| Gila Bend| Arizona|
Gus| Gray| 22322| 35| Bagdad| Arizona|
Mary Ann| May| 32326| 52| Tucson| Arizona|
Erica| Williams| 32327| 60| Show Low| Arizona|
Leroy| Brown| 32380| 22| Pinetop| Arizona|
Elroy| Cleaver| 32382| 22| Globe| Arizona|

Display the first name, last name, and city for everyone that's not from Payson.

select first, last, city
from empinfo
where city <> 'Payson'

Display all columns for everyone that is over 40 years old.
select * from empinfo
where age>40

Display the first and last names for everyone whose last name ends in an "ay". select first, last
from empinfo
where last LIKE '%ay'

Display all columns for everyone whose first name equals "Mary".

select * from empinfo
where first='Mary'

Display all columns for everyone whose first name contains "Mary".

select * from empinfo
where first LIKE '%Mary%'

Create Table Exercise
You have just started a new company. It is time to hire some employees. You will need to create a table that will contain the following information about your new employees: firstname, lastname, title, age, and salary. After you create the table, you should receive a small form on the screen with the appropriate column names. If you are missing any columns, you need to double check your SQL statement and recreate the table. Once it's created successfully, go to the "Insert" lesson. IMPORTANT: When selecting a table name, it is important to select a unique name that no one else will use or guess. Your table names should have an underscore followed by your initials and the digits of your birth day and month. For example, Tom Smith, who was born on November 2nd, would name his table myemployees_ts0211 Use this convention for all of the tables you create. Your tables will remain on a shared database until you drop them, or they will be cleaned up if they aren't accessed in 4-5 days. If "support" is good, I hope to eventually extend this to at least one week. When you are finished with your table, it is important to drop your table (covered in last lesson). create table employees_ap0377

(firstname varchar(20), lastname varchar(20), title varchar(20), age number (3), salary number(7);

Inserting into a Table
The insert statement is used to insert or add a row of data into the table. To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not. insert into "tablename"

(first_column,...last_column)
values (first_value,...last_value);
In the example below, the column name first will match up with the value 'Luke', and the column name state will match up with the value 'Georgia'. Example:
insert into employee
(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars Nest',
'Hazard Co', 'Georgia');
Note: All strings should be enclosed between single quotes: 'string' Insert statement exercises
It is time to insert data into your new employee table.
Your first three employees are the following:
Jonie Weber, Secretary, 28, 19500.00
Potsy Weber, Programmer, 32, 45300.00
Dirk Smith, Programmer II, 45, 75020.00
Enter these employees into your table first, and then insert at least 5...
tracking img