SQL

CS2020: Web Science, Sytems and Design

SQL: a brief overview

Structured Querry Language = A language to access databases

In order to alter, insert or retrieve data from a database, you will use a specialized Database language. Most modern databases will allow you to do this via SQL or a version of SQL. SQL stands for Structured Query Language and is a standard for accessing and updating data in a database (once you are connected to it....you will connect either via a database tool or as we will do via Java&JDBC).

SQL is a conceptually easy language to understand. We will not be teaching SQL in this class...but, only using simple commands to access it. Note below table is the name of the table you are accessing.

Creating Table

Altering Table

Roles, Granting Privileges

YET MORE ON SQL

SOME SIMPLE SQL COMMANDS TO MANIPULATE DATA IN A TABLE.

>see reading and above liks "More on SQL" for additional commands.

SQL command Meaning

SELECT

select * from table
select all the keys/fields from the table

select Distribute from table select only the field Distribute from the table

select * from table
where Name is 'Butch Grewe' Select all keys/fields from table where the key/field Name = Butch Grewe

INSERT

insert into table
values ('James Bond', '1 Eiffel Tower', 'Paris', 'France','Spy Software')
Insert into the table, the data entry having the following key/field entries in order:
Name = James Bond
Street Address = 1 Eiffel Tower
City = Paris
State = France (huh???)
Distribute = Spy Software

UPDATE

update table set age=6 where Name='Butch Grewe'
updates any records with the name 'Butch Grewe' to have the value of 6 for the field age.

DELETE

delete from table where age=10
From table, deletes all records/rows that have the field of age with the value 10.

DESCRIBE

describe table
List all of the keys/fields that makeup this table.

the where clause....

where a1=x and a2=y ALL of the conditions must be true

where a1=x or a1=y Only one of the conditions need to be true

where a1=x or a2 >z Only one of the conditions need to be true

where a1 between x and y Range of values

where a1 not between x and y NOT in range of values

where a1 in (x, y, z) Must be in the list of items

where a1 not in (x,y,z) Must NOT be in the list of items.

where a1 != x Must be NOT equal to value.

where a1 like 'M%' Pattern Search....means a1 must start with a capital M.

where a1 like '%son%' Pattern Search....means a1 must have somewhere in its value the string 'son'. Examples that would be good: Furgeson, Forsonning, etc.

where a1 not like 'M%' Pattern Search....means a1 does NOT start with a capital M.

where a1 like '%son%' Pattern Search....means a1 must have somewhere in its value the string 'son'. Examples that would be good: Furgeson, Forsonning, etc.

the order by clause

select * from table order by name
Order alpahbetically in descending order the records in the table, by their value stored in the column name.

Creating a Table

Using SQL*Plus to create a Table

below are two examples of creating tables. The second specifies the tablespace the table should appear in, used if you have more than one tablespace. It also specifies the initial size, how it grows in size and minimum number of extents allocated for it.

SQL> create table customer(

last_name       varchar2(30) not null,

state_cd          varchar(2),

sales                 number);

SQL> create table products(

name              varchar2(30) not null,

description    varchar2(300),

price             number(4,2) )

tablespace     productspace

storage(initial 25k next 25k minextents 1);

Altering a Table

Using SQL*Plus to alter a Table

Use the SQL alter command to add or drop columns in a table after it has been created.

Suppose we have a table customer that has the following fields: last_name, state_CD, sales

To add a column tax_exempt_id

SQL>alter table customer

add tax_exempt_id varchar2(20);

To drop the column sales

SQL> alter table customer

drop column sales;