MS SQL SERVER, 3 basic questions

[JC724]

1st question: I am trying to import excel files. Is there a way to set primary keys and foreign keys while importing the files?

2nd question: How do you set up foreign keys?

3rd question: I am trying to write some complex queries as I have learned I am having trouble doing a simple query looking for a string.

Ex: I have a table(Named client) clientID firstName lastName address
'client1' 'James' 'Smith' 'Chicago'

If I wanted to search for address with chicago

SELECT cl.address
FROM client cl
WHERE cl.address = 'detroit'

It is show address with nothing. If I take out the WHERE then it shows it.

How do you query a string?? I would think 'string' would work?

 

[modi123]

1 - no, typically that is setup when you design the table to be the destination for the data, or set afterwards when the insert is done on a new table.
2 - Have a key in one table tied to another.
3. If you are looking for part of a string use the term 'LIKE' and put % around the term.
Ex:
WHERE myColumn LIKE '%foo%'

Have you read up on basic SQL querying? It would suggest that before random poking around.

 

[Doward]

Just to go a little more in depth, weeks later

1. If your data does not already have a uniquely identified column (or set of columns) then no, don't even try to force it. For MS-SQL, just create your new table structure with the data you want from your excel, use the import wizard to pull your excel sheet into a _temp (or whatever naming convention you prefer) table, *verify* the data, then just select into <properly set up, clean table> from <excel_temp>;

2. Depends!

Table creation :: CONSTRAINT FK_excel1_excel2 (or whatever naming convention you prefer) FOREIGN KEY (col_id) REFERENCES table (col_id)
Table alteration :: ALTER TABLE excel_table (or whatever naming convention you prefer) ADD CONSTRAINT FK_excel1_excel2 (or whatever naming convention you prefer) FOREIGN KEY (col_id) REFERENCES table (col_id);

The entire point of a foreign key (often FK) is that say you have table a, column b and table c column d, and you want to ONLY allow a.b to have values that are listed in c.d - you set up a foreign key. ALTER TABLE a ADD CONSTRAINT FK_b_d FOREIGN KEY (b) REFERENCES c (d). I've now created a reference between a.b and c.d There's more you can do (cascading updates, deletes, etc) - but this is a start.

3. the % character is the 'any wildcard' character in SQL, and is used with LIKE. so LIKE '%red' matches ared, bred, cred, but NOT arede, brede, crede. LIKE 'red%' would match red2, red5, redb, but NOT ared, ared2, aredb. LIKE '%red%' would match every single example used here

Here's a good place for a lot more on wildcards in SQL - https://www.w3schools.com/sql/sql_wildcards.asp

 

[StoleMyOwnCar]

Foreign keys can get kind of tricky depending on how many columns you want to reference. My knowledge is based on MySQL as that is what we use, but basically if you need a foreign key that determines if a PAIR is present in another table, keep in mind that this pair needs to be part of a key in the source table. Also, say you want another table to have nullable values for that dependency. That is, the key either has to be present in the other table, or null... well the thing is if you're using a pair for your FK dependency, this gets really tricky. In fact, last I checked it's impossible to allow half the FK dependency (if you're using a pair for instance) to be null. That is, one column out of the pair can be null while the other cannot.

Just fun stuff to keep in mind. What do you need foreign keys for in your case? You sound like a newbie, so it's weird that you're worrying about data integrity before you can even query the data properly...

Also in MySQL, the "like" keyword is by default case insensitive. Not sure if it is in what you're using, but you might want to check to make sure.

The reason I'm commenting even though I've only done work in MySQL, Oracle, and Sybase (and some NoSQL flavors) is because essentially most of these relational database systems rely on relational algebra and tuple calculus on some level, and are just different (in terms of strictness and featureset) implementations of roughly the same standards. After you get good with SQL, you can kind of port it around, though you might have to make adjustments depending on DB. Oracle for instance is more strict than MySQL and requires explicit column definitions in any aggregations (unlike MYSQL which just pulls a random value out of the aggregation if you select a column not in the grouping). MySQL also doesn't support exclusions and other fun things like ranks (at least afaik, not natively; you need to use variables and it's a bit weird) and recursive queries.