Vendor Idnumber(4)PRIMARY KEY

CREATE TABLE vendors

(

vendor_idNUMBER(4)PRIMARY KEY,

stateCHAR(2),

invoice_totalVARCHAR2(4,2),

invoice_dateDATE,

phoneCHAR(9),

CONSTRAINT vendors_state_fk FOREIGN KEY (state) REFERENCES addresses(state)

);

ALTER TABLE vendors (MAKES PRIMARY KEY)

ADD CONSTRAINT vendors_vendor_id_pk PRIMARY KEY (vendor_id);

(parantez yok, curly bracket hic yok)

CONSTRAINTS: primary key – foreign key – unique – not null – check (For ALTER TABLE commands, only NOT NULL is not ADD but is MODIFY)

ALTER TABLE vendors (MAKES NOT NULL)

MODIFY (phone CONSTRAINT vendors_phone_nn NOT NULL);

ALTER TABLE vendors (MAKES NOT NULL)

MODIFY vendor_name VARCHAR2(100);

ALTER TABLE books(ADDS COLUMN)

ADD catcode VARCHAR2(3) PRIMARY KEY;

ALTER TABLE books(DROPS COLUMN)

DROP COLUMN catcode;

INSERT INTO invoices

(invoice_id, invoice_category, invoice_date)

VALUES

('5626526', 'Business', '03-DEC-09');

DELETE FROM invoices

WHERE invoice_id = 100 AND invoice_sequence = 1;

ALTER TABLE invoices

ADD CONSTRAINT payment_total_ck1

CHECK ((first set of conditions there are two things being asked for) OR

(second set of conditions there are two things being checked));

CREATE TABLE invoices

(

invoice_idNUMBERPRIMARY KEY,

invoice_totalNUMBER(9,2)NOT NULL CHECK(invoice_total >=0),

payment_totalNUMBER (9,2)DEFAULT 0,

CHECK(payment_total >=0)

);

UPDATE invoices (names the table)

SET credit_total = credit_total + 100, (names the column and the new value)

WHERE invoice_number = '2865' (specifies condition)

COMMIT; – ROLLBACK; (1-word commands)

RENAME vendors TO vendor;

TRUNCATE TABLE vendor; (deletes all data from a table)

DROP TABLE vendor (deletes the table from the schema)

the sql statements can be divided into 2

DML means Data manipulation language that lets you work with the data in the database

DDL data definition language that lets you work with the objects in the database

SQL programmers use DML database administrators use DDL

/*this is a block comment*/

--this is a single line comment

SELECT orders.order_id, orders.customer_id, order_date, customer_first_name, customer_last_name

FROM orders JOIN customers

ON orders.customer_id = customers.customer_id

WHERE shipped_date >= '01-JUN-08'

AND shipped_date<= '31-JUL-08'

ORDER BY shipped_date ASC, customer_id DESC;

SELECT vendor_name, invoice_number, invoice_date, invoice_total-payment_total-credit_total AS balance_due

FROM vendors JOIN invoices

ON vendors.vendor_id = invoices.vendor_id

WHERE (invoice_total-payment_total-credit_total) !=0

ORDER BY vendor_name ASC;

SELECT invoice_number, invoice_date, invoice_total

FROM invoices

WHERE invoice_date BETWEEN '01-MAY-08' AND '31-MAY-08'

ORDER BY invoice_date;

SELECT *

(all columns from a table)

SELECT vendor_city, vendor_state,

vendor_city ||' ,' || vendor state

FROM vendors

(concatenates data: your get Toronto, ON

varchar2 or char must be used)

Arithmetic operations: Parenthesis are used like in algebra.

=<=>=

SELECT *

FROM null_sample

WHERE invoice_total IS NOT NULL (w/o null values)

WHERE invoice_total IS NULL (w null values)

WHERE invoice_total > 0 (non-zero values)

WHERE invoice_total = 0 (zero values)

