SQL
Table of Contents
S.No
Particulars
Page No 1 Sql – Introduction 2 2 Table Basics 3 3 Selecting Data 4 4 Creating Tables 7 5 Inserting into a Table 9 6 Updating Records 10 7 Deleting Records 11 8 Drop a Table 12 9 Advanced Queries 13 10
CHAPTER: I
What is SQL?:
“SQL – Structured Query Language”
SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.
Everything you learn here will be ANSI SQL compliant and should work with most SQL databases such as Oracle, SQL Server, mySQL, MS Access, Informix, Sybase, or any other ANSI SQL compliant database.
There are different versions of sql used such as Sql2000, Sql2005, Sql2008, etc...
CHAPTER: II
Table Basics:
A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather”. City, state, high, and low are the columns. The rows contain the data for this table:
Weather city state high low Phoenix Arizona 105 90 Tucson Arizona 101 92 Flagstaff Arizona 88 69 San Diego California 77 60 Albuquerque New Mexico 80 72
CHAPTER: III
Selecting Data:
The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:
Select "column1"
[,"column2",etc]
from "tablename"
[where "condition"];
[] = optional
The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.
The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.
The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.
Conditional selections used in the where clause:
= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <> Not equal to
LIKE *See note below The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified.
For example:
Select first, last, city
from empinfo
where first LIKE 'Er%';
This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.
Or you can specify,
select first, last
from empinfo
where last LIKE '%s';
This statement will match any last names that end in a 's'.
select * from empinfo
where first = 'Eric';
This will only select rows where the first name equals 'Eric' exactly.
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
Some of the SQL select statements:
select first, last, city from empinfo;
select last, city, age from empinfo
where age > 30;
select first, last, city, state from empinfo
where first LIKE 'J%';
select * from empinfo;
select first, last, from empinfo
where last LIKE '%s';
select first, last, age from empinfo
where last LIKE '%illia%';
select * from empinfo where first = 'Eric';
Exercises :( Try to work yourself)
Enter select statements to:
Display the first name and age for everyone that's in the table.
Display the first name, last name, and city for everyone that's not from Payson.
Display all columns for everyone that is over 40 years old.
Display the first and last names for everyone whose last name ends in an "ay".
Display all columns for everyone whose first name equals "Mary".
Display all columns for everyone whose first name contains "Mary".
CHAPTER: IV
Creating Tables:
The create table statement is used to create a new table. Here is the format of a simple create table statement:
Create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type");
Format of create table if you were to use optional constraints:
[Constraints: used to limit the type of data that can go into table. Such constraints can be specified when the table is first created via the CREATE TABLE statement or after the table is already created via the ALTER TABLE statement
NOT NULL Constraint: Ensures that a column cannot have Null value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
Primary Key Constraint: Used to uniquely identify a row in the table.
Foreign Key Constraint: Used to ensure referential integrity of the data.
For more details visit http://www.1keydata.com/sql/sql-constraint.html ]
Create table "tablename"
("column1" "data type"
[constraint],
"column2" "data type"
[constraint],
"column3" "data type"
[constraint]);
[ ] = optional
Note: You may have as many columns as you'd like, and the constraints are optional.Example:
create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));
To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you seperate each column definition with a comma. All SQL statements should end with a ";".
The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).
Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.
Here are the most common Data types:
char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. varchar(size) Variable-length character string. Max size is specified in parenthesis. number(size) Number value with a max number of column digits specified in parenthesis. date Date value number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal. What are constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table.
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
CHAPTER: V
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
After they're inserted into the table, enter select statements to:
Select all columns for everyone in your employee table.
Select all columns for everyone with a salary over 30000.
Select first and last names for everyone that's under 30 years old.
Select first name, last name, and salary for anyone with "Programmer" in their title.
Select all columns for everyone whose last name contains "ebe".
Select the first name for everyone whose first name equals "Potsy".
Select all columns for everyone over 80 years old.
Select all columns for everyone whose last name ends in "ith".
CHAPTER: VI
Updating Records:
The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.
update "tablename"
set "columnname" =
"newvalue"
[,"nextcolumn" =
"newvalue2"...]
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
[] = optional
[The above example was line wrapped for better viewing on this Web page.]
Examples:
update phone_book
set area_code = 623
where prefix = 979;
update phone_book
set last_name = 'Smith', prefix=555, suffix=9292
where last_name = 'Jones';
update employee
set age = age+1
where first_name='Mary' and last_name='Williams';
Update statement exercises:
After each update, issue a select statement to verify your changes.
Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams.
Dirk Smith's birthday is today, add 1 to his age.
All secretaries are now called "Administrative Assistant". Update all titles accordingly.
Everyone that's making under 30000 are to receive a 3500 a year raise.
Everyone that's making over 33500 are to receive a 4500 a year raise.
All "Programmer II" titles are now promoted to "Programmer III".
All "Programmer" titles are now promoted to "Programmer II".
CHAPTER: VII
Deleting Records:
The delete statement is used to delete records or rows from the table.
delete from "tablename"
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
[ ] = optional
[The above example was line wrapped for better viewing on this Web page.]
Examples:
delete from employee;
Note: if you leave off the where clause, all records will be deleted!
delete from employee
where lastname = 'May';
delete from employee
where firstname = 'Mike' or firstname = 'Eric';
To delete an entire record/row from a table, enter "delete from" followed by the table name, followed by the where clause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.
Delete statement exercises
(Use the select statement to verify your deletes):
Jonie Weber-Williams just quit, remove her record from the table.
It's time for budget cuts. Remove all employees who are making over 70000 dollars.
CHAPTER: VIII
Drop a Table:
The drop table command is used to delete a table and all rows in the table.
To delete an entire table including all of its rows, issue the drop table command followed by the tablename. drop table is different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. Dropping the table removes the table definition as well as all of its rows.
drop table "tablename"
Example:
drop table myemployees_ts0211;
Drop Table exercises
Drop your employee table.
Page | 5