InitHEADmain

3 files changed, 204 insertions, 0 deletions

diff --git a/Homework/cs5800/Exams/1.org b/Homework/cs5800/Exams/1.org
new file mode 100644
index 0000000..51d2fc1
--- /dev/null
+++ b/Homework/cs5800/Exams/1.org
@@ -0,0 +1,21 @@
+* EER and relational (notations, translation, ER -> EER)
+* Languages (DML - DBA or User, DDL - , VDL, SDL)
+* DBMS system components
+User queries and DDL queries paths
+* Conceptual Data Modeling (Focus on min-max) (ER and Enhanced ER model)
+Partial participation, categorization on unions,
+convert to integrity constraints
+
+non-null is an entity integrity constraint
+* Relational model concepts
+** Keys
+(Super key, candidate key, primary key, foreign key)
+studentno is a key, {studentno, birthdate} is a superkey
+sometimes there is more than one key
+* Data Definition Language (DDL)
+** Creating table, adding constraints, table attrributes
+** DBA uses
+** Attributes, primary keys, foreign keys
+
+** Drop table, Alter table
+** Constraints: null/not null, unique, CHECK
diff --git a/Homework/cs5800/Exams/2.study b/Homework/cs5800/Exams/2.study
new file mode 100644
index 0000000..8616d0c
--- /dev/null
+++ b/Homework/cs5800/Exams/2.study
@@ -0,0 +1,27 @@
+delimiter //
+create procedure get_user_deets3(in u_ssn varchar(9), out u_email varchar(64), out u_type varchar(64), out u_name varchar(64)) 
+begin 
+  if u_ssn in (select ssn from user)
+  then
+    select email, type, name
+    into u_email, u_type, u_name
+    from user where ssn=u_ssn;
+  end if;
+end //
+
+delimiter ; 
+
+
+
+delimiter //
+create function before_date (start date)
+returns integer deterministic
+begin
+declare count_before_date integer;
+select count(*) into count_before_date
+from reservation
+where date < start;
+return count_before_date;
+end //
+delimiter ;
+
diff --git a/Homework/cs5800/Exams/thing.sql b/Homework/cs5800/Exams/thing.sql
new file mode 100644
index 0000000..56edb66
--- /dev/null
+++ b/Homework/cs5800/Exams/thing.sql
@@ -0,0 +1,156 @@
+drop table ORDERS;
+drop table PRODUCTS;
+drop table CUSTOMERS;
+drop table OFFICES;
+drop table SALESREPS;
+
+create table ORDERS(
+ORDER_NUM varchar(6),
+ORDER_DATA date,
+CUST char(4),
+REP char(3),		
+MFR char(3),
+PRODUCT varchar(10),
+QTY int, 
+AMOUNT decimal (10,2),
+constraint pk_orders primary key (ORDER_NUM)
+);
+
+create table PRODUCTS(
+MFR_ID char(3),
+PRODUCT_ID varchar(10),
+DESCRIPTION varchar(20),
+PRICE decimal (10,2),
+QTY_ON_HAND int,
+constraint pk_products primary key(MFR_ID, PRODUCT_ID)
+);
+
+create table CUSTOMERS(
+CUST_NUM char(4),
+COMPANY varchar(20),
+CUST_REP char(3),
+CREDIT_LIMIT decimal (10,2),
+constraint pk_customers primary key (CUST_NUM)
+);
+
+create table OFFICES(
+OFFICE char(2),
+CITY varchar(20),
+REGION varchar(10),
+MGR char(3),
+TARGET decimal (10,2),
+SALES decimal (10,2),
+constraint pk_offices primary key (OFFICE)
+);
+
+create table SALESREPS(
+emp_num char(3),
+name varchar(20),
+age int,
+rep_office char(2),
+title varchar(10),
+manager char(3), 
+hire_date date,
+quota decimal (10,2),
+sales decimal (10,2),
+constraint pk_salesRep primary key (emp_num)
+);
+
+INSERT INTO CUSTOMERS VALUES('2101', 'Jones Mfg.', '106', '65000');
+INSERT INTO CUSTOMERS VALUES('2102', 'First Corp.', '101', '65000');
+INSERT INTO CUSTOMERS VALUES('2103', 'Acme Mfg.', '105', '50000');
+INSERT INTO CUSTOMERS VALUES('2105', 'AAA Investments','101', '45000' );
+INSERT INTO CUSTOMERS VALUES('2106', 'Fred Lewis Corp.', '102', '65000');
+INSERT INTO CUSTOMERS VALUES('2107', 'Ace International', '110', '35000');
+INSERT INTO CUSTOMERS VALUES('2108', 'Holm & Landis', '109', '55000');
+INSERT INTO CUSTOMERS VALUES('2109', 'Chen Associates', '103', '25000');
+INSERT INTO CUSTOMERS VALUES('2111', 'JCP Inc.', '103', '50000');
+INSERT INTO CUSTOMERS VALUES('2112', 'Zetacorp', '108', '50000');
+INSERT INTO CUSTOMERS VALUES('2113', 'Ian & Schmidt', '104', '20000');
+INSERT INTO CUSTOMERS VALUES('2114', 'Orion Corp.', '102', '20000');
+INSERT INTO CUSTOMERS VALUES('2115', 'Smithson Corp.', '101', '20000');
+INSERT INTO CUSTOMERS VALUES('2117', 'J.P. Sinclair', '106', '35000');
+INSERT INTO CUSTOMERS VALUES('2118', 'Miswest Sytems', '108', '60000');
+INSERT INTO CUSTOMERS VALUES('2119', 'Solomon Inc.', '109', '25000');
+INSERT INTO CUSTOMERS VALUES('2120', 'Rico Enterprises', '102', '50000');
+INSERT INTO CUSTOMERS VALUES('2121', 'QMA Assoc.', '103', '54000');
+INSERT INTO CUSTOMERS VALUES('2122', 'Three-Way Lines', '105', '30000');
+INSERT INTO CUSTOMERS VALUES('2123', 'Carter & sons', '102', '40000');
+INSERT INTO CUSTOMERS VALUES('2124', 'Peter Brothers', '107', '40000');
+
+INSERT INTO OFFICES VALUES ('11','New York','Eastern','106','575000','692637');
+INSERT INTO OFFICES VALUES ('12','Chicago','Eastern','104','800000','735042');
+INSERT INTO OFFICES VALUES ('13','Atlanta','Eastern','105','350000','367911');
+INSERT INTO OFFICES VALUES ('21','Los Angeles','Western','108','725000','835915');
+INSERT INTO OFFICES VALUES ('22','Denver','Western','108','300000','186042');
+
+INSERT INTO ORDERS VALUES ('112961','1999-12-17','2117','106','REI','2A44L',7,'31500');
+INSERT INTO ORDERS VALUES ('112963','1999-12-17','2103','105','ACI','41004',28,'3276');
+INSERT INTO ORDERS VALUES ('112968','1999-12-10','2102','101','ACI','41004',34,'3978');
+INSERT INTO ORDERS VALUES ('112975','1999-12-10','2111','103','REI','2A44G',6,'2100');
+INSERT INTO ORDERS VALUES ('112979','1999-12-10','2114','102','ACI','4100Z',6,'15000');
+INSERT INTO ORDERS VALUES ('112983','1999-12-27','2103','105','ACI','41004',6,'702');
+INSERT INTO ORDERS VALUES ('112987','1999-12-31','2103','105','ACI','4100Y',11,'27500');
+INSERT INTO ORDERS VALUES ('112989','1900-01-03','2101','106','FEA','114',6,'1458');
+INSERT INTO ORDERS VALUES ('112992','1999-11-04','2118','108','ACI','41002',10,'760');
+INSERT INTO ORDERS VALUES ('112993','1999-01-04','2106','102','REI','2A45C',24,'1896');
+INSERT INTO ORDERS VALUES ('112997','2000-01-08','2124','107','BIC','41003',1,'652');
+INSERT INTO ORDERS VALUES ('113003','2000-01-25','2108','109','IMM','779C',3,'5625');
+INSERT INTO ORDERS VALUES ('113007','2000-01-08','2112','108','IMM','773C',3,'2925');
+INSERT INTO ORDERS VALUES ('113012','2000-01-11','2111','105','ACI','41003',35,'3745');
+INSERT INTO ORDERS VALUES ('113013','2000-01-24','2118','108','BIC','41003',1,'652');
+INSERT INTO ORDERS VALUES ('113024','2000-01-20','2114','108','QSA','XK47',20,'7100');
+INSERT INTO ORDERS VALUES ('113027','2000-01-22','2103','105','ACI','41002',54,'4104');
+INSERT INTO ORDERS VALUES ('113034','2000-01-29','2107','110','REI','2A45C',8,'632');
+INSERT INTO ORDERS VALUES ('113036','2000-01-30','2107','110','ACI','4100Z',9,'22500');
+INSERT INTO ORDERS VALUES ('113042','2000-02-02','2113','101','REI','2A44R',5,'22500');
+INSERT INTO ORDERS VALUES ('113045','2000-02-02','2112','108','REI','2A44R',10,'45000');
+INSERT INTO ORDERS VALUES ('113048','2000-10-02','2120','102','IMM','779C',2,'3750');
+INSERT INTO ORDERS VALUES ('113049','2000-10-02','2118','108','QSA','XK47',2,'776');
+INSERT INTO ORDERS VALUES ('113051','2000-10-02','2118','108','QSA','XK47',4,'1420');
+INSERT INTO ORDERS VALUES ('113055','2000-02-15','2108','101','ACI','4100X',6,'150');
+INSERT INTO ORDERS VALUES ('113057','2000-02-18','2111','103','ACI','4100X',24,'600');
+INSERT INTO ORDERS VALUES ('113058','2000-02-23','2108','109','FEA','112',10,'1480');
+INSERT INTO ORDERS VALUES ('113062','2000-02-24','2124','107','FEA','114',10,'2430');
+INSERT INTO ORDERS VALUES ('113065','2000-02-27','2106','102','QSA','XK47',6,'2130');
+INSERT INTO ORDERS VALUES ('113069','2000-03-02','2109','107','IMM','775C',22,'31350');
+
+INSERT INTO PRODUCTS VALUES ('ACI','41002','Size 2 Widget', '76',167);
+INSERT INTO PRODUCTS VALUES ('ACI','41003','Size 3 Widget','107',207);
+INSERT INTO PRODUCTS VALUES ('ACI','41004','Size 4 Widget','117',139);
+INSERT INTO PRODUCTS VALUES ('ACI','4100X','Widget Adjuster','25',37);
+INSERT INTO PRODUCTS VALUES ('ACI','4100Y','Widget Remover','2750',25);
+INSERT INTO PRODUCTS VALUES ('ACI','4100Z','Size 1 Widget','55',277);
+INSERT INTO PRODUCTS VALUES ('ACI','4101','Widget Intaller','2500',28);
+INSERT INTO PRODUCTS VALUES ('BIC','41003','Handle','652',3);
+INSERT INTO PRODUCTS VALUES ('BIC','41089','Retainer','225',78);
+INSERT INTO PRODUCTS VALUES ('BIC','41675','Plate','180',0);
+INSERT INTO PRODUCTS VALUES ('FEA','112','Housing','148',115);
+INSERT INTO PRODUCTS VALUES ('FEA','114','Motor Mount','243',5);
+INSERT INTO PRODUCTS VALUES ('IMM','773C','300-lb Brace','975',28);
+INSERT INTO PRODUCTS VALUES ('IMM','775C','500 -lb Brace','1425',5);
+INSERT INTO PRODUCTS VALUES ('IMM','779C','900 -lb Brace','1875',9);
+INSERT INTO PRODUCTS VALUES ('IMM','887H','Brace Holder','54',223);
+INSERT INTO PRODUCTS VALUES ('IMM','887P','Brace Pin','250',24);
+INSERT INTO PRODUCTS VALUES ('IMM','887X','Brace Retainer','475',32);
+INSERT INTO PRODUCTS VALUES ('QSA','XK47','Reducer','355',15);
+INSERT INTO PRODUCTS VALUES ('QSA','XK48','Reducer','134',203);
+INSERT INTO PRODUCTS VALUES ('QSA','XK48A','Reducer','177',37);
+INSERT INTO PRODUCTS VALUES ('REI','2A44G','Hinge Pin','350',14);
+INSERT INTO PRODUCTS VALUES ('REI','2A44L','Left Hinge','4500',12);
+INSERT INTO PRODUCTS VALUES ('REI','2A44R','Right Hinge','4500',12);
+INSERT INTO PRODUCTS VALUES ('REI','2A45C','Ratchet Link','79',210);
+
+INSERT INTO SALESREPS VALUES ('101','Dan Roberts',45,'12','Sales Rep', '104', '1996-10-20','300000','305673');
+INSERT INTO SALESREPS VALUES ('102','Sue Smith',48,'21','Sales Rep','108',    '1996-10-12', '350000','474050');
+INSERT INTO SALESREPS VALUES ('103','Paul Cruz',29,'12','Sales Rep','104',    '1997-03-01', '275000','286775');
+INSERT INTO SALESREPS VALUES ('104','Bob Smith',33,'12','Sales Mrg','106',    '1997-05-19', '200000','142594');
+INSERT INTO SALESREPS VALUES ('105','Bill Adams',37,'13','Sales Rep','104',   '1996-02-12', '350000','367911');
+INSERT INTO SALESREPS VALUES ('106','Sam Clark',52,'11','Vp Sales',  null,    '1998-06-14', '275000','299912');
+INSERT INTO SALESREPS VALUES ('107','Nancy Angelli',49,'22','Sales Rep','108','1998-11-14','300000','186042');
+INSERT INTO SALESREPS VALUES ('108','Larry Fitch',62,'21','Sales Mrg',  '106','1999-10-12', '350000','361865');
+INSERT INTO SALESREPS VALUES ('109','Mary Jones',31,'11','Sales Rep',   '106','1999-10-12','300000','392725');
+INSERT INTO SALESREPS VALUES ('110','Tom Snyder',41,NULL,'Sales Rep',   '101','2000-01-13', NULL,'75985');
+
+select * from SALESREPS;
+