Create a SQL Database

Anyone able to help?

Hmm, I cant even get the first table created. It wants to do everything like setting up the keys and all that. If I could just create the table Id add the keys in using the SSE EXpress

2 things...
(1) you probably wanted MS SQL -- not MySQL
(2) I believe HW help is against the TOS for EE.

Well what I am using is MySQL even if it is Microsoft. Sorry if I put this in the wrong area.

I believe virmaior hit on both of the relevant answers.  

Your first task is to figure out which database server you are using.  If you're not sure of that, then it is very likely we cannot provide the kind of help you need.  You may have also selected the wrong classes.

We cannot do your homework/tests for you:

https://www.experts-exchange.com/help.jsp#hs=23&hi=21

But we can point you to resources useful in finding the answer.  Assuming you are using MSSQL (not MySQL, which is not a Microsoft product), your first answer can be found here:

http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

You should be able to explore the links provided on that page to discover the rest of the knowledge you will need.

Eagle, we will be happy to help with your assignment, but you have to ask a _specific_ question. We can't do the project for you. Specific question can be something like "how to do this better, using way A or way B?", or "I wrote this query but it gives this error", or similar.

Right,

So, either way MySQL and MS SQL are not so hugely different, but there can be subtleties in their syntax. So anything the experts post, you need to just double and tripple check.

First up, seeing there are a lot of different parts to your work, where are the stumbling blocks ?

Is it just the create table ? Can you show us what you have attempted so far ?

Just a word of warning on those tables. There seems to be a co-dependancy of departments and employees (manager-id) with those foreign keys. Meaning that a department cannot exist with an manager-id until such time that the row in the employee table exists. Similarly the Employee cannot exist with a department-ID until such time the Department exists (which of cours cannot happen until employees exist - ouch - huge catch-22).

To me that might imply that either department-id on Employee table or Manager-id on departments can be NULL.

Because of that rather confusing relationship, we must first create the tables (at least their structure - including primary keys) and then we can create the foreign keys.

It also means when adding rows, we need to first add employees, then their managers and their departments. One could question the legitimacy of department-id on the employee table. There does seem to ba a "rule" missing that further defines the dependancies or relationships between employee and department tables (ie manager-id)...

How does that sound so far ?

As far as CREATE TABLE syntax, you can google either MS SQL CREATE TABLE or MySQL CREATE TABLE and find examples. And similarly for FOREIGN KEYS (which are constraints).

Now if you have specific questions, show us what you are trying, show us where you need specific help (and "create table" is too general, and too close to providing an answer to one of the tasks). The more your are trying and doing, the more we can help your understanding and confusion.

I have tried creating the table with the information specified like it is laid out, but I cannot seem to grasp the syntax of MSSQL 2008 Express. You are right about the tables being co-dependent on each other. I was trying to follow my book but I am not exactly sure I understand the table name itself. For example.

I did it like this.

USE FINAL
GO

CREATE TABLE Departments
(
  Department_ID vchar (20) null,
  Department_Name char (14) Not Null,
  Manager_ID bigint (10) FK refers to PK Employees,
  Location_ID bigint (8) FK refers to PK Locations,
 )

I haven't even attempted the other two tables as I am very tired and coming down with something that I picked up on our visit to family up north. If I Can get the three tables going everything else will be kosher. Im not a totall noob when it comes to this but the basic syntax they suggest kind of throws me off. Thanks.

Eagle

BTW thanks to all of you who have helped and suggested some sites to look at, I greatly appreciate it.

1. In the sql above, take a very close look at what's just before the closing parenthesis.

2. usually, every table has to have a primary key, which is a column used to uniquely identity each row, like sequential number. This column can't be empty.

3. there's no "vchar" data type. see http://msdn.microsoft.com/en-us/library/ms187752.aspx

4. there's no "refers to". See http://msdn.microsoft.com/en-us/library/ms174979.aspx , specifically the definition of  column_constraint

Your right its varchar, lol. Blegh I even tend to forget the simple things. As for the primary key, do I just call it a primary key? I never did get those when we were going over them and my professor isnt the one to timely answer questions in Virtual Office, AIM, or even email

OK, lets use an "easy one" first... Locations...

Create Table LOCATIONS
(
LOCATION_ID int identity Primary Key,      
POSTAL_CODE varchar(30),
CITY varchar(30)
STATE_PROVINCE varchar(30)
COUNTRY varchar(30)
)

The location_id being an "identity" is a special column. It will auto increment. The equivalent in MySQL is AUTO_INCREMENT, but in MySQL you can also do:

LOCATION_ID serial,

which is the same as:

LOCATION_ID BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

Think you need to first create the tables and then seperately worry about the DDL to add the constraints for FOREIGN KEYS (ie as separate commands).

The above is covered off in those links previously supplied...

Have a look at the MYSQL documentation online : http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html it will give you good examples and answer pretty much any question about syntax.

now we are your professors, lol. OK, it's like

create table table1 (id int not null primary key)
create table table2 (id int not null primary key, col2 int references table1(id))

insert into  table1 select 1 -- creates parent record in table1
insert into table2 select 1,1 -- creates child record in table2

I was wanting to do that. Try to get the tables setup basically and then worry about the other things but it seems, as in this statement here, I cannot do that. While I see how you are going about doing locations, when I went to try to put it in SQL 2008 Express it gave me this error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'STATE_PROVINCE'.

which doesn't make sense to me because everything is worded correctly like it wants in the instructions

ahh I got it. forgot to put commas in and I went ahead and enclosed each column name in [] brackets.

also, does it make sense to create the locations and employees table first then do the first table last?

teh definitions of the columns have to be separated by commas:

create table t (

col1  int ,
col2 varchar(50) ,
col3 int
)

Ok this is how I got locations now, see attached. This is cool, I think I may attempt to try employees and see if I can get it going.
Create-Table-Locations.jpg

primary key must be not null, while the default for a column is null. Look at my example in http:#34042471

ah, sorry, I overlooked that it's also identity - then NOT NULL is not necessary; but for completeness, better to specify anyways.

ok so I need to delete the table and then redo it and make sure I put (not Null).

Gotcha

well actually you don't have to put that in there. if you put null it makes it a null entry, nothing being added is ignored and taken as not null if I am not mistaken.

ok gotcha vad. Im gonna try to do the employees table and post it here for yall to look at.

re. sequence of creating related tables, the best is going from parents to children, like in my example. Then you create the child table in one operation, and it's already related to the parent at once.

when setting an email address is varchar the best identifier to use? reason I ask is some may have numbers in their address or would it be best to use char and keep it just all letters?

ahh ok

varchar is much better because char is fixed length, it will be adding spaces in the end of everything, and these spaces sooner or later will cause a trouble somewhere.

Ok I did the employees table after I partly did the departments table. On the departments table I create the table using the first two parts, but I didn't reference the employees table because I hadn't created it yet. So I went ahead and did the employees table and I am thinking I will be doing an alter table to add the last two parts of the departments to the table itself. Think that was a good way to go about it?

See attached.
Create-Table-Employees.jpg

if you look at these 3 tables, locations, departments, and employees - what is the order of their relations? the locations are indeed on the top, but what directly refers the locations - employees or departments? that's what you create next.

Departments well actually I Think they both refer back to it.

It seems I am having an issue altering the table and adding the LOCATION_ID in my departments table.

I think the reason is because I kept what Will said and used the int identifier instead of say char (5).

See image please.
Alter-Table-ADD-Location-ID-ERRO.jpg

Yes, you can not create the employee AND the department with the embedded "REFERENCES" there will be at least one of those that has to have the referential constraints add in via "ALTER TABLE"

> I Think they both refer back to it

Look carefully at the table "employees" in the pdf file - does it refer anything from Locations?

e.g. that DDL comment I made before...

alter table departments ADD CONSTRAINT FK_department_manager_id FOREIGN KEY (manager_id)  REFERENCES employees (employee_id)

Mark yeah I was doing the alter table. I was trying to add in the Locations part of departments via ALTER Table and it was giving me the error.

VAD you are correct, Departments is the only thing referencing back to LOCATIONS.

Mark so how do I correct the error I have?

@Mark ahh ok doing what you just put in the e.g. DDL worked and altered the table for me. Thanks.

ok, so locations are at the top, departments refer to locations, and employees refer to departments. This makes possible to have all 3 tables created from top to bottom without going back, i.e. without "alter table" - 3 create table's should be enough. Can you do it?

Oh, and if at this stage you make a mistake, then no problems simply drop the table - but not after you start using it - you will have other tables referring to it, and will lose data as well. If you have established to referential integrity contraints (foreign keys) then you need to deal with them first before you can drop a table.

vadimrapp1 - departments also refer to employees.... it is an "interesting" design.

ah, right, sorry, I did not notice.

well ok that was already in there for my first alter table add. Still need help adding in the :

LOCATION_ID FK refers to PK of Locations table.

Oh, and cannot use NOT NULL on department_id in employees, nor NOT NULL on manager-id in departments. Means you have to be able to add a row with a legitimate value first - and cannot be done. How can you add a department that must have a manager-id that comes from employees that must have a department-id before the manager can exist - doesnt make sense (that is why the design is flawed and department_id on employee is highly questionable)

no I cant do 3 create tables. The last thing in departments is really ticking me off because I cant get it to add the location_id at all. I tried your version mark and of course the normal way I did it with the first ADD I did earlier in the post and I keep getting errors.

I think since this is an assignment, the final goal is to have one sql script that creates all the tables and populates them with the data etc. So the best is to just add step by step to the script, including INSERT's,  dropping the tables on each step; then add new line(s) to the script, and run it all, so it recreates the tables from scratch. Once that works, add next statement, and again from scratch, and so on. In the end, you'll have complete script for the whole assignment.

yeah I don't get that for a final question. Its like a basic introduction to SQL 2008 Express but for some reason if the experts here are saying it doesn't make sense then it really is confusing to me.

Vad that is too darn complicated man, lol. I don't know if it is supposed to work that way. If you read it, we can create the three tables first. Then we do use INSERT to add 3 departments, 7 employees, and 2 locations (Which I assume the locations may include the extra data from the locations table meaning city, state, etc).  The Tables are to be created first though from my understanding of the reading.

R U KIDDING ME!!!???? LOL! Because I was using int in the location_id, I had to use the same thing for the altering the table.

Ok quick question...how do I go back to the Location_ID and change the int integer to char(5). I think that is the best way to go about doing it/ Also if I do that, would I also have to go and change the departments reference?

It sure does make sense - think about the real life. In real life, the company would first hire new employee, and then announced that this employee will be the manager of department X.

This means that at some moments, the department can be without any manager at all - which in turn means that manager_id can be null. Foreign key can be null, it's allowed.

Check it out.
AHA-Alter-Table-Location-ID.jpg

BTW Thank you both so much for helping me with this. I just want to make sure I have these setup correctly so when I go to use them with my 2 select statements I dont mess anything up. You guys really are rock solid!

Vad btw you do make sense, it just doesn't make much sense in the scheme of how she wants it done, lol. Since we know I will be adding 7 employees one of which will be a manager.

When you add departments, assign the locations at once as you add them. When you add employees, also assign them to the departments at once as you add them. Then go to departments and assign the manager.

OK, a foreign key is saying that the values of "this column" must already exist on "that_table" as "PK_columns"

The relationship of manager to department is interesting.

I think what the design is saying (and this is the bit where you need to really work out what the question is saying) :

1) all employees exist in employee table
2) managers are one of those employees
3) employees belong to managers
4) there are departments
5) there is a manager in charge of that department

then there is the question mark.... do employees belong to the manager or to a department. Right now the FK contraints say they cannot belong to a department (because of that circular argument before) so they have to have department as NULL.  OR, they belong to a department and the department belongs to a manager. But right now we have two columns for each employee, department and manager and that is the confusing part.

In a relationship such as this, one might reasonably expect a manager / employee relationship, or a department / manager relationship - either way a chain of ownership can be established. But having both just adds one level of consusion because the (business) object "manager" is not quite the same as "employee" because they "own" different parts of the puzzle. They "own" departments and employees, and that is what the design is *trying* to show.

Yeah I Think I got that. I believe I have all three tables good to go. I was just worried about using INT as the Location ID in both departments and locations instead of char or varchar(5). That is usually what we had been using was char(5) for the id string.

so my question is should I start over with the locations and departments (since I havent done any using of those in any select statement so far)? Once I rebuild them back in, the reference for Employees will be fine I am thinking. Let me know.

Yes, vadimrapp is correct...

1) add locations
2) add departments (with no manager - must be permitted to be NULL)
3) add management employees
4) update departments with management
5) add employees with managers (and department ID I guess)

I would be inclined to have all ????_ID columns (the primary keys) as ????_ID INT INDENTITY PRIMARY KEY

and then in the columns on the other tables that refer to those primary keys, they are simply INT columns

ok then I  may need to change the one on employees because I have that as CHAR(5). now I have to look at my departments and see if I indeed have manager set to null. I dont believe I put anything there so lemme take a quick look.

@Mark if I was to go and change Employee_ID from char(5) to int, how would I exactly do that? Would it be

ALTER TABLE Employees
Change Employee_id char(5) to INT
GO

??

OK I see under departments that both Manager_ID and Location_ID are allowed to be null.

bathroom break, brb, thanks again for the help guys

You can use SSMSE to change that interactively would be the easiest.

But at this point, before you add your FK's, can just as easily DROP TABLE <tablename> if it is scripted, then fix the sql, and do the create again.

for example look at this little test :


if object_id('tbl_ee_employee') is not null DROP TABLE tbl_ee_employee

create table tbl_ee_employee (emp_id char(5))

alter table tbl_ee_employee alter column emp_id int


-- but now lets look at a column with a constraint


if object_id('tbl_ee_employee') is not null DROP TABLE tbl_ee_employee

create table tbl_ee_employee (emp_id char(5) primary key)

alter table tbl_ee_employee alter column emp_id int      -- this fails because it is a PK and "other objects" now depend on it.

To make it simmetrical, let's allow both departments.manager_id and employees.department_id to be null. Reflecting real life situations when department at the moment has no assigned manager (which is possible in real life), and employee was hired but not yet assigned to any department (also possible). This still allows them to be foreign keys and gives us the flexibility to decide to add first depaments or employees.

Mark I don't think dropping the table will be the easiest. I would just want to change the Employee_ID on the Employees table from char(5) to int INTEGER Primary Key. That drop table is kind of complicated sounding to me. Its almost seems easier if I were to delete the table and remove it that way and redo it than to do if than clauses.

don't miss my comment http:#4043210 - I think your assignment will look best if you present it as one script doing everything from beginning to the end; which means that while developing it, on each run, you drop the tables every time and then have the script creating and populating them from scratch. And in the end the views that we will have to create as well.

correction: http:#34043210

delete the table is the same as drop - you can right click on the table and delete, or you can use T-SQL in a query window and drop. Either way....

You will need to script your work anyway so it can be submitted for marking.

Just one more comment on the design with department-id and manger-id relationships....

say we have 2 departments (dep1 and dep2) and two managers (man1 and man2) and three employess emp1,emp2,emp3 belonging to dep1 and man1. Lets then change dep1 to have man2, and dep2 to be man1. We now have emp1,emp2,emp3 belonging to a department that is managed by someone who is not their manager.

And on that note I think I will leave you in the very capable hands of vadimrapp1 - it is nearly 3:45am down under and I really do need my beauty sleep (shame really, but necessary *laughing*)

Eagle, please post the complete script that you have so far - let's see where we are at, and verify it before we go further.

yeah but I don't really get how to do that Vad. I am a beginner with this stuff to be honest. I mean I know some if not most of the basic, but as far as running one script and dropping tables and adding them back seems extremely complicated to me and a bit more than what my assignment calls for. I am actually starting to insert the data into the tables and Im double checking the book to make sure I got it right.

It shows me to Insert INTO (though into is not needed) table
 then of course the column names
then Valus
which makes sense to me. Anyways sorry If I am rambling.

Well I can give you the three tables I created with the print screen I took. That is how we submit our codes and queries to the professor. If that will work Ill add them to this right here. I havent done the inserts yet.

@Mark thanks bud I do appreciate your help.

I don't know of a way to show the whole script being kept once I take it out and start a new line. Basically I do the script execute it, show that it completed successfully, then print screen it, save it, delete what I wrote and go again for the next table. I am also going to add the Alter table I did to add to the Departments to make sure I had the Department_ID and Location_ID.
Create-Table-Employees.jpg
Create-Table-Locations.jpg
Create-Table-Departments---Objec.jpg
Alter-Table-Departments-Add-Mana.jpg
AHA-Alter-Table-Location-ID.jpg

drop table employees
drop table departments
drop table locations

