SQL

1. Which SQL clause comes immediately after SELECT?

C. WHERE

List all the records and fields in the Customers table. How many records are there in this table? How many fields?

SELECT * FROM northwinds.customers;

• List all records and fields in the Shippers table.

SELECT * FROM northwinds.shippers;

2. Display the average freight charge for Northwinds orders shipped to the USA and ordered by a customer based in the USA. Include only orders taken by employee number 7.

SELECT AVG(freight) FROM northwinds.orders JOIN northwinds.customers ON customers.customerid = orders.customerid WHERE shipcountry = 'USA' AND country='USA' AND employeeid = 7

2. What is the average reorder level for all of Northwind's ACTIVE (non-discontinued) products?

SELECT AVG(reorderlevel) FROM northwinds.products WHERE discontinued = 0;

4. List the number of respondents and average visual appeal of billing statement version 1 by income range. Order the list in descending order of average visual appeal of billing statement version 1.

SELECT AVG(s1visual), incomerange, COUNT(id) FROM cc4.survey JOIN cc4.inckey ON survey.inc = inckey.inc GROUP BY incomerange ORDER BY AVG(s1visual) DESC

5. What is the average actual gross profit (sales price - cost) that Northwind's realized for all product orders in the Confections category?

SELECT AVG(unitprice-unitcost) FROM northwinds.products JOIN northwinds.categories ON categories.categoryid = products.categoryid WHERE categoryname = 'Confections';

3. What is the average number of units in stock for all of Northwind's products from the supplier named Exotic Liquids?

SELECT AVG(unitsinstock) FROM northwinds.products JOIN northwinds.suppliers ON suppliers.supplierid = products.supplierid WHERE companyname = 'Exotic Liquids';

4. Write a query to show the number and average length of comments made by male respondents to the billing statement survey. Group and order the list by ethnicity (the key, not the text). Include the sex, ethnicity (key and text), the average comment length, and the number of comments in the output.

SELECT COUNT(comments), AVG(LENGTH(comments)), survey.eth, ethnicity, sex FROM cc4.survey JOIN cc4.ethkey ON survey.eth = ethkey.eth WHERE sex = 'Male' GROUP BY survey.eth, ethnicity, sex ORDER BY survey.eth;

2. How many Northwinds employees report to Employee 5, Dr. Steven Buchanan?

SELECT COUNT(employeeid) FROM northwinds.employees WHERE reportsto = '5';

5. Write a query to show the number of respondents and average overall readability of each version of the billing statement, categorized by education level and whether or not the respondent is Hispanic. Include only female respondents in the averages and only include categories where the readability of each version averaged more than 6. Order the list in descending order of the readability of billing statement 2.

SELECT COUNT(id), AVG(s1readability), AVG(s2readability), education, hispanic FROM cc4.survey JOIN cc4.edukey ON survey.edu = edukey.edu WHERE sex = 'Female' GROUP BY education, hispanic HAVING AVG(s1readability) > 6 and AVG(s2readability) > 6 ORDER BY AVG(s2readability) DESC

8. Display the total number of orders and total shipping costs of Northwind's orders placed by a customer located in Germany, France, or Austria and shipped by Speedy Express.

SELECT COUNT(orderid), SUM(freight) FROM northwinds.orders JOIN northwinds.customers ON customers.customerid = orders.customerid JOIN northwinds.shippers ON shippers.shipperid = orders.shipvia WHERE country IN ('Germany', 'France', 'Austria') AND shippers.companyname = 'Speedy Express';

1. How many customers are assigned to a region? How many customers are not assigned to a region? You can determine this with one query. (Hint: Customers not assigned to a region have nothing in the region field.)

SELECT COUNT(region), COUNT(customerid) FROM northwinds.customers;

7. According to Northwind's database, how many suppliers use a Sales Representative as their main point of contact with the team at Northwinds?

SELECT COUNT(supplierid) FROM northwinds.suppliers WHERE contacttitle = 'Sales Representative';

• How many products does Northwinds offer? In one sentence, explain how you determined this.

SELECT DISTINCT productname FROM northwinds.products;

• List all the distinct regions in the Suppliers table.

SELECT DISTINCT region FROM northwinds.suppliers;

• List all the unique cities to which orders were shipped.

SELECT DISTINCT shipcity FROM northwinds.orders;

