1 / 19

Oracle & SQL Introduction

Oracle & SQL Introduction. Database Concepts Revision. DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS?. Database Design Revision. Steps in building a database for an application: Analysis: Understand real-world domain being captured.

bryantt
Download Presentation

Oracle & SQL Introduction

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Oracle & SQL Introduction

  2. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS?

  3. Database Design Revision Steps in building a database for an application: • Analysis: • Understand real-world domain being captured. • Design: • Specify it using a database conceptual model (ER). • Translate specification to model of DBMS (Relational or tables). • Implementation (using DBMS): • Create schema using DBMS commands (DDL). • Load data (DML).

  4. Relational Database • A relational database is a DB that stores the data in the form of tables/relations with rows and columns. Every table shares at least one column with another table (Relationships). • A table/Relation is the category of data, like Staff. The columns are information about the category (Attributes), like name or address and the rows are the actual data or records.

  5. Oracle • Oracle is a powerful relational database management system (RDBMS) that offers a large feature set. • Oracle is widely regarded as one of the popular full-featured database systems on the market today. • In almost all relational databases, data is accessed through Structured Query Language (SQL), and Oracle is one of them. • SQL is nonprocedural language that represents a combination of DDL and DML.

  6. Data Definition Language (DDL) • DDL is a descriptive language for defining the database schema. • Some of the main SQL-DDL commands are: • CREATE TABLE • ALTER TABLE • DROP TABLE

  7. Data Manipulation Language (DML) • DML is a language for retrieving and updating (insert, delete, & modify) the data in the DB. • The main SQL-DML commands are: • SELECT • INSERT INTO • UPDATE • DELETE FROM

  8. Simple Queries Syntax: SELECT { * | column | column_expression [,…] } FROM table_name; Staff

  9. Simple QueriesRetrieve all columns & rows Example: Retrieve all staff information. SELECT sno, fname, lname, position, sex, dob, salary, bno FROM staff; OR SELECT * FROM staff;

  10. Simple QueriesRetrieve specific columns & all rows Example: List salaries of all staff, showing only the staff number, the full and last name, and salary. SELECT sno, fname, lname, salary FROM staff;

  11. Simple QueriesRow selection (WHERE clause) Syntax: SELECT { * | column | column_expression [,…] } FROM table_name WHERE condition; Comparison operators: = , <, >, <=, >=, <>

  12. Simple QueriesRow selection (WHERE clause) Example: List all staff with a salary greater than 10,000. SELECT sno, fname, lname, salary FROM staff WHERE salary > 10000;

  13. Demo Oracle 10g (SQL worksheet)

  14. From Start menu choose: All Programs  Oracle-OraClient10g_home1  Application Development SQLPlus Worksheet.

  15. A DOS window will open (Don't close it) and a login window. • In the login window type S then your serial# then _ then your lecture section# as the Username & password. • In the Service field type O10G then press OK.

  16. The worksheet window will open. Make sure that “Connected” word is written in the lower panel.

  17. Disconnect icon: connect/disconnect from server. • Execute icon: execute a SQL statement (as shown below).

  18. Command History icon: show the SQL Command executed during this session. • Previous/Next Command icon: show the previous/Next SQL statement (Undo/ Redo).

  19. Try your account and execute a simple query.

More Related