Week 2 Individual Assignment - Table Queries & Screen Shots

Topics: SQL, Relational model, Microsoft SQL Server Pages: 5 (628 words) Published: May 6, 2013
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',...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Week 2 Individual Assignment Essay
  • Essay on BUS475 Week 2 individual assignment
  • Essay about Individual Assignment Week 2
  • Week 2 Assignment 2 Essay
  • Essay on Uop Eco 365 Week 2 Individual Assignment
  • Acc 492 Week 2 Individual Assignment Essay
  • RES 351 Week 2 Individual Assignment Essay
  • Ldr531 Organizational Leadership Week 2 Individual Assignment Essay

Become a StudyMode Member

Sign Up - It's Free