Introduction to SQL Database Management System 1 ITED

Introduction to SQL Database Management System 1 (ITED 123 A) ITE Department

Objectives • Define terms • Interpret history and role of SQL • Discuss SQL: 1999 and SQL: 2008 standards • Identify the different types of SQL commands • Explore the SQL Developer and SQL *Plus environment ITE Department

SQL Overview • Structured Query Language • The standard for relational database management systems (RDBMS) • RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables ITE Department

History of SQL • 1970–E. F. Codd develops relational database concept • 1974 -1979–System R with Sequel (later SQL) created at IBM Research Lab • 1979–Oracle markets first relational DB with SQL • 1981 – SQL/DS first available RDBMS system on DOS/VSE • Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase (1986) • 1986–ANSI SQL standard released • 1989, 1992, 1999, 2003, 2006, 2008–Major ANSI standard updates • Current–SQL is supported by most major database vendors ITE Department

Purpose of SQL Standard • Specify syntax/semantics for data definition and manipulation • Define data structures and basic operations • Enable portability of database definition and application modules • Specify minimal (level 1) and complete (level 2) standards • Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets) ITE Department

Benefits of a Standardized Relational Language • • • Reduced training costs Productivity Application portability Application longevity Reduced dependence on a single vendor • Cross-system communication ITE Department

SQL Environment • Catalog – A set of schemas that constitute the description of a database • Schema – The structure that contains descriptions of objects created by a user (base tables, views, constraints) • Data Definition Language (DDL) – Commands that define a database, including creating, altering, and dropping tables and establishing constraints • Data Manipulation Language (DML) – Commands that maintain and query a database • Data Control Language (DCL) – Commands that control a database, including administering privileges and committing data ITE Department

SQL Statements • • • SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMENT • • GRANT REVOKE • • • COMMIT ROLLBACK Transaction control SAVEPOINT Data manipulation language (DML) Data definition language (DDL) Data control language (DCL) ITE Department

Figure 6 -1 A simplified schematic of a typical SQL environment, as described by the SQL: 2008 standard ITE Department 9

SQL Data Types ITE Department

Figure 6 -4 DDL, DML, DCL, and the database development process ITE Department 11

Oracle Database 11 g Manageability High availability Performance Security Information integration ITE Department

Development Environments for SQL • There are two development environments for this course: – Primary tool is Oracle SQL Developer – SQL*Plus command line interface may also be used SQL Developer SQL *Plus ITE Department

Creating a Database Connection 1 Connections tabbed page Database Connection Window 2 3 Click to test ITE Department

Browsing Database Objects • Use the Connections Navigator to: – Browse through many objects in a database schema – Review the definitions of objects at a glance ITE Department

Using the SQL Worksheet • Use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL *Plus statements. • Specify any actions that can be processed by the database connection associated with the worksheet. Click the Open SQL Worksheet icon. Select SQL Worksheet from the Tools menu, or ITE Department

Using the SQL Worksheet 2 1 4 3 6 5 8 7 9 1. 2. 3. 4. 5. 6. 7. 8. 9. Execute statement Run script Commit Rollback Cancel SQL History Execute explain plan Autotrace Clear ITE Department

Using the SQL Worksheet • Use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. • Specify any actions that can be processed by the database connection associated with the worksheet. Enter SQL statements. Results are shown here. ITE Department

Executing SQL Statements • Use the Enter SQL Statement box to enter single or multiple SQL statements. View the results on the Script Output tabbed page. ITE Department

Saving SQL Scripts Click the Save icon to save your SQL statement to a file. Enter a file name and identify a location to save the file, and click Save. The contents of the saved file are visible and editable in your SQL Worksheet window. ITE Department

Executing SQL Statements • Use the Enter SQL Statement box to enter single or multiple SQL statements. F 9 F 5 F 9 ITE Department

Formatting the SQL Code Before formatting After formatting ITE Department

Using Snippets • Snippets are code fragments that may be just syntax or examples. When you place your cursor here, it shows the Snippets window. From the drop-down list, you can select the functions category that you want. ITE Department

Logging In to SQL*Plus 1 sqlplus [username[/password[@database]]] 2 ITE Department

Displaying the Table Structure • Use the SQL*Plus DESCRIBE command to display the structure of a table: DESC[RIBE] tablename ITE Department

Displaying the Table Structure DESCRIBE departments Name -----------DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID Null? Type -----------NOT NULL NUMBER(4) NOT NULL VARCHAR 2(30) NUMBER(6) NUMBER(4) ITE Department

SQL*Plus File Commands • • SAVE filename GET filename START filename @ filename EDIT filename SPOOL filename EXIT ITE Department

Using the SAVE, START, and EDIT Commands LIST 1 SELECT last_name, manager_id, department_id 2* FROM employees SAVE my_query Created file my_query START my_query LAST_NAME MANAGER_ID DEPARTMENT_ID -------------King 90 Kochhar 100 90. . . 107 rows selected. ITE Department

Using the SAVE, START, and EDIT Commands EDIT my_query ITE Department

Summary • In this lesson, you should have learned the following: – History of SQL – SQL statements – Browse, create, and edit database objects – Execute, edit, and save SQL statements ITE Department

References • Hoffer, J. , Ramesh, V. , Topi, H. (2013). Modern Database Management 11 th Edition, Prentice Hall. • Singh, P. , Pottle, B. (2009). Oracle Database 11 g: SQL Fundamentals I, Oracle. ITE Department