Learning SQL

**self- hosted**

Congrats!

My formal education started with learning SQL via the certificate program in the computer training wing of a local university. I had been already been using it for years though.  But in that piecemeal way where you know how to do a certain thing that needs to be done, but don't understand the larger context of what you are doing.  If you can find a certificate program, I would highly recommend taking a least the intro SQL classes from that program.

As for SQL in the context of Tessitura, I would highly recommend you reach out to Tom Brown (I can put you in contact with him) for some training. I have used SQL in three other database over a ten year timespan and each time I started using a new database I had to to relearn some things or learn something new (functions, commands, components) that I never needed in the previous databases.

Finally, these forums are my saving grace! Many times my coworkers want something that I cannot whip out as quickly as they would like with my knowledge of the impresario database and I come here for help. 99% of the time I get the help I need, learn something new, and make a new friend. 

Ashley Elliott

Database Administrator

St. Louis Symphony Orchestra

314-286-4198

ashleye@slso.org

I'm by far no SQL master but here are my favorite things with regard to keeping the box office functioning.  I learned these over the last 16 years via help tickets or peers.  You may already know them but here goes.

*  To find the ticket element IDs for print at home designs. ???? = ticket design no

select d.design_no, d.description, f.ele_sl_no, f.description, f.mask
from t_design d, tx_design_element f
where d.design_no = f.design_no
and d.design_no = ????

*  To unlock seats locked (in lavender) on seat maps.   ???? = locked by no

-----You will first need to get the "locked by" number from the
---"database seat no" then run (replace ???? with locked by number)...

use impresario

tp_cleanup_connection ????

*  To unlock ticket orders. ???? = order no

---- first, see if the mir lock no on the order is a number other than zero

select * from t_order where order_no = ????

---- If the mir is other than zero then...

begin tran
update t_order
set mir_lock = 0
from t_order
where mir_lock = (whatever the mir # is) and order_no = ????
commit tran

-- if things don't look right type:

rollback tran

There are many others but these are the ones I use often in the ticketing sphere.  

Heather

Melanie Boma

Over the past few months, I've been offering to friends and colleagues in the Tessitura Community some SQL introductory sessions.  If others would like to take me up on the same offer.  We can start another session in May.  I can give you more details.  Please reach out to my email address tbrown7 @ ithaca.edu .  

Hi Melanie - I was in the same boat as you about 15 years ago! I ended up taking a night school course through a local college to learn SQL. It gave me a good foundation for understanding a database and all the different statements. I would highly recommend some form of formal training rather than doing it on your own. 

Also, something that is invaluable to me is a layout that someone from our org put together when we went live of how all the tables join together. I still use it to this day. 

"Accidental SQL" is such a perfect description... 

I've always found that the piece I was missing from my uni courses were the how all of the data works in the impresario database. The system admin docs help a lot there (Screen Ref/Table Reference)... PORD, PCONT, PBOOK are your friends too. 

I've reviewed a couple of courses from edx which I liked a lot, but they only run them from time to time (Querying with Transact SQL DAT201). Linkedin Learning has a lot... or the Mimo app (duolingo for code, basically) are all good places to start. 

There's another fun tip about BEGIN TRAN... remember you either have to commit or rollback as it will create a lock on the table which prevents other processes from accessing it until you do. If it's a busy/important table *cough* T_CUSTOMER *cough* then no one can use it until you're done... which is something I think we've all learned the hard way at some point. 

Other things I find helpful to keep in mind... everyone is new to SQL at some point, most of us google solutions all the time (if even just to copy/paste syntax), there are multiple -correct- ways to accomplish what you are trying to do, done is better than perfect, if it works-it works... and there are loads of people willing to help.

Are you able to share the layout?  I've been teaching SQL to members of the community, and I'd appreciate being able to share any good tools that can be made available.

Stratford Festival Tessitura ERD mapping diagram.pdf

Here you go! I have this printed out at my desk and refer to it constantly. There is a error on one of the relationships but it's marked on the physical copy at my work office so I don't have it at home to correct the ERD. 

The "Pmap" links need updating for price layers, at least.

Yes, for sure. I have some saved code that I use for those. We've been meaning to update this over the years, but it's one of those things that we never seem to find the time to do.

Lots of great replies here, to which I would only add, anytime you're in the client, and wonder "where exactly does this data live?", re-opening the window with Help -> Log Viewer open can shed some helpful light. 

Wow! Thanks.

I hate creating ERD's - I had to do them in one of the classes I took - but I LOVE using one someone else created, lol

Ashley

Wow! Thank you. As I said above, these forums are a life saver. I doubt I would have ever known this was available to me if you had not posted it here.

Thanks so much!

Ashley

All, here's a CTE for the joins to get back to the olde time-y T_PMAP - in case anyone wants/needs. 

--pmap= 
WITH pmap(pmap_no, price_category)
AS
(
SELECT a.id, c.price_category_id
FROM [dbo].T_PERF_PRICE_TYPE a
JOIN [dbo].T_PERF_PRICE_LAYER b ON a.perf_price_layer = b.id
JOIN [dbo].TR_PRICE_LAYER_TYPE c ON b.price_layer_type = c.id
)

Don't worry about remembering syntax, that can always be looked up, understanding the concepts, where and when to apply them is much more important.

I am very much in the same boat as you. I have this document open any time I am attempting to run any sort of queries: https://www.tessituranetwork.com/en/Files/Docs/SystemAdmin/Tessitura-Table-Structures-V15_1 

I think Heath posted this file from V12 somewhere below, I believe this is the most up-to-date version. 

Would be interested to know if you found a course you liked!

Michelle