Semi-colons at the end

CREATE TABLE AircraftMaintenance (

MaintenanceID smallint(4) unsigned NOT NULL AUTO_INCREMENT,

MaintenanceDescription varchar(200) NOT NULL,

MaintenanceDate Date NOT NULL,

MaintenanceFollowUp Date NOT NULL,

MaintenancePersonFName varchar(50) NOT NULL,

MaintenancePersonLName varchar(50) NOT NULL,

NumberHoursFlown int Not NULL,

AircraftTypeID int not null,

PRIMARY KEY (`MaintenanceID`)

) type=InnoDB;

ALTER TABLE AircraftMaintenance

ADD CONSTRAINT FK_AircraftTypeID FOREIGN KEY (AircraftTypeID)

REFERENCES aircrafttype (AircraftTypeID);

insert into AircraftMaintenance values

(100,'Check fuel lines','2009-11-20','2009-12-20','Sandy','Beach',10500,503),

(101,'Adjust door seals','2009-11-21','2009-12-20','Robin','Banks',8456,616),

(102,'Replace seat belt sign bulb','2009-11-22','2010-02-15','Rick','Shaw','12876','618'),

(103,'Check tires for wear','2009-11-25','2009-11-30','Willie','Makeit',6234,504);

UPDATE AircraftMaintenance

SET MaintenanceFollowUpDate='2010-02-17'

WHERE MaintenanceID='103';

DELETE FROM AircraftMaintenance

WHERE MaintenanceID ='103';

TRUNCATE TABLE employee;

SELECT airportid, airportcode, airportname, cityname, numrunways, numterminals

FROM `airport`

WHERE numrunways >2

AND numterminals <5

ORDER BY numrunways DESC;

SELECT airportcode, airportname, cityname

FROM airport

WHERE airportname LIKE "%z%"

OR airportname LIKE "%j%";

SELECT route.RouteID, FlightID FROM route

INNER JOIN flight ON route.routeid = flight.routeid

WHERE route.routeID >10 AND route.routeID <75;

PRACTICE EXAM

SHOW databases; (to see what database is there)

USE Northwoods; (to make sure)

CREATE TABLE student (table creation)

