Date: 06/13/06     Keywords: sql, microsoft

Does anyone know of a place to grab the single file any more? Microsoft has taken down the page for it, and the MDAC 2.8 files don't seem to want to install on XP. They say that all of the components are already available, but SQL Server does not show up in the "available drivers" when I try to create a new DSN. (My computer was wiped and redone from scratch a few weeks ago.)

In any case, I need the SQL Server ODBC driver, and Microsoft doesn't want to give it to me. Anyone have it?

Source: http://community.livejournal.com/sqlserver/49579.html

Date: 06/02/06     Keywords: database, sql

Okay, so we finally got SQL Server 2005 installed on my machine. (Wiped and started from scratch. Took a week to get it done right. Don't ask.) First thing I have to do: a vendor is changing table structures on me, so I need to go in and change the tables. Simple enough matter. Most of the tables change fine. Two of them, however... when I hit "save," I get a warning box: "saving definition changes to tables with large amounts of data..." then it bleeds off the side of the screen, but in essence it's a warning that the table will be locked. I hit OK, and about a minute and a half later, I get a timeout expired error.

Is there any way to do this without stripping the data, copying the table structure, or anything like that? (In other words, is there any way to save definition changes to a large, heavily populated database table as is?)

Source: http://community.livejournal.com/sqlserver/49376.html

Date: 05/25/06     Keywords: no keywords

Is it possible to convert nvarchar date 19950123 to varchar date 2006-05-23?

Thank you.

Source: http://community.livejournal.com/sqlserver/48722.html

Date: 05/25/06     Keywords: software, xml, database, sql, microsoft

Okay. So I've been trying to install SQL Server 2005 for about a month now, on and off. I keep running into the usual problems. My boss wants it installed now now now, so this time I went whole hog. I uninstalled everything remotely Microsoft (including all versions of Visual Studio, all versions of the .NET framework, Office, and a bunch of little add-ons I assumed went to Visual Studio, like WSE and the XML parsers) from the machine. I physically deleted every file I could from the C:\Program Files\Microsoft SQL Server directory. (instapi.dll, from the Microsoft SQL Server\90 directory could not be deleted, no matter what I did, but everything else went away). I try to install, and when I get to the point where it's actually going to install the software< I get the following dialog box:

The following components that you chose to install are already installed on the machine. To view a report of available options and alternatives click on Details.
SQL Server Database Services 9.00.1309.06
Analysis Services 9.00.1309.06
Reporting Services 9.00.852
Notification Services 9.00.852
Integration Services 9.00.852
Workstation Components, Books Online, and development tools 9.00.852

Each of these has a checkbox next to it, but all the checkboxes are blank. When I hit Next, I get the following dialog box:
None of the selected features can be installed or upgraded. Setup cannot proceed since no effective change can be made to the machine. To continue, click Back and select features to install.

Now, I know the physical fiels aren't there, since I uninstalled the software and then deleted any artifactas, so the problem must be that there are some stray registry keys telling the install procedure that the software still exists on the machine. Does anyone have a comprehensive list of registry keys written by SQL Server 2005, so I can make sure everything is gone before I try this again?

Source: http://community.livejournal.com/sqlserver/48457.html

Date: 05/17/06     Keywords: no keywords

I have a data in the field looks like N 4390-1524-23
How can I convert them to look like N 4390152423?

Thank you in advance.

Elena.

Source: http://community.livejournal.com/sqlserver/48343.html

Date: 04/27/06     Keywords: no keywords

I have txt file that contains columns that are fixed length and i know each one of the lengths.
The problem is that on the last field in the row it reads in a chunk of the first field on the next line for whatever reason. Row delimeters are the usual {CR}{LF}

I suspect two things:

1. I am not doing some obvious in DTS that I should be.
2. The last field is not padded with spaces that it should be padded with. Txt file comes down from the mainframe so how can I check to see if the file is really padded with the spaces.

UPDATE:I just found out that it is not padded with spaces all the way to 50 characters in the last field of the row. At the end of each line I find a '$' when I used VIM and ':set invlist' to show the characters. There is no option for '$' for line feed in DTS. WTF?! I tried setting it to LF with the same result as just CR and CRLF

SOLVED: Apparently '$' somehow translates into {CR}{LF} in vim so the dollar wasn't the problem. What you have to use instead of source txt file is a bulk insert with a file format where the last row terminator is set to "\r\n". Sweet.

Source: http://community.livejournal.com/sqlserver/47898.html

Date: 04/27/06     Keywords: sql

What tools do you guys use for direct access to SQL Server? I am on Windows 2000 Pro, and right now use Access through a DSN. Is there a better way?

Source: http://community.livejournal.com/sqlserver/47667.html

Date: 04/26/06     Keywords: database, sql, microsoft

I'm attempting to put a simple copy-to-file into a DTS package as the last step of a sequence of events. What I want it to do is take a table and copy that table to a plain text file. Whether or not that file physically exists when I try to do this, the same thing happens. (It can physically exist because I'm capable of doing this manually with Import/Export Data; I'd just like to automate it, as this will be a monthly procedure.)

I put in the source (SQL Server) and the Destination (Text file), set up the properties on those, then add the data pump task. The data pump task defaults to the same text file as is used in the source step. (This is both attempting to use the same SQL server connection as in the first step-- which is called edi-- and attempting an entirely new connection to the same database-- which is called, imaginatively, edi2.) I click the "Define columns..." button, click "populate from source," and get the columns accurately. I then hit "Execute," the box goes away, and the "columns" listbox on the destination tab is left completely blank. If I hit "define columns..." again, I get the following error, and the two numbers are the same every time:

The instruction at "0x4173d23a" referenced memory at "0x01591eb8". The memory could not be "written". Click on OK to terminate the program.

I only managed to find one reference to the 0x417 number, and the reference turned out to be out of date (it 404ed on me). Everything Microsoft has about the generic error has to do with OLAP or things running in parallel; I'm not dealing with OLAP, and I'm not even running the package, just trying to biold it, so no help there.

Anyone seen anything like this? Got any ideas? Thanks.

Source: http://community.livejournal.com/sqlserver/47511.html

Date: 04/25/06     Keywords: no keywords

Hi!
I have 2 tables.
First table has 2 fields:
FIRST NAME LAST NAME

I need to copy data to new table, but new table has only one field:
CLIENT NAME
How can I copy data?

Thank you,
Elena.

Source: http://community.livejournal.com/sqlserver/47332.html

Date: 04/17/06     Keywords: no keywords

SELECT a.ProvID, a.Category, a.DRGs, a.RevCode, a.TypeID, a.Rate AS MarketRate, b.Rate AS ContractRate, ' ' as MarketLevel2Rate, ISNULL(a.Comment, 'None') AS Comment, a.LineEffective, a.LineExpiration
INTO CombinedRateData
FROM RateData a
LEFT OUTER JOIN RateData b ON a.ProvID = b.ProvID AND a.Category = b.Category AND a.DRGs = b.DRGs AND a.RevCode = b.RevCode AND a.TypeID = b.TypeID AND ((a.Comment = b.Comment) OR (a.Comment IS NULL AND b.Comment IS NULL))
AND a.LineEffective = b.LineEffective AND a.LineExpiration = b.LineExpiration AND b.RateTypeID = 2
WHERE a.RateTypeID = 1
GROUP BY a.provid, a.category, a.DRGs, a.RevCode, a.TypeID, a.Comment, a.LineEffective, a.LineExpiration, a.Rate, b.Rate


okay... I have a table. It presently has ProvID, Category, DRGs, RevCode, TypeID, Rate, Comment, LineEffective, LineExpiration, RateTypeID.

I need it to have ProvID, Category, DRGs, RevCode, TypeID, MarketRate, ContractRate, Comment, LineEffective, LineExpiration.

The difference is that Market Rates have a RateTypeID of 1, and Contract Rates have a RateTypeID of 2. Thus, the query above.

The problem is, no matter how I play with the RateTypeID, where I put it, etc., I can never get ContractRate to populate everywhere it should. It doesn't matter where I put "RateTypeID = ", it still doesn't manage to find the two columns-- MarketRate will be filled, and ContractRate will be NULL.

EDIT: This is now only happening when the two Rates are identical. One hospital with a Market rate of 7% and a Contract rate of 8% is now filling fine; a hospital whose market and contract are both $1050 per diem is giving me market 1050 and contract NULL. Grrrrr.

Source: http://community.livejournal.com/sqlserver/47100.html

Date: 03/28/06     Keywords: no keywords

Here's my situation.

In either an INSERT INTO or an UPDATE query-- I can go either way with it, depending on what ends up being faster-- I need to assign a number to each row. That number is based on criteria from the other fields being pulled in. I could do this with a CASE statement, but that would be a long, long, long, long CASE statement.

An easier way to do it is with math. Pseudocode:

1. x = 0.
2. If CH.ParStatus = "N", x=x+5.
3. If Ch.ClmType not in ("I","O"), x=x+4 else
3a. If CD.POS = 21, x=x+1
3b. If CD.POS = 23, x=x+3
3c. For all other values of CD.POS, x=x+2
4. table.CatID = x.

Now, the question is... is it possible to do all this within one INSERT INTO/UPDATE statement? Back in the old FoxPro days, I'd have done this with a SCAN, but that option isn't available to me here...

Thanks.

Source: http://community.livejournal.com/sqlserver/46572.html

Date: 03/14/06     Keywords: programming, sql, security

Hi everyone.

...about programming MS ADO with MS SQL Server.
Is it possible to get a (ADODB) Recordset having NOT Forward-only cursor type from 'Execute' method of ADO Command object?

To be clear, in terms of VBA Code:

Dim adocmd as ADODB.Command
Dim rst as ADODB.Recordset
Set adocmd = New ADODB.Command
' ....... Connecting 'adocmd' via MS SQLOLEDB Provider
Set rst = adocmd.Execute

The problem is that I could not get non-ForwardOnly recordset in the last statement in all cases, and I wish to use the result (rst) as the data source (Recordset property) for a MS Access form. Such forms don't allow to set forward-only recordsets as form data sources, of course.

The parameters of 'Execute' method of ADODB.Command object do not include cursor type setting of the recordset returned.

Thanks in advance for any help.

UPDATED. A solution is found.

Setting the client-side cursor to the active connection may fix the problem. As the documentation says, "Cursors returned by the Execute method inherit this setting", so after such setting the Recordset object returned by Execute method of the Command object has Client-side static cursor - this is appropriate for setting it as a Data Source Recordset for an MS Access Form to browse records.

Here is a VBA sample code. (Of course, in this example using ADODB.Command for executing a simple SELECT Query to return records is senseless, but it's just for example. In my situation I call a stored procedure with parameters (including OUTPUT params and RETURN_VALUE) that also return records, and I wanted to use all advantages of ADODB.Command object for such multiple calls).

    Dim cnn As ADODB.Connection
    Dim adocmd As ADODB.Command
    Dim rst As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    cnn.CursorLocation = adUseClient
    cnn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=TestDB;Persist Security Info=False;Integrated Security=SSPI;"
    
    Set adocmd = New ADODB.Record
    With adocmd
        Set .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "SELECT 10 AS foo UNION SELECT 20"
        Set rst = .Execute
    End With

' Now `rst` has CursorLocation=adUseClient and CursorType=adOpenStatic
' and may be used as a Recordset for an MS Access Form
' If we are in form module, then one may set
'       Set Me.Recordset = rst
' or even we could write before
'        Set Me.Recordset = .Execute

Source: http://community.livejournal.com/sqlserver/45984.html

Date: 03/09/06     Keywords: sql, microsoft

My apologies. I've been quite inquisitive the past few weeks. It'll get slightly worse before it gets better, but it will get better, eventually.

Installed SQL Server 2005. Attempted to start SQL Server Management Studio. Splash screen flashed. Then nothing. Tried to start Business Intelligence Development Studio. Got the following error message:

Cannot find one or more components. Please reinstall the application.

Yelled at the computer, sacrificed a few small animals, did a rain dance. Nothing worked. Attempted a "repair installation" routine which told me everything was, in fact, installed. Finally broke down and reinstalled the application-- from scratch (as in, pulled off all SQL Server 2005 components, not just the BI stuff, and reinstalled everything.) Tried to start SQL Server Management Studio. This time I got the splash screen for almost a full second, but the end result was still no program. Tried to start Business Intelligence Development Studio, and... you know the drill.

As reinstalling is an admission of failure on both my part and Microsoft's-- especially when I have to do it twice in a row, with the same outcome expected when I do it again-- does anyone know of any way I can find out which component(s) is/are missing and work from there?

Thanks.

Source: http://community.livejournal.com/sqlserver/45630.html

Date: 03/09/06     Keywords: php, mysql, sql

Hi guys,

I'm wondering if it's possible to validate the output of an sql query?

I have the following query/code (using PHP5, and MSSQL):

		$sql = "SELECT TOP 100 PERCENT Mem_Number, Title_1, Name_1, Surname_1, Email, Date_Sent_Renewal, EmailList, Renewal_Date FROM BTCMembers WHERE (DATEDIFF(d, Renewal_Date, GETDATE()) > ".$cutoff.") AND (DATEDIFF(d, Renewal_Date, Date_Sent_Renewal) < 0) AND (Email <> '') AND (Date_Sent_Renewal <> '') AND (EmailList = 1) ORDER BY Mem_Number ASC";



		$result = odbc_exec($contact, $sql); // Run the query

php

sqlserver

mysql

Source: http://community.livejournal.com/sqlserver/45440.html

Date: 03/08/06     Keywords: database, sql

I am trying to transform data from an old Image database to MSSQL and I have hit a wall on this one table.

The schema of the Image table is

old_table(
id char(10),
desc_text char(70),
eff_yrq_beg char(4),
ln_num char(2))

There are multiple records (ln_num) for each id that in order create a description text block.

What I am envisioning is a table

new_table(
id varchar(10),
description varchar(500),
eff_yrq_beg char(4))

In the new table the description is a concatenation of the desc_text from the first table in order by ln_num.

Is there any way that you can think to do this without cursors?

Source: http://community.livejournal.com/sqlserver/45175.html

Date: 03/07/06     Keywords: software, sql, microsoft

Okay, I give up. I've been beating myself over the head with this all day.

I'm trying to install SQL Server 2005 Standard Edition. I keep getting the error "SQL Server Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again." There's another sentence, but it's useless.

I have uninstalled anything and everything listed in add/remove programs that could possibly be causing this error, and I still get it. I got so frustrated I installed VS2005 Standard, hoping that it would overwrite whatever the problem component is, but no help there.

Tell me what lists, files, registry settings, etc. you need to see to be able to tell me what I need to fix to get this to work. Please. And thanks.

EDIT: Running the auto-force-removal tool, not surprisingly, found nothing else to uninstall. Going through the core.log file reveals:
Product "{3BDB182E-8371-46BD-AC39-C14A91D5EEF8}" versioned 9.00.852 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is "Microsoft SQL Server 2005 Reporting Services Beta 2"
Product "{63A5DC0D-1EDD-4D69-8F31-87FAEB1F7084}" versioned 9.00.852 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is "Microsoft SQL Server 2005 Notification Services Beta 2"
Product "{E0A41F96-7231-4AE8-A654-EEB34F935462}" versioned 9.00.852 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is "Microsoft SQL Server 2005 Data Transformation Services Beta 2"
Product "{90032DD0-ABEE-4424-AC1E-B076BDD4E350}" versioned 9.00.852 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is "Microsoft SQL Server 2005 Tools Beta 2"

The products are not listed in add/remove programs, and the bracketed values are not findable in the computer's registry. I'd rather not just wander around deleting DLLs randomly; if anyone's got any ideas where I might find these keys/files/proggies/what-have-you, I'm all ears.

EDIT2: Solved. Left here in case anyone else has the problem...

there were four registry entries left over from beta software I'd removed months ago. They were in HKEY_CLASSES_ROOT/Installer/Products; anything in there from .NET2 betas, even if those products are long gone, will set off the watchdog.

Source: http://community.livejournal.com/sqlserver/45014.html

Date: 03/05/06     Keywords: database, sql

So i'm in a sql class and i have no idea what i'm doing. *yikes* I need to...

I'd like to have at least five transactions, with at least two
different movies rented each, and one where they bring back a movie

(RL_INOUT).

You have to decide:

1. which employees are going to make those transactions
2. Which customers are going to rent the movies/games
3. Which movies those customers are going to rent.


Yeah... I'm LOST!


Our current table structure
Here's what I've got for our database structure so far. Discuss here!

Here are the tables (called out by SELECT * FROM TABLE_NAME and DESC TABLE_NAME)
ACCT:
Code:

+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| ACCT_ADDRESS | char(50) | YES | | NULL | |
| CITY | char(50) | YES | | NULL | |
| ACCT_STATE | char(2) | YES | | NULL | |
| ACCT_ZIP | decimal(5,0) | YES | | NULL | |
| ACCT_DEBT | decimal(6,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

+------------+------------------+-----------------+------------+----------+-----------+
| ACCT_ID | ACCT_ADDRESS | CITY | ACCT_STATE | ACCT_ZIP | ACCT_DEBT |
+------------+------------------+-----------------+------------+----------+-----------+
| 8132546892 | 85916 FOXHILL | HAMTRAMMICK | MI | 48325 | 15.50 |
| 8217394659 | 74985 BELINDA | CLINTON TWP | MI | 48310 | 0.00 |
| 8221354897 | 28486 STAPLER | SPINDLE | MI | 48310 | 15.00 |
| 8231645797 | 78456 GARFIELD | LAWSON | MI | 47158 | 25.00 |
| 8312584695 | 3125 CHARITY | LIVONIA | MI | 48310 | 0.00 |
| 8574962138 | 3131 ALBANY | LANSING | MI | 48317 | 2.50 |
| 8579462312 | 28547 RUTHER | DEARBORNE | MI | 48315 | 5.00 |
| 8779654251 | 25816 GEORGIA | KNOB CREEK | MI | 47861 | 0.00 |
| 8796452135 | 25781 SOMESTREET | FAIRFIELD | MI | 48631 | 7.50 |
| 8896354187 | 85749 CHECKERS | TIMBUCKTWO | MI | 48156 | 0.00 |
| 8899775521 | 45612 LAYFETTE | MOUND | MI | 48356 | 5.00 |
| 8974653127 | 31582 RYAN | MADISON HEIGHTS | MI | 48197 | 0.00 |
| 8976542315 | 95846 MARK ORR | ROYAL OAK | MI | 48312 | 5.00 |
| 8995542368 | 21058 SALEM | TROY | MI | 48213 | 7.50 |
| 9478652351 | 20487 PINEBROOK | ALLEN PARK | MI | 48321 | 10.50 |
+------------+------------------+-----------------+------------+----------+-----------+
15 rows in set (0.00 sec)



CUSTOMER:
Code:

+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| CUST_NUM | decimal(2,0) | | PRI | 0 | |
| CUST_FIRST_N | char(15) | YES | | NULL | |
| CUST_LAST_N | char(15) | YES | | NULL | |
| CUST_BDAY | date | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

+------------+----------+--------------+-------------+------------+
| ACCT_ID | CUST_NUM | CUST_FIRST_N | CUST_LAST_N | CUST_BDAY |
+------------+----------+--------------+-------------+------------+
| 8132546892 | 1 | MELISSA | LEMMINS | 1950-02-18 |
| 8217394659 | 1 | DESRA | JACKSON | 1981-01-27 |
| 8221354897 | 1 | JUSTIN | MARLBORO | 1987-06-25 |
| 8231645797 | 1 | SARAH | HOPPINS | 1980-08-26 |
| 8312584695 | 1 | LARRY | BROWN | 1968-03-22 |
| 8574962138 | 1 | SUE | EVANS | 1983-03-17 |
| 8579462312 | 1 | JAMES | RAUSCH | 1956-01-16 |
| 8779654251 | 1 | DAN | COOLMEN | 1985-01-08 |
| 8796452135 | 1 | BOB | RAMSEY | 1975-02-14 |
| 8896354187 | 1 | ANDREA | TYLER | 1985-07-13 |
| 8899775521 | 1 | CHRIS | JOANS | 1984-11-02 |
| 8974653127 | 1 | TIFFANY | SCHULTZ | 1985-07-25 |
| 8976542315 | 1 | ROBBY | SMITH | 1982-04-23 |
| 8995542368 | 1 | JESSE | SAAD | 1973-04-20 |
| 8995542368 | 2 | MARY | SAAD | 1976-03-24 |
| 9478652351 | 1 | JOANN | TIMBERSHMIT | 1973-05-18 |
+------------+----------+--------------+-------------+------------+
16 rows in set (0.01 sec)



INVENTORY:
Code:

+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| INV_ID | char(12) | | PRI | | |
| INV_MEDIA | char(3) | YES | | NULL | |
| INV_TITLE | char(30) | YES | | NULL | |
| RT_TYPE | char(2) | YES | MUL | NULL | |
| RATE_CODE | char(4) | YES | MUL | NULL | |
| INV_COPIES | decimal(4,0) | YES | | NULL | |
| INV_ONHAND | decimal(4,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

+--------------+-----------+-------------------------------+---------+-----------+------------+------------+
| INV_ID | INV_MEDIA | INV_TITLE | RT_TYPE | RATE_CODE | INV_COPIES | INV_ONHAND |
+--------------+-----------+-------------------------------+---------+-----------+------------+------------+
| 012236189411 | DVD | WAITING... | NV | UR | 7 | 4 |
| 012569500921 | DVD | GONE WITH THE WIND | OV | G | 2 | 2 |
| 012569593237 | DVD | MILLION DOLLAR BABY | NV | UR | 2 | 0 |
| 013388200177 | GCN | RESIDENT EVIL 4 | NG | M | 4 | 1 |
| 014633147025 | PS2 | NEED FOR SPEED UNDERGROUND | OG | E | 3 | 2 |
| 024543044789 | DVD | FIGHT CLUB | OV | R | 5 | 2 |
| 025192211928 | DVD | CINDERELLA MAN | NV | PG13 | 8 | 3 |
| 043396067660 | DVD | BLACK HAWK DOWN | OV | R | 3 | 3 |
| 045496870461 | N64 | SUPER SMASH BROS | OG | E | 4 | 3 |
| 047875810211 | XBO | CALL OF DUTY 2: BIG RED ONE | NG | T | 4 | 0 |
| 085391773726 | DVD | THE MATRIX | OV | R | 3 | 2 |
| 097360184044 | DVD | CLUE | OV | G | 1 | 1 |
| 097363365143 | DVD | ALONG CAME A SPIDER | NV | R | 2 | 1 |
| 710425274107 | PS2 | GRAND THEFT AUTO: SAN ANDREAS | NG | AO | 3 | 2 |
| 786936161571 | DVD | PULP FICTION | OV | R | 3 | 3 |
| 788687500012 | PS2 | HITMAN 2: SILENT ASSASSIN | NG | M | 2 | 0 |
| 794043749728 | DVD | THE NOTEBOOK | NV | PG13 | 4 | 1 |
+--------------+-----------+-------------------------------+---------+-----------+------------+------------+
17 rows in set (0.00 sec)



RATING:
Code:

+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| RATE_CODE | char(4) | | PRI | | |
| RATE_DESC | char(30) | YES | | NULL | |
| RATE_MINAGE | decimal(2,0) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

+-----------+--------------------------+-------------+
| RATE_CODE | RATE_DESC | RATE_MINAGE |
+-----------+--------------------------+-------------+
| AO | Adults Only | 18 |
| E | Everyone | 1 |
| E10 | Everone 10+ | 10 |
| EC | Early Childhood | 1 |
| G | General Audiences | 1 |
| M | Mature | 17 |
| NC17 | Restricted 17 years | 17 |
| PG | Parental Guidence | 6 |
| PG13 | Strong Parental Guidence | 13 |
| R | Restricted | 17 |
| T | Teen | 13 |
| UR | Unrated | 21 |
| X | Mature Content | 21 |
+-----------+--------------------------+-------------+
13 rows in set (0.00 sec)



RENT_TYPE:
Code:

+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| RT_TYPE | char(2) | | PRI | | |
| RT_DESC | char(30) | YES | | NULL | |
| RT_LENGTH | decimal(2,0) | YES | | NULL | |
| RT_FEE | decimal(4,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

+---------+-----------+-----------+--------+
| RT_TYPE | RT_DESC | RT_LENGTH | RT_FEE |
+---------+-----------+-----------+--------+
| NG | New Game | 5 | 5.00 |
| NV | New Video | 2 | 6.00 |
| OG | Old Game | 2 | 4.00 |
| OV | Old Video | 7 | 5.00 |
| S1 | Special 1 | 2 | 3.00 |
| S2 | Special 2 | 2 | 1.50 |
+---------+-----------+-----------+--------+
6 rows in set (0.00 sec)



EMPLOYEE:
Code:

+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| EMP_ID | char(4) | | PRI | | |
| EMP_FIRST_N | char(15) | YES | | NULL | |
| EMP_LAST_N | char(15) | YES | | NULL | |
| EMP_ADDRESS | char(50) | YES | | NULL | |
| EMP_CITY | char(50) | YES | | NULL | |
| EMP_STATE | char(2) | YES | | NULL | |
| EMP_ZIP | decimal(5,0) | YES | | NULL | |
| EMP_WAGES | decimal(4,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

+--------+-------------+------------+-----------------+--------------+-----------+---------+-----------+
| EMP_ID | EMP_FIRST_N | EMP_LAST_N | EMP_ADDRESS | EMP_CITY | EMP_STATE | EMP_ZIP | EMP_WAGES |
+--------+-------------+------------+-----------------+--------------+-----------+---------+-----------+
| 0001 | TIM | WOJOWSKI | 38592 WEST | MACOMB | MI | 48035 | 15.00 |
| 0002 | LARRY | HILLIARD | 29438 NORTH AVE | MT. CLEMENS | MI | 38940 | 9.70 |
| 0003 | WILLIAM | JOHNSON | 29438 GRATIOT | MT. CLEMENS | MI | 38940 | 9.50 |
| 0004 | ANTHONY | WALKER | 24882 FAIRCHILD | MACOMB | MI | 48042 | 9.50 |
| 0005 | GEORGE | TOMLIN | 28784 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
| 0006 | BOB | BATES | 28743 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
+--------+-------------+------------+-----------------+--------------+-----------+---------+-----------+
6 rows in set (0.00 sec)



These tables do not have data yet!!

Code:
TRANSACTION:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| ACCT_ID | char(10) | YES | MUL | NULL | |
| CUST_NUM | decimal(2,0) | YES | | NULL | |
| TRANS_DATE | date | YES | | NULL | |
| EMP_ID | char(4) | YES | MUL | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

RENT_LINE:
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| INV_ID | char(10) | | PRI | | |
| RENT_QTY | decimal(2,0) | YES | | NULL | |
| RL_INOUT | char(3) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)




Following is all of the SQL code so far:

Code:
CREATE DATABASE BAKER_DVD;

USE BAKER_DVD;

CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) , -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address 1
EMP_CITY CHAR(50) , -- Address 2
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
PRIMARY KEY (EMP_ID) ) ENGINE=INNODB;

CREATE TABLE RATING
(RATE_CODE CHAR(04) , -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
PRIMARY KEY (RATE_CODE) ) ENGINE=INNODB;

CREATE TABLE ACCT
(ACCT_ID CHAR(10) , -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
CITY CHAR(50) , -- City of residence
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
PRIMARY KEY (ACCT_ID) ) ENGINE=INNODB;

CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) , -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
PRIMARY KEY (RT_TYPE) ) ENGINE=INNODB;

CREATE TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) , -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
PRIMARY KEY (ACCT_ID, CUST_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID) ) ENGINE=INNODB;