4. List the total number of characters in comments for each call center survey date which has at least 100 characters in all comments and the comments include 'excellent job' at anywhere in the text regardless of capitalization. Order the results in ascending sequence based on survey date.

SELECT SUM(LENGTH(comments)), surveydate FROM cc2.surveys WHERE LENGTH(comments) >100 AND UPPER(comments) LIKE '%EXCELLENT JOB%' GROUP BY surveydate ORDER BY surveydate ASC;

6. What is the total cost of Northwind's current in stock inventory of discontinued items?

SELECT SUM(unitsinstock * unitcost) FROM northwinds.products WHERE discontinued = 1;

4. What is the total cost of the inventory currently on order from suppliers based in Manchester, UK?

SELECT SUM(unitsonorder * unitcost) FROM northwinds.products JOIN northwinds.suppliers ON products.supplierid = suppliers.supplierid WHERE city = 'Manchester' AND country = 'UK';

2. Write a query that shows the number of call center comments between 10 and 100 characters in length (inclusive) by call center. Order the output in alphabetical order of call center.

SELECT callcenter, COUNT(comments) FROM cc2.surveys WHERE LENGTH(comments) >= 10 AND LENGTH(comments) <= 100 GROUP BY callcenter ORDER BY callcenter

2. In the Cleveland Clinic call centers, agents often talk to senior and elderly people. We want to find out if the agents talk to these customers in an appropriate speed. List the call center, the agent, and comments information for comments including 'speaking rapidly', 'speaking slowly', slow, or 'rapid' anywhere of the text regardless of capitalization.

SELECT callcenter, agent, comments FROM cc2.surveys WHERE UPPER(comments) LIKE '%SPEAKING RAPIDLY%' OR UPPER(comments) LIKE '%SPEAKING SLOWLY%' OR UPPER(comments) LIKE '%SLOW%' OR UPPER(comments) LIKE '%RAPID%';

3. Write a query that shows the customer ID and number of responses for all customers who had more than one response to the call center survey. Show the results in descending order of number of responses.

SELECT cid, COUNT(cid) FROM cc2.surveys GROUP BY cid HAVING COUNT(cid) > 1 ORDER BY COUNT(cid) DESC;

3. From the call center survey data, list the customer IDs, the number of comments, and the average length of comments for each customer who had more than one comment with minimum average length of 300. Order the results in descending sequence by the customer ID.

SELECT cid, COUNT(comments), AVG(LENGTH(comments)) FROM cc2.surveys GROUP BY cid HAVING AVG(LENGTH(comments)) >= 300 AND COUNT(comments) >1 ORDER BY cid DESC;

5. Write a query to list the total revenue (the unit price paid times the quantity ordered) by Northwinds customer. Include only products that have sauce in the product name and customers that have generated more than $1,000 in revenue for such products. Order the list in descending order of total revenue and include customer name in the output.

SELECT companyname, SUM(orderdetails.unitprice * quantity) FROM northwinds.products JOIN northwinds.orderdetails ON products.productid = orderdetails.productid JOIN northwinds.orders ON orderdetails.orderid = orders.orderid JOIN northwinds.customers ON orders.customerid = customers.customerid WHERE UPPER(productname) LIKE '%SAUCE%' GROUP BY companyname HAVING SUM(orderdetails.unitprice * quantity) > 1000 ORDER BY SUM(orderdetails.unitprice * quantity) DESC;

• List the company name and contact name of all of Northwind's suppliers.

SELECT companyname, contactname FROM northwinds.suppliers;

5. For each order placed by a customer located in Portugal, display detailed information including the customer name, order number, order date, product name, quantity ordered, list price, actual price and discount percentage (e.g. 0.06 is 6% discount from list price).

SELECT companyname, orders.orderid, orderdate, productname, quantity, products.unitprice, orderdetails.unitprice, 100 * (products.unitprice - orderdetails.unitprice)/products.unitprice FROM northwinds.products JOIN northwinds.orderdetails ON orderdetails.productid = products.productid JOIN northwinds.orders ON orders.orderid = orderdetails.orderid JOIN northwinds.customers ON orders.customerid = customers.customerid WHERE country = 'Portugal';

6. Using the Northwinds database, write a SQL query that shows which countries' customers (based on their headquarters' customer address, not where the order was shipped) ordered a total of more than $30,000 based on actual price paid and quantity. Only include orders which had zero freight. List the countries in descending order based on the total sales.