(

s_id INT(6) primary key,

s_last varchar(30),

s_first varchar(30),

s_mi char(1),

s_address varchar(25),

s_city varchar(20),

s_state char(1),

s_zip char(10),

s_class char(2),

s_dob date,

s_pin SMALLINT(4),

f_id SMALLINT(3),

time_enrolled varchar(30)

)type=InnoDB; (don't forget this)

ALTER TABLE student (fk adding)

ADD CONSTRAINT FK_grade FOREIGN KEY (grade)

REFERENCES enrollment (grade);

INSERT INTO term VALUES

("loc_id", "bldg_code", "room", "capacity");

UPDATE term(deletes one value only)

SET loc_id = NULL

WHERE loc_id = '6';

ORACLE PL/SQL

Set Serveroutput On;

CREATE OR REPLACE PROCEDURE insert_glaccount

(

Account_Number_Parameter General_Ledger_Accounts.Account_Number%Type,

Account_Description_Parameter General_Ledger_Accounts.Account_Description%TYPE

)

AS

Begin

INSERT INTO General_Ledger_Accounts

Values(Account_Number_Parameter, Account_Description_Parameter);

Exception

When DUP_VAL_ON_INDEX Then

Dbms_Output.Put_Line ('A DUP_VAL_ON_INDEX error occured.');

When Others Then

Dbms_Output.Put_Line ('An unknown exception occured.');

END;

/

CREATE a procedure

BEGIN

SELECT status INTO current_status

FROM term

WHERE term.id = current_term.id

IF current_status = 'open' THEN

new.status := 'closed'

ELSE

new.status := 'open';

END IF;

UPDATE

WHERE term.id = current_term.id

DROP TABLE assignment; (deletes the table)

DROP TABLE assignment, employees; (deletes more)

Semi-colons at the end

CREATE TABLE vendors

(

vendor_idNUMBER(4)PRIMARY KEY,

stateCHAR(2),

invoice_totalVARCHAR2(4,2),

invoice_dateDATE,

phoneCHAR(9),

CONSTRAINT vendors_state_fk FOREIGN KEY (state) REFERENCES addresses(state)

);

ALTER TABLE vendors (MAKES PRIMARY KEY)

ADD CONSTRAINT vendors_vendor_id_pk PRIMARY KEY (vendor_id);

(parantez yok, curly bracket hic yok)

CONSTRAINTS: primary key – foreign key – unique – not null – check (For ALTER TABLE commands, only NOT NULL is not ADD but is MODIFY)

ALTER TABLE vendors (MAKES NOT NULL)

MODIFY (phone CONSTRAINT vendors_phone_nn NOT NULL);

ALTER TABLE vendors (MAKES NOT NULL)

MODIFY vendor_name VARCHAR2(100);

ALTER TABLE books(ADDS COLUMN)

ADD catcode VARCHAR2(3) PRIMARY KEY;

ALTER TABLE books(DROPS COLUMN)

DROP COLUMN catcode;

INSERT INTO invoices

(invoice_id, invoice_category, invoice_date)

VALUES

('5626526', 'Business', '03-DEC-09');

DELETE FROM invoices

WHERE invoice_id = 100 AND invoice_sequence = 1;

ALTER TABLE invoices

ADD CONSTRAINT payment_total_ck1

CHECK ((first set of conditions there are two things being asked for) OR

(second set of conditions there are two things being checked));

CREATE TABLE invoices

(

invoice_idNUMBERPRIMARY KEY,

invoice_totalNUMBER(9,2)NOT NULL CHECK(invoice_total >=0),

payment_totalNUMBER (9,2)DEFAULT 0,

CHECK(payment_total >=0)

);

UPDATE invoices (names the table)

SET credit_total = credit_total + 100, (names the column and the new value)

WHERE invoice_number = '2865' (specifies condition)

COMMIT; – ROLLBACK; (1-word commands)

RENAME vendors TO vendor;

TRUNCATE TABLE vendor; (deletes all data from a table)

DROP TABLE vendor (deletes the table from the schema)

the sql statements can be divided into 2

DML means Data manipulation language that lets you work with the data in the database

DDL data definition language that lets you work with the objects in the database

SQL programmers use DML database administrators use DDL

/*this is a block comment*/

--this is a single line comment

SELECT orders.order_id, orders.customer_id, order_date, customer_first_name, customer_last_name

FROM orders JOIN customers

ON orders.customer_id = customers.customer_id

WHERE shipped_date >= '01-JUN-08'

AND shipped_date<= '31-JUL-08'

ORDER BY shipped_date ASC, customer_id DESC;

SELECT vendor_name, invoice_number, invoice_date, invoice_total-payment_total-credit_total AS balance_due

FROM vendors JOIN invoices

ON vendors.vendor_id = invoices.vendor_id

WHERE (invoice_total-payment_total-credit_total) !=0

ORDER BY vendor_name ASC;

SELECT invoice_number, invoice_date, invoice_total

FROM invoices

WHERE invoice_date BETWEEN '01-MAY-08' AND '31-MAY-08'

ORDER BY invoice_date;

SELECT *

(all columns from a table)

SELECT vendor_city, vendor_state,

vendor_city ||' ,' || vendor state

FROM vendors

(concatenates data: your get Toronto, ON

varchar2 or char must be used)

Arithmetic operations: Parenthesis are used like in algebra.

=<=>=

SELECT *

FROM null_sample

WHERE invoice_total IS NOT NULL (w/o null values)

WHERE invoice_total IS NULL (w null values)

WHERE invoice_total > 0 (non-zero values)

WHERE invoice_total = 0 (zero values)