Individual class project Kudler Fine Foods

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Individual class project Kudler Fine Foods

Author

Topic

tlouvierre
Starting Member

8 Posts

Posted - 2010-08-27 : 15:11:44

Hello Everyone:
Create the following two tables using the following fields:
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
Telephone information, Gender, Age, and Job description can allow nulls. All other fields must require data.

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
· 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

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

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

· Save all SQL statements and the results to submit for class.
___________________________________________________________________
USE <database, sysname, AdventureWorks>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO
SQL Server 2008 Method of Row Construction:

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
ADD <new_column_name, sysname, column3> <new_column_datatype,, datetime> <new_column_nullability,, NULL>
GO

I am having trouble with defining the database. I have choosen Kudler Fine Foods. If that is ok. sysname is varchar (30)"NULL".
I am working on defining object id, schema name, dbo, table name, and sample table. Can you help with this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-27 : 15:30:48

Are you asking us to do your homework for you?

You will probably learn more if you do it yourself.

CODO ERGO SUM

tlouvierre
Starting Member

8 Posts

Posted - 2010-08-28 : 12:42:19

No I am not asking you to do my homework for me. I do need help with the coding and I have been working on it since I wrote you. I would like to know an example of sample_name. Would you look at this new application of the program parts. I am not sure how to link them up. P.S. This is due Sunday.
USE <KudlerFineFoods, nvarchar, AdventureWorks>
GO

IF OBJECT_ID('<Information_Services, nvarchar, guest>.<Employee_Table, nvarchar, sample_table>', 'U') IS NOT NULL
DROP TABLE <Information_Services, nvarchar, guest>.<Employee_Table, nvarchar, sample_table>
GO

CREATE TABLE <Information_Services, nvarchar,guest>.<Employee_Table, nvarchar, sample_table>
(

INSERT INTO samples (sample_id, sample_name)
SELECT n + 1,
'sample ' + CAST(n + 1 -
(SELECT MAX(sample_id) FROM samples)
AS VARCHAR(10))
FROM Numbers
WHERE n BETWEEN (SELECT MAX(sample_id) FROM samples)
AND (SELECT MAX(sample_id) FROM samples) + 5;

<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, nvarchar, c2> <column2_datatype, , char(50)> <column2_nullability,, NULL>,
<column3_name, nvarchar, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, nvarchar, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

Tiffany D. Louvierre-Ramirez

tlouvierre
Starting Member

8 Posts

Posted - 2010-08-28 : 13:43:10

Hello Michael or Whoever is helping me to work on this assignment. I have submitted two previous attempts at this assignment until something clicked on the layout of the table and this is what I have come up with. Only thing is there are errors with the > sign and I am not sure why. I have provided the new code with the error messages.

Create table template
-- =========================================
USE <KudlerFineFoods, nvarchar, AdventureWorks>
GO

IF OBJECT_ID('<Information_Services, nvarchar, guest>.<Employee_Table, nvarchar, sample_table>', 'U') IS NOT NULL
DROP TABLE <Information_Services, nvarchar, guest>.<Employee_Table, nvarchar, sample_table>
GO

CREATE TABLE <Information_Services, nvarchar,guest>.<Employee_Table, nvarchar, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol )
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column2_name, nvarchar, c2> <column2_datatype, , char(25)> <column2_nullability,, NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column3_name, nvarchar, c3> <column3_datatype, , char(25)> <column3_nullability,, NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column4_name, nvarchar, c4> <column4_datatype, , char(25)> <column4_nullability,, NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column5_name, nvarchar, c5> <column5_datatype, , char(25)> <column5_nullability,, NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column6_name, nvarchar, c6> <column6_datatype, , char(25)> <column6_nullability,, NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column7_name, nvarchar, c7> <column7_datatype, , int> <column7_nullability,, NOT NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

<column8_name, nvarchar, c8> <column8_datatype, , int> <column8_nullability,, NOT NULL>,
INSERT INTO Employee_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
('Sixth',6)
('Seventh'7)

CONSTRAINT <contraint_name, nvarchar, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
INSERT INTO Employee_Table (SERIALNUMBER, LAST NAME, FIRST NAME, Address, City, State, Telephone Area Code, Telephone Number) VALUES (COALESCE((SELECT MAX(SERIALNUMBER)
FROM Employee_Table), 0)+1, ‘John’, ‘Smith’, 999.99)
)
GO

102, Level 15, State 1, Line 5
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 72
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 82
Incorrect syntax near 'Sixth'.
Msg 102, Level 15, State 1, Line 86
Incorrect syntax near 'NAME'.
Msg 102, Level 15, State 1, Line 87
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '<'.Msg

Tiffany D. Louvierre-Ramirez

tlouvierre
Starting Member

8 Posts

Posted - 2010-08-28 : 15:33:01

This is work for the last part of the assignment. I by accident took it out of context and I would like you to look at it to.
Create table template
-- =========================================
USE <KudlerFineFoods, nvarchar, AdventureWorks>
GO

IF OBJECT_ID('<Human_Resources, nvarchar, guest>.<EmployeeFiles_Table, nvarchar, sample_table>', 'U') IS NOT NULL
DROP TABLE <Human_Resources, nvarchar, guest>.<EmployeeFiles_Table, nvarchar, sample_table>
GO

CREATE TABLE <Human_Resources, nvarchar,guest>.<EmployeeFiles_Table, nvarchar, sample_table>
<columns_in_primary_key, , c1> <column1_datatype, , char(25)> <column1_nullability,, NOT NULL>,
INSERT INTO EmployeeFiles_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol, NinthCol )
VALUES ('First',1),
('Second',2),
('Third',3),
CONSTRAINT <contraint_name, nvarchar, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
INSERT INTO EmployeeFiles_Table (SERIALNUMBER, LAST NAME, FIRST NAME, Dependability/Reliability, Interpersonal Skills, Self Development & Appraisal, Goals, Development Plans, Employee Rating) VALUES (COALESCE((SELECT MAX(SERIALNUMBER)
FROM Employee_Table), 0)+1, ‘John’, ‘Smith’, 999.99)
)
GO

INSERT TABLE <Human_Resources, nvarchar,guest>.<EmployeeFiles_Table, nvarchar, sample_table>
<columns_in_primary_key, , c1> <column1_datatype, , char(25)> <column1_nullability,, NOT NULL>,
INSERT INTO EmployeeFiles_Table (FirstCol, SecondCol, ThirdCol, FourthCol, FifthCol, SixthCol, SeventhCol, EighthCol, NinthCol )
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4)
CONSTRAINT <contraint_name, nvarchar, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
INSERT INTO EmployeeFiles_Table (SERIALNUMBER, LAST NAME, FIRST NAME, Dependability/Reliability, Interpersonal Skills, Self Development & Appraisal, Goals, Development Plans, Employee Rating) VALUES (COALESCE((SELECT MAX(SERIALNUMBER)
FROM Employee_Table), 0)+1, ‘John’, ‘Smith’, 999.99)
)
GO

ERROR MESSAGES

Tiffany D. Louvierre-Ramirez

tlouvierre
Starting Member

8 Posts

Posted - 2010-08-28 : 15:33:37

Error Messages

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'USE'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'CONSTRAINT'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'NAME'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'TABLE'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CONSTRAINT'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'NAME'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ','.

Tiffany D. Louvierre-Ramirez

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources