Explore BrainMass
Share

Computer: SQL Data Table

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

I am trying to get my SQL data table to pass but it will not, can you me thanks. Here is the code:
SET echo on;
DROP TABLE COURSE CASCADE CONSTRAINTS;
DROP TABLE XCLASS CASCADE CONSTRAINTS;
DROP TABLE STUDENT CASCADE CONSTRAINTS;
DROP TABLE ENROLL CASCADE CONSTRAINTS;

CREATE TABLE COURSE(
CRS_CODE VARCHAR(8) NOT NULL PRIMARY KEY,
CRS_DESCRIPTION VARCHAR(35) NOT NULL,
CRS_CREDIT INT DEFAULT '4' NOT NULL
CHECK(CRS_CREDIT IN (1,2,3,4)));

CREATE TABLE XCLASS(
CLASS_CODE NUMBER(5) PRIMARY KEY,
CRS_CODE VARCHAR(8)NOT NULL,
CLASS_SECTION INT NOT NULL
CHECK(CLASS_SECTION IN (0,1,2,3,4,5,6,7,8)),
VAR_TIME VARCHAR(25) NOT NULL,
CLASS_ROOM CHAR (6),
FOREIGN KEY(CRS_CODE) REFERENCES COURSE(CRS_CODE));

CREATE TABLE STUDENT(
STU_NUM NUMBER(6) NOT NULL PRIMARY KEY,
STU_LNAME VARCHAR(25) NOT NULL,
STU_FNAME VARCHAR(20) NOT NULL,
STU_INIT CHAR (1),
STU_DOB DATE NOT NULL,
STU_HRS INT DEFAULT '0' NOT NULL
CHECK(STU_HRS <=1000),
STU_CLASS CHAR(2) NOT NULL
CHECK(STU_CLASS IN (Fr,So,Jr,Sr,Gr)),
STU_GPA NUMBER(2,2) NOT NULL
CHECK(STU_GPA <=4.00),
STU_PHONE NUMBER(4) NOT NULL );

CREATE TABLE ENROLL(
CLASS_CODE VARCHAR(8) NOT NULL,
STU_NUM NUMBER (6) NOT NULL,
ENROLL_GRADE CHAR(1) DEFAULT 'Z' NOT NULL,
CHECK(ENROLL_GRADE IN ('A,B,C,D,F,I,Z')),
PRIMARY KEY (CLASS_CODE, STU_NUM),
FOREIGN KEY (CLASS_CODE) REFERENCES STUDENT(CLASS_CODE) ON DELETE CASCADE,
FOREIGN KEY (STU_NUM) REFERENCES XCLASS(STU_NUM) ON DELETE CASCADE);
set echo off;

© BrainMass Inc. brainmass.com October 25, 2018, 8:06 am ad1c9bdddf
https://brainmass.com/computer-science/sql/computer-sql-data-table-526537

Solution Preview

Dear student,

Please find attached the solution.

(--Please note that my work ...

Solution Summary

An SQL data table in computers is examined.

$2.19
See Also This Related BrainMass Solution

This question deals with a set of SQL queries based on the Kudler Fine Foods Virtual Case. This includes a set of SQL DDL commands as well as DML commands.

Create the following two tables using the following fields:

Note: Supply the appropriate SQL Server data types when creating the tables. In the Employee table, create an Employee ID field that will generate a unique number for each employee and designate the field as the Primary Key. In the Job Title table, you will need to either utilize one of the listed fields as the table's primary key or you will need to create an additional field to use as the primary key. The primary key from the Job Title table will appear as the foreign key in the Employee table.
Employee

Emp_ID
Last_name
First_name
Address
City

State
Telephone_area_code
Telephone_number
EEO-1 Classification

Hire_date

Salary

Gender

Age

Foreign Key from Job Title table

Job_title

EEO-1 Classification

Job_title

Job description
Exempt / Non-Exempt Status

Question 2:

Using the SQL INSERT statement:

? Go to the Human Resources department in the Kudler Fine Foods intranet. Using information found in the Employee Files for the La Jolla and Encinitas stores, enter records into the employee table for the following employees:

a) Glenn Edelman

b) Eric McMullen

c) Raj Slentz

d) Erin Broun

e) Donald Carpenter

f) David Esquivez

g) Nancy Sharp
? Using the Kudler Fine Foods Job Classifications and Job Descriptions information, enter records into the job_title table for the following job titles:

a) Accounting Clerk
b) Asst. Manager
c) Bagger

d) Cashier
e) Computer Support Specialist
f) Director of Finance & Accounting
g) Retail Asst. Bakery & Pastry

h) Retail Asst. Butchers and Seafood Specialists

i) Stocker

Non-Exempt employees at Kudler Fine Foods are paid an hourly wage and are required to track their working hours.

Question 3:

Check the results by selecting all of the columns from both of your tables.

Question 4:

Using the database and tables from Week Two, write SQL statements and enter the records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location. Check the results by selecting all of the columns from both of your tables

Question 5:

Using the database and tables in Week Two, write SQL queries using Between, Like and Union:

a) Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.)
b) Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)
c) Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)
d) Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use age to restrict data.)
e) Write a SQL query that uses UNION of the two tables to produce a third table.

Question 6:

Using the updated database, write the following queries using the SQL GROUP statement:

a) Group employees by job classification: Select the employees' last names and group them by EEO-1 Classification.
b) Group employees by salary: Select the employees' last names and group them by salary.
c) Group employees by salary within their job classification: Select the employees' last names and group them by salary within their EEO-1 Classification.
d) Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt.

Question 7:

Using the database and tables from Week Three, write queries using the SQL SELECT statement.

Note: Select all of the data from both of your tables before you perform the following.

a) Choose an EEO-1 Classification: Increase all employees' salaries that have: the selected EEO-1 classification by 10%.
b) Increase all employees' salaries by 5%.
c) Choose an employee from the employee table; delete this employee.

Question 8:

Using the database and tables from Week Three, write SQL statements to:

a) Calculate the average salary for all employees.
b) Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees.
c) Calculate the maximum salary for all employees.
d) Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees.
e) Calculate the minimum salary for all employees.

View Full Posting Details