1 / 33

Introduction To SQL

Introduction To SQL. Chapter 7. Objectives. Understand the 3 types of SQL commands (Data Definition, Data Manipulation, Data Control) Be able to create a table Be able to populate a table Be able to retrieve information from a single table. SQL Commands Overview. SQL is not case sensitive

hamlet
Download Presentation

Introduction To SQL

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. Introduction To SQL Chapter 7

  2. Objectives • Understand the 3 types of SQL commands (Data Definition, Data Manipulation, Data Control) • Be able to create a table • Be able to populate a table • Be able to retrieve information from a single table

  3. SQL Commands Overview • SQL is not case sensitive • Type commands using uppercase or lowercase letters • Exception: when inserting character values into a table, use the correct case • Commands are free format • No rule says that a particular word must begin in a particular position on a line • However, you cannot have any blank lines in a command • Indicate the end of a command line by typing a semicolon (required in Oracle, but it is not universal)

  4. Definitions • 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.

  5. DDL, DML, DCL, and The Database Development Process

  6. Data Definition • Creating Tables • Using and Defining Views (Saving Queries) • Creating Data Integrity Control • Changing Table Definitions • Removing Tables

  7. Creating Tables • Command: CREATE TABLE • Name column (attribute) • Less than 18 characters (in Oracle < 30) • Start with a letter, contain letters, numbers, & underscores ( _ ). No spaces! • Identify data type, length of each attribute • NUMBER, DATE, CHAR, VARCHAR2 • Identify columns that can accept null values • UNIQUE, NOT NULL, PRIMARY KEY • Determine default values • DEFAULT • Identify primary key – foreign key mates • REFERENCES • Identify columns where domain specifications need restrictions • CHECK

  8. Create Table Example CREATE TABLE BOOK ( ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000), TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL, COST NUMBER(8,2) DEFAULT 0.00 , LENT_DATE DATE, SECTION_ID NUMBER(3), CONSTRAINT BOOK_PK PRIMARY KEY (ISBN), CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID) ) The SECTION table must already be defined.

  9. Data Integrity • Column Level Constraints • Go directly after the column definition to which they refer ISBN NUMBER(10) PRIMARY KEY, • Table Level Constraints • Go after the last column definition • Required for compound (multi-column) primary key and foreign keys PRIMARY KEY (ORDER_ID, PRODUCT_ID) • Oracle gives default names to constraints not explicitly named • Names may make it easier to identify, enable/ disable the constraint • The names must be unique across all tables in the database.

  10. Data Integrity CREATE TABLE Box ( Box_id CHAR(2) PRIMARY KEY, Fruit CHAR(10) DEFAULT 'apple' CHECK (Fruit IN ('apple', 'pear', 'orange')) REFERENCES FruitTable ); Ex: Column Constraints CREATE TABLE Box ( Box_id CHAR(2) CONSTRAINT Box_PK PRIMARYKEY, Fruit CHAR(10) DEFAULT 'apple' CONSTRAINT Box_Check CHECK (Fruit IN ('apple', 'pear', 'orange')) CONSTRAINT Box_FK REFERENCES FruitTable ); Ex: Named Column Constraints

  11. Data Integrity CREATE TABLE Box ( Box_id CHAR (2), Fruit VARCHAR2 (10) DEFAULT 'apple', CONSTRAINT Box_PK PRIMARYKEY (Box_id), CONSTRAINT Box_FK FOREIGNKEY (Fruit) REFERENCES FruitTable, CONSTRAINT Box_Fruit_Values CHECK (Fruit IN('apple', 'pear', 'orange')) ); Ex: Named Table Constraints

  12. Oracle Data Types • Use VARCHAR2 instead of VARCHAR • Now deprecated - VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions. • Storing character data as VARCHAR2 will save space: • Store 'SMITH' not 'SMITH ' • NUMBER, DECIMAL and INTEGER • DECIMAL is not available in some SQL versions (MS Access SQL) • In Oracle, DECIMAL (3,2) is same as NUMBER(3,2) • In Oracle, INTEGER is the same as NUMBER(38) Name Type REPNUM NOT NULL CHAR(2) COMMISSION NUMBER(7,2) RATE NUMBER(3,2) NUM NUMBER(38) NUM2 NUMBER(38)

  13. Using & Defining Views • Command: CREATE VIEW • A view is a named query • Tables vs. Views • Tables are used to store data, correspond to entities • Views correspond to “saved queries”, contents materialize when referenced.

  14. View Example CREATE VIEW CS_MAJOR_V AS SELECT Name, Major, Email, Address FROM StudentTable WHERE Major =‘CSCI’ ; Define the view: Remember semicolon. Use the view: SELECT * FROM CS_MAJOR_V;

  15. Using & Defining Views • Views can simplify query commands • Once a view is created, use it to create a more complex query SELECT Name, Email FROM CS_MAJOR_V WHERE Name = ‘Matt Smith’; • Provide data security • Tables/columns not included will not be obvious to user • Restrict user access to a view with GRANT and REVOKE s • Enhance programming consistency • Creating a view for each base table establishes physical data independence • Programs can reference view - limit dependence on physical table.

  16. Creating Data Integrity Controls • Referential Integrity – an attribute in one relation depends on the value of a primary key in the same or another relation • Restrict values of a Foreign key • REFERENCES prevents a foreign key from being added if it is not already a valid value in the referenced primary column CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID)

  17. Creating Data Integrity Controls • Restrictions on Updating a Primary key • ON UPDATE RESTRICT • Cannot change primary key if value used in another table • ON UPDATE CASCADE • Pass change to all tables that ref primary key value • ON UPDATE NULL • Change primary key value, but make foreign ref NULL • Restrictions on Deleting a Primary key • ON DELETE (RESTRICT, CASCADE, NULL)

  18. Data Definition: Create Table Example CREATE TABLE BOOK ( ISBN NUMBER(10) CONSTRAINT B_ISBN CHECK (ISBN BETWEEN 1 AND 2000), TITLE VARCHAR2(200) CONSTRAINT B_TITLE NOT NULL, COST NUMBER(8,2) DEFAULT 0.00 , SECTION_ID NUMBER(3), CONSTRAINT BOOK_PK PRIMARY KEY (ISBN), CONSTRAINT BOOK_SECT FOREIGN KEY (SECTION_ID) REFERENCES SECTION(SECTION_ID), ON UPDATE CASCADE )

  19. Changing Table Definitions • Command: ALTER TABLE • Keywords ADD, DROP and MODIFY allow the column’s names, data types, length and constraints to be changed. ALTER TABLE STUDENT ADD Advisor VARCHAR(15); • When adding an new column, the initial value for all rows will be NULL

  20. Removing Tables • Command: DROP TABLE • Removes a table from the database. DROP TABLE STUDENT;

  21. Describe Table • Command: DESCRIBE DESCRIBE SampTable; Display the structure of the table Name Type REPNUM NOT NULL CHAR(2) COMMISSION NUMBER(7,2) RATE NUMBER(3,2) NUM NUMBER(38) NUM2 NUMBER(38)

  22. Data Manipulation • Inserting Data • Deleting Data • Updating Data • Selecting Data : Query

  23. Inserting Data: Populate Tables / Views • Command: INSERT INTO • Insert complete record INSERT INTO SampTable VALUES (111, ‘Cody’, ‘CBIS’); • Insert with nulls – specify columns INSERT INTO SampTable (ID, Name) (222, ‘Greg’); • Insert a subset of another table INSERT INTO GA_Customer SELECT * FROM Customer WHERE CUSTOMER_STATE = ‘GA’; • Inserting into a VIEW will insert data into base table

  24. Inserting Data • Whole Numbers: NUMBER (4) • Fractional numbers will round to nearest whole number . • Cannot insert numbers with more digits that set in definition If NUM is defined as NUMBER(4) Inserting: 1.99 will be stored as 2 -- value is rounded Inserting : 12345 will not be allowed – too many digits • Decimal Numbers: NUMBER (p, q) • A decimal number p digits long with q of these digits being digits to the right of decimal point. If RATE is defined as NUMBER(3,2) Inserting: 1.99 will be ok - 1.99 is stored Inserting: 1.3456 will be stored as 1.35 – value is rounded Inserting : 12.1 will not be allowed – too many digits • NOTE: In some SQL Implementations the decimal point and negative sign may be considered a digit.

  25. Inserting Data • DATE • The format for DATE varies from one SQL implementation to another: Oracle: ‘DD-MON-YYYY’ : ‘4-JUL-2000’ Access: #MM/DD/YYYY# : #07/04/2000# • Valid insertions in Oracle ‘3-OCT-1999’ ‘3-oct-1999’ ‘03-October-1999’ • Invalid insertions in Oracle ’10/3/1999’ ‘ October 3, 1999’

  26. Deleting Database Contents • COMMAND: DELETE • Deleting rows that meet a certain criteria DELETE FROM CUSTOMER WHERE Customer_State = ‘HI’; • Deleting all rows from the CUSTOMER table DELETE FROM CUSTOMER;

  27. Changing Database Contents • Command: UPDATE • Modify the price of product 7 UPDATE PRODUCT SET Unit_Price = 777 WHERE ProductID = 7 • Set the Chris’ major to NULL UPDATE STUDENT SET Major = NULL WHERE Name = ‘Chris’;

  28. SELECT Command • SELECT clause • Followed by the columns to be included in the query. • An asterisk (*) indicate to include all columns in query • FROM clause • followed by name of the table that contains the data to query • WHERE clause • followed by conditions that apply to the data to be retrieved

  29. Selecting Data: Query • Using Comparison Operators SELECT OrderID, OrderDate FROM ORDER WHERE OrderDate > ’25-OCT-2004’;

  30. Selecting Data: Query • Using Boolean Operators (AND, OR, NOT) • The CSCI Major named Chris SELECT * FROM STUDENT WHERE Name = ‘Chris’ AND Major = ‘CSCI’; • Show record if it is a CSCI Major named Chris OR if it is Matt SELECT * FROM STUDENT WHERE (Name = ‘Chris’ AND Major = ‘CSCI’) OR Name = ‘Matt’;

  31. Selecting Data: Query • Using Ranges for Qualification • Show students who GPA is in range 3.4 – 3.7 SELECT * FROM STUDENT WHERE GPA >=3.4 AND GPA <=3.7; SELECT * FROM STUDENT WHERE GPA BETWEEN 3.4 AND 3.7;

  32. Selecting Data: Query • Using IN and NOT IN with Lists • Show students who live in GA, FL, or TX SELECT * FROM STUDENT WHERE State IN (‘GA’, ‘FL’, ‘TX’); • Show students who DO NOT live in GA, FL, or TX SELECT * FROM STUDENT WHERE State NOT IN (‘GA’, ‘FL’, ‘TX’);

  33. Selecting Data: Query • Using ORDER BY clause to sort results • Sort Results by Name: ascending order SELECT * FROM STUDENT ORDER BY Name; • To sort on multiple keys, list keys in order of importance • To sort in descending order, follow the sort key with the word DESC • Sort by majors in descending order. Within each group of students that have the same major, sort the students by their Name. • SELECT * FROM STUDENT • ORDER BY Major DESC, Name;

More Related