Lecture_4-_SQL_and_Advanced_SQL

Lecture_4-_SQL_and_Advanced_SQL - CSCI-507 Database Theory...

Info icon This preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
1 CSCI-507 Database Theory Lecture 3 Structured Query Language (SQL) & Advanced SQL 2 Objectives In this lecture, you will learn: The basic commands and functions of SQL How to use SQL for data administration (to create tables, indexes, and views) How to use SQL for data manipulation (to add, modify, delete, and retrieve data) How to use SQL to query a database for useful information
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
2 3 Objectives (con’d.) About the relational set operators UNION, UNION ALL, INTERSECT, and MINUS How to use the advanced SQL JOIN operator syntax About the different types of subqueries and correlated queries How to use SQL functions to manipulate dates, strings, and other data 4 Objectives (cont’d.) How to create and use updatable views How to create and use triggers and stored procedures How to create embedded SQL
Image of page 2
3 5 Introduction to SQL SQL functions fit into two broad categories: – Data definition language – Data manipulation language Basic command set has vocabulary of fewer than 100 words American National Standards Institute (ANSI) prescribes a standard SQL Several SQL dialects exist 6 Data Definition Commands The database model – In this chapter, a simple database with these tables is used to illustrate commands: • CUSTOMER • INVOICE • LINE • PRODUCT • VENDOR – Focus on PRODUCT and VENDOR tables
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
4 7 8 Creating the Database Two tasks must be completed: – Create database structure – Create tables that will hold end-user data First task: – RDBMS creates physical files that will hold database – Differs substantially from one RDBMS to another
Image of page 4
5 9 The Database Schema • Authentication – DBMS verifies that only registered users are able to access database – Log on to RDBMS using user ID and password created by database administrator Schema – Group of database objects that are related to each other 10 Data Types Data type selection is usually dictated by nature of data and by intended use Supported data types: – Number(L,D), Integer, Smallint, Decimal(L,D) – Char(L), Varchar(L), Varchar2(L) – Date, Time, Timestamp – Real, Double, Float – Interval day to hour – Many other types
Image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
6 11 Creating Table Structures Use one line per column (attribute) definition Use spaces to line up attribute characteristics and constraints Table and attribute names are capitalized NOT NULL specification UNIQUE specification 12 Creating Table Structures (cont’d.) Primary key attributes contain both a NOT NULL and a UNIQUE specification RDBMS will automatically enforce referential integrity for foreign keys Command sequence ends with semicolon
Image of page 6
7 13 SQL Constraints NOT NULL constraint – Ensures that column does not accept nulls UNIQUE constraint – Ensures that all values in column are unique DEFAULT constraint – Assigns value to attribute when a new row is added to table CHECK constraint – Validates data when attribute value is entered 14 SQL Indexes
Image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern