Advanced SQL Greenhouse Database Tutorial with Microsoft Enterprise Consortium
Learn how to work with the Greenhouse Database using SQL Server Management Studio provided by Microsoft Enterprise Consortium. Follow step-by-step instructions, diagrams, and row count queries to manage the database effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Microsoft Enterprise Consortium Advanced SQL Greenhouse Database Introduction Saving output in SQL Server Management Studio Microsoft Enterprise Consortium: http://enterprise.waltoncollege.uark.edu Microsoft Faculty Connection/Faculty Resource Center http://www.facultyresourcecenter.com 1 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium What you ll need Log in to MEC for this lesson and into MSSMS (Microsoft SQL Server Management Studio). Be sure to select your account ID under Database in the Object Explorer pane, similar to the example shown here. Select the Greenhouse database. (Note: The database name might change over time. Look for MEC_greenhouse or UARK_greenhouse.) 2 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Greenhouse Database Below is the greenhouse data model. Amend_Code = AmendCode bay_bed CropPlantingID CropPlantingID CropVarID Crop 3 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Greenhouse SSMS Diagram SQL Server Management Studio Diagram Copy to Clipboard and paste in to a Word file. 4 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Greenhouse Database - How many rows in a table? /* Get a count of the records in each table. */ select count(*) as "tblCrop Rows" from tblCrop; select count(*) as "tblCropVariety Rows" from tblCropVariety; select count(*) as "tblBay_Bed Rows" from tblBay_Bed; select count(*) as "tblCropPlanting Rows" from tblCropPlanting; select count(*) as "tblAmendment Rows" from tblAmendment; select count(*) as "tblCropPlantingAmend Rows" from tblCropPlantingAmend; select count(*) as "tblCropHarvest Rows" from tblCropHarvest; 5 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Greenhouse Database - List contents of small tables. /* List contents of smaller tables. Save output to a file. */ 6 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Greenhouse Database Crops are planted and harvested. Area, Zone, Sector, Bay-Bed Area = Greenhouse Zone = North Seed Zone = South Seed Sector = A Bay= 01 Bay= 02 A1 A2 A3 A1 A2 A3 Sector = B Bay= 01 B1 B2 B3 7 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Greenhouse Database - View some crop planting data. /* Example SQL: Show how many crops have been planted in bay-bed 01A1. */ select count(*) as "01A1 Crop Count" from tblCropPlanting where bay_bed = '01A1'; /* Show which crops have been planted in bay-bed 01A1. */ select tblCropPlanting.cropVarID, crop, variety, date_planted from tblCropPlanting join tblCropVariety on tblCropPlanting.cropVarID = tblCropVariety.cropVarID where bay_bed = '01A1' order by crop, variety, date_planted; 8 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium What was covered An overview of the greenhouse database. How to save SQL output to a file in SQL Server Management Studio. 9 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
Microsoft Enterprise Consortium Microsoft Enterprise Consortium Resources http://enterprise.waltoncollege.uark.edu/mec.asp Microsoft Faculty Connection Faculty Resource Center http://www.facultyresourcecenter.com/ Microsoft Transact-SQL Reference http://msdn.microsoft.com/en-us/library/aa299742(v=SQL.80).aspx AdventureWorks Sample Database http://msdn.microsoft.com/en-us/library/ms124659%28v=sql.100%29.aspx 10 Prepared by Jennifer Kreie, New Mexico State University Hosted by the University of Arkansas
