Download
1 / 19
190 likes | 196 Vues
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.
E N D
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. • 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).
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.
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.
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
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
Simple Queries Syntax: SELECT { * | column | column_expression [,…] } FROM table_name; Staff
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;
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;
Simple QueriesRow selection (WHERE clause) Syntax: SELECT { * | column | column_expression [,…] } FROM table_name WHERE condition; Comparison operators: = , <, >, <=, >=, <>
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;
Demo Oracle 10g (SQL worksheet)
From Start menu choose: All Programs Oracle-OraClient10g_home1 Application Development SQLPlus Worksheet.
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.
The worksheet window will open. Make sure that “Connected” word is written in the lower panel.
Disconnect icon: connect/disconnect from server. • Execute icon: execute a SQL statement (as shown below).
Command History icon: show the SQL Command executed during this session. • Previous/Next Command icon: show the previous/Next SQL statement (Undo/ Redo).
