Transcript Lecture 2 - California State University, East Bay

CS6320 - SQL
The Structured Query Language
(SQL)
Composed of two categories:
• Data Definition



create database
create table
drop database
• Data Manipulation




used to manipulate the data
select
delete
update
Data Definition




CREATE TABLE - allows you to create
a table definition in a database
CREATE DATABASE - allows you to
create a database
DROP TABLE - removes a table from
a database
ALTER TABLE - modifies the definition
of a table in a database
Create Table
Example 1:
create table customer(
last_name
varchar2(30) not null,
state_cd
varchar(2),
sales
number);
Example 2:
create table products(
name
varchar2(30) not null,
description varchar2(300),
price
number(4,2) )
tablespace
productspace
storage(initial 25k next 25k minextents 1);
Alter Table
Add column
alter table customer
add tax_exempt_id
Drop column
alter table customer
drop column sales;
varchar2(20);
Data Manipulation Language

SELECT - query the database
• select * from customer where id > 1001

INSERT - adds new rows to a table.
• Insert into customer values (1009, ‘John
Doe’)

DELETE - removes a specified row
• delete from customer where amount = 100

UPDATE - modifies an existing row
• update customers set amount = 10 where
id > 1003
Select

Get all data from table
select * from table

Get column Distribute data from table
select Distribute from table

Get all data where….
select * from table where Name is 'Butch Grewe'
Insert

Add following entry to table…order of
data fields MUST be the same as
when created table
insert into table
values ('James Bond', '1 Eiffel Tower',
'Paris', 'France','Spy Software')
Delete

Remove entries where….
delete from table where age=10
Describe

Get meta-data
describe table