Structured Query Language SQL INTRODUCTION TO STRUCTURED QUERY LANGUAGE SQL ISM 318 Database Systems Dr Hamid Nemati Structured Query Language SQL 4 4 Structured Query Language SQL 4 4 4 4 4 Understand basics of database languages Introduction to SQL Learn how to create a database using SQL Learn how to manipulate and manage a database using SQL Learn how to use Nested Queries Learn how to use Multiple Table Queries Learn how to create VIEWS Introduction to SQL l SQL meets ideal database language requirements u SQL coverage fits into three categories 1 Data definition Structured Query Language SQL v Commands to create the database and its table structures 2 Data management v Commands used to enter correct delete and update data within the database table 3 Data query v u u Commands to explore the database contents SQL is relatively easy to learn ANSI prescribes a standard SQL Introduction to SQL l Structured Query Language SQL Reasons for Studying SQL u The ANSI standardization effort has led to a de facto query standard for relational databases u SQL has become the basis for present and expected future DBMS integration efforts u SQL has become the catalyst in the development of distributed databases and database client server architecture Data Definition Commands l The Database Model u Database PRODUCT and VENDOR tables l l Structured Query Language SQL Each product is supplied by only a single vendor A vendor may supply many products The Database Model Data Definition Commands l The Tables and Their Components u Structured Query Language SQL Some observations on the PRODUCT and VENDOR tables l l l The VENDOR table contains vendors who are not referenced in the PRODUCT table PRODUCT is optional to VENDOR All V CODE values in the PRODUCT table must have a match in the vendor table A few products are supplied factory direct a few are made in house and a few may have been bought in a special warehouse sale That is a product is not necessarily supplied by a vendor VENDOR is optional to PRODUCT Data Definition Commands l Structured Query Language SQL Creating Table Structures CREATE TABLE table name attribute1 name and attribute1 characteristics attribute2 name and attribute2 characteristics attribute3 name and attribute3 characteristics primary key designation foreign key designation and foreign key requirement l Rules for table and attribute names u u u No more than 31 chars long Contain letters upper or lower case digits dollar signs and underscores only End with a letter or digit Data Definition Commands Some Common SQL Data Types Structured Query Language SQL Data Type Format INTEGER INTEGER SMALLINT SMALLINT NUMERIC NUMERIC l d DECIMAL DECIMAL l d CHARACTER CHARACTER l DATE DATE Data Definition Commands Structured Query Language SQL CREATE TABLE VENDOR V CODE FCHAR 5 V NAME VCHAR 35 V CONTACT VCHAR 15 V AREACODE FCHAR 3 V PHONE FCHAR 3 V STATE FCHAR 2 V ORDER FCHAR 1 PRIMARY KEY V CODE NOT NOT NOT NOT NOT NOT NOT NULL UNIQUE NULL NULL NULL NULL NULL NULL Data Definition Commands Structured Query Language SQL CREATE TABLE PRODUCT P CODE VCHAR 10 NOT NULL UNIQUE P DESCRIPT VCHAR 35 NOT NULL P INDATE DATE NOT NULL P ONHAND SMALLINT NOT NULL P MIN SMALLINT NOT NULL P PRICE DECIMAL 8 2 NOT NULL P DISCOUNT DECIMAL 4 1 NOT NULL V CODE SMALLINT PRIMARY KEY P CODE FOREIGN KEY V CODE REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE Data Definition Commands l SQL Integrity Constraints u Entity Integrity l l Structured Query Language SQL u PRIMARY KEY NOT NULL and UNIQUE Referential Integrity l l l FOREIGN KEY ON DELETE ON UPDATE Basic Data Management l Data Entry INSERT INTO table name VALUES attribute 1 value attribute 2 value etc Structured Query Language SQL u Examples INSERT INTO customers VALUES c001 TipTop Duluth 10 00 INSERT INTO Orders VALUES 1011 jan c001 a01 p01 1000 450 00 Basic Data Management l Checking the Table Contents SELECT attribute names FROM table names u Examples SELECT FROM products Structured Query Language SQL SELECT pid pname quantity price FROM customers SELECT pid pname quantity price FROM customers Basic Data Management l Saving the Table Contents COMMIT table names u Example COMMIT PRODUCT Structured Query Language SQL Basic Data Management l Adding Data to the Table INSERT INTO table name VALUES attribute values u Structured Query Language SQL Example INSERT INTO PRODUCT VALUES 14 Q1 L3 9 00 in Pwr saw lade 11 12 96 18 12 17 49 0 00 21344 Basic Data Management l Making a Correction UPDATE table name SET attribute name attribute value WHERE attribute name attribute value Structured Query Language SQL u Examples UPDATE PRODUCT SET P INDATE 12 11 96 WHERE P CODE 13 Q2 P2 UPDATE PRODUCT SET P INDATE 12 11 96 P PRICE 15 99 P MIN 10 WHERE P CODE 13 Q2 P2 l Restoring the Table Contents ROLLBACK Basic Data Management l Deleting Table Rows DELETE FROM table names WHERE attribute name attribute value u Structured Query Language SQL Example DELETE FROM PRODUCT WHERE P CODE 2238 QPD DELETE FROM PRODUCT WHERE P MIN 5 Queries l Partial Listing of Table Contents SELECT column s FROM table name WHERE conditions Structured Query Language SQL u Examples SELECT P DESCRIPT P INDATE P PRICE V CODE FROM PRODUCT WHERE V CODE 21344 Selected PRODUCT Table Attributes for the VENDOR CODE 21344 To Retrieve a Record And or Attribute Value l The most general form of the SELECT is SELECT DISTINCT attrib 1 attrib 2 FROM table 1 table 2 WHERE condition 1 boolean 1 condition 2 GROUP BY attrib 3 attrib 4 HAVING condition 3 boolean 1 condition 4 ORDER BY attrib 5 attrib 6 ASC DESC Structured Query Language SQL NOTES l The ORDER clause can be used to specify the vertical sequencing of values in the resulting table Sequencing options include u Structured Query Language SQL u l ASC for ascending DESC for descending with asc being the default EXAMPLE SELECT S NO STATUS FROM S WHERE CITY Paris ORDER BY STATUS DESC NOTES l Structured Query Language SQL l l Select in SQL does not eliminate duplicate rows from the result of the select statement unless user explicitly requests the distinct option Here SQL implementation is at odds with relational theory EXAMPLE SELECT CITY FROM Supplier l To element duplicates use DISTINCT clause SELECT DISTINCT CITY FROM Supplier NOTES l Structured Query Language SQL l l A qualified attribute name consists of a table name and an attribute name in that order separated by a period It is never wrong to use qualified name and sometimes it is essential e g if 2