MIT1_204S10_lec03

MIT1_204S10_lec03 - 1.204 Lecture 3 SQL Basics Joins SQL...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1 1.204 Lecture 3 SQL: Basics, Joins SQL Structured query language (SQL) used for Data d th e b finition (DDL): tables and views (virtual tables). These are the basi ti t t d t d l t ic operations to convert a data model to a database Data manipulation (DML): user or program can INSERT, DELETE, UPDATE or retrieve (SELECT) data. Data integrity: referential integrity and transactions. Enforces keys. Access control: security Data sharing: by concurrent users Not Not a complete a complete language like Java language like Java SQL is sub-language of about 30 statements Nonprocedural language No branching or iteration Declare the desired result, and SQL provides it
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 SQL SELECT SELECT constructed of clauses to get columns and rows from one or more SELECT constructed of clauses to get tables or views. Clauses must be in order: SELECT columns/attributes INTO new table FROM table or view WHERE specific rows WHERE specific rows or or a join is created join is created GROUP BY grouping conditions (columns) HAVING group-property (specific rows) ORDER BY ordering criterion ASC | DESC Orders OrderNbr Cust Prod Qty Amt Disc 1 211 Bulldozer 7 $31,000.00 0.2 2 522 Riveter 2 $4,000.00 0.3 3 522 Crane 1 $500,000.00 0.4 CustNbr Company CustRep CreditLimit 211 Connor Co 89 $50,000.00 522 AmaratungaEnterprise 89 $40,000.00 890 Feni Fabricators 53 $1,000,000.00 RepNbr Name RepOffice Quota Sales 53 Bill Smith 1 $100 000 00 $0 00 Customers SalesReps $100,000.00 $0.00 89 Jen Jones 2 $50,000.00 $130,000.00 Offices p OfficeNbr City State Region Target Sales Phone 1 Denver CO West $3,000,000.00 $130,000.00 970.586.3341 2 New York NY East $200,000.00 $300,000.00 212.942.5574 57 Dallas TX West $0.00 $0.00 214.781.5342 Example tables
Background image of page 2
3 Example schema +OrderID Using SQL Server and Management Studio Express Your SQ when y Qg L Server database engine should start by default our system starts y Ask TA for help if needed at office hours Start Management Studio Express (MSE) from Start- >Programs->SQL Server 2008 Open Lecture3CreateDB.sql with MSE in Windows Explorer Download the .sql file from Stellar and double-click on it Select Select ‘Execute from toolbar Execute from toolbar Database MIT1204 will be created and data inserted for examples during this class Open Lecture3Examples.sql for all SQL code in this lecture Experiment with it
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 SQL queries: SELECT Click Click ‘New New Query Query’ in
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/04/2011 for the course ESD 1.204 taught by Professor Georgekocur during the Spring '10 term at MIT.

Page1 / 11

MIT1_204S10_lec03 - 1.204 Lecture 3 SQL Basics Joins SQL...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online