1 / 16

Agenda for Class 9/26/2013

Agenda for Class 9/26/2013. Introduce Microsoft’s SQL Server database management system. Use the lab to discuss how to CREATE, DROP and populate (INSERT) tables. Introduce SELECT statement .

abra-porter
Download Presentation

Agenda for Class 9/26/2013

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Agenda for Class 9/26/2013 • Introduce Microsoft’s SQL Server database management system. • Use the lab to discuss how to CREATE, DROP and populate (INSERT) tables. • Introduce SELECT statement. • We will review the basic concepts together and create 1 table as a class. Then you will finish the rest of the lab at your own speed during class.

  2. Accessing SQL Server • Login to COB server (use your netID). • Accessing from COB labs: • Execute Microsoft SQL Server Management Studio. • Accessing from home: • Execute Remote Desktop Connection. • Login to server called sts.coba.unr.edu • Use your netID as the User name and password. • When not in the COB labs, your netID for Windows authentication is UNR\netID. • Execute Microsoft SQL Server Management Studio.

  3. Logging into the class server for SQL Server • After executing SQL Server Management Studio, either directly when in the COB labs, or through remote desktop when not in the COB labs, you must login to our class server. • Server name is ISSQL\Students • Use Windows authentication for all work on SQL Server.

  4. SQL Server Management Studio • Object Explorer • New Query editor • Options • File Tabs • Difference between database object and SQL code • Accessing files from the k: or u: drives • Saving SQL code to/from a separate file

  5. Guidelines for writing SQL • SQL statements start with a command, and then include few or many modifiers/extensions for the command. • SQL statements are not case sensitive. • The data stored in a data base ARE case sensitive. • SQL statements can span more than one physical line; it is a free form language. • SQL keywords cannot be abbreviated or split across lines.

  6. Much tradition in SQL code • Keywords and/or main clauses are typically placed on separate lines. • Tabs and indentation are used to enhance readability. • Keywords are typically aligned in the first column. • Keywords are usually capitalized. • Data are usually in lowercase or a combination of uppercase and lowercase. • Comments are included sparingly, but usefully.

  7. Class Exercise Database Referential integrity: Table constraint. When inserting data in the child table, checks to see whether a related row exists in the parent table.

  8. Let’s create a small table • Click on the “new query” button. This is “task #1” on the exercise document. • Type the following: CREATE TABLE tblVendor (VendorID char(4) primary key, VendortNamevarchar(30), FirstBuyDatedatetime); • Click on the “Execute” button.

  9. Deleting a table • Cannot have more than one data object with the same name. • Must delete data objects before re-creating them. • SQL Statement is: DROP TABLE tblVendor; • Let’s try and drop the table named tblVendor. Keep the current query tab open, and open another “new query” tab. Drop the table. This is task #2 on the exercise. • General information: Must delete objects in the order of referential integrity constraints.

  10. Create a named constraint • Constraints can be “named” in SQL for easier future reference. This is task #3 on the exercise. CREATE TABLE tblVendor (VendorID char(4), VendorNamevarchar(30), FirstBuyDatedatetime CONSTRAINT pkVendor PRIMARY KEY (vendorID)); • Constraint names may be referenced in the future if they need to be changed (altered). • Easier to reference a name that you create, rather than one that SQL Server creates for you.

  11. SQL INSERT Statement • Used to “populate” a table with data. • Used to enter one row of data. • Character data and dates must be entered surrounded by single quotes. • Dates can be entered using a variety of formats: • ‘26-sep-2013’ • ‘09/26/2013’ • ‘09-26-2013’

  12. Let’s put data into that table • Click on the “new query” button. • Type the SQL statements on Task #4 on the exercise document. Each command will produce one row in the table called “tblVendor”.

  13. Let’s look at the data • Click on the “new query” button. • The SQL statement below is task #5 on the exercise document. SELECT * FROM tblVendor;

  14. Examples of Retrieving Data from a Table SELECT * FROM tblVendor; SELECT VendorID, VendorName FROM tblVendor WHERE VendorID= ‘0062’; The * means retrieve all columns. The FROM statement, without a WHERE statement, means retrieve all rows.

  15. Now create the other three tables in the sample ERD Do tasks #6 through #10 on the exercise document.

  16. Sharing tables with your team • You are the owner of your database. • You have the right to GRANT access to your database. • There is a handout on the class website discussing how to change permissions, and we will discuss it next class. • Best practice for right now: • Create and populate the tables in each group member’s database. The data for HW#5-8 is relatively static, so it will lessen the complexity of the assignments if everyone has direct access to the tables. • Each class member should get experience creating and populating tables in his/her own database, even when working as part of a team.

More Related