1 / 12

ITEC 313 Database Programming

ITEC 313 Database Programming. Bits of SQL. Objectives. Review Select Statement Creating tables Learn how to create and use Sequence View Learn how to Grant/Revoke priveleges. Syntax of SELECT. SELECT [ALL | DISTINCT] select-list FROM table-reference-list

yank
Download Presentation

ITEC 313 Database Programming

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. ITEC 313 Database Programming Bits of SQL

  2. Objectives • Review • Select Statement • Creating tables • Learn how to create and use • Sequence • View • Learn how to Grant/Revoke priveleges

  3. Syntax of SELECT SELECT [ALL | DISTINCT] select-list FROM table-reference-list [WHERE search-condition] [GROUP BY column-name [, column-name]... ] [HAVING search-condition] [ [UNION | UNION ALL |INTERSECT | MINUS] select-statement ] ... [ORDER BY {unsigned integer | column-name} [ASC|DESC]]

  4. Syntax of SELECT • select-list : • columns, expressions, fixed values. • Include aliases • table-reference-list • Tables, views, subqueries(inline view) • search-condition • Conditions involving columns, fixed values, subqueries

  5. Syntax of Create Table Statement CREATE TABLE table-name ( Column-name data-type [column-constraints] [DEFAULT default-value] {, Column-name data-type [column-constraints] [DEFAULT default-value] } [table-constraint {, table-constraint}] )

  6. Format of Alter Table Statement ALTER TABLE table-name ADD column-declaration [ column-constraints] Or ALTER TABLE table-name ADD table-constraints Or ALTER TABLE table name MODIFY column

  7. Sequences • autonumber field • independent of a column • An object in Oracle that is used to generate a number sequence • Useful for creating a unique number to act as a primary key

  8. Format of Sequence CREATE SEQUENCE sequence_name    MINVALUE value    MAXVALUE value    START WITH value    INCREMENT BY value    CACHE value;

  9. Views • Representation of an sql statement • Only the code to create the view is stored in the data dictionary NOT the actual data • Used for • Improving efficiency • Improving Security • Hiding details/columns from end users

  10. Syntax of VIEW CREATE VIEW view-name AS Select-statement

  11. Grant/Revoke • Privileges are of two types : • System Privileges • Object privileges • GRANT : allows users access to your objects. • INSERT, DELETE, UPDATE, SELECT, EXECUTE privileges may be granted to other users • REVOKE : removes existing privileges on an object from other users.

  12. SYNTAX of Grant/Revoke • GRANT INSERT, DELETE, UPDATE, SELECT, EXECUTE|ALL on object-name TO user-list|ROLE|PUBLIC; • REVOKE INSERT, DELETE, UPDATE, SELECT, EXECUTE | ALL on object-name FROM user-list|ROLE|PUBLIC; • ROLE: contaimns groups of users determined by the DBA • PUBLIC : A system ROLE that contains all users of the database

More Related