US15-Provide Data Manipulation Language (DML) queries

Description:

As a developer, I want to provide DML quireies for retreiving and manipulating data in database tables to support customer interaction between the customer and the company to result in improved satisfaction of the customer.

Acceptance Criteria:

The client reviews and approves DDL for tables through reveiwng primary DML queries.

Tasks:

Create DML queries for website’s database tables. 3hrs - Tobin

/* Retrieve class information for a customer based on customer's user name */
SELECT CLASSNAME, DESCRIPTION, INSTRUCTOR, DAY, CLASSTIME FROM CLASS
  WHERE CLASSID IN (SELECT CLASSID FROM ENROLLMENT
   WHERE ENROLLMENT.CUSTOMERID = (SELECT USERID FROM USERINFO
    WHERE USERNAME='user_name'));

/* Retrieve all classes from a specific category*/
SELECT CLASSNAME, DESCRIPTION, INSTRUCTOR, DAY, CLASSTIME FROM CLASS
  WHERE CLASS.CATEGORYID=(SELECT CATEGORYID FROM CATEGORY
   WHERE CATEGORYTYPE='class_type');

/* Insert membership category information */
INSERT INTO Membership (Type) VALUES (type);

/* Insert customer information */
INSERT INTO Customer (FirstName,LastName,Address,City,State,Zip,Email,MembershipID)
   VALUES (firstName,lastName,address,city,state,zip,email,membershipID);

/* Insert class category information */
INSERT INTO Category (CategoryType) VALUES(categoryType);

/* Insert class information */
INSERT INTO Class (ClassName,Descrition,Instructor,Day,ClassTime,CategoryID)
   VALUES (className,descrition,instructor,day,classTime,categoryID);

/* Insert enrollment information */
INSERT INTO Enrollment VALUES (classID,customerID);

/* Insert user logon information */
INSERT INTO UserInfo (UserName, Password) VALUE (userName, password);