CREATE TABLE INVENTORY
(INV_ID CHAR(12) , -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating, (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
PRIMARY KEY (INV_ID),
FOREIGN KEY (RT_TYPE) REFERENCES RENT_TYPE(RT_TYPE),
FOREIGN KEY (RATE_CODE) REFERENCES RATING(RATE_CODE) ) ENGINE=INNODB;

CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10,0) , -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(02) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
PRIMARY KEY (TRANS_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID) ) ENGINE=INNODB;

CREATE TABLE RENT_LINE
(TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_ID CHAR(10) , -- Relavant video code
RENT_QTY DECIMAL(02,0) , -- Quantity rented, usually one.
RL_INOUT CHAR(03) , -- Item In or Out status
PRIMARY KEY (TRANS_NUM, RENT_NUM),
FOREIGN KEY (TRANS_NUM) REFERENCES TRANSACTION(TRANS_NUM) ) ENGINE=INNODB;



Here's the data already entered:

Rent Type and Rating:

Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );

INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );



Inventory:

Code:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');

INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');

INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');

INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');

INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');

INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');

INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');

INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');

INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG13', '8', '3');

INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');

INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');

INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');

INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');

INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');

INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');

INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');

INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');



Customers and Accounts:

Code:
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');

INSERT INTO CUSTOMER
VALUES
('8574962138', 1, 'SUE', 'EVANS', '19830317');

INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');

INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'LARRY', 'SCHULTZ', '19810322');

INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');

INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');

INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');

INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');

INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');

INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');

INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');

INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');

INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');

INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');

INSERT INTO CUSTOMER
VALUES
('8995542368', 2, 'MARY', 'SAAD', '19760324');

INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');

INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');

INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');

INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');

INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI', '48197', 0);

INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315', '5.00');

INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');

INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310', 0);

INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');

INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');

INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');

INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310', 0);

INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');

INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861', 0);

INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');

INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');

INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156', 0);



Employee:

Code:
INSERT INTO EMPLOYEE
VALUES ('0001', 'TIM', 'WOJOWSKI', '38592 WEST', 'MACOMB', 'MI', '48035', '15.00');

INSERT INTO EMPLOYEE
VALUES ('0002', 'LARRY', 'HILLIARD', '29438 NORTH AVE', 'MT. CLEMENS', 'MI', '38940', '9.70');

INSERT INTO EMPLOYEE
VALUES ('0003', 'WILLIAM', 'JOHNSON', '29438 GRATIOT', 'MT. CLEMENS', 'MI', '38940', '9.50');

INSERT INTO EMPLOYEE
VALUES ('0004', 'ANTHONY', 'WALKER', '24882 FAIRCHILD', 'MACOMB', 'MI', '48042', '9.50');

INSERT INTO EMPLOYEE
VALUES ('0005', 'GEORGE', 'TOMLIN', '28784 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');

INSERT INTO EMPLOYEE
VALUES ('0006', 'BOB', 'BATES', '28743 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');



Discuss here. (the text after the -- is a comment.)


Last edited by enigma.0Z on Fri Mar 03, 2006 4:14 am; edited 7 times in total

Tue Jan 31, 2006 10:44 am

magecougar
Newbie


Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041


ER Diagram is complete... I'll get started on the Inventory Table Today... if anybody needs to know any of the info, like the vid num code, let me know... ttyl homies...


Mon Feb 20, 2006 3:33 am

magecougar
Newbie


Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041


I slightly restructured a bit, but mostly with what the primary keys are. The modified tables are as follows:

CREATE DATABASE BAKER_DVD;

CREATE TABLE INVENTORY
(INV_VID_CODE CHAR(12) PRIMARY KEY, -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
);

CREATE TABLE RATING
(RATE_CODE CHAR(04) PRIMARY KEY, -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
);

CREATE TABLE ACCT
(ACCT_ID CHAR(10) PRIMARY KEY, -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
);

CREAT TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) PRIMARY KEY, -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
);

CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) PRIMARY KEY, -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
);

CREATE TABLE RENT_LINE
(RENT_ID DECIMAL(10,0) PRIMARY KEY, -- ID Code for movie rental
TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_VID_CODE CHAR(10) , -- Relavant video code
RL_INOUT CHAR(03) , -- Item In or Out status
);

CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10) PRIMARY KEY, -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(04) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
);

CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) PRIMARY KEY, -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
);

let me know if any of this will cause any problems... hopefully not, because during my boredom, I had completed the data for 3 of the tables: INVENTORY, RATING, and RENT_TYPE. Hopefully nobody had their hearts set on those because they're done now. If you want the info for those tables, here are the codes:

INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');

INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');

INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');

INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');

INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');

INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');

INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');

INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');

INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG-13', '8', '3');

INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');

INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');

INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');

INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');

INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');

INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');

INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');

INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');

**MOD EDIT: Rent type and rating have already been made **

INSERT INTO RATING
VALUES
('G', 'GENERAL AUDIENCES', '0');

INSERT INTO RATING
VALUES
('PG', 'PARENTAL GUIDANCE SUGGESTED', '0');

INSERT INTO RATING
VALUES
('PG-13', 'PARENT STRONGLY CAUTIONED', '13');

