Transcript Slide 1
Review exam Review schedule Introduce SQL Class meets in COB computer lab (AB312) on 2/21 1 SQL (structured query language) is a non-procedural language designed to process data. Data processing operations include: ◦ Creating tables ◦ Create and enforcing constraints ◦ Adding, changing, deleting data ◦ Accessing data (either through single rows or aggregation) SQL is an ANSI (American National Standards Institute) standard language 2 A front-end programming tool Does not: ◦ Make pretty output (forms/reports) ◦ Make pretty websites ◦ Make anything pretty for users to look at!! ◦ Do extensive calculations ◦ Do statistical calculations 3 All commands are considered to be “queries” Any command acts upon the database: ◦ creating database objects, ◦ altering (changing) database objects, ◦ adding/deleting/changing data inside a database object, ◦ looking at data inside a database object. Operates with an implied loop; no explicit loops. The programmer has no control over the execution of the loop, so it is important to understand the implied loop. 4 Data Definition Commands (DDL) Data Manipulation Commands (DML) Data Control Commands (DCL) CREATE INSERT GRANT ALTER UPDATE REVOKE DROP DELETE COMMIT TRUNCATE ROLLBACK SELECT SET TRANSACTION 5 SQL statements start with a command, and then include few or many modifiers/extensions for the command. SQL statements are not case sensitive. Can span more than one physical line; it is a free form language. SQL keywords cannot be abbreviated or split across lines. 6 It is a command-based language. 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 TblEmployee PK employeeID lastname billingrate TblTime records PK,FK1 employeeID PK Datetimestarted FK2 Amount contractID Is recorded for TblContract PK contractID datesigned datedue CREATE TABLE tblEmployee (EmployeeID CHAR(5) PRIMARY KEY, LastName VARCHAR(30), BillingRate MONEY); 8