create table locations        (col1 <datatype> not null primary key, col 2 datatype, .... )
create table departments  (col1 <datatype> not null primary key, col 2 datatype references locations(col1), col 3 datatype, .... )
create table employees(col1 <datatype> not null primary key, col 2 datatype references departments(col1), col 3 datatype, .... )

insert into locations(col1,col2,...) values 'value1','value2',... <- create 1nd location
insert into locations(col1,col2,...) values 'value3','value4',... <- create 2nd location

insert into departments(col1,col2,...) values 'value1','value2',... <- 1st department
insert into departments(col1,col2,...) values 'value3','value4',... <- 2nd department
insert into departments(col1,col2,...) values 'value5','value6',... <- 3rd department
...

insert into employees(col1,col2,...) values 'value1','value2',...  <- 1st employee
insert into employees(col1,col2,...) values 'value3','value4',...  <- 2nd employee
insert into employees(col1,col2,...) values 'value5','value6',...  <- 3rd employee
...

update table departments set manager_id = <value> where id=<value> <-- that's where we assign
the manager to the department
...


For now, let's forget about the foreign key representing the relationship of departments to employees. If we have time, we can add it later, when we have finished with the main things. This will not prevent us from entering correct data.

If you submit the results to the professor as screenshots rather than sql script, then simply draw them in the designer, draw the relationships from one table to another by dragging the line from column in child table to column in parent table, then you will have all 3 tables with relationships.

yeah I dont get that whole thing you posted above the one you just posted, lol. I am sorry Vad, maybe its a losing battle with me buddy. Basically what I get from the post above is we will do this all at once with the dropping the tables then we go create the tables, right?

by the way, the easiest way to do this visual design is in diagram. Create diagram in management studio, and there you will be able to work with all three tables in one place, it will be very clear.

first let's decide what we are going to create - sql script, or screenshots. Looking into the assignment, sql script is not required. So create diagram, add all 3 tables, draw the relationships.

We will be doing screen shots. So do I need to start all over from scratch with this?

no, you already have the tables, just create the diagram and add to it the existing tables.

What diagram? Lemme explain how we do it. We write down the question number, post a screen shot after copying the question. Then move on. We don't have to do a diagram drawing linking anything because we show the screen shots and she gets the information from there. It is basically do the query, execute it, show it suceesfully executed and then go. Then we do the queries for select stateements and show the results, print screen and go.

So what do I need to do next? Insert data right?

I mean, you create the diagram for yourself, in order to easily design all the tables and their relations. Here's the sceleton of what you need.
Capture-11-02-00001.png

ya know I am not exactly sure what she means with inserting three departments. I really don't. Perhaps its like Shipping, Production, and Management? Not really sure. Any clues Vad?

better version
Capture-11-02-00002.png

ok I see what you mean with the diagram and thanks for that.

sure, make up 3 departments and add the data. Right click the table in management studio, "edit rows" or whatever it's called, it will open up the table, there just type the names you want.

OK doing that this is what it shows, I was hoping to just use SQL statements to add the data, eg
INSERT DATA Departments
 (ProductionID, ShippingID, ManagementID)
VALUES
('ACME Novelties' , 'Freedom Freight' , 'Sales Manager')
GO
Edit-top-200-rows.jpg

right, so just type there.

But, again, looking at the assignment, it says:

Be sure to paste in screenshots of the query and results for each question.

and then if the question 1 is "create the following 3 tables", then I'd think that the query to do that is exactly the SQL script that I mentioned - CREATE TABLE and so on. It's odd that she wants not the text of the query itself but screenshot of the text, but that's what it says.

if you already have the tables created, right-click them and "script to new window". This will give you the text of the script to create. Then you delete the table, then run that script, which recreates the table, you make the screenshot. Then go to the next table. I think this will satisfy the assignment.

I dont have a script to new window. I have to go to CREATE TO new window or something and that doesn't make sense to me.

Actually I cannot do that. It is an invalid column name if I do it how I have it setup. This is actually starting to frustrate me now, lol. How can I create new departments when I get an error saying this:


Msg 207, Level 16, State 1, Line 2
Invalid column name 'ProductionID'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ShippingID'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ManagementID'.

doing the new script for INSERT INTO in a new window gives me what is there in the script like this, but Im not sure what I am supposed to do. I am thinking about just saying screw it and taking a 0 on this.
New-Script-INSERT-INTO-New-Query.jpg

