1 Entity Attributes Entity represents a person thing
1 Entity / Attributes • Entity represents a person / thing • Entity represents an Access table • Attributes describe facets of an entity • Attributes represents columns in an Access table CLIENT *Client. ID Client. Name Client. Services Client. City Client. State Client. Revenue
2 Identifiers / Primary Keys • Every entity must be uniquely identified (via a primary key) • Asterisk represents the primary key • A primary key can be a single attribute or a combination of attributes • Unique attributes can serve as the primary key (Client. Name) • A primary key can be created (ID) in situations where there are unique attributes CLIENT *Client. ID Client. Name Client. Services Client. City Client. State Client. Revenue
3 Example: Client Table Client. ID Client Name Client Services Client City Client State Client Revenue 1 BK Associates Commercial Portland Oregon $210, 000. 00 2 Blaloc Industrial Kansas City Missouri $330, 000. 00 3 Bankton Electric Government New York $210, 000. 00 4 Bick Industrial Raleigh North Carolina $550, 000. 00 5 TX Electric Government Houston Texas $160, 000. 00 6 Crow Commercial Dallas Texas $270, 000. 00 7 GRB Industrial Atlanta Georgia $180, 000. 00 8 H&P Industrial Denver Colorado $90, 000. 00 9 LB&B Industrial Boston Massachusetts $211, 000. 00 10 Congro Industrial Atlanta Georgia $122, 000. 00 11 Moss Enterprises Commercial Phoenix Arizona $33, 000. 00 12 Ruby Industrial San Antonio Texas $344, 000. 00 13 Silver Industries Industrial Omaha Nebraska $218, 000. 00 14 TPH Commercial Annaheim California $166, 000. 00
4 Querying: Client Table To show all of the data in the Client table. SELECT * FROM Client; Client. ID Client Name Client Services Client City Client State Client Revenue 1 BK Associates Commercial Portland Oregon $210, 000. 00 2 Blaloc Industrial Kansas City Missouri $330, 000. 00 3 Bankton Electric Government New York $210, 000. 00 4 Bick Industrial Raleigh North Carolina $550, 000. 00 5 TX Electric Government Houston Texas $160, 000. 00 6 Crow Commercial Dallas Texas $270, 000. 00 7 GRB Industrial Atlanta Georgia $180, 000. 00 8 H&P Industrial Denver Colorado $90, 000. 00 9 LB&B Industrial Boston Massachusetts $211, 000. 00 10 Congro Industrial Atlanta Georgia $122, 000. 00 11 Moss Enterprises Commercial Phoenix Arizona $33, 000. 00 12 Ruby Industrial San Antonio Texas $344, 000. 00 13 Silver Industries Industrial Omaha Nebraska $218, 000. 00 14 TPH Commercial Annaheim California $166, 000. 00
5 Selecting Specific Columns (vertical cut) Client. ID Client Name Client Services Client City Client State Client Revenue 1 BK Associates Commercial Portland Oregon $210, 000. 00 2 Blaloc Industrial Kansas City Missouri $330, 000. 00 3 Bankton Electric Government New York $210, 000. 00 4 Bick Industrial Raleigh North Carolina $550, 000. 00 5 TX Electric Government Houston Texas $160, 000. 00 6 Crow Commercial Dallas Texas $270, 000. 00 7 GRB Industrial Atlanta Georgia $180, 000. 00 8 H&P Industrial Denver Colorado $90, 000. 00 9 LB&B Industrial Boston Massachusetts $211, 000. 00 10 Congro Industrial Atlanta Georgia $122, 000. 00 11 Moss Enterprises Commercial Phoenix Arizona $33, 000. 00 12 Ruby Industrial San Antonio Texas $344, 000. 00 13 Silver Industries Industrial Omaha Nebraska $218, 000. 00 14 TPH Commercial Annaheim California $166, 000. 00
6 Selecting Specific Columns (vertical cut) Report a Client’s name, services, and revenues. Display order based on query (i. e. in order of the comma separators) SELECT Client. Name, Client. Services, Client. Revenue FROM Client; Client Name Client Services Client Revenue BK Associates Commercial $210, 000. 00 Blaloc Industrial $330, 000. 00 Bankton Electric Government $210, 000. 00 Bick Industrial $550, 000. 00 TX Electric Government $160, 000. 00 Crow Commercial $270, 000. 00 GRB Industrial $180, 000. 00 H&P Industrial $90, 000. 00 LB&B Industrial $211, 000. 00 Congro Industrial $122, 000. 00 Moss Enterprises Commerical $33, 000. 00 Ruby Industrial $344, 000. 00 Silver Industries Industrial $218, 000. 00 TPH Commercial $166, 000. 00
7 Selecting Specific Rows (horizontal cut) Client. ID Client Name Client Services Client City Client State Client Revenue 1 BK Associates Commercial Portland Oregon $210, 000. 00 2 Blaloc Industrial Kansas City Missouri $330, 000. 00 3 Bankton Electric Government New York $210, 000. 00 4 Bick Industrial Raleigh North Carolina $550, 000. 00 5 TX Electric Government Houston Texas $160, 000. 00 6 Crow Commercial Dallas Texas $270, 000. 00 7 GRB Industrial Atlanta Georgia $180, 000. 00 8 H&P Industrial Denver Colorado $90, 000. 00 9 LB&B Industrial Boston Massachusetts $211, 000. 00 10 Congro Industrial Atlanta Georgia $122, 000. 00 11 Moss Enterprises Commercial Phoenix Arizona $33, 000. 00 12 Ruby Industrial San Antonio Texas $344, 000. 00 13 Silver Industries Industrial Omaha Nebraska $218, 000. 00 14 TPH Commercial Annaheim California $166, 000. 00
8 Selecting Specific Rows (horizontal cut) Show all client data for client that contribute revenue less than $150, 000. SELECT * FROM Client WHERE Client. Revenue < 150000; Client. ID Client Name Client Services Client City Client State Client Revenue 8 H&P Industrial Denver Colorado $90, 000. 00 10 Congro Industrial Atlanta Georgia $122, 000. 00 11 Moss Enterprises Commercial Phoenix Arizona $33, 000. 00
9 Selecting Specific Data Within Columns & Rows Client. ID Client Name Client Services Client City Client State Client Revenue 1 BK Associates Commercial Portland Oregon $210, 000. 00 2 Blaloc Industrial Kansas City Missouri $330, 000. 00 3 Bankton Electric Government New York $210, 000. 00 4 Bick Industrial Raleigh North Carolina $550, 000. 00 5 TX Electric Government Houston Texas $160, 000. 00 6 Crow Commercial Dallas Texas $270, 000. 00 7 GRB Industrial Atlanta Georgia $180, 000. 00 8 H&P Industrial Denver Colorado $90, 000. 00 9 LB&B Industrial Boston Massachusetts $211, 000. 00 10 Congro Industrial Atlanta Georgia $122, 000. 00 11 Moss Enterprises Commercial Phoenix Arizona $33, 000. 00 12 Ruby Industrial San Antonio Texas $344, 000. 00 13 Silver Industries Industrial Omaha Nebraska $218, 000. 00 14 TPH Commercial Annaheim California $166, 000. 00
10 Selecting Specific Columns & Rows Display the Client’s name and revenues where revenue is greater than or equal to $270, 000. SELECT Client. Name, Client. Revenue from Client Where Client. Revenue >= 270000; Client. Name Client. Revenue Blaloc $330, 000. 00 Bick $550, 000. 00 Crow $270, 000. 00 Ruby $344, 000. 00
11 Selecting Specific Cases List client id, name and state who are based in either Texas or Arizona. SELECT client. ID, client. Name, client. State FROM Client where client. State = 'Texas' or Client. State = 'Arizona'; also SELECT client. ID, client. Name, client. State FROM Client where client. State in ('Texas', 'Arizona'); client. ID client. Name client. State 5 TX Electric Texas 6 Crow Texas 11 Moss Enterprises Arizona 12 Ruby Texas
12 Excluding Specific Cases List client services and name for clients that provide services that are not Industrial. SELECT Client. Services, Client. Name from Client where Client. Services <> 'Industrial'; also SELECT Client. Services, Client. Name from Client where Client. Services Not In ('Industrial'); Client. Services Client. Name Commercial BK Associates Government Bankton Electric Government TX Electric Commercial Crow Commercial Moss Enterprises Commercial TPH
13 Displaying Output in Order - Columns SELECT Clientname, Client. State from Client where Client. State = 'North Carolina'; Client Name Client. State Bick North Carolina SELECT Client. State, Clientname from Client where Client. State = 'North Carolina'; Client. State Clientname North Carolina Bick
14 Displaying Output in Order - Rows Display Client. Name, Client. City, Client. Revenue firms where revenues are greater than $180, 000 in order of descending revenue. Where revenues are equivalent, list the Client firms in alphabetical order (based on their city). SELECT Client. Name, Client. City, Client. Revenue FROM Client where Client. Revenue > 180000 order by Client. Revenue desc, Client. City; Client. Name Client. City Client. Revenue Bick Raleigh $550, 000. 00 Ruby San Antonio $344, 000. 00 Blaloc Kansas City $330, 000. 00 Crow Dallas $270, 000. 00 Silver Industries Omaha $218, 000. 00 LB&B Boston $211, 000. 00 Bankton Electric New York $210, 000. 00 BK Associates Portland $210, 000. 00
15 SQL Functions and Calculations • COUNT, AVG, SUM, MIN, and MAX Find the average Client Revenue SELECT Avg(Client. Revenue) as Average. Revenue FROM Client; Average. Revenue $221, 000. 00 Display the Client name, state, and revenue (in thousands) for Client from Texas SELECT Client. Name, Client. State, Client. Revenue/1000 as Thousand. Dollars FROM Client Where Client. State = 'Texas'; Client. Name Client. State Thousand. Dollars TX Electric Texas 160 Crow Texas 270 Ruby Texas 344
16 Subqueries • Subquery = A query within a query Report all Client names and revenue for those firms with revenues that exceed the average revenue for the group as a whole. SELECT Client. Name, Client. Revenue From Client Where Client. Revenue > (SELECT AVG(Client. Revenue) From Client); Client. Name Client. Revenue Blaloc $330, 000. 00 Bick $550, 000. 00 Crow $270, 000. 00 Ruby $344, 000. 00
17 Like / Not Like List all Clients with a name starting with ‘B’. SELECT Clientname FROM Client Where Client. Name Like 'B*'; Clientname BK Associates Blaloc Bankton Electric Bick List all Clients have do not have either an ‘S” or ‘B’ in their name SELECT Clientname FROM Client Where Client. Name Not Like '*B*' and Client. Name Not Like '*S*'; Clientname TX Electric Crow H&P Congro TPH
18 DISTINCT Find the number of different states represented by the Client base SELECT Distinct Client. State as 'Client Locations' FROM Client; 'Client Locations' Arizona California Colorado Georgia Massachusetts Missouri Nebraska New York North Carolina Oregon Texas
- Slides: 18