Week 2 Individual Assignment - Table Queries & Screen Shots

Only available on StudyMode
  • Download(s) : 76
  • Published : May 6, 2013
Open Document
Text Preview
Week 2 Individual Assignment - Table Queries & Screen Shots SQL for Business POS/410
October 8, 2012
Prof. Keith Martin

* Create a working database that will be used during the next 3 * weeks to build and test SQL statements
*
* Author:
* Date: 08 October, 2012
****************************************************************/
/*
* 2.1 Create an SQL Server database for Kudler Fine Foods
* and create the following two tables
*/

-- Create the database using CREATE DATABASE
CREATE DATABASE KulderFineFood;
GO

-- Use KulderFineFood as the current database
USE KulderFineFood;

-- Create the Job_title table.
CREATE TABLE Job_title
(
Job_title varchar(50) NOT NULL PRIMARY KEY, EEO_1_Classification varchar(30) NOT NULL,
Job_description varchar(250) NOT NULL,
Exempt_Non_Exempt_Status bit NOT NULL
);
GO

-- Create the Employee using CREATE TABLE
CREATE TABLE Employee
(
Emp_id int NOT NULL IDENTITY(1,1) PRIMARY KEY, Last_name varchar(25) NOT NULL,
First_name varchar(25) NOT NULL,
Address varchar(40) NOT NULL,
City varchar(15) NOT NULL,
State char(2) NOT NULL,
Telephone_area_code varchar(3) NOT NULL,
Telephone_number varchar(8) NOT NULL,
Job_title varchar(50) NOT NULL
FOREIGN KEY REFERENCES Job_title(Job_title),
Hire_date smalldatetime NOT NULL,
Wage money NOT NULL,
Gender char(1) NOT NULL,
Race varchar(25) NOT NULL,
Age int NOT NULL
);
GO

/*
* 2.2 Using the SQL INSERT statement,
* enter records into the tables
*/

-- Insert Job_title records

INSERT INTO Job_title VALUES
('Accounting Clerk', 'Office/Clerical', 'Computes, classifies, records, and verifies numerical data for use in maintaining accounting records.', 0);

INSERT INTO Job_title VALUES
('Assistant Manager', 'Officials & Managers', 'Supervises and coordinates activities of workers in department of food store. Assists store manager in daily operations of store.', 1);

INSERT INTO Job_title VALUES
('Bagger', 'Sales Workers', 'Places customer orders in bags. Performs carryout duties for customers.', 0);

INSERT INTO Job_title VALUES
('Cashier', 'Sales Workers', 'Operates cash register to itemize and total customer''s purchases in grocery store.', 0);

INSERT INTO Job_title VALUES
('Computer Support Specialist', 'Technician', 'Installs, modifies, and makes minor repairs to personal computer hardware and software systems, and provides technical assistance and training to system users.', 1);

INSERT INTO Job_title VALUES
('Director of Finance & Accounting', 'Officials & Managers', 'Plans and directs the finance and accounting activities for Kudler Fine Foods.', 1);

INSERT INTO Job_title VALUES
('Retail Asst. Bakery & Pastry', 'Craft Workers', 'Obtains or prepares food items requested by customers in retail food store.', 0);

INSERT INTO Job_title VALUES
('Retail Asst. Butchers & Seafood Specialists', 'Operatives', 'Obtains or prepares food items requested by customers in retail food store.', 0);

INSERT INTO Job_title VALUES
('Stocker', 'Office/Clerical', 'Stores, prices and restocks merchandise displays in store.', 0);

GO

-- Insert the Employee records

INSERT INTO Employee VALUES
('Edelman', 'Glenn', '175 Bishops Lane', 'La Jolla', 'CA', '619', '555-0199', 'Cashier', '07-OCT-2003', 21500.00, 'M', 'Caucasian', 64);

INSERT INTO Employee VALUES
('McMullen', 'Eric', '763 Church St', 'Lemon Grove', 'CA', '619', '555-0133', 'Bagger', '1-NOV-2002', 13500.00, 'M', 'Caucasian', 20);

INSERT INTO Employee VALUES
('Slentz', 'Raj', '123 Torrey Dr.', 'North Clairmont', 'CA',...
tracking img