SELECT country, sum(orderdetails.unitprice*quantity) FROM northwinds.customers JOIN northwinds.orders ON customers.customerid=orders.customerid JOIN northwinds.orderdetails ON orderdetails.orderid=orders.orderid WHERE freight > 0 GROUP BY country HAVING sum(orderdetails.unitprice*quantity) > 30000

5. For those respondents who preferred billing statement 1, display the number of respondents and the average overall rating of billing statement version 2 by education level and employment status. Show only those education/employment groupings where the number of respondents is at least 10. You should include the description of education level and employment status in the output. Order the output in descending order of overall rating of billing statement 2.

SELECT education, empstatus, COUNT(*), AVG(s2overall) FROM cc4.survey JOIN cc4.edukey ON survey.edu = edukey.edu JOIN cc4.empkey ON survey.emp = empkey.emp WHERE pref = 1 GROUP BY education, empstatus HAVING COUNT(*) >= 10 ORDER BY AVG(s2overall) DESC;

• List the first name, last name and birth date of all Northwinds employees.

SELECT firstname, lastname, birthdate FROM northwinds.employees;

1. List the first name, last name and hire date of all UK-based employees of Northwinds whose title is Sales Representative.

SELECT firstname, lastname, hiredate FROM northwinds.employees WHERE title = 'Sales Representative' AND country = 'UK';

1. Write a query that lists the ID and search term for all web site search terms that contain the word insurance and are greater than 10 characters long.

SELECT id, search FROM cc1.searches WHERE UPPER(search) LIKE '%INSURANCE%' AND LENGTH(search) > 10; SELECT id, search FROM cc1.searches WHERE UPPER(search) LIKE '%INSURANCE%' AND LENGTH(search) > 10

