Transcript SQL
What makes SQL so popular? • Worldwide language of choice for DBMS • Ease of use / English keyword-oriented • Allows working with data to be logical without having to worry about the details of retrieval or manipulation. Limited statements but powerful. • Portability – Compatible with other database managers. Why SQL… • Powerful language for Set-At-A-Time processing -- processes sets of data as groups rather than as individual elements. • Excellent tool for application development • SQL is NOT an end user query product (commands are simple but complex) • Commonly used in HLL applications. (Embedded SQL) SQL Tasks • Querying Data • Inserting, updating, deleting rows in tables. • Creating, replacing altering and dropping objects. • Controlling access to the database and it’s objects. • Guaranteeing database consistency and integrity. SQL is governed by the ANSI standards committee. ANSI – http://www.ansi.org/ ALL major DBMS comply with ANSI standards. However each version provides it’s own set of extensions that enhance the functionality of the language. As a programmer be aware that the use of the extensions makes the code less portable. (T-SQL, PL-SQL) Four Types of SQL Statements • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • Extensions of SQL, based on the DBMS. DDL-Used to create and management database objects. • CREATE – – – – – – Table View Trigger Procedure Indexes User Defined Functions • DROP • ALTER DML- Most commonly used component by database developers. • • • • SELECT INSERT UPDATE DELETE DCL – Used to manage security in databases. • GRANT – Grant access to an object or a statement. • DENY – Used to deny any permission on an object or statement. • REVOKE – Removes any entry in the permissions table (sys-permissions) that GRANT or DENY use. SQL Keywords Statements • • • • • • • • SELECT FROM DISTINCT WHERE HAVING ORDER BY GROUP BY Aggregate Functions – COUNT, MAX, MIN, AVG, SUM Control of Flow Statements • • • • • • • IF … ELSE RETURN WAITFOR WHILE BREAK CONTINUE GOTO Programming Conventions • Use uppercase characters for all keywords • Capitalize all table names and objects • Use lowercase characters for all column names and variables • Include comments!! • Keep names unique – Tables, Views, Stored Procedures etc. To CREATE a table called Test_table We key in: CREATE TABLE Test_table ( test_id SMALLINT, test_description VARCHAR(25) ); Data Types String Data Types: CHAR (1-255) NCHAR, NVARCHAR TEXT Numeric Data types: BIT (0,1) DECIMAL (length varies) INT (Integer, 2147483648) SMALLINT (32767) TINYINT (0-255) To CREATE a table called Product Key in: CREATE TABLE Product ( pro_id Number, pro_description VARCHAR(25), pro_cost CURRENCY, Primary Key(pro_id)); To Delete a table called Product Key in: DROP TABLE Product To change a table called Product Key in: ALTER TABLE Product ADD Column prod_supplier TEXT(30); ALTER TABLE Product Alter Column prod_id Number;