WIXLIB

Chapter 6Introduction to SQL:Structured Query LanguageObjectivesn n n n n Define termsDefine a database using SQL data definitionlanguageWrite single table queries using SQLEstablish referential integrity using SQLDiscuss SQL:1999 and SQL:200n standards

Figure 6-1A simplified schematic of a typical SQL environment, asdescribed by the SQL: 200n standardSQL Environmentn Data Definition Language (DDL)- Commands that define a database, including creating, altering,and dropping tables and establishing constraints- CREATE / DROP / ALTER, n Data Manipulation Language (DML)- Commands that maintain and query a database- INSERT, UPDATE, DELETE, SELECT, n Data Control Language (DCL)- Commands that control a database, including administeringprivileges and committing data- GRANT, ADD, REVOKE

SQL Database Definitionn n Data Definition Language (DDL)Major CREATE statements:---CREATE SCHEMA–defines a portion of thedatabase owned by a particular userCREATE TABLE–defines a new table and itscolumnsCREATE VIEW–defines a logical table fromone or more tables or viewsDDL: Table CreationGeneral syntax for CREATE TABLEused in data definition languageSteps in table creation:1. Identify data types forattributes2. Identify columns that canand cannot be nullCREATE TABLE table name (field type constraints,field2 type2,CONSTRAINT name .,.);3. Identify columns thatmust be unique(candidate keys)CREATE TABLE Book (ISBN CHAR(9)NOT NULL,Title VARCHAR(20) UNIQUE,Pages INTEGER,CONSTRAINT ISBN PRIMARY KEY);6. Identify constraints oncolumns (domainspecifications)4. Identify primary key5. Determine default values7.Identify foreign keys

SQL Data TypesThe following slides create tables forthis enterprise data model(from Chapter 1, Figure 1-3)

Figure 6-6 SQL database definition commands for Pine Valley FurnitureCompany (Oracle 11g)Overall tabledefinitions1. Defining attributes and their data typesThis is Oracle syntax.In MySQLNUMBER should be replaced by NUMERICVARCHAR2 should be replaced by VARCHAR

2. Non-nullable specificationPrimary keyscan never haveNULL values4. Identifying Primary KeyNon-nullable specificationsPrimary keySome primary keys are composite

Controlling the Values in Attributes5. Default value6. Domain constraint7. Identifying foreign keys and establishing relationshipsPrimary key ofparent tableForeign key of dependent table

66324'70542' 7'4756'4756' tionNo)Semester)CourseID'2143'3467'4756' 'Birkin'Berndt'Collins''2712'2713'2714'2715' 113'ISM'3112'ISM'4212'ISM'4930' 08'IR2008'IR2008'IR2008'''Practice: Exercise #1Write a database description for each of the relations shown,using SQL DDL. Assume the following attribute data types:StudentID (integer, primary key)StudentName (max 25 characters)FacultyID (integer, primary key)toFacultyName (max 25 characters)e indoclCourseID (8 characters, primary key)Q L eg.sqSrtRouCourseName (max 15 characters)e y udenvaS le StDateQualified (date)a fiSectionNo (integer, primary key)Semester (max 7 characters)

Using MySQLn n n n n n n Available on csdb.csc.villanova.eduInvoke withmysql –u username –D database -pSHOW DATABASES;SHOW TABLES;DESCRIBE name T; (or SHOW COLUMNS FROM name T;)SOURCE script.sql\! shell commandConventionsn n n n n -- comments until end of line/* can also use C-style comments */SQL is case insensitive (except for data)But we usually type reserved words in ALL CAPSUse single quotes for ‘character constants’

Changing Tablesn ALTER TABLE statement allows you tochange column specifications:n Table Actions:n Example (adding a new column with a default value):Should be single quotes!Removing Tablesn DROP TABLE statement allows you toremove tables from your schema:n DROP TABLE CUSTOMER T

Practice: Exercise #4Write SQL data definition commands for each of the following:1. Add an attribute Class to the Student table, then drop it2. Create a new Dummy table, then remove it3. Change the FacultyName field from 25 characters to 40charactersInsertn INSERT INTO table (fields)VALUES (values)

Insert Statementn Adds one or more rows to a tableInserting into a tablen Better practice is to list the fields that actually get datan Inserting from another tablen ntName'38214'54907'66324'70542' 756' 'Birkin'Berndt'Collins''2712'2713'2714'2715' ateQualified'lsqg. itheR swntde tableuSt ourend late ytxE oputo p 43'3467'3467'4756'4756' '3113'ISM'3112'ISM'4212'ISM'4930' 08'IR2008'IR2008'IR2008'''

