GO
/****** Object:  Database [KudlerFineFoodsDB]    Script Date: 05/09/2009 16:49:13 ******/
CREATE DATABASE [KudlerFineFoodsDB] ON  PRIMARY 
( NAME = N'Kudler Fine Foods', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Kudler Fine Foods.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Kudler Fine Foods_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Kudler Fine Foods_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Kudler Fine Foods', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [KudlerFineFoodsDB].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [KudlerFineFoodsDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Kudler Fine Foods] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [Kudler Fine Foods] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Kudler Fine Foods] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Kudler Fine Foods] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Kudler Fine Foods] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Kudler Fine Foods] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET  ENABLE_BROKER 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET  READ_WRITE 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET RECOVERY FULL 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET  MULTI_USER 
GO
ALTER DATABASE [KudlerFineFoodsDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [KudlerFineFoodsDB] SET DB_CHAINING OFF 

GO
/*
Using Microsoft SQL Server CREATE DATABASE, CREATE TABLE, and SQL INSERT statements. 
Use the CREATE DATABASE statement to create a SQL Server database for Kudler Fine Foods. 
In the Kudler Fine Foods Database, use the CREATE TABLE statement to create the following 
two tables with the fields identified below:
Note: Specify appropriate SQL Server data types for the fields in these tables.*/


/*Job_title
Job_ID (Primary key)
EEO-1 Classification                             
Job_title                           
Job-description
Exempt_status (Yes or No)*/

GO
/****** Object:  Table [dbo].[JOB_TITLE]    Script Date: 05/09/2009 15:02:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Job_Title](
	[Job_id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,	
	[EEO-1 Classification] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,	
	[Job_title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Job_description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Exempt_Status] bit  NOT NULL , --Yes is 0,No is 1 
PRIMARY KEY CLUSTERED 
(
	[Job_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
/* The primary key from the Job_title table will appear as a foreign key in the Employee table.
Emp_ID (Primary Key, Identity)
Last_name                                  
First_name                                  
Emp_address                             
Emp_city                                      
State_code
Telephone_area_code                         
Telephone_number                              
EEO_1_classification               
Hire_date             
Emp_salary
Emp_gender
Emp_age
Job_ID (Foreign key to Job_title table)*/          

/****** Object:  Table [dbo].[employee]    Script Date: 05/09/2009 14:04:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
	[Emp_id] [int] IDENTITY(1,1) NOT NULL,-- (Primary Key, Identity)
	[Last_name] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[First_name] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Emp_address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Emp_city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[State_Code] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Telephone_area_code] [int] NOT NULL,
	[Telephone_number] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EEO_1_classification] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Hire_date] [datetime] NOT NULL,
	[Emp_salary] [decimal](10, 2) NULL,
	[Emp_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Emp_age] [int] NOT NULL,
	[Job_id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
	[Emp_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Job_title] FOREIGN KEY([Job_id])
REFERENCES [dbo].[Job_Title] ([Job_id])--Creating foregign key for the table Job_Title with key "Job_Id"
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Job_title]-- add this foreign key constrain to Employee table

GO
/*Check your results by examining the tables and columns in the SQL. 
You also should run the SP_HELP stored procedure to list the tables and their columns.
The commands are SP_HELP Employee and SP_HELP Job_title. */

SP_HELP  [Employee]  --this will display all information about [Employee] table
GO
SP_HELP [Job_Title]  --this will display all information about [Job_Title] table
 

/*Using the Kudler Fine Foods Job Classifications and Job Descriptions information, 
enter records into the job_title table for the following job titles:
Accounting Clerk
Asst. Manager
Bagger
Cashier
Computer Support Specialist
Director of Finance & Accounting
Retail Asst. Bakery & Pastry
Retail Asst. Butchers and Seafood Specialists
Stocker*/


INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'ACCLK', 'Sales Worker','Accounting Clerk','Accounting Clerk',0)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'ASTMG', 'Officials & Managers','Asst. Manager','Asst. Manager',1)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'BGGER', 'Sales Workers','Bagger','Bagger',0)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'CASHR', 'Sales Workers','Cashier','Cashier',0)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'CMPSS', 'Operatives (Semi Skilled)','Computer Support Specialist','Computer Support Specialist',0)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'DROFA', 'Officials & Managers','Director of Finance & Accounting','Director of Finance & Accounting',1)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'RTABP', 'Operatives (Semi Skilled)','Retail Asst. Bakery & Pastry','Retail Asst. Bakery & Pastry',0)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'RBSFP', 'Operatives (Semi Skilled)','Retail Asst. Butchers and Seafood Specialists','Retail Asst. Butchers and Seafood Specialists',0)

INSERT INTO [dbo].[Job_Title]
([Job_id],[EEO-1 Classification],[Job_title],[Job_description],[Exempt_Status])
VALUES( 'OFCLR', 'Office/Clerical','Stocker','Stocker',0)

/*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:
Glenn Edelman
Eric McMullen
Raj Slentz
Erin Broun
Donald Carpenter
David Esquivez
Nancy Sharp*/
TRUNCATE TABLE [Employee]
--Cashier	Sales Worker	Glenn Edelman	10/7/2003	$10.75	$21,500.00	M	Caucasian	64
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('Glenn','Edelman','','','','','','Sales Workers','10/7/2003',21500.00,'M',64,'CASHR')
--Bagger	Sales Worker	Eric McMullen	11/1/2002	$6.75	$13,500.00	M	Caucasian	20
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('Eric','McMullen','','','','','','Sales Workers','11/1/2002',13500,'M',20,'CASHR')