I am thinking I should just add 3 departments but I am not exactly sure how I will do that.

Ok if I am looking at this right in the new script window (I took a break for a min to collect thoughts) where it says Department I should be able to use that column and create 3 different departments there. However I am not exactly sure how I go about that using the INSERT command. Maybe update would work but in the instructions she says to use INSERT as the command. Would it look like this:

INSERT Departments
 [Departments]
Values
 ("Management' , 'Production' , 'Shipping')
GO

??

Let's first concentrate on CREATE TABLE. For each table, generate the script that creates that table. Post the scripts here but don't run them.

Why would I do that if I already created the tables? It makes no sense to me, unless ooooooh hold on hold on I gotcha one min.

USE [FINAL]
GO

/****** Object:  Table [dbo].[Departments]    Script Date: 11/02/2010 13:15:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Departments](
      [DEPARTMENT_ID] [char](5) NOT NULL,
      [DEPARTMENT_NAME] [varchar](20) NOT NULL,
      [MANAGER_ID] [char](5) NULL,
      [LOCATION_ID] [int] NULL,
PRIMARY KEY CLUSTERED
(
      [DEPARTMENT_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].[Departments]  WITH CHECK ADD FOREIGN KEY([LOCATION_ID])
REFERENCES [dbo].[LOCATIONS] ([LOCATION_ID])
GO

ALTER TABLE [dbo].[Departments]  WITH CHECK ADD FOREIGN KEY([MANAGER_ID])
REFERENCES [dbo].[Employees] ([EMPLOYEE_ID])
GO

ALTER TABLE [dbo].[Departments]  WITH CHECK ADD  CONSTRAINT [FK_department_manager_id] FOREIGN KEY([MANAGER_ID])
REFERENCES [dbo].[Employees] ([EMPLOYEE_ID])
GO

ALTER TABLE [dbo].[Departments] CHECK CONSTRAINT [FK_department_manager_id]
GO

CREATE TABLE [dbo].[Employees](
      [EMPLOYEE_ID] [char](5) NOT NULL,
      [LAST_NAME] [varchar](20) NULL,
      [FIRST_NAME] [varchar](20) NULL,
      [EMAIL] [char](20) NULL,
      [SALARY] [int] NULL,
      [PHONE_NUMBER] [int] NULL,
      [HIRE_DATE] [date] NULL,
      [MANAGER_ID] [char](5) NULL,
      [DEPARTMENT_ID] [char](5) NULL,
PRIMARY KEY CLUSTERED
(
      [EMPLOYEE_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].[Employees]  WITH CHECK ADD FOREIGN KEY([DEPARTMENT_ID])
REFERENCES [dbo].[Departments] ([DEPARTMENT_ID])
GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD FOREIGN KEY([MANAGER_ID])
REFERENCES [dbo].[Employees] ([EMPLOYEE_ID])
GO

CREATE TABLE [dbo].[LOCATIONS](
      [LOCATION_ID] [int] IDENTITY(1,1) NOT NULL,
      [POSTAL_CODE] [varchar](30) NULL,
      [CITY] [varchar](30) NULL,
      [STATE_PROVINCE] [varchar](30) NULL,
      [COUNTRY] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(

Good. Now the same for two other tables.

excellent. Now do this:

Open another instance of management studio
create new database
copy the script to create Locations from the first m.s. to the 2nd m.s.
run the script, so the 2nd m.s. creates table Locations in the 2nd database. Make the screenshot of that, with the result saying that it was successful.

Post the screenshot here.

if somebody comes here, please post, and I will post the update. The reason for swtiching to email is that it already takes too long for the page to render, there's still much to do, and there's nobody else. I will post the final result anyways.

NOTE : email details removed - mark wills zone advisor

Select allOpen in new window

OK gotcha, here it is. I took that whole code and put it in there to make it simple
VAD-Final-2-SS-of-Locations-Crea.jpg

if you did not receive the reply I sent by email, beginning with "ok, the 1st screenshot is ok", please post here. I received an email, replied, but then you resent the same 1st email.

Hmm you around Vad?

sure

didya get my email about those errors? I only got two rows to populate.

It still shows up. I finally did get your emails to come through. The errors I get are pretty much the same. I haven't tried changning anything because I am just not getting why the heck it won't work. I haven't done anything different to be honest.

I am thinking that maybe, just maybe, I am supposed to take managerID as if they are the manager, if not, then I don't know what the heck I am actually doing anymore with that table.

OK, stop coding for a second... sit back...

I have, lol. I went and took a walk for about an hour and a half and the wife and I went out to eat. Then I went to town with a friend to clear my mind. I have on problem left with the table employees. After that I got everything else with the group joins, sorts, etc. I don;t know why this is bothering me so much, lol  BUT THANK YOU BOTH SERIOUSLY!!!

Get a piece of paper...

write down your requirements : 3 departments, 7 employees, and 2 locations.

for each department you need the information for the columns in the table. 1 department = 1 row = 1 insert. You end up with three inserts.
for each employee you need the information for the columns in the table. 1 employee = 1 row = 1 insert. You end up with seven inserts.
for each location you need the information for the columns in the table. 1 location = 1 row = 1 insert. You end up with two inserts.

Now because of the foreign key constraints, you must do the insert in the correct sequence...

1) first location (two insert statements)
2) then departments (three insert statements)
3) then manually pick a manager (or two or three) from your list of employees (on paper remember)
4) add those "special" employees first
5) update the departments manager-id with those employees
6) add the rest of the employees making sure the correct department and manager id is assigned

the format of the insert is :

INSERT <table_name> (<columns_list excluding the ID>) values (<source columns>)

we exclude the ID because they are identity and will be automatically allocated.

Lets take "locations" as an example:

INSERT LOCATIONS ([POSTAL_CODE], [CITY], [STATE_PROVINCE], [COUNTRY]) values ('2000','Sydney','NSW','Australia')


the format of the UPDATE statement is :

UPDATE <table_name>
SET <column_name> = <new_value>
WHERE <conditions>

for example if we want to update  department 1 with employee_id 1 as the manager code

UPDATE DEPARTMENTS
SET MANAGER_ID = 1
WHERE DEPARTMENT_ID = 1

For dates = use datatype DATETIME and in inserts use the string e.g. '20101001' (ie YYYYMMDD) when doing your inserts

For money = use datatype MONEY and in insterts use the value e.g. 120000.00  (ie just a number without quotes and such like)

For integers = use datatype INT and in inserts / updates use the value  e.g. 1 (ie just an integer without quotes)

For strings - use datatype VARCHAR(30) and in inserts use the string e.g. 'Some Name' (ie use single quotes around the character string)

(you may want to use VARCHAR(60))

Sweet Mark that actually makes sense and helps. I am in the process of trying to drop a table (Employees) but I get that pesky foreign key dependency. I need to shore up my varchar(20) and go with what you mentioned.

Kind of not sure where I should use integer at. I already did it for Location_ID and DEPARTMENT)ID I think when we did them earlier

also why not use just date? to get the YYYY-MM-DD?

Right,

You have :

1) create tables
2) Datatypes, Primary Keys
3) create constraints (Foreign Keys)
4) Inserts
5) Updates

You still have to do :
a) a SELECT statement
b) create a VIEW
c) database experiment (like "joe joins the company", or a clue from the sample they give "whats the average salary")
d) recommendations / improvements (hint : we have discussed a few above)

The best approach is to start playing with your SELECT statements because a view is  like a stored select statement - creating a "virtual table" if you like - because you just use the view name as your datasource in a select statement just like a table.

When getting data from more than one place you need to JOIN those datasources.

Have a read of :
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_3102-SQL-101-SELECT.html
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2685-SQL-Server-Joins-Explained-Logical-Joins.html

You can use YYYY-MM-DD just needs to be encapsulated in quotes so that the engine knows to convert to date. The "universal" date format as a string that is immediately convertable / recognised as a date (when used in a date context) is YYYYMMDD or YYYY-MM-DD. so either way your fine.

Using MM/DD/YYYY can also work - but can depend on how your database is setup - normally the US style date format doesnt present a problem, just "best practises" to use the universal date format.

In SSMS if you right click on a table (on left hand side in object browser) you can script the DROP to a new query window and it will show you how to remove those constraints.

Sweetm thanks

well it shows me IF Exists, blah blah blah. Tons of stuff. I am thinking close SQL restart and delete the table using a right click on the table :)

Oh, and DATE in place of datetime is fine for things like "date started" (ie in your situation) but datetime is better for things like "date_last_changed" because it includes the time component  (which does not apply to you in this case).

You do need to get across the top of that IF EXISTS and stuff.

It is actually good stuff - all it is doing is checking before it does something so there wont be any errors.

You cannot just delete - those constraints still exist...

That is also why it is good to first create your tables, then afterwards, apply the constraints.

Imagine your teacher. Gets some script, runs it. Crash and burn because it is not checking "IF EXISTS" and that type of thing.

Bonus points for you if you first check it and it runs without errors :)

I see what u r saying, and I actually understand, lol

Hey Mark I did update one Manager ID in the departments with my john bosley deal.

Now when I try to do the shipping it gives me this error:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__Departmen__MANAG__239E4DCF". The conflict occurred in database "Final", table "dbo.Employees", column 'EMPLOYEE_ID'.
The statement has been terminated

My whole plan is to have Gary Ullom with the 13411 Emp ID be the manager over Shipping and Production.

So I want it to work with this:

UPDATE [Final].[dbo].[Departments]
   SET [MANAGER_ID] = 13411
   WHERE [DEPARTMENT_ID] = 10091
GO

and then as well with this


UPDATE [Final].[dbo].[Departments]
   SET [MANAGER_ID] = 13411
   WHERE [DEPARTMENT_ID] = 10244
GO

Both tend to give me that error I mentioned
Update-Error.jpg

Nevermind I have the wrong emp ID lol lemme try something else

Aha it worked!

Alright I got those updates in teh department ID. Now I should be able to fill in the other 5 employees and shouldn't have to update anything else since I have the 'special people' in place

Yep going good, but there are still foreign key constraints for those last 5 employees - have to (well, at least *should*) belong to a department and manager

I already got those. Ok now this is for the select statement. I got the 4a one no worries.

I got this going for 4b:

4b. A subquery which returns all of the employees first and last name, their
department name, but only those employees who earn more than the average salary
for their department.

Here is something I got, but it isn't giving me the names of people who earn more than the average salary:


Select Departments.DEPARTMENT_NAME, AVG(Employees.SALARY)as 'Avg Salary'
From Employees
Join Departments
On employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID
Group BY Departments.DEPARTMENT_NAME

Here is the results that it gives:

Check the picture, it is easier.

Question is, what can I put into that statement to get my SELECT STATEMENT to get the correct information?
4B.jpg

Ok I did something different and I am getting closer but I don't have the department name in there. Check it out Mark and thanks.

Jason
Revised-4b.jpg

I Can add Department_ID but from Employees there is no department name. This is where it gets tricky to me because you can do a variable or something or do a call to the departments table to get the name but I am not sure how I can go about doing that.

Ok Mark I was messing around and tried this:
SELECT EMPLOYEE_ID, DEPARTMENT_NAME, LAST_NAME, FIRST_NAME, SALARY
FROM employees, departments
      WHERE salary > 
(SELECT AVG(salary) from employees)       
GO

I am attaching the print screen showing what the results are. It is almost like it is a in triplicate of it. Any help is appreciated.
Newest--4b.png

ok wait lemme see if I got this right.

SELECT * FROM EMPLOYEES
JOIN Departments
ON Employees.Department_ID = Departments.Department_ID
JOIN
   (Select DEPARTMENT_ID, AVG(Employees.SALARY)as Avg_Salary
      From Employees
      Group BY DEPARTMENT_ID) as SAL on SAL.department_id = employees.department_id

where employees.salary > SAL.avg_salary

Just like that and it will work out for me?

Actually we can cut out everything after Employees and before the second join.

It works out rather well to be honest with you. If I include what I put above it adds a couple duplicates to the equation. This way it covers everything. Thanks Mark.

Hmmmm....

SELECT * FROM EMPLOYEES
JOIN Departments
ON Employees.Department_ID = Departments.Department_ID


should not show any duplicates - unless an employee has more than one department... Might be worth going back and double checking your data...

Ok is there a way to give VAD and Mark the points for this?

These two guys helped me so much and more than they probably should have. I got a great understanding of what I needed to do and what I forgot. The links were helpful and everything made sense for me. I even figured out some errors Ion my own. Thank you Vad and Mark so much!