Last asgn
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)