INSERT INTO RATING
VALUES
('R', 'RESTRICTED', '17');

INSERT INTO RATING
VALUES
('NC-17', 'NO ONE 17 AND UNDER ADMITTED', '18');

INSERT INTO RATING
VALUES
('EC', 'EARLY CHILDHOOD', '3');

INSERT INTO RATING
VALUES
('E', 'EVERYONE', '6');

INSERT INTO RATING
VALUES
('E10+', 'EVERYONE 10 AND OLDER', '10');

INSERT INTO RATING
VALUES
('T', 'TEEN', '13');

INSERT INTO RATING
VALUES
('M', 'MATURE', '17');

INSERT INTO RATING
VALUES
('AO', 'ADULTS ONLY', '18');

INSERT INTO RENT_TYPE
VALUES
('NR', 'NEW RELEASE', '2', '3.50');

INSERT INTO RENT_TYPE
VALUES
('AC', 'ACTION, ADVENTURE', '5', '2.50');

INSERT INTO RENT_TYPE
VALUES
('CL', 'CLASSIC', '7', '2.00');

INSERT INTO RENT_TYPE
VALUES
('SF', 'SCI-FI', '5', '2.50');

INSERT INTO RENT_TYPE
VALUES
('CO', 'COMEDY', '5', '2.50');

