Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    [M5-MAIN]_Introduction to SQL

    Uploaded by

    202310943

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    [M5-MAIN]_Introduction to SQL

    Uploaded by

    202310943
    4 views33 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    4 views33 pages

    [M5-MAIN]_Introduction to SQL

    Uploaded by

    202310943

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 33

    INFORMATION MANAGEMENT

    MODULE 5: Introduction to SQL


    MODULE 5

    INTRODUCTION TO SQL

    ■At the end of the chapter, the learner should be able to:
    • Define terms
    • Interpret history and role of SQL
    • Discuss SQL:1999 and SQL:2011 standards
    • Identify the different types of SQL commands
    • Explore the SQL Developer and SQL *Plus environment
    • 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
    • 1970–Edgar 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, 2011–Major ANSI standard
    updates
    • Current–SQL is supported by most major database vendors
    • 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)
    • Reduced training costs
    • Productivity
    • Application portability
    • Application longevity
    • Reduced dependence on a single vendor
    • Cross-system communication
    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
    COMMANDS SQL LANGUAGE

    SELECT DATA MANIPULATION LANGUAGE (DML)


    INSERT
    UPDATE
    DELETE
    MERGE
    CREATE DATA DEFIINITION LANGUAGE (DDL)
    ALTER
    DROP
    RENAME
    TRUNCATE
    COMMENT
    GRANT DATA CONTROL LANGUAGE (DCL)
    REVOKE
    COMMIT TRANSACTION CONTROL
    ROLLBACK
    SAVEPOINT
    Figure 6-4
    DDL, DML, DCL, and the database development process
    Manageability

    High availability

    Performance

    Security

    Information integration
    There are two development environments for this course:
    • Primary tool is Oracle SQL Developer
    • SQL*Plus command line interface may also be used

    SQL *Plus
    SQL Developer
    1 Connections Database
    tabbed page Connection
    Window

    3 Click to test
    Use the Connections Navigator to:
    • Browse through many objects in a database schema
    • Review the definitions of objects at a glance
    • 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
    2 4 6 8

    1. Execute
    statement
    2. Run script
    1 3 5 7 9
    3. Commit
    4. Rollback
    5. Cancel
    6. SQL History
    7. Execute explain
    plan
    8. Autotrace
    9. Clear
    • 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.
    Use the Enter SQL Statement box to enter single or multiple SQL
    statements.

    Use the Enter SQL


    Statement box to enter
    single or multiple SQL
    statements.

    View the results on the


    Script Output tabbed
    page.
    Click the Save icon to save your Enter a file name and identify a
    SQL statement to a file. location to save the file, and
    click Save.

    The contents of the saved file


    are visible and editable in your
    SQL Worksheet window.
    Use the Enter SQL Statement box to enter single or multiple SQL
    statements.

    F9 F5

    F5

    F9
    Before
    formatting

    After
    formatting
    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.
    1

    sqlplus [username[/password[@database]]]

    2
    Use the SQL*Plus DESCRIBE command to display the structure of a table:

    DESC[RIBE] tablename
    DESCRIBE departments

    Name Null? Type


    ----------------------- -------- ------------
    DEPARTMENT_ID NOT NULL NUMBER(4)
    DEPARTMENT_NAME NOT NULL VARCHAR2(30)
    MANAGER_ID NUMBER(6)
    LOCATION_ID NUMBER(4)
    • SAVE filename
    • GET filename
    • START filename
    • @ filename
    • EDIT filename
    • SPOOL filename
    • EXIT
    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.
    EDIT my_query
    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
    ASK ANY QUESTION RELATED TO OUR
    TOPIC FOR TODAY.
    Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For Dummies.
    Harrington, J. (2016). Relational Database Design and Implementation (4th
    Edition). Morgan Kaufmann
    Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction to
    Databases and Data Warehouses. Prospect Press
    Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson.
    Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison-
    Wesley.
    Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th
    Edition, Prentice Hall

    You might also like