Creating Tables with Identity ColumnsIntroduced with SQL:200nInserting into a table does not require explicit customer ID entry:INSERT INTO CUSTOMER T VALUES ( ‘Contemporary Casuals’,‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);Note: In mysql only the primary key can be auto-incremented:ID INT PRIMARY KEY NOT NULL AUTO INCREMENTDelete Statementn Removes rows from a table:DELETE FROM tableWHERE conditions;n n If no conditions, delete all dataDoes NOT delete the meta-data,use DROP TABLE for that

Delete Statementn Delete certain rowsn n DELETE FROM Customer T WHERECustomerState ‘CA’;Delete all rowsn DELETE FROM CUSTOMER T;Update Statementn Modifies data in existing rows:n UPDATEtableSET field valueWHERE conditions

Update Statementn Can use the field to modify in an expression:UPDATE StudentSET Age Age 1WHERE StudentID 1n Do this:- Add an Age field to the Student T table, with adefault value of 18- Increment the Age of the student with ID 54907Practice: Exercise #5Write SQL commands for the following:1. Create two different forms of the INSERT command to add astudent with a student ID of 65798 and last name Lopez tothe Student table.2. Now write a command that will remove Lopez from theStudent table.3. Create an SQL command that will modify the name of courseISM 4212 from Database to Introduction to RelationalDatabases.

Data Integrity Controlsn Referential integrity – constraint that ensures thatforeign key values of a table must match primarykey values of a related table in 1:M relationshipsn Restricting:n n n Deletes of primary recordsUpdates of primary recordsInserts of dependent recordsData Integrity ControlsWrite CREATE TABLE statements:What if a major is deleted from the Major table?What should happen to the rows pointing to that major?

Data Integrity ControlsCREATE TABLE Student(Id INTEGER PRIMARY KEY,Name VARCHAR(20) NOT NULL,MajorId CHAR(3) REFERENCES Major(Id) ON UPDATE RESTRICT);Options: ON [UPDATE DELETE]RESTRICT/*CASCADE/*SET NULL/*SET DEFAULT 7'66324'70542' 7'4756'4756' 56' 'Birkin'Berndt'Collins''2712'2713'2714'2715' olSECTION'(SectionNo,'Semester,'CourseID)''''do not allow */propagate change */Set MajorId to NULL */Set MajorId to its default value M'3113'ISM'3112'ISM'4212'ISM'4930' 08'IR2008'IR2008'IR2008'''onyCtirgInte?

Basic SELECTBasic SELECTn Used for queries on single or multiple tables.SELECT [DISTINCT] attribute-listFROM table-listWHERE conditions SELECT : the columns (and expressions) to be returnedfrom the queryFROM: indicate the table(s) or view(s) from whichdata will be obtainedWHERE: indicate the conditions under which a rowwill be included in the result

Basic SELECTn Used for queries on single or multiple tables.SELECT [DISTINCT] attribute-listFROM table-listWHERE conditions Conditions: comparisons, combinedwith AND, OR, NOT DISTINCT is an optional keywordindicating that the answer should notcontain duplicates. The default is thatduplicates are not eliminated!Fragment of Figure 6-10: SQL statement processing order

SELECT Examplen Find products with standard price less than 275Table 6-3: Comparison Operators in SQLSELECT Example Using Aliasn Alias is an alternative column or table nameSELECT Cust.CustomerName AS Name,Cust.CustomerAddressFROM Customer T AS CustWHERE CustomerName ‘Home Furnishings’;

Practice: Exercise #6Write SQL queries to answer the following questions:1. Which students have an ID number that is less than 50000?2. What is the name of the faculty member whose ID is 4756?Summaryn DDL- CREATE TABLE- DROP TABLE- ALTER TABLEn DML-INSERT INTOUPDATEDELETE FROMSELECT

SQL Database Definition ! Data Definition Language (DDL) ! Major CREATE statements: - CREATE SCHEMA–defines a portion of the database owned by a particular user - CREATE TABLE–defines a new table and its columns - CREATE VIEW