--Assistant Manager	Officials & Managers	Raj Slentz	6/1/2000	N/A	$48,000.00	M	Asian	34
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('Raj','Slentz','','','','','','Assistant Manager	Officials & Managers','6/1/2000',48000,'M',34,'ASTMG')
    
--Bagger – 30 hours/wk	Sales Worker	Erin Broun	3/12/2003	$6.75	$10,530.00	F	Caucasian	24
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('Erin','Broun','','','','','','Sales Worker','3/12/2003',10530,'F',24,'BGGER')

--Stocker	Office/Clerical	Donald Carpenter	11/1/2003	$7.50	$15,000.00	M	African American	18
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('Donald','Carpenter','','','','','','Office/Clerical','11/1/2003',15000,'M',18,'OFCLR')

--Asst. -Butchers & Seafood Specialists	Operatives (Semi Skilled)	David Esquivez	7/25/2003	$9.25	$18,500.00	M	Hispanic	25
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('David','Esquivez','','','','','','Asst. -Butchers & Seafood Specialists','7/25/2003',18500,'M',25,'RBSFP')

--Cashier	Sales Workers	Nancy Sharp	7/12/2003	$10.50	$21,000.00	F	Caucasian	24
INSERT INTO [dbo].[Employee]([First_name],[Last_name],[Emp_address],[Emp_city],[State_Code],[Telephone_area_code],[Telephone_number],[EEO_1_classification]
,[Hire_date],[Emp_salary],[Emp_gender],[Emp_age],[Job_id])
values('Nancy','Sharp','','','','','','Asst. -Butchers & Seafood Specialists','7/12/2003',21000,'F',24,'RBSFP')

 

/*Non-Exempt employees at Kudler Fine Foods are paid an hourly wage and are required to track their working hours.
 You need to enter an estimated yearly salary for these employees.
 
Check your results by using the SQL SELECT statement to list all of the columns and rows from both of your tables. 

In Microsoft SQL Server Management Studio, save your CREATE DATABASE,
 CREATE TABLE, and SELECT SQL statements as .SQL files.  Save your SQL output as .RPT files. 
 You will need to turn in both types of files (.SQL and .RPT) */

--Get all rows from Employee tables
SELECT * FROM dbo.Employee
--Get all rows form Job_Title
SELECT * FROM dbo.Job_Title

/*Write SQL Queries (SELECT statements) using BETWEEN, LIKE, UNION, and GROUP.
Using the database and tables from Week Two, write SQL INSERT statements to insert records into the
 employee table for the workers identified in the Employee Files for the administrative offices and the 
Del Mar location. Check your results by selecting all of the columns from both of your tables*/
 
--Using the database and tables in Week Two, write SQL queries using BETWEEN, LIKE, and UNION:
/*1.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.)*/
SELECT Employee.*, Job_title.*
	FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
WHERE Employee.Emp_salary between 15000 and 40000

/*2.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.)*/
--this will get all employees have been hired between 2003-07-12 and 2003-11-01
SELECT Employee.*, Job_title.*
	FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
WHERE Employee.Hire_date between '2003-07-12'and '2003-11-01'

/*3.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.)*/
--this will get all employees having telephone area code starting with 6
SELECT Employee.*, Job_title.*
	FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
WHERE Employee.Telephone_area_code like '0%'

/*4.Write a SQL query that joins two tables in the example database and uses LIKE to
 restrict record selection. (Use age to find all people in their 20’s.)*/

SELECT Employee.*, Job_title.*
	FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
WHERE Employee.Emp_age like '2%'

/*5.Write a SQL query that uses UNION of the two tables.  Be creative.*/

select Employee.Last_name ,Employee.First_name
FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
where  Job_title.Job_title = 'Stocker'
union
select Employee.Last_name ,Employee.First_name
FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
where  Job_title.Job_title = 'Operatives'


/*6.Using the updated database, write the following queries using the SQL GROUP BY statement 
and appropriate aggregate statements (when needed):

Write a SQL query that displays the average salary / hourly wage grouped by job_title.*/
select AVG(Employee.Emp_salary) AS AverageSalaray ,Employee.EEO_1_classification
FROM Employee
group by EEO_1_classification

/*7.Write a SQL query that displays the number of people in each EE0-1 Classification 
and the average salary / hourly wage for each classification.*/

select Job_title.[EEO-1 Classification] as JobClassification,AVG(Employee.Emp_salary) AS AverageSalaray, Count(*) NumberOfPeople
FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
group by Job_title.[EEO-1 Classification]


/* 8.Group employees by EEO classification: Select the employees’ last names
 and group them by EEO-1 Classification*/

select Job_title.[EEO-1 Classification] as JobClassification,Employee.Last_name
FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
group by Job_title.[EEO-1 Classification],Employee.Last_name


/*9.Group employees by salary within their EEO classification: Select the employees’
 last names and group them by salary within their EEO-1 Classification.  */

select Employee.Emp_salary ,Employee.Last_name,Job_title.[EEO-1 Classification] as JobClassification
FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
group by Employee.Emp_salary,Employee.last_name,Job_title.[EEO-1 Classification]

/*Select the employees’ last names and group them by salary within job titles that are grouped 
into exempt and non-exempt.*/


select Employee.emp_salary ,Employee.Last_name,Job_title.Exempt_Status
FROM Employee JOIN Job_title
	ON (Employee.EEO_1_classification = Job_title.[EEO-1 Classification])
group by Employee.emp_salary,Employee.last_name,Job_title.Job_title,Job_title.Exempt_Status