INSERT INTO RENT_TYPE
VALUES
('DR', 'DRAMA', '5', '2.50');

INSERT INTO RENT_TYPE
VALUES
('SH', 'SHOOTER', '5', '2.50');

INSERT INTO RENT_TYPE
VALUES
('SI', 'SIMULATOR', '5', '2.50');


I post the correct values tonight during class.

** End edit **

For the rest of you all, good luck on whatever the hell your part is. Again to enigma, the ER diagram is done. I'll have it for you tomorrow during class at the latest. If i can find a way to e-mail it to you in my spare time, i will do so. Peace yall..


Mon Feb 20, 2006 6:17 am

ncamb02
Newbie


Joined: 31 Jan 2006
Posts: 1



INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');

INSERT INTO CUSTOMER
VALES
('8574962138', 1, 'SUE', 'EVANS', '19830317');

INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');

INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');

INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');

INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');

INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');

INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');

INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');

INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');

INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');

INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');

INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');

INSERT INTO CUSTOMER
VALUES
('8995542369', 2, 'MARY', 'SAAD', '19760324');

INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');

INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');

INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');

INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');

INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI '48197');

INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315' '5.00');

INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');

INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310');

INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');

INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');

INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');

INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310');

INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');

INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861');

INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');

INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');

INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156');
_____________________________________________________________________________________________________
I HAVE A COUPLE QUESTIONS... HOPE THE DATA IS RIGHT BUT IF NOT ITS A COUPLE QUICK FIXES. FIRST, THE TABLE STRUCTURE HAS DECIMAL(02) WITHIN THE CUSTOMER TABLE FOR CUST_NUM... I DIDNT KNOW IF YOU WANTED THAT AS A DECIMAL OR JUST A TWO DIGIT NUMBER? SECOND UNDER THE ACCT TABLE NOT EVERY CUSTOMER SHOULD HAVE A SECONDARY ADDRESS SHOULD THEY? AS I WAS TYPING IT IN... DONT U THINK THEY SHOULD HAVE A TELEPHONE #? IF ANYONE WANTS TO ME TO FIX NETHIN ILL BE AVAILABLE ALL DAY BE4 CLASS SO GIVE ME A CALL @ 586-855-9290 ... ALRIGHT IF NOT SEE U GUYS IN CLASS


Mon Feb 20, 2006 4:24 pm

magecougar
Newbie


Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041


I didn't think we would need a second address, that's why I didn't put it in my revised table structure... By the way, I'm still waiting for an opinion on whether that was the way yall wanted to go... let me know... If you want to meet up before class, Nick, I'll be in the learning center from now (12:00) till class... let me know... later...


Tue Feb 21, 2006 2:01 am

Invinciblemoron
Newbie


Joined: 26 Jan 2006
Posts: 2



Technically we need the second address for the city address. I coded with room for the City address


Code:
insert into employee
values ('0001', 'Tim', 'Wojowski', '38592 West', 'Macomb", 'MI', '48035', '15.00');

insert into employee
values ('0002', 'Larry', 'Hilliard', '29438 North Ave', 'Mt. Clemens' 'MI', '38940', '9.70');

insert into employee
values ('0003', 'William', 'Johnson', '29438 Gratiot', 'Mt. Clemens 'MI', '38940', '9.50');

insert into employee
values ('0004', 'Anthony', 'Walker', '24882 Fairchild', 'Macomb', 'MI', '48042', '9.50');

insert into employee
values ('0005', 'George', 'Tomlin', '28784 Cotton', 'Chesterfield', 'MI', '48315', '9.50');

insert into employee
values ('0006, 'Bob', 'Bates', 28743 Cotton', 'Chesterfield', 'MI', '48315', '9.50');



_________________
You have Employees

5 employees is gewd.

place in code bracket and use insert statements

Tue Feb 21, 2006 6:04 am

enigma.0Z
Site Admin


Joined: 25 Jan 2006
Posts: 8



I've already did rating and rent_type. That's what I handed out to everyone. We need the address_2 because addresses have two lines.

As far as the inventory entries go, we just need to change them to match what I handed out last class (I'll edit them myself).


Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years old" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );

INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );e




As far as dec(02) in customer, that's just the number of the customer under that account number:

for instance Account 12345 Customer 1 is a dad, customer 2 is a mom, and 3, 4, and 5 are their kids

Source: http://community.livejournal.com/sqlserver/44667.html

Date: 02/23/06     Keywords: sql

Hi again,
I posted before about how I was coming from Oracle to SQL Server so apologies in advance if this is a stupid question.
I've been using Stored Procedures to return select statements.
What I'm wondering is if they can return data from multiple select statements or variables?
For example
A customer table, and a customer phones table which can have 4 records for the customer.
I want to return customer, phone1, phone2, phone3, phone4
If I just joined the tables I'd get
customer, phone1
customer, phone2 etc.

In Oracle I'd write a function which would get the customer, have a second query to go through the phones table and assign each one to a variable and then return the customer no and the variables.

Can this be done in SQL Server?

Source: http://community.livejournal.com/sqlserver/43823.html

Date: 02/17/06     Keywords: sql

In your opinion, should a table's clustered index be the primary key? Always? Never? Under certain conditions? When? Why or why not?

If it's not the primary key, what are your rules for determining which index should be clustered?

EDIT: I'm actually going to use this to ask a real queston, too. In SQL Server 2000.

I have a table with roughly 5,500 lines, which means I don't want to do this manually. The data is concatenated from two different tables, so the ID field has a number of duplicates. What I'd like to do is wipe out the data in the ID column (which is arbitrary) and have the system bulk assign IDs when I set the column to identity. I've tried about twenty different ways to do this with no success. various rand() functions are moving far too fast for me to get different values (and nothing else in the table is unique, or close enough to it, for me to use as a more random seed than millisecond).

Is there any way to get SQL Server to bulk-assign identity column values, so I can get done with this and move on? Thanks.

Source: http://community.livejournal.com/sqlserver/43610.html

Date: 02/17/06     Keywords: no keywords

In your opinion, should a table's clustered index be the primary key? Always? Never? Under certain conditions? When? Why or why not?

If it's not the primary key, what are your rules for determining which index should be clustered?

Source: http://community.livejournal.com/sqlserver/43494.html