1. List the IDs and the number of web site searches using a search term that includes the word 'insurance' anywhere in the text or 'payment' at the end of the text. (You don't have to convert case for this query.)

SELECT id, search, count FROM cc1.searches WHERE UPPER(search) LIKE '%INSURANCE%' OR UPPER(search) LIKE '%PAYMENT%';

4. List the name of every ACTIVE (non-discontinued) product that has a current stock level below the reorder level and has not been reordered.

SELECT productname FROM northwinds.products WHERE discontinued = 0 AND unitsinstock < reorderlevel AND unitsonorder = 0;

3. What is Northwind's markup per item (retail list price - cost) for each product in the Confections category? List the product name and the markup in the output.

SELECT productname, unitprice - unitcost FROM northwinds.products JOIN northwinds.categories ON categories.categoryid = products.categoryid WHERE categoryname = 'Confections';

1. List the average likelihood to recommend eVisits to a friend or family member grouped by the five different levels of agreement with the question "It was easy to submit information for the Mychart eVisit." Include both the number (the level of agreement) and the text meaning of the response in the output. Order the list by ascending order of level of agreement.

SELECT r2, response, AVG(recommend) FROM cc3.evisits JOIN cc3.scale ON evisits.r2 = scale.rcode GROUP BY r2, response ORDER BY r2 ASC; R2 RESPONSE AVG(RECOMMEND)

2. List the number of respondents who strongly disagreed, disagreed, were neutral, agreed, and strongly agreed with the question "I would use MyChart eVisit again." Show only agreement level categories with more than 100 respondents. You should order the results by response code, and show the response text (strongly disagreed, disagreed, etc.) in the table.

SELECT r5, response, COUNT(r5) FROM cc3.evisits JOIN cc3.scale ON evisits.r5 = scale.rcode GROUP BY r5, response HAVING COUNT(r5) > 100 ORDER BY r5;

2. List the average of the average of the five Likert scale responses (r1 through r5) grouped by each of the possible responses to the question "On a scale of 1-10, (with 1 being the least likely and 10 being the most likely), how likely are you to recommend MyChart eVisits to a friend or family member?" Include only Express Care eVisits in the results.

SELECT recommend, ((AVG(r1) + AVG(r2) + AVG(r3) + AVG(r4) + AVG(r5))/5) FROM cc3.evisits JOIN cc3.types ON evisits.type = types.type JOIN cc3.scale ON evisits.r1 = scale.rcode WHERE description = 'Express Care' GROUP BY recommend ORDER BY recommend ASC

3. List the number of respondents by date who believed their healthcare concern was resolved, along with their average likelihood to recommend eVisits to a friend or family member. Show only dates where there were at least 5 eVisits by respondents who believed their healthcare concern was resolved, and list them in chronological order.

SELECT resolved, AVG(recommend), startdate, COUNT(id) FROM cc3.evisits WHERE resolved = 'Yes' GROUP BY resolved, startdate HAVING COUNT(startdate) >= 5 ORDER BY startdate ASC;

1. List all web site search terms that include the words 'visit' or 'appointment', and also include the words 'foot' or 'toe'. The letters in the words can be either upper or lower case.

SELECT search FROM CC1.searches WHERE (UPPER(search) LIKE '%TOE%' OR UPPER(search) LIKE '%FOOT%') AND (UPPER(search) LIKE '%VISIT%' OR UPPER(search) LIKE '%APPOINTMENT%');

4. List the average visual appeal rating for billing statement versions 1 and 2 by sex, along with the average of the difference between the two ratings. You should show the sex and the number of respondents of each sex in the output.

SELECT sex, COUNT(*), AVG(s1visual), AVG(s2visual), AVG(s1visual - s2visual) FROM cc4.survey GROUP BY sex;

5. Write a SQL query that shows the total number of characters in all comments by date and evisit type description. Include only date/evisit type combinations that had more than 3 respondents. Order the list in chronological order.

SELECT startdate, description, SUM(LENGTH(comments)) FROM CC3.evisits JOIN CC3.types ON types.type = evisits.type GROUP BY startdate, description HAVING COUNT(comments) > 3 ORDER BY startdate;

4. Write a SQL query that lists the average difference in visual appeal evaluation between billing statement version 1 and billing statement version 2. Include only respondents who preferred billing statement version 1. Group the results by income rage and sex and include the number of respondents along with the average. Order the results by the numeric value of the income range (the key) and sex, but also show the text of the income range in the output.

SELECT survey.inc, incomerange, sex, AVG(s1visual - s2visual), COUNT(s1visual) FROM cc4.survey JOIN cc4.inckey ON survey.inc = inckey.inc WHERE pref = 1 GROUP BY survey.inc, incomerange, sex ORDER BY survey.inc, sex;

3. List the number of respondents who rated their likelihood to recommend MyChart eVisits to a friend or family member as a 10. Group your results based on their response to the question: "Do you feel your MyChart eVisit saved you time compared to an in-office visit?"

SELECT timesaved, COUNT(recommend) FROM cc3.evisits WHERE recommend = 10 GROUP BY timesaved;

3. Write a query grouped by the respondents' assessment of if they time saved by eVisits that shows the number of respondents who gave a reason for their assessment that was at least 15 characters in length and contained the words wait, time, or waste.

SELECT timesaved, COUNT(tsexp) FROM cc3.evisits WHERE LENGTH(tsexp) >= 15 AND (UPPER(tsexp) LIKE '%WAIT%' OR UPPER(tsexp) LIKE '%TIME%' OR UPPER(tsexp) LIKE '%WASTE%') GROUP BY timesaved;

1. List the average level of agreement with the question "It was easy to submit information for the MyChart eVisit." for respondents of each eVisit type code (you don't need to show the description, just the number).

SELECT type, AVG(r2) FROM cc3.evisits GROUP BY type;

14. When multiple conditions are used in a WHERE or HAVING clause they are separated by: a. AND or OR, depending on the test conditions b. , (commas) c. . (periods) d. && or ||, depending on the test conditions

a. AND or OR, depending on the test conditions

7. The DISTINCT command in SQL will: a. Limit the output to one unique occurrence of each value in a field b. Only display multiple values of a field that are identical c. Only display single values of a field that have no matching values d. None of the above

a. Limit the output to one unique occurrence of each value in a field

12. Records in the output display of an SQL query are often referred to as: a. Rows b. Columns c. Fields d. Conditions

a. Rows

18. Field discontinued in the table Northwinds.products contains a 1 if the product has been discontinued. Which of the following queries would show the number of discontinued products divided by the total number of products? a. SELECT SUM(discontinued)/COUNT(discontinued) FROM Northwinds.products b. SELECT COUNT(discontinued)/COUNT(*) FROM Northwinds.products c. SELECT COUNT(discontinued)/COUNT(productid) FROM Northwinds.products d. SELECT AVG(discontinued)/AVG(productid) FROM Northwinds.products WHERE discontinued = 1

a. SELECT SUM(discontinued)/COUNT(discontinued) FROM Northwinds.products

4. Which table allows you to reference each billing statement survey respondent with the description of their current employment status when it is included in the FROM or JOIN? a. cc4.empkey b. cc4.empstatus c. cc4.ccempkey d. cc4.description

a. cc4.empkey

17. The error message "invalid identifier" means a. Unaggregated and aggregated fields were called without proper grouping. b. A field or table name was misspelled. c. There is an improper equation or comparison. d. The query includes a field with the same name as a field in another joined table

b. A field or table name was misspelled.

2. Functions such as SUM, AVG, and COUNT are formally called _________________ functions. a. Group b. Aggregate c. Math d. Internal

b. Aggregate

11. cc2 is the name of a ______________. a. Field b. Database c. Table d. Record

b. Database

3. In the Northwinds table named OrderDetails, the fields unitprice and quantity contain the actual price that the customer paid for items related to a specific customer order. If you want to write a query to summarize the total extended price (unitprice * quantity) by customer order, which of the following clauses would be included in the query? a. SELECT orderdetails.productid, SUM(unitprice * quantity) b. GROUP BY orderdetails.orderid c. WHERE SUM(orderdetails.quantity * orderdetails.unitprice) > 0 d. HAVING orderdetails.orderid > 0

b. GROUP BY orderdetails.orderid

20. The SQL command used to determine the number of characters in the text field comments is: a. COUNT(comments) b. LENGTH(comments) c. UPPER(comments) d. LEN(comments)

b. LENGTH(comments)

8. SQL is an acronym for: a. Standard Questioning Language b. Structured Query Language c. Simple Query Language d. Structured Questioning Language

b. Structured Query Language

1. The primary key field for Northwinds.Suppliers is a. CompanyName b. SupplierID c. SupplierName d. CompanyID

b. SupplierID

5. Including the table name with the field name (e.g. using products.productid instead of just productid) is necessary when: a. The field is a primary key field b. The field name appears in more than one table that is part of the query c. The field is in a JOIN clause after ON d. The field is in a WHERE clause

b. The field name appears in more than one table that is part of the query

14. The following WHERE clause can be used to return records related to customers of Northwinds located in Portugal, Germany, or the UK: WHERE country IN ('Portugal', 'Germany', 'UK'). Which of the following clauses produces the same result? a. WHERE country = 'Portugal' AND country = 'Germany' AND country = 'UK' b. WHERE country = 'Portugal' OR country = 'Germany' OR country = 'UK' c. WHERE country = 'Portugal' OR 'Germany' OR 'UK' d. WHERE country = 'Portugal' AND 'Germany' AND 'UK'

b. WHERE country = 'Portugal' OR country = 'Germany' OR country = 'UK'

16. The difference between WHERE and HAVING is: a. WHERE will always decrease the number of rows returned and HAVING can decrease or increase the number of rows returned. b. WHERE makes selections on data in the tables and HAVING makes selections on groups of records. c. WHERE can only be used with one table and HAVING is used with multiple tables. d. WHERE requires the selection field to be in the SELECT while HAVING requires the selection field to be in the GROUP BY.

b. WHERE makes selections on data in the tables and HAVING makes selections on groups of records.

10. When multiple fields are included in a SELECT or GROUP BY clause they are separated by: a. AND b. OR c. , (commas) d. . (periods)

c. , (commas)

20. Which of the following contains tables? a. A record b. A column c. A relational database d. A field

c. A relational database

6. Which element or elements in SQL statements are case sensitive? a. Command keywords such as SELECT, FROM, LIKE b. Field and table names c. Data contained in fields or within single quotes d. All of the above are case-sensitive

c. Data contained in fields or within single quotes

5. If my SQL query starts with SELECT sex, AVG(s1understand), AVG(s2understand) then it must also include: a. GROUP BY AVG(s1understand), AVG(s2understand) b. GROUP BY sex, AVG(s1understand), AVG(s2understand) c. GROUP BY sex d. ORDER BY AVG(s1understand)

c. GROUP BY sex

17. Adding an additional table to a query will always make the number of rows: a. Increase or stay the same. b. Decrease or stay the same. c. Increase, decrease, or stay the same, depending on the table added. d. Increase or decrease, depending on the GROUP BY clause.

c. Increase, decrease, or stay the same, depending on the table added.

9. The SQL command WHERE does which of the following? a. Indicates the tables to be used in the query b. Lists the names of the fields to be displayed on the screen c. Limits the records to only those meeting the test condition(s) d. All of the above

c. Limits the records to only those meeting the test condition(s)

4. The DISTINCT command: a. Is used to compare fields that have similar values b. Aggregates results based on DISTINCT fields c. Results in unique values of SELECT fields d. Separates fields based on the WHERE conditions

c. Results in unique values of SELECT fields

11. Which of these would give the total number of sample searches that are included in cc1.searches? a. COUNT(search) b. COUNT(nbhits) c. SUM(count) d. SUM(nbhits)

c. SUM(count)

13. SELECT DISTINCT id, where id is a primary key field will return a. Fewer records than SELECT * b. More records than SELECT * c. The same number of records as SELECT id d. The same number of records as COUNT(*)

c. The same number of records as SELECT id

8. The error message "invalid relational operator" means a. Unaggregated and aggregated fields were called without proper grouping. b. A field or table name was misspelled. c. There is an improper equation or comparison. d. Two fields with the same name exist in the current table.

c. There is an improper equation or comparison.

7. Which of the following queries will return the all the sample Cleveland Clinic website searches that contained 'CT' (as in CT scan) whether the user entered 'CT' or 'ct' or 'Ct' or 'cT'? a. WHERE UPPER(search) = 'CT' b. WHERE UPPER(search) LIKE 'CT' c. WHERE UPPER(search) LIKE '%CT%' d. WHERE search LIKE UPPER('%CT%')

c. WHERE UPPER(search) LIKE '%CT%'

9. The table that provides the description of the type of eVisit an eVisit survey respondent had is: a. cc3.evisits b. cc3.scale c. cc3.types d. None of the above

c. cc3.types

3. Which table allows you to reference ethnicities by their name in CC4 when you include it in the FROM or JOIN clause? a. hispanic b. survey c. ethkey d. ethnicity

c. ethkey

19. Which of the following is true of a primary key? a. It uniquely identifies each record in a table b. It can never be null c. This field can appear in related tables as a foreign key d. All of the above

d. All of the above

18. rcode a. Is the primary key of the cc3.scale table b. Will be included after the ON when joining the tables cc3.scale and cc3.evisits c. Cannot be null d. All of the above.

d. All of the above.

19. Aggregate functions: a. Always perform arithmetic operations on fields. b. Require fields to be grouped. c. Can only be applied to numeric fields. d. Always return a single numerical value.

d. Always return a single numerical value.

10. Why will these two queries return different averages? SELECT AVG(unitprice - unitcost) FROM northwinds.products SELECT AVG(orderdetails.unitprice - unitcost) FROM northwinds.orderdetails JOIN northwinds.products on products.productid = orderdetails.productid a. They will not return different averages b. Neither will return an average because a GROUP BY is missing c. Because products contains actual sales prices and orderdetails contains retail prices d. Because orderdetails contains actual sales prices and products contains retail prices

d. Because orderdetails contains actual sales prices and products contains retail prices

15. In the output of an SQL query, (null) means that: a. The field is equal to 0 b. The field is equal to 'O' c. The field is equal to ' ' d. None of the above

d. None of the above

2. If a query against database CC3 includes the clause, WHERE description = 'Express Care', which of the following is true? a. description must be included in the SELECT b. cc3.types must be included in the FROM c. GROUP BY description must also be included d. None of these *Note that cc3.types could have been included in the JOIN instead of FROM

d. None of these *Note that cc3.types could have been included in the JOIN instead of FROM

6. When present, the ___________________clause should be the final clause in an SQL query. a. GROUP BY b. WHERE c. HAVING d. ORDER BY

d. ORDER BY

12. The error message "column ambiguously defined" means: a. Unaggregated and aggregated fields were used without proper grouping b. A field or table name was misspelled c. There is an improper equation or comparison d. The query includes a field with the same name as a field in another joined table

d. The query includes a field with the same name as a field in another joined table